第 11 章 建立檢視表.

Presentation on theme: "第 11 章 建立檢視表."— Presentation transcript:

1 第 11 章 建立檢視表

2 本章提要 11-1 檢視表的用途 11-2 使用 SQL Server Management Studio 管理工具建立檢視表
11-3 用 CREATE VIEW 敘述建立檢視表 11-4 用 ALTER VIEW 敘述修改檢視表 11-5 運用 UNION 設計檢視表 11-6 編輯檢視表中的記錄 11-7 刪除檢視表

3 11-1 檢視表的用途 以往當我們要查詢資料時, 一定是很認份地從設計 SELECT 敘述開始, 然後執行查詢敘述得到所要的結果。
現在我們就依照這個程序, 從練習 01 資料庫的訂單與客戶資料表中, 查詢下單日期、客戶名稱和地址等資訊。 訂單與客戶這兩個資料表的結構及目前的內容如下：

4 檢視表的用途

5 檢視表的用途

6 檢視表的用途 接著我們設計如下的查詢敘述取得所要的資料：

7 檢視表的用途 上述的流程似乎是理所當然, 而且也不怎麼費事, 但假如您經常要以同樣的條件來查詢資料時, 那麼每次都要重複輸入相同的查詢敘述, 可就太沒有效率了！ 若將這個經常要重複使用的查詢敘述建立成檢視表, 就不用這麼麻煩了！請看底下的示範：

8 檢視表的用途 以後若要再用相同的條件來查詢資料時, 只要輸入下面一行敘述就可以得到所要的查詢結果了：

9 檢視表的用途 其實將查詢敘述建立成檢視表, 不僅僅是簡化查詢的動作而已；更重要的是, 檢視表具備資料表的特性, 可以衍生出更多的應用, 例如檢視表也可以像資料表一般, 作為查詢的資料來源呢！

10 檢視表與資料表的差異 檢視表用起來雖然與資料表沒什麼兩樣, 但還是要認清兩者本質上的不同。
我們知道資料表是實際儲存記錄的地方, 然而檢視表並不保存任何記錄, 它儲存的是查詢敘述, 其所呈現出來的記錄實際上是來自於資料表：(如下頁) 正因為檢視表只存查詢敘述, 不存記錄, 所以在應用上相當具有彈性, 因為我們可以依據各種查詢需要建立不同的檢視表, 但不會因此而增加資料庫的資料量。

11 檢視表與資料表的差異

12 檢視表的資料來源 這裏要澄清一點, 檢視表的記錄是經由查詢而來的, 這個查詢的資料來源可以是單一資料表、多個資料表、甚至是其它檢視表！
但各位要知道, 檢視表的記錄即使是從其它檢視表中查詢而來的, 追本溯源, 這些記錄仍是存在資料表中, 而非檢視表裏。

13 使用檢視表的優點 了解檢視表的用途及本質, 最後我們總結一下使用檢視表的優點, 這些優點同時也是我們為什麼要使用檢視表的原因：
增加可讀性：在檢視表中可以使用較易了解的欄位名稱, 方便使用者檢視查詢結果。 資料安全及保密：針對不同的使用者, 可以建立不同的檢視表, 以限制其所能檢視或編輯的資料內容。

14 使用檢視表的優點 總而言之, 善用檢視表可以讓資料庫的設計、管理、及使用, 都更加有效率、更加輕鬆愉快。
降低查詢的複雜度：使用者可以透過檢視表來做複雜的查詢, 而不需學習或使用複雜的查詢技巧。 方便程式維護：如果應用程式使用檢視表來存取資料, 那麼當資料表的結構改變時, 只需更改檢視表的設定即可, 不須更改程式。 總而言之, 善用檢視表可以讓資料庫的設計、管理、及使用, 都更加有效率、更加輕鬆愉快。

15 11-2 使用 SQL Server Management Studio 管理工具建立檢視表
觀察與修改檢視表的設計 更改檢視表名稱

16 建立檢視表 要建立檢視表請在 SQL Server Management Studio 的物件總管窗格中展開欲處理的資料庫 (筆者以練習 01 為例), 展開後於檢視項目上按滑鼠右鈕, 執行『新增檢視』 命令：(如下頁) 建立檢視表的方法與操作介面, 和第 10-3 節所介紹使用查詢設計工具設計 SQL 查詢的方法差不多, 其差異僅在於設計好檢視表的查詢敘述後, 別忘了按下儲存鈕儲存檢視表。

17 建立檢視表

18 建立檢視表

19 建立檢視表

20 觀察與修改檢視表的設計 建好的檢視表會集中放在資料庫的檢視項目中, 我們只要將 SQL Server Management Studio 的物件總管窗格切換到資料庫的檢視項目, 就可以看到已建立的檢視表了：

21 觀察與修改檢視表的設計

22 觀察與修改檢視表的設計 如果要觀察或修改檢視表的內部設計 (即檢視表的 SELECT 敘述), 請在物件總管中選取檢視表, 然後按滑鼠右鈕執行『開啟檢視』 命令。 以下以 11-3 頁建立的下單記錄檢視表為例：(如下頁) 您可以在 SQL 窗格看到完整的 SELECT 敘述, 也可以直接修改 SELECT 敘述的內容。

23 觀察與修改檢視表的設計

24 更改檢視表名稱 檢視表名稱是可以更改的。 在 SQL Server Management Studio 的物件總管窗格中, 在欲更名的檢視表名稱上連按兩下, 或選取檢視表後按滑鼠右鈕執行『重新命名』命令, 讓檢視表名稱呈反白狀態, 此時就可輸入新的檢視表名稱。

25 11-3 用 CREATE VIEW 敘述建立檢視表 T-SQL 提供了 CREATE VIEW 敘述來建立檢視表。一般我們建立檢視表所使用的語法相當簡單：

26 用 CREATE VIEW 敘述建立檢視表 實際上, CREATE VIEW 敘述的完整語法還包括自訂欄位別名、為建立檢視表的 SQL 敘述加密 ... 等等：

27 用 CREATE VIEW 敘述建立檢視表 CREATE VIEW 敘述中的 select_statement 不可以使用 INTO、ORDER BY、COMPUTE 或 COMPUTE BY 子句。例如下面這樣的寫法是錯誤的：

28 用 CREATE VIEW 敘述建立檢視表 如果要排序, 可以等到實際使用檢視表做查詢時再設定, 例如：

29 指定檢視表的欄位別名 若在 view_name 後面沒有指定要顯示的欄位別名, 則檢視表將直接使用 SELECT 子句中的欄位名稱；而如果在 view_name 後有加上欄位別名 (Alias), 則檢視表的欄位名稱便會使用此處所指定的別名。例如：

30 指定檢視表的欄位別名 如果在建立檢視表的時候自行加上欄位別名, 則顯示的效果也會不一樣：

31 指定檢視表的欄位別名

32 為 CREATE VIEW 敘述加密 每個資料庫中記載該資料庫裡所有檢視表、規則、預存程序 (Stored Procedures)、觸發程序 (Trigger) 與條件約束 (Constraint) 的資訊都存在 sys.syscomments 系統檢視表中。 如果我們不想讓 sys.syscomments 檢視表中記載的 CREATE VIEW 敘述毫無防備地供人查閱, 在建立檢視表時, 可利用 WITH ENCRYPTION 參數來加密。

33 為 CREATE VIEW 敘述加密 底下我們就來建立一個加密的檢視表：

34 為 CREATE VIEW 敘述加密 在練習 01 資料庫的檢視項目中建立客戶聯絡電話檢視表後, 您可按新增查詢鈕來開啟一個查詢頁次, 輸入如下的指令查詢： 您可以看到下列的結果：

35 為 CREATE VIEW 敘述加密

36 為 CREATE VIEW 敘述加密 檢視表一旦加密之後就無法解密了, 意思是說, 我們無法再檢視或修改檢視表的設計。但這並不影響檢視表的使用, 我們還是可以使用加密的檢視表作為查詢的資料來源, 或查閱檢視表的資料內容。 如果想要修改加密檢視表的原始設計, 唯一的辦法就是以新的設計取代原始設計。 例如將原檢視表先刪除然後重新建立、或利用稍後介紹的 ALTER VIEW 敘述來重建檢視表。

37 結構描述繫結 WITH SCHEMABINDING
CREATE VIEW 敘述若加上 WITH SCHEMABINDING (結構描述繫結) 參數, 則可限制此檢視表所用到的資料表或檢視表, 都不允許用 ALTER 更改設計, 或用 DROP 將之刪除。

38 結構描述繫結 WITH SCHEMABINDING
請注意, 當加上 WITH SCHEMABINDING 參數時, select_statement 中不可以用 * 代表所有欄位, 必須將欄位名稱寫出；同時使用到的資料表或檢視表名稱, 必須用兩部份式名稱 (即 Schema.object) 來表示, 例如：

39 在檢視表設計視窗中設定結構描述繫結選項 在檢視表設計工具中若要為檢視表設定繫結至結構描述, 則只要在檢視表左邊的屬性窗格將繫結至結構描述項目設定為 "是" 即可。

40 檢查檢視表的資料變動 若在 CREATE VIEW 敘述的最後加上 WITH CHECK OPTION, 則當此檢視表中的記錄被修改或新增時, 若不符合原先建立檢視表時的條件, 便會拒絕執行。 例如：

41 檢查檢視表的資料變動

42 檢查檢視表的資料變動 現在我們將 CheckOption 檢視表中, 書籍編號為 3 的書籍降價為 350 (原來為 450), 看看會發生什麼事：

43 檢查檢視表的資料變動 很不幸的, 執行後將出現錯誤訊息, 您知道錯在哪裏嗎？
原來當初建立 CheckOption 檢視表時, 有個條件是售價需在 400 ~ 600 之間, 而現在若將售價降為 350, 便不符合這項條件了, 所以剛才的編輯動作無法執行。

44 檢查檢視表的資料變動 不過, 如果建立檢視表時沒有加上 WITH CHECK OPTION 參數, 則編輯檢視表的記錄時, 便不會去檢查是否符合檢視表的條件限制。 但有一點要注意, 如果編輯後的結果, 使得那筆記錄不符合檢視表的條件限制, 則下次執行檢視表時會看不到該筆記錄。

45 11-4 用 ALTER VIEW 敘述 修改檢視表 ALTER VIEW 敘述可用來修改現有檢視表的內部設計, 其語法如下：

46 用 ALTER VIEW 敘述修改檢視表 ALTER VIEW 敘述的語法和 CREATE VIEW 敘述完全一樣, 只不過此處的 view_name 必須是已經建立的檢視表名稱。 底下我們就直接舉例來說明 ALTER VIEW 敘述的用法。 下面是之前 頁建立客戶聯絡電話檢視表的語法：

47 用 ALTER VIEW 敘述修改檢視表 現在我們要修改客戶聯絡電話檢視表為不加密, 並且還要為它設定欄位別名：
執行後, 您可到物件總管去檢查看看, 客戶聯絡電話檢視表是否真的沒有加密了。

48 用 ALTER VIEW 敘述修改檢視表 再來我們來修改 VIEW_CheckOption 檢視表, 這個檢視表是比照之前 CheckOption 檢視表的方式建立的：

49 用 ALTER VIEW 敘述修改檢視表 現在我們用 ALTER VIEW 敘述來調整它的售價範圍：
執行後, VIEW_CheckOption 檢視表的資料內容就會以新的條件重新組合。

50 11-5 運用 UNION 設計檢視表 UNION 的作用可合併多個 SELECT 敘述的查詢結果, 這裏我們要教您利用 UNION 來設計檢視表。 假設旗旗公司的員工名單及客戶名單分別儲存在員工與客戶資料表中 (這兩個表結構都相同, 只是存放的資料不一樣而已), 現在旗旗公司準備舉辦年終尾牙, 要擬出一份結合員工和客戶資料表的參加人員名單, 怎麼做呢？

51 運用 UNION 設計檢視表 我們的做法是, 運用 UNION 結合兩個資料表建立一個尾牙參加人員名單檢視表：

52 運用 UNION 設計檢視表 很簡單吧！不需新增任何資料就可輕而易舉達到我們的目的。

53 11-6 編輯檢視表中的記錄 基本上, 我們可以如同編輯資料表的資料一般, 編輯檢視表中的資料, 而且操作技巧還完全相同。
不過要編輯檢視表中的記錄其實有諸多限制, 底下我們先來看看有哪些限制, 再為您介紹編輯的技巧。 編輯檢視表記錄的前提 用 INSERT、UPDATE、DELETE 敘述編輯檢視表 SQL Sever Management Studio 編輯檢視表

54 編輯檢視表記錄的前提 什麼樣的檢視表才可以接受編輯呢？我們知道檢視表的資料內容其實是查詢的結果, 在 8-1 節曾提過要編輯整個查詢結果的一些限制, 若您忘了的話, 趕緊回去復習一下。 另外, 就是欄位本身的問題, 例如： 檢視表中的欄位, 若源自於計算欄位, 或是運用彙總函數、運算式所產生的, 則該欄位的值不能在檢視表中更改。

55 編輯檢視表記錄的前提 來源資料表中不必 (也不可) 輸入的欄位, 例如設定識別屬性或 Timestamp 型別的欄位, 在檢視表中同樣也不必 (不能) 輸入或更改。 在檢視表中更動的內容最好只影響到單一資料表, 以免發生出乎意料的結果。 而我們在 8-1 節提過的欄位限制同樣也不能忽略。總而言之, 若要在檢視表中編輯資料, 選擇愈單純的檢視表愈好, 而且那個檢視表最好只有一個來源資料表, 因為這樣比較容易確保資料能夠正確輸入或修正。

56 用 INSERT、UPDATE、DELETE 敘述編輯檢視表
例如我們想刪除客戶聯絡電話檢視表中的天天書局這筆記錄：

57 用 INSERT、UPDATE、DELETE 敘述編輯檢視表
編輯檢視表資料比較棘手的地方應該是 “新增”。通常檢視表裏僅會顯示資料表的部份欄位, 可是新增資料時, 除了指定這些欄位的值, 我們還要確保 SQL Server 知道如何為那些未顯示的資料表欄位填入資料, 否則這項操作就會失敗。 例如要為客戶聯絡電話檢視表新增一筆記錄：

58 用 INSERT、UPDATE、DELETE 敘述編輯檢視表
這項操作是無法成功的, 因為來源資料表客戶總共有 5 個欄位, 上面的敘述僅指定 3 個欄位值, 另外客戶編號欄位沒有值可填, 又不允許 NULL, 也不會自動編號, 亦沒有預設值, SQL Server 實在不知道怎麼辦才好, 所以只好拒絕這項操作啦！ 倘若客戶編號這個欄位允許 NULL, 或是能夠自動產生值, 則剛才那筆新增的敘述就能夠成功了。

59 SQL Sever Management Studio 編輯檢視表
若要在 SQL Sever Management Studio 中使用管理工具編輯檢視表的資料內容, 請在物件總管中選取欲編輯的檢視表, 然後按滑鼠右鈕執行『修改』命令開啟檢視頁次, 接著按一下工具列的執行 SQL 鈕 將檢視表的資料內容顯示在結果窗格中, 就可在結果窗格中編輯資料了：

60 SQL Sever Management Studio 編輯檢視表

61 11-7 刪除檢視表 若建立的檢視表已經沒有利用價值, 則可以將它刪除。您可以選擇使用 SQL Server Management Studio 管理工具刪除, 或利用 DROP VIEW 敘述來刪除。 使用 SQL Server Management Studio 管理工具刪除檢視表 用 DROP VIEW 敘述刪除檢視表

62 使用 SQL Server Management Studio 管理工具刪除檢視表
請先將物件總管窗格切換到資料庫的檢視項目, 然後選取要刪除的檢視表, 接著按滑鼠右鈕執行『刪除』命令 (或選擇功能表的『編輯 / 刪除』命令, 或按 Delete 鍵), 此時會出現如下的交談窗：

63 使用 SQL Server Management Studio 管理工具刪除檢視表

64 用 DROP VIEW 敘述 刪除檢視表 DROP VIEW 敘述的語法如下：