SQL-Server實用數(shù)據(jù)庫技術(shù)課件第3章

課程回顧SQLServer2008的特點SQLServer2008的體系結(jié)構(gòu)SQLServer2008的安裝及配置SQLServer2008的管理工具本章綜述

對數(shù)據(jù)信息的存儲、管理和加工等操作都在數(shù)據(jù)庫中進行，因此數(shù)據(jù)庫的創(chuàng)建與管理是學習和使用數(shù)據(jù)庫的基礎(chǔ)。在前面的章節(jié)中，我們已經(jīng)了解了SQLServer2008的工具構(gòu)成，學習了SQLServer2008的安裝及配置。本章主要講述數(shù)據(jù)庫及其文件的存儲結(jié)構(gòu)、數(shù)據(jù)庫的創(chuàng)建與管理，以及數(shù)據(jù)庫的分離與附加。

9.1.1備份概述

使用SSMS創(chuàng)建數(shù)據(jù)庫使用createdatabase語句創(chuàng)建數(shù)據(jù)庫數(shù)據(jù)庫對象系統(tǒng)數(shù)據(jù)庫文件與文件組查看數(shù)據(jù)庫修改數(shù)據(jù)庫刪除數(shù)據(jù)庫分離數(shù)據(jù)庫附加數(shù)據(jù)庫3.1數(shù)據(jù)庫概述

3.2創(chuàng)建數(shù)據(jù)庫3.3管理數(shù)據(jù)庫

3.4分離與附加數(shù)據(jù)庫

本章結(jié)構(gòu)技能展示了解SQLServer2008數(shù)據(jù)庫存儲結(jié)構(gòu)了解數(shù)據(jù)庫對象了解系統(tǒng)數(shù)據(jù)庫掌握數(shù)據(jù)庫的創(chuàng)建掌握數(shù)據(jù)庫的修改和刪除掌握數(shù)據(jù)庫的分離與附加3.1 數(shù)據(jù)庫概述3.1.1數(shù)據(jù)庫對象3.1.2系統(tǒng)數(shù)據(jù)庫3.1.3文件與文件組3.1.1數(shù)據(jù)庫對象在SQLServer2008中，數(shù)據(jù)庫作為整體集中進行管理，數(shù)據(jù)庫中的數(shù)據(jù)及信息在邏輯上組成一系列對象。數(shù)據(jù)庫可以看作數(shù)據(jù)庫對象的容器，每個數(shù)據(jù)庫包括數(shù)據(jù)表、視圖、索引、約束、規(guī)則、存儲過程、觸發(fā)器等數(shù)據(jù)庫對象。在對象資源管理器窗口可以查看數(shù)據(jù)庫中的數(shù)據(jù)庫對象

。3.1.1數(shù)據(jù)庫對象

數(shù)據(jù)表:數(shù)據(jù)庫中最基本的對象，用于存儲數(shù)據(jù)庫中的數(shù)據(jù)。數(shù)據(jù)表由行和列組成，每一行稱為一條記錄，每一列稱為一個字段。每個表通常有一個關(guān)鍵字，用于惟一標識一條記錄。視圖:視圖是從一個或多個基本表導(dǎo)出的表，也稱虛擬表。與數(shù)據(jù)表相似，視圖由字段和記錄組成，但視圖本身并不存儲實際數(shù)據(jù)。存儲過程:存儲過程是預(yù)先編譯好的T-SQL語句的集合，相當于其他編程語言中的函數(shù)、過程和方法，具有“編寫一次處處調(diào)用”的特點”，便于程序的維護和減少網(wǎng)絡(luò)通信量。觸發(fā)器:觸發(fā)器是一段能自動執(zhí)行的程序，是一種特殊的存儲過程。觸發(fā)器不允許用戶調(diào)用，當對表進行插入、刪除和修改操作時由系統(tǒng)自動調(diào)用并執(zhí)行。3.1.2系統(tǒng)數(shù)據(jù)庫mastermodeltempdbmsdbresource3.1.2系統(tǒng)數(shù)據(jù)庫

master:SQLServer系統(tǒng)最重要的數(shù)據(jù)庫。記錄SQLServer系統(tǒng)級信息，包括登錄賬號，系統(tǒng)配置信息、數(shù)據(jù)庫信息等。model:所有數(shù)據(jù)庫的模板

。Tempdb:存放臨時表、臨時存儲過程以及其他臨時性對象。msdb:SQLServer代理使用msdb安排報警、作業(yè)并記錄操作員的操作。resource：只讀數(shù)據(jù)庫，包含SQLServer2008中的所有系統(tǒng)對象。3.1.3文件與文件組

主數(shù)據(jù)文件:存放數(shù)據(jù)和啟動信息每個數(shù)據(jù)庫必須有且只有一個主數(shù)據(jù)文件，擴展名為MDF。輔助數(shù)據(jù)文件:存放未包含在主數(shù)據(jù)文件中的數(shù)據(jù)。每個數(shù)據(jù)庫可以沒有也允許有多個輔助數(shù)據(jù)文件，擴展名為NDF。事務(wù)日志文件:存放對數(shù)據(jù)庫的修改和操作信息。每個數(shù)據(jù)庫必須有一個也可以有多個事務(wù)日志文件，擴展名為LDF。默認狀態(tài)下，主數(shù)據(jù)文件、輔助數(shù)據(jù)文件、事務(wù)日志文件均存放在C:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\的系統(tǒng)目錄下，用戶可以自己指定其他路徑而不會影響對數(shù)據(jù)庫的操作。文件組：為了便于分配和管理，將多個數(shù)據(jù)庫文件組織在一起。分類主文件組(Primary)默認狀態(tài)下主數(shù)據(jù)文件和輔助數(shù)據(jù)文件均屬于主文件組用戶定義的文件組使用Filegroup定義文件組注一個文件或者文件組只能屬于一個數(shù)據(jù)庫。一個數(shù)據(jù)文件只能屬于一個文件組事務(wù)日志文件不屬于文件組。3.2 創(chuàng)建數(shù)據(jù)庫3.2.1使用SSMS創(chuàng)建數(shù)據(jù)庫3.2.2使用createdatabase語句創(chuàng)建數(shù)據(jù)庫3.2.1使用SSMS創(chuàng)建數(shù)據(jù)庫【例3-1】使用對象資源管理器創(chuàng)建學生信息數(shù)據(jù)庫student在對象資源管理器中連接到SQLServer數(shù)據(jù)庫引擎，然后展開該實例。在“數(shù)據(jù)庫”節(jié)點上單擊鼠標右鍵，從彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”命令，如圖3-1所示。

圖3-1

在如圖3-2所示的“新建數(shù)據(jù)庫”對話框中輸入數(shù)據(jù)庫的名稱。

圖3-2如果采用默認值創(chuàng)建數(shù)據(jù)庫，則單擊“確定”按鈕，否則進行以下設(shè)置：數(shù)據(jù)庫的所有者：具有在數(shù)據(jù)庫中執(zhí)行所有活動權(quán)限的用戶，如sa。邏輯名稱：指定該文件的文件名。與SQLServer2005一樣，在默認情況下不再為用戶輸入的文件名添加下劃線和Data字樣。文件類型：用于區(qū)別當前文件是數(shù)據(jù)文件還是日志文件文件組：顯示當前文件所屬的文件組。一個數(shù)據(jù)庫文件只能存在于一個文件組中。初始大小：指定文件的初始容量，數(shù)據(jù)文件的默認大小為3MB，日志文件的默認大小為1MB。自動增長：用于設(shè)置文件根據(jù)何種方式自動增長，單擊“自動增長”列中的省略號按鈕，可以打開如圖3-3所示的“更改自動增長設(shè)置”對話框。路徑：指定存放文件的目錄，默認情況下SQLServer2008將存放路徑設(shè)置為SQLServer2008安裝目錄下的data子目錄，單擊該列的按鈕可以打開“定位文件夾”對話框，更改數(shù)據(jù)庫文件存儲路徑。

圖3-3如果要更改數(shù)據(jù)庫的排序選項、恢復(fù)模式和數(shù)據(jù)庫選項，可選擇如圖3-4所示“選項”頁，進行相關(guān)設(shè)置

圖3-4如果要添加或刪除文件組，可以單擊“文件組”頁，然后單擊“添加”或“刪除”按鈕更改數(shù)據(jù)庫文件所屬的文件組。完成以上操作后，單擊“確定”按鈕，即可完成數(shù)據(jù)庫的創(chuàng)建。此時可以在對象資源管理器中看到新建的數(shù)據(jù)庫。

3.2.2使用createdatabase語句創(chuàng)建數(shù)據(jù)庫

語法格式如下CREATDATABASE數(shù)據(jù)庫名稱[ON[PRIMARY]([NAME=數(shù)據(jù)文件的邏輯名稱,]FILENAME=數(shù)據(jù)文件的物理名稱[,SIZE=數(shù)據(jù)文件的初始大小][,MIXSIZE=數(shù)據(jù)文件的最大容量][,FILEGROWTH=數(shù)據(jù)文件的增長量])]LOGON([NAME=數(shù)據(jù)文件的邏輯名稱,]FILENAME=數(shù)據(jù)文件的物理名稱[,SIZE=數(shù)據(jù)文件的初始大小][,MIXSIZE=數(shù)據(jù)文件的最大容量[MB|KB]|UNLIMITED][,FILEGROWTH=數(shù)據(jù)文件的增長量[MB|KB|%]][COLLATE排序規(guī)則名稱]/*COLLATE子句*/

參數(shù)說明ON子句：說明數(shù)據(jù)存放的位置、數(shù)據(jù)文件的名稱、大小和增長方式。PRIMARY：指定后面定義的數(shù)據(jù)文件屬于主文件組PRIMARY，也可以加入用戶自已定義的文件組。

NAME：數(shù)據(jù)文件的邏輯名稱，用于在SQLServer中引用文件。

FILENAME：數(shù)據(jù)文件的物理文件名稱以及其完整的存儲路徑。SIZE：數(shù)據(jù)文件的初始大小，以MB為默認單位?？梢愿鶕?jù)需要使用KB、MB、GB或者TB作為文件大小的后綴。FILEGROWTH：數(shù)據(jù)文件的增長量。可以用MB或者百分比。UNLIMITED表示在磁盤容量允許情況下不受限制。LOGON子句：說明日志文件存放的位置、名稱、大小和增長方式。

【例3-2】使用T-SQL語句的默認設(shè)置創(chuàng)建學生信息數(shù)據(jù)庫student

。啟動SSMS，在對象資源管理器中連接到SQLServer數(shù)據(jù)庫引擎。在工具欄上單擊“新建查詢”按鈕，在打開的查詢編輯器窗口中輸入以下代碼CREATEDATABASEstudent

。單擊工具欄中的運行按鈕或按F5鍵運行SQL語句，即可完成數(shù)據(jù)庫的創(chuàng)建，運行結(jié)果如下圖3-5所示。

圖3-5

【例3-3】使用T-SQL語句在C:\DATA\與D:\DATA\文件夾中創(chuàng)建總務(wù)管理數(shù)據(jù)庫build，該數(shù)據(jù)庫包含

：主數(shù)據(jù)文件邏輯名‘build’，物理名‘C:\DATA\build.mdf’,初始容量3MB，最大容量10MB，每次增長量為15%。輔助數(shù)據(jù)文件邏輯名‘build2’物理名‘D:\DATA\build2.ndf’,初始容量2MB，最大容量15MB，每次增長量為2MB。兩個數(shù)據(jù)文件不單獨創(chuàng)建文件組，即使用默認的PRIMARY組；事務(wù)日志文件邏輯名‘buildlog’物理名‘D:\DATA\buildlog.ldf’，初始容量500KB，最大容量不受限制，每次增長量為500KB。

CREATEDATABASEbuildON(NAME=builddata1,FILENAME='C:\DATA\build.mdf',SIZE=3MB,--默認字節(jié)單位MB可以省略

MAXSIZE=10,--文件最大容量10MBFILEGROWTH=15%--增長量為文件容量%),(NAME=builddata2,FILENAME='D:\DATA\build2.mdf',SIZE=2,MAXSIZE=15,FILEGROWTH=2--增長量為MB)LOGON/*創(chuàng)建事務(wù)日志文件*/(NAME=buildlog,FILENAME='D:\DATA\buildlog.LDF',SIZE=500KB,/*初始容量，用KB為單位，不能省略*/MAXSIZE=UNLIMITED,/*日志文件最大容量不受限制*/FILEGROWTH=500KB/*增長量KB不能省略*/)

圖3-6

【例3-4】創(chuàng)建一個test數(shù)據(jù)庫，要求如下：主數(shù)據(jù)文件邏輯名‘test_data，物理文件名為test.mdf，初始大小為10MB，最大尺寸為無限大，增長速度為10%。事務(wù)日志文件邏輯名test_log，物理文件名為test.ldf，初始大小為3MB，最大尺寸為6MB，增長速度為1MB

。

CREATEDATABASEtestONPRIMARY(NAME='test_data',FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\test.mdf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)LOGON(NAME='test_log',FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\\test.ldf',SIZE=3MB,MAXSIZE=6MB,FILEGROWTH=1MB)

圖3-73.3 管理數(shù)據(jù)庫3.3.1查看數(shù)據(jù)庫3.3.2修改數(shù)據(jù)庫3.3.3刪除數(shù)據(jù)庫3.3.1查看數(shù)據(jù)庫使用系統(tǒng)存儲過程sp_helpdb可以查看數(shù)據(jù)庫的信息，語法格式

：

[EXECUTE]sp_helpdb[數(shù)據(jù)庫名]

注EXECUTE可以縮寫為EXEC，如果是批處理中第一個語句則可全部省略。如果省略數(shù)據(jù)庫名，則查看所有數(shù)據(jù)庫的信息。

開封大學信息工程學院【例3-5】使用系統(tǒng)存儲過程sp_helpdb查看所有數(shù)據(jù)庫的信息。EXECsp_helpdb

圖3-8【例3-6】使用系統(tǒng)存儲過程sp_helpdb查看build數(shù)據(jù)庫的信息。EXECsp_helpdb

build

圖3-93.3.2修改數(shù)據(jù)庫【例3-7】設(shè)置和修改數(shù)據(jù)庫build的屬性及選項。

在對象資源管理器中右擊需要修改的數(shù)據(jù)庫build，在彈出的快捷菜單中選擇【屬性】命令，打開如圖3-10所示【數(shù)據(jù)庫屬性】對話框。在【數(shù)據(jù)庫屬性】對話框中可以查看數(shù)據(jù)庫的屬性，并能設(shè)置和修改數(shù)據(jù)庫的屬性和選項設(shè)置

。

圖3-103.3.2修改數(shù)據(jù)庫

使用系統(tǒng)存儲過程sp_dboption查看、設(shè)置或修改數(shù)據(jù)庫選項

[EXECUTE]sp_dboption[數(shù)據(jù)庫名[選項名，選項值]

注選項名可使用以下選項

single單用戶

readonly只讀

autoshrink自動收縮

autoclose自動關(guān)閉選項名為true為設(shè)置該選項，為false表示取消該選項。EXECUTE可以縮寫為EXEC,如果是批處理中的第一個語句則可全部省略。

【例3-8】將build數(shù)據(jù)庫設(shè)置為自動收縮。

sp_dboption'build','autoshrink','true'

圖3-113.3.2修改數(shù)據(jù)庫

使用ALTERDATABASE語句修改數(shù)據(jù)庫，語法格式如下ALTERDATABASE數(shù)據(jù)庫名{

|MODIFYNAME=new_database_name

|ADDFILE<文件格式>[TOFILEGROUP文件組]|ADDLOGFILE<文件格式>|REMOVEFILE邏輯文件名

|ADDFILEGROUP文件組名

|REMOVEFILEGROUP文件組名

|MODIFYFILE<文件格式>|MODIFYFILEGROUP文件組名，文件組屬性

|<SET_DATABASE_OPTIONS>}【例3-9】向build中添加名為buildfilegroup的文件組，在D盤DATA文件夾中添加數(shù)據(jù)文件buildadd.ndf將加其添加到此文件組中。ALTERDATABASEbuildADDFILEGROUPbuildfilegroupGOALTERDATABASEbuildADDFILE(NAME=buildadd,FILENAME='D:\DATA\buildadd.ndf')TOFILEGROUPBUILDFILEGROUPGOExecutesp_helpdbbuild

圖3-12

【例3-10】將build數(shù)據(jù)庫原有主數(shù)據(jù)文件的初始大小改為4MB，按2MB自動增長到最大容量20MB

。ALTERDATABASEbuildMODIFYFILE(NAME='builddata1',SIZE=4,MAXSIZE=20,FILEGROWTH=2)GOExecutesp_helpdbbuild

圖3-13【例3-11】將數(shù)據(jù)庫build設(shè)置為只讀數(shù)據(jù)庫

。USEmasterGOALTERDATABASEbuildSETREAD_ONLYGOExecutesp_helpdbbuild3.3.3刪除數(shù)據(jù)庫

1.使用對象資源管理器刪除數(shù)據(jù)庫

在對象資源管理器中右擊需要刪除的數(shù)據(jù)庫，在彈出的快捷菜單中選擇【刪除】命令，打開如圖3-14所示的【刪除對象】對話框，進行設(shè)置后單擊【確定】按鈕，完成刪除操作。

圖3-143.3.3刪除數(shù)據(jù)庫使用DROPDATABASE語句可以刪除數(shù)據(jù)庫，語法格式如下

：

DROPDATABASE{數(shù)據(jù)庫名}[,...n]

注系統(tǒng)數(shù)據(jù)庫不能被刪除。正在使用的數(shù)據(jù)庫不能刪除，必須首先斷開連接。使用SQL命令一次可以刪除多個數(shù)據(jù)庫?！纠?-12】刪除數(shù)據(jù)庫build

。USEmasterGODROPDATABASEbuild

3.4 分離與附加數(shù)據(jù)庫如果想把數(shù)據(jù)庫從一個SQLServer系統(tǒng)移到另一個SQLServer系統(tǒng)，或者需要把數(shù)據(jù)文件從一個磁盤移到另一磁盤上，比如當包含數(shù)據(jù)庫文件的磁盤空間已經(jīng)用完而又希望擴充現(xiàn)有的數(shù)據(jù)庫文件，則可以先將數(shù)據(jù)庫與SQLServer系統(tǒng)分離，然后再將數(shù)據(jù)庫重新附加到另一系統(tǒng)或磁盤中。

3.4.1分離數(shù)據(jù)庫分離數(shù)據(jù)庫分離數(shù)據(jù)庫只是從SQLServer2008系統(tǒng)中刪除數(shù)據(jù)庫，組成該數(shù)據(jù)庫的數(shù)據(jù)文件和事務(wù)日志文件依然完好無損地保存在磁盤上?？梢酝ㄟ^數(shù)據(jù)文件和事務(wù)日志文件將數(shù)據(jù)庫附加到任何其他機器的SQLServer2008系統(tǒng)中，而且數(shù)據(jù)庫在新系統(tǒng)中的使用狀態(tài)與它分離時的狀態(tài)完全相同。方式使用SSMS使用系統(tǒng)存儲過程sp_detach_db1使用SSMS分離數(shù)據(jù)庫在對象資源管理器中，連接到數(shù)據(jù)庫引擎實例。展開“數(shù)據(jù)庫”，右擊要分離的數(shù)據(jù)庫，在“任務(wù)”命令下選擇“分離”命令，將出現(xiàn)如圖3-15所示的“分離數(shù)據(jù)庫”對話框。

圖3-153.4.1分離數(shù)據(jù)庫在如圖3-16所示的“分離數(shù)據(jù)庫”對話框中檢查數(shù)據(jù)庫的狀態(tài)。注意：如果有用戶正在連接數(shù)據(jù)庫，則不能分離數(shù)據(jù)庫，必須選取“刪除連接”復(fù)選框斷開與數(shù)據(jù)庫的連接。此外，還可以選取“更新統(tǒng)計信息”復(fù)選框。

單擊“確定”按鈕完成數(shù)據(jù)庫的分離。此時，數(shù)據(jù)庫將不在數(shù)據(jù)庫列表中顯示，但是數(shù)據(jù)庫文件依然在磁盤中，此時可以對數(shù)據(jù)庫文件進行復(fù)制等操作。

圖3-162使用系統(tǒng)存儲過程分離數(shù)據(jù)庫使用sp_detach_db語句分離數(shù)據(jù)庫，語法格式如下EXECsp_detach_db數(shù)據(jù)庫名稱true|false注數(shù)據(jù)庫名稱”指定要分離的數(shù)據(jù)庫第二個參數(shù)指定是否在分離數(shù)據(jù)庫前更新數(shù)據(jù)庫統(tǒng)計信息，取false為指定更新數(shù)據(jù)庫統(tǒng)計信息，取true或省略則不更新數(shù)據(jù)庫統(tǒng)計信息。例：分離build數(shù)據(jù)庫，不更新統(tǒng)計信息EXECsp_detach_db‘build','true'3.4.2附加數(shù)據(jù)庫在對象資源管理器中，連接到數(shù)據(jù)庫引擎實例。右擊“數(shù)據(jù)庫”，然后選擇“附加”命令，打開如圖3-17所示的附加數(shù)據(jù)庫對話框。

圖3-173.4.2 附加數(shù)據(jù)庫在如圖3-18所示的“附加數(shù)據(jù)庫”對話框中指定要附加的數(shù)據(jù)庫，單擊“添加”按鈕，然后在“定位數(shù)據(jù)庫文件”對話框中選擇數(shù)據(jù)庫所在的磁盤驅(qū)動器，并展開目錄樹以查找和選擇數(shù)據(jù)庫的.mdf文件。然后單擊“確定”按鈕若要為附加的數(shù)據(jù)庫指定不同的名稱，則在“附加數(shù)據(jù)庫”對話框的“附加為”列中輸入名稱；也可以通過在“所有者”列中選擇其他項更改數(shù)據(jù)庫的所有者

。

圖3-182使用系統(tǒng)存儲過程附加數(shù)據(jù)庫使用sp_attach_db語句附加數(shù)據(jù)庫，語法格式如下[EXEC]sp_attach_db[@dbname=]'dbname',[@filename1=]'filename_n'[,...16]注Dbname:指定要附加的數(shù)據(jù)庫Filename:數(shù)據(jù)庫文件的物理名稱，包括路徑。例：附加build數(shù)據(jù)庫，不更新統(tǒng)計信息EXECsp_attach_db@dbname=N'DATA',

@filename1=N'c:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\DATA.mdf',

@filename2=N'c:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\DATA_log.ldf'小結(jié)SQLServer2008數(shù)據(jù)庫概述數(shù)據(jù)庫的創(chuàng)建數(shù)據(jù)庫的管理數(shù)據(jù)的附加與分離習題--填空題1．SQLServer2008的4個系統(tǒng)數(shù)據(jù)庫分別為________、________、msdb和________。2．SQLServer2008中主數(shù)據(jù)文件的擴展名為_________，輔助數(shù)據(jù)文件的擴展名為_________，日志文件的擴展名為________。3．在查詢編輯器窗口中，使用_________語句創(chuàng)建數(shù)據(jù)庫，使用_________命令查看數(shù)據(jù)庫定義信息，使用_________命令設(shè)置數(shù)據(jù)庫選項，使用_________命令修改數(shù)據(jù)庫。4．創(chuàng)建數(shù)據(jù)庫時，SIZE和MAXSIZE分別指定文件的_________和