第6章 資料庫管理系統
6-1 關聯式資料庫管理系統 6-2 SQL Server資料庫管理系統 6-3 安裝SQL Server資料庫管理系統 6-4 SQL Server的管理工具 6-5 系統目錄 6-6 物件與資料類型 6-7 建立SQL Server資料庫 6-1-1 關聯式資料庫管理系統
6-1-2 關聯式資料庫管理系統的架構
關聯式資料庫管理系統（Relational Database Management System, 簡稱RDBMS）是一個軟體程 式，可以讓使用者定義、新增、更新和管理關聯 式資料庫（Relational Database）。 E. F. Codd定義的關聯式資料庫管理系統只需滿足 兩個條件，如下所示： • 能夠存取關聯式資料庫。 • 提供關聯式代數的選擇（Select）、投影 （Project）和合併（Join）等運算。
6-1-1 關聯式資料庫管理系統
6-1-2 關聯式資料庫管理系統的
架構-圖例
查詢剖析器（Query Parser）：處理DML語言的指令敘述， 檢查語法正確後轉換成低階指令，以關聯式資料庫來說是 轉換成關聯式代數，然後送到查詢最佳化模組處理。 查詢最佳化模組（Query Optimizer）：最佳化查詢指令的 處理程序，使用查詢最佳化技術參考系統目錄的Meta-data進行「查詢轉換」（Query Transformation），將外部 綱要查詢轉換成內部綱要的查詢，或是使用索引加速資料 查詢，產生「執行計劃」（Execution Plan）。 程式碼產生器（Code Generator）：使用RDBMS提供的演 算法，將最佳化後產生的執行計劃轉換成程式碼。
6-1-2 關聯式資料庫管理系統的架構-說明1
DDL編譯器（DDL Compiler）：將DDL語言的指令編譯成資 料綱要Meta-data存入系統目錄。 執行引擎（Execution Engine）：當查詢指令在經過最佳化 處理後，就是在執行引擎實際執行資料庫的查詢來存取實 體資料庫，如果是交易，就交給交易管理來處理。 鎖定管理（Lock Manager）：也稱為「並行控制管理」 （Concurrency-Control Manager），負責保證各交易處理 能夠分開和獨立執行，提供鎖定功能鎖定資料，只允許一 個交易的操作可以存取這些資料。
6-1-2 關聯式資料庫管理系統的架構-說明2
交易管理（Transaction Manager）：負責配合記錄管理 （Log Manager）和鎖定管理（Lock Manager）處理資料庫 的交易，交易管理負責送出SQL指令COMMIT和ROLLBACK 來完成交易，並且避免「死結」（Deadlock）情況的發生。 回復管理（Recovery Manager）：負責回復資料庫系統到 最後的穩定狀態，主要是透過記錄管理（Log Manager） 的交易記錄來執行回復程序。 記錄管理（Log Manager）：負責記錄資料庫系統執行的 每一項操作，這些記錄是透過緩衝區管理（Buffer Manager）存入磁碟。
6-1-2 關聯式資料庫管理系統的架構-說明3
存取模組（Access Module）：轉換成低階檔案系統指令後， 透過緩衝區管理存取資料庫資料。 緩衝區管理（Buffer Manager）：負責電腦記憶體的管理， 系統會配置一塊資料庫緩衝區（Database Buffer）記憶體 對應磁碟儲存的區塊，換句話說，資料庫的資料都是暫存 在緩衝區，需要透過緩衝區管理來存取。 檔案管理（File Manager）：本來屬於作業系統的功能， 配置磁碟空間將資料存入外部儲存裝置，不過為了效率考 量，資料庫管理系統通常自行配置磁碟空間，將資料存入 外部儲存裝置的資料庫，或是從資料庫讀取資料。
6-1-2 關聯式資料庫管理系統的架構-說明4
6-2-1 微軟的SQL Server
6-2-2 SQL Server資料庫系統處理架構
6-2-3 SQL Server的版本
在2008年8月推出SQL Server 2008版，提供多種全新資料類 型，支援微軟LINQ（Language Integrated Query）、
FILESTREAM檔案資料流和更強XML支援，並且提供多種全 新Transact-SQL語法，包含疏鬆欄位（Sparse Column）、 GROUPING SETS、篩選索引、MERGE指令和初始變數等。 2010年的SQL Server 2008 R2（版本10.5），增強多伺服器 的管理和更多商業智慧功能。在2012年4月推出SQL Server 2012版，支援檔案資料表（FileTable）和順序物件（ Sequences），更強的全文檢索搜尋和可用性支援，在T-SQL語言部分，支援更多全新內建函數，例如：IIF()和 CHOOSE()邏輯函數，OFFSET和FETCH NEXT分頁查詢，和錯 誤處理的THROW指令敘述等。
6-2-1 微軟的SQL Server
雖然SQL Server可以將資料庫引擎和相關工具都安 裝在同一台電腦，不過，其邏輯架構上仍然是一 種主從架構，只是客戶端和伺服端都位在同一台 電腦。 一般來說，因為資料庫存取需要使用大量電腦的 系統資源，我們通常是將SQL Server安裝在一台專 屬或多台電腦作為SQL Server資料庫伺服器。在客 戶端電腦是使用網路連接來建立主從架構或分散 式資料庫系統架構。
6-2-2 SQL Server資料庫系統處理架構-
系統架構(說明)
6-2-2 SQL Server資料庫系統處理架構-
系統架構(圖例)
SQL Server 2000之後版本支援同一台實體電腦安裝
多個執行個體（Instances），每一個執行個體擁 有獨立SQL Server服務和SQL Server Agent，可以提 供不同的服務和用途。
簡單的說，執行個體如同是在同一台電腦安裝多 個SQL Server資料庫伺服器。
6-2-2 SQL Server資料庫系統處理架構-
執行個體(說明)
6-2-2 SQL Server資料庫系統處理架構-
執行個體(圖例)
預設執行個體（Default Instance）：預設執行個體 是一個不需名稱的執行個體，在每一台電腦只能 安裝一個預設執行個體。因為是電腦預設的執行 個體，客戶端只需指名電腦名稱，就可以連接此 SQL Server執行個體。 具名執行個體（Named Instance）：在電腦安裝的 SQL Server不是預設或不具名的執行個體，就是具 名執行個體，我們需要替此執行個體命名。因為 同一台電腦可以安裝多個具名執行個體，所以連 接SQL Server時，除了電腦名稱外，還需指明執行 個體名稱。
6-2-2 SQL Server資料庫系統處理架構-
執行個體(種類)
企業版（Enterprise Edition）：提供SQL Server 2012版的所 有功能，它是一個完備的資料管理和商業情報平台，可以 幫助我們建立大型和跨國企業的資料庫系統或分散式資料 庫系統，提供進階商業智慧分析、更強大資料轉換功能和 更高的可用性（High Availability）。 標準版（Standard Edition）：此版本適合使用在中型至小 型企業組織建構完整資料管理和分析平台，提供核心資料 庫引擎、報表和資料分析功能。
商業智慧版（Business Intelligence Edition）：提供核心資 料庫引擎、完整報表和分析能力，和支援完整的商業智慧 ，如同標準版，商業智慧版支援較少的CPU數，而且沒有 提供完整的可用性、安全性和資料倉儲功能。
開發版（Developer Edition）：提供軟體開發商開發建立 各種應用SQL Server的資料庫應用程式，其功能和企業版 完全相同，不過，只能授權使用在系統開發、展示與軟體 測試用途。 Web版（Web Edition）：此版本是針對需要在Windows Server作業系統建立Web環境所提供的解決方案，能夠支 援建立低成本、大規模和立即使用的網際網路應用程式。 Express版（Express Edition）：免費版本的SQL Server，限
制單一CPU、1GB記憶體和最大資料庫尺寸10GB，可以用 來建立桌上型或小型伺服器的資料庫應用程式，作為個人 、SOHO族或小型公司的資料庫解決方案。此版本只提供 資料庫引擎、用戶端工具、Management Studio管理工具 、全文檢索搜尋和部分Reporting Services報表服務功能。
6-2-3 SQL Server的版本-特殊版本
SQL Server 2012 Express：標準版包含最基本的資
料庫引擎，可以用來建立、儲存、更新和存取資 料庫的資料。
SQL Server 2012 Express with Tools：除了資料庫引 擎外，還包含Management Studio Express圖形化客 戶端管理工具。
SQL Server 2012 Express with Advanced Services： Express進階服務版除了標準版功能外，還包含
Management Studio Express管理工具、全文檢索搜 尋和Reporting Services報表服務。
SQL Server 2012 Express版是微軟資料庫系統的入
門產品，在本書是使用Express版來實作資料庫、 測試執行SQL查詢指令、建立視界、預存程序、實 作安全性管理和備份與還原資料庫。
安裝SQL Server資料庫管理系統就是在安裝SQL Server 2012執行個體。SQL Server 2012 Express with Tools 版 除 了 資 料 庫 引 擎 外 ， 還 包 含
Management Studio Express圖形化客戶端管理工具。
6-4-1 SQL Server組態管理員 6-4-2 啟用網路通訊協定
6-4-3 Management Studio整合管理工具
SQL Server組態管理員可以管理SQL Server服務、 設定網路組態和SQL Native Client。 SQL組態管理員管理的項目說明，如下所示： • SQL Server服務：顯示SQL Server伺服器提供的服務清單 ，依安裝和版本而有所不同。 • SQL Server網路組態：顯示支援的網路通訊協定清單（ 分為32和64位元）。
• SQL Native Client 11.0組態：顯示SQL Native Client連線 的相關設定，包含客戶端通訊協定和別名（分為32和 64位元）。
SQL Server：SQL Server最主要的服務，啟動此服 務才能執行SQL指令存取資料庫。預設【自動】啟 動，即當開機啟動Windows作業系統後，就會自 動啟動此服務。 SQL Server Browser：此服務可以讓客戶端連接正 確的執行個體，而不用指明使用的通訊埠號，預 設已停止此服務。
SQL Server Agent：SQL Server代理程式，能夠建立
工作排程和產生警示，可以幫助我們建立SQL Server的自動化管理功能，預設已停止此服務。
在SQL Server組態管理員可以檢視支援的網路通訊 協定，在左邊選【SQLEXPRESS的通訊協定】，可 以看到支援的通訊協定清單，狀態欄是目前狀態。 執行【右】鍵快顯功能表的指令，即可啟用或停 用通訊協定。
6-4-2 啟用網路通訊協定-說明
Shared Memory（共用記憶體）：一種不需要任何設定的 通訊協定，主要是使用在本機電腦，可以在同一台電腦以 安全方式讓客戶端程式連接SQL Server執行個體。
具名管道（Named Pipes）：微軟替Windows區域網路開發 的通訊協定，源於UNIX作業系統的管道觀念，客戶端是使 用IPC（Inter-process Communication）連接SQL Server執行 個體，使用部分記憶體來傳遞資訊至本機或其他網路上的 電腦。 TCP/IP：此為Internet網際網路使用的通訊協定，可以讓不 同硬體架構和作業系統的遠端電腦使用IP位址方式，來連 接SQL Server執行個體。
6-4-2 啟用網路通訊協定-種類
SQL Server Management Studio管理工具是SQL
Server圖形介面的整合管理環境，可以使用同一個 工具來存取、設定、管理和開發SQL Server元件。 在Express版稱為SQL Server Management Studio Express管理工具，一個簡化版的整合管理工具。
請執行「開始/所有程式/Microsoft SQL Server
2012/SQL Server Management Studio」指令啟動 SQL Server Management Studio。
6-4-3 Management Studio整合管理工具-
說明
6-4-3 Management Studio整合管理工具-
圖例
6-5-1 系統目錄的基礎
6-5-2 SQL Server的系統目錄
系統目錄（System Catalog）的內容是資料庫儲存 資料所衍生的一些資料，例如：資料庫系統目前 有哪些資料庫、使用者和每一個資料庫綱要的定 義資料等。 系統目錄的主要目的是提供資料庫管理系統維護 資料庫所需的相關資訊。換句話說，資料庫管理 師需要系統目錄提供的資訊，才能正確管理和維 護資料庫。
6-5-1 系統目錄的基礎-說明
系統資訊：系統內建的資料型態、緩衝區尺寸和 分頁尺寸等系統資訊。 資料庫綱要（Database Schema）：包含每一個關 聯表名稱、建立者、欄位名稱、資料型態、限制 條件、主鍵和外來鍵等資料。 索引（Indexing）資訊：包含所有索引名稱、結構、 搜尋屬性等。 視界（Views）資訊：資料庫定義的視界，它是使 用SQL語言CREATE VIEW指令建立的虛擬關聯表， 包含視界名稱、欄位、資料型態，其來源關聯表 或其他視界。
6-5-1 系統目錄的基礎-內容1
程式化物件資訊：目前的SQL語言大都提供程式化 功能，一些程式化物件預存程序（Stored Procedure）和觸發程序（Trigger）的相關資訊， 包含程序名稱、相關關聯表等資訊也是儲存在系 統目錄。 安全管理資訊：儲存使用者帳號、密碼和使用權 限，即允許使用者存取哪些關聯表、是否可以新 增、更新或刪除值組，是否可以建立資料庫、關 聯表、更改關聯表綱要、匯出或備份資料庫等權 限的資料。
6-5-1 系統目錄的基礎-內容2
關聯式資料庫管理系統的系統目錄在SQL Server是 儲存在系統資料庫之中，我們可以使用目錄檢視 來查詢SQL Server的系統目錄。 在安裝好SQL Server執行個體後，SQL Server預設建立數個 系統資料庫（System Databases），這是一些系統所需和 維持SQL Server正常操作的資料庫。 • master系統資料庫 • Resource資料庫 • model資料庫 • msdb資料庫 • tempdb資料庫
6-5-2 SQL Server的系統目錄-系統資料庫
master系統資料庫 master資料庫記錄SQL Server所有系統層級的資訊， 包含：每位登入的使用者帳戶、系統組態設定、 其他資料庫的狀態和使用者資料庫初始化資訊的 檔案位置。 SQL Server的master資料庫是一個十分重要的系統 資料庫，如果master資料庫損壞，SQL Server就沒 有辦法正常運作。所以資料庫管理者一定要定時 備份master資料庫。
6-5-2 SQL Server的系統目錄-
master系統資料庫
model資料庫 model資料庫是建立SQL Server使用者資料庫的範 本，內含使用者資料庫的基本關聯表綱要，當在 SQL Server建立資料庫時，就是直接複製model資 料庫來建立新資料庫。
6-5-2 SQL Server的系統目錄-
model資料庫
msdb資料庫 msdb資料庫是提供給SQL Server代理程式（SQL Server Agent）使用的資料庫，其內容是儲存警告 （Alert）或作業（Jobs）等排程資料，例如：資 料庫備份的相關工作排程。
6-5-2 SQL Server的系統目錄-
msdb資料庫
tempdb資料庫 tempdb資料庫包含所有暫存資料表和預存程序， 可以儲存目前SQL Server使用中的暫存資料。例如： SQL Server執行查詢時產生的一些中間結果。 tempdb資料庫是一種全域資源，連線SQL Server的 所有使用者都可以使用此資料庫來儲存暫存資料 表和預存程序。
6-5-2 SQL Server的系統目錄-
tempdb資料庫
SQL Server系統目錄是使用一組檢視（即視界）來 提供中繼資料（Meta-data）的資訊，可以讓應用 程式，透過檢視來取得連接資料庫物件的結構與 屬性。 在SQL Server查詢系統目錄，因為原來的系統資料 表已經隱藏，所以新版是建議使用系統檢視下的 目錄檢視（Catalog Views）來查詢，這是一些以 sys結構描述開頭的系統檢視。
6-5-2 SQL Server的系統目錄-
目錄檢視
6-6-1 資料庫物件 6-6-2 資料類型
SQL Server系統或使用者資 料庫都是由物件組成，在 Management Studio的「物 件總管」視窗可以檢視資 料庫的物件清單。
6-6-1 資料庫物件-說明
6-6-1 資料庫物件-
資料庫常用物件的說明
物件 說明 資料庫圖表 使用圖形方式來顯示關聯表綱要 資料表 關聯表（Relations） 檢視 視界（Views） 可程式性 可程式化的相關物件 安全性 安全性管理的相關物件6-6-1 資料庫物件-可程式性物件說明
物件 說明 預存程序 將例行、常用和複雜的資料庫操作預先建立 Transact-SQL 指令碼的程式檔，這是在資料庫管理系統執行的程式檔 案，可以簡化相關或重複的資料庫操作 函數 將一或多個 Transact-SQL 指令建立成函數，以便能夠重複 使用這些函數 資料庫觸發程序 一種特殊用途的預存程序，屬於主動執行的程序，不像預 存程序是使用者執行，而是當資料表操作符合特定條件 時，就自動執行觸發程序 規則 設定與 CHECK 條件相同功能的檢查條件，同一個規則物 件可以套用在多個資料表的不同欄位 預設 定義欄位的預設值，同一個預設物件可以套用在多個資料 表的不同欄位6-6-1 資料庫物件-安全性物件說明
物件 說明 使用者 資料庫的使用者，即允許存取資料庫的使用者清單 角色 角色是將使用資料庫的權限以扮演的角色來進行分類，以便 直接使用角色來快速指定使用者權限 結構描述 結構描述（Schema）可以替資料庫物件新增分類名稱，換 句話說，物件名稱的全名是「結構描述.物件名稱」 SQL Server資料類型（Data Type）相當於是關聯式 資料庫模型的定義域（Domains），它是下列值的 集合，如下所示： • 空值（NULL Value）：沒有指定或未知值。 • 非空值（Non-NULL Value）：字元、字串、數值、日期 /時間、布林值和二進位資料。 SQL Server預設資料類型是Transact-SQL語言使用 的資料類型。Transact-SQL允許使用者擴充資料類 型來自訂資料類型。
6-6-2 資料類型-說明
6-6-2 資料類型-預設資料類型
資料型態 說明 BIT 值為 0、1 或空值（NULL），可以用來儲存布林資料 INT 整數資料，使用 4 位元組儲存的整數，其範圍為-2147483648~2147483647 DECIMAL 數值資料，儲存整數和小數部分的數字，最大可以達到 38 位數 FLOAT 浮點數值資料，使用 8 位元組儲存，其範圍為-1.79E+308~1.79E+308 DATETIME 日期/時間資料 MONEY 貨幣資料，儲存金額的數目，其範圍為-922337203685477.5808~922337203685477.5807 CHAR(n) 固定字串 n 個字元，最大的字串長度達 8000 個字元 VARCHAR(n) 變動字串的 n 個字元，它和 CHAR 的差別在字串長度是變動的， 如果字串沒有填滿，空白的部分會刪除掉，最長為 8000 個字元 TEXT 變動字串，最長為 2GB IMAGE 二進位資料，可以用來儲存圖片資料 XML 儲存 XML 文件或片斷資料 Transact-SQL日期/時間的資料類型和ANSI有些不同， 在ANSI-SQL 92提供三種日期/時間的資料類型，如 下表所示：
6-6-2 資料類型-日期/時間資料類型1
資料型態 說明 DATE 日期資料，格式為 YYYY-MM-DD TIME 時間資料，格式為 HH:MM:SS.nn TIMESTAMP 日期/時間資料，格式為 YYYY-MM-DD HH:MM:SS.nn SQL Server的Transact-SQL提供SMALLDATETIME和 DATETIMESQL Server的Transact-SQL提供 smalldatetime和datetime資料類型，都可以儲存 ANSI-SQL的date、time和timestamp三種資料類型 的日期/時間資料。 在2008之後版本新增ANSI-SQL的date與time，和 datetime2、datetimeoffset共四種日期/時間的資料 類型。
6-6-2 資料類型-日期/時間資料類型2
6-7-1 在SQL Server建立資料庫 6-7-2 執行SQL指令碼檔案
6-7-3 資料庫的卸離與附加
Management Studio提供圖形化管理介面來建立和
刪除使用者資料庫。
6-7-1 在SQL Server建立資料庫-
Transact-SQL語言的CREATE DATABASE指令（這並
不是ANSI-SQL標準的指令）可以建立資料庫，其 語法如下所示：
CREATE DATABASE database_name
上述語法可以建立名為database_name的資料庫。 Transact-SQL刪除資料庫是使用DROP DATABSE指令， 其語法如下所示：
DROP DATABASE database_name
上述語法的database_name是刪除的資料庫名稱。
6-7-1 在SQL Server建立資料庫-
使用SQL指令建立與刪除資料庫
對於現存的SQL指令碼檔案，我們可以在
Management Studio直接開啟檔案來執行SQL指令。
SQL Server可以同時管理多個資料庫，為了避免沒
有使用的資料庫平白佔用系統資源，或者需要將 資料庫移至其他SQL Server。我們可以先卸離指定 資料庫後，再在其他SQL Server將它附加回去。
卸離資料庫並不是 刪除資料庫，卸離 只是將資料庫定義 資料從master資料 庫刪除，如此使用 者就可以複製資料 庫的.MDF
（Master Data File） 資料檔和.LDF
（Log Data File）
交易記錄兩個檔案。
在複製好資料庫檔案後，我們可以在另一台電腦 的SQL Server使用附加（Attach）方式來回存資料 庫。
