Transcript 第六章建立資料庫

第6章
建立資料庫
著作權所有 © 旗標出版股份有限公司
1
本章提要






6-1 使用 SQL Server Management Studio 管
理工具建立資料庫
6-2 用 CREATE DATABASE 敘述建立資料庫
6-3 卸離與附加資料庫
6-4 使用 SQL Server Management Studio 管
理工具修改資料庫設定
6-5 用 ALTER DATABASE 敘述修改資料庫
6-6 刪除資料庫
2
6-1 使用 SQL Server Management
Studio 管理工具建立資料庫


SQL Server 中的資料庫都必須使用自己專屬
的檔案來儲存資料, 而且至少需要兩個檔案：
一個用來儲存資料, 通稱為資料檔案, 另一個用
來儲存資料庫的異動記錄, 通稱為記錄檔。
所謂 "建立資料庫", 主要就是為資料庫指定上
述那兩種檔案, 當然, 還有一些屬性要設定, 例
如為資料庫命名、設定檔案大小 ... 等等。詳
細情形, 待後面實際操作時就會明瞭了。
3
使用 SQL Server Management
Studio 管理工具建立資料庫


這一節我們先介紹在 SQL Server
Management Studio 管理工具中建立資料庫的
方法。
請啟動 SQL Server Management Studio, 連
上您要操作的 SQL Server, 如下建立資料庫：
4
使用 SQL Server Management
Studio 管理工具建立資料庫
5
使用 SQL Server Management
Studio 管理工具建立資料庫
6
使用 SQL Server Management
Studio 管理工具建立資料庫


資料檔案與記錄檔是 SQL Server 存放資料與
記錄用的檔案, 所以想當然耳, 資料量或記錄量
越多, 這兩個檔案所需的大小就會越大。
當您建立資料庫時, 必須設定這兩個檔案的初
始大小, 就像建立磁碟機 (C:、D:) 時, 必須指
定要分配多大的容量給磁碟機一樣。
7
使用 SQL Server Management
Studio 管理工具建立資料庫


您可能會擔心, 如果一開始設定的初始大小不
夠大怎麼辦？SQL Server 提供了自動成長的
功能, 當您的資料量太多, 導致檔案放不下的話,
SQL Server 會自動幫您擴大檔案的大小, 這樣
就不必擔心檔案不夠大的問題了。
不過, 讓資料庫的檔案大小自動成長雖然方便,
但成長後容易造成資料在磁碟中不連續存放,
亦會降低資料庫的效率, 所以還是建議先估算
資料庫所需容量, 然後在初始大小欄位中直接
設定適當大小。
8
使用 SQL Server Management
Studio 管理工具建立資料庫

至於自動成長的功能也有很多選項, 可以讓您
依照環境自行調整成長的方式。請在上述步驟
中, 按自動成長欄位內的 ... 鈕：
9
使用 SQL Server Management
Studio 管理工具建立資料庫

完成自動成長的設定後, 請按確定鈕回到新增
資料庫交談窗, 如下操作：
10
使用 SQL Server Management
Studio 管理工具建立資料庫
11
定序

當新增資料庫時, 若如下切換到選項頁面, 可以
看到一個名為定序的設定：
12
定序


定序是設定資料庫所要使用的字元集 (code
page) 以及字元資料排序的方式。
一開始安裝 SQL Server 時, 安裝程式便會依
據作業系統的區域設定, 選擇適當的定序, 例如
在台灣地區, SQL Server 預設的定序為
"Chinese_Taiwan_Stroke_CI_AS", 其中各項
目的說明如下：
13
定序




Chinese_Taiwan：表示台灣繁體中文。
Stroke：表示按照筆畫排序；若是 Bobomofo 則表
示按注音排序。
CI：表示 Case Insensitive, 與大小寫無關；CS 則
表示要區分大小寫。
AS：表示 Accent Sensitive, 要區分含重音節符號
的字元；AI 則表示不區分。
14
定序


從 SQL Server 2000 開始, 不僅伺服器本身,
就連資料庫、資料表的欄位 (限字串及
Unicode 字串類的資料型別) 都可以個別設定
定序。
不過, 除非有特殊需求, 例如想改用注音來排序、
要區分大小寫、欄位中有他國語言的資料...等
等, 否則建議保留預設的 <伺服器預設值> 設
定, 沿用SQL Server 的預設值就可以了。
15
6-2 用 CREATE DATABASE
敘述建立資料庫

用來建立資料庫的 SQL 敘述為 CREATE
DATABSE , 這個敘述用起來可說是收放自如,
我們馬上來小試身手一番。請如下操作：
16
用 CREATE DATABASE 敘
述建立資料庫
17
CREATE DATABASE 敘述完
整版


前面的示範是 CREATE DATABASE 敘述最簡
單的用法 ― 所有的設定皆使用預設值。
接著我們來看完整的 CREATE DATABASE 敘
述：
18
CREATE DATABASE 敘述完
整版
19
SQL 語法中的符號意義

在 SQL 語法中有的用中括號 [ ], 也有的用 < >
前後包夾, 它們分別代表了不同的意思, 底下我
們來說明各種符號代表的意義：


在 SQL 語法中, SQL 關鍵字我們一律用大寫字母
表示, 如 CREATE DATABASE；小寫字母的項目
則是要使用者代換為實際的值, 例如
database_name。
凡是可以省略的項目或參數, 就用 [ ] 括住, 例如
[PRIMARY]。
20
SQL 語法中的符號意義




如果有多個選項可以擇一使用, 則用 | 隔開, 例如
[arg1 | arg2], 表示可用 arg1 或 arg2, 但兩者不可
同時使用。
用 { } 括起來的則表示不可省略, 例如 {max_size |
UNLIMITED}。通常會與 | 符號搭配, 表示必須擇一
使用。
當某一項的內容太多, 需要另外說明的就以 < > 來
表示, 例如 < filespec > 與 < filegroup >, 而 ::= 後
的敘述即為該項目的語法。
項目後加上 [ ,...n ], 表示此項目可設定多個, 然後用
逗點做分隔, 例如 < filespec > [,...n] 表示
<filespec_1>, <filespec_2>, ...。
21
設定資料庫名稱

第一行的 CREATE DATABSE
database_name 在設定資料庫的名稱, 我們只
要將 database_name 換成實際的資料庫名稱
即可。
22
設定資料檔案 ― 資料庫的檔
案與檔案群組
ON 參數在設定資料庫所需的資料檔案, 在說
明其中的項目之前, 我們還是先對資料庫要使
用的檔案及檔案群組做個充分的了解。
檔案
 之前提過, 在建立 SQL Server 的資料庫時, 需
為資料庫配置兩種檔案：資料檔案和記錄檔,
其中資料檔案又分為主資料檔和次資料檔。我
們現在就來弄清楚這 3 種檔案：

23
設定資料檔案 ― 資料庫的檔
案與檔案群組
24
設定資料檔案 ― 資料庫的檔
案與檔案群組

一個資料庫至少要包括一個主資料檔儲存資料,
以及一個記錄檔記錄異動資訊, 至於次資料檔
則不一定需要。
25
設定資料檔案 ― 資料庫的檔
案與檔案群組

另外, 上述那 3 種檔案的檔案名稱還有兩種表
達形式：


邏輯檔案名稱 (logical_file_name)：SQL 敘述中要
參照某個檔案時所使用的名稱, 必須符合 SQL 識別
名稱的命名規則, 而且在資料庫中不可有重複的邏
輯檔案名稱出現。
實際檔案名稱 (os_file_name)：檔案在作業系統
(如 Windows 98、NT、2000) 中的名稱, 必須符合
存放該檔案之作業系統的檔案命名規則。
26
設定資料檔案 ― 資料庫的檔
案與檔案群組
檔案規格設定參數
 接著我們來看 CREATE DATABASE 敘述中用來設定
檔案規格的參數：



PRIMARY：指明其後所定義的檔案是主資料檔。若省略此
參數, 則第一個定義的檔案即為主資料檔。
NAME = logical_file_name：設定資料檔的邏輯檔案名稱。
FILENAME = 'os_file_name'：設定資料檔在作業系統下的
存放路徑及實際檔案名稱, 存放路徑只要是在安裝 SQL
Server 的那部電腦上即可。例如：FILENAME = 'C:\DATA\
訂單資料檔_1.MDF'。
27
設定資料檔案 ― 資料庫的檔
案與檔案群組


SIZE = size：將 size 換成資料檔的起始大小, 可加
上單位, 包括 KB、MB (預設單位)、GB、TB。次
資料檔與紀錄檔的起始大小預設值為 1 MB。
MAXSIZE = max_size | UNLIMITED：若要設定檔
案的最大容量上限, 請將 max_size 換成實際的上
限值, 可加上單位, 包括 KB、MB (預設單位)、GB、
TB, 例如：MAXSIZE = 10 MB。若不想設定上限,
則可省略 MAXSIZE 參數, 或設定為 MAXSIZE =
UNLIMITED 。
28
設定資料檔案 ― 資料庫的檔
案與檔案群組

FILEGROWTH = grow_increment：當檔案容量不
足且尚未超過最大容量上限時, 檔案會自動成長, 此
參數即用來設定每次成長的數量。成長數量可用數
值或百分比來指定, 使用數值時, 可加上 KB、MB、
GB、TB 單位 (預設單位為 MB) 。若不想讓檔案自
動成長, 請設為 0。如果省略 FILEGROWTH 參數,
則資料檔預設會以 1 MB 成長, 而記錄檔預設以
10% 來成長。若此值小於 64 KB, 則會以 64 KB 來
成長。
29
設定資料檔案 ― 資料庫的檔
案與檔案群組
檔案群組 (filegroup)
 當我們為資料庫設立多個資料檔案時, 可利用
檔案群組來加強資料的配置及存取效率。
 例如一台主機中有 C、D、E 三台硬碟, 我們
可以建立一個檔案群組, 其中包含了 3 個資料
檔案, 這 3 個資料檔案分別存放在 3 台硬碟中,
由於 SQL Server 會自動將資料分散儲存在檔
案群組的各個檔案中, 因此可以有效地提升存
取效率 (因為 3 台硬碟可以同時運作)。
30
設定資料檔案 ― 資料庫的檔
案與檔案群組

SQL Server 的檔案群組可分為兩種：


主檔案群組：在建立資料庫時, SQL Server 會自動
產生主檔案群組, 名稱就叫 PRIMARY, 而主資料檔
則固定屬於主檔案群組, 不可更改。在為資料庫加
入其它的次資料檔時, 若未特別指定所屬的檔案群
組, 預設也會放入 PRIMARY 檔案群組中。
使用者自訂檔案群組：使用者自行建立的檔案群組,
稍後即會說明建立的方法。
31
設定資料檔案 ― 資料庫的檔
案與檔案群組


另外, 資料庫目前預設會使用的檔案群組稱為
預設檔案群組。
在建立新的資料庫物件 (如資料表、檢視表 ... )
時, 若未指定要放在哪一個檔案群組中, SQL
Server 就會做主將它們放到預設檔案群組裏。
SQL Server 預設會以 PRIMARY 檔案群組作
為預設檔案群組, 但我們可自行變更此設定。
32
設定資料檔案 ― 資料庫的檔
案與檔案群組
檔案群組的設定參數
 在 CREATE DATABASE 敘述中, 可以利用
FILEGROUP 參數來建立使用者自訂檔案群組,
並指定要放入此檔案群組的次資料檔。例如：
33
設定資料檔案 ― 資料庫的檔
案與檔案群組
應用範例
 底下我們舉幾個範例, 讓各位能夠確實了解檔
案及檔案群組設定參數的用法。

此例會建立產品資料庫, 並自己指定主資料檔的檔
案名稱及存放路徑 (其它參數皆使用預設值)：
34
設定資料檔案 ― 資料庫的檔
案與檔案群組

此例會建立機密產品資料庫, 並自己指定主資料檔
的檔案規格, 包括檔案名稱、起始大小、最大容量
上限、以及每次檔案成長的數量：
35
設定資料檔案 ― 資料庫的檔
案與檔案群組

此例會建立銷售資料庫, 它總共包含 6 個資料檔案
和 3 個檔案群組：
36
設定資料檔案 ― 資料庫的檔
案與檔案群組
37
使用 SQL Server Management
Studio 管理工具建立檔案群組

若是使用 SQL Server Management Studio 管
理工具建立資料庫, 可用下面的方法新增檔案
群組：
38
使用 SQL Server Management
Studio 管理工具建立檔案群組
39
使用 SQL Server Management
Studio 管理工具建立檔案群組
40
設定交易記錄檔

如果希望自己指定交易記錄檔的檔案規格, 請
在 LOG ON 參數中設定, 設定的語法和資料檔
案是一樣的, 例如：
41
設定定序

利用 COLLATE 參數可為資料庫個別指定定序
(若省略此參數, 則資料庫會沿用 SQL Server
的定序設定), 例如：
42
設定定序



利用 COLLATE 參數設定定序比較辛苦, 因為
我們必須先去查閱定序的全名, 才能輸入。
SQL Server 2005 的定序分成兩大類：
Windows 定序和 SQL 定序, 後者是為了與舊
版的 SQL Server 相容而設的, 若沒有必要儘
量不用。
您可以執行如下的敘述, 列出所有的定序, 從中
查出您要使用的定序, 以便在 COLLATE 參數
中設定：
43
6-3 卸離與附加資料庫


為了避免 SQL Server 同時管理太多的資料庫,
耗用不必要的系統資源, 我們可以將暫時用不
著的資料庫從 SQL Server 中卸離, 即取消
SQL Server 與資料庫各個實體檔案的關係, 這
樣一來, SQL Server 可以稍微輕鬆一點兒。
如果後來要再使用到已卸離的資料庫, 沒關係,
只要再將該資料庫附加到 SQL Server 中就可
以了。
44
卸離資料庫

在 SQL Server Management Studio 管理工具
中若要卸離某資料庫, 方法很簡單, 之前我們建
立了不少資料庫, 就拿其中一個來示範吧：
45
卸離資料庫

現在您應該看不到剛才卸離的資料庫了。
46
卸離資料庫

另外, 我們還可以按下 SQL Server
Management Studio 視窗上的新增查詢
鈕
, 執行 sp_detach_db 預存程
序來卸離資料庫, 例如：
47
無法卸離資料庫

若資料庫還有使用者在連線使用中的話, 該資
料庫是無法卸離的：
48
無法卸離資料庫

此時如果您按下確定鈕, 會得到下面錯誤訊息：
49
無法卸離資料庫

您可以選擇過些時間, 等沒有人使用這個資料庫
後, 再進行卸離的動作。如果您沒有辦法等, 需要
立即執行卸離, 則可如下勾選卸除連接選項, 即可
中斷此資料庫目前所有連線：
50
附加資料庫

如果後來要將已經卸離的資料庫重新附加到
SQL Server, 或者想要將書附光碟的資料庫檔
案載入 SQL Server, 都可以如下操作 (假設我
們之前已將訂單資料庫卸離, 現在要將它附加
回來)：
51
附加資料庫
52
附加資料庫
53
附加資料庫
54
附加資料庫


因主資料檔內存放了其它檔案的相關資訊, 所
以指明主資料檔後, 其它檔案的所在位置也就
知道了。
不過, 若在卸載資料庫後, 這些檔案曾經搬移或
重新命名過, 那麼就需要自己去修改目前的檔
案路徑, 否則可能會發生找不到檔案的錯誤。
55
附加資料庫

利用 CREATE DATEBASE 敘述中的 FOR
ATTACH 參數也可以將資料庫重新附加到
SQL Server 中：
56
附加資料庫

另外, 我們也可以用 sp_attach_db 預存程序來
附加資料庫, 其語法如下：
57
附加資料庫


同樣的, 只需指明主資料檔的路徑以及其他有
更動過位置的檔案路徑即可。
但 sp_attach_db 中最多只能列出 16 個檔案,
若您要列出的檔案超過 16 個, 請改用
CREATE DATABASE 的 FOR ATTACH 參數。
58
6-4 使用 SQL Server Management
Studio 管理工具修改資料庫設定

資料庫建好之後, 若覺得當初的設定不妥或設
立的資料檔案不夠用 ... 等, 我們都可以事後來
更改。利用 SQL Server Management Studio
管理工具修改資料庫的檔案及屬性設定。






開啟資料庫的屬性交談窗
一般頁面
檔案頁面
檔案群組頁面
選項頁面
權限頁面
59
開啟資料庫的屬性交談窗

若要使用 SQL Server Management Studio 管
理工具修改資料庫的屬性設定或檔案, 只要如
下打開資料庫的屬性交談窗即可：
60
開啟資料庫的屬性交談窗
61
一般頁面

一般頁面是純粹做觀賞用的, 在此頁次您可以
檢視資料庫的名稱、一般資訊, 以及使用的定
序 ... 等等：
62
一般頁面
63
檔案頁面

在檔案頁面可修改並新增資料庫的資料檔案與
記錄檔：
64
檔案群組頁面

在檔案群組頁面可指定預設檔案群組、以及調
整現有檔案群組的屬性：
65
檔
案
群
組
頁
面
66
選項頁面

選項頁面是一些比較進階的資料庫屬性設定,
這裏僅做簡介, 若看不懂可先略過：
67
選項頁面
68
選項頁面
定序
 定序項目可以設定資料庫的字元集 (code
page) 以及字元資料排序方式。
復原模式
 此項目指定資料庫的復原模式, 復原模式可決
定如何備份資料, 以及損失資料的風險。有下
列 3 種復原模式可選擇：
69
選項頁面



完整：此模式會備份所有執行過的交易命令, 所以
只要適度搭配資料庫完整備份與交易記錄備份, 就
可以讓資料庫復原到任何時間點。
簡單：簡單模式下一旦確認交易正確寫入後, 便會
清除交易的記錄。所以使用此模式的資料庫只能做
完整備份與差異式備份, 無法做交易記錄備份。
大量記錄：與完整模式類似, 但是大量匯入的交易
不會被記錄下來。所以這個模式下如果交易記錄備
份中有大量匯入的動作時, 還原時便無法還原到任
意時間點。
70
選項頁面
相容性層級
 相容性可以設定資料庫與 SQL Server 各版本
的相容性。如果您的資料庫只會在 SQL
Server 2005 中使用, 選擇 SQL Server 2005
(90) 即可；若資料庫需要在其他版本中使用,
則依照版本選擇其他層級。
其他選項
 其他重要選項的說明如下：
71
選項頁面
其他選項
 其他重要選項的說明如下：
72
權限頁面

此頁面可以設定使用者對資料庫的權限：
73
權
限
頁
面
74
6-5 用 ALTER DATABASE
敘述修改資料庫

T-SQL 用來修改資料庫屬性及檔案設定的敘述
為 ALTER DATABASE 敘述, 其完整語法如
下：
75
用 ALTER DATABASE 敘述
修改資料庫
76
用 ALTER DATABASE 敘述
修改資料庫


ALTER DATABASE 敘述看起來洋洋灑灑一大
串, 但實際用起來不會這麼嚇人, 因為 ALTER
DATABASE 敘述一次只能修改一件事。
底下我們來看它的參數。
77
參數說明



ALTER DATABASE database：指定欲修改的
資料庫, 請將 database 換成要修改的資料庫
名稱。
ADD FILE < filespec > [ ,...n ] [ TO
FILEGROUP filegroup_name ]
ADD FILE 參數可以為資料庫新增資料檔案,
檔案規格 < filespec > 的語法如下 (由於內容
和在 CREATE DATABASE 敘述中建立資料檔
一樣, 所以此處不再重複說明)：
78
參數說明

若不要讓新增的資料檔案放在預設檔案群組中,
則可加上 TO FILEGROUP 參數另外指定檔案
群組, 將 filegroup_name 換成實際的檔案群組
名稱即可。
79
參數說明


ADD LOG FILE < filespec > [ ,...n ]：為資料
庫新增記錄檔, 請將 < filespec > 換成實際的
檔案規格。
REMOVE FILE logical_file_name：刪除資料
庫的資料檔案或記錄檔, 請將
logical_file_name 換成欲刪除的邏輯檔案名稱。
刪除檔案有個先決條件, 即檔案內不能包含任
何資料, 否則無法刪除。
80
參數說明


ADD FILEGROUP fi legroup_name：為資料
庫新增自訂的檔案群組, 請將filegroup_name
換成實際的檔案群組名稱。
REMOVE FILEGROUP filegroup_name：刪
除資料庫現有的自訂檔案群組, 請將
filegroup_name 換成欲刪除的檔案群組名稱。
刪除檔案群組的先決條件是, 該檔案群組內不
能包含任何檔案, 否則無法刪除。
81
參數說明

MODIFY FILE < filespec >：修改資料庫檔案
(資料檔案或記錄檔) 的屬性設定, 例如 NAME、
SIZE、MAXSIZE ... 等, 但一次只能更改一項。
其 < filespec > 的內容如下：
82
列示資料庫現有的檔案及屬性：
sp_helpfile

在修改資料庫的檔案屬性之前, 您可能想先查
看資料庫目前有哪些檔案及屬性, 這時可利用
預存程序 sp_helpfile 來查詢, 其語法如下：

執行前, 記得先在工具列將目前使用的資料庫
切換為檔案所在的資料庫：
83
列示資料庫現有的檔案及屬性：
sp_helpfile
84
參數說明

MODIFY NAME = new_dbname：修改資料庫
的名稱。在修改某資料庫的名稱之前, 您必須
確定目前除了您之外, 沒有其它任何人在使用
該資料庫, 否則會失敗。下面為使用範例：
85
更改資料庫名稱：sp_renamedb

我們也可以用預存程序 sp_renamedb 來更改
資料庫名稱, 其語法如下：
86
參數說明


MODIFY FILEGROUP fi legroup_name
{filegroup_property | NAME
=new_filegroup_name}：修改檔案群組的屬性
或名稱, 一次只能更改一種。
檔案群組可設定的屬性如下：
87
參數說明

例如, 假設銷售資料庫有 3 個檔案群組：
PRIMARY (目前為預設檔案群組)、銷售資料
庫檔案群_1、銷售資料庫檔案群_2, 現要將銷
售資料庫檔案群_2 設為預設檔案群組：
88
列示資料庫現有的檔案群組：
sp _helpfilegroup

我們可利用預存程序 sp_helpfilegroup 來查詢
資料庫目前有哪些檔案群組, 其語法如下：

執行方式和 sp_helpfile 相同, 但請記得, 要先
在工具列將目前使用的資料庫切換為檔案所在
的資料庫。
89
參數說明

SET < optionspec > [ ,...n ] [ WITH < termination > ]：
變更資料庫的選項設定, 其功用和 6-4 節介紹的
選項頁面相同。< optionspec > 的語法如下：
90
參數說明
91
參數說明
92
參數說明

轉換資料庫的狀態時 (即變更 state_option),
可加上 WITH < termination > 參數, 其用意是
指定當轉換的動作無法完成時, 要何時復原 (即
取消轉換動作)。<termination > 的語法如下：

COLLATE < collation_name >：變更資料庫的
定序名稱。變更時, 您必須確定目前沒有其它
人在使用該資料庫。
93
應用範例

讓我們來看個 ALTER DATABASE 敘述的應用
範例。假設我們要為銷售資料庫新增 1 個記錄
檔銷售日誌_2：
94
6-6 刪除資料庫

當不再需要某個資料庫的時候, 我們可以將它
刪除。


使用 SQL Server Management Studio 管理工具刪
除資料庫
刪除資料庫 DROP DATABASE 敘述
95
使用 SQL Server Management
Studio 管理工具刪除資料庫


使用 SQL Server Management Studio 管理工
具刪除資料庫真是再簡單也不過了, 您只要在
物件總管窗格中選取欲刪除的資料庫, 然後如
下操作：(如下頁)
還有另一種方法是先使用滑鼠選取要刪除的資
料庫, 然後再按下鍵盤上的 Delete 鍵即可。
96
使用 SQL Server Management
Studio 管理工具刪除資料庫
97
使用 SQL Server Management
Studio 管理工具刪除資料庫
98
刪除資料庫
DROP DATABASE 敘述

在 T-SQL 中用來刪除資料庫的敘述是 DROP
DATABASE 敘述, 其語法如下：

您必須確定欲刪除的資料庫不在使用中, 否則無
法刪除。另外要提醒的是, 利用 DROP
DATABASE 敘述刪除資料庫不會出現確認訊息,
請小心使用。
99
列出 SQL Server 現有的資料庫：
sp_helpdb

如果您想知道目前 SQL Server 中有哪些資料
庫, 可以執行 sp_helpdb 預存程序來查詢：
100
列出 SQL Server 現有的資料庫：
sp_helpdb
101