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

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

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

(10000/2)*8KB=40MB其中，每頁中有6000字節(jié)用于存儲數(shù)據(jù)，有2060個字節(jié)是浪費的。12/393.1.3數(shù)據(jù)庫文件組為便于分配和管理數(shù)據(jù)文件，將相關(guān)的數(shù)據(jù)文件組織起來，放置到一個文件組中。文件組的概念類似于操作系統(tǒng)中的文件夾。在一個數(shù)據(jù)庫中可以定義多個文件組，然后將數(shù)據(jù)文件放置到不同的文件組中。SQLServer有兩種類型的文件組：主文件組用戶定義的文件組13/39主文件組（PRIMARY）系統(tǒng)預先定義好的一個文件組，包含主要數(shù)據(jù)文件和任何沒有明確分配給其他文件組的其他文件。系統(tǒng)表的所有頁均分配在主文件組中。14/39用戶定義文件組用戶可以創(chuàng)建自己的文件組，以將相關(guān)數(shù)據(jù)文件組織起來，便于管理和數(shù)據(jù)分配。通過在CREATEDATABASE或ALTERDATABASE語句中使用FILEGROUP關(guān)鍵字定義。說明：日志文件不包括在文件組內(nèi)，一個文件不可以是多個文件組的成員。文件組用途：例如，可以分別在三個磁盤驅(qū)動器上創(chuàng)建三個文件Data1.ndf、Data2.ndf和Data3.ndf，然后將它們分配給文件組fgroup1。然后，可以明確地在文件組fgroup1上創(chuàng)建一個表。對表中數(shù)據(jù)的查詢將分散到三個磁盤上，從而提高了性能。15/39使用文件和文件組的一般建議使用文件組將對象放置在特定的物理磁盤上。為性能最大化，應(yīng)在盡可能多的不同可用物理磁盤上創(chuàng)建文件和文件組，將爭奪空間最激烈的對象置于不同的文件組中。將同一連接查詢中使用的不同表置于不同文件組中。將最經(jīng)常訪問的表和屬于這些表的非聚集索引置于不同的文件組中。不要將事務(wù)日志文件和其他文件或文件組置于同一磁盤上。3.1.4數(shù)據(jù)庫文件的屬性文件名及其位置：每個文件都有一個邏輯文件名和物理文件名及物理存儲位置初始大?。嚎梢灾付總€文件的初始大小。增長方式：可以指定文件是否自動增長最大大?。嚎梢灾付ㄎ募鲩L的最大空間限制17/393.2創(chuàng)建數(shù)據(jù)庫創(chuàng)建用戶數(shù)據(jù)庫前，須先確定數(shù)據(jù)庫名、所有者、文件名、數(shù)據(jù)文件大小、數(shù)據(jù)庫字符集、是否自動增長以及如何自動增長等信息。在一個SQLServer實例中，最多可創(chuàng)建32767個數(shù)據(jù)庫。數(shù)據(jù)庫名稱必須滿足標識符規(guī)則。在命名數(shù)據(jù)庫時，一定要保證數(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)建一個名為“mytest”的數(shù)據(jù)庫，其他選項均采用默認設(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ù)庫選項展開“數(shù)據(jù)庫”節(jié)點，在要查看屬性的數(shù)據(jù)庫上右擊鼠標，然后在彈出的快捷菜單中選擇“屬性”命令。24/393.4修改數(shù)據(jù)庫3.4.1擴大數(shù)據(jù)庫空間3.4.2收縮數(shù)據(jù)庫空間3.4.3添加和刪除數(shù)據(jù)庫文件3.4.4創(chuàng)建和更改文件組25/393.4.1擴大數(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ù)庫添加一個新的數(shù)據(jù)文件，邏輯文件名為：RShDB_Data2，物理存儲位置為：E:\Data文件夾下，物理文件名為：RShDB_Data2.ndf,初始大小為6MB，不自動增長。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ù)庫加添加一個新的日志文件，邏輯文件名為：students_log1，物理存儲位置為：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ù)庫空間自動收縮手工收縮用圖形化方法用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只有當文件中沒有數(shù)據(jù)或事務(wù)日志信息，文件完全為空時，才可以從數(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ù)庫定義一個新文件組，文件組名為：NewFileGroup1，同時在該文件組中新添加兩個數(shù)據(jù)文件，邏輯名分別為：students_dat1和students_dat2，初始大小分別為：4MB和6MB，均存放在D:\Data文件夾下，不自動增長。（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è)為默認文件組。ALTERDATABASEStudentsMODIFYFILEGROUPNewFileGroup1DEFAULT40/393.5刪除數(shù)據(jù)庫DROPDATABASEdatabase_name[,…n]例1．刪除單個數(shù)