SQL視圖的創(chuàng)建和使用

1、第7章 視圖的創(chuàng)建和使用1本章學(xué)習(xí)目標了解視圖和數(shù)據(jù)表之間的主要區(qū)別了解視圖的優(yōu)點掌握創(chuàng)建、修改和刪除視圖的方法掌握查看視圖信息的方法掌握通過視圖修改數(shù)據(jù)表的方法27.1 概述37.1.1 視圖的概念視圖是一種數(shù)據(jù)庫對象，是從一個或者多個數(shù)據(jù)表或視圖中導(dǎo)出的虛表，視圖所對應(yīng)的數(shù)據(jù)并不真正地存儲在視圖中，而是存儲在所引用的數(shù)據(jù)表中，視圖的結(jié)構(gòu)和數(shù)據(jù)是對數(shù)據(jù)表進行查詢的結(jié)果。根據(jù)創(chuàng)建視圖時給定的條件，視圖可以是一個數(shù)據(jù)表的一部分，也可以是多個基表的聯(lián)合，它存儲了要執(zhí)行檢索的查詢語句的定義，以便在引用該視圖時使用。47.1.2 視圖的優(yōu)點簡化數(shù)據(jù)操作：視圖可以簡化用戶處理數(shù)據(jù)的方式。著重于特定數(shù)據(jù)：

2、不必要的數(shù)據(jù)或敏感數(shù)據(jù)可以不出現(xiàn)在視圖中。視圖提供了一個簡單而有效的安全機制，可以定制不同用戶對數(shù)據(jù)的訪問權(quán)限。提供向后兼容性：視圖使用戶能夠在表的架構(gòu)更改時為表創(chuàng)建向后兼容接口。自定義數(shù)據(jù)：視圖允許用戶以不同方式查看數(shù)據(jù)。導(dǎo)出和導(dǎo)入數(shù)據(jù)：可使用視圖將數(shù)據(jù)導(dǎo)出到其他應(yīng)用程序。57.2 創(chuàng)建視圖在SQL Server 2005中創(chuàng)建視圖主要有兩種方法：使用SQL Server管理控制臺和使用Transact-SQL語句中的CREATE VIEW命令。創(chuàng)建視圖之前，應(yīng)考慮以下基本原則：只能在當前數(shù)據(jù)庫中創(chuàng)建視圖。視圖名稱必須遵循標識符的規(guī)則，且對每個架構(gòu)都必須唯一。必須獲取由數(shù)據(jù)庫所有者授予的創(chuàng)建

3、視圖的權(quán)限。67.2.1 使用SQL Server管理控制臺創(chuàng)建視圖方法是在SQL Server管理控制臺左邊的“樹”選項卡中展開指定的服務(wù)器，打開要創(chuàng)建視圖的數(shù)據(jù)庫，右擊其中的“視圖”對象，從彈出的快捷菜單中選擇“新建視圖”選項。例7-2-1：創(chuàng)建一個視圖，要求顯示T_STUDENT表中所有姓張的同學(xué)，并按性別進行升序排序。77.2.2 使用Transact-SQL語句創(chuàng)建視圖使用Transact-SQL語言中的CREATE VIEW語句創(chuàng)建視圖，其語法形式如下：CREATE VIEW schema_name . view_name (column ,.n ) WITH ENCRYPTION

4、 AS select_statement WITH CHECK OPTION ; 87.2.2 使用Transact-SQL語句創(chuàng)建視圖其中，各參數(shù)的說明如下：schema_name：視圖所屬架構(gòu)的名稱。 view_name：視圖的名稱。column：視圖中的列使用的名稱。AS：指定視圖要執(zhí)行的操作。select_statement：定義視圖的 SELECT 語句。97.2.2 使用Transact-SQL語句創(chuàng)建視圖CHECK OPTION ：強制針對視圖執(zhí)行的所有數(shù)據(jù)修改語句都必須符合在 select_statement 中設(shè)置的條件。ENCRYPTION：對視圖文本進行加密。例7-2-2

5、：使用Transact-SQL語句創(chuàng)建一個新視圖，命名為VIEW_SCORE，要求基表的來源為：T_STUDENT、T_SCORE和T_COURSE，選擇的字段為：T_STUDENT表中的S_NUMBER和S_NAME字段、T_COURSE表中的C_NAME字段及T_SCORE表中的SCORE字段，要求查詢的數(shù)據(jù)為05541班學(xué)生的考試成績。107.2.2 使用Transact-SQL語句創(chuàng)建視圖例7-2-3：使用Transact-SQL語句創(chuàng)建新視圖VIEW_SCORE_NotPass。對數(shù)據(jù)表和對字段的選擇同上，要求只顯示各班不及格的學(xué)生的學(xué)號、姓名、課程名及成績。并加密視圖的定義。例7-

6、2-3：使用Transact-SQL語句創(chuàng)建新視圖VIEW_SCORE_SumAndAverage。要求計算各個班級各門課程的總分及平均分。117.3 查看視圖信息在SQL Server中，可以通過SQL Server管理控制臺或者使用系統(tǒng)存儲過程來查看視圖信息。127.3.1 使用SQL Server管理控制臺查看視圖信息打開SQL Server管理控制臺窗口，在SQL Server管理控制臺左邊的“樹”選項卡中選擇指定的SQL Server組，展開指定的服務(wù)器，打開要查看視圖的數(shù)據(jù)庫文件夾，選擇“視圖”目錄。137.3.2 使用系統(tǒng)存儲過程查看視圖信息系統(tǒng)存儲過程sp_help可以顯示數(shù)據(jù)

7、庫對象的特征信息，sp_depends可以顯示數(shù)據(jù)庫對象所依賴的對象，它們可以在任何數(shù)據(jù)庫對象上運行。sp_helptext可以用于顯示視圖、觸發(fā)器或存儲過程等在系統(tǒng)表中的定義。它們的語法形式分別如下：sp_help 數(shù)據(jù)庫對象名稱sp_helptext 視圖（觸發(fā)器、存儲過程）sp_depends 數(shù)據(jù)庫對象名稱147.3.2 使用系統(tǒng)存儲過程查看視圖信息例7-3-1：使用系統(tǒng)存儲過程sp_help顯示VIEW_SCORE視圖的特征信息。例7-3-2：使用sp_helptext顯示VIEW_SCORE視圖在系統(tǒng)表中的定義。例7-3-3：查看加密視圖的定義信息。例7-3-4：使用sp_depe

8、nds顯示VIEW_SCORE視圖所依賴的對象。157.4 修改和重命名視圖如果已定義的視圖不能滿足用戶要求時，可以使用SQL Server管理控制臺或Transact-SQL語句修改視圖的定義。也可以對現(xiàn)有的視圖重新命名。167.4.1 修改視圖1使用SQL Server管理控制臺修改視圖在SQL Server管理控制臺中，右擊要修改的視圖名稱，從彈出的快捷菜單中選擇“修改”選項，會出現(xiàn)視圖的設(shè)計窗口。該窗口與創(chuàng)建視圖時的窗口相同，可以按照創(chuàng)建視圖的方法修改視圖的定義。172使用Transact-SQL語句修改視圖可以使用Transact-SQL語言中的ALTER VIEW語句修改視圖：AL

9、TER VIEW schema_name . view_name ( column ,.n ) WITH ENCRYPTION AS select_statement WITH CHECK OPTION ; 7.4.1 修改視圖18注意：如果原來的視圖定義是用WITH ENCRYPTION或WITH CHECK OPTION創(chuàng)建的，那么只有在ALTER VIEW中也包含這些選項時，這些選項才有效。例7-4-1：修改視圖VIEW_SCORE_NotPass，在該視圖中增加一個新的條件，要求只顯示高等數(shù)學(xué)不及格的學(xué)生信息。7.4.1 修改視圖197.4.2 重命名視圖1使用SQL Server管理

10、控制臺重命名視圖在SQL Server管理控制臺中，右擊要修改名稱的視圖，從彈出的快捷菜單中選擇“重命名”選項?；蛘咴谝晥D上再次單擊，也可以修改視圖的名稱。當該視圖的名稱變成可輸入狀態(tài)時，可以直接輸入新的視圖名稱。202使用系統(tǒng)存儲過程重命名視圖可以使用系統(tǒng)存儲過程sp_rename來修改視圖的名稱，該過程的語法形式如下：sp_rename old_name,new_name例7-4-2：使用系統(tǒng)存儲過程sp_rename來完成把視圖“VIEW_SCORE_NotPass”重命名為“VIEW_SCORE_NotPass_高等數(shù)學(xué)”。7.4.1 修改視圖217.5 使用視圖通過視圖可以方便地檢索

11、數(shù)據(jù)，并可以修改基本表的數(shù)據(jù)，使用視圖修改數(shù)據(jù)時，需要注意以下幾點：任何修改都只能引用一個基表的列。在視圖中修改的列必須直接引用表列中的基礎(chǔ)數(shù)據(jù)。使用集合運算符得出的計算結(jié)果不可更新。修改的數(shù)據(jù)必須符合對這些列的約束，例如為空性、約束及DEFAULT定義等。227.5.1 插入數(shù)據(jù)記錄1使用SQL Server管理控制臺在視圖中插入記錄具體方法是：在SQL Server管理控制臺中，打開要插入記錄的數(shù)據(jù)表對應(yīng)的視圖，在返回的數(shù)據(jù)記錄的最下面一行中直接插入新記錄即可。237.5.1 插入數(shù)據(jù)記錄2使用Transact-SQL語句通過視圖插入記錄注意：如果視圖創(chuàng)建時定義了限制條件或者基本表的列允許

12、空值或有默認值，而插入的記錄不滿足該條件時，此時，仍然可以向表中插入記錄，只是用視圖檢索時不會顯示出新插入的記錄。如果不想讓這種情況發(fā)生，則可以使用WITH CHECK OPTION選項限制插入不符合視圖規(guī)則的視圖。這樣，在插入記錄時，如果記錄不符合限制條件就不能插入。247.5.1 插入數(shù)據(jù)記錄例7-5-1：創(chuàng)建一個基于表T_STUDENT的新視圖VIEW_STUDENT_05541，要求包含05541班的學(xué)生記錄。例7-5-2：首先創(chuàng)建一個包含限制條件的視圖VIEW_STUDENT_05541_男，限制條件為顯示05541班的男同學(xué)信息，然后插入了一條不滿足限制條件的記錄，再用SELECT

13、語句檢索視圖和表。例7-5-3：在例子7-5-2的基礎(chǔ)上添加WITH CHECK OPTION選項。257.5.2 更新數(shù)據(jù)記錄1使用SQL Server管理控制臺在視圖中更新記錄具體方法是：在SQL Server管理控制臺中，打開要更新記錄的數(shù)據(jù)表對應(yīng)的視圖，在返回的數(shù)據(jù)記錄窗口中直接修改記錄即可。267.5.2 更新數(shù)據(jù)記錄2使用Transact-SQL語句通過視圖更新記錄例7-5-4：創(chuàng)建一個基于表T_STUDENT的視圖VIEW_STUDENT_05541_Modify，條件是顯示05541班的學(xué)生的學(xué)號、姓名和性別，然后通過該視圖修改表T_STUDENT中的記錄。277.5.3 刪除

14、數(shù)據(jù)記錄1使用SQL Server管理控制臺在視圖中刪除記錄具體方法是：在SQL Server管理控制臺中，打開要刪除記錄的數(shù)據(jù)表對應(yīng)的視圖，在返回的數(shù)據(jù)記錄窗口中直接刪除記錄即可。287.5.3 刪除數(shù)據(jù)記錄2使用Transact-SQL語句通過視圖刪除記錄使用視圖刪除記錄時，可以直接利用Transact-SQL語言的DELETE語句刪除視圖中的記錄。但應(yīng)該注意，如果有刪除條件，則WHERE條件中使用到的字段必須是在視圖中定義過的字段。例7-5-5：利用視圖VIEW_STUDENT_05541，先插入一條記錄，然后刪除此條記錄。297.6 刪除視圖307.6.1 使用SQL Server管理

15、控制臺刪除視圖打開SQL Server管理控制臺窗口，選擇要刪除的視圖，右擊該視圖名稱，從彈出的快捷菜單中選擇“刪除”選項，會出現(xiàn)“刪除對象”對話框。注意：在確認刪除之前，應(yīng)該查看視圖的依賴關(guān)系窗口，查看是否有數(shù)據(jù)庫對象依賴于將被刪除的視圖。如果存在這樣的對象，那么首先確定是否還有必要保留該對象，如果不必繼續(xù)保存，可以直接刪除掉該視圖，否則只能放棄刪除。317.6.2 使用Transact-SQL語句刪除視圖可以使用Transact-SQL語句中的DROP VIEW命令刪除視圖，其語法形式如下：DROP VIEW view_name ,n可以使用該命令同時刪除多個視圖，只需在要刪除的各視圖名稱之間用逗號隔開即可。例7-6-1：刪除視圖VIEW_STUDENT_05541_Modify和視圖VIEW_STUDENT_05541_男。327.7 思考與練習(xí)1視圖和數(shù)據(jù)表之間的主要區(qū)別是什么？2使用視圖的優(yōu)點有哪些？3如何通過SQL Server管