1、SQL Server 2005數(shù)據(jù)庫(kù)管理與開發(fā)教程,第3章 創(chuàng)建和管理數(shù)據(jù)庫(kù),本章主要介紹使用Transact-SQL語(yǔ)句和使用管理控制器創(chuàng)建數(shù)據(jù)庫(kù)、修改數(shù)據(jù)庫(kù)和刪除數(shù)據(jù)庫(kù)的過程。 通過本章的學(xué)習(xí)，可以熟悉SQL Server 2005數(shù)據(jù)庫(kù)的組成元素，并能夠掌握創(chuàng)建和管理數(shù)據(jù)庫(kù)的方法，可以調(diào)用sp_helpdb存儲(chǔ)過程或使用管理控制器查看數(shù)據(jù)庫(kù)參數(shù)等。,3.1認(rèn)識(shí)數(shù)據(jù)庫(kù),3.1.1數(shù)據(jù)庫(kù)基本概念 數(shù)據(jù)庫(kù)（Data Base）是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)，是存儲(chǔ)在一起的相關(guān)數(shù)據(jù)的集合。,其優(yōu)點(diǎn)主要體現(xiàn)在以下幾方面： （1）減少數(shù)據(jù)的冗余度，節(jié)省數(shù)據(jù)的存儲(chǔ)空間。 （2）具有較高的數(shù)

2、據(jù)獨(dú)立性和易擴(kuò)充性。 （3）實(shí)現(xiàn)數(shù)據(jù)資源的充分共享。,下面介紹一下與數(shù)據(jù)庫(kù)相關(guān)的幾個(gè)概念。 （1）數(shù)據(jù)庫(kù)系統(tǒng) （2）數(shù)據(jù)庫(kù)管理系統(tǒng), 數(shù)據(jù)存取的物理構(gòu)建 數(shù)據(jù)操縱功能 數(shù)據(jù)定義功能 數(shù)據(jù)庫(kù)的運(yùn)行管理 數(shù)據(jù)庫(kù)的建立和維護(hù)功能,（3）關(guān)系數(shù)據(jù)庫(kù), 關(guān)系數(shù)據(jù)結(jié)構(gòu) 關(guān)系操作集合 完整性約束,3.1.2數(shù)據(jù)庫(kù)常用對(duì)象,（1）表 （2）字段 （3）索引：一個(gè)單獨(dú)、物理的數(shù)據(jù)庫(kù)結(jié)構(gòu)。 （4）視圖：一個(gè)或多個(gè)表中導(dǎo)出的表（虛表），是用戶查看數(shù)據(jù)表中數(shù)據(jù)的一種方式。 （5）存儲(chǔ)過程：一組為了完成特定功能的SQL語(yǔ)句集合，經(jīng)編譯后以名稱的形式存儲(chǔ)在SQL Server服務(wù)器端的數(shù)據(jù)庫(kù)中，由用戶通過指定存儲(chǔ)過程的方

3、式來執(zhí)行。,3.1.3數(shù)據(jù)庫(kù)組成,（1）文件 主要數(shù)據(jù)文件 ：存放數(shù)據(jù)和數(shù)據(jù)庫(kù)的初始化信息。每個(gè)數(shù)據(jù)庫(kù)有且只有一個(gè)主數(shù)據(jù)文件，默認(rèn)擴(kuò)展名為.mdf。 次要數(shù)據(jù)文件：存放除主要數(shù)據(jù)文件之外的所有數(shù)據(jù)文件，可以有0至多個(gè)，默認(rèn)擴(kuò)展名為.ndf。 事務(wù)日志文件 ：存放用于恢復(fù)數(shù)據(jù)庫(kù)的所有日志信息。一個(gè)或多個(gè)，默認(rèn)擴(kuò)展名為.ldf,（2）文件組, 主文件組 ：包含主要數(shù)據(jù)文件和任何沒有明確指派給其他文件組的文件。系統(tǒng)表的所有頁(yè)都分配在主文件組中。 用戶定義文件組 ：主要是在CREATE DATABASE或ALTER DATABASE語(yǔ)句中，使用FILEGROUP關(guān)鍵字指定的文件組。 每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)

4、文件組作為默認(rèn)文件組運(yùn)行。沒有指定情況下，主文件組就為默認(rèn)文件組。 優(yōu)點(diǎn)： 系統(tǒng)管理員進(jìn)行備份時(shí)，可以備份或恢復(fù)個(gè)別的文件或文件組，不用涉及整個(gè)數(shù)據(jù)庫(kù)。,對(duì)文件進(jìn)行分組時(shí)，一定要遵循文件和文件組的設(shè)計(jì)規(guī)則。 文件只能是一個(gè)文件組的成員。 文件或文件組不能由一個(gè)以上的數(shù)據(jù)庫(kù)使用。 數(shù)據(jù)和事務(wù)日志信息不能屬于同一文件或文件組。 日志文件不能作為文件組的一部分。 日志空間與數(shù)據(jù)空間分開管理。,3.1.4系統(tǒng)數(shù)據(jù)庫(kù),（1）Master數(shù)據(jù)庫(kù)：master 數(shù)據(jù)庫(kù)記錄SQL Server 2005實(shí)例的所有系統(tǒng)級(jí)信息。 master數(shù)據(jù)庫(kù)還記錄所有其他數(shù)據(jù)庫(kù)是否存在以及這些數(shù)據(jù)庫(kù)文件的位置。另外，mas

5、ter還記錄了SQL Server的初始化信息。 （2）Tempdb數(shù)據(jù)庫(kù)：tempdb數(shù)據(jù)庫(kù)是連接到SQL Server 2005實(shí)例的所有用戶都可用的全局資源，它保存所有臨時(shí)表和臨時(shí)存儲(chǔ)過程。另外，它還用來滿足所有其他臨時(shí)存儲(chǔ)要求。,（3）Model數(shù)據(jù)庫(kù)：model數(shù)據(jù)庫(kù)用作SQL Server 2005實(shí)例上創(chuàng)建的所有數(shù)據(jù)庫(kù)的模板。 如果修改model數(shù)據(jù)庫(kù)，之后創(chuàng)建的所有數(shù)據(jù)庫(kù)都將繼承這些修改。 （4）Msdb數(shù)據(jù)庫(kù)：用于代理計(jì)劃警報(bào)和作業(yè)。 （5）Mssqlsystemresource（資源）系統(tǒng)數(shù)據(jù)庫(kù)，只讀數(shù)據(jù)庫(kù)，包含SQL Server中的所有系統(tǒng)對(duì)象，不包含用戶數(shù)據(jù)或元數(shù)據(jù)。

6、物理文件名為Mssqlsystemresource.mdf。 不能移動(dòng)或重命名該數(shù)據(jù)庫(kù)的數(shù)據(jù)文件，否則SQL Server無(wú)法啟動(dòng)。,3.2創(chuàng)建數(shù)據(jù)庫(kù),T-SQL用于創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)句是CREATE DATABASE。 語(yǔ)法格式如下：,CREATE DATABASE DATABASE_NAME ON PRIMARY ,n ,n LOG ON,n COLLATE collation_name FOR LOAD（RESTORE)|FOR ATTACH :=,(NAME=logical_file_name, FILENAME=os_file_name , SIZE=size KB|MB|GB|TB ,

7、 MAXSIZE=max_size KB|MB|GB|TB | UNLIMITED , FILEGROWTH =growth_increment KB|MB|GB|TB|% ),n := FILEGROUP filegroup_name,n,其中各參數(shù)的含義如下： database_name: 新數(shù)據(jù)庫(kù)的名稱。數(shù)據(jù)庫(kù)名稱在SQL Server 2005的實(shí)例中必須唯一，并且必須符合標(biāo)識(shí)符規(guī)則。要求不能超過128個(gè)字符，實(shí)際能指定的字符為123. ON:指明數(shù)據(jù)庫(kù)文件和文件組的明確含義 PRIMARY： 指定主數(shù)據(jù)文件或主文件組。主文件組中的第一個(gè)文件被認(rèn)為是主文件。沒有該項(xiàng)，則命令中列出的第一

8、個(gè)文件是主文件。 Filespec:文件說明，用來說明文件的用途,LOG ON：指定日志文件的文件屬性，沒有該選項(xiàng)，可自動(dòng)產(chǎn)生與數(shù)據(jù)庫(kù)名相同的容量為數(shù)據(jù)庫(kù)文件1/4大小的事務(wù)日志文件 COLLATE：校驗(yàn)方式?？梢允荳indows也可以是SQL方式，缺省此項(xiàng)，則使用當(dāng)前的SQL Server校驗(yàn)方式 FOR LOAD(RESTORE)：為了和SQL Server 7.0以前版本兼容而設(shè)定的。 FOR ATTACH：用于附加已存在的數(shù)據(jù)庫(kù)文件到新的數(shù)據(jù)庫(kù)中，而不用重新創(chuàng)建數(shù)據(jù)庫(kù)文件。使用該選項(xiàng)必須指定主文件。 NAME=logical_file_name： 指定文件的邏輯名稱。當(dāng)使用FOR AT

9、TACH，就不用使用該選項(xiàng)。 FILENAME= os_file_name ： 指定操作系統(tǒng)（物理）文件名稱。,SIZE： 指定文件的初始大小。默認(rèn)為MB.SIZE中不能使用小數(shù)，最小值512KB，默認(rèn)1MB. MAXSIZE：指定文件可增大到的最大大小。 FILEGROWTH： 指定文件的自動(dòng)增量。默認(rèn)為MB，沒有此選項(xiàng)，默認(rèn)值為10%,每次擴(kuò)大的容量為64KB. ： 控制文件組屬性。,1使用默認(rèn)值創(chuàng)建數(shù)據(jù)庫(kù),用戶在使用CREATE DATABASE命令創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，除了指定數(shù)據(jù)庫(kù)名稱外，可以不用設(shè)定任何參數(shù)，所有參數(shù)均取默認(rèn)值。 所以新創(chuàng)建的數(shù)據(jù)庫(kù)是由Model模板數(shù)據(jù)庫(kù)復(fù)制過來的，其文件

10、大小與Model中的設(shè)置完全相同。 所創(chuàng)建的數(shù)據(jù)文件和日志均存放在SQL Server安裝路徑下。,例如，在“master”數(shù)據(jù)庫(kù)中，使用CREATE DATABASE命令創(chuàng)建一個(gè)名稱為“STU”的數(shù)據(jù)庫(kù)。 SQL代碼如下： CREATE DATABASE STU,【例3-1】 使用CREATE DATABASE命令創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)。,圖3.1創(chuàng)建一個(gè)名稱為“mrgwh”的數(shù)據(jù)庫(kù),2自定義選項(xiàng)創(chuàng)建數(shù)據(jù)庫(kù),自定義選項(xiàng)創(chuàng)建數(shù)據(jù)庫(kù)是比較常用的創(chuàng)建數(shù)據(jù)庫(kù)的一種方法。 創(chuàng)建的數(shù)據(jù)文件（后綴名為.mdf）存儲(chǔ)于SQL Server安裝路徑“D:Program FilesSql serverMSSQLData

11、”下，而日志則存放在相同目錄下的日志（后綴名為.ldb）中。,【例3-2】 自定義選項(xiàng)創(chuàng)建數(shù)據(jù)庫(kù)。,圖3.2自定義選項(xiàng)創(chuàng)建數(shù)據(jù)庫(kù),3.2.2使用管理控制器創(chuàng)建數(shù)據(jù)庫(kù),在SQL Server 2005中，通過SQL Server Management Studio可以創(chuàng)建數(shù)據(jù)庫(kù)，用于存儲(chǔ)數(shù)據(jù)及其他對(duì)象（如視圖、索引、存儲(chǔ)過程和觸發(fā)器等）。,圖3.3選擇新建數(shù)據(jù)庫(kù),圖3.4“常規(guī)”選項(xiàng)卡,3.3修改數(shù)據(jù)庫(kù),3.3.1使用Transact-SQL修改數(shù)據(jù)庫(kù) T-SQL中修改數(shù)據(jù)庫(kù)的命令為ALTER DATABASE。,語(yǔ)法格式如下： ALTER DATABASE DATABASE ADD FILE,

12、nTO FILEGROUP filegroup_name |ADD LOG FILE,n |REMOVE FILE logical_file_name |ADD FILEGROUP filegroup_name |REMOVE FILEGROUP filegroup_name,|MODIFY FILE |MODIFY NAME=new_dbname |MODIFY FILEGROUP filegroup_namefilegroup_property|NAME=new_filegroup_name |COLLATE ,參數(shù)說明： filegroup_property：文件組屬性，分為只讀READ

13、ONLY，可讀寫READWRITE和默認(rèn)DEFAULT。其中主文件組不能為只讀。默認(rèn)的功能是把指定文件組設(shè)置為默認(rèn)文件組，一個(gè)數(shù)據(jù)庫(kù)只能有一個(gè)默認(rèn)文件組。,例：將兩個(gè)數(shù)據(jù)文件和一個(gè)事務(wù)日志文件添加到test數(shù)據(jù)庫(kù)中。 ALTER DATABASE Test ADD FILE (NAME = Test1, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDatatest1.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB), (NAME = Test2, FILENAME=c:Program F

14、ilesMicrosoft SQL ServerMSSQLDatatest2.ndf, SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 1MB),GO ALTER DATABASE Test ADD LOG FILE ( NAME = testlog1, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDatatestlog1.ldf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) GO,1修改數(shù)據(jù)庫(kù)時(shí)修改數(shù)據(jù)庫(kù)大小,可以使用ALTER DATABASE命令修改數(shù)

15、據(jù)庫(kù)的大小。 【例3-3】 使用ALTER DATABASEMODIFY命令修改數(shù)據(jù)庫(kù)的大小。 注意：只能增大，不能減少,圖3.5修改數(shù)據(jù)庫(kù)的大小,2修改數(shù)據(jù)庫(kù)時(shí)縮小數(shù)據(jù)庫(kù),（1）可以調(diào)用T-SQL的命令DBCC SHRINKDATABASE縮小過于龐大的數(shù)據(jù)庫(kù)，以回收沒有使用的數(shù)據(jù)頁(yè)。該操作與備份與恢復(fù)不能同時(shí)進(jìn)行。,DBCC SHRINKDATABASE的語(yǔ)法格式如下： DBCC SHRINKDATABASE ( database_name|,target_percent ,NOTRUNCATE|TRUNCATEONLY ),參數(shù)說明： target_percen：縮減后剩下的自由空間。

16、NOTRUNCATE|TRUNCATEONLY：NOTRUNCATE表示被釋放的空間保留在數(shù)據(jù)庫(kù)文件的范圍中。 TRUNCATEONLY表示被釋放的空間被OS回收利用。使用該選項(xiàng)，忽略target_percen的限制。,【例3-4】 使用DBCC SHRINKDATABASE命令縮小數(shù)據(jù)庫(kù)的文件。,圖3.6縮小gwh數(shù)據(jù)庫(kù)的大小,（2）可以使用DBCC SHRINKFILE命令收縮相關(guān)數(shù)據(jù)庫(kù)的指定數(shù)據(jù)文件或日志文件大小。,DBCC SHRINKFILE的語(yǔ)法格式如下： DBCC SHRINKFILE (file_name , target_size | ,EMPTYFILE | NOTRUNC

17、ATE | TRUNCATEONLY ),參數(shù)說明： target_size：縮減到指定的長(zhǎng)度，以MB為單位。如果沒該選項(xiàng)，則盡可能 縮小。 EMPTYFILE：將指定文件上數(shù)據(jù)全部轉(zhuǎn)移到文件組內(nèi)其他文件上，以后的操作將不再向該文件增加數(shù)據(jù)。,【例3-5】 使用DBCC SHRINKFILE命令縮小joy文件。,圖3.7使用DBCC SHRINKFILE命令縮小joy文件,3修改數(shù)據(jù)庫(kù)時(shí)將數(shù)據(jù)庫(kù)更名,系統(tǒng)存儲(chǔ)過程sp_renamedb可以修改數(shù)據(jù)庫(kù)的名字。 語(yǔ)法格式如下： sp_renamedb dbname = old_name , newname = new_name,【例3-6】 將數(shù)據(jù)

18、庫(kù)名稱“mr”更名為“mrsoft”。,圖3.8將數(shù)據(jù)庫(kù)名稱“mr”更名為“mrsoft”,3.3.2使用管理控制器修改數(shù)據(jù)庫(kù),數(shù)據(jù)庫(kù)創(chuàng)建完成以后，用戶在使用過程中可以根據(jù)需要對(duì)其原始定義進(jìn)行修改。,修改的內(nèi)容主要包括以下幾項(xiàng)： （1）擴(kuò)展數(shù)據(jù)庫(kù)。 （2）收縮數(shù)據(jù)庫(kù)。 （3）添加和刪除文件（數(shù)據(jù)文件和事務(wù)日志文件）。 （4）創(chuàng)建文件組。 （5）創(chuàng)建默認(rèn)文件組。 （6）更改數(shù)據(jù)庫(kù)所有者。,圖3.9選擇數(shù)據(jù)庫(kù)屬性,圖3.10“文件”選項(xiàng)卡,圖3.11選擇數(shù)據(jù)庫(kù)所有者,圖3.12“查找對(duì)象”對(duì)話框,3.4刪除數(shù)據(jù)庫(kù),3.4.1使用Transact-SQL刪除數(shù)據(jù)庫(kù) DROP DATABASE命令可以

19、刪除一個(gè)或多個(gè)數(shù)據(jù)庫(kù)。 當(dāng)某一個(gè)數(shù)據(jù)庫(kù)被刪除后，這個(gè)數(shù)據(jù)庫(kù)的所有對(duì)象和數(shù)據(jù)都將被刪除，所有日志文件和數(shù)據(jù)文件也都將刪除，所占用的空間將會(huì)釋放給操作系統(tǒng)。,語(yǔ)法格式如下： DROP DATABASE database_name ,.n ,圖3.13刪除正在使用的數(shù)據(jù)庫(kù)，系統(tǒng)會(huì)報(bào)錯(cuò),1使用DROP DATABASE命令刪除一個(gè)數(shù) 據(jù)庫(kù),【例3-7】 使用DROP DATABASE命令刪除數(shù)據(jù)庫(kù)。,圖3.14刪除“teacher”數(shù)據(jù)庫(kù),2使用DROP DATABASE命令批量刪除數(shù)據(jù)庫(kù),DROP DATABASE 學(xué)生管理, 住房信息管理 DROP DATABASE student,stu,goods,shopping,【例3-8】 使用DROP DATABASE命令將“hello”、“every”和“person”這3個(gè)數(shù)據(jù)庫(kù)批量刪除。,圖3.15批量刪除數(shù)據(jù)庫(kù),3.4.2使用管理控制器刪除數(shù)據(jù)庫(kù),如果用戶不再需要某一數(shù)據(jù)庫(kù)時(shí)，只要滿足一定的條件即可將其刪除，刪除之后，相應(yīng)的數(shù)據(jù)庫(kù)文件及其數(shù)據(jù)都會(huì)被刪除，并且不可恢復(fù)。,刪除數(shù)據(jù)庫(kù)時(shí)