Microsoft PowerPoint - sql2005_ch03.ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - sql2005_ch03.ppt"

旋贝
5 years ago
Views:

1 第三章認識及操作 SQL Server 資料庫 3-1 關於系統資料庫 3-2 資料庫的組成 3-3 以 T-SQL 控制及了解資料庫以 T-SQL 查詢資料查看伺服器組態設定 1

2 3.1 關於系統資料庫系統資料庫 master 資料庫 tempdb 資料庫描述記錄 SQL Server 執行個體的所有系統層級資訊是保存暫存物件或中繼結果集的工作空間 model 資料庫 msdb 資料庫 resource 資料庫用來當作 SQL Server 執行個體上建立之所有資料庫的範本對 model 資料庫進行的修改 ( 例如, 資料庫大小定序復原模式和其他資料庫選項 ) 會套用到之後建立的任何資料庫 SQL Server Agent 用於排程警示和作業唯讀的資料庫包含 SQL Server 2005 擁有的系統物件系統物件實際上會保存在資源資料庫中, 但邏輯上會出現在每個資料庫的 sys 結構描述中 2

3 master 資料庫記錄了 SQL Server 系統的所有系統資訊, 例如 : 登入帳戶連結的伺服器, 以及系統組態設定記錄 SQL Server 的初始化資訊如果 master 資料庫無法使用,SQL Server 也會無法啟動備份 master 資料庫在 SQL Server 2005 中, 系統物件不再儲存於 master 資料庫, 而是儲存於資源資料庫 3

4 tempdb 資料庫內含連線至系統的所有使用者之暫存資料表與預存程序當 SQL Server 啟動時, 便會使用模型 (model) 資料庫的複本來重建 Tempdb, 並將其大小重設為上次的設定大小 tempdb 不允許進行備份和還原作業 4

5 model 資料庫可作為資料庫範本使用 CREATE DATABASE 陳述式時, 會複製 model 資料庫的內容, 來建立資料庫的第一個部份, 新資料庫的剩餘部份則填入空白頁 5

6 msdb 資料庫 SQL Server 代理程式使用的資料庫提供 Service Broker, Database Mail 之類的功能使用 Database Mail 是從 Microsoft SQL Server 2005 Database Engine 傳送電子郵件訊息的企業解決方案使用 Database Mail, 資料庫應用程式就能夠將電子郵件訊息傳送給使用者這類訊息能包含查詢結果, 也可以包含來自網路上任何資源的檔案 6

7 3-2 資料庫的組成 (8 大物件 ) 由資料庫圖表至安全性都是資料庫的組成 7

8 3-2-1 資料表 table 8

9 3-2-2 資料庫圖表 9

10 3-2-2 資料庫圖表 (cont.) 1 對多 1 對多 10

11 3-2-3 檢視表 view SP 參考第 8 章檢視表是一種虛擬資料表, 是由查詢定義其內容檢視表與真正的資料表類似, 檢視是由一組命名的資料行和資料列所組成除了索引檢視以外, 檢視在資料庫中並沒有儲存的資料值集資料的資料列與資料行是從定義檢視的查詢所參考的資料表而來, 而且在參考檢視時不會動態產生 11

12 3-2-3 檢視表 view (cont.) 12

13 3-2-4 預存程序 Store Procedure 預存程序 : 類似於其他程式設計語言中的程序 SP 參考第 10 章可接受輸入參數, 並以輸出參數的形式將多個數值傳回呼叫程序或批次處理包含可在資料庫中執行作業的程式陳述式, 包括呼叫其他程序將狀態值傳回呼叫程序或批次處理, 以指示成功或失敗 ( 及失敗原因 ) Transact-SQL 的 EXECUTE 陳述式可用來執行預存程序系統內建以 sp_ 開頭 13

14 3-2-4 預存程序 Store Procedure (cont.) 使用者建立之預存程序盡量不以 sp_ 開頭 14

15 DML 觸發程序 : 觸發程序 trigger 只有當資料表中的資料被修改時才會執行在 INSERT UPDATE 及 DELETE 陳述式上操作, 並可協助強制執行商業規則, 以及當資料表或檢視中的資料修改時協助擴充資料的完整性 DDL 觸發程序 : Trigger 參考第 11 章在回應資料定義語言 (DDL) 陳述式時引發在 CREATE ALTER DROP 以及其他 DDL 陳述式上操作它們是用以執行管理工作並強制執行影響資料庫的商業規則它們適用於整個資料庫或整個伺服器之單一類型的所有命令 15

16 3-2-5 觸發程序 trigger 範例 16

17 3-2-6 自訂函數 user-define function 使用者自訂函數是以資料庫物件的形式儲存, 可提供下列方式重複使用的程式碼 : 在 Transact-SQL 陳述式中, 例如 : SELECT 在呼叫函數的應用程式中在另一個使用者自訂函數的定義中若要將檢視參數化, 或改善索引檢視的功能若要在資料表中定義資料行若要在資料行上定義 CHECK 條件約束若要取代預存程序 17

18 3-2-6 自訂函數 user-define function (cont.) 18

19 3-2-7 自訂資料型態 ( 二大類 ) 1. 使用別名使用者自訂類型 : ( 較簡易 ) 2. 使用 CLR 使用者自訂類型 : 在 SQL Server 針對 Microsot.NET Framework Common Language Runtime (CLR) 中所建的組件進行程式化後,SQL Server 2005 可讓您在此 SQL Server 內建立資料庫物件資料庫物件可充分運用 CLR 所提供的豐富程式設計模型, 包括觸發程序預存程序函數彙總函數和類型等其他資料可參考線上叢書 19

20 使用別名使用者自訂類型別名資料類型是以系統資料類型為基礎別名資料類型的使用時機 : 當多個資料表必須在一個資料行中儲存相同的資料類型這些資料行的資料類型長度和 Null 屬性完全一樣時例如 :postal_code 的別名資料類型可以根據 char 資料類型來建立當您建立別名資料類型時, 必須提供下列參數 : 名稱做為別名資料類型基礎的系統資料類型 Null 值屬性 ( 資料類型是否允許 Null 值 ) 20

21 使用別名使用者自訂類型 - 範例 21

22 22

23 補充篇 Null 值 NULL 是資料庫中的特殊值, 用來代表未知值的概念 NULL 與空白字元或 0 不同空白實際上是有效的字元, 而 0 是有效的數字 NULL 只代表不知道數值究竟為何這樣的一個概念 NULL 也與零長度的字串不同如果資料行定義中包含 NOT NULL 子句, 就無法插入包含 NULL 值的資料列如果資料行定義中只包含 NULL 關鍵字, 就可以接受 NULL 值 23

24 資料完整性 : 補充篇完整性資料行的每筆資料有正確的資料值必須屬於正確的資料類型正確的值域內參考完整性 : 正確地維護資料表之間的關係資料表中的資料只應該指向另一個資料表中現有的資料列 ; 而不應該指向不存在的資料列維護完整性的實作方法 : 1. 條件約束 constraint 2. 規則 rule 3. 預設值 default 4. DML 觸發程序, DML trigger 24

25 條件約束 (constraint) 條件約束是定義有關資料行中允許值的規則, 是強制完整性的標準機制條件約束應優先於 DML 觸發程序規則以及預設值查詢最佳化工具也使用條件約束定義來建立高效能的查詢執行計劃 e.g. NOT NULL : 指定資料行不接受 NULL 值 CHECK 條件約束 : 限制資料行中可接受的值 UNIQUE 條件約束 : 資料行中數值的唯一性 FOREIGN KEY 條件約束 : 強制資料表之間的關係 25

26 條件約束 (constraint) 範例 26

27 規則規則它會執行一些與 CHECK 條件約束一樣的功能具有回溯相容性 (Backward Compatibility) 的功能規則 rule CHECK 條件約束在限制資料行的數值方面, 使用 CHECK 條件約束是優先標準的方式 CHECK 條件約束也會比規則更加精確只可以將一個規則套用到資料行規則會建立成個別的物件, 須與資料行連結未來 SQL 不再提供 rule, 使用 CHECK 條件約束代替可以套用多個 CHECK 條件約束到資料行 CHECK 條件約束是指定為 CREATE TABLE 陳述式的一部份 27

28 3-2-9 預設值 default 新增資料時, 如果未指定資料行的值, 將由預設值指定資料行所用的值預設值可以是求值結果為常數的任何值, 例如 : 常數內建函數或數學運算式若要套用預設值, 使用 DEFAULT 關鍵字來建立預設值定義若資料行並不允許 Null 值, 並且沒有 DEFAULT 定義, 必須明確地替資料行指定值, 否則會傳回錯誤, 指出該資料行並不允許 Null 值 28

29 3-3 以 T-SQL 控制及了解資料庫以 Transact-SQL 查詢資料使用新增查詢功能撰寫程式碼分別按下及按鈕剖析 : 檢查 SQL 語法是否有誤執行 : 執行 SQL 語法 29

30 T-SQL 範例 TRY! 將 SELECT 改為 SELET >> 30

31 3-3-2 查看伺服器組態設定執行 EXEC sp_configure sp_configure 是 SQL Server 系統提供的預存程序顯示或變更目前伺服器的全域組態設定 31

32 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

投影片 1

投影片 1 資料庫管理程式 ( 補充教材 -Part2) 使用 ADO.NET 連結資料庫 ( 自行撰寫程式碼以實現新增刪除修改等功能 ) Private Sub InsertButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InsertButton.Click ' 宣告相關的 Connection