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

1、,SQL Server數(shù)據(jù)庫,1,,使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫,2,,使用Transact-SQL語言創(chuàng)建數(shù)據(jù)庫,3,,數(shù)據(jù)庫的修改和刪除,4,,數(shù)據(jù)庫遷移,第2章 數(shù)據(jù)庫的創(chuàng)建與管理,2.1 SQL Server數(shù)據(jù)庫,SQL server 2000作為一個數(shù)據(jù)庫管理系統(tǒng)（DBMS），它的主要功能就是管理數(shù)據(jù)庫及其其他數(shù)據(jù)庫對象，為了方便用戶操作數(shù)據(jù)庫和數(shù)據(jù)庫對象，系統(tǒng)提供了兩個實用工具，其中一個是以圖形可視化界面操作數(shù)據(jù)庫及其

2、數(shù)據(jù)庫對象的企業(yè)管理器，另一個是以SQL語句方法操作數(shù)據(jù)庫及其數(shù)據(jù)庫對象的查詢分析器,一、SQL Server數(shù)據(jù)庫結(jié)構(gòu),數(shù)據(jù)庫的物理結(jié)構(gòu)數(shù)據(jù)庫文件類型數(shù)據(jù)文件主要數(shù)據(jù)文件和次要數(shù)據(jù)文件日志文件文件組主文件組用戶定義文件組數(shù)據(jù)庫的邏輯結(jié)構(gòu),數(shù)據(jù)庫的物理結(jié)構(gòu),在SQL server中任何一種數(shù)據(jù)庫的內(nèi)容都是以文件的形式存儲在磁盤上的，具體來說就是以數(shù)據(jù)庫文件和事務(wù)日志 文件的形式存儲在磁盤上的，所以我們通常說數(shù)據(jù)庫由數(shù)據(jù)庫

3、文件和事務(wù)日志 文件組成，一個數(shù)據(jù)庫至少應(yīng)包含一個數(shù)據(jù)庫文件和一個事務(wù)日志文件。,數(shù)據(jù)庫文件 類型,數(shù)據(jù)庫文件（Database File）是存放數(shù)據(jù)庫中的所有數(shù)據(jù)和數(shù)據(jù)庫對象（表、視圖等）的一種文件。分為兩種類類型：1. 主數(shù)據(jù)文件：是數(shù)據(jù)庫的起點，用來存儲數(shù)據(jù)庫的啟動信息和部分或全部數(shù)據(jù)。擴(kuò)展名為“.mdf”。注意：一個數(shù)據(jù)庫只能有一個主數(shù)據(jù)文件。2. 次數(shù)據(jù)文件：用來存儲主數(shù)據(jù)庫沒有存儲的其它數(shù)據(jù)。擴(kuò)展名為“.ndf”。注意：

4、一個數(shù)據(jù)庫可以有0個或多個次數(shù)據(jù)文件。,日志文件,日志文件用來記錄SQL Server的所有事務(wù)以及由這些事務(wù)引起的數(shù)據(jù)庫數(shù)據(jù)的變化。所謂事務(wù)就是一個單元的工作，該單元的工作要么全部完成，要么全部取消。 SQL Server 2000是遵守先寫日志再執(zhí)行數(shù)據(jù)庫修改的數(shù)據(jù)庫系統(tǒng)，在數(shù)據(jù)庫數(shù)據(jù)的任何變化寫到磁盤之前，首先在日志文件中做記錄，因此如果SQL Server系統(tǒng)發(fā)生出錯，甚至出現(xiàn)數(shù)據(jù)庫系統(tǒng)崩潰時，數(shù)據(jù)庫管理員（DBA）可以通過日

5、志文件完成數(shù)據(jù)庫的修復(fù)與重建。,日志文件,每個數(shù)據(jù)庫必須至少有一個日志文件，但可以不止一個。日志文件的默認(rèn)文件擴(kuò)展名是 .ldf。建立數(shù)據(jù)庫時，SQL Server會自動建立數(shù)據(jù)庫的事務(wù)日志。一般情況下，一個簡單的數(shù)據(jù)庫可以只有一個主數(shù)據(jù)文件和一個日志文件。如果數(shù)據(jù)庫很大或很重要，則可以設(shè)置多個次要數(shù)據(jù)文件或更多的日志文件。,文件組,為了對數(shù)據(jù)庫中的數(shù)據(jù)文件（主、次）進(jìn)行有效管理， SQL Server 系統(tǒng)允許將多個數(shù)據(jù)庫文件按照其

6、類型進(jìn)行分組，相同類型的歸納為同一組，并賦予此組一個名稱，這就是文件組。,文件組,SQL Server 2000有兩種類型的文件組：主文件組和用戶定義文件組。主文件組包含主要數(shù)據(jù)文件（.mdf）和任何沒有明確指派給其它文件組的其它文件。用戶定義文件組是在 CREATE DATABASE（創(chuàng)建數(shù)據(jù)庫）或 ALTER DATABASE（修改數(shù)據(jù)庫）語句中，使用 FILEGROUP 關(guān)鍵字另外指定的文件組。,使用文件組需要注意以下幾點

7、：,SQL Server2000中的文件或文件組不能由一個以上數(shù)據(jù)庫使用；每個數(shù)據(jù)庫中的文件只能是一個文件組的成員；日志文件不屬于任何文件組。如果文件組中的某個數(shù)據(jù)文件遭到破壞， 那么整個文件組中的數(shù)據(jù)都無法使用。,二、SQL Server數(shù)據(jù)庫類型,SQL Server 2000數(shù)據(jù)庫分為三種類型：系統(tǒng)數(shù)據(jù)庫、示例數(shù)據(jù)庫和用戶自定義的數(shù)據(jù)庫。相同點：三種數(shù)據(jù)庫都能夠創(chuàng)建數(shù)據(jù)庫對象、存儲數(shù)據(jù)以及管理數(shù)據(jù)不同點：（1）只

8、有系統(tǒng)數(shù)據(jù)庫可以用來操作和管理數(shù)據(jù)庫系統(tǒng)。（2）系統(tǒng)數(shù)據(jù)庫（4個）和樣本數(shù)據(jù)庫（2個）一般是在安裝SQL Server 2000的同時，系統(tǒng)自動建立的。 而用戶自定義的數(shù)據(jù)庫是用戶自己創(chuàng)建的。,系統(tǒng)數(shù)據(jù)庫● master 數(shù)據(jù)庫：是SQL Server 2000的總控數(shù)據(jù)庫。master 數(shù)據(jù)庫從整體上控制 SQL Server系統(tǒng)和用戶數(shù)據(jù)庫應(yīng)用程序的運行。它記錄了所有的登錄標(biāo)識\系統(tǒng)配置\ SQL Server初始化設(shè)置\用戶數(shù)

9、據(jù)庫及其初始化信息。由于master 數(shù)據(jù)庫是非常重要的一個系統(tǒng)的數(shù)據(jù)庫，所以一般禁止用戶直接訪問。● tempdb 數(shù)據(jù)庫 ：是SQL Server系統(tǒng)的一個臨時數(shù)據(jù)庫，所有連接到系統(tǒng)的用戶的臨時表和存儲過程都存儲在該數(shù)據(jù)庫中。在連接斷開時自動清除。,系統(tǒng)數(shù)據(jù)庫● model 數(shù)據(jù)庫 ：是建立所有數(shù)據(jù)庫的模板庫，所有在本系統(tǒng)中創(chuàng)建的新數(shù)據(jù)庫的內(nèi)容，剛開始都與這個模板數(shù)據(jù)庫完全一樣?！?msdb 數(shù)據(jù)庫 ：是SQL Server

10、2000 代理服務(wù)所使用的數(shù)據(jù)庫，用來執(zhí)行預(yù)定的任務(wù)，如數(shù)據(jù)庫備份和數(shù)據(jù)轉(zhuǎn)換、調(diào)度警報和作業(yè)等。,示例數(shù)據(jù)庫● Pubs和Northwind數(shù)據(jù)庫這兩個是樣本數(shù)據(jù)庫，SQL Server文檔和聯(lián)機(jī)叢書的大部分實例都是在這兩個示例數(shù)據(jù)庫基礎(chǔ)上生成的，這是為了方便初學(xué)者使用。,用戶數(shù)據(jù)庫,用戶數(shù)據(jù)庫是用戶在開發(fā)具體應(yīng)用程序時，因?qū)嶋H需要而在SQL Server 2000系統(tǒng)中建立的的數(shù)據(jù)庫，它們都以model系統(tǒng)數(shù)據(jù)庫為樣板。用戶數(shù)據(jù)庫

11、也可從其他數(shù)據(jù)庫管理系統(tǒng)建立的數(shù)據(jù)庫經(jīng)轉(zhuǎn)換而來。,,2.2 創(chuàng)建數(shù)據(jù)庫,若要創(chuàng)建數(shù)據(jù)庫，必須確定數(shù)據(jù)庫的名稱、所有者、大小，以及用于存儲數(shù)據(jù)庫的文件和文件組。,創(chuàng)建數(shù)據(jù)庫的方法 : 1、使用企業(yè)管理器（Enterprise Manager）創(chuàng)建數(shù)據(jù)庫。2、在查詢分析器中利用Transact-SQL語言創(chuàng)建數(shù)據(jù)庫。,1、使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫,（１）在企業(yè)管理器中，單擊工具欄中的 圖標(biāo)，或在數(shù)據(jù)庫文件夾或其下屬任一

12、數(shù)據(jù)庫圖標(biāo)上單擊右鍵，選擇新建數(shù)據(jù)庫選項。（２）在常規(guī)（General）頁框中，要求用戶輸入數(shù)據(jù)庫名稱以及排序規(guī)則名稱。 （３）點擊數(shù)據(jù)文件（Data Files）頁框，該頁框用來輸入數(shù)據(jù)庫文件的邏輯名稱、存儲位置、初始容量大小和所屬文件組名稱，如圖3-10所示。 （４）點擊事務(wù)日志（Transaction Log）頁框，該頁框用來設(shè)置事務(wù)日志文件信息，如圖3-11所示。 （５）單擊圖3-11中的“確定”按鈕，則開始創(chuàng)建新的數(shù)據(jù)

13、庫。,,2、使用Transact-SQL語言創(chuàng)建數(shù)據(jù)庫,簡單語法如下：CREATE DATABASE 數(shù)據(jù)庫名 [ON 子句 ] [LOG ON 子句 ]說明： ON子句：用來定義數(shù)據(jù)文件(.mdf .ndf）屬性。LOG ON句:用來定義日志文件（.ldf）屬性,1. ON 子句格式 [ON [PRIMARY] （[name=‘?dāng)?shù)據(jù)文件邏輯文件名’，] filename=‘?dāng)?shù)據(jù)文件物

14、理文件名’ [，SIZE=size] [，MAXSIZE={max_size|UNLIMITED}] [，F(xiàn)ILEGROWTH=growth_increment] ) [，…n] [， FILEGROUP 文件組名 ( 數(shù)據(jù)文件定義) [，…n] ] ],關(guān)于數(shù)據(jù)庫文件以及 所屬文件組的定義選項。（ [,…n]表示可以

15、定義多個）,指數(shù)據(jù)庫創(chuàng)建后在所有T-SQL語句中引用該文件時的名字。,存放到磁盤上時的具體路徑和文件名。操作系統(tǒng)借助于該項去訪問相應(yīng)數(shù)據(jù)。,指定數(shù)據(jù)庫文件的初始大小,指定數(shù)據(jù)庫文件的最大大小,表明文件大小不限,指定數(shù)據(jù)庫文件每次的增量 ：10% 2MB,2. LOG ON 子句格式 [LOG ON （[name=‘日志文件邏輯文件名’，] filename=‘日志文件物理文件名’ [，SIZE=

16、初始大小] [，MAXSIZE={max_size|UNLIMITED}] [，F(xiàn)ILEGROWTH=growth_increment] ) [，…n] ],【例2-1】創(chuàng)建一個BOOK數(shù)據(jù)庫,CREATE DATABASE BOOKON(NAME=BOOK1, FILENAME='F:\SQL2008\BOOK1.MDF', SIZE=5, MAXSIZE=15,

17、 FILEGROWTH=2),(NAME=BOOK2, FILENAME='F:\SQL2008\BOOK2.NDF', SIZE=2,,FILEGROUP yyy,【例2-1】創(chuàng)建一個BOOK數(shù)據(jù)庫,MAXSIZE=10, FILEGROWTH=10%)LOG ON(NAME=BOOK_LOG, FILENAME='F:\SQL2008\BOOK_LOG.LDF', SIZE=1,

18、MAXSIZE=10, FILEGROWTH=1),2.4 修改數(shù)據(jù)庫,修改數(shù)據(jù)庫的方法 : 1、使用企業(yè)管理器（Enterprise Manager）修改數(shù)據(jù)庫。2、在查詢分析器中利用Transact-SQL語言修改數(shù)據(jù)庫。,在企業(yè)管理器中修改數(shù)據(jù)庫,方法：選定某個數(shù)據(jù)庫? 右鍵單擊快捷菜單的屬性。擴(kuò)大數(shù)據(jù)庫：按至少1MB增加收縮數(shù)據(jù)庫：自動和手動,利用企業(yè)管理器修改數(shù)據(jù)庫,設(shè)置中的各選項說明如下：ANSI NULL默

19、認(rèn)設(shè)置：允許在數(shù)據(jù)庫表的列中輸入空（NULL）值。 遞歸觸發(fā)器：允許觸發(fā)器遞歸調(diào)用。SQL Server設(shè)定的觸發(fā)器遞歸調(diào)用的層數(shù)最多為32層。自動更新統(tǒng)計信息：允許使用SELECT INTO或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入數(shù)據(jù)。 殘缺頁檢測：允許自動檢測有損壞的頁。自動關(guān)閉：當(dāng)數(shù)據(jù)庫中無用戶時，自動關(guān)閉該數(shù)據(jù)庫，并將所占用的資源交還給操作系統(tǒng)。,自動收縮：允許定期對數(shù)據(jù)庫進(jìn)行檢查，當(dāng)數(shù)據(jù)庫文

20、件或日志文件的未用空間超過其大小的25%時，系統(tǒng)將會自動縮減文件使其未用空間等于25%。自動創(chuàng)建統(tǒng)計信息：在優(yōu)化查詢（Query Optimizer）時，根據(jù)需要自動創(chuàng)建統(tǒng)計信息。使用被引用的標(biāo)識符：標(biāo)識符必須用雙引號括起來，且可以不遵循Transact-SQL命名標(biāo)準(zhǔn)。,2. 利用企業(yè)管理器修改數(shù)據(jù)庫,用Transact-SQL語句修改數(shù)據(jù)庫,在查詢分析器中，可以使用ALTER DATABASE語句來對數(shù)據(jù)庫進(jìn)行修改。ALTER

21、 DATABASE功能: 在數(shù)據(jù)庫中添加或刪除文件和文件組。更改數(shù)據(jù)庫名稱、文件組名稱以及數(shù)據(jù)文件和日志文件的邏輯名稱及其各種屬性。,簡要語法,ALTER DATABASE 數(shù)據(jù)庫名 { ADD FILE [ ,...n ] |ADD LOG FILE [ ,...n ] |REMOVE FILE 邏輯文件名 |ADD FILEGROUP 文件組名| REMOVE F

22、ILEGROUP 文件組名| MODIFY FILE | MODIFY NAME =新數(shù)據(jù)庫名},內(nèi)容: ( NAME =邏輯文件名 [ ,FILENAME = ‘物理文件名’][ , SIZE = 文件大小 ][ , MAXSIZE = { maxsize | UNLIMITED } ][ , FILEGROWTH = 文件的增長增量 ]),【例子2-2】,ALTER DATABASE BOOKMODIFY F

23、ILE(NAME=book1,SIZE=8)ALTER DATABASE BOOKREMOVE FILE book2,Name選項必須有；文件創(chuàng)建后只能修改其size maxsize filegrowth屬性，不能修改name \filename;Size的值只能比原來 擴(kuò)大,【例子2-3】,ALTER DATABASE TEACHDBADD FILE(NAME=TEACHDB1, FILENAME='F

24、:\SQL2008\TEACHDB1.NDF',SIZE=4,MAXSIZE=10,FILEGROWTH=10%),用Transact-SQL語句收縮數(shù)據(jù)庫,語法: DBCC SHRINKDATABASE (數(shù)據(jù)庫名[,目標(biāo)百分比])說明:目標(biāo)百分比指收縮后文件中所要剩余的可用空間的百分比.(語法上應(yīng)是一個正數(shù),分?jǐn)?shù)學(xué)上的百分比數(shù)據(jù))【例子2-4】dbcc shrinkdatabase(teachdb,10),

25、刪除數(shù)據(jù)庫,刪除數(shù)據(jù)庫的方法 : 1、使用企業(yè)管理器（Enterprise Manager）刪除數(shù)據(jù)庫。2、在查詢分析器中利用Transact-SQL語言刪除數(shù)據(jù)庫。,其語法如下：DROP DATABASE 數(shù)據(jù)庫名[,…n]【例3-3】drop database book,2.5 數(shù)據(jù)庫遷移,分離和附加SQL Server數(shù)據(jù)庫 1．分離和附加數(shù)據(jù)庫的作用當(dāng)在SQL Server 2000中新建一個數(shù)據(jù)庫時，系統(tǒng)

26、數(shù)據(jù)庫master記載了此數(shù)據(jù)庫的相應(yīng)信息，從而將它附加到SQL Server中來。此時，服務(wù)器擁有對該數(shù)據(jù)庫的一切管轄權(quán)，包括對它的所有訪問和管理操作。,分離和附加數(shù)據(jù)庫的作用,但出于以下原因，我們有時需要將一個數(shù)據(jù)庫從SQL Server中分離出來，使其中的所有數(shù)據(jù)文件和日志文件脫離服務(wù)器獨立存在，然后又將它附加到原SQL Server或另一臺計算機(jī)的服務(wù)器中：例如：將數(shù)據(jù)庫移到其他計算機(jī)的SQL Server中使用。例如：改變

27、存放數(shù)據(jù)庫數(shù)據(jù)文件和日志文件的物理位置、名稱。,分離數(shù)據(jù)庫,數(shù)據(jù)庫分離以后，在企業(yè)管理器樹狀結(jié)構(gòu)窗口中就找不到該數(shù)據(jù)庫了。此時，可將該數(shù)據(jù)庫對應(yīng)的數(shù)據(jù)文件和日志文件移動到其他磁盤或計算機(jī)去，再進(jìn)行附加數(shù)據(jù)庫操作。,分離數(shù)據(jù)庫,通過數(shù)據(jù)庫分離來改變數(shù)據(jù)庫中數(shù)據(jù)文件和日志文件的存儲位置例如：創(chuàng)建了一個數(shù)據(jù)庫BOOK,原數(shù)據(jù)文件和日志文件存放位置：D:\SQL2008\BOOK1.MDFD:\SQL2008\BOOK2.NDF(1)將

28、主數(shù)據(jù)文件BOOK1.MDF改名、改存儲位置。 D :\bookshop.mdf(2)將次數(shù)據(jù)文件BOOK2.NDF改名。D:\SQL2008\BOOKshop.NDF,附加數(shù)據(jù)庫,啟動企業(yè)管理器在企業(yè)管理器樹狀結(jié)構(gòu)窗口中，在數(shù)據(jù)庫結(jié)點上單擊鼠標(biāo)右鍵，在彈出的快捷菜單上，選擇所有任務(wù)中的“附加數(shù)據(jù)庫”命令，如圖所示。在“附加數(shù)據(jù)庫”對話框中的“要附加數(shù)據(jù)庫的MDF文件:[M]”文本框中輸入主數(shù)據(jù)文件的存放路徑及文件名，也可單擊

29、…按鈕查找選擇對應(yīng)的.mdf文件。這里找到已經(jīng)WINDOWS資源管理器移到D盤根目錄并改名bookshop.mdf的主數(shù)據(jù)文件。,附加數(shù)據(jù)庫,,選定主數(shù)據(jù)文件后，在中間框中將顯示原數(shù)據(jù)庫各文件名和當(dāng)前文件位置。如果在當(dāng)前主數(shù)據(jù)文件所在的位置找不到原來的文件，則在該文件的當(dāng)前位置行會出現(xiàn)紅色“╳”提示，如上頁圖所示。這時就應(yīng)重新輸入正確的路徑和文件名。 用鼠標(biāo)和鍵盤手工修正了錯誤的文件位置和文件名后，原來的“╳”將變成“√”。 

30、;  可在“附加為[A]：”右邊的文本框中輸入該數(shù)據(jù)庫附加到SQL Server后的新文件名。在“指定數(shù)據(jù)庫所有者：[S]”右邊的列表框中選擇一個用戶帳戶作為該數(shù)據(jù)庫的所有者。  按“確定”按鈕即顯示消息框，提示附加數(shù)據(jù)庫順利完成。 提示：SQL Server 2000中，也可以使用系統(tǒng)存儲過程sp_detach_db來分離數(shù)據(jù)庫，用sp_attach_db來附加數(shù)據(jù)庫。,導(dǎo)入和導(dǎo)出數(shù)據(jù),在SQL Server 2

31、000系統(tǒng)中，我們不但可以通過分離和附加數(shù)據(jù)庫實現(xiàn)對SQL Server數(shù)據(jù)庫的遷移，還可以利用系統(tǒng)工具在SQL Server數(shù)據(jù)庫和其他異種數(shù)據(jù)庫之間進(jìn)行數(shù)據(jù)的導(dǎo)入和導(dǎo)出。利用SQL Server 2000系統(tǒng)提供的導(dǎo)入和導(dǎo)出數(shù)據(jù)工具可以把SQL Server數(shù)據(jù)庫（表）中的數(shù)據(jù)直接轉(zhuǎn)換為其它系統(tǒng)可以使用的數(shù)據(jù)，例如：轉(zhuǎn)換成FoxPro數(shù)據(jù)庫、Excel表格、Access數(shù)據(jù)庫、Oracle數(shù)據(jù)庫，甚至文本文件等。同樣也可以將這些系

32、統(tǒng)中的數(shù)據(jù)轉(zhuǎn)換為SQL Server中的數(shù)據(jù)，供SQL Server使用。從而實現(xiàn)各種不同系統(tǒng)和應(yīng)用之間數(shù)據(jù)的移植和共享。,導(dǎo)入和導(dǎo)出數(shù)據(jù),數(shù)據(jù)的導(dǎo)入或?qū)С霾粌H牽涉到數(shù)據(jù)傳輸，還存在數(shù)據(jù)格式轉(zhuǎn)換等問題。這些工作實際上都是借助于SQL Server提供的DTS（Data Transform Services）--數(shù)據(jù)傳輸服務(wù)來完成的。,1. 使用“導(dǎo)入導(dǎo)出數(shù)據(jù)”工具將數(shù)據(jù)在SQL Server表中excel電子表格中轉(zhuǎn)換。 【例如】將

33、student 數(shù)據(jù)庫中的class_info導(dǎo)出到excel表中。 方法－：在企業(yè)管理器窗口中選擇‘工具’菜單－>‘?dāng)?shù)據(jù)轉(zhuǎn)換服務(wù)’－>‘導(dǎo)出數(shù)據(jù)’ 方法二：選擇開始菜單－>程序－>Mcirsoft SQL Server－>導(dǎo)入導(dǎo)出數(shù)據(jù) 方法三：在企業(yè)管理器窗口中選擇‘工具’菜單－向?qū)?gt;‘?dāng)?shù)據(jù)轉(zhuǎn)換服務(wù)’－>‘導(dǎo)出向?qū)А?注意：可以借助于這種方式進(jìn)行表