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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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