數(shù)據(jù)庫原理與應(yīng)用數(shù)據(jù)庫的創(chuàng)建和管理

數(shù)據(jù)庫原理與應(yīng)用數(shù)據(jù)庫的創(chuàng)建和管理第1頁/共35頁《數(shù)據(jù)庫原理與應(yīng)用》

第四講SQLServer2005數(shù)據(jù)庫的創(chuàng)建和管理第2頁/共35頁主要內(nèi)容SQLServer特點(diǎn)

SQLServer的安裝和配置

SQLServer工具和實(shí)用程序第3頁/共35頁4.1數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)

數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)分為邏輯存儲(chǔ)結(jié)構(gòu)和物理存儲(chǔ)結(jié)構(gòu)兩種。

數(shù)據(jù)庫的邏輯存儲(chǔ)結(jié)構(gòu)指的是數(shù)據(jù)庫是由哪些性質(zhì)的信息所組成。實(shí)際上，SQLServer的數(shù)據(jù)庫是由諸如表、視圖、索引等各種不同的數(shù)據(jù)庫對象所組成。數(shù)據(jù)庫的物理存儲(chǔ)結(jié)構(gòu)是討論數(shù)據(jù)庫文件是如何在磁盤上存儲(chǔ)的，數(shù)據(jù)庫在磁盤上是以文件為單位存儲(chǔ)的，由數(shù)據(jù)庫文件和事務(wù)日志文件組成，一個(gè)數(shù)據(jù)庫至少應(yīng)該包含一個(gè)數(shù)據(jù)庫文件和一個(gè)事務(wù)日志文件。

第4頁/共35頁4.1數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)4.1.1數(shù)據(jù)庫文件

1．主數(shù)據(jù)庫文件（PrimaryDatabaseFile）

一個(gè)數(shù)據(jù)庫可以有一個(gè)或多個(gè)數(shù)據(jù)庫文件，一個(gè)數(shù)據(jù)庫文件只能屬于一個(gè)數(shù)據(jù)庫。當(dāng)有多個(gè)數(shù)據(jù)庫文件時(shí)，有一個(gè)文件被定義為主數(shù)據(jù)庫文件（簡稱為主文件），其擴(kuò)展名為mdf。第5頁/共35頁4.1數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)主數(shù)據(jù)庫文件用來存儲(chǔ)數(shù)據(jù)庫的啟動(dòng)信息以及部分或者全部數(shù)據(jù)，是所有數(shù)據(jù)庫文件的起點(diǎn)，包含指向其它數(shù)據(jù)庫文件的指針。一個(gè)數(shù)據(jù)庫只能有一個(gè)主數(shù)據(jù)庫文件。

第6頁/共35頁4.1數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)2．輔助數(shù)據(jù)庫文件（SecondaryDatabaseFile）

用于存儲(chǔ)主數(shù)據(jù)庫文件中未存儲(chǔ)的剩余數(shù)據(jù)和數(shù)據(jù)庫對象，一個(gè)數(shù)據(jù)庫可以沒有輔助數(shù)據(jù)庫文件，但也可以同時(shí)擁有多個(gè)輔助數(shù)據(jù)庫文件。

輔助數(shù)據(jù)庫文件的擴(kuò)展名為ndf（簡稱為輔助文件）。

第7頁/共35頁4.1數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)3．事務(wù)日志文件

存儲(chǔ)數(shù)據(jù)庫的更新情況等事務(wù)日志信息，當(dāng)數(shù)據(jù)庫損壞時(shí)，管理員使用事務(wù)日志恢復(fù)數(shù)據(jù)庫。每一個(gè)數(shù)據(jù)庫至少必須擁有一個(gè)事務(wù)日志文件，而且允許擁有多個(gè)日志文件。事務(wù)日志文件的擴(kuò)展名為ldf，日志文件的大小至少是512KB。

SQLServer事務(wù)日志采用提前寫入的方式。第8頁/共35頁4.1數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)注意：SQLServer2000中的數(shù)據(jù)和事務(wù)日志文件不能存放在壓縮文件系統(tǒng)或象共享網(wǎng)絡(luò)目錄等遠(yuǎn)程的網(wǎng)絡(luò)驅(qū)動(dòng)器上。

SQLServer2000的文件擁有兩個(gè)名稱，即邏輯文件名和物理文件名。當(dāng)使用Transact-SQL命令語句訪問某一個(gè)文件時(shí)，必須使用該文件的邏輯名。物理文件名是文件實(shí)際存儲(chǔ)在磁盤上的文件名，而且可包含完整的磁盤目錄路徑。

第9頁/共35頁4.1數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)4.1.2數(shù)據(jù)庫文件組

利用文件組可以使服務(wù)器的性能得到提高。

主文件組中包含了所有的系統(tǒng)表，當(dāng)建立數(shù)據(jù)庫時(shí)，主文件組包括主數(shù)據(jù)庫文件和未指定組的其他文件。用戶定義文件組中可以指定一個(gè)缺省文件組，那么在創(chuàng)建數(shù)據(jù)庫對象時(shí)如果沒有指定將其放在哪一個(gè)文件組中，就會(huì)將它放在缺省文件組中。如果沒有指定缺省文件組，則主文件組為缺省文件組。第10頁/共35頁4.1數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)一個(gè)文件只能存在于一個(gè)文件組中，一個(gè)文件組也只能被一個(gè)數(shù)據(jù)庫使用；日志文件是獨(dú)立的，它不能作為任何文件組的成員。

本章首頁第11頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫

4.2.1創(chuàng)建數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫需要一定許可，在默認(rèn)情況下，只有系統(tǒng)管理員和數(shù)據(jù)庫擁有者可以創(chuàng)建數(shù)據(jù)庫。數(shù)據(jù)庫被創(chuàng)建后，創(chuàng)建數(shù)據(jù)庫的用戶自動(dòng)成為該數(shù)據(jù)庫的所有者。

創(chuàng)建數(shù)據(jù)庫的過程實(shí)際上就是為數(shù)據(jù)庫設(shè)計(jì)名稱、設(shè)計(jì)所占用的存儲(chǔ)空間和存放文件位置的過程等。第12頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫1．使用向?qū)?chuàng)建數(shù)據(jù)庫

2．使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫

3．使用Transact-SQL語言創(chuàng)建數(shù)據(jù)庫第13頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫說明：在Transact-SQL語言的命令格式中，用[]括起來的內(nèi)容表示是可選的；[，…n]表示重復(fù)前面的內(nèi)容；用<>括起來表示在實(shí)際編寫語句時(shí)，用相應(yīng)的內(nèi)容替代；用{}括起來表示是必選的；類似A|B的格式，表示A和B只能選擇一個(gè)，不能同時(shí)都選。

第14頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫CREATEDATABASEdatabase_name[ON[PRIMARY][<filespec>[，…n][，<filegroupspec>[，…n]]][LOGON{<filespec>[，…n]}][FORLOAD|FORATTACH]

第15頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫<filespec>::=（[NAME=logical_file_name，]FILENAME=‘os_file_name’[，SIZE=size][，MAXSIZE={max_size|UNLIMITED}][，F(xiàn)ILEGROWTH=growth_increment]）

[，…n]

<filegroupspec>::=FILEGROUPfilegroup_name<filespec>[，…n]

第16頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫database_name：新數(shù)據(jù)庫的名稱。數(shù)據(jù)庫名稱在服務(wù)器中必須唯一，最長為128個(gè)字符，并且要符合標(biāo)識(shí)符的命名規(guī)則。每個(gè)服務(wù)器管理的數(shù)據(jù)庫最多為32767個(gè)。ON：指定存放數(shù)據(jù)庫的數(shù)據(jù)文件信息。<filespec>列表用于定義主文件組的數(shù)據(jù)文件，<filegroup>列表用于定義用戶文件組及其中的文件。PRIMARY：用于指定主文件組中的文件。主文件組的第一個(gè)由<filespec>指定的文件是主文件。如果不指定PRIMARY關(guān)鍵字，則在命令中列出的第一個(gè)文件將被默認(rèn)為主文件。第17頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫LOGON：指明事務(wù)日志文件的明確定義。如果沒有本選項(xiàng)，則系統(tǒng)會(huì)自動(dòng)產(chǎn)生一個(gè)文件名前綴與數(shù)據(jù)庫名相同，容量為所有數(shù)據(jù)庫文件大小1/4的事務(wù)日志文件。FORLOAD：表示計(jì)劃將備份直接裝入新建的數(shù)據(jù)庫，主要是為了和過去的SQLServer版本兼容。FORATTACH：表示在一組已經(jīng)存在的操作系統(tǒng)文件中建立一個(gè)新的數(shù)據(jù)庫。NAME：指定數(shù)據(jù)庫的邏輯名稱。FILENAME：指定數(shù)據(jù)庫所在文件的操作系統(tǒng)文件名稱和路徑，該操作系統(tǒng)文件名和NAME的邏輯名稱一一對應(yīng)。第18頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫SIZE：指定數(shù)據(jù)庫的初始容量大小。如果沒有指定主文件的大小，則SQLServer默認(rèn)其與模板數(shù)據(jù)庫中的主文件大小一致，其它數(shù)據(jù)庫文件和事務(wù)日志文件則默認(rèn)為1MB。指定大小的數(shù)字size可以使用KB、MB、GB和TB后綴，默認(rèn)的后綴為MB。Size中不能使用小數(shù)，其最小值為512KB，默認(rèn)值為1MB。主文件的size不能小于模板數(shù)據(jù)庫中的主文件。MAXSIZE：指定操作系統(tǒng)文件可以增長到的最大尺寸。如果沒有指定，則文件可以不斷增長直到充滿磁盤。第19頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫FILEGROWTH：指定文件每次增加容量的大小，當(dāng)指定數(shù)據(jù)為0時(shí)，表示文件不增長。增加量可以確定為以KB、MB作后綴的字節(jié)數(shù)或以%作后綴的被增加容量文件的百分比來表示。默認(rèn)后綴為MB。如果沒有指定FILEGROWTH，則默認(rèn)值為10%，每次擴(kuò)容的最小值為64KB。

第20頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫例4-2-1：使用CREATEDATABASE創(chuàng)建一個(gè)student數(shù)據(jù)庫，所有參數(shù)均取默認(rèn)值。

例4-2-2：創(chuàng)建一個(gè)Student1數(shù)據(jù)庫，該數(shù)據(jù)庫的主文件邏輯名稱為Student1_data，物理文件名為Student1.mdf，初始大小為10MB，最大尺寸為無限大，增長速度為10%；數(shù)據(jù)庫的日志文件邏輯名稱為Student1_log，物理文件名為Student1.ldf，初始大小為1MB，最大尺寸為5MB，增長速度為1MB。

第21頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫例4-2-3：創(chuàng)建一個(gè)指定多個(gè)數(shù)據(jù)文件和日志文件的數(shù)據(jù)庫。該數(shù)據(jù)庫名稱為students，有1個(gè)10MB和1個(gè)20MB的數(shù)據(jù)文件和2個(gè)10MB的事務(wù)日志文件。數(shù)據(jù)文件邏輯名稱為student1和student2，物理文件名為student1.mdf和student2.mdf。主文件是student1，由primary指定，兩個(gè)數(shù)據(jù)文件的最大尺寸分別為無限大和100MB，增長速度分別為10%和1MB。事務(wù)日志文件的邏輯名為studentlog1和studentlog2，物理文件名為studentlog1.ldf和studentlog2.ldf，最大尺寸均為50MB，文件增長速度為1MB。

第22頁/共35頁4.2創(chuàng)建和刪除數(shù)據(jù)庫4.2.2刪除數(shù)據(jù)庫

1．利用企業(yè)管理器刪除數(shù)據(jù)庫

2．利用Drop語句刪除數(shù)據(jù)庫

Dropdatabasedatabase_name[,…n]說明：只有處于正常狀態(tài)下的數(shù)據(jù)庫，才能使用DROP語句刪除。當(dāng)數(shù)據(jù)庫處于以下狀態(tài)時(shí)不能被刪除：數(shù)據(jù)庫正在使用；數(shù)據(jù)庫正在恢復(fù)；數(shù)據(jù)庫包含用于復(fù)制的已經(jīng)出版的對象。

本章首頁第23頁/共35頁4.3修改數(shù)據(jù)庫

4.3.1數(shù)據(jù)庫更名

在重命名數(shù)據(jù)庫之前，應(yīng)該確保沒有用戶使用該數(shù)據(jù)庫，而且數(shù)據(jù)庫應(yīng)該設(shè)置為“單用戶”模式。系統(tǒng)存儲(chǔ)過程sp_renamedb語法如下：sp_renamedb[@dbname=]'old_name',[@newname=]'new_name'第24頁/共35頁4.3修改數(shù)據(jù)庫4.3.2利用企業(yè)管理器修改數(shù)據(jù)庫屬性

限制訪問包含兩個(gè)選項(xiàng)：“db_owner,dbcreator或sysadmin的成員”選項(xiàng)表示只有數(shù)據(jù)庫的所有者、數(shù)據(jù)庫創(chuàng)建者和系統(tǒng)管理員才有權(quán)使用數(shù)據(jù)庫；“單用戶”

選項(xiàng)表示數(shù)據(jù)庫在同一時(shí)間只能供一個(gè)用戶使用。ANSINULL默認(rèn)設(shè)置：允許在數(shù)據(jù)庫表的列中輸入空（NULL）值。遞歸觸發(fā)器：指定是否允許觸發(fā)器遞歸調(diào)用。自動(dòng)更新統(tǒng)計(jì)信息：允許使用SELECTINTO或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入數(shù)據(jù)。在開發(fā)數(shù)據(jù)庫時(shí)常常將此選項(xiàng)設(shè)置為真。第25頁/共35頁4.3修改數(shù)據(jù)庫殘缺頁檢測：允許自動(dòng)檢測有損壞的頁。頁是數(shù)據(jù)庫內(nèi)容的基本存儲(chǔ)單位，每個(gè)頁的大小為8KB。由于SQLServer對頁的讀寫單位是大小為512字節(jié)的扇區(qū)，當(dāng)每個(gè)頁的第一個(gè)扇區(qū)被成功寫入后，SQLServer就認(rèn)為此頁已經(jīng)被成功寫入。因此，如果寫完第一個(gè)扇區(qū)后發(fā)生突發(fā)事件，導(dǎo)致寫入中斷，就會(huì)產(chǎn)生有損壞的頁，需要通知備份來恢復(fù)數(shù)據(jù)庫。自動(dòng)關(guān)閉：當(dāng)數(shù)據(jù)庫中無用戶時(shí)，自動(dòng)關(guān)閉該數(shù)據(jù)庫，并將所占用的資源交還給操作系統(tǒng)。對那些不間斷使用的數(shù)據(jù)庫不要使用此選項(xiàng)。第26頁/共35頁4.3修改數(shù)據(jù)庫自動(dòng)收縮：允許定期對數(shù)據(jù)庫進(jìn)行檢查，當(dāng)數(shù)據(jù)庫文件或日志文件中未用空間超過其大小的25%時(shí)，系統(tǒng)將會(huì)自動(dòng)縮減文件，使其未用空間等于25%。當(dāng)文件大小沒有超過其建立時(shí)的初始大小時(shí)，不會(huì)縮減文件?？s減后的文件也必須大于或等于其初始大小。自動(dòng)創(chuàng)建統(tǒng)計(jì)信息：在優(yōu)化查詢時(shí)，根據(jù)需要自動(dòng)創(chuàng)建統(tǒng)計(jì)信息。使用被引用的標(biāo)識(shí)符：標(biāo)識(shí)符必須用雙引號(hào)括起來，且可以不遵循Transact-SQL命名標(biāo)準(zhǔn)。

第27頁/共35頁4.3修改數(shù)據(jù)庫4.3.3使用Transact-SQL語言修改數(shù)據(jù)庫

只有數(shù)據(jù)庫管理員或具有CREATEDATABASE權(quán)限的數(shù)據(jù)庫所有者才有權(quán)執(zhí)行該語句。ALTERDATABASE語句的語法格式如下：第28頁/共35頁4.3修改數(shù)據(jù)庫Alterdatabasedatabasename{addfile<filespec>[,…n][tofilegroupfilegroupname]|addlogfile<filespec>[,…n]|removefilelogical_file_name|removefilegroupfilegroup_name

|modifyfile<filespec>|modifyname=new_databasename|addfilegroupfilegroup_name|modifyfilegroupfilegroup_name{filegroup_property|name=new_filegroup_name}}第29頁/共35頁4.3修改數(shù)據(jù)庫addfile<filespec>[,…n][tofilegroupfilegroupname]：表示向指定的文件組中添加新的數(shù)據(jù)文件。addlogfile<filespec>[,…n]：增加新的日志文件。removefilelogical_file_name：刪除指定的操作系統(tǒng)文件。removefilegroupfilegroup_name：刪除指定的文件組。modifyfile<filespec>：修改某個(gè)操作系統(tǒng)文件。第30頁/共35頁4.3修改數(shù)據(jù)庫modifyname=new_databasename：重命名數(shù)據(jù)庫。addfilegroupfilegroup_name：增加一個(gè)文件組。modifyfilegroupfilegroup_name：修改某個(gè)指定文件組的屬性。

第31頁