第03章 sql語言5

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

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

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

4、圖WITH CHECK OPTION的視圖基于多個基表的視圖基于視圖的視圖帶表達式的視圖分組視圖一類不易擴充的視圖,3.6.1 定義視圖,(1)行列子集視圖 從單個基本表導(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視圖： 表示對視圖進行增、刪、改操作時，不得破壞視圖定義中的謂詞條件(即子查詢中的條件表達式)。,3.6.1 定義視圖,

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

7、CHECK OPTION視圖（續(xù)） 對IS_Stu視圖的更新操作：修改、刪除操作： DBMS自動加上Sdept= ‘IS’的條件。插入操作： DBMS會自動檢查Sdept屬性值是否為‘IS’ ，如果不是，則拒絕該插入操作.,3.6.1 定義視圖,(3)建立視圖---基于多個基表的視圖 [例3] 建立信息系選修了1號課程的學(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)基于視圖的視圖： 視圖可建立在一個或多個已定義的視圖上。 [例4] 建立信息系選修了1號課程且成績在90分以上的學(xué)生視圖。 CREATE VIEW IS_Stu2 AS SELECT Sno，Sname，Grade

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

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

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

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

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

15、P VIEW ；功能： 從數(shù)據(jù)字典中刪除指定的視圖定義。 [例8]刪除視圖IS_S1。 DROP VIEW IS_S1；,3.6.1 定義視圖,,說明：視圖或基表刪除后，由該視圖或基表定義的其他視圖定義仍然存在，但已不能使用，必須使用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 查詢視圖,思考該語句的執(zhí)行過程 ？,從用戶角度：查詢視圖與查詢基本表相同,3.6.2 查詢視圖,從用戶角度：查詢視圖與查詢基本表相同DBMS實現(xiàn)視圖查詢的方法方法1-----實體化視圖有效性檢查：檢查所查詢的視圖是否存在

17、執(zhí)行視圖定義：將視圖臨時實體化，生成臨時表查詢臨時表刪除臨時表,[例9] 在信息系學(xué)生視圖中找出年齡<20歲的學(xué)生。 SELECT Sno，Sage FROM IS_Stu WHERE Sage<20；采用實體化視圖執(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實現(xiàn)視圖查詢的方法方法2-----視圖消解法（View Resolution）有效性檢查：檢查查詢表、視圖是否存在。視圖定義中的子查詢與用戶的查詢相結(jié)合：

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

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

21、= '1'；,3.6.2 查詢視圖,思考兩種方法的執(zhí)行過程 ？,[例11]在S_G視圖中查詢平均成績>=90分的學(xué)生學(xué)號和平均成績。 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í)行過程 ？,[例11]在S_G視圖中查詢平均成績>=90分的學(xué)生學(xué)號和平均成績。錯誤的轉(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會限制這類查詢。,1.定義視圖,復(fù)習(xí),CREATE VIEW IS_StuAS

24、SELECT * FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION；,復(fù)習(xí),2.查詢視圖 (1)實體化視圖 (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.用戶角度：更新視圖與更新基本表相同通過視圖實現(xiàn)插入、刪除和修改數(shù)據(jù)。由于視圖是虛表，因此對視圖的更新最終要轉(zhuǎn)換為對基本表的更新。 2.視圖更新方法視圖實體化法（View Materialization）視圖消解法（View Resolution）,3.6.3 更新視圖,3.帶WITH CHECK OPTION子句視圖的更新 DBMS在更新視圖時會進行檢查，防止用戶通過視圖

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

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

28、’，‘趙新’，20) 檢查插入元組的sdept屬性值是否符合視圖IS_S的要求,3.6.3 更新視圖,SQL Server拒絕插入！,[例14] 刪除視圖IS_S中學(xué)號為0215129的記錄。 DELETE FROM IS_Student WHERE Sno= ‘0215129' 轉(zhuǎn)換為對基本表的更新： 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í)行如下更新語句

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

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

32、定義的視圖不允許更新(7)若視圖定義有嵌套查詢，并且內(nèi)層查詢的FROM子句中涉及的表也是導(dǎo)出該視圖的基本表，則此視圖不允許更新。,實際系統(tǒng)對視圖更新的限制,[例] 將SC中成績在平均成績之上的元組定義成一個視圖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.視圖能夠簡化用戶的操作,當(dāng)視圖中數(shù)據(jù)不是直接來自基本表時，定義視

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

35、) “垂直”地分成兩個基本表： SX(Sno，Sname，Sage，…) SY(Sno，Ssex，Sdept ，…),通過建立一個視圖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.視圖能夠簡化用戶的操作,2.視圖使用戶能以多種角度看待同一數(shù)據(jù),3.視圖對在一定程度上提高了數(shù)據(jù)庫的邏輯獨立性,使用戶的外模式保持不變，從而對原Student表的查詢程序不必修改,對不同用戶定義不同視圖，使每個用戶只能看到他有權(quán)看到的數(shù)據(jù)；通過WITH CHECK OPTION對關(guān)鍵數(shù)據(jù)定義操作限制,4.能夠?qū)?shù)據(jù)提供安全保護,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.視圖能夠簡化用戶的操作,2.視圖使用戶能以多種角度看待同一數(shù)據(jù),3.視圖對在一定程度上提高了數(shù)據(jù)庫的邏輯獨立性,4.能夠?qū)?shù)據(jù)提供安全保護,5.適當(dāng)?shù)睦靡晥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的視圖使用視圖的