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

第2章

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2.2.2使用T-SQL語句創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫的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：新創(chuàng)建的數(shù)據(jù)庫的名稱。ON：指出用來存儲數(shù)據(jù)庫中數(shù)據(jù)部分的磁盤文件（數(shù)據(jù)文件）。PRIMARY：指定主文件組中的主文件。LOGON：指定用來存儲數(shù)據(jù)庫日志的磁盤文件。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ù)文件或日志文件可以增長到的最大容量，默認(rèn)單位為MB。FILEGROWTH=growth_increment：指定數(shù)據(jù)文件或日志文件的增長幅度，默認(rèn)單位為MB。例2-1創(chuàng)建一個數(shù)據(jù)庫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)建一個包含多個數(shù)據(jù)文件和日志文件的數(shù)據(jù)庫。該數(shù)據(jù)庫名為student，含有三個初始大小為10MB的數(shù)據(jù)文件和兩個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ù)庫的管理2.3.1查看數(shù)據(jù)庫

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

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

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

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

sp_helpdb

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

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

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

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

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

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

sp_renamedb'business','company

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

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

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

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

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

DBCCSHRINKDATABASE(sales,20)

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

(file_name

{[,target_size]

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

)f