sqlserver提高篇

標籤：ret   符號   注意   schema   照相   art   構造   uname   建立資料庫   

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.Database Engine Tuning Advisor：

這個工具可以協助使用者分析工作負載、提出建立高效率索引的建議等功能。他就是一個最佳化大師，儘可能的提高系統效率。

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個特點：資料定義語言 (Data Definition Language)、資料操縱語言、資料控制語言、交易管理語言和附加的語言元素。

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‘);

 

SELECTDATENAME(year,‘2015-04-30 01:01:01‘) AS yearValue,

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

       DATENAME(dayofyear,‘2015-04-3001:01:01‘) AS dayofyearValue,

       DATENAME(day,‘2015-04-30 01:01:01‘) ASdayValue,

       DATENAME(week,‘2015-04-30 01:01:01‘) ASweekValue,

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

       DATENAME(hour,‘2015-04-30 01:01:01‘) AShourValue,

       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-3001:01:01‘);

       

SELECT    DATEADD(year,1,‘2015-04-30 01:01:01‘) ASyearAdd,

        DATEADD(month ,2, ‘2015-04-3001: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 unqiueidentityfierdefault NEWID() 這就一個ROWGUIDCOL列。

10.建立分區表步驟:

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

2）建立資料分割配置，指定分區函數的分區在檔案祖上的位置。

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

例如：

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

                As rang left forvalues(10,100,1000,10000)

建立資料分割配置:   create partition schema saleAmountPS

                As partition saleAmountPF

                TO(saleFG1,saleFG2,…)

建立分區表:     create table salePT(

serolId int,  saleAmountint, 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 排序欄位 computesum(欄位)

select… from 表名  order by 排序欄位 computesum(欄位) 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 uniqueclustered index index_xxx on tabl_xxx(…)

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

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

sqlserver提高篇