11 檢視表的建立 11-1 檢視表的基礎 11-2 建立檢視表 11-3 修改與刪除檢視表 11-4 編輯檢視表的內容.

Presentation on theme: "11 檢視表的建立 11-1 檢視表的基礎 11-2 建立檢視表 11-3 修改與刪除檢視表 11-4 編輯檢視表的內容."— Presentation transcript:

1

2 11 檢視表的建立 11-1 檢視表的基礎 11-2 建立檢視表 11-3 修改與刪除檢視表 11-4 編輯檢視表的內容

3 11-1 檢視表的基礎 SQL Server檢視表 檢視表的種類 檢視表的優缺點

4 SQL Server檢視表-說明 SQL Server檢視表是一個虛擬資料表，因為它本身並沒有儲存資料，而只有定義資料，定義從哪些資料表或檢視表挑出哪些欄位或記錄。我們一樣可以在檢視表執行記錄新增、刪除和更新，當然，這些操作都是作用在其定義的來源資料表。 檢視表顯示的資料是從基底資料表（Base Tables）所取出，只是依照定義過濾掉不屬於檢視表的資料，如果檢視表的資料是從其他檢視表導出，也只是重複再過濾一次。所以檢視表如同是一個從不同資料表或檢視表抽出的資料積木，然後使用這些積木拼出所需的資料表。

5 SQL Server檢視表-圖例

6 檢視表的種類 列欄子集檢視表（Row-and-Column Subset Views）：從單一資料表或其他檢視表所導出的檢視表，只挑選資料表或其他檢視表中所需的欄位和記錄。換句話說，建立的檢視表是資料表或其他檢視表的子集。 合併檢視表（Join Views）：使用合併查詢從多個資料表或其他檢視表所導出的檢視表，合併檢視表的欄位和記錄是來自多個資料表或其他檢視表。 統計摘要檢視表（Statistical Summary Views）：一種特殊的列欄子集檢視表或合併檢視表，只是再使用聚合函數（Aggregate Function）產生指定欄位所需的統計資料。

7 檢視表的優缺點-優點1 達成邏輯資料獨立：檢視表的定義相當於外部與概念對映（External/Conceptual Mapping），就算更改資料表的定義資料，也只需同時更改檢視表的外部與概念對映的定義資料，就可以讓使用者檢視相同觀點的資料，而不會影響外部綱要。 增加資料安全性：檢視表可以隱藏和過濾資料，只讓使用者看到它允許看到的資料，可以增加資料的安全性。例如：在【員工】資料表擁有【薪水】欄位，使用檢視表就可以隱藏員工的薪水資料，只讓使用者看到其他部分的員工資料。

8 檢視表的優缺點-優點2 簡化資料查詢：將常用和複雜的查詢定義成檢視表，即可簡化資料查詢，因為我們不再需要每次重複執行複雜的SQL查詢指令，直接開啟現成的檢視表即可。 簡化使用者觀點：檢視表可以增加資料的可讀性，讓資料庫使用者專注於所需的資料，例如：替欄位更名成使用者觀點的欄位名稱。

9 檢視表的優缺點-缺點 執行效率差：檢視表並沒有真正儲存資料，只是一個虛擬資料表，資料是在使用時才從資料表導出，因為經過一道轉換手續，其執行效率比不過直接存取資料表。 更多的操作限制：檢視表雖然也是一種資料表，不過在新增、更新和刪除資料時，為了避免違反完整性限制條件，在操作上有更多的限制。 增加管理的複雜度：檢視表可以一層一層的從其他檢視表導出，例如：B_檢視表和C_檢視表是從D_檢視表導出，A_檢視表是從B_檢視表導出，複雜的檢視表關聯將增加管理眾多資料表和檢視表的複雜度，因為不小心刪錯檢視表，可能會造成嚴重後果。

10 11-2 建立檢視表 11-2-2 使用T-SQL指令建立檢視表 11-2-3 從其他檢視表建立檢視表
使用Management Studio建立檢視表 使用T-SQL指令建立檢視表 從其他檢視表建立檢視表

11 11-2-1 使用Management Studio 建立檢視表

12 11-2-2 使用T-SQL指令建立檢視表-語法 T-SQL語言是使用CREATE VIEW指令來建立檢視表，其基本語法如下所示：
[WITH ENCRYPTION ][ WITH SCHEMABINDING ] AS SELECT指令敘述

13 11-2-2 使用T-SQL指令建立檢視表- 建立列欄子集檢視表(說明)
列欄子集檢視表是指檢視表的內容是資料表記錄或欄位的子集合，它是從資料表的欄位和記錄集合中，取出所需子集合的檢視表。列欄子集檢視表依選擇的範圍，可以分為三種，如下所示： 欄子集檢視表（Column Subset Views）：指檢視表的欄位是資料表欄位集合的子集合。 列子集檢視表（Row Subset Views）：指檢視表的記錄是資料表記錄集合的子集合。 列欄子集檢視表（Row-and-Column Subset Views）：指檢視表的欄位和記錄都是資料表欄位和記錄集合的子集合。

14 11-2-2 使用T-SQL指令建立檢視表- 建立列欄子集檢視表(範例1)
SQL指令碼檔：Ch11_2_2_01.sql 在【學生】資料表建立學生電話聯絡資料的【學生聯絡_檢視】的檢視表，並且指定檢視表的別名，如下所示： CREATE VIEW 學生聯絡_檢視 (學號,學生姓名,學生電話) AS SELECT 學號, 姓名, 電話 FROM 學生 GO SELECT * FROM 學生聯絡_檢視

15 11-2-2 使用T-SQL指令建立檢視表- 建立列欄子集檢視表(範例2)
SQL指令碼檔：Ch11_2_2_02.sql 在【員工】資料表建立薪水超過50000員工資料的【高薪員工_檢視】的加密檢視表，如下所示： CREATE VIEW 高薪員工_檢視 WITH ENCRYPTION AS SELECT * FROM 員工 WHERE 薪水 > 50000 GO SELECT * FROM 高薪員工_檢視

16 11-2-2 使用T-SQL指令建立檢視表- 建立列欄子集檢視表(範例3)
SQL指令碼檔：Ch11_2_2_03.sql 在【員工】資料表建立薪水超過50000員工資料，而且只有身份證字號、姓名和電話三個欄位的【高薪員工聯絡_檢視】檢視表，並且使用結構描述繫結選項，如下所示： CREATE VIEW 高薪員工聯絡_檢視 WITH SCHEMABINDING AS SELECT 身份證字號, 姓名, 電話 FROM dbo.員工 WHERE 薪水 > 50000 GO

17 11-2-2 使用T-SQL指令建立檢視表- 建立合併檢視表(說明)
合併檢視表（Join Views）是多個資料表執行合併查詢所建立的檢視表。

18 11-2-2 使用T-SQL指令建立檢視表- 建立合併檢視表(範例1)
SQL指令碼檔：Ch11_2_2_04.sql 在【學生】、【課程】、【教授】和【班級】四個資料表建立合併檢視表的【學生_班級_檢視】檢視表，可以用來顯示學生的上課資料，如下所示： CREATE VIEW 學生_班級_檢視 AS SELECT 學生.學號, 學生.姓名, 課程.*, 教授.* FROM 教授 INNER JOIN (課程 INNER JOIN

19 11-2-2 使用T-SQL指令建立檢視表- 建立合併檢視表(範例2)
(學生 INNER JOIN 班級 ON 學生.學號=班級.學號) ON 班級.課程編號 = 課程.課程編號) ON 班級.教授編號 = 教授.教授編號 GO SELECT * FROM 學生_班級_檢視

20 11-2-2 使用T-SQL指令建立檢視表- 建立統計摘要檢視表(說明)
統計摘要檢視表（Statistical Summary Views）是一種特殊的列欄子集檢視表或合併檢視表，只是再使用聚合函數（Aggregate Function）產生指定欄位所需的統計資料。

21 11-2-2 使用T-SQL指令建立檢視表- 建立統計摘要檢視表(範例1-1)
SQL指令碼檔：Ch11_2_2_05.sql 建立【學生】、【課程】和【班級】三個資料表的統計摘要檢視表【學分_檢視】，它是一個合併檢視表，因為使用COUNT()和SUM()的聚合函數來顯示每位學生的上課數和所修的總學分，如下所示： CREATE VIEW 學分_檢視 AS SELECT 學生.學號, COUNT(*) AS 修課數, SUM(課程.學分) AS 學分數 FROM 學生, 課程, 班級

22 11-2-2 使用T-SQL指令建立檢視表- 建立統計摘要檢視表(範例1-2)
WHERE 學生.學號 = 班級.學號 AND 課程.課程編號 = 班級.課程編號 GROUP BY 學生.學號 GO SELECT * FROM 學分_檢視

23 11-2-2 使用T-SQL指令建立檢視表- 建立統計摘要檢視表(範例2-1)
SQL指令碼檔：Ch11_2_2_06.sql 請修改統計摘要【學分_檢視】檢視表，建立只顯示學生所修總學分超過6個學分的學生上課總數和學分數的合併檢視表【高學分_檢視】，如下所示： CREATE VIEW 高學分_檢視 AS SELECT 學生.學號, COUNT(*) AS 修課數, SUM(課程.學分) AS 學分數 FROM 學生, 課程, 班級

24 11-2-2 使用T-SQL指令建立檢視表- 建立統計摘要檢視表(範例2-2)
WHERE 學生.學號 = 班級.學號 AND 課程.課程編號 = 班級.課程編號 GROUP BY 學生.學號 HAVING SUM(課程.學分) >= 6 GO SELECT * FROM 高學分_檢視

25 從其他檢視表建立檢視表-說明 檢視表不只可以從資料表導出，如果有已經存在的檢視表，我們也可以從現有檢視表來建立新檢視表。

26 11-2-3 從其他檢視表建立檢視表-範例 SQL指令碼檔：Ch11_2_3.sql
在上一節【學分_檢視】檢視表只顯示學號，我們可以再次使用此檢視表和【學生】資料表，建立合併檢視表【學生_學分_檢視】來顯示學生姓名和電話欄位的詳細資料，如下所示： CREATE VIEW 學生_學分_檢視 AS SELECT 學分_檢視.*, 學生.姓名, 學生.電話 FROM 學生, 學分_檢視 WHERE 學生.學號 = 學分_檢視.學號 GO SELECT * FROM 學生_學分_檢視

27 從其他檢視表建立檢視表-圖例

28 11-3 修改與刪除檢視表 修改檢視表 刪除檢視表

29 11-3-1 修改檢視表- 在Management Studio修改檢視表
修改檢視表的設計 更改檢視表名稱

30 11-3-1 修改檢視表- 使用T-SQL指令修改檢視表(語法)
T-SQL語言是使用ALTER VIEW指令來修改檢視表，其基本語法如下所示： ALTER VIEW 檢視表名稱 [(欄位別名清單)] [WITH ENCRYPTION ][ WITH SCHEMABINDING ] AS SELECT指令敘述 語法是使用ALTER VIEW指令來修改已經存在檢視表，它和CREATE VIEW語法完全相同，簡單的說，修改檢視表就是重新定義檢視表設計。

31 11-3-1 修改檢視表- 使用T-SQL指令修改檢視表(範例)
SQL指令碼檔：Ch11_3_1.sql 修改【學生聯絡 _檢視】檢視表，取消別名且新增性別欄位，如下所示： ALTER VIEW 學生聯絡_檢視 AS SELECT 學號, 姓名,性別, 電話 FROM 學生 GO SELECT * FROM 學生聯絡_檢視

32 11-3-2 刪除檢視表- 使用Management Studio
對於資料表中不再需要的檢視表，我們可以使用Manament Studio工具的「物件總管」視窗的檢視表上，執行【右】鍵快顯功能表的【刪除】指令，就可以刪除檢視表。

33 11-3-2 刪除檢視表-使用T-SQL T-SQL語言是使用DROP VIEW指令來刪除檢視表，其基本語法如下所示：
SQL指令碼檔：Ch11_3_2.sql 刪除Ch13_3_1_01.sql建立的【學生連絡_檢視】檢視表，如下所示： DROP VIEW 學生聯絡_檢視

34 11-4 編輯檢視表的內容 在檢視表新增記錄 在檢視表更新記錄 在檢視表刪除記錄

35 11-4 編輯檢視表的內容-說明 檢視表雖然是一個虛擬資料表，不過，如同資料表一般，我們一樣可以在檢視表執行新增、更新和刪除操作。
編輯檢視表需要滿足一些限制條件，如下所示： 檢視表需要包含資料表的主鍵。 在CREATE VIEW指令的SELECT指令敘述不可包含DISTINCT、聚合函數、GROUP BY和HAVING子句，如果有，檢視表就只能查詢。換句話說，統計摘要檢視表因為擁有聚合函數，所以只能查詢，而不能新增、更新和刪除記錄。 因為檢視表是從資料表所導出，所以新增、更新和刪除操作仍然需要遵守來源資料表的完整性限制條件。

36 11-4 編輯檢視表的內容- WITH CHECK OPTION子句
CREATE VIEW指令的WITH CHECK OPTION子句是一個選項，表示其建立的檢視表在新增、更新和刪除記錄時，需要檢查完整性限制條件，如果不符合條件，就顯示錯誤訊息。基本語法如下所示： CREATE VIEW 檢視表名稱 AS SELECT指令敘述 WITH CHECK OPTION

37 11-4-1 在檢視表新增記錄-1 【生日_檢視_有主鍵】檢視表因為擁有主鍵，滿足前述限制條件，換句話說，我們可以在此檢視表新增記錄。
SQL指令碼檔：Ch11_4_1_01.sql 在【生日_檢視_有主鍵】檢視表新增一筆學生記錄，如下所示： INSERT INTO 生日_檢視_有主鍵 VALUES ('S016', '江峰', ' ' ) GO SELECT * FROM 學生

38 在檢視表新增記錄-2 【生日_檢視_沒有主鍵】檢視表因為沒有主鍵，並不滿足前述限制條件。換句話說，我們並不允許在此檢視表新增記錄。 SQL指令碼檔：Ch11_4_1_02.sql 在【生日_檢視_沒有主鍵】檢視表新增一筆學生記錄，如下所示： INSERT INTO 生日_檢視_沒有主鍵 VALUES (‘江峰峰’, ‘ ’) 因為在建立檢視表時使用WITH CHECK OPTION指令，所以SQL Server會出現錯誤訊息。

39 11-4-2 在檢視表更新記錄-1 【生日_檢視_有主鍵】檢視表因為擁有主鍵，滿足前述限制條件，換句話說，我們可以在此檢視表更新記錄。
SQL指令碼檔：Ch11_4_2_01.sql 在【生日_檢視_有主鍵】檢視表將學號S016學生的生日改為 ' '，如下所示： UPDATE 生日_檢視_有主鍵 SET 生日 = ' ' WHERE 學號 = 'S016' GO SELECT * FROM 學生

40 11-4-2 在檢視表更新記錄-2 【生日_檢視_沒有主鍵】檢視表因為沒有主鍵，並不滿足前述限制條件。
SQL指令碼檔：Ch11_4_2_02.sql 在【生日_檢視_沒有主鍵】檢視表將學生江峰的生日改為' '，如下所示： UPDATE 生日_檢視_沒有主鍵 SET 生日 = ' ' WHERE 姓名 = '江峰' GO SELECT * FROM 學生 SQL Server仍然會更新【學生】資料表的記錄，理論上，資料庫管理系統應該避免在沒有主鍵的視界執行更新操作。

41 11-4-3 在檢視表刪除記錄-1 【生日_檢視_有主鍵】檢視表因為擁有主鍵，滿足前述限制條件，換句話說，我們可以在此檢視表刪除記錄。
SQL指令碼檔：Ch11_4_3_01.sql 在【生日_檢視_有主鍵】檢視表刪除學號S016的學生資料，如下所示： DELETE FROM 生日_檢視_有主鍵 WHERE 學號='S016' GO SELECT * FROM 學生

42 11-4-3 在檢視表刪除記錄-2 【生日_檢視_沒有主鍵】檢視表因為沒有主鍵，不滿足前述限制條件。
SQL指令碼檔：Ch11_4_3_02.sql 在【生日_檢視_沒有主鍵】檢視表刪除學生王美麗，如下所示： DELETE 生日_檢視_沒有主鍵 WHERE 姓名='王美麗' GO SELECT * FROM 學生 SQL Server仍然會刪除【學生】資料表的記錄，理論上，資料庫管理系統應該避免在沒有主鍵的視界執行刪除操作。