6 SQL語言與資料庫建置 6-1 SQL語言的基礎 6-2 SQL Server的資料庫結構 6-3 建立使用者資料庫

Presentation on theme: "6 SQL語言與資料庫建置 6-1 SQL語言的基礎 6-2 SQL Server的資料庫結構 6-3 建立使用者資料庫"— Presentation transcript:

1

2 6 SQL語言與資料庫建置 6-1 SQL語言的基礎 6-2 SQL Server的資料庫結構 6-3 建立使用者資料庫
6-4 修改使用者資料庫 6-5 刪除使用者資料庫 6-6 資料庫的卸離與附加

3 6-1 SQL語言的基礎 6-1-1 SQL結構化查詢語言 6-1-2 SQL語言的基本語法 6-1-3 SQL語言的指令種類
6-1-4 在Management Studio執行SQL指令碼檔案

4 6-1-1 SQL結構化查詢語言-說明 「SQL」（Structured Query Language）的全名是結構化查詢語言，本書簡稱SQL語言。SQL語言在1980年成為「ISO」（International Organization for Standardization）和「ANSI」（American National Standards Institute）的標準資料庫語言，其版本分為1989年的ANSI-SQL 89和1992年制定的ANSI-SQL 92，也稱為SQL 2，這是目前關聯式資料庫的標準語言，ANSI-SQL 99稱為SQL 3，適用在物件關聯式資料庫的SQL語言。SQL Server的T-SQL也支援最新的ANSI-SQL 2008的特點。

5 6-1-2 SQL語言的基本語法-說明 SQL語言的基礎是關聯式代數和計算，SQL語法可以視為是一種關聯式計算的版本，屬於一種非程序式（Non-procedural）查詢語言，這是一種宣告語言，並不用一步一步描述執行的過程，如下所示： SELECT * FROM 員工 WHERE 薪水 >= 30000 上述SQL指令敘述查詢員工資料表中，薪水超過3萬元的員工資料，SQL指令敘述是直接告訴資料庫管理系統需要什麼樣的查詢結果，而不用詳細說明取得查詢結果的步驟。

6 6-1-2 SQL語言的基本語法- Transact-SQL的語法元素1
識別名稱（Identifiers）：SQL Server執行個體的資料庫物件名稱，所有資料庫物件都擁有對應的識別名稱，例如：資料庫、資料表、檢視、預存程序和觸發程序等。 資料類型（Data Types）：指定欄位、T-SQL變數或參數可以儲存的資料內容，詳細的資料類型說明請參閱＜第7-1節：資料類型＞。 函數（Functions）：SQL Server內建或自訂函數，例如：GETDATE()內建函數可以傳回目前的系統日期時間，我們可以使用函數來指定欄位的預設值。

7 6-1-2 SQL語言的基本語法- Transact-SQL的語法元素2
運算式（Expressions）：在SQL指令敘述的子句可以使用運算式來取得單一值，例如：欄位名稱和變數等運算式就是取得欄位和變數的單一值。 關鍵字（Keywords）：在SQL Server擁有特殊意義的保留字（Reserved Words），例如：SELECT、FROM和WHERE等都是關鍵字。

8 6-1-2 SQL語言的基本語法-命名原則 名稱長度不可超過128個字元。
名稱是Unicode所定義的字元，包含a到z和從A到Z的字元、數字0~9、底線或其他語系的字元，例如：中文字元。 名稱不可以是T-SQL關鍵字，因為T-SQL不區分大小寫，所以不可以包含任何大小寫的關鍵字。 名稱不允許內嵌空格、關鍵字或特殊字元開頭，否則需要使用雙引號「""」或方括號「[ ]」將名稱括起，例如：[My School]、[1CD]和[where]等。

9 6-1-2 SQL語言的基本語法- 資料庫物件的完整名稱
T-SQL資料庫物件的完整名稱是由四個部分所組成，其語法如下所示： 伺服器名稱.資料庫名稱.結構描述名稱.物件名稱 上述名稱是使用「.」句號運算子來連接，例如：【員工】資料表的完整名稱為【JOE-PC.我的學校.dbo.員工】。 在SQL Server參考指定物件時，我們並不需要使用完整名稱，物件名稱只需足以讓SQL Server資料庫引擎識別即可。

10 6-1-3 SQL語言的指令種類 資料定義語言DDL（Data Definition Language）：DDL指令是用來建立、修改、刪除資料庫物件的資料表、檢視表、索引、預存程序、觸發和函數等。 資料操作語言DML（Data Manipulation Language）：DML指令是針對資料表的指令，可以插入、刪除、更新和查詢記錄資料。 資料控制語言DCL（Data Control Language）：資料庫安全管理的權限設定指令，主要有GRANT、DENY和REVOKE指令。

11 6-2 SQL Server的資料庫結構 6-2-1 資料庫檔案與檔案群組 6-2-2 分頁 6-2-3 範圍

12 6-2 SQL Server的資料庫結構-說明 SQL Server實體資料庫結構是在探討資料庫檔案的檔案結構（File Organizations）。 簡單的說，檔案結構是安排記錄如何儲存在檔案中，不同檔案結構不只佔用不同大小的空間，因為結構的不同，將擁有不同的存取方式。

13 6-2 SQL Server的資料庫結構-種類 邏輯資料庫結構：使用者觀點的資料庫結構，SQL Server的邏輯資料庫結構是由資料表、檢視表、索引和限制條件等物件所組成。 實體資料庫結構：實際儲存觀點的資料庫結構，也就是如何將資料儲存在磁碟的結構，以作業系統來說，資料庫是以檔案為單位來儲存在磁碟，檔案內容是由分頁（Pages）和範圍（Extents）所組成，為了方便管理，我們可以將檔案分類成檔案群組（Filegroups）。

14 6-2-1 資料庫檔案與檔案群組-說明 SQL Server資料庫是由多個作業系統檔案所組成的集合，資料庫儲存的資料（Data）和交易記錄（Log）分別位在不同檔案。在資料部分基於存取效率、備份和還原的管理上考量，我們可以進一步將大型資料檔（Data Files）分割成多個小型資料檔。 檔案群組（Filegroups）是用來組織資料庫的多個資料檔，以方便資料庫管理師來管理多個資料檔。

15 6-2-1 資料庫檔案與檔案群組-圖例

16 6-2-1 資料庫檔案與檔案群組- 資料庫檔案 主資料檔（Primary Data Files）：資料庫儲存的資料一開始就是存入主資料檔，在主資料檔除了能夠儲存資料外，它還包含資料庫的啟動資訊，每一個資料庫都有一個且只有一個主資料檔，其建議的副檔名為.mdf。 次資料檔（Secondary Data Files）：不是主資料檔的其他資料檔稱為次資料檔，一個資料庫可能沒有任何次資料檔，也可能擁有多個次資料檔，其建議的副檔名為.ndf。 交易記錄檔（Log Files）：儲存交易記錄的檔案，每一個資料庫至少擁有一個交易記錄檔，也可能擁有多個交易記錄檔，建議副檔名是.ldf。

17 6-2-1 資料庫檔案與檔案群組- 檔案群組(說明)
SQL Server資料庫如果只有一個資料檔時，我們並不需要考量檔案群組的問題。但是，對於大型資料庫，或基於管理或配置磁碟空間的考量（例如：將部分資料置於不同磁碟），而將資料庫建立成多個資料檔時，我們就可以將它們分成不同檔案群組（Filegroups），來方便資料庫檔案的管理。 當使用檔案群組來群組多個資料檔時，將資料存入資料檔時，就是以檔案群組為單位，而不是個別資料檔。

18 6-2-1 資料庫檔案與檔案群組- 檔案群組(種類)
主檔案群組（Primary Filegroups）：這是內含主資料檔的檔案群組，它是在建立資料庫時，SQL Server預設建立的檔案群組，如果資料庫建立其他次資料檔時，沒有指定所屬檔案群組的資料檔，就是屬於主檔案群組。 使用者定義檔案群組（User-defined Filegroups）：使用者自行建立的檔案群組，這是使用FILEGROUP關鍵字，在T-SQL指令CREATE DATABASE或ALTER DATABASE指令所建立的檔案群組。 預設檔案群組（Default Filegroups）：這是資料庫預設使用的檔案群組，可以是主檔案群組或使用者定義檔案群組，如果沒有指定，預設就是主檔案群組。

19 6-2-2 分頁-說明 SQL Server資料檔的內容在邏輯上是分成連續的分頁（Pages），它是SQL Server最基本的儲存單位，當資料庫配置資料檔的磁碟空間（即副檔名.mdf或ndf）時，就是配置0至n頁的連續分頁。 分頁（Pages）是SQL Server儲存資料的基本單位，其大小是8KB，128頁分頁等於1MB空間。當在資料檔（即副檔名.mdf或ndf）新增記錄時，如果是在空資料檔新增第1筆記錄時，不論記錄大小，SQL Server一定配置一頁分頁給資料表來儲存這筆記錄，其他記錄則會依序存入分頁配置的可用空間中。

20 6-2-2 分頁-圖例 分頁開始是96位元組的標頭資訊，用來儲存系統所需的相關資訊，之後依序是存入的記錄資料，在分頁的最後擁有資料列位移（Row Offsets）的指標，可以指向分頁中各記錄的開始位址。

21 6-2-3 範圍-說明 範圍（Extends）是由八個連續分頁所組成，其目的是讓SQL Server可以更有效率的來管理資料檔的眾多分頁，如下圖所示：

22 6-2-3 範圍-種類 制式範圍（Uniform Extends）：在制式範圍中的分頁都是儲存同一個物件的資料，即完全由一個物件所使用，例如：都是配置給資料表或都配置給索引。當我們建立存在資料表的索引時，如果一建立就需要配置超過8頁分頁的索引資料，此時就是使用制式範圍來儲存索引資料。 混合範圍（Mixed Extends）：混合範圍中的分頁是儲存不同物件的資料，例如：部分分頁屬於資料表，部分屬於索引。一般來說，新建立的資料表或索引都是儲存在混合範圍，等到資料表或索引成長至超過8頁分頁時，就會轉成使用制式範圍來儲存。

23 6-3 建立使用者資料庫 6-3-1 在Management Studio建立資料庫 6-3-2 使用T-SQL指令建立資料庫
6-3-3 建立多檔案群組的資料庫

24 6-3-1 在Management Studio建立資料庫

25 6-3-2 使用T-SQL指令建立資料庫-語法 T-SQL語言是使用CREATE DATABASE指令來建立資料庫，其基本語法如下所示：
[ ON [PRIMART] 資料檔規格清單] [ LOG ON 交易記錄檔規格清單] [COLLATE 定序名稱] [FOR ATTACH]

26 6-3-2 使用T-SQL指令建立資料庫-範例1 SQL指令碼檔：Ch6_3_2_01.sql
使用SQL Server預設值建立名為【圖書】的資料庫，如下所示： CREATE DATABASE 圖書

27 6-3-2 使用T-SQL指令建立資料庫-範例2 SQL指令碼檔：Ch6_3_2_02.sql
請指定資料檔和交易記錄檔的規格清單來建立名為【學校】的資料庫，檔案是位在「C:\Data」路徑，如下所示： CREATE DATABASE 學校 ON PRIMARY ( NAME='學校', FILENAME= 'C:\Data\學校.mdf', SIZE=8MB, MAXSIZE=10MB, FILEGROWTH=1MB ) LOG ON ( NAME='學校_log', FILENAME = 'C:\Data\學校_log.ldf', SIZE=1MB, FILEGROWTH=10% )

28 6-3-3 建立多檔案群組的資料庫-說明 SQL Server在建立資料庫的同時就可以新增檔案群組。在這一節我們準備建立名為【產品】的資料庫，內含2個檔案群組、3個資料檔和1個交易記錄檔，如右圖所示：

29 6-3-3 建立多檔案群組的資料庫- Management Studio

30 6-3-3 建立多檔案群組的資料庫- T-SQL(1)
SQL指令碼檔：Ch6_3_3.sql 建立多檔案群組的資料庫【代理產品】，其架構和上一節的【產品】相同，內含2個檔案群組、3個資料檔和1個交易記錄檔，如下所示： CREATE DATABASE 代理產品 ON PRIMARY ( NAME='代理產品', FILENAME= 'C:\Data\代理產品.mdf', SIZE=8MB, MAXSIZE=10MB, FILEGROWTH=1MB ),

31 6-3-3 建立多檔案群組的資料庫- T-SQL(2)
FILEGROUP 代理產品_群組 ( NAME = '代理產品_群組_11', FILENAME = 'C:\Data\代理產品_群組_11.ndf', SIZE = 2MB, MAXSIZE=10MB, FILEGROWTH=1MB ), ( NAME = '代理產品_群組_12', FILENAME = 'C:\Data\代理產品_群組_12.ndf', FILEGROWTH=1MB ) LOG ON ( NAME='代理產品_log', FILENAME = 'C:\Data\代理產品_log.ldf', SIZE=1MB, FILEGROWTH=10% )

32 6-4 修改使用者資料庫 6-4-1 使用Management Studio修改使用者資料庫 6-4-2 資料庫選項
6-4-3 使用T-SQL指令修改使用者資料庫

33 6-4-1 使用Management Studio修改 使用者資料庫
新增或修改資料庫檔案 新增或修改檔案群組

34 6-4-2 資料庫選項 在「資料庫屬性」對話方塊選【選項】頁面，可以看到一些資料庫的進階屬性，如右圖所示：

35 6-4-3 使用T-SQL指令修改使用者資料庫-語法1
T-SQL語言可以使用ALTER DATABASE指令來修改使用者資料庫，其基本語法如下所示： ALTER DATABASE 資料庫名稱 MODIFY NAME = 新的資料庫名稱 | COLLATE 定序名稱 | ADD FILE 資料檔規格清單 [ TO FILEGROUP 檔案群組名稱 ] | ADD LOG FILE 交易記錄檔規格清單 | REMOVE FILE 邏輯檔案名稱

36 6-4-3 使用T-SQL指令修改使用者資料庫-語法2
| MODIFY FILE 資料檔規格清單 | ADD FILEGROUP 檔案群組名稱 | REMOVE FILEGROUP 檔案群組名稱 | MODIFY FILEGROUP 檔案群組名稱 READONLY | READWRITE | DEFAULT | NAME = 新檔案群組名稱 | SET 選項屬性清單 [ WITH ROLLBACK AFTER 等待秒數 [ SECONDS ] | ROLLBACK IMMEDIATE | NO_WAIT ]

37 6-4-3 使用T-SQL指令修改使用者資料庫-範例1
SQL指令碼檔：Ch6_4_3_01.sql 在【產品】資料庫的【產品_群組】檔案群組，新增名為【產品_群組_13】的資料檔，如下所示： ALTER DATABASE 產品 ADD FILE ( NAME = '產品_群組_13', FILENAME = 'C:\Data\產品_群組_13.ndf', SIZE = 2MB, MAXSIZE=10MB, FILEGROWTH=1MB ) TO FILEGROUP 產品_群組

38 6-4-3 使用T-SQL指令修改使用者資料庫-範例2
SQL指令碼檔：Ch6_4_3_02.sql 請在【產品】資料庫新增名為【產品_log2】的交易記錄檔，如下所示： ALTER DATABASE 產品 ADD LOG FILE ( NAME = '產品_log2', FILENAME = 'C:\Data\產品_log2.ldf', SIZE = 5MB, MAXSIZE=10MB, FILEGROWTH=1MB )

39 6-4-3 使用T-SQL指令修改使用者資料庫-範例3
SQL指令碼檔：Ch6_4_3_03.sql 請調整【代理產品】資料庫交易記錄檔案的尺寸成為5MB，如下所示： ALTER DATABASE 代理產品 MODIFY FILE ( NAME = '代理產品_log', SIZE = 5MB )

40 6-4-3 使用T-SQL指令修改使用者資料庫-範例4
SQL指令碼檔：Ch6_4_3_04.sql 更改【代理產品】資料庫預設檔案群組為【代理產品_群組】，如下所示： ALTER DATABASE 代理產品 MODIFY FILEGROUP 代理產品_群組 DEFAULT

41 6-5 刪除使用者資料庫- Management Studio

42 6-5 刪除使用者資料庫- T-SQL指令 T-SQL刪除資料庫是使用DROP DATABASE指令，其語法如下所示：
SQL指令碼檔：Ch6_5.sql 在SQL Server同時【教務系統】和【學校】資料庫，如下所示： DROP DATABASE 教務系統, 學校

43 6-6 資料庫的卸離與附加 6-6-1 卸離資料庫 6-6-2 附加資料庫 6-6-3 使用T-SQL指令卸離與附加資料庫

44 6-6-1 卸離資料庫 卸離資料庫並不是刪除資料庫，卸離只是將資料庫定義資料從master資料庫刪除，如此使用者就可以複製資料庫的.MDF（Master Data File）資料檔和.LDF（Log Data File）交易記錄兩個檔案。

45 6-6-2 附加資料庫 在複製好資料庫檔案後，我們可以在另一台電腦的SQL Server或其他磁碟，使用附加（Attach）方式來回存資料庫。

46 6-6-3 使用T-SQL指令卸離與附加資料庫-卸離資料庫
在SQL Server可以使用sp_detach_db系統預存程序來卸離資料庫，其基本語法如下所示： EXEC sp_detach_db '資料庫名稱' 上述系統預存程序可以卸離參數的資料庫。 SQL指令碼檔：Ch6_6_3_01.sql 使用系統預存程序來卸離【圖書】資料庫，如下所示： EXEC sp_detach_db '圖書'

47 6-6-3 使用T-SQL指令卸離與附加資料庫-T-SQL附加資料庫
T-SQL指令CREATE DATABASE可以使用FOR ATTACH子句來附加資料庫。 SQL指令碼檔：Ch6_6_3_02.sql 在SQL Server使用T-SQL指令附加【圖書】資料庫，位置是在「C:\Data」資料夾，如下所示： CREATE DATABASE 圖書 ON PRIMARY ( FILENAME = 'C:\Data\圖書.mdf' ) FOR ATTACH

48 6-6-3 使用T-SQL指令卸離與附加資料庫-系統預存程序附加資料庫
除了T-SQL指令外，我們也可以使用sp_attach_db系統預存程序來附加資料庫，其基本語法如下所示： EXEC sp_attach_db '資料庫名稱', '資料檔路徑' SQL指令碼檔：Ch6_6_3_03.sql 在SQL Server使用系統預存程序附加位在「C:\Data」資料夾的【圖書】資料庫，如下所示： EXEC sp_attach_db '圖書','C:\Data\圖書.mdf'