第三章認識及操作 SQL Server 資料庫 3-1 關於系統資料庫 3-2 資料庫的組成 3-3 以 T-SQL 控制及了解資料庫 3-3-1 以 T-SQL 查詢資料 3-3-2 查看伺服器組態設定 1
3.1 關於系統資料庫 系統資料庫 master 資料庫 tempdb 資料庫 描述 記錄 SQL Server 執行個體的所有系統層級資訊 是保存暫存物件或中繼結果集的工作空間 model 資料庫 msdb 資料庫 resource 資料庫 用來當作 SQL Server 執行個體上建立之所有資料庫的範本 對 model 資料庫進行的修改 ( 例如, 資料庫大小 定序 復原模式和其他資料庫選項 ) 會套用到之後建立的任何資料庫 SQL Server Agent 用於排程警示和作業 唯讀的資料庫 包含 SQL Server 2005 擁有的系統物件 系統物件實際上會保存在資源資料庫中, 但邏輯上會出現在每個資料庫的 sys 結構描述中 2
master 資料庫 記錄了 SQL Server 系統的所有系統資訊, 例如 : 登入帳戶 連結的伺服器, 以及系統組態設定 記錄 SQL Server 的初始化資訊 如果 master 資料庫無法使用,SQL Server 也會無法啟動 備份 master 資料庫 在 SQL Server 2005 中, 系統物件不再儲存於 master 資料庫, 而是儲存於資源資料庫 3
tempdb 資料庫 內含連線至系統的所有使用者之暫存資料表與預存程序 當 SQL Server 啟動時, 便會使用模型 (model) 資料庫的複本來重建 Tempdb, 並將其大小重設為上次的設定大小 tempdb 不允許進行備份和還原作業 4
model 資料庫 可作為資料庫範本 使用 CREATE DATABASE 陳述式時, 會複製 model 資料庫的內容, 來建立資料庫的第一個部份, 新資料庫的剩餘部份則填入空白頁 5
msdb 資料庫 SQL Server 代理程式 使用的資料庫 提供 Service Broker, Database Mail 之類的功能使用 Database Mail 是從 Microsoft SQL Server 2005 Database Engine 傳送電子郵件訊息的企業解決方案 使用 Database Mail, 資料庫應用程式就能夠將電子郵件訊息傳送給使用者 這類訊息能包含查詢結果, 也可以包含來自網路上任何資源的檔案 6
3-2 資料庫的組成 (8 大物件 ) 由 資料庫圖表 至 安全性 都是資料庫的組成 7
3-2-1 資料表 table 8
3-2-2 資料庫圖表 9
3-2-2 資料庫圖表 (cont.) 1 對多 1 對多 10
3-2-3 檢視表 view SP 參考第 8 章 檢視表是一種虛擬資料表, 是由查詢定義其內容 檢視表與真正的資料表類似, 檢視是由一組命名的資料行和資料列所組成 除了索引檢視以外, 檢視在資料庫中並沒有儲存的資料值集 資料的資料列與資料行是從定義檢視的查詢所參考的資料表而來, 而且在參考檢視時不會動態產生 11
3-2-3 檢視表 view (cont.) 12
3-2-4 預存程序 Store Procedure 預存程序 : 類似於其他程式設計語言中的程序 SP 參考第 10 章 可接受輸入參數, 並以輸出參數的形式將多個數值傳回呼叫程序或批次處理 包含可在資料庫中執行作業的程式陳述式, 包括呼叫其他程序 將狀態值傳回呼叫程序或批次處理, 以指示成功或失敗 ( 及失敗原因 ) Transact-SQL 的 EXECUTE 陳述式可用來執行預存程序 系統內建以 sp_ 開頭 13
3-2-4 預存程序 Store Procedure (cont.) 使用者建立之預存程序盡量不以 sp_ 開頭 14
DML 觸發程序 : 3-2-5 觸發程序 trigger 只有當資料表中的資料被修改時才會執行 在 INSERT UPDATE 及 DELETE 陳述式上操作, 並可協助強制執行商業規則, 以及當資料表或檢視中的資料修改時協助擴充資料的完整性 DDL 觸發程序 : Trigger 參考第 11 章 在回應 資料定義語言 (DDL) 陳述式時引發 在 CREATE ALTER DROP 以及其他 DDL 陳述式上操作 它們是用以執行管理工作並強制執行影響資料庫的商業規則 它們適用於整個資料庫或整個伺服器之單一類型的所有命令 15
3-2-5 觸發程序 trigger 範例 16
3-2-6 自訂函數 user-define function 使用者自訂函數是以資料庫物件的形式儲存, 可提供下列方式重複使用的程式碼 : 在 Transact-SQL 陳述式中, 例如 : SELECT 在呼叫函數的應用程式中 在另一個使用者自訂函數的定義中 若要將檢視參數化, 或改善索引檢視的功能 若要在資料表中定義資料行 若要在資料行上定義 CHECK 條件約束 若要取代預存程序 17
3-2-6 自訂函數 user-define function (cont.) 18
3-2-7 自訂資料型態 ( 二大類 ) 1. 使用別名使用者自訂類型 : ( 較簡易 ) 2. 使用 CLR 使用者自訂類型 : 在 SQL Server 針對 Microsot.NET Framework Common Language Runtime (CLR) 中所建的組件進行程式化後,SQL Server 2005 可讓您在此 SQL Server 內建立資料庫物件 資料庫物件可充分運用 CLR 所提供的豐富程式設計模型, 包括觸發程序 預存程序 函數 彙總函數和類型等 其他資料可參考線上叢書 19
使用別名使用者自訂類型 別名資料類型是以系統資料類型為基礎 別名資料類型的使用時機 : 當多個資料表必須在一個資料行中儲存相同的資料類型 這些資料行的資料類型 長度和 Null 屬性完全一樣時 例如 :postal_code 的別名資料類型可以根據 char 資料類型來建立 當您建立別名資料類型時, 必須提供下列參數 : 名稱 做為別名資料類型基礎的系統資料類型 Null 值屬性 ( 資料類型是否允許 Null 值 ) 20
使用別名使用者自訂類型 - 範例 21
22
補充篇 Null 值 NULL 是資料庫中的特殊值, 用來代表未知值的概念 NULL 與空白字元或 0 不同 空白實際上是有效的字元, 而 0 是有效的數字 NULL 只代表不知道數值究竟為何這樣的一個概念 NULL 也與零長度的字串不同 如果資料行定義中包含 NOT NULL 子句, 就無法插入包含 NULL 值的資料列 如果資料行定義中只包含 NULL 關鍵字, 就可以接受 NULL 值 23
資料完整性 : 補充篇 完整性 資料行的每筆資料有正確的資料值 必須屬於正確的資料類型 正確的值域內 參考完整性 : 正確地維護資料表之間的關係 資料表中的資料只應該指向另一個資料表中現有的資料列 ; 而不應該指向不存在的資料列 維護完整性的實作方法 : 1. 條件約束 constraint 2. 規則 rule 3. 預設值 default 4. DML 觸發程序, DML trigger 24
條件約束 (constraint) 條件約束是定義有關資料行中允許值的規則, 是強制完整性的標準機制 條件約束應優先於 DML 觸發程序 規則以及預設值 查詢最佳化工具也使用條件約束定義來建立高效能的查詢執行計劃 e.g. NOT NULL : 指定資料行不接受 NULL 值 CHECK 條件約束 : 限制資料行中可接受的值 UNIQUE 條件約束 : 資料行中數值的唯一性 FOREIGN KEY 條件約束 : 強制資料表之間的關係 25
條件約束 (constraint) 範例 26
規則 規則它會執行一些與 CHECK 條件約束一樣的功能 具有回溯相容性 (Backward Compatibility) 的功能 3-2-8 規則 rule CHECK 條件約束 在限制資料行的數值方面, 使用 CHECK 條件約束是優先 標準的方式 CHECK 條件約束也會比規則更加精確 只可以將一個規則套用到資料行 規則會建立成個別的物件, 須與資料行連結 未來 SQL 不再提供 rule, 使用 CHECK 條件約束代替 可以套用多個 CHECK 條件約束到資料行 CHECK 條件約束是指定為 CREATE TABLE 陳述式的一部份 27
3-2-9 預設值 default 新增資料時, 如果未指定資料行的值, 將由預設值指定資料行所用的值 預設值可以是求值結果為常數的任何值, 例如 : 常數 內建函數或數學運算式 若要套用預設值, 使用 DEFAULT 關鍵字來建立預設值定義 若資料行並不允許 Null 值, 並且沒有 DEFAULT 定義, 必須明確地替資料行指定值, 否則會傳回錯誤, 指出該資料行並不允許 Null 值 28
3-3 以 T-SQL 控制及了解資料庫 3-3-1 以 Transact-SQL 查詢資料 使用新增查詢功能 撰寫程式碼 分別按下及按鈕 剖析 : 檢查 SQL 語法是否有誤 執行 : 執行 SQL 語法 29
T-SQL 範例 TRY! 將 SELECT 改為 SELET >> 30
3-3-2 查看伺服器組態設定 執行 EXEC sp_configure sp_configure 是 SQL Server 系統提供的預存程序 顯示或變更目前伺服器的全域組態設定 31
sp_configure 資料行名稱 name minimum maximum config_value run_value 資料類型 nvarchar(35) int int int int 描述 組態選項的名稱 組態選項的最小值 組態選項的最大值 利用 sp_configure 來設定的組態選項值 (sys.configurations.value 中的值 ) 組態選項目前在執行中的值 (sys.configurations.value_in_use 中的值 ) 32