數(shù)據(jù)庫(kù)的創(chuàng)建和管理課件

第2章

數(shù)據(jù)庫(kù)的創(chuàng)建和管理學(xué)習(xí)要點(diǎn)：SQLServer數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu)事務(wù)日志的概念數(shù)據(jù)庫(kù)的創(chuàng)建、刪除、管理2.1基本概念

2.2創(chuàng)建數(shù)據(jù)庫(kù)2.3數(shù)據(jù)庫(kù)的管理

2.4刪除數(shù)據(jù)庫(kù)2.1基本概念

主要數(shù)據(jù)文件數(shù)據(jù)文件數(shù)據(jù)庫(kù)次要數(shù)據(jù)文件

事務(wù)日志文件主要數(shù)據(jù)文件用來(lái)存儲(chǔ)數(shù)據(jù)庫(kù)的啟動(dòng)信息、部分或全部數(shù)據(jù)，擴(kuò)展名為.mdf每個(gè)數(shù)據(jù)庫(kù)只能有一個(gè)主要數(shù)據(jù)文件次要數(shù)據(jù)文件用于保存所有主要數(shù)據(jù)文件中容納不下的數(shù)據(jù)，擴(kuò)展名為.ndf用于存儲(chǔ)數(shù)據(jù)庫(kù)中的所有對(duì)象

保存用于恢復(fù)數(shù)據(jù)庫(kù)的日志信息文件組:將多個(gè)數(shù)據(jù)文件集合起來(lái)形成的一個(gè)整體目的：便于進(jìn)行管理和數(shù)據(jù)的分配類型：主文件組：包含了所有的系統(tǒng)表

用戶自定義文件組默認(rèn)文件組：包含所有在創(chuàng)建時(shí)沒(méi)有指定文件組的表、索引等數(shù)據(jù)庫(kù)對(duì)象注意：一個(gè)數(shù)據(jù)文件不能存在于兩個(gè)或兩個(gè)以上的文件組里，日志文件不屬于任何文件組2.1.1數(shù)據(jù)庫(kù)的物理存儲(chǔ)結(jié)構(gòu)master數(shù)據(jù)庫(kù)master數(shù)據(jù)庫(kù)記錄了SQLServer系統(tǒng)的所有系統(tǒng)級(jí)別的信息，包括所有的登錄帳戶和密碼、系統(tǒng)的配置信息以及所有用戶定義數(shù)據(jù)庫(kù)的存儲(chǔ)位置、初始化信息。master是SQLServer中重要的數(shù)據(jù)庫(kù)，如果master數(shù)據(jù)庫(kù)受到損壞，SQLServer將不能啟動(dòng)。

model數(shù)據(jù)庫(kù)model數(shù)據(jù)庫(kù)是建立新數(shù)據(jù)庫(kù)的模板，它包含了將復(fù)制到每個(gè)新建數(shù)據(jù)庫(kù)中的系統(tǒng)表。

msdb數(shù)據(jù)庫(kù)該數(shù)據(jù)庫(kù)主要被SQLServerAgent用于進(jìn)行復(fù)制、作業(yè)調(diào)度以及管理報(bào)警等活動(dòng)。tempdb數(shù)據(jù)庫(kù)tempdb數(shù)據(jù)庫(kù)保存所有的臨時(shí)表、臨時(shí)數(shù)據(jù)以及臨時(shí)創(chuàng)建的存儲(chǔ)過(guò)程。這四個(gè)系統(tǒng)數(shù)據(jù)庫(kù)是運(yùn)行SQLServer的基礎(chǔ)。示例數(shù)據(jù)庫(kù)

pubs數(shù)據(jù)庫(kù)記錄一個(gè)虛擬的圖書出版公司的數(shù)據(jù)信息

northwind數(shù)據(jù)庫(kù)保存了一個(gè)虛擬的貿(mào)易公司的數(shù)據(jù)信息

（3）在“常規(guī)”選項(xiàng)卡中輸入數(shù)據(jù)庫(kù)的名稱，假設(shè)為Sales。輸入數(shù)據(jù)庫(kù)名稱（4）選擇“數(shù)據(jù)文件”選項(xiàng)卡，在該窗口中設(shè)置數(shù)據(jù)文件的屬性。數(shù)據(jù)文件的邏輯名

文件位置初始大小文件所在的文件組文件否允許自動(dòng)增長(zhǎng)

（5）選擇“事務(wù)日志”選項(xiàng)卡，在該窗口中可設(shè)置日志文件的屬性。

（6）單擊“確定”按鈕，數(shù)據(jù)庫(kù)創(chuàng)建完成。

2.2.2使用T-SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)的T-SQL語(yǔ)句的語(yǔ)法形式：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：新創(chuàng)建的數(shù)據(jù)庫(kù)的名稱。ON：指出用來(lái)存儲(chǔ)數(shù)據(jù)庫(kù)中數(shù)據(jù)部分的磁盤文件（數(shù)據(jù)文件）。PRIMARY：指定主文件組中的主文件。LOGON：指定用來(lái)存儲(chǔ)數(shù)據(jù)庫(kù)日志的磁盤文件。NAME=logical_file_name：指定數(shù)據(jù)文件或日志文件的邏輯名。FILENAME='os_file_name'：指定數(shù)據(jù)文件或日志文件的操作系統(tǒng)文件名，包括文件名和路徑。SIZE=size：指定數(shù)據(jù)文件或日志文件的初始大小，默認(rèn)單位為MB。MAXSIZE={max_size|UNLIMITED}：指定數(shù)據(jù)文件或日志文件可以增長(zhǎng)到的最大容量，默認(rèn)單位為MB。FILEGROWTH=growth_increment：指定數(shù)據(jù)文件或日志文件的增長(zhǎng)幅度，默認(rèn)單位為MB。例2-1創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)sample。主文件為sample_dat。CREATEDATABASEsampleONPRIMARY(NAME=sample_dat,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\sample_data.mdf',SIZE=5,MAXSIZE=50,FILEGROWTH=10)例2-3創(chuàng)建一個(gè)包含多個(gè)數(shù)據(jù)文件和日志文件的數(shù)據(jù)庫(kù)。該數(shù)據(jù)庫(kù)名為student，含有三個(gè)初始大小為10MB的數(shù)據(jù)文件和兩個(gè)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數(shù)據(jù)庫(kù)的管理2.3.1查看數(shù)據(jù)庫(kù)

1．用企業(yè)管理器查看數(shù)據(jù)庫(kù)的屬性

進(jìn)入SQLServer企業(yè)管理器，在左邊的目錄樹(shù)結(jié)構(gòu)中選中要查看的數(shù)據(jù)庫(kù)Sales，單擊右鍵，在彈出的快捷菜單上單擊“屬性”命令，即可進(jìn)入Sales數(shù)據(jù)庫(kù)的屬性窗口。該窗口共有6個(gè)選項(xiàng)卡，包括“常規(guī)”、“數(shù)據(jù)文件”、“事務(wù)日志”、“文件組”、“選項(xiàng)”及“權(quán)限”，我們可以根據(jù)需要進(jìn)入選擇不同的選項(xiàng)卡查看數(shù)據(jù)庫(kù)相應(yīng)的信息。

2．用T-SQL語(yǔ)句查看數(shù)據(jù)庫(kù)的屬性使用系統(tǒng)存儲(chǔ)過(guò)程sp_helpdb可以查看某個(gè)數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的屬性。格式：sp_helpdbdatabase_name該存儲(chǔ)過(guò)程顯示了Sales數(shù)據(jù)庫(kù)的名稱、大小、所有者、創(chuàng)建日期以及數(shù)據(jù)文件和日志文件等屬性。例2-5查看數(shù)據(jù)庫(kù)Sales的屬性。

sp_helpdbSales例2-6查看所有數(shù)據(jù)庫(kù)的屬性

sp_helpdb

2.3.2修改數(shù)據(jù)庫(kù)

1．用企業(yè)管理器修改數(shù)據(jù)庫(kù)屬性

進(jìn)入數(shù)據(jù)庫(kù)的屬性窗口，在屬性窗口中修改數(shù)據(jù)庫(kù)屬性。注意：在刪除文件組時(shí)要求該文件組中不包含任何數(shù)據(jù)文件，否則SQLServer會(huì)給出錯(cuò)誤信息2．用T-SQL語(yǔ)句修改數(shù)據(jù)庫(kù)屬性

其語(yǔ)法形式為：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為數(shù)據(jù)庫(kù)sample添加一個(gè)數(shù)據(jù)文件sample_dat2和一個(gè)日志文件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為數(shù)據(jù)庫(kù)sample添加一個(gè)文件組USER1，并向該文件組添加一個(gè)二個(gè)數(shù)據(jù)文件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從數(shù)據(jù)庫(kù)sample中刪除文件sampuser_dat2。ALTERDATABASEsampleREMOVEFILEsampuser_dat2例2-10刪除數(shù)據(jù)庫(kù)sample中的文件組USER1。ALTERDATABASEsampleREMOVEFILEsampuser_dat1ALTERDATABASEsampleREMOVEFILEGROUPUSER1注意：在刪除文件組時(shí)必須先刪除文件組中所包含的文件。

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

2.3.3重命名數(shù)據(jù)庫(kù)語(yǔ)法形式為：sp_renamedb‘old_name’,’new_name’old_name：數(shù)據(jù)庫(kù)的當(dāng)前名稱；new_name是數(shù)據(jù)庫(kù)的新名稱。例2-14將數(shù)據(jù)庫(kù)business更名為company。

sp_renamedb'business','company

2.3.4數(shù)據(jù)庫(kù)的收縮

SQLServer2000允許收縮數(shù)據(jù)庫(kù)中的每個(gè)文件以刪除未使用的頁(yè)。數(shù)據(jù)文件和事務(wù)日志文件都可以收縮。

1．自動(dòng)收縮（1）使用企業(yè)管理器設(shè)置自動(dòng)收縮數(shù)據(jù)庫(kù)在數(shù)據(jù)庫(kù)的“屬性對(duì)話框”中，點(diǎn)擊“選項(xiàng)”選項(xiàng)卡，選中“自動(dòng)收縮”復(fù)選框，即可完成數(shù)據(jù)庫(kù)自動(dòng)收縮的設(shè)置。

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

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

DBCCSHRINKDATABASE(sales,20)

還可以使用DBCC命令來(lái)縮小某一個(gè)操作系統(tǒng)文件的長(zhǎng)度，其語(yǔ)法形式為：DBCCSHRINKFILE

(file_name

{[,target_size]

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

)f