1、第第5 5章章創(chuàng)建和管理創(chuàng)建和管理SQLServer 2008SQLServer 2008數(shù)據(jù)庫數(shù)據(jù)庫主要內(nèi)容主要內(nèi)容l5.1 系統(tǒng)數(shù)據(jù)庫概述 l5.2 創(chuàng)建數(shù)據(jù)庫 l5.3 管理數(shù)據(jù)庫l本章小節(jié) SQL Server 2008 SQL Server 2008 數(shù)據(jù)庫概述數(shù)據(jù)庫概述 數(shù)據(jù)庫是SQL Server 存放數(shù)據(jù)和數(shù)據(jù)對象（如表、索引、視圖、存儲過程、觸發(fā)器）的容器，用戶使用數(shù)據(jù)庫管理系統(tǒng)提供的功能時，首先必須將自己的數(shù)據(jù)放置和保存到用戶數(shù)據(jù)庫中。 數(shù)據(jù)庫可以分為系統(tǒng)自帶數(shù)據(jù)庫系統(tǒng)自帶數(shù)據(jù)庫和用戶自有數(shù)據(jù)庫用戶自有數(shù)據(jù)庫。5.1 5.1 系統(tǒng)數(shù)據(jù)庫概述系統(tǒng)數(shù)據(jù)庫概述lmaster數(shù)據(jù)庫

2、lmsdb數(shù)據(jù)庫lmodel數(shù)據(jù)庫ltempdb數(shù)據(jù)庫lResource數(shù)據(jù)庫MasterMaster數(shù)據(jù)庫數(shù)據(jù)庫Master數(shù)據(jù)庫是“數(shù)據(jù)庫的數(shù)據(jù)庫”，Master數(shù)據(jù)庫記錄SQL Server系統(tǒng)的所有系統(tǒng)級信息。Master數(shù)據(jù)庫中包含以下重要信息l實例范圍的元數(shù)據(jù)（例如登錄賬戶）、端點、連接服務(wù)器和系統(tǒng)配置設(shè)置。l記錄SQL Server系統(tǒng)的所有系統(tǒng)級信息。lSQL Server的初始化信息。 MsdbMsdb數(shù)據(jù)庫數(shù)據(jù)庫l存儲計劃信息l存儲備份和恢復(fù)相關(guān)的信息，尤其是SQL Serve代理需要使用它來執(zhí)行安排工作和警報，記錄操作者等操作。 ModelModel數(shù)據(jù)庫數(shù)據(jù)庫l創(chuàng)建的所

3、有數(shù)據(jù)庫的模板。l當執(zhí)行CREATE DATABASE語句時，將通過復(fù)制 model 數(shù)據(jù)庫中的內(nèi)容來創(chuàng)建數(shù)據(jù)庫的第一部分，然后用空頁填充新數(shù)據(jù)庫的剩余部分。l如果修改model數(shù)據(jù)庫，之后創(chuàng)建的所有數(shù)據(jù)庫都將繼承這些修改。 TempdbTempdb數(shù)據(jù)庫數(shù)據(jù)庫 ltempdb 保存所有臨時表和臨時存儲過程。l每次啟動SQL Server時，都要重新創(chuàng)建tempdb。 ResourceResource數(shù)據(jù)庫數(shù)據(jù)庫lResource數(shù)據(jù)庫是只讀數(shù)據(jù)庫，它包含了SQL Server 2008中的所有系統(tǒng)對象。lResource數(shù)據(jù)庫是隱藏的，通常應(yīng)該由MicroSoft客戶服務(wù)專家來打開，用于查找

4、問題和進行客戶支持。 5.2 5.2 用戶自定義數(shù)據(jù)庫用戶自定義數(shù)據(jù)庫數(shù)據(jù)庫用戶使用SQL Server 2008存放數(shù)據(jù)，需要用戶創(chuàng)建數(shù)據(jù)庫。關(guān)于用戶數(shù)據(jù)庫，需要掌握以下內(nèi)容l數(shù)據(jù)庫文件 l使用Management StudioManagement Studio創(chuàng)建數(shù)據(jù)庫 l用SQL命令創(chuàng)建數(shù)據(jù)庫5.2.1 5.2.1 數(shù)據(jù)庫文件數(shù)據(jù)庫文件每個SQL Server 2008數(shù)據(jù)庫至少具有兩個操作系統(tǒng)文件：一個數(shù)據(jù)文件和一個日志文件。 l數(shù)據(jù)文件l日志文件數(shù)據(jù)文件數(shù)據(jù)文件l數(shù)據(jù)庫文件是存放數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)庫對象的文件。一個數(shù)據(jù)庫可以有一個或多個數(shù)據(jù)庫文件。l一個數(shù)據(jù)庫只能有一個主數(shù)據(jù)庫文件，擴

5、展名為mdf。 l其它數(shù)據(jù)庫文件被稱為次數(shù)據(jù)庫文件，擴展名為ndf。 日志文件日志文件 l保存用于恢復(fù)數(shù)據(jù)庫的日志信息。l每個數(shù)據(jù)庫必須至少有一個日志文件。也可以為多個l事務(wù)日志的建議文件擴展名是 .ldf。關(guān)于數(shù)據(jù)庫文件-1l每個數(shù)據(jù)庫文件有兩個名稱： l物理文件名（os_file_name）：是包括目錄路徑的物理文件名。如：D:DBFILECJGL.mdf l邏輯文件名（logical_file_name）：是在所有 Transact-SQL 語句中引用物理文件時所使用的名稱。如cjgl_datal文件大小l指定數(shù)據(jù)和日志文件的初始大小，或采用默認大小。l如果已經(jīng)超過了初始數(shù)據(jù)文件的大小并

6、且文件開始自動增長，則重新計算預(yù)期的數(shù)據(jù)庫大小最大值。關(guān)于數(shù)據(jù)庫文件-2l文件組l使用文件組可以隔離用戶和文件，使得用戶針對文件組來建立表和索引，而不是實際磁盤中的文件。當文件移動或修改時，由于用戶建立的表和索引是建立在文件組上的，并不依賴具體文件，這大大加強了可管理性.l使用文件組來管理文件可以使得同一文件組內(nèi)的不同文件分布在不同的硬盤中，極大的提高了IO性能.關(guān)于數(shù)據(jù)庫文件-2l文件組l文件組可以分為主文件組（Primary）和用戶自定義的文件組。l一個數(shù)據(jù)庫只能有主文件組（Primary），但可以有多個自定義文件組。l自定義文件組可在創(chuàng)建數(shù)據(jù)庫時定義。l【例5.5.1】在Master數(shù)據(jù)

7、庫下，執(zhí)行存儲過程sys.sp_helpfile，則得到結(jié)果如圖5.5.2所示，邏輯文件名在“name”列，物理文件名在“filename”列，最大容量在maxsize列，增長容量在growth列，文件組在“filegroup”列。查看數(shù)據(jù)庫文件信息查看數(shù)據(jù)庫文件信息5.2.2 5.2.2 使用使用Management StudioManagement Studio創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫 l在對象資源管理器中數(shù)據(jù)庫節(jié)點上右擊 , 在彈出菜單中單擊“新建數(shù)據(jù)庫”，則出現(xiàn)“新建數(shù)據(jù)庫”對話框。5.2.3 5.2.3 用用SQLSQL命令創(chuàng)建數(shù)據(jù)庫命令創(chuàng)建數(shù)據(jù)庫lCREATE DATABASE dat

8、abase_name l ON l PRIMARY ,.n l , ,.n l LOG ON ,.n l l COLLATE collation_name l WITH l l其參數(shù)說明如下。（1）database_name：新數(shù)據(jù)庫的名稱。（2）ON：指定數(shù)據(jù)庫文件或文件組的明確定義。（3）PRIMARY：指明主數(shù)據(jù)庫文件或主文件組。一個數(shù)據(jù)庫只能有一個主文件，如果沒有指定 PRIMARY，那么 CREATE DATABASE 語句中列出的第一個文件將成為主文件。（4） ：控制文件組屬性。其語法格式為： := FILEGROUP filegroup_name ，.n其中：控制文件屬性。其格式

9、如下： := ( NAME = logical_file_name ， FILENAME = os_file_name , SIZE = size KB | MB | GB | TB , MAXSIZE = max_size KB | MB | GB | TB | UNLIMITED , FILEGROWTH = growth_increment KB | MB | GB | TB | % ) ,.n 其中有邏輯文件名（NAME），物理文件名（FILENAME），初始大小（SIZE，默認單位為MB），可增大到的最大容量（MAXSIZE），自動增長（FILEGROWTH）。每個文件之間以逗號分隔

10、。 （5）LOG ON：明確指定存儲數(shù)據(jù)庫日志的磁盤文件（日志文件）。LOG ON 后跟以逗號分隔的用以定義日志文件的 項列表。如果沒有指定 LOG ON，將自動創(chuàng)建一個日志文件，其大小為該數(shù)據(jù)庫的所有數(shù)據(jù)文件大小總和的25% 或512 KB，取兩者之中的較大者。例5.2 創(chuàng)建未指定文件的數(shù)據(jù)庫創(chuàng)建未指定文件的數(shù)據(jù)庫USE MasterGOIF DB_ID (Ntest) IS NOT NULL DROP DATABASE testGOCREATE DATABASE test -創(chuàng)建數(shù)據(jù)庫GOSELECT name,size,size*1.0/128 AS Size in MBs-檢查數(shù)據(jù)庫文

11、件和大小FROM sys.master_files WHERE name = Ntestl【例5.5.3】創(chuàng)建指定文件的數(shù)據(jù)庫，數(shù)據(jù)文件、日志文件的初始尺寸，大小和增長幅度（單位都是MB）都已指定。CREATE DATABASE testON ( NAME = test_dat, FILENAME = E:sql server 2008 dbtestdat.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )LOG ON( NAME = test_log, FILENAME = E:sql server 2008 dbtestlog.ldf, SIZE

12、= 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB)l【例5.5.4】通過指定多個數(shù)據(jù)和事務(wù)日志文件創(chuàng)建數(shù)據(jù)庫test。該數(shù)據(jù)庫具有2個10MB的數(shù)據(jù)文件和2個10MB的事務(wù)日志文件。主文件是列表中的第一個文件，并使用PRIMARY關(guān)鍵字顯式指定。事務(wù)日志文件在LOG ON關(guān)鍵字后指定。請注意用于FILENAME選項中各文件的擴展名：.mdf 用于主數(shù)據(jù)文件，.ndf 用于輔助數(shù)據(jù)文件，.ldf 用于事務(wù)日志文件。CREATE DATABASE testCREATE DATABASE testononprimaryprimary(name = test_data,(

13、name = test_data,filename=E:sql server 2008 dbtest_dat.mdf,filename=E:sql server 2008 dbtest_dat.mdf,size = 10,size = 10,maxsize = 100,maxsize = 100,filegrowth = 5)filegrowth = 5), ,(name =test_data1,(name =test_data1,filename=E:sql server 2008 dbtest_dat1.ndf,filename=E:sql server 2008 dbtest_dat1.

14、ndf,size =10,size =10,maxsize =100,maxsize =100,filegrowth =10)filegrowth =10)log onlog on(name = test_log,(name = test_log,filename=E:sql server 2008 dbtest_log.ldf,filename=E:sql server 2008 dbtest_log.ldf,size = 10MB,size = 10MB,maxsize = 50MB,maxsize = 50MB,filegrowth=5MB),filegrowth=5MB),(name

15、= test_log1,(name = test_log1,filename=E:sql server 2008 dbtest_log1.ldf,filename=E:sql server 2008 dbtest_log1.ldf,size = 10MB,size = 10MB,maxsize = 50MB,maxsize = 50MB,filegrowth=5MB)filegrowth=5MB)5.3 5.3 管理數(shù)據(jù)庫管理數(shù)據(jù)庫l查看數(shù)據(jù)庫屬性 l修改數(shù)據(jù)庫 l收縮數(shù)據(jù)庫 l刪除數(shù)據(jù)庫 查看數(shù)據(jù)庫屬性查看數(shù)據(jù)庫屬性 對象資源管理器是Management Studio的一個組 使用使用Man

16、agement StudioManagement Studio查看數(shù)據(jù)庫屬性查看數(shù)據(jù)庫屬性 用視圖、系統(tǒng)函數(shù)和系統(tǒng)存儲過程來查看數(shù)據(jù)庫屬性用視圖、系統(tǒng)函數(shù)和系統(tǒng)存儲過程來查看數(shù)據(jù)庫屬性 視圖視圖函數(shù)函數(shù)存儲過程和其他語句存儲過程和其他語句sys.databasesDATABASE_PRINCIPAL_IDsp_databasessys.database_filesDATABASEPROPERTYEXsp_helpdbsys.data_spacesDB_IDsp_helpfilesys.filegroupsDB_NAMEsp_helpfilegroupsys.allocation_unitsFI

17、LE_IDsp_spaceusedsys.master_filesFILE_IDEXDBCC SQLPERFsys.partitionsFILE_NAME sys.partition_functionsFILEGROUP_ID sys.partition_parametersFILEGROUP_NAME sys.partition_range_valuesFILEGROUPPROPERTY sys.partition_schemesFILEPROPERTY sys.dm_db_partition_statsfn_virtualfilestats l【例5.5.5】顯示Master數(shù)據(jù)庫信息。s

18、p_helpdb Master執(zhí)行結(jié)果如圖5.5.10所示。修改數(shù)據(jù)庫修改數(shù)據(jù)庫l可以在Management Studio中利用數(shù)據(jù)庫屬性的設(shè)置更改數(shù)據(jù)庫各項參數(shù)，也可使用ALTER DATABASE命令來更改數(shù)據(jù)庫，ALTER DATABASE可以更改數(shù)據(jù)庫的屬性或其文件和文件組。ALTER DATABASE的語法格式如下：ALTER DATABASE database_name | | | MODIFY NAME = new_database_name | COLLATE collation_name參數(shù)簡要說明參數(shù)簡要說明（1）database_name：要修改的數(shù)據(jù)庫的名稱。（2）MO

19、DIFY NAME = new_database_name：使用指定的名稱 new_database_name 重命名數(shù)據(jù)庫。（3）：指定添加、修改或刪除的數(shù)據(jù)庫文件。其語法格式為：:= ADD FILE ,.n TO FILEGROUP filegroup_name | DEFAULT | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | MODIFY FILE （4）：指定添加、修改或刪除的文件組。（5）：更改數(shù)據(jù)庫參數(shù)。【例5.5.6】將數(shù)據(jù)庫名testtest更改為test1test1。ALTER DATABASE testtest

20、MODIFY NAME = test1test1【例5.5.7】將一個 5MB 的數(shù)據(jù)文件添加到“test”數(shù)據(jù)庫。ALTER DATABASE test ADD FILE ( NAME = test1, FILENAME = E:sql server 2008 dbtestdat1.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)【例5.5.8】刪除上例中添加的數(shù)據(jù)庫文件。ALTER DATABASE testREMOVE FILE test1【例5.5.9】移動數(shù)據(jù)庫文件的位置。ALTER DATABASE testMODIFY FIL

21、E( NAME = test_dat1.ndf, FILENAME = Nc:test_data2.ndf)【例5.5.10】更改數(shù)據(jù)庫文件大小。ALTER DATABASE test MODIFY FILE (NAME = test_data,SIZE = 20MB)【例例5.1111】向數(shù)據(jù)庫添加兩個日志文件向數(shù)據(jù)庫添加兩個日志文件ALTER DATABASE test ADD LOG FILE ( NAME = test_log2, FILENAME = E:sql server 2008 dbtest_log2.ldf , SIZE = 5MB, MAXSIZE = 100MB, FI

22、LEGROWTH = 5MB),( NAME = test_log3, FILENAME = E:sql server 2008 dbtest_log2.ldf , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)【例例5.1212】更改數(shù)據(jù)庫選項。更改數(shù)據(jù)庫選項。ALTER DATABASE test SET SINGLE_USER -單用戶ALTER DATABASE test SET READ_ONLY -只讀ALTER DATABASE test SET AUTO_SHRINK ON -自動收縮GO收縮數(shù)據(jù)庫收縮數(shù)據(jù)庫l使用一段時間后，時常會

23、出現(xiàn)因數(shù)據(jù)刪除而造成數(shù)據(jù)庫中空閑空間太多的情況，需要使用收縮的方式來縮減數(shù)據(jù)庫空間。l可在數(shù)據(jù)庫屬性選項中選擇“Auto shrink”選項，讓系統(tǒng)自自動動收縮數(shù)據(jù)庫，也可用人工的方法來收縮。 l1使用Management Studio收縮數(shù)據(jù)庫l2使用Transact-SQL 命令收縮數(shù)據(jù)庫 （1）使用DBCC SHRINKDATABASE對數(shù)據(jù)庫進行收縮。其語法格式為： DBCC SHRINKDATABASE ( database_name | database_id | 0 ,target_percent , NOTRUNCATE | TRUNCATEONLY ) WITH NO_INF

24、OMSGS l各參數(shù)說明如下。 database_name | database_id | 0 ：要收縮的數(shù)據(jù)庫的名稱或 ID。如果指定 0，則使用當前數(shù)據(jù)庫。 target_percent：收縮后的數(shù)據(jù)庫文件中可用空間百分比。 NOTRUNCATE：指定在數(shù)據(jù)庫文件中保留所釋放的文件空間。如果未指定，將所釋放的文件空間釋放給操作系統(tǒng)。 TRUNCATEONLY：指定數(shù)據(jù)文件中任何未使用空間被釋放給操作系統(tǒng)，并將文件收縮到最后分配的區(qū)。l【例5.5.13】將UserDB用戶數(shù)據(jù)庫中的文件減小，以使test中的文件有10%的可用空間。 DBCC SHRINKDATABASE (UserDB, 10)l（2）使用DBCC SHRINKFILE對數(shù)據(jù)庫中指定的文件進行收縮。其語法格式為：DBCC SHRINKFILE ( file_name | file_id , EMPTYFILE | , ta