第3章 數(shù)據(jù)庫創(chuàng)建與維護(hù)

李政偉數(shù)據(jù)庫應(yīng)用李政偉第3章

數(shù)據(jù)庫的創(chuàng)建與管理3.1SQLServer數(shù)據(jù)庫概述3.2創(chuàng)建數(shù)據(jù)庫3.3查看和設(shè)置數(shù)據(jù)庫選項(xiàng)3.4修改數(shù)據(jù)庫3.5刪除數(shù)據(jù)庫3.6分離和附加數(shù)據(jù)庫3.7復(fù)制數(shù)據(jù)庫2/39數(shù)據(jù)庫(Database，DB)：將數(shù)據(jù)按一定的數(shù)據(jù)模型組織、描述和儲(chǔ)存，具有較小的冗余度，較高的數(shù)據(jù)獨(dú)立性和易擴(kuò)展性，并可被多用戶、多應(yīng)用程序共享的數(shù)據(jù)集合。（1）在計(jì)算機(jī)的存儲(chǔ)設(shè)備上存放的，屬于計(jì)算機(jī)領(lǐng)域的一個(gè)術(shù)語。（2）一個(gè)數(shù)據(jù)集合。（3）這個(gè)數(shù)據(jù)集合是有結(jié)構(gòu)的，這是和文件系統(tǒng)相比最大的特點(diǎn)之一。（4）這個(gè)數(shù)據(jù)集合是指相關(guān)聯(lián)的數(shù)據(jù)集合。（5）這個(gè)數(shù)據(jù)集合是合理存放的。3.1SQLServer數(shù)據(jù)庫概述3.1SQLServer數(shù)據(jù)庫概述兩大類：系統(tǒng)數(shù)據(jù)庫：存放維護(hù)系統(tǒng)正常運(yùn)行的信息。用戶數(shù)據(jù)庫:存放用戶自己的信息。4/39系統(tǒng)數(shù)據(jù)庫Master：最重要數(shù)據(jù)庫，用于記錄所有系統(tǒng)級(jí)信息。包括所有的登錄信息、系統(tǒng)設(shè)置信息、初始化信息和其他系統(tǒng)數(shù)據(jù)庫及用戶數(shù)據(jù)庫的相關(guān)信息。如果master數(shù)據(jù)庫不可用，則SQLServer無法啟動(dòng)。Model：用作所有數(shù)據(jù)庫的模板。當(dāng)用戶創(chuàng)建數(shù)據(jù)庫時(shí)，系統(tǒng)自動(dòng)將model數(shù)據(jù)庫中的內(nèi)容復(fù)制到新建數(shù)據(jù)庫中。當(dāng)發(fā)出CREATEDATABASE（創(chuàng)建數(shù)據(jù)庫）時(shí)，將通過復(fù)制model數(shù)據(jù)庫的內(nèi)容來創(chuàng)建數(shù)據(jù)庫的第一部分，然后用空頁填充新數(shù)據(jù)庫的剩余部分。Msdb：保存關(guān)于報(bào)警、作業(yè)、操作員等信息。5/39系統(tǒng)數(shù)據(jù)庫Tempdb：臨時(shí)數(shù)據(jù)庫，用于存儲(chǔ)用戶創(chuàng)建的臨時(shí)表、用戶聲明的變量以及用戶定義的游標(biāo)數(shù)據(jù)等。Tempdb數(shù)據(jù)庫由整個(gè)系統(tǒng)的所有數(shù)據(jù)庫使用，不管用戶使用哪個(gè)數(shù)據(jù)庫，他們所建立的所有臨時(shí)表和存儲(chǔ)過程都存儲(chǔ)在tempdb上。SQLServer每次啟動(dòng)時(shí)，tempdb數(shù)據(jù)庫被重新建立。當(dāng)用戶與SQLServer斷開連接時(shí)，其臨時(shí)表和存儲(chǔ)過程自動(dòng)被刪除。Resource：一個(gè)只讀數(shù)據(jù)庫，包含SQLServer包括的系統(tǒng)對(duì)象。系統(tǒng)對(duì)象在物理上保留在Resource數(shù)據(jù)庫中，但在邏輯上顯示在每個(gè)數(shù)據(jù)庫的sys架構(gòu)中。SQLSERVER樣本數(shù)據(jù)庫AdventureWorks、AdventureWorksDW等示例數(shù)據(jù)庫，示例數(shù)據(jù)基于一個(gè)虛擬的公司AdventureWorksCycles，這是一個(gè)大型的跨國制造公司，生產(chǎn)金屬和復(fù)合材料自行車，產(chǎn)品遠(yuǎn)銷北美、歐洲和亞洲市場。AdventureWorks中的某些表的結(jié)構(gòu)和內(nèi)容與SQLServer2000中Pubs和Northwind數(shù)據(jù)庫的表結(jié)構(gòu)和內(nèi)容相似，對(duì)照聯(lián)機(jī)幫助上的對(duì)應(yīng)關(guān)系可以很方便的將SQLServer2000中使用Northwind和Pubs數(shù)據(jù)庫的相關(guān)查詢轉(zhuǎn)換到AdventureWorks中來。3.1.2SQLServer數(shù)據(jù)庫的組成SQLServer將數(shù)據(jù)庫映射為一組操作系統(tǒng)文件，這些文件被劃分為兩類：數(shù)據(jù)文件和日志文件。數(shù)據(jù)文件包含數(shù)據(jù)和對(duì)象，例如表、索引、存儲(chǔ)過程和視圖等。日志文件包含恢復(fù)數(shù)據(jù)庫中的所有事務(wù)需要的信息。數(shù)據(jù)和日志信息不混合在同一個(gè)文件中，一個(gè)文件只由一個(gè)數(shù)據(jù)庫使用。8/391.數(shù)據(jù)文件用于存放數(shù)據(jù)庫數(shù)據(jù)。數(shù)據(jù)文件又分為：主要數(shù)據(jù)文件：推薦擴(kuò)展名是.mdf。包含數(shù)據(jù)庫的系統(tǒng)信息，也可存放用戶數(shù)據(jù)。每個(gè)數(shù)據(jù)庫都有且只能有一個(gè)主要數(shù)據(jù)文件。是為數(shù)據(jù)庫創(chuàng)建的第一個(gè)數(shù)據(jù)文件。不能小于3MB。次要數(shù)據(jù)文件：推薦擴(kuò)展名是.ndf。一個(gè)數(shù)據(jù)庫可以包含0~n個(gè)次要數(shù)據(jù)文件?？膳c主要數(shù)據(jù)文件存放在不同地方。所有文件的使用對(duì)用戶也是透明的。9/392.日志文件推薦擴(kuò)展名為.ldf。用于存放恢復(fù)數(shù)據(jù)庫的所有日志信息。每個(gè)數(shù)據(jù)庫有1~n個(gè)日志文件。10/393．關(guān)于數(shù)據(jù)的存儲(chǔ)分配數(shù)據(jù)庫中數(shù)據(jù)的存儲(chǔ)分配單位是數(shù)據(jù)頁。一頁是一塊8KB（其中用8060個(gè)字節(jié)存放數(shù)據(jù)，另外132個(gè)字節(jié)存放系統(tǒng)信息）的連續(xù)磁盤空間。頁是存儲(chǔ)數(shù)據(jù)的最小空間分配單位。不允許表中的一行數(shù)據(jù)存儲(chǔ)在不同頁上（varchar(max)、nvarchar(max)、text、ntext、varbinary(max)和image數(shù)據(jù)類型除外），即行不能跨頁存儲(chǔ)。日志文件不包含頁，而是包含一系列日志記錄。11/39示例假設(shè)某數(shù)據(jù)表有10000行數(shù)據(jù)，每行3000字節(jié)，估算其需要的空間:

(10000/2)*8KB=40MB其中，每頁中有6000字節(jié)用于存儲(chǔ)數(shù)據(jù)，有2060個(gè)字節(jié)是浪費(fèi)的。12/393.1.3數(shù)據(jù)庫文件組為便于分配和管理數(shù)據(jù)文件，將相關(guān)的數(shù)據(jù)文件組織起來，放置到一個(gè)文件組中。文件組的概念類似于操作系統(tǒng)中的文件夾。在一個(gè)數(shù)據(jù)庫中可以定義多個(gè)文件組，然后將數(shù)據(jù)文件放置到不同的文件組中。SQLServer有兩種類型的文件組：主文件組用戶定義的文件組13/39主文件組（PRIMARY）系統(tǒng)預(yù)先定義好的一個(gè)文件組，包含主要數(shù)據(jù)文件和任何沒有明確分配給其他文件組的其他文件。系統(tǒng)表的所有頁均分配在主文件組中。14/39用戶定義文件組用戶可以創(chuàng)建自己的文件組，以將相關(guān)數(shù)據(jù)文件組織起來，便于管理和數(shù)據(jù)分配。通過在CREATEDATABASE或ALTERDATABASE語句中使用FILEGROUP關(guān)鍵字定義。說明：日志文件不包括在文件組內(nèi)，一個(gè)文件不可以是多個(gè)文件組的成員。文件組用途：例如，可以分別在三個(gè)磁盤驅(qū)動(dòng)器上創(chuàng)建三個(gè)文件Data1.ndf、Data2.ndf和Data3.ndf，然后將它們分配給文件組fgroup1。然后，可以明確地在文件組fgroup1上創(chuàng)建一個(gè)表。對(duì)表中數(shù)據(jù)的查詢將分散到三個(gè)磁盤上，從而提高了性能。15/39使用文件和文件組的一般建議使用文件組將對(duì)象放置在特定的物理磁盤上。為性能最大化，應(yīng)在盡可能多的不同可用物理磁盤上創(chuàng)建文件和文件組，將爭奪空間最激烈的對(duì)象置于不同的文件組中。將同一連接查詢中使用的不同表置于不同文件組中。將最經(jīng)常訪問的表和屬于這些表的非聚集索引置于不同的文件組中。不要將事務(wù)日志文件和其他文件或文件組置于同一磁盤上。3.1.4數(shù)據(jù)庫文件的屬性文件名及其位置：每個(gè)文件都有一個(gè)邏輯文件名和物理文件名及物理存儲(chǔ)位置初始大?。嚎梢灾付總€(gè)文件的初始大小。增長方式：可以指定文件是否自動(dòng)增長最大大小：可以指定文件增長的最大空間限制17/393.2創(chuàng)建數(shù)據(jù)庫創(chuàng)建用戶數(shù)據(jù)庫前，須先確定數(shù)據(jù)庫名、所有者、文件名、數(shù)據(jù)文件大小、數(shù)據(jù)庫字符集、是否自動(dòng)增長以及如何自動(dòng)增長等信息。在一個(gè)SQLServer實(shí)例中，最多可創(chuàng)建32767個(gè)數(shù)據(jù)庫。數(shù)據(jù)庫名稱必須滿足標(biāo)識(shí)符規(guī)則。在命名數(shù)據(jù)庫時(shí)，一定要保證數(shù)據(jù)庫名稱簡短并有一定的含義。3.2.1用圖形化方法創(chuàng)建數(shù)據(jù)庫3.2.2用T-SQL語句創(chuàng)建數(shù)據(jù)庫18/393.2.1用圖形化方法創(chuàng)建數(shù)據(jù)庫19/393.2.2用T-SQL語句創(chuàng)建數(shù)據(jù)庫CREATEDATABASEdatabase_name

[ON[PRIMARY][<filespec>[,...n][LOGON{<filespec>[,...n]}]]]

<filespec>::={(NAME=logical_file_name,FILENAME={'os_file_name'|'filestream_path'}[,SIZE=size[KB|MB|GB|TB]][,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]])[,...n]}20/39示例1(P66)例1．創(chuàng)建一個(gè)名為“mytest”的數(shù)據(jù)庫，其他選項(xiàng)均采用默認(rèn)設(shè)置。 CREATEDATABASEmytest21/39示例2(P66)CREATEDATABASERShDBON(NAME=RShDB,FILENAME='D:\RShDB_Data\RShDB.mdf',SIZE=10,MAXSIZE=30,FILEGROWTH=5)LOGON(NAME=RShDB_log,FILENAME='D:\RShDB_Data\RShDB_log.ldf',SIZE=3,MAXSIZE=12,FILEGROWTH=2)22/39示例3(P67)CREATEDATABASEstudentsONPRIMARY(NAME=students,FILENAME='F:\Data\students.mdf',SIZE=3MB,MAXSIZE=UNLIMITED),(NAME=students_data1,FILENAME='D:\Data\students_data1.ndf',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOGON(NAME=students_log,FILENAME='F:\Data\students_log.ldf',SIZE=2MB,MAXSIZE=6MB,FILEGROWTH=10%)23/393.3查看和設(shè)置數(shù)據(jù)庫選項(xiàng)展開“數(shù)據(jù)庫”節(jié)點(diǎn)，在要查看屬性的數(shù)據(jù)庫上右擊鼠標(biāo)，然后在彈出的快捷菜單中選擇“屬性”命令。24/393.4修改數(shù)據(jù)庫3.4.1擴(kuò)大數(shù)據(jù)庫空間3.4.2收縮數(shù)據(jù)庫空間3.4.3添加和刪除數(shù)據(jù)庫文件3.4.4創(chuàng)建和更改文件組25/393.4.1擴(kuò)大數(shù)據(jù)庫空間ALTERDATABASEdatabase_name{

<add_or_modify_files>}<add_or_modify_files>::={

ADDFILE<filespec>[,...n]

[TOFILEGROUP{filegroup_name

|DEFAULT}]

|ADDLOGFILE<filespec>[,...n]

|MODIFYFILE<filespec>26/39示例例1．為“RShDB”數(shù)據(jù)庫添加一個(gè)新的數(shù)據(jù)文件，邏輯文件名為：RShDB_Data2，物理存儲(chǔ)位置為：E:\Data文件夾下，物理文件名為：RShDB_Data2.ndf,初始大小為6MB，不自動(dòng)增長。ALTERDATABASERShDBADDFILE(NAME=RShDB_Data2,FILENAME='E:\Data\RShDB_Data2.ndf',SIZE=6MB,FILEGROWTH=0)27/39示例例2．修改Students數(shù)據(jù)庫中主數(shù)據(jù)文件Students的初始大小，將其初始大小改為8MB。ALTERDATABASEStudentsMODIFYFILE(NAME=students,SIZE=8MB)28/39示例例3．為Students數(shù)據(jù)庫加添加一個(gè)新的日志文件，邏輯文件名為：students_log1，物理存儲(chǔ)位置為：D:\Data文件夾下，物理文件名為：students_log1.ldf,初始大小為2MB，每次增加1MB，最多增加到10MB。ALTERDATABASEStudentsADDLOGFILE(NAME=students_log1,FILENAME='D:\Data\students_log1.ldf',SIZE=2MB,FILEGROWTH=1MB,MAXSIZE=10MB)29/393.4.2收縮數(shù)據(jù)庫空間自動(dòng)收縮手工收縮用圖形化方法用T-SQL語句30/39收縮數(shù)據(jù)庫DBCCSHRINKDATABASE('database_name'|0[,target_percent][,{NOTRUNCATE|TRUNCATEONLY}])31/39收縮文件DBCCSHRINKFILE('file_name'{[,EMPTYFILE]|[[,target_size][,{NOTRUNCATE|TRUNCATEONLY}]]})32/39示例例4．收縮Students數(shù)據(jù)庫，使該數(shù)據(jù)庫中所有的文件都有20%的可用空間。 DBCCSHRINKDATABASE（students,20）例5．將Students數(shù)據(jù)庫中的students_data1文件收縮到4MB。DBCCSHRINKFILE(students_data1,4)33/39數(shù)據(jù)庫文件使用情況34/39添加和刪除數(shù)據(jù)庫文件添加數(shù)據(jù)庫文件使用ALTERDATABASE語句。刪除數(shù)據(jù)庫文件ALTERDATABASEdatabase_nameREMOVEFILElogical_file_name只有當(dāng)文件中沒有數(shù)據(jù)或事務(wù)日志信息，文件完全為空時(shí)，才可以從數(shù)據(jù)庫中刪除該文件。35/39示例例6．刪除Students數(shù)據(jù)庫中的Students_data1文件。ALTERDATABASEStudentsREMOVEFILEStudents_data1例7．刪除Students數(shù)據(jù)庫中的Students_log1文件。ALTERDATABASEStudentsREMOVEFILEStudents_log136/393.4.4創(chuàng)建和更改文件組用圖形化方法：在數(shù)據(jù)庫屬性窗口中；用T-SQL語句。37/39創(chuàng)建和更改文件組語句ALTERDATABASEdatabase_name

{|ADDFILEGROUPfilegroup_name

|REMOVEFILEGROUPfilegroup_name

|MODIFYFILEGROUPfilegroup_name{<filegroup_updatability_option>|DEFAULT|NAME=new_filegroup_name

}}<filegroup_updatability_option>::={{READ_ONLY|READ_WRITE}}38/39示例例1．為students數(shù)據(jù)庫定義一個(gè)新文件組，文件組名為：NewFileGroup1，同時(shí)在該文件組中新添加兩個(gè)數(shù)據(jù)文件，邏輯名分別為：students_dat1和students_dat2，初始大小分別為：4MB和6MB，均存放在D:\Data文件夾下，不自動(dòng)增長。（1）創(chuàng)建文件組ALTERDATABASEStudentsADDFILEGROUPNewFileGroup1（2）添加新數(shù)據(jù)文件ALTERDATABASEstudentsADDFILE(NAME=students_dat1,FILENAME='D:\Data\students_dat1.ndf',SIZE=4MB,FILEGROWTH=0),(NAME=students_dat2,FILENAME='D:\Data\students_dat2.ndf',SIZE=6MB,FILEGROWTH=0)TOFILEGROUPNewFileGroup139/39示例例2．將Students數(shù)據(jù)庫中的NewFileGroup1文件組設(shè)為默認(rèn)文件組。ALTERDATABASEStudentsMODIFYFILEGROUPNewFileGroup1DEFAULT40/393.5刪除數(shù)據(jù)庫DROPDATABASEdatabase_name[,…n]例1．刪除單個(gè)數(shù)