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

1、2024/3/21,1,第7章 視圖的創(chuàng)建和使用,2,本章學(xué)習(xí)目標(biāo),了解視圖和數(shù)據(jù)表之間的主要區(qū)別了解視圖的優(yōu)點(diǎn)掌握創(chuàng)建、修改和刪除視圖的方法掌握查看視圖信息的方法掌握通過(guò)視圖操作數(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ù)庫(kù)對(duì)象，是從一個(gè)或者多個(gè)數(shù)據(jù)表或視圖中導(dǎo)出的虛表，其結(jié)構(gòu)和數(shù)據(jù)是建立在對(duì)表的查詢(xún)基礎(chǔ)上的。視圖被定義后便存儲(chǔ)在數(shù)據(jù)庫(kù)中，通過(guò)視圖看到的數(shù)據(jù)只是存放在基表中的數(shù)據(jù)。當(dāng)對(duì)通過(guò)視圖看到的數(shù)據(jù)進(jìn)行修改時(shí)，相應(yīng)的基表的數(shù)據(jù)也會(huì)發(fā)生變化；若基表的數(shù)據(jù)發(fā)生變化，這種變化也會(huì)自動(dòng)地反映到視圖中。視圖可以是一個(gè)數(shù)據(jù)表的一部分，也可以是多個(gè)數(shù)據(jù)表的聯(lián)合。,5,7.1 概述,7.1.2 視圖的優(yōu)點(diǎn)視圖可以屏蔽數(shù)據(jù)的復(fù)雜性

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

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

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

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

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

8、T_SCORE和T_COURSE，選擇的字段為：T_STUDENT表中的S_NUMBER和S_NAME字段、T_COURSE表中的C_NAME字段及T_SCORE表中的SCORE字段，要求查詢(xún)的數(shù)據(jù)為05541班學(xué)生的考試成績(jī)。,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語(yǔ)句創(chuàng)建新視圖VIEW_SCORE_NotPass。對(duì)數(shù)據(jù)表和對(duì)字段的選擇同上，要求只顯示各班不及格的學(xué)生的學(xué)號(hào)、姓名、課程名及成績(jī)。并加密視圖的定義。,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語(yǔ)句創(chuàng)建新視圖VIEW_SCORE_SumAndAverage。要求計(jì)算各個(gè)班級(jí)各

12、門(mén)課程的總分及平均分。,7.2 創(chuàng)建視圖,16,CREATE VIEW VIEW_SCORE_SumAndAverageAS SELECT SUBSTRING(T_STUDENT.S_NUMBER,1, LEN(T_STUDENT.S_NUMBER)-2) AS 班級(jí), T_COURSE.C_NAME AS 課程名稱(chēng), 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)建視圖,本章首頁(yè),17,7.3 查看視圖信息,7.3.1 使用SQL Server管理控制臺(tái)查看視圖信息方法：右擊相應(yīng)視圖——打開(kāi)視圖,18,7.3.2 使用系統(tǒng)存儲(chǔ)過(guò)程查看視圖信息sp_help：顯示數(shù)據(jù)庫(kù)對(duì)象的特征信息，sp_depends：顯示數(shù)據(jù)庫(kù)對(duì)象所依賴(lài)的對(duì)象，它們可以在任何數(shù)據(jù)庫(kù)對(duì)象上運(yùn)行。sp_helptext：顯示視圖、觸發(fā)器或存儲(chǔ)過(guò)程等在系統(tǒng)表中的定義。語(yǔ)法形式：sp_

15、help 數(shù)據(jù)庫(kù)對(duì)象名稱(chēng)sp_helptext 視圖（觸發(fā)器、存儲(chǔ)過(guò)程）sp_depends 數(shù)據(jù)庫(kù)對(duì)象名稱(chēng),7.3 查看視圖信息,19,【例7-5】使用系統(tǒng)存儲(chǔ)過(guò)程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視圖所依賴(lài)的對(duì)象。,sp_helptext VIEW_SCORE_NotPass,sp_depends VIEW_SCORE,7.3 查看視圖信息,本章首頁(yè),21,7.4 修改和重命名視圖,7.4.1 修改視圖1．使用SQL Server管理控制臺(tái)修改視圖方法：右擊要修改的視圖——修改,22,2．使用Transact-SQL

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

18、，那么只有在ALTER VIEW中也包含這些選項(xiàng)時(shí)，這些選項(xiàng)才有效。,7.4 修改和重命名視圖,23,【例7-9】修改視圖VIEW_SCORE_NotPass，在該視圖中增加一個(gè)新的條件，要求只顯示高等數(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管理控制臺(tái)重命名視圖方法：右擊相應(yīng)視圖——重命名2．使用系統(tǒng)存儲(chǔ)過(guò)程重命名視圖可以使用系統(tǒng)存儲(chǔ)過(guò)程sp_rename來(lái)修改視圖的名稱(chēng)。語(yǔ)法形式：sp_rename old_name,new_name【例7-10】使用系統(tǒng)存儲(chǔ)過(guò)程sp_rename來(lái)完成把視圖“VIEW_SCORE_NotPass”重命名為“VIEW_SCO

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

22、的數(shù)據(jù)必須符合對(duì)這些列的約束，例如為空性、約束及DEFAULT定義等。,26,7.5 使用視圖,7.5.1 插入數(shù)據(jù)記錄1．使用SQL Server管理控制臺(tái)在視圖中插入記錄方法：打開(kāi)要相應(yīng)視圖—— 錄入新記錄2．使用Transact-SQL語(yǔ)句通過(guò)視圖插入記錄,27,7.5 使用視圖,【例7-11】創(chuàng)建一個(gè)基于表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)建一個(gè)包含限制條件的視圖VIEW_STUDENT_05541_男，限制條件為顯示05541班的男同學(xué)信息，然后插入了一條不滿足限制條

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

27、【例7-12】的基礎(chǔ)上添加WITH CHECK OPTION選項(xiàng)。,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管理控制臺(tái)在視圖中更新記錄方法：打開(kāi)要相應(yīng)視圖—— 直接修改記錄2．使用Transact-SQL語(yǔ)句通過(guò)視圖更新記錄【例7-14】創(chuàng)建一個(gè)基于表T_STUDENT的視圖VIEW_STUDENT_

29、05541_Modify，條件是顯示05541班的學(xué)生的學(xué)號(hào)、姓名和性別，然后通過(guò)該視圖修改表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管理控制臺(tái)在視圖中刪除記錄方法：打開(kāi)要相應(yīng)視圖—— 直接刪除記錄2．使用Transact-SQL語(yǔ)句通過(guò)視圖刪除記錄可以直接利用Transact

31、-SQL語(yǔ)言的DELETE語(yǔ)句刪除視圖中的記錄。但應(yīng)該注意，如果有刪除條件，則WHERE條件中使用到的字段必須是在視圖中定義過(guò)的字段。,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,本章首頁(yè),35,7.6.1 使用SQL Server管理控制臺(tái)刪除視圖方法：打開(kāi)要相應(yīng)視圖——?jiǎng)h除【注意】在確認(rèn)刪除之前，應(yīng)該查看視圖的依賴(lài)關(guān)系窗口，查看是否有數(shù)據(jù)庫(kù)對(duì)象依賴(lài)于將被刪除的視圖。如果存在這樣的對(duì)象，那么首先確定是否還有必要保留該對(duì)象，如果不必繼續(xù)保存，可以直接刪除掉該視圖，否則只能放棄刪除。,

33、7.6 刪除視圖,36,7.6.2 使用Transact-SQL語(yǔ)句刪除視圖可以使用Transact-SQL語(yǔ)句中的DROP VIEW命令刪除視圖。語(yǔ)法形式：DROP VIEW {view_name} [,…n]可以使用該命令同時(shí)刪除多個(gè)視圖，只需在要?jiǎng)h除的各視圖名稱(chēng)之間用逗號(hào)隔開(kāi)即可。,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 刪除視圖,本章首頁(yè),38,7.7 思考與練習(xí),1、視圖和數(shù)據(jù)表之間的主要區(qū)別是什么？2、使用視圖的優(yōu)點(diǎn)有哪些？3、如何通過(guò)SQL Server管理控制臺(tái)和Transact-SQL語(yǔ)句對(duì)視圖進(jìn)行創(chuàng)建、修改和刪除？4、使用哪些存儲(chǔ)過(guò)程可以查看視圖的信息？5、創(chuàng)建一個(gè)包含B04511班