SQL-Server-2012數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用02

1、第2章 數(shù)據(jù)庫(kù)管理SQL Server 2012數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用目錄2.1 SQL Server數(shù)據(jù)庫(kù)的結(jié)構(gòu)2.2 系統(tǒng)數(shù)據(jù)庫(kù)2.3 使用SSMS操作數(shù)據(jù)庫(kù)2.4 使用T-SQL操作數(shù)據(jù)庫(kù)2.1 SQL Server數(shù)據(jù)庫(kù)的結(jié)構(gòu)從數(shù)據(jù)庫(kù)管理員的角度，SQL Server數(shù)據(jù)庫(kù)的物理表現(xiàn)形式是數(shù)據(jù)文件，即一個(gè)數(shù)據(jù)庫(kù)由一個(gè)或多個(gè)磁盤(pán)上的文件組成。這種物理表現(xiàn)只對(duì)數(shù)據(jù)庫(kù)管理員是可見(jiàn)的，對(duì)用戶(hù)是透明的，可稱(chēng)為物理數(shù)據(jù)庫(kù)。從數(shù)據(jù)庫(kù)用戶(hù)的角度，SQL Server數(shù)據(jù)庫(kù)是由存放數(shù)據(jù)的表和對(duì)這些數(shù)據(jù)進(jìn)行各類(lèi)操作的邏輯對(duì)象共同組成的一個(gè)集合。這種集合稱(chēng)為邏輯數(shù)據(jù)庫(kù)，組成邏輯數(shù)據(jù)庫(kù)的各種對(duì)象稱(chēng)為數(shù)據(jù)庫(kù)對(duì)象。2.

2、1 SQL Server數(shù)據(jù)庫(kù)的結(jié)構(gòu)數(shù)據(jù)庫(kù)文件分類(lèi)：1數(shù)據(jù)庫(kù)文件主數(shù)據(jù)文件，是數(shù)據(jù)庫(kù)的關(guān)鍵文件，包含數(shù)據(jù)庫(kù)的啟動(dòng)信息、數(shù)據(jù)庫(kù)對(duì)象、其他文件的位置信息以及數(shù)據(jù)等。每個(gè)數(shù)據(jù)庫(kù)必須有且僅有一個(gè)主數(shù)據(jù)文件，其擴(kuò)展名為.mdf。輔助數(shù)據(jù)文件，用于存儲(chǔ)未包含在主文件中的數(shù)據(jù)信息。使用輔助數(shù)據(jù)文件可以擴(kuò)展數(shù)據(jù)庫(kù)的存儲(chǔ)空間。若數(shù)據(jù)庫(kù)只有主數(shù)據(jù)文件來(lái)存儲(chǔ)數(shù)據(jù)，則主數(shù)據(jù)文件的最大容量將受到整個(gè)磁盤(pán)空間的限制；若采用了輔助數(shù)據(jù)庫(kù)文件，并將多個(gè)文件存放在不同的磁盤(pán)上，則數(shù)據(jù)庫(kù)的容量不再受一個(gè)磁盤(pán)空間的限制。每個(gè)數(shù)據(jù)庫(kù)可有0個(gè)或多個(gè)輔助數(shù)據(jù)文件，其擴(kuò)展名為.ndf。2.1 SQL Server數(shù)據(jù)庫(kù)的結(jié)構(gòu)事務(wù)日志文件，

3、用來(lái)記錄對(duì)數(shù)據(jù)庫(kù)的所有修改操作和執(zhí)行每次修改的事務(wù)，保存恢復(fù)數(shù)據(jù)庫(kù)所需的事務(wù)日志信息。SQL Server遵循先寫(xiě)日志再執(zhí)行數(shù)據(jù)庫(kù)修改操作（如INSERT、UPDATE、DELETE等SQL命令）的原則，一旦發(fā)生數(shù)據(jù)庫(kù)系統(tǒng)崩潰，數(shù)據(jù)庫(kù)管理員可以通過(guò)日志文件完成數(shù)據(jù)庫(kù)的修復(fù)與重建。每個(gè)數(shù)據(jù)庫(kù)有一個(gè)或多個(gè)事務(wù)日志文件，其擴(kuò)展名為.ldf。2.1 SQL Server數(shù)據(jù)庫(kù)的結(jié)構(gòu)2文件組為了擴(kuò)展存儲(chǔ)空間，在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)常將多個(gè)數(shù)據(jù)文件存放在不同的磁盤(pán)上，并把多個(gè)數(shù)據(jù)文件組成一個(gè)或多個(gè)文件組。創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象時(shí)可以指定它所在的文件組，但不能指定文件，這樣當(dāng)對(duì)數(shù)據(jù)庫(kù)對(duì)象進(jìn)行操作時(shí)，由數(shù)據(jù)庫(kù)對(duì)象找到它所在的

4、文件組，再由文件組找到組中的數(shù)據(jù)文件。數(shù)據(jù)庫(kù)根據(jù)組內(nèi)數(shù)據(jù)文件的大小，按比例寫(xiě)入組內(nèi)所有數(shù)據(jù)文件中，而不是將組內(nèi)第一個(gè)數(shù)據(jù)文件寫(xiě)滿(mǎn)后再寫(xiě)第二個(gè)、第三個(gè)這樣可以使多個(gè)磁盤(pán)同時(shí)并行工作，大大提高了讀寫(xiě)速度，又使組內(nèi)的數(shù)據(jù)文件同時(shí)寫(xiě)滿(mǎn)。2.1 SQL Server數(shù)據(jù)庫(kù)的結(jié)構(gòu)數(shù)據(jù)庫(kù)對(duì)象 表：SQL Server最主要的數(shù)據(jù)庫(kù)對(duì)象，是由行和列組成的二維表，作為存放和操作數(shù)據(jù)的一種邏輯結(jié)構(gòu)。視圖：從一個(gè)或多個(gè)基表中創(chuàng)建的虛擬表，數(shù)據(jù)庫(kù)中只存放視圖的定義，數(shù)據(jù)仍然存放在基表中。索引：提供加快檢索數(shù)據(jù)的方式，是對(duì)數(shù)據(jù)表某些列的數(shù)據(jù)進(jìn)行排序的一種結(jié)構(gòu)。2.1 SQL Server數(shù)據(jù)庫(kù)的結(jié)構(gòu)數(shù)據(jù)庫(kù)對(duì)象同義詞：在

5、架構(gòu)范圍內(nèi)為存在于本地或遠(yuǎn)程服務(wù)器上的其他數(shù)據(jù)庫(kù)對(duì)象提供備用名稱(chēng)的一種技術(shù)手段。存儲(chǔ)過(guò)程：一組經(jīng)過(guò)預(yù)編譯的SQL語(yǔ)句集合，用于完成特定功能。觸發(fā)器：能夠被某些操作激發(fā)并自動(dòng)觸發(fā)執(zhí)行的一種特殊的存儲(chǔ)過(guò)程。規(guī)則：用來(lái)限制表列數(shù)據(jù)范圍、保障數(shù)據(jù)完整性的一種手段。默認(rèn)值：在用戶(hù)沒(méi)有給出具體數(shù)據(jù)時(shí)，系統(tǒng)所自動(dòng)生成的數(shù)值。約束：用來(lái)保障數(shù)據(jù)的一致性與完整性的簡(jiǎn)便方法。2.2 系統(tǒng)數(shù)據(jù)庫(kù)系統(tǒng)數(shù)據(jù)庫(kù)SQL Server 2012包含5個(gè)系統(tǒng)數(shù)據(jù)庫(kù)，分別是：master、tempdb、model、masb和resource。其中，前4個(gè)數(shù)據(jù)庫(kù)是可見(jiàn)的，可以在SSMS中的【對(duì)象資源管理器】窗口（見(jiàn)圖2.1）【系統(tǒng)

6、數(shù)據(jù)庫(kù)】節(jié)點(diǎn)中看到；resource數(shù)據(jù)庫(kù)為隱藏?cái)?shù)據(jù)庫(kù)，它存在于sys框架中，無(wú)法顯示在SSMS【系統(tǒng)數(shù)據(jù)庫(kù)】節(jié)點(diǎn)中。2.2 系統(tǒng)數(shù)據(jù)庫(kù)用戶(hù)數(shù)據(jù)庫(kù)系統(tǒng)數(shù)據(jù)庫(kù)是在安裝SQL Server 2012時(shí)由安裝程序自動(dòng)創(chuàng)建的數(shù)據(jù)庫(kù)。系統(tǒng)數(shù)據(jù)庫(kù)存放著SQL Server運(yùn)行和管理其他數(shù)據(jù)庫(kù)的重要信息，是SQL Server 2012管理數(shù)據(jù)庫(kù)的依據(jù)。如果系統(tǒng)數(shù)據(jù)庫(kù)遭到破壞，SQL Server將不能正常運(yùn)行。2.3 使用SSMS操作數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)實(shí)質(zhì)上就是定義數(shù)據(jù)庫(kù)文件與設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)，包括確定數(shù)據(jù)庫(kù)的邏輯文件名與物理文件名，規(guī)劃數(shù)據(jù)庫(kù)文件的容量，指定文件的增長(zhǎng)模式，設(shè)計(jì)數(shù)據(jù)庫(kù)的排序規(guī)則，選

7、擇數(shù)據(jù)庫(kù)的字符集，設(shè)置數(shù)據(jù)庫(kù)文件的存放位置等。2.3 使用SSMS操作數(shù)據(jù)庫(kù)邏輯名稱(chēng)：數(shù)據(jù)庫(kù)的邏輯文件名。文件類(lèi)型：用來(lái)標(biāo)識(shí)創(chuàng)建的文件是存儲(chǔ)記錄（行數(shù)據(jù)）的數(shù)據(jù)文件，還是存儲(chǔ)日常事務(wù)行為的日志文件。文件組：用來(lái)標(biāo)識(shí)創(chuàng)建的數(shù)據(jù)庫(kù)所歸屬的文件組名稱(chēng)。文件組包含所有的系統(tǒng)表。一個(gè)數(shù)據(jù)庫(kù)文件只能存在于一個(gè)文件組中。日志文件不允許屬于任何文件組。初始大小：用來(lái)設(shè)置空數(shù)據(jù)庫(kù)文件的初始空間大小值（單位默認(rèn)為MB）。剛創(chuàng)建的數(shù)據(jù)庫(kù)只有系統(tǒng)表，而不存在用戶(hù)數(shù)據(jù)庫(kù)對(duì)象，因此初始文件大小可以比較小。以后隨著數(shù)據(jù)庫(kù)量增長(zhǎng)，可以自動(dòng)擴(kuò)展數(shù)據(jù)庫(kù)的空間大小。2.3 使用SSMS操作數(shù)據(jù)庫(kù) 自動(dòng)增長(zhǎng)：當(dāng)數(shù)據(jù)庫(kù)文件在超過(guò)其初始

8、空間大小時(shí)，該項(xiàng)可用以啟動(dòng)文件大小的自動(dòng)增長(zhǎng)功能，可設(shè)置具體的增長(zhǎng)方式。 路徑：數(shù)據(jù)庫(kù)物理文件所存放的位置。默認(rèn)情況下為SQL Server安裝目錄下的某個(gè)特定子目錄?？梢酝ㄟ^(guò)其右側(cè)的 按鈕來(lái)指定文件的存儲(chǔ)路徑。文件名：數(shù)據(jù)庫(kù)物理文件的名稱(chēng)。該名稱(chēng)一般由系統(tǒng)根據(jù)邏輯名稱(chēng)按內(nèi)部規(guī)則設(shè)定，也可以通過(guò)輸入改變默認(rèn)名稱(chēng)。2.3 使用SSMS操作數(shù)據(jù)庫(kù)【任務(wù)2.1】 創(chuàng)建一個(gè)名為Manage的數(shù)據(jù)庫(kù)，該數(shù)據(jù)庫(kù)包含一個(gè)主數(shù)據(jù)庫(kù)文件、一個(gè)事務(wù)日志文件，所有文件都存儲(chǔ)在C:Program FilesMicrosoft SQL Serverdata文件夾下。其中，主數(shù)據(jù)庫(kù)文件初始大小為5MB，按照10%的容量增

9、長(zhǎng)，大小無(wú)限制。日志文件初始大小為1MB，每次增長(zhǎng)1MB，文件最大容量為100MB。2.3 使用SSMS操作數(shù)據(jù)庫(kù)【任務(wù)2.2】 為Manage數(shù)據(jù)庫(kù)添加一個(gè)輔助數(shù)據(jù)文件Mange_DB1，將其初始大小設(shè)置為5，自動(dòng)增加10%，最大空間設(shè)置為20MB。2.3 使用SSMS操作數(shù)據(jù)庫(kù)1分離數(shù)據(jù)庫(kù)分離數(shù)據(jù)庫(kù)就是將數(shù)據(jù)庫(kù)從SQL Server實(shí)例中卸載，但組成該數(shù)據(jù)庫(kù)的數(shù)據(jù)文件和事務(wù)日志文件依然完好無(wú)損地保存在磁盤(pán)上。通過(guò)分離得到的數(shù)據(jù)庫(kù)，可以重新附加到SQL Server實(shí)例上。在對(duì)數(shù)據(jù)庫(kù)進(jìn)行分離之前，要確保沒(méi)有任何用戶(hù)登錄到該數(shù)據(jù)庫(kù)上。2.3 使用SSMS操作數(shù)據(jù)庫(kù)【任務(wù)2.3】 將創(chuàng)建好的Ma

10、nage的數(shù)據(jù)庫(kù)從服務(wù)器上進(jìn)行分離。2.3 使用SSMS操作數(shù)據(jù)庫(kù)【任務(wù)2.4】 將被分離的Manage的數(shù)據(jù)庫(kù)重新附加到SQL Server服務(wù)器上。2.3 使用SSMS操作數(shù)據(jù)庫(kù)【任務(wù)2.5】 刪除系統(tǒng)中的Manage數(shù)據(jù)庫(kù)。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)CREATE DATABASE語(yǔ)句用來(lái)創(chuàng)建指定的數(shù)據(jù)庫(kù)，該語(yǔ)句的基本語(yǔ)法格式如下：CREATE DATABASE數(shù)據(jù)庫(kù)名ON PRIMARY (NAME=數(shù)據(jù)文件的邏輯名稱(chēng),FILENAME= 數(shù)據(jù)文件的物理名稱(chēng) ,SIZE=數(shù)據(jù)文件的初始大小,MAXSIZE=數(shù)據(jù)文件的最大容量,FILEGROWTH=數(shù)據(jù)文件的增長(zhǎng)量) ,nLOG O

11、N(NAME=事務(wù)日志文件的邏輯名稱(chēng),FILENAME = 事務(wù)日志文件的物理名稱(chēng),SIZE=事務(wù)日志文件的初始大小,MAXSIZE=事務(wù)日志文件的最大容量 ,FILEGROWTH =事務(wù)日志文件的增長(zhǎng)量) ,nCOLLATE數(shù)據(jù)庫(kù)的排序方式FOR ATTACH | ATTACH_REBUILD_LOG;2.4 使用T-SQL操作數(shù)據(jù)庫(kù)語(yǔ)法說(shuō)明如下。（1）ON關(guān)鍵字表示數(shù)據(jù)庫(kù)是根據(jù)后面的參數(shù)來(lái)創(chuàng)建的，LOG ON子句用于指定該數(shù)據(jù)庫(kù)的事務(wù)日志文件。每個(gè)文件定義包含NAME、FILENAME、SIZE、MAXSIZE和FILEGROWTH五個(gè)參數(shù)，需用括號(hào)括起來(lái)，若存在多個(gè)文件，文件定義用逗號(hào)分

12、隔。（2）PRIMARY關(guān)鍵字指定將后面定義的數(shù)據(jù)文件加入主文件組中，也可加入用戶(hù)自創(chuàng)建的文件組中。（3）NAME關(guān)鍵字用來(lái)指定數(shù)據(jù)庫(kù)文件的邏輯名。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)（4）FILENAME關(guān)鍵字用來(lái)指定數(shù)據(jù)庫(kù)文件的物理名，需要指明文件路徑及帶后綴的全名，用單引號(hào)引起來(lái)。（5）SIZE關(guān)鍵字用于指定文件的初始大小，默認(rèn)單位為MB，也可定義為其他單位（如KB、GB或TB）。數(shù)據(jù)文件初始大小為3MB，日志文件初始大小為1MB。（6）MAXSIZE關(guān)鍵字用于指定文件的最大容量，單位與SIZE參數(shù)單位相同。若文件大小不受限制，表示文件可不斷增長(zhǎng)，直到磁盤(pán)空間完全被使用，此時(shí)可省略該參數(shù)，或

13、將參數(shù)值設(shè)置為UNLIMITED。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)（7）FILEGROWTH關(guān)鍵字用于指定數(shù)據(jù)庫(kù)文件的增加量，增量采用固定大小和按比例增長(zhǎng)兩種方式，可加MB等單位或%，默認(rèn)單位為MB。（8）COLLATE關(guān)鍵字引導(dǎo)的子句用來(lái)指定數(shù)據(jù)庫(kù)的默認(rèn)排序方式。COLLATE子句不出現(xiàn)時(shí)，數(shù)據(jù)庫(kù)將依照SQL Server的model系統(tǒng)數(shù)據(jù)庫(kù)的設(shè)置來(lái)定義數(shù)據(jù)庫(kù)的默認(rèn)排序方式。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)（9）FOR ATTACH | ATTACH_REBUILD_LOG子句用來(lái)指定以何種附加方式向新建數(shù)據(jù)庫(kù)中添加初始數(shù)據(jù)。FOR ATTACH選項(xiàng)將依據(jù)已有的數(shù)據(jù)文件來(lái)創(chuàng)建新的數(shù)據(jù)庫(kù)，此

14、時(shí)新建數(shù)據(jù)庫(kù)必須保證主文件已經(jīng)被指定。FOR ATTACH_REBUILD_LOG選項(xiàng)則依據(jù)日志文件的事務(wù)記錄來(lái)創(chuàng)建新的數(shù)據(jù)庫(kù)。（10）若省略所有的選項(xiàng)，則數(shù)據(jù)庫(kù)創(chuàng)建時(shí)會(huì)根據(jù)model系統(tǒng)數(shù)據(jù)庫(kù)的默認(rèn)設(shè)置來(lái)自動(dòng)設(shè)定各屬性參數(shù)。當(dāng)用戶(hù)不需對(duì)新建數(shù)據(jù)庫(kù)的各種特性進(jìn)行較多控制時(shí)，可以采用最簡(jiǎn)化的數(shù)據(jù)庫(kù)創(chuàng)建方式，即語(yǔ)句CREATE DATABASE數(shù)據(jù)庫(kù)名。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)【任務(wù)2.6】 創(chuàng)建一個(gè)名為Manage的數(shù)據(jù)庫(kù)，該數(shù)據(jù)庫(kù)包含一個(gè)主數(shù)據(jù)庫(kù)文件、一個(gè)事務(wù)日志文件，所有文件都存儲(chǔ)在D:data文件夾下。其中，主數(shù)據(jù)庫(kù)文件初始大小為5MB，按照10%的容量增長(zhǎng)，大小無(wú)限制；日志文件初

15、始大小為1MB，每次增長(zhǎng)1MB，文件最大容量為100MB。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)USE語(yǔ)句的基本語(yǔ)法格式如下。USE數(shù)據(jù)庫(kù)名;2.4 使用T-SQL操作數(shù)據(jù)庫(kù)【任務(wù)2.7】 將當(dāng)前數(shù)據(jù)庫(kù)切換為Manage數(shù)據(jù)庫(kù)。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)ALTER DATABASE語(yǔ)句的基本語(yǔ)法格式如下：ALTER DATABASE數(shù)據(jù)庫(kù)名 ADD FILE TO FILEGROUP文件組| ADD LOG FILE | REMOVE FILE邏輯文件名| ADD FILEGROUP文件組名| REMOVE FILEGROUP文件組名| MODIFY FILE | MODIFY FILEGRO

16、UP文件組名，文件組屬性| MODIFY NAME=數(shù)據(jù)庫(kù)新名稱(chēng)| SET數(shù)據(jù)庫(kù)功能選項(xiàng),n WITH終止符| COLLATE數(shù)據(jù)庫(kù)的排序方式;2.4 使用T-SQL操作數(shù)據(jù)庫(kù)語(yǔ)法說(shuō)明如下：（1）在上述語(yǔ)法格式中，“|”表示幾項(xiàng)中僅選一項(xiàng)。（2）ADD FILE與ADD LOG FILE子句分別用來(lái)向數(shù)據(jù)庫(kù)中添加數(shù)據(jù)文件與日志文件，文件的屬性定義見(jiàn)CREATE DATABASE。TO FILEGROUP子句指定將文件添加到哪個(gè)文件組，默認(rèn)為主文件組PRIMARY。（3）REMOVE FILE子句用于從數(shù)據(jù)庫(kù)中刪除一個(gè)數(shù)據(jù)文件。（4）ADD FILEGROUP和REMOVE FILEGROUP子

17、句分別指定添加和刪除一個(gè)數(shù)據(jù)庫(kù)文件組。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)（5）MODIFY FILE子句用于修改數(shù)據(jù)庫(kù)文件的初始大小、文件最大容量及文件增長(zhǎng)量等信息。注意：數(shù)據(jù)庫(kù)文件的物理文件名稱(chēng)不允許修改，因此FILENAME子句不允許出現(xiàn)在文件修改中。另外，在修改文件的“分配的空間”項(xiàng)時(shí)，所改動(dòng)的值必須大于現(xiàn)有的空間值。（6）MODIFY FILEGROUP子句用于修改文件組屬性。（7）MODIFY NAME子句用于改變數(shù)據(jù)庫(kù)的邏輯名稱(chēng)。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)注意：要對(duì)數(shù)據(jù)庫(kù)改名必須先將數(shù)據(jù)庫(kù)改為排他鎖鎖定狀態(tài)，即斷開(kāi)所有其他連接，以執(zhí)行該操作。（8）SET關(guān)鍵字引導(dǎo)的子句用來(lái)設(shè)置某些數(shù)據(jù)庫(kù)功能選項(xiàng)，這些選項(xiàng)將影響數(shù)據(jù)庫(kù)的某些特征或?qū)傩浴＃?）COLLATE關(guān)鍵字引導(dǎo)的子句用來(lái)為數(shù)據(jù)庫(kù)指定排序規(guī)則。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)【任務(wù)2.8】 編寫(xiě)T-SQL代碼，為Manage數(shù)據(jù)庫(kù)添加一個(gè)新文件組newfilegroup，并在其中添加一個(gè)輔助數(shù)據(jù)文件Manage_add.ndf，將其保存在與主數(shù)據(jù)文件相同的目錄下，初始大小3MB，最大值不受限制，每次增長(zhǎng)10MB。2.4 使用T-SQL操作數(shù)據(jù)庫(kù)T-SQL提供了DROP DATABASE命令來(lái)刪除數(shù)據(jù)庫(kù)，該語(yǔ)句的基本語(yǔ)法格式如下。DROP DATABASE數(shù)據(jù)庫(kù)名;2.4 使用T-SQL操作數(shù)據(jù)庫(kù)【任務(wù)2.9】