任務二為數據管理創建數據庫環境

—創建和管理SQLServer數據庫任務:在SSMS中創建數據庫JXGL，并對JXGL進行簡單管理；使用CREATEDATABASE語句創建數據庫JXGL，使用T-SQL語句對JXGL進行簡單管理。2.1認識SQLServer數據庫2.2創建SQLServer數據庫2.3管理SQLServer數據庫2.4知識進階

項目實訓學習目標了解SQLServer數據庫的存儲結構建立事務日志的概念使用SSMS創建數據庫使用CREATEDATABASE語句創建數據庫使用SSMS管理數據庫使用T-SQL語言管理數據庫2.1認識SQLServer數據庫任務：了解SQLServer數據庫的存儲結構，建立事務日志的概念。創建和管理SQLServer數據庫2.1.1SQLServer數據庫的存儲結構2.1.2事務日志2.1.1SQLServer數據庫的存儲結構SQLServer數據庫的存儲結構分為邏輯存儲結構和物理存儲結構。邏輯存儲結構指的是數據庫是由各種數據庫對象組成的；物理存儲結構是討論數據庫文件如何在磁盤上存儲的。事務日志是SQLServer數據庫的一個亮點，它對數據庫恢復起著至關重要的作用。從邏輯存儲結構角度看，SQLServer數據庫是由表、視圖、索引、存儲過程等各種數據庫對象組成的，它們被數據庫管理系統管理。從物理存儲結構角度看，數據庫實際上是一個文件的集合，在磁盤上以文件為單位存儲。數據庫的物理存儲結構是用來討論數據庫文件如何在磁盤上存儲的。創建和管理SQLServer數據庫數據的邏輯結構和物理結構

物理存儲結構（數據頁）數據區學號姓名性別出生日期民族J07051129王浩男1988-9-23漢族J08010212杜薇女1990-3-16回族…槽2槽1槽0頁頭（96字節）行偏移數組…邏輯存儲結構（數據表）2.1.1SQLServer數據庫的存儲結構創建和管理SQLServer數據庫1．數據庫的物理文件數據庫是存儲數據的容器，數據以文件形式存儲在磁盤上，稱為數據庫的物理文件或操作系統文件。每個SQLServer2005數據庫包含兩類物理文件：數據文件和日志文件。數據文件存儲數據和數據庫對象。第一個數據文件稱為主數據文件，文件名后綴為.mdf（PrimaryDataFile）；日志文件用來存儲恢復數據庫中所有事務所需的信息，文件名后綴為.ldf（LogDataFile）。每個SQLServer數據庫至少包含一個主數據文件和一個日志文件。當數據庫存儲的數據量巨大，或需要將重要數據與次要數據分開存儲時，用戶可自行創建次數據文件，輔助主數據文件存儲數據，文件名后綴為.ndf（SecondaryDataFile）。一個SQLServer2005數據庫只包含一個主數據文件，必須至少包含一個日志文件，可以不包含或包含多個次數據文件。

2.1.1SQLServer數據庫的存儲結構創建和管理SQLServer數據庫2．數據庫文件的名稱每個SQLServer2005數據庫文件有兩個文件名：邏輯文件名和操作系統文件名。（1）邏輯文件名。在T-SQL語句中引用數據庫文件時使用的名稱。其命名必須符合SQLServer標識符規則，且在同一數據庫中必須唯一。（2）操作系統文件名。包含存儲目錄路徑的數據庫文件名稱。其命名必須符合操作系統文件命名規則，且在同一數據庫中必須唯一。2.1.1SQLServer數據庫的存儲結構創建和管理SQLServer數據庫3．數據庫文件的存儲（1）數據頁

數據文件的基本存儲單位為8KB（8192B）的連續空間，稱為一個頁（Page）。SQLServer2005中包含數據頁、索引頁、文本/圖像頁等8種類型的頁。頁由以下三部分組成：

1）頁頭。頁首的96KB用來存儲頁的系統管理信息，如頁類型、頁的可用空間、頁碼及擁有該頁的數據庫對象的分配單元ID值等信息。

2）數據區。以槽（Slot）為單位的空間，用來存儲數據。數據記錄（行）就存儲在槽中，緊接著頁頭按順序放置。

3）行偏移數組。映射槽的相對位置，通過查詢行偏移數組中某個槽的偏移量，就可以準確定位槽在數據區的位置。

一個頁只能存儲一個數據庫對象，如表、索引。

2.1.1SQLServer數據庫的存儲結構創建和管理SQLServer數據庫（2）盤區SQLServer2005將8個物理上連續的頁作為一個單元為數據庫對象分配存儲空間，這個單元稱為盤區（Extent），容量為64KB。以盤區為分配單位，就不會因分配存儲空間的操作十分繁忙而影響數據庫性能，提高了空間分配效率。分配盤區的基本策略是：容量不滿8個頁的數據庫對象盡量從已經存在的混合盤區中挑選一個進行后續分配；為容量滿8個頁的數據庫對象分配統一盤區。這樣大大提高了空間利用率。創建和管理SQLServer數據庫2.1.1SQLServer數據庫的存儲結構2.1.1SQLServer數據庫的存儲結構數據庫（表，索引）（數據）數據（文件）

.mdf

或

.ndf日志（文件）

.ldf.頁（8KB，每行最大容量8060B）盤區（64KB，8個物理上連續的頁）數據庫的物理存儲結構創建和管理SQLServer數據庫2.1.1SQLServer數據庫的存儲結構4．數據文件組為便于分配數據和管理文件，可以將數據文件組織到不同的文件組中。SQLServer2005包含兩種類型的文件組：主文件組（Primary）和用戶定義文件組。主文件組包含主數據文件和未放入其他文件組的所有次數據文件。每個數據庫只有一個主文件組，也是數據庫的默認文件組，系統表的所有頁均分配在主文件組中。數據庫包含多個數據文件時，用戶也可以自定義文件組，將次文件放到自定義文件組中。提示：文件組是命名的文件集合，只包含數據文件，日志文件不包括在任何文件組內。2.1.1SQLServer數據庫的存儲結構創建和管理SQLServer數據庫2.1.1SQLServer數據庫的存儲結構事務（Transaction）是需要一次完成的操作集合。事務作為SQLServer的單個邏輯工作單元，必須徹底執行完或根本不執行。事務日志用來記錄所有事務和每個事務對數據庫所做的更新操作，以日志文件形式存儲在磁盤中。1．事務日志的作用（1）單獨恢復個別事務。（2）保證數據庫的完整。（3）將恢復的數據庫、文件、文件組或頁回滾到某一故障點。2．事務日志的特點（1）作為單獨的文件實現，可以根據需要定義文件為自動增長。（2）日志文件與數據文件分開存放，減少了沖突。（3）事務日志可以單獨備份、恢復，不必遵守數據頁的存儲格式。（4）日志的截斷機制速度快，對事務的容量影響小。（5）記錄了數據頁的分配和釋放信息。2.1.2事務日志創建和管理SQLServer數據庫2.2創建SQLServer數據庫任務：創建JXGL數據庫。創建數據庫前，必須首先明確數據庫名稱、數據庫所有者、初始容量、最大容量、數據庫文件的存儲路徑及增長方式等。創建和管理SQLServer數據庫2.2.1使用SSMS創建數據庫2.2.2使用T-SQL創建數據庫【例2-1】創建未指定文件的數據庫shili01。代碼如下：IFDB_ID('shili01')ISNOTNULL--判斷當前服務中是否已有名稱為shili01的數據庫DROPDATABASEshili01GO--若已有名稱為shili01的數據庫，則刪除它CREATEDATABASEshili01GO

分析：本示例沒有<filespec>語句項。主數據庫文件的大小為model數據庫主文件的大小1MB，事務日志文件為下列值中較大者：512KB或主數據文件大小的25%。因為沒有指定MAXSIZE，文件可以增大到填滿全部可用的磁盤空間為止。2.2.1使用SSMS創建數據庫創建和管理SQLServer數據庫創建用戶數據庫的過程就是為數據庫確定名稱、大小和數據庫文件名稱及存放位置的過程。新建數據庫的信息存放在系統數據庫master中，屬于系統級信息。創建SQLServer數據庫主要有兩種方法：使用視圖化管理工具SQLServerManagementStudio（SSMS）和在代碼編輯器中使用T-SQL語言中的CREATEDATABASE語句。創建示例數據庫JXGL的操作步驟：2.2.1使用SSMS創建數據庫創建和管理SQLServer數據庫1．CREATEDATABASE的基本語法使用CREATEDATABASE語句創建數據庫。基本語法如下：CREATEDATABASEdatabase_name[ON[PRIMARY][<filespec>[,…n]][,<filegroup>][,…n]][LOGON[<filespec>[,…n]]]其中：<filespec>::=(NAME=logical_file_name[,FILENAME='os_file_name'][,SIZE=size[MB]][,MAXSIZE=max_size[MB]][,FILEGROWTH=growth_increment[MB|%]])[,…n]其中：<filegroup>::=FILEGROUPfilegroup_name[DEFAULT]2.2.2使用T-SQL創建數據庫創建和管理SQLServer數據庫【例2-1】創建未指定文件的數據庫shili01。代碼如下：IFDB_ID('shili01')ISNOTNULL

--判斷當前服務中是否已有名稱為shili01的數據庫DROPDATABASEshili01GO--若已有名稱為shili01的數據庫，則刪除它CREATEDATABASEshili01GO

分析：本示例沒有<filespec>語句項。主數據庫文件的大小為model數據庫主文件的大小1MB，事務日志文件為下列值中較大者：512KB或主數據文件大小的25%。因為沒有指定MAXSIZE，文件可以增大到填滿全部可用的磁盤空間為止。創建和管理SQLServer數據庫2.2.2使用T-SQL創建數據庫【例2-2】創建指定數據文件和日志文件的數據庫shili02。代碼如下：CREATEDATABASEshili02ON(NAME=shili02_data,FILENAME='D:\shili\shili02_data.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=shili02_log,FILENAME='D:\shili\shili02_log.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)GO

分析：本示例沒有使用關鍵字PRIMARY，第一個文件shili02_data將成為主文件。在shili02_data.mdf文件的SIZE參數中沒有指定單位，將使用MB并按MB分配空間。創建和管理SQLServer數據庫2.2.2使用T-SQL創建數據庫【例2-3】創建指定多個數據和事務日志文件的數據庫shili03。代碼：CREATEDATABASEshili03ONPRIMARY(NAME=shili03_data,FILENAME='D:\shili\shili03_data.mdf',SIZE=2,MAXSIZE=200,FILEGROWTH=2),(NAME=shili03_data302,FILENAME='D:\shili\shili03_data302.ndf',SIZE=5,MAXSIZE=500,FILEGROWTH=5%)2.2.2使用T-SQL創建數據庫創建和管理SQLServer數據庫LOGON(NAME=shili03_log301,FILENAME='D:\shili\shili03_log301.ldf',SIZE=10,MAXSIZE=200,FILEGROWTH=10%),(NAME=shili03_log302,FILENAME='D:\shili\shili03_log302.ldf',SIZE=5,MAXSIZE=1024,FILEGROWTH=2)GO

分析：本示例要創建的數據庫shili03具有兩個數據文件和兩個日志文件。主數據文件使用PRIMARY關鍵字顯式指定，日志文件在LOGON關鍵字后指定。創建和管理SQLServer數據庫2.2.2使用T-SQL創建數據庫【例2-4】創建具有文件組的數據庫shili04。代碼如下：CREATEDATABASEshili04ONPRIMARY(NAME=shili04_data,FILENAME='d:\shili\shili04_data.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=shili04_data402,FILENAME='d:\shili\shili04_data402.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),創建和管理SQLServer數據庫2.2.2使用T-SQL創建數據庫FILEGROUPshiligroup1(NAME=shili04_data403,FILENAME='d:\shili\shili04_data403.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=shili04_data404,FILENAME='d:\shili\shili04_data404.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)2.2.2使用T-SQL創建數據庫創建和管理SQLServer數據庫LOGON(NAME=shili04_log,FILENAME='d:\shili\shili04_log.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)GO

分析：本示例創建的數據庫shili04包含以下兩個文件組：①包含文件shili04_data和shili04_data402的主文件組PRIMARY；②名為shiligroup1的文件組，包含數據文件shili04_data403和shili04_data404。創建和管理SQLServer數據庫2.2.2使用T-SQL創建數據庫4．創建“高職院校教學管理系統”數據庫JXGL【例2-5】創建本教材實例數據庫JXGL。該數據庫具有兩個文件組：①包含主數據文件JXGL_data的主文件組，文件初始容量為4MB，遞增率為5MB，最大容量為500MB；②filegroup1文件組，包含數據文件JXGL_data02，文件初始容量為2MB，遞增率為10%，最大容量為500MB。包含兩個日志文件JXGL_log和JXGL_log02，文件初始容量為5MB，遞增率為5%和5MB，最大容量為1GB和500MB。代碼如下：創建和管理SQLServer數據庫2.2.2使用T-SQL創建數據庫CREATEDATABASEJXGLONPRIMARY(NAME=JXGL_data,FILENAME='D:\JXGL\JXGL_data.mdf',SIZE=4,MAXSIZE=500,FILEGROWTH=5),FILEGROUPfilegroup1(NAME=JXGL_data02,FILENAME='D:\JXGL\JXGL_data02.ndf',SIZE=2,MAXSIZE=500,FILEGROWTH=10%)2.2.2使用T-SQL創建數據庫創建和管理SQLServer數據庫LOGON(NAME=JXGL_log,FILENAME='D:\JXGL\JXGL_log.ldf',SIZE=5,MAXSIZE=1024,FILEGROWTH=5%),(NAME=JXGL_log02,FILENAME='D:\JXGL\JXGL_log02.ldf',SIZE=5,MAXSIZE=500,FILEGROWTH=5)GO創建和管理SQLServer數據庫2.2.2使用T-SQL創建數據庫創建用戶數據庫時要注意以下幾點：（1）默認情況下，只有系統管理員可以創建數據庫。（2）當新的用戶數據庫創建后，master數據庫的系統表sysdatabases立即被更新，因此創建數據庫后應及時備份master數據庫。（3）所有用戶數據庫都是model數據庫的副本，數據庫的系統表包含在主文件中。model數據庫的數據文件為1MB，日志文件也是1MB。這意味著用戶數據庫的最小容量為2MB。（4）在一個數據庫實例上，最多可創建32767個數據庫。一個數據庫最多可包含32767個數據文件和32767個日志文件。（5）創建完成新數據庫后，想在“對象資源管理器”的數據庫列表中看到剛創建的數據庫，需要刷新“數據庫”節點。創建和管理SQLServer數據庫2.2.2使用T-SQL創建數據庫

2.3管理SQLServer數據庫任務：使用SSMS及T-SQL語言兩種方法對數據庫JXGL進行簡單管理。

創建和管理SQLServer數據庫2.3.1使用SSMS管理數據庫2.3.2使用T-SQL管理數據庫1．查看數據庫屬性2．修改數據庫3．刪除數據庫提示：①對數據庫執行刪除命令，不但數據庫將被刪除，數據庫文件也將從磁盤中被刪除。②刪除數據庫將更新master數據庫中的系統表。若master需要還原，則從上次備份master之后刪除的所有數據庫仍然在系統表中被引用，因而可能導致出錯。4．數據庫更名數據庫被創建后一般不要更改名稱，否則所有與該數據庫的連接都將出錯。5．生成創建數據庫腳本2.3.1使用SSMS管理數據庫創建和管理SQLServer數據庫1．查看數據庫屬性使用系統存儲過程sp_helpdb查看指定數據庫或當前所有數據庫信息?；菊Z法如下：sp_helpdb[database_name]database_name缺省時，查看當前所有數據庫信息。【例2-6】查看數據庫實例下所有數據庫信息，查看JXGL數據庫信息。代碼如下：（1）sp_helpdb--查看數據庫實例下所有數據庫信息（2）sp_helpdbJXGL--查看JXGL數據庫信息運行結果見圖2-9，2-10

2.3.2使用T-SQL管理數據庫創建和管理SQLServer數據庫2．修改數據庫通過ALTERDATABASE語句可以添加、刪除數據庫文件、文件組；可以更改文件或文件組的屬性，如名稱、容量等；還可以更改數據庫名稱；但不能移動數據庫的存儲位置。基本語法如下：ALTERDATABASE<database_name>ADDFILE<datafile>|ADDFILE<logfile>|REMOVEFILE<logicfile_name>|ADDFILEGROUP<filegroupname>|REMOVEFILE<filegroupname>|MODIFYFILE<file_name>|MODIFYNAME<newdatabase_name>|MODIFYFILEGROUP<filegroup_name>|SET<option>創建和管理SQLServer數據庫2.3.2使用T-SQL管理數據庫【例2-7】使用ALTERDATABASE語句對數據庫JXGL進行以下修改。添加一個次數據文件JXGL_data03，初始大小為5MB，最大容量為200MB，增長率為2%。代碼如下：USEJXGLGOALTERDATABASEJXGLADDFILE(NAME=JXGL_data03,FILENAME='D:\JXGL\JXGL_data03.ndf',SIZE=5,MAXSIZE=200,FILEGROWTH=2%)GO創建和管理SQLServer數據庫2.3.2使用T-SQL管理數據庫【例2-8】使用ALTERDATABASE語句對數據庫JXGL進行以下修改：將日志文件JXGL_log02的初始容量由5MB改為10MB，刪除數據文件JXGL_data03。代碼如下：USEJXGLGOALTERDATABASEJXGLMODIFYFILE(NAME=JXGL_log02,SIZE=10MB)GOALTERDATABASEJXGLREMOVEFILEJXGL_data03GO創建和管理SQLServer數據庫2.3.2使用T-SQL管理數據庫3．移動數據庫位置通過ALTERDATABASE語句的FILENAME子句指定數據庫文件的新位置，可以移動系統數據庫文件和用戶數據庫文件?！纠?-9】使用ALTERDATABASE語句將數據庫shili02移動到D:\shiliyidong。（1）移動數據文件位置，代碼如下：ALTERDATABASEshili02MODIFYFILE(NAME=shili02_data,FILENAME='D:\shiliyidong\shili_data02.mdf')GOALTERDATABASEshili02MODIFYFILE(NAME=shili02_log,FILENAME='D:\shiliyidong\shili_log02.ldf')GO創建和管理SQLServer數據庫2.3.2使用T-SQL管理數據庫

提示：移動數據庫文件，需要知道數據庫文件的邏輯名。可以通過查詢sys.master_files目錄視圖中的name列獲取。不能移動或重命名資源數據庫文件，若該文件已重命名或移動，SQLServer將不啟動。（2）驗證數據文件位置已更改，代碼如下：SELECTname,physical_nameFROMsys.master_filesWHEREdatabase_id=DB_id('shili02')GO創建和管理SQLServer數據庫2.3.2使用T-SQL管理數據庫4．刪除數據庫使用DROPDATABASE語句刪除數據庫?；菊Z法如下：DROPDATABASE<database_name>【例2-10】刪除不再需要的數據庫shili01。代碼如下：DROPDATABASEshili01執行代碼后，刷新“數據庫”節點，觀察發現，數據庫shili01已不在數據庫列表中。創建和管理SQLServer數據庫2.3.2使用T-SQL管理數據庫5．數據庫更名使用系統存儲過程sp_renamedb可以對數據庫進行重命名?；菊Z法如下：sp_renamedb<old_database_name>,<new_database_name>如將數據庫shili04改名為shili。代碼如下：sp_renamedbshili04,shili創建和管理SQLServer數據庫2.3.2使用T-SQL管理數據庫2.4知識進階任務：對數據庫容量進行理論估算，對數據庫實施收縮，節省存儲空間。創建和管理SQLServer數據庫2.4.1數據庫空間的估算2.4.2增加和收縮數據庫容量許多因素影響數據庫的最終容量:記錄的大小。列的大小決定了記錄的大小。記錄的數量。一般情況下，應該按數據表所代表的實體或關系的實際數據量估算該表要存儲的記錄數，而不是憑空想象。表的數量。按關系模型特點和數據庫設計規范化原則確定。索引的大小。索引的大小取決于索引關鍵列的大小、索引中包含的數據量（記錄數）和索引填充因子。填充因子的值越小，索引頁上的空閑空間越大。索引的數量。每個表中都至少包含一個索引。事務日志的大小。修改數據頻率越高，存放日志需要更多空間其他數據庫對象的大小和數量。除數據表外，數據庫還包含如索引、視圖、存儲過程等數據庫對象。數據庫的計劃增長率。預先對數據庫存儲需求估算得高些。創建和管理SQLServer數據庫2.4.1數據庫空間的估算SQLServer采用預先指定、分配存儲空間的方法來創建數據庫。當數據庫的數據增長到要超過定義的使用空間時，必須加大數據庫容量，增加數據庫容量即為數據庫提供額外的存儲空間。反之，當分配的空間沒有被充分利用時，會造成空間浪費。收縮數據庫功能允許對數據庫中的每個文件進行收縮，刪除已經分配但沒有使用的頁。不能將整個數據庫收縮到比其初始容量還小。收縮數據庫的動作在后臺執行，不影響數據庫內的用戶活動，但不能在備份數據庫時收縮數據庫，也不在收縮數據庫時創建或備份數據庫。創建和管理SQLServer數據庫2.4.2增加和收縮數據庫容量1．使用T-SQL語言手工收縮數據庫語法如下：DBCCSHRINKDATABASE|SHRINKFILE(d