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

1、第二章 數(shù)據(jù)庫的創(chuàng)建與管理2.1 SQL Server數(shù)據(jù)庫2.1.1 數(shù)據(jù)庫的結(jié)構(gòu) 2.1.2 系統(tǒng)數(shù)據(jù)庫2.2 使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫2.2.1企業(yè)管理器的工作界面2.2.2企業(yè)管理器創(chuàng)建用戶數(shù)據(jù)庫2.2.3向?qū)?chuàng)建用戶數(shù)據(jù)庫2.3使用T-SQL語句創(chuàng)建數(shù)據(jù)庫2.3.1查詢分析器2.3.2 用T-SQL語句創(chuàng)建數(shù)據(jù)庫2.4 修改和刪除數(shù)據(jù)庫2.4.1 修改數(shù)據(jù)庫2.4.2 刪除數(shù)據(jù)庫2.5 數(shù)據(jù)庫遷移2.5.1分離和附加數(shù)據(jù)庫2.5.2 導(dǎo)入和導(dǎo)出數(shù)據(jù)本章主要內(nèi)容1、本章介紹SQL Server數(shù)據(jù)庫的組成和功能;2、數(shù)據(jù)庫日常管理;3、企業(yè)管理器使用;4、查詢分析器的使用;5、數(shù)據(jù)庫遷

2、移。本章重點1、企業(yè)管理器使用;2、查詢分析器的使用;3、數(shù)據(jù)庫遷移。本章難點數(shù)據(jù)庫遷移。授課方法講授+實驗+作業(yè)學(xué)時：6課時授課過程如下：2.1 SQL Server數(shù)據(jù)庫2.1.1 數(shù)據(jù)庫的結(jié)構(gòu) 2.1數(shù)據(jù)庫的存儲結(jié)構(gòu) 數(shù)據(jù)庫的存儲結(jié)構(gòu)分為邏輯存儲結(jié)構(gòu)和物理存儲結(jié)構(gòu)兩種。 數(shù)據(jù)庫的邏輯存儲結(jié)構(gòu)指的是，SQL Server的數(shù)據(jù)庫是由諸如表、視圖、索引等各種不同的數(shù)據(jù)庫對象所組成。數(shù)據(jù)庫的物理存儲結(jié)構(gòu)是討論數(shù)據(jù)庫文件是如何在磁盤上存儲的，數(shù)據(jù)庫在磁盤上是以文件為單位存儲的，由數(shù)據(jù)庫文件和事務(wù)日志文件組成，一個數(shù)據(jù)庫至少應(yīng)該包含一個數(shù)據(jù)庫文件和一個事務(wù)日志文件。 一、 數(shù)據(jù)庫文件 1主數(shù)據(jù)庫文

3、件（Primary Database File） 一個數(shù)據(jù)庫可以有一個或多個數(shù)據(jù)庫文件，一個數(shù)據(jù)庫文件只能屬于一個數(shù)據(jù)庫。當(dāng)有多個數(shù)據(jù)庫文件時，有一個文件被定義為主數(shù)據(jù)庫文件（簡稱為主文件），其擴(kuò)展名為mdf主數(shù)據(jù)庫文件用來存儲數(shù)據(jù)庫的啟動信息以及部分或者全部數(shù)據(jù)，是所有數(shù)據(jù)庫文件的起點，包含指向其它數(shù)據(jù)庫文件的指針。一個數(shù)據(jù)庫只能有一個主數(shù)據(jù)庫文件。 2 次數(shù)據(jù)庫文件（Secondary Database File） 用于存儲主數(shù)據(jù)庫文件中未存儲的剩余數(shù)據(jù)和數(shù)據(jù)庫對象，一個數(shù)據(jù)庫可以沒有次數(shù)據(jù)庫文件，但也可以同時擁有多個輔助數(shù)據(jù)庫文件。 次數(shù)據(jù)庫文件的擴(kuò)展名為ndf（簡稱為輔助文件）。 3事

4、務(wù)日志文件 存儲數(shù)據(jù)庫的更新情況等事務(wù)日志信息 ，當(dāng)數(shù)據(jù)庫損壞時，管理員使用事務(wù)日志恢復(fù)數(shù)據(jù)庫。每一個數(shù)據(jù)庫至少必須擁有一個事務(wù)日志文件，而且允許擁有多個日志文件。事務(wù)日志文件的擴(kuò)展名為ldf，日志文件的大小至少是512KB。 SQL Server事務(wù)日志采用提前寫入的方式 SQL Server 2000的文件擁有兩個名稱，即邏輯文件名和物理文件名。當(dāng)使用Transact-SQL命令語句訪問某一個文件時，必須使用該文件的邏輯名 。物理文件名是文件實際存儲在磁盤上的文件名，而且可包含完整的磁盤目錄路徑。 SQL Server 2000用文件來存放數(shù)據(jù)庫，數(shù)據(jù)庫文件有三類。1. 主數(shù)據(jù)庫文件（Pr

5、imary）：(1)存儲數(shù)據(jù)庫的啟動信息,是所有數(shù)據(jù)庫文件的起點，包含指向其它數(shù)據(jù)庫文件的指針 (2)存放數(shù)據(jù)，每個數(shù)據(jù)庫都必須有一個主數(shù)據(jù)文件。2. 次要數(shù)據(jù)文件（Secondary）：存放數(shù)據(jù)，一個數(shù)據(jù)庫可以沒有也可以有多個Secondary文件。3. 事務(wù)日志文件（Transaction Log）：存放事務(wù)日志，每個數(shù)據(jù)庫至少有一個或多個日志文件。一般情況下，一個簡單的數(shù)據(jù)庫可以只有一個主數(shù)據(jù)文件和一個日志文件。如果數(shù)據(jù)庫很大，則可以設(shè)置多個Secondary文件和日志文件，并將它們放在不同的磁盤上。默認(rèn)狀態(tài)下，數(shù)據(jù)庫文件存放在MSSQLdata目錄下，數(shù)據(jù)文件名為“數(shù)據(jù)庫名_Data.

6、MDF”，日志文件名為“數(shù)據(jù)庫名_Log.LDF”。數(shù)據(jù)庫的創(chuàng)建者可以在創(chuàng)建時指定其他的路徑和文件名，也可以添加Secondary文件和更多的日志文件。二、數(shù)據(jù)庫文件組 利用文件組可以使服務(wù)器的性能得到提高。 (系統(tǒng)提供)主文件組中包含了所有的系統(tǒng)表，當(dāng)建立數(shù)據(jù)庫時，主文件組包括主數(shù)據(jù)庫文件和未指定組的其他文件。用戶定義文件組中可以指定一個缺省文件組，那么在創(chuàng)建數(shù)據(jù)庫對象時如果沒有指定將其放在哪一個文件組中，就會將它放在缺省文件組中。如果沒有指定缺省文件組，則主文件組為缺省文件組。文件組允許多個數(shù)據(jù)庫文件組成一個組，并對它們整體進(jìn)行管理。比如，可以將三個數(shù)據(jù)文件（data1.ndf、data2

7、.ndf和data3.ndf）分別創(chuàng)建在三個盤上，這三個文件組成文件組fgroup1，在創(chuàng)建表的時候，就可以指定一個表創(chuàng)建在文件組fgroup1上。這樣該表的數(shù)據(jù)就可以分布在三個盤上，在對該表執(zhí)行查詢時，可以并行操作，大大提高了查詢效率。SQL Server的數(shù)據(jù)庫文件和文件組必須遵循以下規(guī)則：（1）一個文件和文件組只能被一個數(shù)據(jù)庫使用。（2）一個文件只能屬于一個文件組。（3）數(shù)據(jù)和事務(wù)日志不能共存于同一文件或文件組上。（4）日志文件不能屬于文件組。三數(shù)據(jù)庫對象SQL Server 2000數(shù)據(jù)庫中的數(shù)據(jù)在邏輯上被組織成一系列對象，當(dāng)一個用戶連接到數(shù)據(jù)庫后，他所看到的是這些邏輯對象，而不是物理

8、的數(shù)據(jù)庫文件。SQL Server 2000中有以下數(shù)據(jù)庫對象：表（Table）、視圖（View）、存儲過程 （Stored procedures）、觸發(fā)器（Triggers）、用戶自定義數(shù)據(jù)類型（User-defined data types）、用戶自定義函數(shù)（User-defined functions）、索引（Indexes）、規(guī)則（Constraints）、默認(rèn)值（Defaults）等。2.1.2 系統(tǒng)數(shù)據(jù)庫在創(chuàng)建任何數(shù)據(jù)庫之前，打開企業(yè)管理器的“服務(wù)器/數(shù)據(jù)庫”目錄，可以看到系統(tǒng)中已經(jīng)有了6個數(shù)據(jù)庫。如圖2-1所示。圖2-1 SQL Server 2000中的系統(tǒng)數(shù)據(jù)庫和示例數(shù)據(jù)庫其

9、中，有4個是SQL Server 2000的系統(tǒng)數(shù)據(jù)庫，它們分別是：master數(shù)據(jù)庫、tempdb數(shù)據(jù)庫、model數(shù)據(jù)庫和msdb數(shù)據(jù)庫。1master數(shù)據(jù)庫master數(shù)據(jù)庫記錄了SQL Server系統(tǒng)級的信息，包括系統(tǒng)中所有的登錄賬號、系統(tǒng)配置信息、所有數(shù)據(jù)庫的信息、所有用戶數(shù)據(jù)庫的主文件地址等。每個數(shù)據(jù)庫都有屬于自己的一組系統(tǒng)表，記錄了每個數(shù)據(jù)庫各自的系統(tǒng)信息，這些表在創(chuàng)建數(shù)據(jù)庫時自動產(chǎn)生。為了與用戶創(chuàng)建的表相區(qū)別，這些表被稱為系統(tǒng)表，表名都以“sys”開頭。master數(shù)據(jù)庫中還有很多系統(tǒng)存儲過程和擴(kuò)展存儲過程。系統(tǒng)存儲過程是預(yù)先編譯好的程序，所有的系統(tǒng)存儲過程的名字都以sp_開

10、頭。2tempdb數(shù)據(jù)庫tempdb 數(shù)據(jù)庫用于存放所有連接到系統(tǒng)的用戶臨時表和臨時存儲過程以及SQL Server產(chǎn)生的其他臨時性的對象。tempdb是SQL Server中負(fù)擔(dān)最重的數(shù)據(jù)庫，因為幾乎所有的查詢都可能需要使用它。在SQL Server 關(guān)閉時，tempdb數(shù)據(jù)庫中的所有對象都被刪除，每次啟動SQL Server時，tempdb數(shù)據(jù)庫里面總是空的。3model數(shù)據(jù)庫model數(shù)據(jù)庫是系統(tǒng)所有數(shù)據(jù)庫的模板，這個數(shù)據(jù)庫相當(dāng)于一個模子，所有在系統(tǒng)中創(chuàng)建的新數(shù)據(jù)庫的內(nèi)容，在剛創(chuàng)建時都和model數(shù)據(jù)庫完全一樣。如果SQL Server專門用作一類應(yīng)用，而這類應(yīng)用都需要某個表，甚至在這個

11、表中都要包括同樣的數(shù)據(jù)，那么就可以在model數(shù)據(jù)庫中創(chuàng)建這樣的表，并向表中添加那些公共的數(shù)據(jù)，以后每一個新創(chuàng)建的數(shù)據(jù)庫中都會自動包含這個表和這些數(shù)據(jù)。當(dāng)然，也可以向model數(shù)據(jù)庫中增加其他數(shù)據(jù)庫對象，這些對象都能被以后創(chuàng)建的數(shù)據(jù)庫所繼承。4msdb數(shù)據(jù)庫msdb 數(shù)據(jù)庫被SQL Server代理（SQL Server Agent）來安排報警、作業(yè)，并記錄操作員。SQL Server在安裝時自動創(chuàng)建了另外兩個數(shù)據(jù)庫：pubs和northwind，它們是SQL Server的示例數(shù)據(jù)庫，示例數(shù)據(jù)庫是讓讀者作為學(xué)習(xí)工具使用的。2.2 創(chuàng)建數(shù)據(jù)庫1使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫在企業(yè)管理器中創(chuàng)建數(shù)據(jù)庫可

12、以按以下步驟操作：（1）選中將要使用的Server，用鼠標(biāo)右鍵單擊數(shù)據(jù)庫，在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”，如圖2-2所示。（2）打開新建數(shù)據(jù)庫對話框的“常規(guī)”選項卡，在名稱欄中輸入數(shù)據(jù)庫的名稱，如圖5-3所示。（3）在圖2-3的對話框中選擇“數(shù)據(jù)文件”選項卡，如圖2-4所示。在這個對話框中可以增減數(shù)據(jù)文件，對每一個數(shù)據(jù)文件設(shè)置以下內(nèi)容：文件名：也叫邏輯文件名，以后就以這個名字來代表這個數(shù)據(jù)文件。文件存放的路徑：也叫物理文件名。文件初始大小。文件大小是否有限制，最大是多少。文件增長方式是以兆字節(jié)增長還是以百分比增長，每次增長的幅度。圖2-2 創(chuàng)建新數(shù)據(jù)庫 圖2-3 輸入數(shù)據(jù)庫名“TEACH

13、DB” 圖2-4 設(shè)置數(shù)據(jù)庫文件、文件大小和增長方式（4）選擇“事務(wù)日志”選項卡，指定存放日志文件的位置、初始大小、增長方式。對每一個日志文件，做和數(shù)據(jù)文件一樣的設(shè)置。如圖5-5所示。（5）設(shè)置完成之后，單擊“確定”按鈕。以上創(chuàng)建了一個名為TEACHDB的數(shù)據(jù)庫，并為它創(chuàng)建了一個主數(shù)據(jù)庫文件和一個日志文件。存放在C盤的MSSQLdata目錄下，初始大小是1M，擴(kuò)展文件時按10%的幅度增長，沒有限制大小。圖2-5 設(shè)置日志文件 、文件大小和增長方式2使用T-SQL語句創(chuàng)建數(shù)據(jù)庫CREATE DATABASE database_name &&定義數(shù)據(jù)庫名必須唯一 ON PRIMAR

14、Y &&聲名以下為定義數(shù)據(jù)文件 | 指定為主文數(shù)據(jù)文件（NAME=logical_file_name， &&邏輯文件名由用戶定義FILENAME=os_file_name &&物理文件名:包括文件路徑和文件名，SIZE=size &&文件初始大小，MAXSIZE=max_size|UNLIMITED &&文件可以增長的最大值，F(xiàn)ILEGROWTH=growth_increment &&文件的增量FILEGROUP filegroup_name &&文件組的定義（NAME=logica

15、l_file_name， &&次要數(shù)據(jù)文件FILENAME=os_file_name，SIZE=size，MAXSIZE=max_size|UNLIMITED，F(xiàn)ILEGROWTH=growth_increment ） LOG ON &&聲名以下定義為日志文件（NAME=logical_file_name， &&邏輯文件名由用戶定義FILENAME=os_file_name &&物理文件名:包括文件路徑和文件名，SIZE=size，MAXSIZE=max_size|UNLIMITED，F(xiàn)ILEGROWTH=growth_incre

16、ment 說明：在Transact-SQL語言的命令格式中，用 括起來的內(nèi)容表示是可選的；類似A|B的格式，表示A和B只能選擇一個，不能同時都選。n database_name：新數(shù)據(jù)庫的名稱。數(shù)據(jù)庫名稱在服務(wù)器中必須唯一，最長為128個字符，并且要符合標(biāo)識符的命名規(guī)則。每個服務(wù)器管理的數(shù)據(jù)庫最多為32767個。n ON：指定存放數(shù)據(jù)庫的數(shù)據(jù)文件信息。n PRIMARY：用于指定主文件,主文件組的第一個文件是主文件。如果不指定PRIMARY關(guān)鍵字，則在命令中列出的第一個文件將被默認(rèn)為主文件。n LOG ON：指明事務(wù)日志文件的明確定義。如果沒有本選項，則系統(tǒng)會自動產(chǎn)生一個文件名前綴與數(shù)據(jù)庫名相

17、同，容量為所有數(shù)據(jù)庫文件大小1/4的事務(wù)日志文件。n NAME：指定數(shù)據(jù)庫的邏輯名稱。n FILENAME：指定數(shù)據(jù)庫所在文件的操作系統(tǒng)文件名稱和路徑，該操作系統(tǒng)文件名和NAME的邏輯名稱一一對應(yīng)。n SIZE：指定數(shù)據(jù)庫的初始容量大小。如果沒有指定主文件的大小，則SQL Server默認(rèn)其與模板數(shù)據(jù)庫中的主文件大小一致，其它數(shù)據(jù)庫文件和事務(wù)日志文件則默認(rèn)為1MB。指定大小的數(shù)字size可以使用KB、MB、GB和TB后綴，默認(rèn)的后綴為MB。Size中不能使用小數(shù)，其最小值為512KB，默認(rèn)值為1MB。主文件的size不能小于模板數(shù)據(jù)庫中的主文件。n MAXSIZE：指定操作系統(tǒng)文件可以增長到的

18、最大尺寸。如果沒有指定，則文件可以不斷增長直到充滿磁盤。n FILEGROWTH：指定文件每次增加容量的大小，當(dāng)指定數(shù)據(jù)為0時，表示文件不增長。增加量可以確定為以KB、MB作后綴的字節(jié)數(shù)或以%作后綴的被增加容量文件的百分比來表示。默認(rèn)后綴為MB。如果沒有指定FILEGROWTH，則默認(rèn)值為10%，每次擴(kuò)容的最小值為64KB。 通過執(zhí)行以下的語句可以創(chuàng)建TEST數(shù)據(jù)庫。1:示例:1CREATE DATABASE test ON( NAME = test1,FILENAME = 'e:sqlMSSQLdata test1.MDF',SIZE = 1,FILEGROWTH = 10%

19、),filegroup fgroup1( NAME = test2,FILENAME = 'e:sqlMSSQLdata test2.NDF',SIZE = 1,FILEGROWTH = 10%)LOG ON( NAME = test_Log,FILENAME = 'e:sqlMSSQLdata test_Log.LDF',SIZE = 5, MAXSIZE=10,FILEGROWTH = 10%)例2CREATE DATABASE book ON( NAME =book1,FILENAME = 'C:Program FilesMicrosoftMSSQ

20、Ldatabook1.MDF',SIZE = 1,FILEGROWTH = 10%)LOG ON( NAME = book_Log,FILENAME = 'C: Program FilesMicrosoft MSSQLdatabook_Log.LDF',SIZE = 5, MAXSIZE=10,FILEGROWTH = 10%)3.我們也可以簡單地來創(chuàng)建數(shù)據(jù)庫：CREATE DATABASE mytest由于沒有指定主文件名，在默認(rèn)的情況下，命名主文件為mytest.mdf，日志文件名為mytest_log.log。同時由于按復(fù)制Model數(shù)據(jù)庫的方式來創(chuàng)建新的數(shù)據(jù)庫，

21、主文件和日志文件的大小都同Model數(shù)據(jù)庫的主文件和日志文件大小一致，并且可以自由增長。2.2 修改數(shù)據(jù)庫數(shù)據(jù)庫2.3.doc修改數(shù)據(jù)庫包括增減數(shù)據(jù)文件和日志文件、修改文件屬性包括:u 增加或刪除數(shù)據(jù)文件u 更改數(shù)據(jù)文件的大小和增長方式u 增加或刪除日志文件u 增加或刪除文件組2.4 用企業(yè)管理器修改數(shù)據(jù)庫使用企業(yè)管理器中的數(shù)據(jù)庫屬性對話框可以很方便地修改數(shù)據(jù)庫。一個已存在的數(shù)據(jù)庫的屬性對話框包括6個選項卡，如圖所示。1增減數(shù)據(jù)文件和日志文件用戶可以使用“數(shù)據(jù)文件”選項卡增減數(shù)據(jù)文件和修改數(shù)據(jù)文件屬性。用戶可以使用“事務(wù)日志”選項卡增減日志文件和修改日志文件屬性，具體的做法與對數(shù)據(jù)文件的操作相

22、同。2修改數(shù)據(jù)庫選項使用數(shù)據(jù)庫屬性對話框的“選項”選項卡可以修改一些數(shù)據(jù)庫選項，只需用鼠標(biāo)單擊這些復(fù)選框，就可以非常容易地更改當(dāng)前數(shù)據(jù)庫的選項。勾選的就是True，否則就是False。如圖所示。 圖2-13 修改LWZZ數(shù)據(jù)庫的數(shù)據(jù)文件 圖2-14 使用“選項”選項卡修改數(shù)據(jù)庫選項比較常用的數(shù)據(jù)庫選項如下：（1）限制訪問：只允許特殊用戶訪問數(shù)據(jù)庫。如圖5-14所示，限制訪問有兩種類型：只允許db_owner、dbcreator和sysadmin的成員訪問。設(shè)置數(shù)據(jù)庫為單用戶模式。如果設(shè)置為單用戶模式之前已有用戶在使用該數(shù)據(jù)庫，那么這些用戶可以繼續(xù)使用，但新的用戶必須等到所有用戶都退出之后才能登

23、錄。（2）只讀：數(shù)據(jù)庫中的數(shù)據(jù)只能讀取，而不能修改。（3）自動關(guān)閉：用于指定數(shù)據(jù)庫在沒有用戶訪問并且所有進(jìn)程結(jié)束時自動關(guān)閉，釋放所有資源，當(dāng)又有新的用戶要求連接時，數(shù)據(jù)庫自動打開。數(shù)據(jù)庫自動關(guān)閉后，數(shù)據(jù)文件可以像普通文件一樣處理（例如拷貝或作為電子郵件的附件發(fā)送），所以這個選項很適合移動用戶，而對于網(wǎng)絡(luò)應(yīng)用數(shù)據(jù)庫，則最好不要設(shè)置這個選項，因為頻繁地關(guān)閉和重新打開會對數(shù)據(jù)庫性能造成極大的影響。（4）自動縮減：當(dāng)數(shù)據(jù)或日志量較少時自動縮小數(shù)據(jù)庫文件的大小，當(dāng)設(shè)置了只讀時，這個選項無效。3使用SQL語句修改數(shù)據(jù)庫（1）用ALTER DATABASE語句增減數(shù)據(jù)文件和日志文件。例如：ALTER DAT

24、ABASE LWZZ ADD FILE (NAME =LWZZ_data2,FILENAME='C: Program FilesMicrosoft MSSQLdataLWZZ_data2.ndf'SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=1MB)其中ADD FILE 是指增加一個數(shù)據(jù)文件，還可以是ADD LOG FILE、REMOVE FILE、MODIFY FILE 等，分別代表增加日志文件、刪除數(shù)據(jù)庫文件和修改文件信息。比如，以下的語句將LWZZ 數(shù)據(jù)庫的第二個數(shù)據(jù)文件LWZZ_data2的初始大小修改為20MB。ALTER DATABASE LWZ

25、Z MODIEY FILE ( NAME = LWZZ_data2, SIZE=20MB)關(guān)于ALTER DATABASE 語句的更詳細(xì)用法可以參考SQL Server 2000的在線手冊。（2）用ALTER DATABASE語句修改數(shù)據(jù)庫選項ALTER DATABASE LWZZ SET AUTO_SHRINK ON（3）使用系統(tǒng)存儲過程sp_dboption修改數(shù)據(jù)庫選項Sp_dboption 'LWZZ', 'autoshrink', 'true'除了設(shè)置選項外，Sp_dboption還有很多其他的作用。如運(yùn)行不帶參數(shù)的Sp_dboption，可以得到所有數(shù)據(jù)庫選項列表。如圖2-15所示。 圖2-15 使用Sp_dboption查看所有數(shù)據(jù)庫選項列表如果希望看到某個數(shù)據(jù)庫當(dāng)前的選項設(shè)置情況，可以給Sp_dboption加上一個數(shù)據(jù)庫名作為參數(shù)，SQL Server將顯示所有