第3章 數(shù)據(jù)庫的創(chuàng)建與管理

1、第3章 數(shù)據(jù)庫的創(chuàng)建與管理123掌握創(chuàng)建數(shù)據(jù)庫的兩種方法掌握創(chuàng)建數(shù)據(jù)庫的兩種方法掌握管理數(shù)據(jù)庫的方法掌握管理數(shù)據(jù)庫的方法學(xué)學(xué)習(xí)習(xí)目目標(biāo)標(biāo)了解數(shù)據(jù)庫對象及構(gòu)成了解數(shù)據(jù)庫對象及構(gòu)成主要內(nèi)容主要內(nèi)容3.1 SQL Server數(shù)據(jù)庫數(shù)據(jù)庫3.2 創(chuàng)建創(chuàng)建數(shù)據(jù)庫數(shù)據(jù)庫3.3 管理數(shù)據(jù)庫管理數(shù)據(jù)庫3.1 3.1 SQLServerSQLServer數(shù)據(jù)庫數(shù)據(jù)庫在SQL Server 2008 中有兩類數(shù)據(jù)庫：系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫。系統(tǒng)數(shù)據(jù)庫存儲有關(guān)SQL Server的系統(tǒng)信息，它們是SQL Server 2008管理數(shù)據(jù)庫的依據(jù)。如果系統(tǒng)數(shù)據(jù)庫遭到破壞，那么SQL Server 將不能正常啟動。 在

2、安裝SQL Server 2008的系統(tǒng)將創(chuàng)建4個可見系統(tǒng)數(shù)據(jù)庫：master、model、msdb和tempdb。 3.1.1 3.1.1 系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫1. master1. master數(shù)據(jù)庫數(shù)據(jù)庫 master數(shù)據(jù)庫是SQL Server中最重要的數(shù)據(jù)庫，如果該數(shù)據(jù)庫被損壞，SQL Server將無法正常工作。master數(shù)據(jù)庫中包含了所有的登錄名或用戶ID所屬的角色、服務(wù)器中的數(shù)據(jù)庫的名稱及相關(guān)信息、數(shù)據(jù)庫的位置、SQL Server如何初始化等四方面的重要信息。2. model2. model數(shù)據(jù)庫數(shù)據(jù)庫 創(chuàng)建數(shù)據(jù)庫時，總是以一套預(yù)定義的標(biāo)準(zhǔn)為模型。例如，若希望所有的數(shù)據(jù)庫都

3、有確定的初始大小，或者都有特定的信息集，那么可以把這些信息放在model數(shù)據(jù)庫中，以model數(shù)據(jù)庫作為其它數(shù)據(jù)庫的模板數(shù)據(jù)庫。如果想要使所有的數(shù)據(jù)庫都有一個特定的表，可以把該表放在model數(shù)據(jù)庫里。3.1.1 3.1.1 系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫3 3. . msdbmsdb數(shù)據(jù)庫數(shù)據(jù)庫msdb給SQLServer代理提供必要的信息來運行作業(yè)。4 4. . tempdbtempdb數(shù)據(jù)庫數(shù)據(jù)庫tempdb數(shù)據(jù)庫用作系統(tǒng)的臨時存儲空間，其主要作用是存儲用戶建立的臨時表和臨時存儲過程，存儲用戶說明的全局變量值，為數(shù)據(jù)排序創(chuàng)建臨時表，存儲用戶利用游標(biāo)說明所篩選出來的數(shù)據(jù)。3.1.2 3.1.2 用戶

4、數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫是由用戶創(chuàng)建數(shù)據(jù)庫。用戶數(shù)據(jù)庫與系統(tǒng)數(shù)據(jù)庫的結(jié)構(gòu)相同，文件的擴展名也相同。每個數(shù)據(jù)庫都包含主數(shù)據(jù)文件和主日志文件，擴展名分別為.mdf和.ldf。3.2 3.2 創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫就是為數(shù)據(jù)庫確定名稱、大小、存放位置、文件名和所在文件組的過程。在一個SQL Server 2008實例中，最多可以創(chuàng)建32767個數(shù)據(jù)庫，數(shù)據(jù)庫的名稱必須滿足系統(tǒng)的標(biāo)識符規(guī)則。在命名數(shù)據(jù)庫時，一定要使數(shù)據(jù)庫名稱簡短并有一定的含義。在SQL Server 2008中創(chuàng)建數(shù)據(jù)庫的方法主要有兩種：一是在SQL Server Management Studio圖形化向?qū)?chuàng)建；二是通過編

5、寫T-SQL語句創(chuàng)建。3.2.1 3.2.1 使用圖形界面方式創(chuàng)建數(shù)據(jù)庫使用圖形界面方式創(chuàng)建數(shù)據(jù)庫【例3.1】使用圖形界面方式創(chuàng)建“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫。具體的操作步驟如下所示：（1）從【開始】菜單中選擇【程序】【SQL Server 2008】【SQL Server Management Studio】命令，打開Microsoft SQL Server Management Studio窗口，并使用Windows或SQL Server身份驗證建立連接。如圖3.1所示。圖3.1 連接服務(wù)器身份驗證3.2.1 3.2.1 使用圖形界面方式創(chuàng)建數(shù)據(jù)庫使用圖形界面方式創(chuàng)建數(shù)據(jù)庫（2）在【對象資源

6、管理器】窗口中展開服務(wù)器，然后選擇【數(shù)據(jù)庫】節(jié)點。（3）在【數(shù)據(jù)庫】節(jié)點上右鍵單擊，從彈出的快捷菜單中選擇【新建數(shù)據(jù)庫】命令，如圖3.2所示。圖3.2 選擇【新建數(shù)據(jù)庫】命令3.2.1 3.2.1 使用圖形界面方式創(chuàng)建數(shù)據(jù)庫使用圖形界面方式創(chuàng)建數(shù)據(jù)庫（4）執(zhí)行上述操作后，會彈出【新建數(shù)據(jù)庫】對話框，如圖3.3所示。在對話框左側(cè)中有三個選項，分別是【常規(guī)】、【選項】和【文件組】。圖3.3 【新建數(shù)據(jù)庫】對話框3.2.1 3.2.1 使用圖形界面方式創(chuàng)建數(shù)據(jù)庫使用圖形界面方式創(chuàng)建數(shù)據(jù)庫（5）在【數(shù)據(jù)庫名稱】文本框中輸入要新建數(shù)據(jù)庫的名稱，例如這里輸入“學(xué)生成績管理系統(tǒng)”。（6）在【所有者】文本框中

7、輸入新建數(shù)據(jù)庫的所有者，如sa。根據(jù)數(shù)據(jù)庫的使用情況，選擇啟用或者禁用【使用全文索引】復(fù)選框。（7）在【數(shù)據(jù)庫文件】列表中包括兩行：一行是數(shù)據(jù)文件，而另一行是日志文件。通過單擊下面相應(yīng)按鈕，可以添加或者刪除數(shù)據(jù)文件。（8）完成以上操作后，單擊【確定】按鈕關(guān)閉【新建數(shù)據(jù)庫】對話框。至此，“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫創(chuàng)建成功。可在【對象資源管理器】窗口看到新建的數(shù)據(jù)庫。3.2.2 3.2.2 使用使用T-SQLT-SQL語句創(chuàng)建數(shù)據(jù)庫語句創(chuàng)建數(shù)據(jù)庫1 1CREATE DATABASECREATE DATABASE語法格式語法格式CREATE DATABASE database_nameON PRIM

8、ARY 1,n, 1,nLOGON 1,nCOLLATE collation_nameFOR ATTACH WITH |ATTACH_REBUILD_LOGWITH ;:=PRIMARY(NAME=logical_file_name,FILENAME=os_file_name3.2.2 3.2.2 使用使用T-SQLT-SQL語句創(chuàng)建數(shù)據(jù)庫語句創(chuàng)建數(shù)據(jù)庫,SIZE=sizeKB|MB|GB|TB,MAXSIZE=max_sizeKB|MB|GB|TB|UNLIMITED,FILEGROWTH=growth_incrementKB|MB|%)1,n:=FILEGROUP filegroup_na

9、me 1,n:=DB_CHAINING ON|OFF|TRUSTWORTHYON|OFF:=ENABLE_BROKE|NEW_BROKE|ERROR_BROKER_CONVERSATIONSl CREATE DATABASE database_name(數(shù)據(jù)庫名稱)l ON PRIMARY（主關(guān)鍵字）l (NAME=Logical_file_name,（邏輯文件名稱）l FILENAME=os_file_name,（操作系統(tǒng)下文件名，路徑）l SIZE=3MB,（初始大?。﹍ MAXSIZE=50MB,（最大大小）l FILEGROWTH=10%)（增長頻率）l LOG ONl (NAME=L

10、ogical_file_name,l FILENAME=os_file_name,l SIZE=1MB,l MAXSIZE=10MB,l FILEGROWTH=10%)l FILEGROUP filegroup_name（文件組名稱）3.2.2 3.2.2 使用使用T-SQLT-SQL語句創(chuàng)建數(shù)據(jù)庫語句創(chuàng)建數(shù)據(jù)庫2 2使用使用CREATE DATABASECREATE DATABASE語句創(chuàng)建語句創(chuàng)建數(shù)據(jù)庫數(shù)據(jù)庫【例3.2】使用CREATEDATABASE語句創(chuàng)建【學(xué)生成績管理系統(tǒng)】數(shù)據(jù)庫。CREATE DATABASE語句如下所示：CREATE DATABASE 學(xué)生成績管理系統(tǒng)ON(NAM

11、E=學(xué)生成績管理系統(tǒng),FILENAME=C:學(xué)生成績管理系統(tǒng).mdf,SIZE=3MB,MAXSIZE=50MB,FILEGROWTH=10%)LOG ON(NAME=學(xué)生成績管理系統(tǒng)_LOG,FILENAME= C:學(xué)生成績管理系統(tǒng)_LOG.ldf,SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=10%)數(shù)據(jù)庫的組成數(shù)據(jù)庫的組成l 表非常重要的對象，是其他對象的基礎(chǔ)，一個數(shù)據(jù)庫可能包含若干個數(shù)據(jù)表l 視圖從一個或多個表（或視圖）導(dǎo)出的表，視圖是一個虛表，不進行實際存儲l 存儲過程經(jīng)過第一次編譯后再次調(diào)用不需要再次編譯，只需指定名稱，給出參數(shù)便可執(zhí)行，好的數(shù)據(jù)庫應(yīng)用程序都用到

12、存儲過程l 觸發(fā)器保證完整性的一種方法l 用戶與角色制定不同權(quán)限l 其他數(shù)據(jù)庫部分l 主要l 主要數(shù)據(jù)文件包含數(shù)據(jù)庫的啟動信息，并指向數(shù)據(jù)庫中的其他文件。用戶數(shù)據(jù)和對象可存儲在此文件中，也可以存儲在次要數(shù)據(jù)文件中。每個數(shù)據(jù)庫有一個主要數(shù)據(jù)文件。主要數(shù)據(jù)文件的建議文件擴展名是 .mdf。 l 次要l 次要數(shù)據(jù)文件是可選的，由用戶定義并存儲用戶數(shù)據(jù)。通過將每個文件放在不同的磁盤驅(qū)動器上，次要文件可用于將數(shù)據(jù)分散到多個磁盤上。另外，如果數(shù)據(jù)庫超過了單個 Windows 文件的最大大小，可以使用次要數(shù)據(jù)文件，這樣數(shù)據(jù)庫就能繼續(xù)增長。次要數(shù)據(jù)文件的建議文件擴展名是 .ndf。l 事務(wù)日志l 事務(wù)日志文件

13、保存用于恢復(fù)數(shù)據(jù)庫的日志信息。每個數(shù)據(jù)庫必須至少有一個日志文件。事務(wù)日志的建議文件擴展名是 .ldf。3.3 3.3 管理數(shù)據(jù)庫管理數(shù)據(jù)庫 數(shù)據(jù)庫管理操作，主要包括查看、修改和刪除。查看是指可以瀏覽數(shù)據(jù)庫的各種屬性和狀態(tài)；修改是指可以修改數(shù)據(jù)庫的名稱、大小、自動增長等；刪除數(shù)據(jù)庫是對不需要的數(shù)據(jù)庫進行刪除，以釋放多余的磁盤空間。3.3.13.3.1使用圖形界面方式管理數(shù)據(jù)庫使用圖形界面方式管理數(shù)據(jù)庫1. 1. 查看數(shù)據(jù)庫信息查看數(shù)據(jù)庫信息1）使用系統(tǒng)視圖常見的查看數(shù)據(jù)庫基本信息的操作有：使用sys.databases數(shù)據(jù)庫和文件目錄視圖查看有關(guān)數(shù)據(jù)庫的基本信息使用sys.database_fi

14、les查看有關(guān)數(shù)據(jù)庫文件的信息使用sys.filegroups查看有關(guān)數(shù)據(jù)庫組的信息使用sys.maste_files查看數(shù)據(jù)庫文件的基本信息和狀態(tài)信息2）利用Microsoft SQL Server Management Studio利用Microsoft SQL Server Management Studio窗口來查看數(shù)據(jù)庫信息。在【對象資源管理器】窗口中右鍵單擊要查看信息的數(shù)據(jù)庫，選擇【屬性】命令，在彈出的【數(shù)據(jù)庫屬性】對話框中就可以查看到數(shù)據(jù)庫的常規(guī)信息、文件信息、文件組信息、選項信息等。3.3.13.3.1使用圖形界面方式管理數(shù)據(jù)庫使用圖形界面方式管理數(shù)據(jù)庫2. 2. 修改數(shù)據(jù)庫的

15、大小修改數(shù)據(jù)庫的大小【例3.4】使用圖形界面方式修改學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫的大小。步驟如下：（1）在【對象資源管理器】窗口中，右鍵單擊要修改大小的數(shù)據(jù)庫“學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫”，選擇【屬性】命令。（2）在【數(shù)據(jù)庫屬性】對話框中切換到【文件】頁。（3）在數(shù)據(jù)文件行的【初始大小】列中，輸入要修改的值。同樣在日志文件行的【初始大小】列中，輸入要修改的值。（4）單擊【自動增長】列中的按鈕），打開【自動增長設(shè)置】對話框，可設(shè)置自動增長的方式及大小。（5）如果要添加文件，可以直接在【文件】頁中單擊【添加】按鈕，進行相應(yīng)大小設(shè)置即可。（6）完成修改后，單擊底部的【確定】按鈕即可。3.3.13.3.1使用圖

16、形界面方式管理數(shù)據(jù)庫使用圖形界面方式管理數(shù)據(jù)庫3. 3. 刪除數(shù)據(jù)庫刪除數(shù)據(jù)庫【例3.5】使用圖形界面方式刪除“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫。步驟如下：（1）在【對象資源管理器】窗口中選中要刪除的數(shù)據(jù)庫，右鍵單擊【刪除】菜單項。（2）在彈出的【刪除對象】對話框中，單擊【確定】按鈕確認刪除。3.3.13.3.1使用圖形界面方式管理數(shù)據(jù)庫使用圖形界面方式管理數(shù)據(jù)庫4. 4. 分離數(shù)據(jù)庫分離數(shù)據(jù)庫【例3.6】使用圖形界面方式實現(xiàn)分離學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫。步驟如下：（1）在【對象資源管理器】窗口中右鍵單擊“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫，選擇【任務(wù)】【分離】命令。（2）在打開的【分離數(shù)據(jù)庫】對話框，查看在【數(shù)

17、據(jù)庫名稱】列中的數(shù)據(jù)庫名稱，驗證這是否為要分離的數(shù)據(jù)庫。（3）在【狀態(tài)】列中的是如果顯示的是“未就緒”，則【消息】列將顯示有關(guān)數(shù)據(jù)庫的超鏈接信息。當(dāng)數(shù)據(jù)庫涉及復(fù)制時，【消息】列將顯示Database replicated。（4）數(shù)據(jù)庫有一個或多個活動連接時，【消息】列將顯示“個活動連接”。在可以分離數(shù)據(jù)列之前，必須啟用【刪除連接】復(fù)選框來斷開與所有活動連接的連接。（5）分離數(shù)據(jù)庫準(zhǔn)備就緒后，單擊【確定】按鈕。3.3.13.3.1使用圖形界面方式管理數(shù)據(jù)庫使用圖形界面方式管理數(shù)據(jù)庫5. 5. 附加數(shù)據(jù)庫附加數(shù)據(jù)庫【例3.7】使用圖形界面將剛分離的學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫再附加到當(dāng)前數(shù)據(jù)庫實例中。步

18、驟如下：（1）在【對象資源管理器】窗口中，右鍵單擊【數(shù)據(jù)庫】節(jié)點并選擇【附加】菜單項。（2）在打開的【附加數(shù)據(jù)庫】對話框中單擊【添加】按鈕，從彈出的【定位數(shù)據(jù)庫文件】對話框中選擇要附加的數(shù)據(jù)庫所在的位置，再依次單擊【確定】按鈕返回。（3）回到【對象資源管理器】中，展開【數(shù)據(jù)庫】節(jié)點，可看到“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫已經(jīng)成功附加到了當(dāng)前的數(shù)據(jù)庫實例中。3.3.2 T-SQL3.3.2 T-SQL語句管理數(shù)據(jù)庫語句管理數(shù)據(jù)庫1.1.查看數(shù)據(jù)庫信息查看數(shù)據(jù)庫信息1）使用函數(shù)可以使用DATABASEPROPERTYEX函數(shù)來查看指定數(shù)據(jù)庫中的指定選項的信息，該函數(shù)一次只能返回一個選項的設(shè)置。例如，要查

19、看“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫中版本信息，可以使用如下語句：SELECT DATABASEPROPERTYEX(學(xué)生成績管理系統(tǒng),Version)2）使用存儲過程可使用sp_spaceused存儲過程查看所有數(shù)據(jù)庫的基本信息。單個數(shù)據(jù)庫的存儲過程EXEC sp_helpdb N圖書館管理系統(tǒng)3.3.2 T-SQL3.3.2 T-SQL語句管理數(shù)據(jù)庫語句管理數(shù)據(jù)庫2. 2. 修改數(shù)據(jù)庫的大小修改數(shù)據(jù)庫的大小可以使用ALTER DATABASE語句來修改數(shù)據(jù)庫的大小，其語法格式如下： ALTER DATABASE 數(shù)據(jù)庫名 ADD FILE (NAME = 數(shù)據(jù)文件的邏輯名稱 ，F(xiàn)ILENAME =

20、數(shù)據(jù)文件的物理名稱 ，SIZE = 數(shù)據(jù)文件的初始大小 MB | KB|GB ，MAXSIZE = 數(shù)據(jù)文件的最大容量 MB | KB|GB | UNLIMITED ,FILEGROWTH =數(shù)據(jù)文件的增長量 MB | KB | GB|% )TO FILEGROUP 文件組l 修改數(shù)據(jù)庫名稱l Alter database 數(shù)據(jù)庫名稱 modify name=新數(shù)據(jù)庫名稱l 修改數(shù)據(jù)庫參數(shù)l Alter database 數(shù)據(jù)庫名稱 modifyl (l Name=邏輯名稱,l Size=新大小,l Maxsize=最大大小,l Filegrowth=新增長方式l )l gol 如需增加日志文

21、件，可以使用add log file子句3.3.2 T-SQL3.3.2 T-SQL語句管理數(shù)據(jù)庫語句管理數(shù)據(jù)庫3.3.刪除數(shù)據(jù)庫刪除數(shù)據(jù)庫 可使用DROP DATABASE語句刪除數(shù)據(jù)庫，其語法格式如下：DROP DATABASE database_name ,n其中，database_name為要刪除的數(shù)據(jù)庫名，,n表示可以有多于一個數(shù)據(jù)庫名。【例3.9】使用DROP DATABASE語句刪除學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫。 DROP DATABASE學(xué)生成績管理系統(tǒng)3.3.2 T-SQL3.3.2 T-SQL語句管理數(shù)據(jù)庫語句管理數(shù)據(jù)庫4.4.分離數(shù)據(jù)庫分離數(shù)據(jù)庫可以使用sp_detach_db

22、存儲過程來執(zhí)行分離數(shù)據(jù)庫操作?！纠?.10】使用sp_detach_db存儲過程分離“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫。執(zhí)行語句如下所示：EXEC sp_detach_db學(xué)生成績管理系統(tǒng)3.3.2 T-SQL3.3.2 T-SQL語句管理數(shù)據(jù)庫語句管理數(shù)據(jù)庫5.5.附加數(shù)據(jù)庫附加數(shù)據(jù)庫【例3.11】下面將剛分離后的“學(xué)生成績管理系統(tǒng)”數(shù)據(jù)庫使用語句再附加到當(dāng)前數(shù)據(jù)庫實例中。執(zhí)行語句如下所示：CREATE DATABASE 學(xué)生成績管理系統(tǒng)ON( NAME=學(xué)生成績管理系統(tǒng),FILENAME = C:學(xué)生成績管理系統(tǒng).mdf)LOG ON( NAME=學(xué)生成績管理系統(tǒng)_LOG, FILENAME=C:學(xué)生成績管理系統(tǒng)_LOG.ldf)FOR ATTACH3.3.2 T-SQL3.3.2 T-SQL語句管理數(shù)據(jù)庫語句管理數(shù)據(jù)庫6.6.收縮數(shù)據(jù)庫收縮數(shù)據(jù)庫在S