第2章-SQL-Server數(shù)據(jù)庫的管理

數(shù)據(jù)庫技術(shù)與應(yīng)用數(shù)據(jù)庫技術(shù)與應(yīng)用教材編寫組第2章數(shù)據(jù)庫的管理與使用SQLServer數(shù)據(jù)庫的存儲結(jié)構(gòu)數(shù)據(jù)庫的創(chuàng)建數(shù)據(jù)庫的修改數(shù)據(jù)庫的刪除數(shù)據(jù)庫的分離和附加數(shù)據(jù)庫的擴(kuò)大和收縮數(shù)據(jù)庫的存儲結(jié)構(gòu)、創(chuàng)建、修改、分離和附加2問題提出數(shù)據(jù)庫的管理指的是什么？是有關(guān)建立、存儲、修改和存取數(shù)據(jù)庫中信息的技術(shù)，是指為保證數(shù)據(jù)庫系統(tǒng)的正常運(yùn)行和服務(wù)質(zhì)量，有關(guān)人員須進(jìn)行的技術(shù)管理工作。數(shù)據(jù)庫管理的主要內(nèi)容是什么？數(shù)據(jù)庫的建立數(shù)據(jù)庫的調(diào)整數(shù)據(jù)庫的重組數(shù)據(jù)庫的重構(gòu)數(shù)據(jù)庫的安全控制數(shù)據(jù)的完整性控制和對用戶提供技術(shù)支持如何實(shí)現(xiàn)數(shù)據(jù)庫的管理？3？2.1SQLServer數(shù)據(jù)庫的存儲結(jié)構(gòu)2.1.1邏輯存儲結(jié)構(gòu)2.1.2物理存儲結(jié)構(gòu)42.1SQLServer數(shù)據(jù)庫的存儲結(jié)構(gòu)52.1.1邏輯存儲結(jié)構(gòu)邏輯存儲結(jié)構(gòu)指的是數(shù)據(jù)庫是由哪些性質(zhì)的信息所組成。它主要應(yīng)用于面向用戶的數(shù)據(jù)組織和管理，如數(shù)據(jù)庫的表、視圖、存儲等。由于這些對象是存儲在數(shù)據(jù)庫中，因此也叫數(shù)據(jù)庫對象。數(shù)據(jù)庫對象62.1.1邏輯存儲結(jié)構(gòu)數(shù)據(jù)庫類型SQLServer2008數(shù)據(jù)庫分為系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫。系統(tǒng)數(shù)據(jù)庫是由系統(tǒng)創(chuàng)建和維護(hù)的數(shù)據(jù)庫。在SQLServer2008中有：master、model、msdb和tempdb共4個系統(tǒng)數(shù)據(jù)庫7數(shù)據(jù)庫名稱數(shù)據(jù)庫描述mastermaster數(shù)據(jù)庫記錄

SQLServer系統(tǒng)的所有系統(tǒng)級信息。主要包括實(shí)例范圍的元數(shù)據(jù)、端點(diǎn)、鏈接服務(wù)器和系統(tǒng)配置設(shè)置以及記錄了所有其他數(shù)據(jù)庫的存在、數(shù)據(jù)庫文件的位置以及

SQLServer的初始化信息。model提供了SQLServer實(shí)例上創(chuàng)建的所有數(shù)據(jù)庫的模板。msdb主要由

SQLServer代理用于計劃警報和作業(yè)。tempdbtempdb系統(tǒng)數(shù)據(jù)庫是一個全局資源，可供連接到

SQLServer實(shí)例的所有用戶使用，并可用于保存顯式創(chuàng)建的臨時用戶對象、SQLServer數(shù)據(jù)庫引擎創(chuàng)建的內(nèi)部對象，和一些版本數(shù)據(jù)等。2.1.1邏輯存儲結(jié)構(gòu)數(shù)據(jù)庫類型用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫分為系統(tǒng)提供的示例數(shù)據(jù)庫和用戶創(chuàng)建的數(shù)據(jù)庫。示例數(shù)據(jù)庫（默認(rèn)的示例數(shù)據(jù)庫）AdventureWorks：自行車制造公司的業(yè)務(wù)數(shù)據(jù)。AdventureWorksDW：是AnalysisServices（分析服務(wù)）的示例數(shù)據(jù)庫用戶創(chuàng)建的數(shù)據(jù)庫82.1.2物理存儲結(jié)構(gòu)數(shù)據(jù)庫的物理存儲結(jié)構(gòu)指的是數(shù)據(jù)庫文件在磁盤中是如何存儲的。數(shù)據(jù)庫文件存放數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)庫對象的文件。3種類型：主數(shù)據(jù)文件（.mdf）：一個數(shù)據(jù)庫只能有一個主數(shù)據(jù)文件次數(shù)據(jù)文件(.ndf)：一個數(shù)據(jù)庫可以有零個或多個次數(shù)據(jù)文件事務(wù)日志文件(.ldf)：一個數(shù)據(jù)庫可以有一個或多個事務(wù)日志文件92.1.2物理存儲結(jié)構(gòu)數(shù)據(jù)庫文件的存儲形式10數(shù)據(jù)庫頁的結(jié)構(gòu)

2.1.2物理存儲結(jié)構(gòu)數(shù)據(jù)庫文件組出于分配和管理目的，可以將數(shù)據(jù)庫文件分成不同的文件組（FileGroup，文件的邏輯集合）。主文件組（PrimaryFileGroup）：系統(tǒng)文件分配到此組，每一個數(shù)據(jù)庫中的主文件和未被放入其他組的次要文件在此組中。用戶定義文件組（SecondaryFileGroup）默認(rèn)文件組：未指定對象所屬文件組的對象都被放入此組注意：事務(wù)日志文件不屬于任何文件組。112.2數(shù)據(jù)庫的創(chuàng)建2.2.1使用對象資源管理器創(chuàng)建數(shù)據(jù)庫2.2.2使用T-SQL語言創(chuàng)建數(shù)據(jù)庫122.2數(shù)據(jù)庫的創(chuàng)建在建立用戶邏輯組件之前（如基本表）必須先建立數(shù)據(jù)庫。Sqlserver2008中可以支持最多32767個數(shù)據(jù)庫命名規(guī)則：名稱長度不能超過128個字符，臨時表名不能超過116個字符名稱第1個字符必須是英文字母、中文、下劃線、@和#（不建議用）除第1個字符外的其他字符可以包含數(shù)字和$符號名稱中間不允許有空格和其他特殊字符132.2.1使用對象資源管理器創(chuàng)建數(shù)據(jù)庫圖形化的方式創(chuàng)建數(shù)據(jù)庫14演示操作主文件必須至少3MB2.2.1使用對象資源管理器創(chuàng)建數(shù)據(jù)庫默認(rèn)情況下，SQLServer2008數(shù)據(jù)庫文件保存在：“..\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA”目錄下。152.2.2使用T-SQL語言創(chuàng)建數(shù)據(jù)庫T-SQL語言的語法約定及使用說明16約定使用說明大寫T-SQL關(guān)鍵字斜體用戶提供的T-SQL語法的參數(shù)粗體數(shù)據(jù)庫名、表名、列名、索引名、存儲過程、實(shí)用工具、數(shù)據(jù)類型名以及必須按所顯示的原樣鍵入的文本下劃線指示當(dāng)語句中省略了包含帶下劃線的值的子句時應(yīng)使用的默認(rèn)值|（豎線）分隔括號或大括號中的語法項(xiàng)。只能使用其中一項(xiàng)[]（方括號）可選語法項(xiàng)。不要鍵入方括號{}（大括號）必選語法項(xiàng)。不要鍵入大括號[,...n]指示前面的項(xiàng)可以重復(fù)n次。各項(xiàng)之間以逗號分隔[...n]指示前面的項(xiàng)可以重復(fù)n次。每一項(xiàng)由空格分隔[;]可選的T-SQL語句終止符。不要鍵入方括號<label>::=語法塊的名稱。此約定用于對可在語句中的多個位置使用的過長語法段或語法單元進(jìn)行分組和標(biāo)記?？墒褂玫恼Z法塊的每個位置由括在尖括號內(nèi)的標(biāo)簽指示：<label>2.2.2使用T-SQL語言創(chuàng)建數(shù)據(jù)庫語句格式：CREATEDATABASEdatabase_name[ON

--指定數(shù)據(jù)庫文件和文件組屬性[<filespec>[,...n]][,<filegroup>[,...n]]][LOGON{<filespec>[,...n]}]

--指定日志文件屬性[COLLATEcollation_name] --使用默認(rèn)排序規(guī)則[FORLOAD|FORATTACH]--附加已經(jīng)存在的數(shù)據(jù)庫172.2.2使用T-SQL語言創(chuàng)建數(shù)據(jù)庫在SSMS主窗口中單擊“新建查詢”工具按鈕，打開SQLServer2008的查詢設(shè)計器進(jìn)行操作。182.2.2使用T-SQL語言創(chuàng)建數(shù)據(jù)庫例2.1：創(chuàng)建一個名稱為Exercise_db1的簡單數(shù)據(jù)庫，文件的所有屬性均取默認(rèn)值。CREATEDATABASEExercise_db1例2.2：創(chuàng)建一個指定主數(shù)據(jù)文件和事務(wù)日志文件的簡單數(shù)據(jù)庫，數(shù)據(jù)庫名稱為Exercise_db2。要求如下：（1）數(shù)據(jù)庫的主數(shù)據(jù)文件邏輯文件名為Exercise_Data，物理文件名為Exercise.MDF,初始大小為5MB,最大文件大小無限制，自動增長量為10%。（2）事務(wù)日志文件邏輯文件名為Exercise_LOG，物理文件名為Exercise.LDF，初始大小為1MB，最大文件大小為10MB，自動增長量為2MB。（3）文件存儲的物理位置均為F:\mydb（設(shè)mydb文件夾已經(jīng)建立）。192.2.2使用T-SQL語言創(chuàng)建數(shù)據(jù)庫創(chuàng)建例2.2要求的數(shù)據(jù)庫語句為：CREATEDATABASEExercise_db2ONPRIMARY(NAME=Exercise_Data,FILENAME='F:\mydb\Exercise.MDF',SIZE=5,MAXSIZE=Unlimited,FILEGROWTH=10%)LOGON(NAME=Exercise_LOG,FILENAME='F:\mydb\Exercise.LDF',SIZE=1,MAXSIZE=10,FILEGROWTH=2)20數(shù)據(jù)庫文件屬性事務(wù)日志文件屬性2.2.2使用T-SQL語言創(chuàng)建數(shù)據(jù)庫21

分析2.3數(shù)據(jù)庫的修改2.3.1使用對象資源管理器修改數(shù)據(jù)庫2.3.2使用T-SQL語言修改數(shù)據(jù)庫222.3.1使用對象資源管理器修改數(shù)據(jù)庫23演示操作2.3.2使用T-SQL語言修改數(shù)據(jù)庫ALTER

DATABASEdatabase{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}|SET<optionspec>[,...n][WITH<termination>]|COLLATE<collation_name>}24filespec：file子句REMOVE：從系統(tǒng)中刪除文件描述和物理文件2.3.2使用T-SQL語言修改數(shù)據(jù)庫例2.5向數(shù)據(jù)庫中添加文件。要求如下：（1）在Exercise_db2數(shù)據(jù)庫中添加一個新數(shù)據(jù)文件，數(shù)據(jù)文件的邏輯文件名、物理位置及文件名分別為Exe1dat1和D:\mydb\Exe1_dat1.NDF。（2）數(shù)據(jù)文件初始大小為5MB，最大文件大小為30MB，自動增長量為2MB。ALTER

DATABASE

Exercise_db2ADDFILE(NAME=Exe1dat1,FILENAME='f:\mydb\Exe1_dat1.NDF',SIZE=5MB,MAXSIZE=30MB,FILEGROWTH=2MB)252.3.2使用T-SQL語言修改數(shù)據(jù)庫例2.9修改數(shù)據(jù)庫文件名稱。要求是：將數(shù)據(jù)庫Exercise_db2名稱修改為Exe_db2。ALTERDATABASEExercise_db2MODIFYNAME=Exe_db2也可將數(shù)據(jù)庫更名為漢字，如ALTERDATABASEExe_db2MODIFYNAME=學(xué)生信息管理系統(tǒng)262.3.2使用T-SQL語言修改數(shù)據(jù)庫修改數(shù)據(jù)庫補(bǔ)例：將“Exercise_db2”數(shù)據(jù)庫主數(shù)據(jù)文件最大大小改為20MB。ALTERDATABASEExercise_db2MODIFYFILE(NAME=Exercise_Data,MAXSIZE=20MB)272.4數(shù)據(jù)庫的刪除2.4.1使用圖形界面方式刪除數(shù)據(jù)庫2.4.2使用T-SQL語言刪除數(shù)據(jù)庫282.4.1使用圖形界面方式刪除數(shù)據(jù)庫29演示操作勾選要刪除的數(shù)據(jù)庫2.4.2使用T-SQL語言刪除數(shù)據(jù)庫命令語法格式：DROP

DATABASE

database_name[,...n]例2.10刪除Test_db1單個數(shù)據(jù)庫。

DROPDATABASETest_db1例2.11同時刪除Test_db2和Test_db3多個數(shù)據(jù)庫。

DROPDATABASETest_db2,Test_db3302.5數(shù)據(jù)庫的分離和附加2.5.1數(shù)據(jù)庫的分離2.5.2數(shù)據(jù)庫的附加312.5.1數(shù)據(jù)庫的分離數(shù)據(jù)庫的分離就是將用戶的數(shù)據(jù)庫從SQLServer中刪除，即從SQLServer服務(wù)器中分離出來，但是保持組成該數(shù)據(jù)的數(shù)據(jù)文件和事務(wù)日志文件中的數(shù)據(jù)完好無損，即數(shù)據(jù)庫文件仍保留在磁盤上。使用對象資源管理器分離數(shù)據(jù)庫32演示操作2.5.1數(shù)據(jù)庫的分離使用系統(tǒng)存儲過程sp_detach_db分離數(shù)據(jù)庫語法格式：sp_detach_db[@dbname=]'database_name'例2.12將student_db數(shù)據(jù)庫從SQLServer2008服務(wù)器中分離。Usestudent_db --打開數(shù)據(jù)庫student_dbGo

sp_detach_db'student_db'Go33GO不是Transact-SQL語句；它是可由sqlcmd和osql實(shí)用工具以及SQLServerManagementStudio代碼編輯器識別的命令。表示當(dāng)前批語句由上一GO命令后輸入的所有語句組成2.5.2數(shù)據(jù)庫的附加附加數(shù)據(jù)庫是分離數(shù)據(jù)庫的逆操作，通過附加數(shù)據(jù)庫，可以將沒有加入SQLServer服務(wù)器的數(shù)據(jù)庫文件添加到服務(wù)器中。使用對象資源管理器附加數(shù)據(jù)庫注意：（1）在附加數(shù)據(jù)庫時，當(dāng)確定主數(shù)據(jù)文件的名稱和物理位置后，與它相配套的事務(wù)日志文件（.LDF）也一并加入。（2）將SQLServer2000或SQLServer2005數(shù)據(jù)庫附加到SQLServer2008后，該數(shù)據(jù)庫立即變?yōu)榭捎?，然后自動升級?4演示操作2.6數(shù)據(jù)庫的擴(kuò)大和收縮2.6.1數(shù)據(jù)庫的擴(kuò)大2.6.2數(shù)據(jù)庫的收縮352.6數(shù)據(jù)庫的擴(kuò)大和收縮由于SQLServer2008對數(shù)據(jù)庫空間分配采取“先分配、后使用”的機(jī)制，所以在SQLServer2008系統(tǒng)中，如果數(shù)據(jù)庫的數(shù)據(jù)量不斷膨脹，可以根據(jù)需要擴(kuò)大數(shù)據(jù)庫的大小。同理，對于數(shù)據(jù)庫的設(shè)計大小過大，或者刪除了數(shù)據(jù)庫中的大量數(shù)據(jù)，這時數(shù)據(jù)庫會白白耗費(fèi)大量的磁盤資源。則可使用SQLServer2008提供的收縮數(shù)據(jù)庫功能，對數(shù)據(jù)庫進(jìn)行收縮，還可以對數(shù)據(jù)庫中的每個文件進(jìn)行收縮，直至收縮到?jīng)]有剩余的可用空間為止。362.6.1數(shù)據(jù)庫的擴(kuò)大擴(kuò)大數(shù)據(jù)庫空間有3種方法（1）設(shè)置數(shù)據(jù)庫為自動增長方式，可以在創(chuàng)建數(shù)據(jù)