Ch4.SQL Server 2005資料庫組成員元件介紹

Presentation on theme: "Ch4.SQL Server 2005資料庫組成員元件介紹"— Presentation transcript:

1 Ch4.SQL Server 2005資料庫組成員元件介紹
第二篇 資料庫物件與系統設計 Ch4.SQL Server 2005資料庫組成員元件介紹 資料庫、資料表、檢視、預存程序、函數、觸發程序、索引、資料庫圖表等 Ch5.資料庫暨資料表設計與建立 資料庫正規化實例 認識系統資料型態 使用SSMSE建立資料庫與資料表並變更資料結構 Ch6.進階資料庫設計 建立示範資料庫(為Ch5.正規化後的資料庫) 整合限制與進階欄位設定 資料庫圖表的使用 有了最第一篇基礎的觀念之後就要進入SQL Server的操作，首先登場的就是SQL Server資料庫的整體觀，介紹在SQL Server中的資料庫內除了常見的資料表之外還有哪些物件。 接著再使用實際的例子做正規化，並且使用SSMSE在SQL Server上建立一個示範資料庫、資料表以及相關資料。 最後再介紹如何使用SQL Server的功能來加強資料表的設計以減低程式設計人員的負擔與錯誤的發生。

2 第四章 SQL Server資料庫組成元件介紹
為使各位同學能對SQL Server 2005 Express有整體的觀念，所以在第四章提綱挈領的介紹組成元件。 有了整個系統架構運作的觀念後對於以後章節的深入探討會有更深切的體會。

3 Chapter 4 Outline 4-1 資料庫 4-2 資料表 4-3 檢視 4-4 預存程序 4-5 函數 4-6 類型
4-7 規則與預設值 4-8 資料庫使用者與角色 4-9 欄位限制 4-10觸發程序 4-11索引 4-12資料庫圖表 4-13本章總結

4 4-1 資料庫(Database) 資料庫原是存放使用者各種資料的地方。但是為了加強系統的安全與效能，因此增加了許多的物件。
開啟SQL Server Management Studio Express(SSMSE) ，實際瀏覽系統資料庫內的物件 資料庫是存放各種物件的容器，而且每個資料庫都是獨立的，因此，不同資料庫內可以有相同名稱的物件。

5 4-2 資料表(Table) 在關聯式資料庫系統中，資料表(Table)是存放資料的地方。 Column 欄位名稱 Row
橫向的資料我們稱之為行(Row)，其意義是一筆資料錄(Record)或稱為Tuple(值組) 縱向的資料我們稱之為列(Column)，其意義是在同一資料表中具有相同屬性的資料集合 資料表是由DBA或相關人員規劃其綱要(包括資料表名稱、屬性以及對應的值域)，然後才在SQL Server中建立 資料的輸入通常搭配應用程式，當然也可以由SSMSE介面輸入 可使用SSMSE實際瀏覽系統資料庫內的資料表，並開啟任一個資料表來檢視其內容 Row

6 4-3檢視(View) 檢視是從一個或多個實際儲存資料的資料表中所衍生出來的，所以可將它想像成只是一個定義在資料表上的虛擬資料表，而被VIEW參考使用到的資料表則稱之為基底資料表 由資料表a、b以及c所產生的View

7 4-3檢視(View)-續 檢視可以想像成另一種存取資料的方式；透過虛擬資料表來觀看資料，就有如使用資料表一樣。
對檢視也可以做資料的新增、修訂或是刪除，不過這些動作會有一些限制而且也都會影響到真正的基底資料表 檢視能夠架構在實際的資料表上也可以架構在另一個已存在的檢視物件之上

8 4-3檢視(View)-優點 資料安全：控制使用者可以查看資料的部份
隱蔽資料複雜性：可以隱蔽實際基本資料表的結構，使用者可以完全不了解真正資料的儲存架構以及欄位意義，可增加邏輯資料的獨立性。 簡化使用者介面：使用者能以簡單的方式來查看該檢視而不必每次都重新下SQL敘述句 資料保密性：對於相同的資料，可針對不同的使用者以不同的方式觀看。 簡化存取權設定：在檢視上指定使用者的存取權限，這樣會比在各個不同資料表上的欄或列設定存取權限要來的有效率。

9 4-4預存程序(Stored Procedure)
查詢資料可以使用SQL敘述，對於重複性較高的查詢則可以將該敘述儲存在資料庫中，預存程序(Stored Procedure)物件就應運而生。 預存程序不但可以將我們撰寫的SQL敘述儲存下來，還可以將該查詢的「查詢計劃」也一併儲存起來以增進下次使用時的執行速度 除了使用者自行建立的預存程序之外，SQL Server內建很多的系統預存程序，幫助DBA管理系統，或提供程式開發人員使用

10 將該查詢建立成預存程序，其名稱為QueryByID
4-4預存程序-續 Select * from customers where customerid=xxx 經常被使用的複雜查詢 Create proc QueryByID As ….. 將該查詢建立成預存程序，其名稱為QueryByID Exec QueryByID 執行預存程序即可

11 4-4預存程序-優點 較佳的執行效能：因執行計畫也一併被儲存 增加資料的安全性：可做為資料安全控管的方式
降低網路傳輸量：只需傳輸呼叫的名稱與參數 確保資料完整性：在預存程序中可包含資料正確性的驗證，不須由前端程式來檢驗。 模組化可重複使用：可以提供多人多次使用 減少人為錯誤：避免人為重新輸入SQL敘述的錯誤

12 4-4預存程序-優點 預存程序的功能還包括： 接受參數的傳遞。 呼叫其他預存程序。 傳回值給呼叫程序或批次程式。
傳回參數值給呼叫程序或批次程式。 由遠端之SQL Server呼叫執行

13 4-5 函數 (Function) 函數與預存程序一樣 ，也是由SQL 敘述所組成，但是呼叫與傳回資料的方式有所同，依照傳回的值可分成兩大類： 純量值函數(數值類函數)：傳回函數中RETURNS子句所定義類型的單一數值。 資料表值函數：傳會TABLE資料類型的函數。 與預存程序一樣，SQL Server也提供很多的系統函數，例如：取得時間日期函數、數學函數等等。

14 4-6 類型(Type) 類型(Types)所指的就是SQL Server所使用的資料型態(Data Types)，除了系統內建的類型之外也允許使用者自訂資料類型。 在進行資料表設計規範欄位的值域(domain)時、進行T-SQL設計宣告變數時都需要明確指定資料型態。例如：整數型態、字元型態、浮點數型態等等。 使用者可以自訂的資料型態，但也是使用系統資料型態來組成

15 4-7規則(Rule) 規則(Rule)是建立一套準則，並結合到欄位或使用者自訂資料型態上。結合完成之後，它會檢查新增的資料或對資料表所做的資料修改是否符合規則所設定的條件。當異動的資料不符合時，SQL Server將會拒絕該處理動作。 適當的使用規則可以減輕前端程式開發的負擔，更能夠避免前端程式處理上所造成的錯誤與減少資料不一致的情況發生。

16 4-8資料庫使用者與角色 在SQL Server 2005中登入帳號(Logins)、資料庫使用者(Users)以及資料庫角色(Roles)是最基本的安全性機制。 登入：在SQL Server 中有建立登入帳號者方能連線入SQL Server，sa就是預設的登入。 資料庫使用者：登入SQL Server並無法使用資料庫，因此還必須為登入建立資料庫使用者 資料庫角色：SQL Server事先規劃好，具有某些資料庫存取權限的資料庫使用者群組。

17 4-9 欄位限制(Constraint) 欄位限制是在設計資料表時，需要考慮欄位的有效值、決定是否允許使用者沒有輸入、決定資料的整合性以及資料一致性等等的問題時使用，欄位限制包括： 主鍵限制(Primary Key Constraints ) 外來鍵限制(Foreign Key Constraints ) 唯一性限制(Unique Constraints ) 檢查限制(Check Constraints ) 預設值限制(Default Constraints ) 欄位的可否NULL限制(Nullability Constraints)

18 4-9 欄位限制-續 主鍵限制：通常在關聯式的資料表上都會設定一主鍵，且每一個資料表最多只能有一個主鍵。主鍵值不可為NULL，且該值在此資料表中是唯一而不可重複的值。 唯一性限制：如果欄位使用了唯一性的限制，則表示輸入的值在該資料表中是唯一而不可重複的值(也就是該欄位可為候選鍵)。由於主鍵本身的定義已有唯一性的限制，所以不能在主鍵欄位上再使用唯一性限制。

19 4-9 欄位限制-續 外來鍵限制：資料表中的欄位或欄位的組合與其他資料表中的主鍵值或是候選鍵(具有唯一性限制的欄位)有關聯特性的鍵就稱之為外來鍵。外來鍵並沒有唯一性的限制，而它所參考的鍵也不一定是在其他的資料表，參考自身的資料表也可以(換句話說，外來鍵與所參考的鍵其欄位名稱可以不同，但是值域卻必須相同)，但所參考的資料表必須在同一個資料庫中。

20 4-9 欄位限制-續 檢查限制：可對某個欄位作檢查條件設計，當對此欄位做資料異動時，系統會自動檢查異動的值是否符合該欄位的檢查限制，如果不符合時則系統會拒絕此次的資料異動。 預設值限制：在欄位上設定預設值可以減少資料的輸入或輸入的錯誤，因為在使用者沒有輸入資料之時，系統便會給予一個所設定的預設值。 欄位的可否NULL限制：設定該欄位是否允許NULL值的輸入，NULL並非是數值的0或是字串中的「空字串」，而是代表未可知的或是不清楚的

21 4-10觸發程序(Trigger Procedure)
觸發程序也是由SQL 敘述所組成。 觸發程序最大的特點就是在資料庫或資料表發生某種事件(新增、刪除、修改)時會自動執行(被觸發的意思)，這也是預存程序或是函數無法達到的事(因為預存程序與函數都需要由使用者呼叫) 觸發程序可達到較為複雜的資料限制功能 觸發程序其實是一種特殊型態的預存程序，當對於特定資料表的特定欄位做特定的處理(INSERT、UPDATE以及DELETE)時，觸發程序就會自動被SQL Server觸發而執行。 觸發程序最主要的特性是它的「自動性」，這也是它的最大的優點，如此一來就不用時時去注意資料表的某種狀態。 觸發程序對於資料的管制與稽核都很有幫助

22 4-11索引(Index) 索引的意思乃是將資料表中的資料按照某個欄位內的值依某種順序排列，以增加資料搜尋的速度，例如英文字典就是一個很好的例子。 當資料表建立上圖的索引之後其資料搜尋的速度就可以加快很多，因為索引是經過排序之後的結果。 例如：未排序(Data Page)之前要尋找資料就只能從頭到尾一筆一筆尋找，稱之為Linear Search所需的時間複雜度(Time Complexity)為O(n)； 按照大小排序的方式(增加了Index Page)之後使用Binary Search所需的時間複雜度就為降為O(log2n)。 使用哪種方式排序、建立成哪種索引樹(Index Tree)就看廠商的功力，使用大小順序來列建立的Binary Tree是最直覺簡單的方式，但是效能最差。通常索引樹都是建成B Tree或是B+ Tree

23 4-12資料庫圖表(Diagram ) 在定義資料表時，通常需要事先將各個資料表中的主鍵、外來鍵以及資料表之間的關聯性定義清楚，如下圖所示。
資料庫圖表最主要是用來描述資料庫中資料表間的關係，而且還可以在上面加以文字註解並且建立/修改資料表間的參照關係。 只要資料表有定義PK與FK，資料庫圖表就會自動建立對應的參照關係，因此在資料表很多的情況下就可以在資料庫圖表中清楚的看到其間的關聯。

24 4-13本章總結 資料庫為存放各種資料與相關物件的容器 Database 資料庫的備份與還原 具權限的使用者方能使用 加速資料查詢 類型
索引 欄位設計 Table 預存程序 交易處理 正規化 主鍵限制 函數 外來鍵限制 資料新增/刪除/修改 1.合法的資料庫使用者方能使用該資料庫，這是SQL Server最基本的登入與系統安全 2.為了存放各種資料所以必須存在資料表，但必須經過正規化的手續 3.建立資料表時必先先設計欄位，因此必須規範每個欄位的名稱與類型 4.可為欄位限制各種限制。 5.可有一個或多個資料表建立檢視 6.為了加速資料的查詢，因此可以建立索引、使用預存程序或是函數 7.對於資料的新增/修改與刪除可以設置觸發程序來稽核 8.資料表間的關聯可以使用資料庫圖表功能來表示與維護 9.為了資料的安全所以必須要有資料庫備份與還原的功能 10.由於是多人使用的系統，因此在資料的新增、查詢、修改以及刪除的過程中會牽涉到一些問題，這就必須由交易處理來解決 *本頁為學習資料庫的大綱精隨，可隨時複習，這樣可讓學生有整體的概念，以達事半功倍之效。 唯一性限制 View 檢查限制 觸發程序 預設值限制 資料表間關係 資料庫圖表 可否null限制