sqlserver提高篇

来源:http://www.cnblogs.com/julinhuitianxia/archive/2017/05/16/6864282.html
-Advertisement-
Play Games

Microsoft SQL Server2008複習提高 一．Microsoft SQL Server 系統的體繫結構 1.Microsoft SQL Server2008由4個主要的部分組成，即4個服務： 資料庫引擎、分析服務、報表服務、集成服務。如下圖： 2.Microsoft SQL Serv ...


Microsoft SQL Server2008複習提高

一．Microsoft SQL Server 系統的體繫結構

1.Microsoft SQL Server2008由4個主要的部分組成，即4個服務：

  資料庫引擎、分析服務、報表服務、集成服務。如下圖：

 

2.Microsoft SQL Server2008提供了兩種類型的資料庫:系統資料庫和用戶資料庫。

系統資料庫包括：master、model、msdb、Resource和tempdb資料庫；

用戶實例資料庫包括：AdventureWorks、AdventureWorksDW、AdventureWorksDW2008、AdventureWorksLT、AdventureWorksLT2008等資料庫。

Master資料庫是最重要的系統資料庫，它記錄了伺服器配置信息、登錄帳戶信息、資料庫文件信息、SQL Server初始化信息等。

Model 他是於SQLServerAgent服務有關的資料庫。該系統記錄了有關作業、警報、操作員、調度信息，這些信息可以用作自動化系統操作。

Tempdb 是一個臨時資料庫，用於存儲查詢過程中的中間數據和結果。

3.OLTP和OLAP

OLTP是傳統的關係型資料庫的主要應用，主要是基本的、日常的事務處理，例如銀行交易。OLAP是數據倉庫系統的主要應用，支持複雜的分析操作，側重決策支持，並且提供直觀易懂的查詢結果.

4.資料庫對象類型主要包括了：

資料庫關係圖、表、視圖、同義詞、可編程性、Service Broker、存儲和安全性等。

5.SQL Server Profilter（照相）

它主要用於從伺服器中捕獲Sqlserver2008事件的工具。

6.資料庫引擎優化顧問：

這個工具可以幫助用戶分析工作負荷、提出創建高效率索引的建議等功能。他就是一個優化大師，儘可能的提高系統效率。

7.資料庫管理員DBA

職責：保證系統正常高效的運行。

 

二．管理安全性

  1. 管理登錄名

包括：創建登錄名、設置密碼策略、查看登錄名信息及修改和刪除登錄名等。

登錄策略：windows身份驗證和sqlserver身份驗證登錄。

比如：create login lin   from windows go//創建windows登錄名

      create login lin with password=‘sa’//創建sqlserver登錄名

  1. 管理資料庫用戶

資料庫用戶是資料庫級的主體，是登錄名在資料庫中的映射，實在資料庫中執行操作和活動的行動者。

在某個A資料庫中創建對應sa登錄名的用戶lin：

use A

create user lin from login sa  [with default_schema=架構名] go

3.管理架構

架構是形成單個命名空間的資料庫實體的集合。

如果一個用戶沒有制定架構名，則預設為dbo架構。

1)創建架構的sql:   create schema 架構名

2)創建某個架構屬於某個用戶：create schema 架構名 authorization 用戶名

3)創建某個架構屬於某個用戶同時創建表: create schema 架構名 authorization 用戶名 create table 表名 (………)

4.資料庫角色(許可權)

資料庫角色是資料庫級別的主體，也是資料庫用戶的集合。

1）創建簡單的角色：create role 角色名

2）創建帶所有者的角色：create role 角色名 authorization 用戶名

3）在角色中添加成員：sp_addrolemember(‘lin’,…)

5.管理許可權（放權和釋權）

授權：grand語句

釋權：revoke語句

拒絕：deny語句 通過繼承他人許可權則無法收回許可權，便可以用deny拒絕。

1)  將創建資料庫的許可權授予某個lin用戶：grant create table to lin [with grant option 表示可以將此許可權轉給他人]

2)  將lin在某張表的查詢許可權回收：revoke select on student from lin go

6. Microsoft SQL Server2008內置的加密機制

對稱加密和非對稱加密兩種。密鑰分為公鑰和私鑰。

對稱加密：

 

三.管理資料庫

1.資料庫文件和文件組的特征：

資料庫文件分為：主資料庫.mdf、次資料庫.ndf、日誌文件.ldf;

1)主資料庫.mdf包含資料庫的啟動信息，並指向資料庫中的其他文件。用

戶數據和對象可存儲在此文件中，也可以存儲在次要數據文件中。

每個資料庫有一個主要數據文件。主要數據文件的建議文件擴展名是 .mdf。

2) 次要數據文件是可選的，由用戶定義並存儲用戶數據。通過將每個文件放在不同的磁碟驅動器上，次要文件可用於將數據分散到多個磁碟上。另外，如果資料庫超過了單個Windows 文件的最大大小，可以使用次要數據文件，這樣資料庫就能繼續增長。次要數據文件的建議文件擴展名是 .ndf。

3)日誌文件

文件組：是文件的集合。

註意事項：①一個文件或者文件組只能用於一個資料庫，不能用於多個數②一個文件只能是某一個文件組的成員，不能是多個文件組的成員③資料庫的數據信息和日誌信息不能放在同一個文件或文件組中，應該分開④日誌文件永遠不能是任何文件組的一部分。

  1. 管理單位：管理的最小物理單位是以頁為單位的，每一個頁的大小為8KB.extend 64kb。
  2. 定義資料庫： create database 資料庫名

修改資料庫： alter database資料庫名

  1. 擴大資料庫方法：

1）通過增加新的文件來擴大資料庫。

例如：alter database 資料庫名  add file ( name=‘‘，filename=’’,size=’’,filegrowth=’’)

2）通過擴大資料庫文件的大小來擴大資料庫

例如：alter database 資料庫名 modify file(name=’’, size=’新值’)

6.三種主要收縮資料庫方法：

   1）設置資料庫為自動收縮；auto_shrink

   2)收縮整個資料庫的大小: dbcc shrinkdatabase

   3)收縮制定的數據文件: dbcc shrinkfile

7.管理資料庫快照:

概念: 資料庫某一瞬時的狀態.

1)創建資料庫快照的語法:   create database 快照名稱   on(name=’’,filename=’’, as snapshot of 源資料庫名稱)

2)刪除快照: drop database  快照名稱

8.RAID技術—獨立磁碟冗餘陣列：

RAID是一個磁碟系統，可以將多個磁碟驅動器合成一個磁碟陣列，以提供高性能、高可靠性及低成本。

1)  容錯等級: RAID0（數據並行）、 RAID1(設備鏡像)、 RAID5(奇偶信息並行存儲).

 

四．T-SQL語言

1.T-SQL語言的4個特點:  一體化（定義、操縱、控制、事務管理語言與一體）、兩種使用方式(互動式和嵌入式到高級語言中)、非過程化語言（告訴乾什麼就行）、類似人的語言，容易理解和掌握。

2.T-SQL語言的5個特點：數據定義語言、數據操縱語言、數據控制語言、事務管理語言和附加的語言元素。

3.重點說一下附加語言元素：

1）標識符格式規則:  規則一，第一個字元①Unicode標准定義的字母a-z、A-Z②下劃線_、符號@、數字元號#； 以一個符號@開頭的標識符表示局部變數，以兩個符號@@開頭表示內置的某些函數。以#開頭表示臨時表或臨時存儲過程，##開頭標識符表示全局臨時對象。

規則二：①跟第一字元規則一樣，多了數字。

規則三：不能是保留字；

規則四：不允許嵌入空格和其他特殊字元。

分割標識符：“”、[]

4.變數和常量：

變數以@開頭 定義變數如: declare @name varchar

用set為變數設置值

對於常量需要使用’’來使用。

  1. 控制流語言:

Begin…end 、break、goto、continue、if…else、 while、return、waitfor（懸掛起批處理、存儲過程、事務的執行）。

  1. 錯誤的捕捉語言:

Try…catchj構造和@@ERROR函數、ERROR_NUMBER(返回錯誤號)。

  1. 數據類型：

 

 

  1. 聚合函數和內置函數:

  SUM                                                             返回選取結果集所有值的和
   MAX                                                             返回選取結果集中所有值的最大值
   MIN                                                             返回選取結果集中所有值的最小值
   AVG                                                             返回選取結果集中所有值的平均值
   COUNT                                                          返回選取結果集中行的數目

  內置函數:

Convert                 Convert(varchar(10),123)結果返回“123”                   裝換數據類型
  DataLength              DataLength('12中國')結果返回6                           返回任何數據類型的位元組數。“中國”包含4個位元組，“12”包含兩個位元組，所以返回6.

 

日期和時間函數:

-時間函數

select  getDate() as currentTime;

select  GETUTCDATE() AS UTCtime;

select  DAY('2015-04-30 01:01:01');

SELECT  MONTH('2015-04-30')AS monthValue;

SELECT  YEAR('2015-04-30'),YEAR('1997-07-01');

 

SELECT DATENAME(year,'2015-04-30 01:01:01') AS yearValue,

       DATENAME(quarter,'2015-04-30 01:01:01') AS quaterValue,

       DATENAME(dayofyear,'2015-04-30 01:01:01') AS dayofyearValue,

       DATENAME(day,'2015-04-30 01:01:01') AS dayValue,

       DATENAME(week,'2015-04-30 01:01:01') AS weekValue,

       DATENAME(weekday,'2015-04-30 01:01:01') AS weekdayValue,

       DATENAME(hour,'2015-04-30 01:01:01') AS hourValue,

       DATENAME(minute,'2015-04-30 01:01:01') AS minuteValue,

       DATENAME(second,'2015-04-30 01:01:01') AS secondValue;

 

SELECT  DATEPART(year,'2015-04-30 01:01:01'),

        DATEPART(month,'2015-04-30 01:01:01'),

        DATEPART(dayofyear,'2015-04-30 01:01:01');

       

SELECT    DATEADD(year,1,'2015-04-30 01:01:01') AS yearAdd,

        DATEADD(month ,2, '2015-04-30 01:01:01') AS weekdayAdd,

        DATEADD(hour,2,'2015-04-30 01:01:01') AS hourAdd;

 

排名函數:

  • --創建測試數據表  
  • DECLARE @table TABLE  (姓名 VARCHAR(4),成績 INT)  
  • insert into @table  
  • SELECT '張三',129 UNION ALL  
  • SELECT '李四',137 UNION ALL  
  • SELECT '王二',137 UNION ALL  
  • SELECT '小明',126 UNION ALL  
  • SELECT '小六',126 UNION ALL  
  • SELECT '小白',125 UNION ALL  
  • SELECT '小黑',124 UNION ALL  
  • SELECT '西大',123 UNION ALL  
  • SELECT '小才',120 UNION ALL  
  • SELECT '師兄',120 UNION ALL  
  • SELECT '唐朝',99 UNION ALL  
  • SELECT '尼瑪',99  
  •     
  • --查看一下  
  • --SELECT * FROM @table  
  •    
  • SELECT 姓名,成績,  
  •   ROW_NUMBER()  OVER(ORDER  BY 成績 DESC) AS [ROW_NUMBER],  
  •   RANK()        OVER(ORDER  BY 成績 DESC) AS [RANK],  
  •   DENSE_RANK()  OVER(ORDER  BY 成績 DESC) AS [DENSE_RANK],  
  •   NTILE(6)      OVER(ORDER  BY 成績 DESC) AS [NTILE]  
  • FROM @table  

五．資料庫表

1.表的類型: 普通表、以分區表、臨時表、系統表。

2.創建表：create table 表名

3.計算列：使用persisted關鍵字，指定由該列保存計算得到的數據，因此該列是一個物理列，當該列所依賴的其他列中的數據發生變化時該列中的數據也會發生變化。

4.創建臨時表: #本地臨時表只能由創建著使用 、 ##全局臨時表，其生存期間可以為所有的用戶使用。

例如：create table ##student(…)

5.增加列和刪除列

Alert table 表名    add/drop  column_name 數據類型

6.修改該列的數據類型

Alter table table_name  alter COLUMN column_name new_type_name數據類型

7.創建和修改標識符列：

identity(seed, increment)

8.ROWGUIDCOL列

Uname unqiueidentityfier default NEWID() 這就一個ROWGUIDCOL列。

10.創建分區表步驟:

1).創建分區函數，指定如何分區。

2）創建分區方案，指定分區函數的分區在文件祖上的位置。

3）創建使用分區表方案的表。

例如：

創建分區函數：  create partition function saleAmountPF(INT)

                As rang left for values(10,100,1000,10000)

創建分區方案:   create partition schema saleAmountPS

                As partition saleAmountPF

                TO(saleFG1,saleFG2,…)

創建分區表:     create table salePT(

serolId int,  saleAmount int, saleDate datetime, salePerson varchar(32)  on saleAmountPF(saleamount)

)

六．操縱數據

 1.使用INSERT語句插入數據

Insert into 表名（欄位列…） values(值…)

如果是unicode數據，應該在字元數據的引號前使用N字元。

字元數據需要使用 引號 引起來，數字數據可以直接插入。

提供空值：NULL、default。

使用default values向表中提供一行全部是預設值。

使用bulk insert語句按照用戶指定的格式批量的插入數據。

2.使用update語句更新數據

Update 表名 set column_name=expression,….

Where 查詢條件

 

3.使用delete語句刪除數據

Delete from table_name where 刪除條件

delete操作只是刪除表中的數據，並沒有破壞表的結構。被刪除的數據會被記錄在日誌中。

Truncate table語句刪除表中的數據，系統會立即釋放表中的數據和索引所占的空間，並且不把這種數據的變化記錄在日中。

4.改變列標題 = 和 as

5.order by進行排序：可以使用多個列進行排序，如果第一個列的值相同，那麼就按照第二個列排序，每一個列都可以指定升序或降序。

Top：返回指定數量的數據行。用在分頁。

6.like進行模糊匹配 % _ [] [^]

7.Compute字句：  當使用聚合函數得到的結果集全是聚合值，沒有明細值，解決方法就是是喲哦那個compute字句。

兩種形式：一種帶by字句；一種不帶by字句。

例如：  select … from 表名  order by 排序欄位 compute sum(欄位)

select … from 表名  order by 排序欄位 compute sum(欄位) by 欄位

8.分組技術:group by子句

分為無條件的分組：

Select 欄位…from 表名  where 條件 group by 列名

有條件的分組:

Select 欄位…from 表名  where 條件 group by 列名 having 分組篩選條件

ROLLUP得到各組的單項組合和CUBE關鍵字得到各組之間的任意組合。

 

9.連接技術：交叉連接即笛卡兒積 cross join、內連接inner join 用on指定連接條件按、左外連接left outer join、有外連接right outer join、完全外連接full outer join

10.子查詢技術：where、group by、having

七．索引

1.索引的類型和特點

建立索引的優點：
1）.大大加快數據的檢索速度;
2）.創建唯一性索引,保證資料庫表中每一行數據的唯一性;
3）.加速表和表之間的連接;
4）.在使用分組和排序子句進行數據檢索時,可以顯著減少查詢中分組和排序的時間.
索引類型：
根據資料庫的功能,可以在資料庫設計器中創建四種索引：唯一索引、非唯一索引、主鍵索引和聚集索引.儘管唯一索引有助於定位信息,但為獲得最佳性能結果,建議改用主鍵或唯一約束.
唯一索引：
唯 一索引是不允許其中任何兩行具有相同索引值的索引.當現有數據中存在重覆的鍵值時,大多數資料庫不允許將新創建的唯一索引與表一起保存.資料庫還可能防止 添加將在表中創建重覆鍵值的新數據.例如,如果在 employee 表中職員的姓 (lname) 上創建了唯一索引,則任何兩個員工都不能同姓.
非唯一索引：
非唯一索引是相對唯一索引,允許其中任何兩行具有相同索引值的索引.當現有數據中存在重覆的鍵值時,資料庫是允許將新創建的索引與表一起保存.這時資料庫不能防止添加將在表中創建重覆鍵值的新數據.
主鍵索引：
資料庫表經常有一列或列組合,其值唯一標識表中的每一行.該列稱為表的主鍵.在資料庫關係圖中為表定義主鍵將自動創建主鍵索引,主鍵索引是唯一索引的特定類型.該索引要求主鍵中的每個值都唯一.當在查詢中使用主鍵索引時,它還允許對數據的快速訪問.
聚集索引（也叫聚簇索引）：
在聚集索引中,表中行的物理順序與鍵值的邏輯（索引）順序相同.一個表只能包含一個聚集索引.如果某索引不是聚集索引,則表中行的物理順序與鍵值的邏輯順序不匹配.與非聚集索引相比,聚集索引通常提供更快的數據訪問速度.

2.創建索引：create index語句

Create [unique唯一] [clustered聚集 | nonclustered非聚集] index index_name on table_or_view_name (列名)[include (列…)包含性]

例如: create unique clustered index index_xxx on tabl_xxx(…)

3.維護索引：dbccshow_statistics命令和圖形化工具。查看索引碎片信息:sys.dm_db_index_physical_stats系統函數和使用圖形化工具。

未完待續。。。。。。明天更新。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 仿拉手團購App5--登錄界面
    activity_login.xml 快速登錄(獲得驗證碼和登錄） 賬號登錄（登錄和切換密碼明文) ...
  • 仿拉手團購App4--我的模塊
    fragment_me.xml MeFragment.java ...
  • 又是一個MVP+RxJava+Retrofit的乾貨集中營
    今天想要與大家一起分享的是近一個星期內開發的一個新app——乾貨集中營客戶端，其實網上已經有許多類似的項目，代碼家也在他的乾貨集中營中推薦了幾款優秀的作品，我也借鑒了其中的一些，自己開發出一款乾貨集中營客戶端，目前項目已經發佈到github，如果你想瞭解整個工程的具體內容，那麼你可以fork或者cl... ...
  • 【開發時，應註意事項】 vendor tools 無法 work 時，怎麼辦？
    遇到 vendor tools 無法 work 時， 最好的方法直接請 vendor 來， 為什麼呢？ 因為 tool 可能 有版本的問題， 譬如： vendor tool A tool 在 build B 版本的 code 時，會發生 error ，需要打開某幾個 config。 vendor t ...
  • MySQL:校對規則
    校對規則：在當前編碼下，字元之間的比較順序。（cs大小寫敏感，ci大小寫不敏感，bin二進位編碼比較） 1. 查看當前校對規則 show collation; 每個字元集都支持不定數量的校對規則,下圖為查看結果的部分截圖： tip : 以big5_chinese_ci為例，其中big5表示字元集，c ...
  • 免安裝Oracle配置（instantclient_11_2）
    首先下載instantclient，可自行搜索引擎搜索下載。 下載完成解壓後 找到解壓的文件目錄下的network\admin路徑下的tnsnames.ora 文件 比如我解壓在c盤根目錄，那麼在C:\instantclient_11_2\network\admin在此路徑中找到tnsnames.o ...
  • VMWare linux安裝mysql 5.7.13
    1、基礎環境說明 虛擬機：VMWare 操作系統：linux 資料庫版本：mysql 5.7.13 社區版（別問為什麼不裝企業版，因為企業版要錢） 背景：虛擬機可以連上外網 2、摸底 查看linux上是否已經安裝了mysql ① 啟動服務 service mysqld start 沒有安裝，則提示 ...
  • .Net程式員學用Oracle系列(27)：PLSQL 之游標、異常和事務
    " 1、游標 " "1.1、游標屬性" "1.2、隱式游標" "1.3、游標處理及案例" " 2、異常 " "2.1、異常類別" "2.2、異常函數" "2.3、異常處理及案例" " 3、事務 " "3.1、開始事務、結束事務" "3.2、自治事務" "3.3、事務處理及案例" " 4、總結 " 1 ...
一周排行
    -Advertisement-
    Play Games
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類