第3章(3)DataBase

1、An Introduction to Database System中國人民大學信息學院計算機系數據庫系統概論數據庫系統概論An Introduction to Database System第三章第三章 關系數據庫標準語言關系數據庫標準語言SQL SQL (續(xù)續(xù)2）An Introduction to Database System第三章第三章 關系數據庫標準語言關系數據庫標準語言SQL3.1 SQL概述3.2 數據定義3.3 查詢3.4 數據更新3.5 視圖3.6 數據控制3.7 嵌入式SQL3.8 小結 An Introduction to Database System3.4 數數 據

2、據 更更 新新 3.4.1 插入數據3.4.2 修改數據3.4.3 刪除數據 An Introduction to Database System3.4.1 插入數據插入數據n兩種插入數據方式n 插入單個元組n 插入子查詢結果An Introduction to Database System1. 插入單個元組插入單個元組n語句格式INSERTINTO (，)VALUES ( ， )n功能 將新元組插入指定表中。An Introduction to Database System插入單個元組（續(xù)）插入單個元組（續(xù)）例1 將一個新學生記錄（學號：95020；姓名：陳冬；性別：男；所在系：IS；年

3、齡：18歲）插入到Student表中。 INSERT INTO Student VALUES (95020，陳冬，男，IS，18)；An Introduction to Database System插入單個元組（續(xù)）插入單個元組（續(xù)）例2 插入一條選課記錄( 95020，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 95020 ， 1 )； 新插入的記錄在Grade列上取空值An Introduction to Database System插入單個元組（續(xù)）插入單個元組（續(xù)）n INTO子句n指定要插入數據的表名及屬性列n屬性列的順序可與表定義中的順序不一致n

4、沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致n指定部分屬性列：插入的元組在其余屬性列上取空值n VALUES子句n 提供的值必須與INTO子句匹配值的個數值的類型An Introduction to Database System2. 插入子查詢結果插入子查詢結果n語句格式 INSERT INTO ( ， ) 子查詢；n功能 將子查詢結果插入指定表中An Introduction to Database System插入子查詢結果（續(xù)）插入子查詢結果（續(xù)）例3 對每一個系，求學生的平均年齡，并把結果存入數據庫。第一步：建表 CREATE TABLE Depta

5、ge (Sdept CHAR(15) /* 系名*/ Avgage SMALLINT)； /*學生平均年齡*/ An Introduction to Database System插入子查詢結果（續(xù)）插入子查詢結果（續(xù)）第二步：插入數據 INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；An Introduction to Database System插入子查詢結果（續(xù)）插入子查詢結果（續(xù)）nINTO子句(與插入單條元組類似)n指定要插入數據的表名及屬性列n屬性列的順序可與

6、表定義中的順序不一致n沒有指定屬性列：表示要插入的是一條完整的元組n指定部分屬性列：插入的元組在其余屬性列上取空值n 子查詢nSELECT子句目標列必須與INTO子句匹配n值的個數n值的類型An Introduction to Database System插入子查詢結果（續(xù)）插入子查詢結果（續(xù)）DBMS在執(zhí)行插入語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則n實體完整性n參照完整性n用戶定義的完整性n對于有NOT NULL約束的屬性列是否提供了非空值n 對于有UNIQUE約束的屬性列是否提供了非重復值n 對于有值域約束的屬性列所提供的屬性值是否在值域范圍內An Introduction

7、to Database System3.4 數數 據據 更更 新新 3.4.1 插入數據3.4.2 修改數據3.4.3 刪除數據 An Introduction to Database System3.4.2 修改數據修改數據n語句格式 UPDATE SET =，= WHERE ；n功能修改指定表中滿足WHERE子句條件的元組An Introduction to Database System修改數據（續(xù)）修改數據（續(xù)）n三種修改方式n修改某一個元組的值n修改多個元組的值n帶子查詢的修改語句An Introduction to Database System1. 修改某一個元組的值修改某一個元

8、組的值例4 將學生95001的年齡改為22歲。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ； An Introduction to Database System2. 修改多個元組的值修改多個元組的值例5 將所有學生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1；An Introduction to Database System修改多個元組的值修改多個元組的值(續(xù)續(xù))例6 將信息系所有學生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；An In

9、troduction to Database System3. 帶子查詢的修改語句帶子查詢的修改語句例7 將計算機科學系全體學生的成績置零。 UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；An Introduction to Database System修改數據（續(xù)）修改數據（續(xù)）nSET子句 指定修改方式 要修改的列 修改后取值nWHERE子句指定要修改的元組缺省表示要修改表中的所有元組An Introduction to Database System修改數據（

10、續(xù)）修改數據（續(xù)）DBMS在執(zhí)行修改語句時會檢查修改操作是否破壞表上已定義的完整性規(guī)則n實體完整性n主碼不允許修改n用戶定義的完整性n NOT NULL約束n UNIQUE約束n 值域約束An Introduction to Database System3.4 數數 據據 更更 新新 3.4.1 插入數據3.4.2 修改數據3.4.3 刪除數據 An Introduction to Database System3.4.3 刪除數據刪除數據 DELETE FROM WHERE ；功能w刪除指定表中滿足WHERE子句條件的元組WHERE子句w指定要刪除的元組w缺省表示要修改表中的所有元組An

11、Introduction to Database System刪除數據（續(xù)）刪除數據（續(xù)）n三種刪除方式n刪除某一個元組的值n刪除多個元組的值n帶子查詢的刪除語句An Introduction to Database System1. 刪除某一個元組的值刪除某一個元組的值例8 刪除學號為95019的學生記錄。 DELETE FROM Student WHERE Sno=95019；An Introduction to Database System2. 刪除多個元組的值刪除多個元組的值例9 刪除2號課程的所有選課記錄。 DELETE FROM SC； WHERE Cno=2；例10 刪除所有的

12、學生選課記錄。 DELETE FROM SC；An Introduction to Database System3. 帶子查詢的刪除語句帶子查詢的刪除語句例11 刪除計算機科學系所有學生的選課記錄。 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；An Introduction to Database System刪除數據刪除數據(續(xù)續(xù))DBMS在執(zhí)行插入語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則參照完整性不允許刪除級聯刪除An Introduction to Database

13、 System更新數據與數據一致性更新數據與數據一致性DBMS在執(zhí)行插入、刪除、更新語句時必須保證數據庫一致性l必須有事務的概念和原子性l完整性檢查和保證An Introduction to Database System第三章第三章 關系數據庫標準語言關系數據庫標準語言SQL3.1 SQL概述3.2 數據定義3.3 查詢3.4 數據更新3.5 視圖3.6 數據控制3.7 嵌入式SQL3.8 小結 An Introduction to Database System3.5 視視 圖圖視圖的特點n虛表，是從一個或幾個基本表（或視圖）導出的表n只存放視圖的定義，不會出現數據冗余n基表中的數據發(fā)生變

14、化，從視圖中查詢出的數據也隨之改變An Introduction to Database System3.5 視視 圖圖基于視圖的操作n 查詢n 刪除n 受限更新n定義基于該視圖的新視圖An Introduction to Database System3.5 視視 圖圖3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.5.4 視圖的作用An Introduction to Database System1. 建立視圖建立視圖n語句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；An Introduction to Database System

15、 建立視圖（續(xù)）建立視圖（續(xù)）DBMS執(zhí)行CREATE VIEW語句時只是把視圖的定義存入數據字典，并不執(zhí)行其中的SELECT語句。在對視圖查詢時，按視圖的定義從基本表中將數據查出。An Introduction to Database System組成視圖的屬性列名組成視圖的屬性列名全部省略或全部指定n省略: 由子查詢中SELECT目標列中的諸字段組成n明確指定視圖的所有列名:(1) 某個目標列是集函數或列表達式(2) 目標列為 *(3) 多表連接時選出了幾個同名列作為視圖的字段(4) 需要在視圖中為某個列啟用新的更合適的名字An Introduction to Database Syste

16、m行列子集視圖行列子集視圖 例1 建立信息系學生的視圖。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；從單個基本表導出只是去掉了基本表的某些行和某些列保留了碼An Introduction to Database System 建立視圖（續(xù)）建立視圖（續(xù)）nWITH CHECK OPTION透過視圖進行增刪改操作時，不得破壞視圖定義中的謂詞條件（即子查詢中的條件表達式）An Introduction to Database System WITH CHECK OPTION的視圖的視圖例

17、2 建立信息系學生的視圖，并要求透過該視圖進行的更新操作只涉及信息系學生。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION；An Introduction to Database System對對IS_Student視圖的更新操作視圖的更新操作n修改操作：DBMS自動加上Sdept= IS的條件n刪除操作：DBMS自動加上Sdept= IS的條件n插入操作：DBMS自動檢查Sdept屬性值是否為IS n如果不是，則拒絕該插入操作n如果沒有提供Sdept屬

18、性值，則自動定義Sdept為ISAn Introduction to Database System基于多個基表的視圖基于多個基表的視圖例4 建立信息系選修了1號課程的學生視圖。 CREATE VIEW IS_S1(Sno，Sname，Grade) AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Sdept= IS AND Student.Sno=SC.Sno AND SC.Cno= 1；An Introduction to Database System基于視圖的視圖基于視圖的視圖例5 建立信息系選修了1號課程且成績在90分以上

19、的學生的視圖。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；An Introduction to Database System帶表達式的視圖帶表達式的視圖例6 定義一個反映學生出生年份的視圖。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2000-Sage FROM Student設置一些派生屬性列, 也稱為虛擬列-Sbirth 帶表達式的視圖必須明確定義組成視圖的各個屬性列名An Introduction to Database

20、System 建立分組視圖建立分組視圖例7 將學生的學號及他的平均成績定義為一個視圖 假設SC表中“成績”列Grade為數字型 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；An Introduction to Database System 建立視圖（續(xù)）建立視圖（續(xù)）n一類不易擴充的視圖n以 SELECT * 方式創(chuàng)建的視圖可擴充性差，應盡可能避免An Introduction to Database System 建立視圖（續(xù)）建立視圖（續(xù)）例8將Student表中所有女生記錄定義為一個視圖

21、CREATE VIEW F_Student1(stdnum，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女；缺點：修改基表Student的結構后，Student表與F_Student1視圖的映象關系被破壞，導致該視圖不能正確工作。An Introduction to Database System 建立視圖（續(xù)）建立視圖（續(xù)） CREATE VIEW F_Student2 (stdnum，name，sex，age，dept) AS SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student WHE

22、RE Ssex=女；為基表Student增加屬性列不會破壞Student表與F_Student2視圖的映象關系。An Introduction to Database System常見的視圖形式常見的視圖形式n行列子集視圖nWITH CHECK OPTION的視圖n基于多個基表的視圖n基于視圖的視圖n帶表達式的視圖n分組視圖An Introduction to Database System2. 刪除視圖刪除視圖nDROP VIEW ；n該語句從數據字典中刪除指定的視圖定義n由該視圖導出的其他視圖定義仍在數據字典中，但已不能使用，必須顯式刪除n刪除基表時，由該基表導出的所有視圖定義都必須顯式刪

23、除An Introduction to Database System刪除視圖刪除視圖(續(xù)）續(xù)） 例9 刪除視圖IS_S1 DROP VIEW IS_S1； An Introduction to Database System3.5 視視 圖圖3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.5.4 視圖的作用An Introduction to Database System3.5.2 查詢視圖查詢視圖n從用戶角度：查詢視圖與查詢基本表相同nDBMS實現視圖查詢的方法n實體化視圖（View Materialization）n有效性檢查：檢查所查詢的視圖是否存在n執(zhí)行視圖定義，將

24、視圖臨時實體化，生成臨時表n查詢視圖轉換為查詢臨時表n查詢完畢刪除被實體化的視圖(臨時表)newAn Introduction to Database System查詢視圖（續(xù)）查詢視圖（續(xù)）n視圖消解法（View Resolution）n進行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數據字典中取出視圖的定義n把視圖定義中的子查詢與用戶的查詢結合起來，轉換成等價的對基本表的查詢n執(zhí)行修正后的查詢An Introduction to Database System查詢視圖（續(xù)）查詢視圖（續(xù)）例1 在信息系學生的視圖中找出年齡小于20歲的學生。 SELECT Sno，Sage FRO

25、M IS_Student WHERE Sage20；IS_Student視圖的定義 (視圖定義例1)： CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；An Introduction to Database System查詢視圖（續(xù)查詢視圖（續(xù)）n視圖實體化法n視圖消解法轉換后的查詢語句為：SELECT Sno，Sage FROM StudentWHERE Sdept= IS AND Sage=90；S_G視圖定義： CREATE VIEW S_G (Sno，Gavg) AS SELECT

26、 Sno，AVG(Grade)FROM SCGROUP BY Sno；An Introduction to Database System查詢轉換查詢轉換錯誤：SELECT Sno，AVG(Grade)FROM SCWHERE AVG(Grade)=90GROUP BY Sno；正確：SELECT Sno，AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)=90；An Introduction to Database System3.5 視視 圖圖3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.5.4 視圖的作用An Introduct

27、ion to Database System3.5.3 更新視圖更新視圖n用戶角度：更新視圖與更新基本表相同nDBMS實現視圖更新的方法n視圖實體化法（View Materialization）n視圖消解法（View Resolution）n指定WITH CHECK OPTION子句后 DBMS在更新視圖時會進行檢查，防止用戶通過視圖對不屬于視圖范圍內的基本表數據進行更新An Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）例1 將信息系學生視圖IS_Student中學號95002 的學生姓名改為“劉辰”。UPDATE IS_StudentSET Sn

28、ame= 劉辰WHERE Sno= 95002；轉換后的語句：UPDATE StudentSET Sname= 劉辰WHERE Sno= 95002 AND Sdept= IS；An Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）例2 向信息系學生視圖IS_S中插入一個新的學生記錄：95029，趙新，20歲INSERTINTO IS_StudentVALUES(95029，趙新，20)；轉換為對基本表的更新：INSERTINTO Student(Sno，Sname，Sage，Sdept)VALUES(95029，趙新，20，IS )；An Intro

29、duction to Database System更新視圖（續(xù)）更新視圖（續(xù)）例3 刪除視圖CS_S中學號為95029的記錄DELETEFROM IS_StudentWHERE Sno= 95029；轉換為對基本表的更新：DELETEFROM StudentWHERE Sno= 95029 AND Sdept= IS；An Introduction to Database System更新視圖的限制更新視圖的限制n一些視圖是不可更新的，因為對這些視圖的更新不能唯一地有意義地轉換成對相應基本表的更新(對兩類方法均如此)例：視圖S_G為不可更新視圖。 CREATE VIEW S_G (Sno，G

30、avg) AS SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；An Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）對于如下更新語句：UPDATE S_GSET Gavg=90WHERE Sno= 95001；無論實體化法還是消解法都無法將其轉換成對基本表SC的更新An Introduction to Database System視圖的可更新性視圖的可更新性理論上可更新 理論上不可更新允許更新 不允許更新不允許更新An Introduction to Database System實際系統對視圖更新的限制實際系統對

31、視圖更新的限制n允許對行列子集視圖進行更新n對其他類型視圖的更新不同系統有不同限制DB2對視圖更新的限制：(1) 若視圖是由兩個以上基本表導出的，則此視圖不允許更新。(2) 若視圖的字段來自字段表達式或常數，則不允許對此視圖執(zhí)行INSERT和UPDATE操作，但允許執(zhí)行DELETE操作。An Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）(3) 若視圖的字段來自集函數，則此視圖不允許更新。(4) 若視圖定義中含有GROUP BY子句，則此視圖不允許更新。(5) 若視圖定義中含有DISTINCT短語，則此視圖不允許更新。(6) 若視圖定義中有嵌套查詢，

32、并且內層查詢的FROM子句中涉及的表也是導出該視圖的基本表，則此視圖不允許更新。(7) 一個不允許更新的視圖上定義的視圖也不允許更新An Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）例：視圖GOOD_SC(修課成績在平均成績之上的元組) CREATE VIEW GOOD_SC AS SELECT Sno，Cno，Grade FROM SC WHERE Grade (SELECT AVG(Grade) FROM SC)；An Introduction to Database System3.5 視視 圖圖3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.5.4 視圖的作用An Introduction to Database System1. 視圖能夠視圖能夠簡化簡化用戶的操作用戶的操作當視圖中數據不是直接來自基本表時，定義視圖能夠簡化用戶的操作n基于多張表連接形成的視圖n基于復雜嵌套查詢的視圖n含導出屬性的視圖An Introduction to Database System2. 視圖使用戶能以視圖使用戶能以多種角度多種角度看待同一數據看待同一數據n視圖機制能使不同用戶以不同方式