數據庫原理與應用實驗2(二版)2

1、數據庫原理與應用實驗教材實驗二 數據庫的創(chuàng)建與管理一、實驗目的及要求本實驗要求學生掌握能夠運用企業(yè)管理器熟練創(chuàng)建數據表，并對數據進行各種方式的訪問。主要要求有：l 掌握在企業(yè)管理器中創(chuàng)建數據庫l 掌握數據庫的SQL創(chuàng)建方式。l 掌握掌握CREATE、ALTER、DROP語句的使用。l 掌握數據庫分離、附加、收縮的SQL實現方式。l 學會使用系統(tǒng)存儲過程查看數據庫信息。二、實驗原理及背景知識（一）SQL Server 2005數據庫系統(tǒng)數據庫1、master數據庫Master數據庫存儲了SQL Server系統(tǒng)的所有系統(tǒng)級信息，包括實例范圍的元數據（例如登錄賬戶、端點、鏈接服務器等）、系統(tǒng)設置參

2、數、所有其它數據庫是否存在，以及這些數據庫文件的位置、SQL Server實例的初始化信息。使用該數據庫時，一旦系統(tǒng)級別的信息發(fā)生改變，就應該進行備份，不要在master數據庫中創(chuàng)建用戶對象。2、model數據庫該系統(tǒng)數據庫主要是保存實例上所有數據庫的模板，也就是說，在創(chuàng)建新數據庫時必須使用該系統(tǒng)數據庫的有關內容。每次創(chuàng)建新的數據庫，將通過復制model數據庫中的內容來創(chuàng)建數據庫，如果修改了model數據庫的內容，則之后創(chuàng)建的所有數據庫都將繼承這些修改，例如：可以設置權限或數據庫選項、添加表、函數和存儲過程等對象。3、msdb數據庫該系統(tǒng)數據庫用于SQL Server 2005代理服務，代理服

3、務主要用于DBA來自動執(zhí)行某些管理任務，例如在每天的晚上18:00自動備份數據庫等，凡是這些自動執(zhí)行的任務就需要代理服務的支持。如果不需要執(zhí)行這些代理服務功能，則該系統(tǒng)數據庫可有可無。4、tempdb數據庫該系統(tǒng)數據庫是連接到SQL Server 實例的所有用戶都可用的全局資源，它保存所有臨時表和臨時存儲過程，不能在該數據庫中保存永久數據。5、resource數據庫該系統(tǒng)數據庫是SQL Server 2005第五個“隱藏的”數據庫，是只讀數據庫，它包含了SQL Server 2005實例中的所有系統(tǒng)對象，如系統(tǒng)表和系統(tǒng)視圖等。SQL Server 系統(tǒng)對象（如sys.objects）在物理上持

4、續(xù)存在于resource數據庫中，但在邏輯上，他們出現在每個數據庫的sys架構中。該數據庫無法使用可以列出所有數據庫的一般SQL命令來看到它。Resource 數據庫的 ID 始終是 32767。若要確定 Resource 數據庫的版本號，請使用：SELECT SERVERPROPERTY('ResourceVersion');若要訪問系統(tǒng)對象的 SQL 定義，請使用 OBJECT_DEFINITION 函數：SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects');（二）SQL Server 2005數據庫的結構和組成

5、1、數據庫邏輯結構從邏輯角度，SQL Server 2005將數據庫組織成為各種數據庫對象，如數據表、視圖、索引、數據類型、存儲過程和觸發(fā)器等。這些數據庫對象是用戶使用數據庫的基本單位。在這些對象中，數據表是由行和列組成的二維表，用來存儲大量的數據，視圖是一種虛擬的數據表，它好像數據表的窗戶，索引是將數據表中若干列作為整個表的引導，就像圖書索引可加快查詢圖書速度一樣；數據類型是存儲在數據表中的各種數據的類型；存儲過程是用T-SQL語句編寫的一組操縱數據庫的語句集合。2、數據庫物理結構從物理角度看，SQL Server 2005數據庫以多種操作系統(tǒng)文件的形式存儲在計算機硬盤上，一般一個數據庫被組

6、織成數據文件和日志文件兩種類型的文件。在SQL Server 2005中，數據文件分為主數據文件和二級數據文件兩種形式，使用一組物理文件來映射數據庫，數據庫中所有數據和對象都存儲在如下的三種操作系統(tǒng)文件中。（1）主數據庫文件：該文件包含數據庫的啟動信息，用來存放數據，它是所有數據庫文件的起點（包含指向其他數據庫文件的指針）；每個數據庫都必須有而且僅有一個主數據文件；主數據文件的默認擴展名為*.mdf。（2）二級數據文件：也用來存放數據。一個數據庫中，可以沒有二級數據文件，也可以擁有多個，如果數據庫文件可以包含數據庫中所有數據，則數據庫就不需要二級數據文件，如果數據庫很大，主數據文件的容量超過系

7、統(tǒng)的限制，就需要設置一個活多個二級數據文件。二級數據文件的擴展名為*.ndf（3）日志文件：用來存放事務日志，記錄了用戶對數據庫的所有操作，因此，根據日志文件信息可以恢復一個數據庫。每個數據庫至少有一個日志文件，也可以擁有多個日志文件；日志文件的擴展名為*.ldf。3、文件組數據庫文件組（File Group）是數據庫中數據文件的邏輯集合，包含一個或多個數據文件，使用文件組可以實現將數據分布在多個磁盤上并在查詢過程中使用并行線程同時掃描多個磁盤，有效地提高數據庫的讀寫速度。如：假如一個文件組中的數據文件分布在不同的硬盤驅動器上，在創(chuàng)建數據表時，表中的數據便分布在不同的硬盤上，讀寫表時，可以通過

8、并行線程操作，大大提高數據的存取效率。數據庫文件組分為以下3種類型：（1）主文件組（Primary）：包含主數據文件和沒有被包含在其他文件組里的文件。數據庫的系統(tǒng)表都被包含在主文件組里。（2）自定義文件組（user_defined）：包含所有在使用Create database或Alter Database時用FileGroup關鍵字進行約束的文件。（3）默認文件組：包含所有在創(chuàng)建時沒有指定文件組的表、索引，以及text、ntext和image數據類型的數據。創(chuàng)建數據庫文件組一般遵循的原則為：（1）一個文件或文件組只能被一個數據庫使用。（2）一個文件只能屬于一個文件組。數據和事務日志不能共存于

9、同一個文件或文件組上。（3）日志文件不能屬于文件組（三）創(chuàng)建數據庫語法格式CREATE DATABASE database_name ON PRIMARY ( NAME = logical_file_name , FILENAME = 'os_file_name' , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,.n , < filegroup > ,.n LOG ON(NAME=logical_file_name,FILENAME=os_file_n

10、ame,SIZE=size,MAXSIZE=max_size|UNLIMTED,FILEGROWTH=grow_increment),n（四）修改數據庫語法格式ALTER DATABASE database ADD FILE < filespec > ,.n TO FILEGROUP filegroup_name | ADD LOG FILE < filespec > ,.n | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name |

11、 MODIFY FILE < filespec > | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name filegroup_property | NAME = new_filegroup_name | SET < optionspec > ,.n WITH < termination > | COLLATE < collation_name > （五）收縮數據庫語法格式 DBCC shrinkdatabase（database_name,target_percent） with

12、 no_infomsgs（六）刪除數據庫語法格式DROP DATABASE database_name三、實驗內容及步驟：基本實驗內容（一）企業(yè)管理管理數據庫1、建立一個名為stumanage的數據庫，其中主數據文件的邏輯名稱為stumanage_mdf，物理文件的名稱為d: datastumanage.mdf，大小為20MB，最大為50MB，以25%的速度增長，數據庫的日志文件為stumanage_log，物理文件的名稱為d: data stumanage.ldf，大小為3MB，最大為10MB，以1MB的速度增長。其操作步驟如下：（1）打開SQL Server Management Stud

13、io，右擊“數據庫”，如圖2-2-1。在彈出的快捷菜單中選擇“新建數據庫”， 彈出圖2-2-2。圖2-1-1 選擇“新建數據庫”圖2-2-2 “新建數據庫”的“常規(guī)”標簽頁（2）在圖2-2-2的“常規(guī)”標簽頁中，定義數據庫數據文件的邏輯文件名稱stumanage_mdf，在路徑下設置物理文件路徑為d: data，初始大小位置設置大小為20MB，打開自動增長對話框，如圖2-2-2所示，在該子對話框中設置文件自動增長方式為25%，文件最大為50MB，日志文件采用和數據文件相似的方法進行屬性設置。注意：物理文件名一經確定，便不能更改，且路徑d: data在本機上必須存在，否則，需要在d盤上建好dat

14、a文件夾后才能建立數據庫。2、在第二個磁盤上添加三個次數據文件：Data1_ndf、Data2_ndf 和 Data3_ndf，Data1_ndf的物理文件名稱為e:datadata1.ndf，Data2_ndf 和 Data3_ndf的物理文件名稱分別為d:datadata2.ndf和e:datadata3.ndf，三個數據文件的其余屬性自己設置。將Data1_ndf組成文件組fgroup1，將 Data2_ndf 和 Data3_ndf組成文件組fgroup2。其步驟如下：（1）在圖2-2-2中，選擇左側的“文件組”選項頁，如圖2-2-3，在該頁面上，點擊下方的“添加”按鈕，在名稱列輸入文

15、件組名稱fgroup1，用同樣的方法再添加一個文件組fgroup2。圖2-2-4 “新建數據庫”的“文件組”標簽頁（2）再次回到圖2-2-3的頁面，點擊下方的“添加”按鈕，在邏輯名稱列輸入data1_ndf，并進行屬性的設置，在“文件組”列打開文件組下拉框，如圖2-2-5，選擇fgroup1文件組，用同樣的方法，添加data2_ndf和data3_ndf數據文件，并將其添加到相應的文件組中。圖2-2-5 將數據文件添加到文件組注意：日志文件不屬于任何文件組，故日志文件沒有可選的文件組。3、修改數據庫。右擊“數據庫”，在出現的快捷菜單上選擇“刷新”，則所建立的數據庫stumanage將出現在左側

16、的數據庫列表中。右擊“stumanage”，如圖2-2-6，在出現的快捷菜單中選擇“屬性”，在如圖2-2-7的界面中可以修改、添加和刪除數據庫文件和文件組。 圖2-2-6 選擇“屬性” 圖2-2-7 修改數據庫選項頁注意：此時，文件路徑不能再進行修改，且對文件大小進行修改時，修改的文件大小要大于或等于修改前數據文件大小。4、重命名數據庫為pupil。在所建立的數據庫上點擊右鍵，選擇“重命名”，輸入新的數據庫名稱“pupil”，完成數據庫更名。注意：一般在數據庫創(chuàng)建好之后，不要更改數據庫名稱5、刪除所建立的數據庫pupil。在所建立的數據庫上點擊右鍵，選擇“刪除”，在出現的刪除對話框中，選擇“關

17、閉現有連接”，點擊確定，完成數據庫刪除。（二）SQL命令管理數據庫1、創(chuàng)建數據庫stumanage：建立一個名為stumanage的數據庫，其中主數據文件的邏輯名稱為stumanage_data，物理文件名稱為d:datastumanage_mdf，大小為20MB，最大為50MB，以25%的速度增長，數據庫的日志文件為stumanage_log,操作系統(tǒng)文件的名稱為d:datastumanage_ldf，大小為3MB，最大為10MB，以1MB的速度增長。2、修改數據庫stumanage（1）在該數據庫上添加一個文件組，文件組邏輯名為group17、刪除數據庫Drop database new_stumanage：高級實驗內容1、按照下面要求使用SQL命令創(chuàng)建一個數據庫。名稱：Test主要數據文件：邏輯名稱：Test_M_Data物理名稱：Test_MData初始大?。?M