第二篇實(shí)驗(yàn)內(nèi)容及安排-8-實(shí)驗(yàn)一數(shù)據(jù)庫的創(chuàng)建與管理一、實(shí)驗(yàn)?zāi)康募耙蟊緦?shí)驗(yàn)的主要目是學(xué)會(huì)運(yùn)用可視化的管理平臺(tái)進(jìn)行數(shù)據(jù)庫的創(chuàng)建和管理，并理解通過T-SQL命令進(jìn)行數(shù)據(jù)庫創(chuàng)建和管理的方式。基本要求包括：掌握在管理平臺(tái)中創(chuàng)建數(shù)據(jù)庫。掌握數(shù)據(jù)庫的SQL創(chuàng)建方式。了解數(shù)據(jù)庫的主要管理操作。了解使用系統(tǒng)存儲(chǔ)過程查看數(shù)據(jù)庫信息。二、實(shí)驗(yàn)原理及背景知識(shí)（一）SQLServer2005系統(tǒng)數(shù)據(jù)庫1、master數(shù)據(jù)庫master數(shù)據(jù)庫存儲(chǔ)了SQLServer系統(tǒng)的所有系統(tǒng)級(jí)信息，包括實(shí)例范圍的元數(shù)據(jù)（例如登錄賬戶、端點(diǎn)、鏈接服務(wù)器等）、系統(tǒng)設(shè)置參數(shù)、所有其它數(shù)據(jù)庫基本信息、這些數(shù)據(jù)庫文件的位置及SQLServer實(shí)例的初始化信息等。使用該數(shù)據(jù)庫時(shí)，一旦系統(tǒng)級(jí)別的信息發(fā)生改變，就應(yīng)該進(jìn)行備份，不要在master數(shù)據(jù)庫中創(chuàng)建用戶對(duì)象。2、model數(shù)據(jù)庫該系統(tǒng)數(shù)據(jù)庫主要是保存實(shí)例上所有數(shù)據(jù)庫的模板，也就是說，在創(chuàng)建新數(shù)據(jù)庫時(shí)必須使用該系統(tǒng)數(shù)據(jù)庫的有關(guān)內(nèi)容。每次創(chuàng)建新的數(shù)據(jù)庫，將通過復(fù)制model數(shù)據(jù)庫中的內(nèi)容來創(chuàng)建數(shù)據(jù)庫，如果修改了model數(shù)據(jù)庫的內(nèi)容，則之后創(chuàng)建的所有數(shù)據(jù)庫都將繼承這些修改，例如：可以設(shè)置權(quán)限或數(shù)據(jù)庫選項(xiàng)、添加表、函數(shù)和存儲(chǔ)過程等對(duì)象。3、msdb數(shù)據(jù)庫該系統(tǒng)數(shù)據(jù)庫用于SQLServer2005代理服務(wù)，代理服務(wù)主要用于DBA來自動(dòng)執(zhí)行某些管理任務(wù)，例如在每天的晚上18:00自動(dòng)備份數(shù)據(jù)庫等，凡是這些自動(dòng)執(zhí)行的任務(wù)就需要代理服務(wù)的支持。如果不需要執(zhí)行這些代理服務(wù)功能，則該系統(tǒng)數(shù)據(jù)庫可有可無。4、tempdb數(shù)據(jù)庫該系統(tǒng)數(shù)據(jù)庫是連接到SQLServer實(shí)例的所有用戶都可用的全局資源，它保存所有臨時(shí)表和臨時(shí)存儲(chǔ)過程，不能在該數(shù)據(jù)庫中保存永久數(shù)據(jù)。5、Resource數(shù)據(jù)庫該系統(tǒng)數(shù)據(jù)庫是SQLServer2005第五個(gè)“隱藏的”數(shù)據(jù)庫，是只讀數(shù)據(jù)庫，它包含了SQLServer2005實(shí)例中的所有系統(tǒng)對(duì)象，如系統(tǒng)表和系統(tǒng)視圖等。SQLServer系統(tǒng)對(duì)象（如sys.objects）在物理上持續(xù)存在于Resource數(shù)據(jù)庫中，但在邏輯上，它們出現(xiàn)在每個(gè)數(shù)據(jù)庫的sys架構(gòu)中。該數(shù)據(jù)庫無法使用可以列出所有數(shù)據(jù)庫的一般SQL命令來看到它。Resource數(shù)據(jù)庫的ID始終是32767。若要確定Resource數(shù)據(jù)庫的版本號(hào)，請(qǐng)使用：SELECTSERVERPROPERTY('ResourceVersion');若要訪問系統(tǒng)對(duì)象的SQL定義，請(qǐng)使用OBJECT_DEFINITION函數(shù)：SELECTOBJECT_DEFINITION(OBJECT_ID('sys.objects'));（二）SQLServer2005數(shù)據(jù)庫的結(jié)構(gòu)和組成1、數(shù)據(jù)庫邏輯結(jié)構(gòu)從邏輯角度，SQLServer2005將數(shù)據(jù)庫組織成為各種數(shù)據(jù)庫對(duì)象，如數(shù)據(jù)表、視圖、索引、數(shù)據(jù)類型、存儲(chǔ)過程和觸發(fā)器等。這些數(shù)據(jù)庫對(duì)象是用戶使用數(shù)據(jù)庫的基本單位。在這些對(duì)象中，數(shù)據(jù)表是由行和列組成的二維表，用來存儲(chǔ)大量的數(shù)據(jù)，視圖是一種虛擬的數(shù)據(jù)表，它好像數(shù)據(jù)表的窗戶，索引是將數(shù)據(jù)表中若干列作為整個(gè)表的引導(dǎo)，就像圖書索引可加快查詢圖書速度一樣；數(shù)據(jù)類型是存儲(chǔ)在數(shù)據(jù)表中的各種數(shù)據(jù)的類型；存儲(chǔ)過程是用T-SQL語句編寫的一組操縱數(shù)據(jù)庫的語句集合。2、數(shù)據(jù)庫物理結(jié)構(gòu)從物理角度看，SQLServer2005數(shù)據(jù)庫以多種操作系統(tǒng)文件的形式存儲(chǔ)在計(jì)算機(jī)硬盤上，一般一個(gè)數(shù)據(jù)庫被組織成數(shù)據(jù)文件和日志文件兩種類型的文件。在SQLServer2005中，數(shù)據(jù)文件分為主數(shù)據(jù)文件和二級(jí)數(shù)據(jù)文件兩種形式，使用一組物理文件來映射數(shù)據(jù)庫，數(shù)據(jù)庫中所有數(shù)據(jù)和對(duì)象都存儲(chǔ)在如下的三種操作系統(tǒng)文件中。（1）主數(shù)據(jù)庫文件：該文件包含數(shù)據(jù)庫的啟動(dòng)信息，用來存放數(shù)據(jù)，它是所有數(shù)據(jù)庫文件的起點(diǎn)（包含指向其他數(shù)據(jù)庫文件的指針）；每個(gè)數(shù)據(jù)庫都必須有而且僅有一個(gè)主數(shù)據(jù)文件；主數(shù)據(jù)文件的默認(rèn)擴(kuò)展名為*.mdf。（2）二級(jí)數(shù)據(jù)文件：也用來存放數(shù)據(jù)。一個(gè)數(shù)據(jù)庫中，可以沒有二級(jí)數(shù)據(jù)文件，也可以擁有多個(gè)，如果主數(shù)據(jù)庫文件可以包含數(shù)據(jù)庫中所有數(shù)據(jù)，則數(shù)據(jù)庫就不需要二級(jí)數(shù)據(jù)文件，如果數(shù)據(jù)庫很大，主數(shù)據(jù)文件的容量超過系統(tǒng)的限制，就需要設(shè)置一個(gè)或多個(gè)二級(jí)數(shù)據(jù)文件。二級(jí)數(shù)據(jù)文件的默認(rèn)擴(kuò)展名為*.ndf（3）日志文件：用來存放事務(wù)日志，記錄了用戶對(duì)數(shù)據(jù)庫的所有操作，因此，根據(jù)日志文件信息可以恢復(fù)一個(gè)數(shù)據(jù)庫。每個(gè)數(shù)據(jù)庫至少有一個(gè)日志文件，也可以擁有多個(gè)日志文件；日志文件的默認(rèn)擴(kuò)展名為*.ldf。3、文件組數(shù)據(jù)庫文件組（FileGroup）是數(shù)據(jù)庫中數(shù)據(jù)文件的邏輯集合，包含一個(gè)或多個(gè)數(shù)據(jù)文件，使用文件組可以實(shí)現(xiàn)將數(shù)據(jù)分布在多個(gè)磁盤上并在查詢過程中使用并行線程同時(shí)掃描多個(gè)磁盤，有效地提高數(shù)據(jù)庫的讀寫速度。如：假如一個(gè)文件組中的數(shù)據(jù)文件分布在不同的硬盤驅(qū)動(dòng)器上，在創(chuàng)建數(shù)據(jù)表時(shí)，表中的數(shù)據(jù)便分布在不同的硬盤上，讀寫表時(shí)，可以通過并行線程操作，大大提高數(shù)據(jù)的存取效率。數(shù)據(jù)庫文件組分為以下3種類型：（1）主文件組（Primary）：包含主數(shù)據(jù)文件和沒有被包含在其他文件組里的文件。數(shù)據(jù)庫的系統(tǒng)表都被包含在主文件組里。（2）自定義文件組（User_defined）：包含所有在使用CREATEDATABASE或ALTERDATABASE時(shí)用FILEGROUP關(guān)鍵字進(jìn)行約束的文件。（3）默認(rèn)文件組：包含所有在創(chuàng)建時(shí)沒有指定文件組的表、索引，以及text、ntext和image數(shù)據(jù)類型的數(shù)據(jù)。創(chuàng)建數(shù)據(jù)庫文件組一般遵循的原則為：（1）一個(gè)文件或文件組只能被一個(gè)數(shù)據(jù)庫使用。（2）一個(gè)文件只能屬于一個(gè)文件組。（3）日志文件不能屬于文件組（4）不僅只有文件屬于文件組，數(shù)據(jù)表也屬于某個(gè)文件組，數(shù)據(jù)表和文件的關(guān)系通過文件組進(jìn)行關(guān)聯(lián)。（三）創(chuàng)建數(shù)據(jù)庫語法格式CREATEDATABASEdatabase_nameON[PRIMARY]([NAME=logical_file_name,]FILENAME='os_file_name'[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])[,...n][,<filegroup>[,...n]]LOGON(NAME=logical_file_name,FILENAME=’os_file_name’[,SIZE=size][,MAXSIZE={max_size|UNLIMTED}][,FILEGROWTH=grow_increment])[,…n]（四）修改數(shù)據(jù)庫語法格式ALTERDATABASEdatabase{ADDFILE<filespec>[,...n][TOFILEGROUPfilegroup_name]|ADDLOGFILE<filespec>[,...n]|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE<filespec>|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}|SET<optionspec>[,...n][WITH<termination>]|COLLATE<collation_name>}（五）收縮數(shù)據(jù)庫語法格式DBCCshrinkdatabase(database_name[,target_percent])[withno_infomsgs]（六）刪除數(shù)據(jù)庫語法格式DROPDATABASEdatabase_name三、實(shí)驗(yàn)內(nèi)容及步驟（一）企業(yè)管理管理數(shù)據(jù)庫1、建立一個(gè)名為stumanage的數(shù)據(jù)庫，其中主數(shù)據(jù)文件的邏輯名稱為stumanage_mdf，物理文件的名稱為d:\data\stumanage.mdf，大小為20MB，最大為50MB，以25%的速度增長(zhǎng)，數(shù)據(jù)庫的日志文件為stumanage_log，物理文件的名稱為d:\data\stumanage.ldf，大小為3MB，最大為10MB，以1MB的速度增長(zhǎng)。其操作步驟如下：（1）打開SQLServerManagementStudio，右擊“數(shù)據(jù)庫”，如實(shí)驗(yàn)圖1-1。在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”，彈出實(shí)驗(yàn)圖1-2。實(shí)驗(yàn)圖1-1選擇“新建數(shù)據(jù)庫”實(shí)驗(yàn)圖1-2新建數(shù)據(jù)庫界面實(shí)驗(yàn)圖1-3“新建數(shù)據(jù)庫”的“常規(guī)”標(biāo)簽頁（2）在實(shí)驗(yàn)圖1-2的“常規(guī)”標(biāo)簽頁中，定義數(shù)據(jù)庫數(shù)據(jù)文件的邏輯文件名稱stumanage_mdf，在路徑下設(shè)置物理文件路徑為d:\data\，初始大小位置設(shè)置大小為20MB，打開自動(dòng)增長(zhǎng)對(duì)話框，如實(shí)驗(yàn)圖1-3所示，在該子對(duì)話框中設(shè)置文件自動(dòng)增長(zhǎng)方式為25%，文件最大為50MB，日志文件采用和數(shù)據(jù)文件相似的方法進(jìn)行屬性設(shè)置。如實(shí)驗(yàn)圖1-3所示。注意：物理文件名一經(jīng)確定，便不能更改，SQLServer可以創(chuàng)建文件，但不能創(chuàng)建路徑，因此路徑d:\data\在本機(jī)上必須存在，否則，需要在d盤創(chuàng)建data文件夾后才能建立數(shù)據(jù)庫2、在第二個(gè)磁盤（如果沒有第二個(gè)磁盤，也可選擇第二個(gè)分區(qū)）上添加三個(gè)次數(shù)據(jù)文件：data1.ndf、data2.ndf和data3.ndf，data1.ndf的物理文件名稱為e:\data\data1.ndf，data2.ndf和data3.ndf的物理文件名稱分別為d:\data\data2.ndf和e:\data\data3.ndf，三個(gè)數(shù)據(jù)文件的其余屬性自己設(shè)置。將data1.ndf歸屬于文件組fgroup1，將data2.ndf和data3.ndf歸屬于文件組fgroup2。其步驟如下：（1）在實(shí)驗(yàn)圖1-2中，選擇左側(cè)的“文件組”選項(xiàng)頁，如實(shí)驗(yàn)圖1-4，在該頁面上，點(diǎn)擊下方的“添加”按鈕，在名稱列輸入文件組名稱fgroup1，用同樣的方法再添加一個(gè)文件組fgroup2。實(shí)驗(yàn)圖1-4“新建數(shù)據(jù)庫”的“文件組”標(biāo)簽頁（2）再次回到實(shí)驗(yàn)圖1-3的頁面，點(diǎn)擊下方的“添加”按鈕，在邏輯名稱列輸入data1.ndf，并設(shè)置屬性，在“文件組”列打開文件組下拉框，如實(shí)驗(yàn)圖1-5，選擇fgroup1文件組，用同樣的方法，添加data2.ndf和data3.ndf數(shù)據(jù)文件，并將其添加到相應(yīng)的文件組中。實(shí)驗(yàn)圖1-5將數(shù)據(jù)文件添加到文件組注意：日志文件不屬于任何文件組，故日志文件沒有可選的文件組。3、修改數(shù)據(jù)庫。右擊實(shí)驗(yàn)圖1-1中左側(cè)樹形控件中“數(shù)據(jù)庫”選項(xiàng)，在出現(xiàn)的快捷菜單上選擇“刷新”，則所建立的數(shù)據(jù)庫stumanage將出現(xiàn)在左側(cè)的數(shù)據(jù)庫列表中，如實(shí)驗(yàn)圖1-6。右擊“stumanage”選項(xiàng)，在出現(xiàn)的快捷菜單中選擇“屬性”，在“屬性”對(duì)話框（如實(shí)驗(yàn)圖1-7）中可以修改、添加和刪除數(shù)據(jù)庫文件和文件組。實(shí)驗(yàn)圖1-6選擇“屬性”實(shí)驗(yàn)圖1-7修改數(shù)據(jù)庫選項(xiàng)頁注意：此時(shí)，不能再修改文件路徑，且對(duì)文件大小進(jìn)行修改時(shí)，修改的文件大小要大于或等于修改前數(shù)據(jù)文件大小。4、重命名數(shù)據(jù)庫為pupil。在所建立的數(shù)據(jù)庫上點(diǎn)擊右鍵，選擇“重命名”，輸入新的數(shù)據(jù)庫名稱“pupil”，完成數(shù)據(jù)庫更名。注意：一般在數(shù)據(jù)庫創(chuàng)建好之后，不要更改數(shù)據(jù)庫名稱5、刪除所建立的數(shù)據(jù)庫pupil。在所建立的數(shù)據(jù)庫上點(diǎn)擊右鍵，選擇“刪除”，在出現(xiàn)的刪除對(duì)話框中，選擇“關(guān)閉現(xiàn)有連接”，點(diǎn)擊確定，完成數(shù)據(jù)庫刪除。注意：完成對(duì)數(shù)據(jù)庫的修改后，需要及時(shí)備份master系統(tǒng)數(shù)據(jù)庫。（二）SQL命令管理數(shù)據(jù)庫1、創(chuàng)建數(shù)據(jù)庫stumanage：建立一個(gè)名為stumanage的數(shù)據(jù)庫，其中主數(shù)據(jù)文件的邏輯名稱為stumanage_data，物理文件名稱為d:\data\stumanage.mdf，大小為20MB，最大為50MB，以25%的速度增長(zhǎng)，數(shù)據(jù)庫的日志文件為stumanage_log,物理文件名稱為d:\data\stumanage.ldf，大小為3MB，最大為10MB，以1MB的速度增長(zhǎng)。2、修改數(shù)據(jù)庫stumanage（1）在該數(shù)據(jù)庫上添加一個(gè)文件組，文件組邏輯名為group1。（2）在文件組group1上添加一個(gè)二級(jí)數(shù)據(jù)文件，邏輯文件名稱為test1，物理文件名稱為d:\data\test1.ndf，文件大小為5MB。（3）在數(shù)據(jù)庫中添加一個(gè)日志文件，文件邏輯文件名為test1_log，物理文件名為d:\data\test1.ldf，文件大小5MB。（4）將test1文件改名為test2，大小為15MB，文件的最大容量為UNLIMITED（不限制容量）。（5）將文件組group1設(shè)定為默認(rèn)文件組（6）移除文件test2，由于group1為默認(rèn)文件組，其中必須至少存在一個(gè)數(shù)據(jù)文件，故此時(shí)不能將test2文件刪除，需要將文件組group1的默認(rèn)屬性轉(zhuǎn)移到別的文件組之后，才能將t