第2章

數據庫的創建和管理學習要點：SQLServer數據庫的存儲結構事務日志的概念數據庫的創建、刪除、管理2.1基本概念

2.2創建數據庫2.3數據庫的管理

2.4刪除數據庫2.1基本概念

主要數據文件數據文件數據庫次要數據文件

事務日志文件主要數據文件用來存儲數據庫的啟動信息、部分或全部數據，擴展名為.mdf每個數據庫只能有一個主要數據文件次要數據文件用于保存所有主要數據文件中容納不下的數據，擴展名為.ndf用于存儲數據庫中的所有對象

保存用于恢復數據庫的日志信息文件組:將多個數據文件集合起來形成的一個整體目的：便于進行管理和數據的分配類型：主文件組：包含了所有的系統表

用戶自定義文件組默認文件組：包含所有在創建時沒有指定文件組的表、索引等數據庫對象注意：一個數據文件不能存在于兩個或兩個以上的文件組里，日志文件不屬于任何文件組2.1.1數據庫的物理存儲結構master數據庫master數據庫記錄了SQLServer系統的所有系統級別的信息，包括所有的登錄帳戶和密碼、系統的配置信息以及所有用戶定義數據庫的存儲位置、初始化信息。master是SQLServer中重要的數據庫，如果master數據庫受到損壞，SQLServer將不能啟動。

model數據庫model數據庫是建立新數據庫的模板，它包含了將復制到每個新建數據庫中的系統表。

msdb數據庫該數據庫主要被SQLServerAgent用于進行復制、作業調度以及管理報警等活動。tempdb數據庫tempdb數據庫保存所有的臨時表、臨時數據以及臨時創建的存儲過程。這四個系統數據庫是運行SQLServer的基礎。示例數據庫

pubs數據庫記錄一個虛擬的圖書出版公司的數據信息

northwind數據庫保存了一個虛擬的貿易公司的數據信息

（3）在“常規”選項卡中輸入數據庫的名稱，假設為Sales。輸入數據庫名稱（4）選擇“數據文件”選項卡，在該窗口中設置數據文件的屬性。數據文件的邏輯名

文件位置初始大小文件所在的文件組文件否允許自動增長

（5）選擇“事務日志”選項卡，在該窗口中可設置日志文件的屬性。

（6）單擊“確定”按鈕，數據庫創建完成。

2.2.2使用T-SQL語句創建數據庫創建數據庫的T-SQL語句的語法形式：CREATEDATABASEdatabase_name[ON

[<filespec>[,...n]][,<filegroup>[,...n]]][LOGON{<filespec>[,...n]}]<filespec>::=[PRIMARY]([NAME=logical_file_name,]FILENAME='os_file_name‘[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])[,...n]<filegroup>::=FILEGROUPfilegroup_name<filespec>[,...n]database_name：新創建的數據庫的名稱。ON：指出用來存儲數據庫中數據部分的磁盤文件（數據文件）。PRIMARY：指定主文件組中的主文件。LOGON：指定用來存儲數據庫日志的磁盤文件。NAME=logical_file_name：指定數據文件或日志文件的邏輯名。FILENAME='os_file_name'：指定數據文件或日志文件的操作系統文件名，包括文件名和路徑。SIZE=size：指定數據文件或日志文件的初始大小，默認單位為MB。MAXSIZE={max_size|UNLIMITED}：指定數據文件或日志文件可以增長到的最大容量，默認單位為MB。FILEGROWTH=growth_increment：指定數據文件或日志文件的增長幅度，默認單位為MB。例2-1創建一個數據庫sample。主文件為sample_dat。CREATEDATABASEsampleONPRIMARY(NAME=sample_dat,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\sample_data.mdf',SIZE=5,MAXSIZE=50,FILEGROWTH=10)例2-3創建一個包含多個數據文件和日志文件的數據庫。該數據庫名為student，含有三個初始大小為10MB的數據文件和兩個8MB的日志文件。

CREATEDATABASEstudentONPRIMARY(NAME=std_dat1,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\student1.mdf',SIZE=10MB,MAXSIZE=200MB,FILEGROWTH=20),(NAME=std_dat2,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\student2.ndf',SIZE=10MB,MAXSIZE=200MB,FILEGROWTH=20),(NAME=std_dat3,

FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\student3.ndf',

SIZE=10MB,

MAXSIZE=200MB,

FILEGROWTH=20)

LOGON

(NAME=std_log1,

FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\stdlog1.ldf',

SIZE=8MB,

MAXSIZE=100MB,

FILEGROWTH=10MB),

(NAME=std_log2,

FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\stdlog2.ldf',

SIZE=8MB,

MAXSIZE=100MB,

FILEGROWTH=10MB)FILEGROUPbusiness_group(NAME=business_dat3,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\businessdat3.ndf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10%)LOGON(NAME=business_log,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\businesslog.ldf',SIZE=8MB,MAXSIZE=100MB,FILEGROWTH=10MB)2.3數據庫的管理2.3.1查看數據庫

1．用企業管理器查看數據庫的屬性

進入SQLServer企業管理器，在左邊的目錄樹結構中選中要查看的數據庫Sales，單擊右鍵，在彈出的快捷菜單上單擊“屬性”命令，即可進入Sales數據庫的屬性窗口。該窗口共有6個選項卡，包括“常規”、“數據文件”、“事務日志”、“文件組”、“選項”及“權限”，我們可以根據需要進入選擇不同的選項卡查看數據庫相應的信息。

2．用T-SQL語句查看數據庫的屬性使用系統存儲過程sp_helpdb可以查看某個數據庫或所有數據庫的屬性。格式：sp_helpdbdatabase_name該存儲過程顯示了Sales數據庫的名稱、大小、所有者、創建日期以及數據文件和日志文件等屬性。例2-5查看數據庫Sales的屬性。

sp_helpdbSales例2-6查看所有數據庫的屬性

sp_helpdb

2.3.2修改數據庫

1．用企業管理器修改數據庫屬性

進入數據庫的屬性窗口，在屬性窗口中修改數據庫屬性。注意：在刪除文件組時要求該文件組中不包含任何數據文件，否則SQLServer會給出錯誤信息2．用T-SQL語句修改數據庫屬性

其語法形式為：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}MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}：修改某一文件組的屬性。例2-7為數據庫sample添加一個數據文件sample_dat2和一個日志文件sample_log2。ALTERDATABASEsampleADDFILE(NAME=sample_dat2,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\sample_dat2.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)ALTERDATABASEsampleADDLOGFILE(NAME=sample_log2,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\sample_log2.ldf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)例2-8為數據庫sample添加一個文件組USER1，并向該文件組添加一個二個數據文件sampuser_dat1和sampuser_dat2。

ALTERDATABASEsampleADDFILEGROUPUSER1

ALTERDATABASEsampleADDFILE(NAME=sampuser_dat1,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\sampuser_dat1.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1),(NAME=sampuser_dat2,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\sampuser_dat2.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)TOFILEGROUPUSER1例2-9從數據庫sample中刪除文件sampuser_dat2。ALTERDATABASEsampleREMOVEFILEsampuser_dat2例2-10刪除數據庫sample中的文件組USER1。ALTERDATABASEsampleREMOVEFILEsampuser_dat1ALTERDATABASEsampleREMOVEFILEGROUPUSER1注意：在刪除文件組時必須先刪除文件組中所包含的文件。

例2-12修改數據庫business中文件組business_group的屬性，將其改名為group1，并設置為DEFAULT屬性（即該文件組為默認文件組）。ALTERDATABASEbusinessMODIFYFILEGROUPbusiness_groupNAME=group1ALTERDATABASEbusinessMODIFYFILEGROUPgroup1DEFAULT例2-13將數據庫student改名為stud_teacher。ALTERDATABASEstudentMODIFYNAME=stud_teacher

2.3.3重命名數據庫語法形式為：sp_renamedb‘old_name’,’new_name’old_name：數據庫的當前名稱；new_name是數據庫的新名稱。例2-14將數據庫business更名為company。

sp_renamedb'business','company

2.3.4數據庫的收縮

SQLServer2000允許收縮數據庫中的每個文件以刪除未使用的頁。數據文件和事務日志文件都可以收縮。

1．自動收縮（1）使用企業管理器設置自動收縮數據庫在數據庫的“屬性對話框”中，點擊“選項”選項卡，選中“自動收縮”復選框，即可完成數據庫自動收縮的設置。

設置自動收縮（2）T-SQL語句自動收縮數據庫使用ALTERDATABASE語句可以將數據庫設為自動收縮。當數據庫中有足夠的可用空間時，就會發生收縮。其語法形式為：ALTERDATABASEdatabase_nameSETAUTO_SHRINKon/off例2-15將數據庫sales的收縮設為自動收縮。ALTERDATABASEsalesSETAUTO_SHRINKon注意：不能將整個數據庫收縮到比其原始大小還要小。2．手工收縮DBCCSHRINKDATABASE(database_name[,target_percent][,{NOTRUNCATE|TRUNCATEONLY}])database_name：是要收縮的數據庫名稱。target_percent：是數據庫收縮后的數據庫文件中所要的剩余可用空間百分比。NOTRUNCATE：被釋放的文件空間依然保持在數據庫文件的范圍內。如果未指定，則被釋放的文件空間將被操作系統回收利用。

TRUNCATEONLY：將數據文件中的任何未使用的空間釋放給操作系統。例2-16縮小sales數據庫的大小，使得該數據庫中的文件有20％的可用空間。

DBCCSHRINKDATABASE(sales,20)

還可以使用DBCC命令來縮小某一個操作系統文件的長度，其語法形式為：DBCCSHRINKFILE

(file_name

{[,target_size]

|[,{EMPTYFILE|NOTRUNCATE|TRUNCATEONLY}]}

)f