new第7章 視圖的創(chuàng)建和使用

1、2024/3/21,1,第7章 視圖的創(chuàng)建和使用,2,本章學(xué)習(xí)目標(biāo),了解視圖和數(shù)據(jù)表之間的主要區(qū)別了解視圖的優(yōu)點掌握創(chuàng)建、修改和刪除視圖的方法掌握查看視圖信息的方法掌握通過視圖操作數(shù)據(jù)表的方法,3,第7章 視圖的創(chuàng)建和使用,,7.1 概述,1,,7.2 創(chuàng)建視圖,2,,7.3 查看視圖信息,3,,7.4 修改和重命名視圖,4,,7.5 使用視圖,3,5,,7.6 刪除視圖,6,4,7.1 概述,7.1.1 視

2、圖的概念視圖是一種數(shù)據(jù)庫對象，是從一個或者多個數(shù)據(jù)表或視圖中導(dǎo)出的虛表，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上的。視圖被定義后便存儲在數(shù)據(jù)庫中，通過視圖看到的數(shù)據(jù)只是存放在基表中的數(shù)據(jù)。當(dāng)對通過視圖看到的數(shù)據(jù)進(jìn)行修改時，相應(yīng)的基表的數(shù)據(jù)也會發(fā)生變化；若基表的數(shù)據(jù)發(fā)生變化，這種變化也會自動地反映到視圖中。視圖可以是一個數(shù)據(jù)表的一部分，也可以是多個數(shù)據(jù)表的聯(lián)合。,5,7.1 概述,7.1.2 視圖的優(yōu)點視圖可以屏蔽數(shù)據(jù)的復(fù)雜性

3、，簡化用戶對數(shù)據(jù)庫的操作。 視圖可以讓不同的用戶以不同的方式看到不同或者相同的數(shù)據(jù)集?？梢允褂靡晥D重新組織數(shù)據(jù)。 視圖可以定制不同用戶對數(shù)據(jù)的訪問權(quán)限。,本章首頁,6,7.2 創(chuàng)建視圖,在SQL Server 2005中創(chuàng)建視圖主要有兩種方法：使用SQL Server管理控制臺和使用Transact-SQL語句中的CREATE VIEW命令。創(chuàng)建視圖之前，應(yīng)考慮以下基本原則：只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖。視圖名稱必須遵循標(biāo)識

4、符的規(guī)則，且對每個架構(gòu)都必須唯一。必須獲取由數(shù)據(jù)庫所有者授予的創(chuàng)建視圖的權(quán)限。,7,7.2 創(chuàng)建視圖,7.2.1 使用SQL Server管理控制臺創(chuàng)建視圖方法：打開相應(yīng)數(shù)據(jù)庫——右擊 “視圖” ——新建視圖【例7-1】創(chuàng)建一個視圖，要求顯示T_STUDENT表中所有姓張的同學(xué)，并按性別進(jìn)行升序排序。,8,7.2 創(chuàng)建視圖,方法1：在SQL Server管理控制臺中直接設(shè)置相關(guān)選項，SQL Server會自動生成相關(guān)的SQ

5、L語句。,9,7.2 創(chuàng)建視圖,方法2：在SQL Server管理控制臺中直接輸入以下SQL語句：SELECT S_NUMBER, S_NAME, SEXFROM T_STUDENTWHERE S_NAME LIKE '張%'ORDER BY SEX,10,7.2 創(chuàng)建視圖,7.2.2 使用Transact-SQL語句創(chuàng)建視圖使用Transact-SQL語言中的CREATE VIEW語句

6、創(chuàng)建視圖。語法形式：CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH ENCRYPTION ] AS select_statement [ WITH CHECK OPTION ] [ ; ],11,7.2 創(chuàng)建視圖,參數(shù)說明：schema_name：視圖所屬架構(gòu)的名稱。 view_name：視圖的名稱。column：視圖

7、中的列使用的名稱。AS：指定視圖要執(zhí)行的操作。select_statement：定義視圖的 SELECT 語句。CHECK OPTION ：強(qiáng)制針對視圖執(zhí)行的所有數(shù)據(jù)修改語句都必須符合在 select_statement 中設(shè)置的條件。ENCRYPTION：對視圖文本進(jìn)行加密。,12,7.2 創(chuàng)建視圖,【例7-2】使用Transact-SQL語句創(chuàng)建一個新視圖，命名為VIEW_SCORE，要求基表的來源為：T_STUDENT、

8、T_SCORE和T_COURSE，選擇的字段為：T_STUDENT表中的S_NUMBER和S_NAME字段、T_COURSE表中的C_NAME字段及T_SCORE表中的SCORE字段，要求查詢的數(shù)據(jù)為05541班學(xué)生的考試成績。,13,7.2 創(chuàng)建視圖,CREATE VIEW VIEW_SCOREAS SELECT T_STUDENT.S_NUMBER, T_STUDENT.S_NAME, T_COUR

9、SE.C_NAME, T_SCORE.SCOREFROM T_STUDENT, T_COURSE, T_SCOREWHERE T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=T_SCORE.C_NUMBER AND LEFT(T_STUDENT.S_NUMBER,5)='05541',14,【例7-3】使用T

10、ransact-SQL語句創(chuàng)建新視圖VIEW_SCORE_NotPass。對數(shù)據(jù)表和對字段的選擇同上，要求只顯示各班不及格的學(xué)生的學(xué)號、姓名、課程名及成績。并加密視圖的定義。,CREATE VIEW VIEW_SCORE_NotPassWITH ENCRYPTIONAS SELECT T_STUDENT.S_NUMBER,T_STUDENT.S_NAME, T_COURSE.C_NAME, T_SCORE.SCO

11、REFROM T_STUDENT,T_COURSE,T_SCOREWHERE T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=T_SCORE.C_NUMBER AND SCORE<60,7.2 創(chuàng)建視圖,15,【例7-4】使用Transact-SQL語句創(chuàng)建新視圖VIEW_SCORE_SumAndAverage。要求計算各個班級各

12、門課程的總分及平均分。,7.2 創(chuàng)建視圖,16,CREATE VIEW VIEW_SCORE_SumAndAverageAS SELECT SUBSTRING(T_STUDENT.S_NUMBER,1, LEN(T_STUDENT.S_NUMBER)-2) AS 班級, T_COURSE.C_NAME AS 課程名稱, SUM(SCORE) AS 總分, AVG(S

13、CORE) AS 平均分FROM T_STUDENT,T_COURSE,T_SCOREWHERE T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=T_SCORE.C_NUMBERGROUP BY SUBSTRING(T_STUDENT.S_NUMBER,1, LEN(T_STUDENT.S_NUMBER)-2),T_COURS

14、E.C_NAME,7.2 創(chuàng)建視圖,本章首頁,17,7.3 查看視圖信息,7.3.1 使用SQL Server管理控制臺查看視圖信息方法：右擊相應(yīng)視圖——打開視圖,18,7.3.2 使用系統(tǒng)存儲過程查看視圖信息sp_help：顯示數(shù)據(jù)庫對象的特征信息，sp_depends：顯示數(shù)據(jù)庫對象所依賴的對象，它們可以在任何數(shù)據(jù)庫對象上運行。sp_helptext：顯示視圖、觸發(fā)器或存儲過程等在系統(tǒng)表中的定義。語法形式：sp_

15、help 數(shù)據(jù)庫對象名稱sp_helptext 視圖（觸發(fā)器、存儲過程）sp_depends 數(shù)據(jù)庫對象名稱,7.3 查看視圖信息,19,【例7-5】使用系統(tǒng)存儲過程sp_help顯示VIEW_SCORE視圖的特征信息?！纠?-6】使用sp_helptext顯示VIEW_SCORE視圖在系統(tǒng)表中的定義。,sp_help VIEW_SCORE,sp_helptext VIEW_SCORE,7.3 查看視圖信息,20,

16、【例7-7】查看加密視圖的定義信息?！纠?-8】使用sp_depends顯示VIEW_SCORE視圖所依賴的對象。,sp_helptext VIEW_SCORE_NotPass,sp_depends VIEW_SCORE,7.3 查看視圖信息,本章首頁,21,7.4 修改和重命名視圖,7.4.1 修改視圖1．使用SQL Server管理控制臺修改視圖方法：右擊要修改的視圖——修改,22,2．使用Transact-SQL

17、語句修改視圖可以使用ALTER VIEW語句修改視圖。語法形式：ALTER VIEW [schema_name.] view_name [ ( column [ ,...n ] ) ] [ WITH ENCRYPTION ] AS select_statement [ WITH CHECK OPTION ] [ ; ]【注意】如果原來的視圖定義是用WITH ENCRYPTION或WITH CHECK OPTION創(chuàng)建的

18、，那么只有在ALTER VIEW中也包含這些選項時，這些選項才有效。,7.4 修改和重命名視圖,23,【例7-9】修改視圖VIEW_SCORE_NotPass，在該視圖中增加一個新的條件，要求只顯示高等數(shù)學(xué)不及格的學(xué)生信息。,7.4 修改和重命名視圖,ALTER VIEW VIEW_SCORE_NotPassWITH ENCRYPTIONAS SELECT T_STUDENT.S_NUMBER, T_STUDENT.S_NAM

19、E, T_COURSE.C_NAME, T_SCORE.SCOREFROM T_STUDENT,T_COURSE,T_SCOREWHERE T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=T_SCORE.C_NUMBER AND T_COURSE.C_NAME='高等數(shù)學(xué)' AND SCORE

20、<60,24,7.4 修改和重命名視圖,7.4.2 重命名視圖1．使用SQL Server管理控制臺重命名視圖方法：右擊相應(yīng)視圖——重命名2．使用系統(tǒng)存儲過程重命名視圖可以使用系統(tǒng)存儲過程sp_rename來修改視圖的名稱。語法形式：sp_rename old_name,new_name【例7-10】使用系統(tǒng)存儲過程sp_rename來完成把視圖“VIEW_SCORE_NotPass”重命名為“VIEW_SCO

21、RE_NotPass_高等數(shù)學(xué)”。,sp_rename VIEW_SCORE_NotPass, VIEW_SCORE_NotPass_高等數(shù)學(xué),本章首頁,25,7.5 使用視圖,通過視圖可以方便地檢索數(shù)據(jù)；也可以通過視圖修改基本表的數(shù)據(jù)； 使用視圖修改數(shù)據(jù)時，需要注意以下幾點：任何修改都只能引用一個基表的列。在視圖中修改的列必須直接引用表列中的基礎(chǔ)數(shù)據(jù)。使用集合運算符（UNION等）得出的計算結(jié)果不可更新。修改

22、的數(shù)據(jù)必須符合對這些列的約束，例如為空性、約束及DEFAULT定義等。,26,7.5 使用視圖,7.5.1 插入數(shù)據(jù)記錄1．使用SQL Server管理控制臺在視圖中插入記錄方法：打開要相應(yīng)視圖—— 錄入新記錄2．使用Transact-SQL語句通過視圖插入記錄,27,7.5 使用視圖,【例7-11】創(chuàng)建一個基于表T_STUDENT的新視圖VIEW_STUDENT_05541，要求包含05541班的學(xué)生記錄。,CREATE

23、 VIEW VIEW_STUDENT_05541(S_NUMBER, S_NAME, SEX)ASSELECT S_NUMBER, S_NAME, SEX FROM T_STUDENTWHERE S_NUMBER like '05541%‘GOinsert into view_student_05541values('0554111','王剛強(qiáng)','男‘),28,7

24、.5 使用視圖,CREATE VIEW VIEW_STUDENT_05541_男ASSELECT * FROM T_STUDENTWHERE S_NUMBER like '05541%' AND SEX='男'GOInsert into VIEW_STUDENT_05541_男Values ('0554112','王麗','女',

25、 '1984-5-5', '團(tuán)員')GOSELECT * FROM VIEW_STUDENT_05541_男GOSELECT * FROM T_STUDENT WHERE S_NUMBER like '05541%'GO,【例7-12】首先創(chuàng)建一個包含限制條件的視圖VIEW_STUDENT_05541_男，限制條件為顯示05541班的男同學(xué)信息，然后插入了一條不滿足限制條

26、件的記錄，再用SELECT語句檢索視圖和表。,29,7.5 使用視圖,【注意】如果視圖創(chuàng)建時定義了限制條件或者基本表的列允許空值或有默認(rèn)值，而插入的記錄不滿足該條件時，此時，仍然可以向表中插入記錄，只是用視圖檢索時不會顯示出新插入的記錄。如果不想讓這種情況發(fā)生，則可以使用WITH CHECK OPTION選項限制插入不符合視圖規(guī)則的視圖。這樣，在插入記錄時，如果記錄不符合限制條件就不能插入。,30,7.5 使用視圖,【例7-13】在

27、【例7-12】的基礎(chǔ)上添加WITH CHECK OPTION選項。,CREATE VIEW VIEW_STUDENT_05541_男_OnlyASSELECT * FROM T_STUDENTWHERE S_NUMBER like '05541%‘ AND SEX='男'WITH CHECK OPTIONGOInsert into VIEW_STUDENT_05541_男_OnlyV

28、alues (0554113,'張華','女', '1984-11-2', '群眾')GO,31,7.5 使用視圖,7.5.2 更新數(shù)據(jù)記錄1．使用SQL Server管理控制臺在視圖中更新記錄方法：打開要相應(yīng)視圖—— 直接修改記錄2．使用Transact-SQL語句通過視圖更新記錄【例7-14】創(chuàng)建一個基于表T_STUDENT的視圖VIEW_STUDENT_

29、05541_Modify，條件是顯示05541班的學(xué)生的學(xué)號、姓名和性別，然后通過該視圖修改表T_STUDENT中的記錄。,32,7.5 使用視圖,CREATE VIEW VIEW_STUDENT_05541_Modify(S_NUMBER, S_NAME, SEX)ASSELECT S_NUMBER, S_NAME, SEX FROM T_STUDENTWHERE S_NUMBER like '05541

30、%'GOUPDATE VIEW_STUDENT_05541_ModifySET S_NAME='王曉麗'WHERE S_NUMBER='0554112',33,7.5 使用視圖,7.5.3 刪除數(shù)據(jù)記錄1．使用SQL Server管理控制臺在視圖中刪除記錄方法：打開要相應(yīng)視圖—— 直接刪除記錄2．使用Transact-SQL語句通過視圖刪除記錄可以直接利用Transact

31、-SQL語言的DELETE語句刪除視圖中的記錄。但應(yīng)該注意，如果有刪除條件，則WHERE條件中使用到的字段必須是在視圖中定義過的字段。,34,7.5 使用視圖,【例7-15】利用視圖VIEW_STUDENT_05541，先插入一條記錄，然后刪除此條記錄。,Insert into VIEW_STUDENT_05541Values ('0554120','張磊','男')GODE

32、LETE FROM VIEW_STUDENT_05541WHERE S_NUMBER='0554120‘GO,本章首頁,35,7.6.1 使用SQL Server管理控制臺刪除視圖方法：打開要相應(yīng)視圖——刪除【注意】在確認(rèn)刪除之前，應(yīng)該查看視圖的依賴關(guān)系窗口，查看是否有數(shù)據(jù)庫對象依賴于將被刪除的視圖。如果存在這樣的對象，那么首先確定是否還有必要保留該對象，如果不必繼續(xù)保存，可以直接刪除掉該視圖，否則只能放棄刪除。,

33、7.6 刪除視圖,36,7.6.2 使用Transact-SQL語句刪除視圖可以使用Transact-SQL語句中的DROP VIEW命令刪除視圖。語法形式：DROP VIEW {view_name} [,…n]可以使用該命令同時刪除多個視圖，只需在要刪除的各視圖名稱之間用逗號隔開即可。,7.6 刪除視圖,37,【例7-16】刪除視圖VIEW_STUDENT_05541_Modify和視圖VIEW_STUDENT_055

34、41_男。,DROP VIEW VIEW_STUDENT_05541_Modify, VIEW_STUDENT_05541_男,7.6 刪除視圖,本章首頁,38,7.7 思考與練習(xí),1、視圖和數(shù)據(jù)表之間的主要區(qū)別是什么？2、使用視圖的優(yōu)點有哪些？3、如何通過SQL Server管理控制臺和Transact-SQL語句對視圖進(jìn)行創(chuàng)建、修改和刪除？4、使用哪些存儲過程可以查看視圖的信息？5、創(chuàng)建一個包含B04511班