第03章 sql語(yǔ)言5

1、數(shù)據(jù)庫(kù)系統(tǒng)概論An Introduction to Database System第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL（5）,第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL,3.1 SQL概述3.2 學(xué)生-課程數(shù)據(jù)庫(kù)3.3 數(shù)據(jù)定義3.4 查詢3.5 數(shù)據(jù)更新3.6 視圖,3.6 視圖,視圖的特點(diǎn)提供給用戶以多種角度觀察數(shù)據(jù)庫(kù)中數(shù)據(jù)的重要機(jī)制。虛表，是從一個(gè)或幾個(gè)基本表(或視圖)導(dǎo)出的表。只存放視圖的定義，不會(huì)出現(xiàn)數(shù)據(jù)冗余。基

2、表的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變.,3.6 視圖,基于視圖的操作 查詢 刪除 更新(受限) 定義基于該視圖的新視圖,3.6 視圖,3.6.1 定義視圖3.6.2 查詢視圖3.6.3 更新視圖3.6.4 視圖的作用,1. 建立視圖語(yǔ)句格式： CREATE VIEW [( [,]…)] AS [WITH CHECK OPTION]；功能：DBMS

3、存放視圖定義，不執(zhí)行其中的子查詢查詢視圖時(shí)，按其定義從基本表中將數(shù)據(jù)查出.,3.6.1 定義視圖,3.6.1 定義視圖,組成視圖的屬性列：或全部省略或全部指定省略: 由子查詢中SELECT目標(biāo)列中的諸字段組成以下情況必須明確指定視圖的所有列名(1)某個(gè)目標(biāo)列是集函數(shù)或列表達(dá)式(2)多表連接時(shí)選出了同名列作為視圖的字段(3)需在視圖中為某列啟用新的更合適的名字,1. 建立視圖（續(xù)）說(shuō)明：,常見(jiàn)的視圖形式行列子集視

4、圖WITH CHECK OPTION的視圖基于多個(gè)基表的視圖基于視圖的視圖帶表達(dá)式的視圖分組視圖一類不易擴(kuò)充的視圖,3.6.1 定義視圖,(1)行列子集視圖 從單個(gè)基本表導(dǎo)出的視圖，只是去掉了基本表的某些行和列，但保留碼。,3.6.1 定義視圖,[例1] 建立信息系學(xué)生的視圖。 CREATE VIEW IS_Stu AS

5、 SELECT Sno，Sname，Sage FROM Student WHERE Sdept= 'IS',*,(2)WITH CHECK OPTION視圖： 表示對(duì)視圖進(jìn)行增、刪、改操作時(shí)，不得破壞視圖定義中的謂詞條件(即子查詢中的條件表達(dá)式)。,3.6.1 定義視圖,

6、[例2]建立信息系學(xué)生的視圖，并要求透過(guò)該視圖進(jìn)行的更新操作只涉及信息系學(xué)生。 CREATE VIEW IS_Stu AS SELECT * FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION；,(2)WITH

7、CHECK OPTION視圖（續(xù)） 對(duì)IS_Stu視圖的更新操作：修改、刪除操作： DBMS自動(dòng)加上Sdept= ‘IS’的條件。插入操作： DBMS會(huì)自動(dòng)檢查Sdept屬性值是否為‘IS’ ，如果不是，則拒絕該插入操作.,3.6.1 定義視圖,(3)建立視圖---基于多個(gè)基表的視圖 [例3] 建立信息系選修了1號(hào)課程的學(xué)生視圖。 CREATE VIEW

8、IS_Stu1(Sno，Sname，Grade) AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Student.Sno=SC.Sno AND Sdept= 'IS'

9、 AND Cno= '1'；,3.6.1 定義視圖,(4)基于視圖的視圖： 視圖可建立在一個(gè)或多個(gè)已定義的視圖上。 [例4] 建立信息系選修了1號(hào)課程且成績(jī)?cè)?0分以上的學(xué)生視圖。 CREATE VIEW IS_Stu2 AS SELECT Sno，Sname，Grade

10、 FROM IS_Stu1 WHERE Grade>=90；,3.6.1 定義視圖,(5)帶表達(dá)式的視圖： 必須明確定義組成視圖的各個(gè)屬性列名。 [例5] 定義一個(gè)反映學(xué)生出生年份的視圖。 CREATE VIEW BT_S AS SELECT Sno，Sname，2011-Sage

11、 FROM Student；,3.6.1 定義視圖,(Sno，Sname，Sbirth),3.6.1 定義視圖,說(shuō)明： 由于視圖中的數(shù)據(jù)并不實(shí)際存儲(chǔ)，可以在視圖中設(shè)置一些派生屬性列(虛擬列) 。 帶表達(dá)式的視圖必須明確定義組成視圖的各個(gè)屬性列名,(6)分組視圖 帶有集函數(shù)和GROUP BY子句查詢的視圖。 [例6] 將系部名稱及該系學(xué)生平均年齡定義為一個(gè)視圖。 CREAT

12、E VIEW avg_age(Sdept, avg_age) AS SELECT Sdept, AVG(sage) FROM student GROUP BY Sdept,3.6.1 定義視圖,(7)一類不易擴(kuò)充的視圖： 以 SELECT * 方式創(chuàng)建的視圖，其

13、可擴(kuò)充性差，應(yīng)盡可能避免。 [例7] 將學(xué)生表中所有女生記錄定義為一個(gè)視圖。 CREATE VIEW F_Stu1(學(xué)號(hào),姓名,性別,年齡,系部) AS SELECT * FROM Student WHERE Ssex='女'；,3.6.1 定義視圖,如果修改該表結(jié)構(gòu)？,(7)一類不易擴(kuò)充的視圖（續(xù)）：

14、解決方法： SELECT子句中盡量不 * 。 CREATE VIEW F_Stu2 (學(xué)號(hào),姓名,性別,年齡,系部) AS SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student WHERE Ssex=‘女’；,3.6.1 定義視圖,2.刪除視圖語(yǔ)句格式： DRO

15、P VIEW ；功能： 從數(shù)據(jù)字典中刪除指定的視圖定義。 [例8]刪除視圖IS_S1。 DROP VIEW IS_S1；,3.6.1 定義視圖,,說(shuō)明：視圖或基表刪除后，由該視圖或基表定義的其他視圖定義仍然存在，但已不能使用，必須使用DROP VIEW顯式刪除。,3.6 視圖,3.6.1 定義視圖3.6.2 查詢視圖3.6.3 更新視圖3.6.4 視圖的作用,[例9] 在信息系學(xué)

16、生視圖中找出年齡<20歲的學(xué)生。 SELECT Sno，Sage FROM IS_Stu WHERE Sage<20；,3.6.2 查詢視圖,思考該語(yǔ)句的執(zhí)行過(guò)程 ？,從用戶角度：查詢視圖與查詢基本表相同,3.6.2 查詢視圖,從用戶角度：查詢視圖與查詢基本表相同DBMS實(shí)現(xiàn)視圖查詢的方法方法1-----實(shí)體化視圖有效性檢查：檢查所查詢的視圖是否存在

17、執(zhí)行視圖定義：將視圖臨時(shí)實(shí)體化，生成臨時(shí)表查詢臨時(shí)表刪除臨時(shí)表,[例9] 在信息系學(xué)生視圖中找出年齡<20歲的學(xué)生。 SELECT Sno，Sage FROM IS_Stu WHERE Sage<20；采用實(shí)體化視圖執(zhí)行 (1)先執(zhí)行視圖定義 CREATE VIEW IS_Stu AS SELECT

18、Sno，Sname，Sage FROM Student WHERE Sdept= ‘IS‘； (2)再執(zhí)行在視圖上的查詢,3.6.2 查詢視圖,3.6.2 查詢視圖,從用戶角度：查詢視圖與查詢基本表相同DBMS實(shí)現(xiàn)視圖查詢的方法方法2-----視圖消解法（View Resolution）有效性檢查：檢查查詢表、視圖是否存在。視圖定義中的子查詢與用戶的查詢相結(jié)合：

19、轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢執(zhí)行修正后的查詢,[例9] 在信息系學(xué)生的視圖中找出年齡<20歲的學(xué)生。 SELECT Sno，Sage FROM IS_Stu WHERE Sage<20；采用視圖消解法，轉(zhuǎn)換后的查詢語(yǔ)句為： SELECT Sno，Sage FROM Student

20、 WHERE Sdept= 'IS' AND Sage<20；,3.6.2 查詢視圖,[例10] 查詢信息系選修了1號(hào)課程的學(xué)生。 SELECT Sno，Sname FROM IS_Stu，SC WHERE IS_Stu.Sno =SC.Sno AND SC.Cno

21、= '1'；,3.6.2 查詢視圖,思考兩種方法的執(zhí)行過(guò)程 ？,[例11]在S_G視圖中查詢平均成績(jī)>=90分的學(xué)生學(xué)號(hào)和平均成績(jī)。 SELECT * FROM S_G WHERE Gavg>=90；,3.6.2 查詢視圖,S_G 視圖定義如下： CREATE VIEW S_G (Sno，Gavg) AS S

22、ELECT Sno，AVG(Grade) FROM SC GROUP BY Sno,思考兩種方法的執(zhí)行過(guò)程 ？,[例11]在S_G視圖中查詢平均成績(jī)>=90分的學(xué)生學(xué)號(hào)和平均成績(jī)。錯(cuò)誤的轉(zhuǎn)換： SELECT Sno，AVG(Grade) FROM SC WHERE AVG(Grade)>=90

23、 GROUP BY Sno；正確的轉(zhuǎn)換： SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90；,3.6.2 查詢視圖,視圖消解法的局限性： 有些情況下，視圖消解法不能生成正確查詢，采用視圖消解法的DBMS會(huì)限制這類查詢。,1.定義視圖,復(fù)習(xí),CREATE VIEW IS_StuAS

24、SELECT * FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION；,復(fù)習(xí),2.查詢視圖 (1)實(shí)體化視圖 (2)視圖消解法,SELECT Sno，Sage FROM IS_StuWHERE Sage<20,3.6 視圖,3.6.1 定義視圖3.6.2 查詢視圖3.6.3 更新視圖3.6.4 視圖

25、的作用,3.6.3 更新視圖,1.用戶角度：更新視圖與更新基本表相同通過(guò)視圖實(shí)現(xiàn)插入、刪除和修改數(shù)據(jù)。由于視圖是虛表，因此對(duì)視圖的更新最終要轉(zhuǎn)換為對(duì)基本表的更新。 2.視圖更新方法視圖實(shí)體化法（View Materialization）視圖消解法（View Resolution）,3.6.3 更新視圖,3.帶WITH CHECK OPTION子句視圖的更新 DBMS在更新視圖時(shí)會(huì)進(jìn)行檢查，防止用戶通過(guò)視圖

26、對(duì)不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)進(jìn)行更新。,[例12] 將視圖IS_Stu中學(xué)號(hào)95002的姓名改為劉辰。 UPDATE IS_Stu SET Sname= ‘劉辰’ WHERE Sno= ‘0215122' 轉(zhuǎn)換后的語(yǔ)句： UPDATE Student SET

27、Sname= ‘劉辰’ WHERE Sno= ‘0215122' AND Sdept= 'IS',3.6.3 更新視圖,[例13] 向信息系學(xué)生視圖IS_S中插入一個(gè)新的學(xué)生記錄 （0215129，趙新，20歲） INSERT INTO IS_Stu VALUES(‘0215129

28、’，‘趙新’，20) 檢查插入元組的sdept屬性值是否符合視圖IS_S的要求,3.6.3 更新視圖,SQL Server拒絕插入！,[例14] 刪除視圖IS_S中學(xué)號(hào)為0215129的記錄。 DELETE FROM IS_Student WHERE Sno= ‘0215129' 轉(zhuǎn)換為對(duì)基本表的更新： DELETE FROM Student

29、 WHERE Sno= ‘0215129' AND Sdept= 'IS',3.6.3 更新視圖,3.6.3 更新視圖,4.更新視圖的限制,例：CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；,執(zhí)行如下更新語(yǔ)句

30、： UPDATE S_G SET Gavg=90 WHERE Sno= ‘0215121’；,3.6.3 更新視圖,4.更新視圖的限制 一些視圖是不可更新的，因?yàn)閷?duì)這些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新(對(duì)兩類方法均如此),視圖的可更新性：理論上可更新：理論上不可更新：,允許更新不允許更新,不允許更新,,實(shí)際系統(tǒng)對(duì)視圖更新的限制,

31、允許對(duì)行列子集視圖進(jìn)行更新對(duì)其他類型視圖的更新不同系統(tǒng)有不同限制(1) 若視圖由多個(gè)基表導(dǎo)出，則此視圖不允許更新(2) 若視圖列來(lái)自字段表達(dá)式或常數(shù)，則不允許執(zhí)行INSERT和UPDATE操作，可執(zhí)行DELETE操作。(3) 若視圖列來(lái)自集函數(shù)，則此視圖不允許更新。,實(shí)際系統(tǒng)對(duì)視圖更新的限制,(4)若視圖定義含有GROUP BY子句，則不允許更新(5)若視圖定義含有DISTINCT短語(yǔ)，則不允許更新(6)一個(gè)不允許更新視圖上

32、定義的視圖不允許更新(7)若視圖定義有嵌套查詢，并且內(nèi)層查詢的FROM子句中涉及的表也是導(dǎo)出該視圖的基本表，則此視圖不允許更新。,實(shí)際系統(tǒng)對(duì)視圖更新的限制,[例] 將SC中成績(jī)?cè)谄骄煽?jī)之上的元組定義成一個(gè)視圖GOOD_SC: CREATE VIEW GOOD_SC AS SELECT Sno，Cno，Grade FROM SC WH

33、ERE Grade > (SELECT AVG(Grade) FROM SC) 導(dǎo)出視圖GOOD_SC的基表與內(nèi)層查詢中涉及的表相同。,SQL Server允許更新！,3.6 視圖,3.6.1 定義視圖3.6.2 查詢視圖3.6.3 更新視圖3.6.4 視圖的作用,3.6.4 視圖作用,1.視圖能夠簡(jiǎn)化用戶的操作,當(dāng)視圖中數(shù)據(jù)不是直接來(lái)自基本表時(shí)，定義視

34、圖能夠簡(jiǎn)化用戶的操作。 基于多張表連接形成的視圖 基于復(fù)雜嵌套查詢的視圖 含導(dǎo)出屬性的視圖,3.6.4 視圖作用,1.視圖能夠簡(jiǎn)化用戶的操作,2.視圖使用戶能以多種角度看待同一數(shù)據(jù),視圖機(jī)制能使不同用戶以不同角度看待同一數(shù)據(jù)，適應(yīng)數(shù)據(jù)庫(kù)共享的需要,3.視圖在一定程度上提高了數(shù)據(jù)庫(kù)的邏輯獨(dú)立性,3.6.4 視圖作用,例：數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)發(fā)生改變 學(xué)生關(guān)系Student(Sno,Sname,Ssex,Sage,Sdept

35、) “垂直”地分成兩個(gè)基本表： SX(Sno，Sname，Sage，…) SY(Sno，Ssex，Sdept ，…),通過(guò)建立一個(gè)視圖Student： CREATE VIEW Student(Sno,Sname,Ssex,Sage,Sdept) AS SELECT SX.Sno, Sname, Ssex, Sage, Sdept FROM

36、 SX，SY WHERE SX.Sno=SY.Sno；,3.6.4 視圖作用,1.視圖能夠簡(jiǎn)化用戶的操作,2.視圖使用戶能以多種角度看待同一數(shù)據(jù),3.視圖對(duì)在一定程度上提高了數(shù)據(jù)庫(kù)的邏輯獨(dú)立性,使用戶的外模式保持不變，從而對(duì)原Student表的查詢程序不必修改,對(duì)不同用戶定義不同視圖，使每個(gè)用戶只能看到他有權(quán)看到的數(shù)據(jù)；通過(guò)WITH CHECK OPTION對(duì)關(guān)鍵數(shù)據(jù)定義操作限制,4.能夠?qū)?shù)據(jù)提供安全保護(hù),CREATE

37、VIEW IS_SC AS SELECT Student.Sno,Sname,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno and Cno= '1' AND datepart(hh,getDATE()) BETWEEN 9 AND 17 AND d

38、atepart(dw,getDATE()) BETWEEN 2 AND 6 WITH CHECK OPTION,分析：該視圖的功能,3.6.4 視圖作用,1.視圖能夠簡(jiǎn)化用戶的操作,2.視圖使用戶能以多種角度看待同一數(shù)據(jù),3.視圖對(duì)在一定程度上提高了數(shù)據(jù)庫(kù)的邏輯獨(dú)立性,4.能夠?qū)?shù)據(jù)提供安全保護(hù),5.適當(dāng)?shù)睦靡晥D可以更清楚的表達(dá)查詢,CREATE VIEW sum_xf(Sno,Sname,Sum_ccredit)

39、 AS SELECT Student.Sno,Sname,SUM(Ccredit) FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Grade>=60 GROUP BY Student.Sno,Sname,分析：該視圖的功能,小結(jié),SQL的視圖使用視圖的