第三章 標準SQL語言3-3

1、1第三章第三章 關系數(shù)據(jù)庫標準語言關系數(shù)據(jù)庫標準語言SQL3.1 SQL概述概述3.2 學生學生-課程數(shù)據(jù)庫課程數(shù)據(jù)庫3.3 數(shù)據(jù)定義數(shù)據(jù)定義3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢3.5 數(shù)據(jù)更新數(shù)據(jù)更新3.6 視圖視圖3.7 小結小結23.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.5.1 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù)3.5.2 3.5.2 修改數(shù)據(jù)修改數(shù)據(jù)3.5.3 3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) 33.5.1 插入數(shù)據(jù)插入數(shù)據(jù)v兩種插入數(shù)據(jù)方式兩種插入數(shù)據(jù)方式1. 1. 插入元組插入元組2. 2. 插入子查詢結果插入子查詢結果可以一次插入多個元組可以一次插入多個元組 4一、插入元組一、插入元組v語句格式語句格式IN

2、SERTINTO ( (1， 2 )VALUES ( 1 ， 2 ) )v功能功能n將新元組插入指定表中將新元組插入指定表中5插入元組（續(xù)）插入元組（續(xù)）v INTO子句子句n屬性列的順序可與表定義中的順序不一致屬性列的順序可與表定義中的順序不一致n沒有指定屬性列沒有指定屬性列n指定部分屬性列指定部分屬性列v VALUES子句子句n 提供的值必須與提供的值必須與INTOINTO子句匹配子句匹配值的個數(shù)值的個數(shù)值的類型值的類型6插入元組（續(xù)）插入元組（續(xù)）例例1 1 將一個新學生元組（學號：將一個新學生元組（學號：200215128200215128；姓名：陳冬；性別：男；所在系：姓名：陳冬；性

3、別：男；所在系：ISIS；年齡：；年齡：1818歲）插入到歲）插入到StudentStudent表中。表中。 INSERTINTO Student (Sno，Sname，Ssex，Sdept，Sage)VALUES (200215128，陳冬陳冬，男男，IS，18)；7插入元組（續(xù)）插入元組（續(xù)）例例2 將學生張成民的信息插入到將學生張成民的信息插入到Student表中。表中。 INSERTINTO StudentVALUES (200215126，張成民張成民, 男男,18，CS); 8插入元組（續(xù)）插入元組（續(xù)）例3 插入一條選課記錄( 200215128，1 )。 INSERTINTO

4、SC(Sno，Cno)VALUES ( 200215128 ， 1 )；INSERTINTO SCVALUES ( 200215128 ， 1 ，NULL)；9二、插入子查詢結果二、插入子查詢結果v語句格式語句格式 INSERT INTO ( ( 1 ， 2 ) ) 子查詢子查詢；v功能功能 將子查詢結果插入指定表中將子查詢結果插入指定表中10插入子查詢結果（續(xù)）插入子查詢結果（續(xù)）v INTO子句子句(與插入元組類似與插入元組類似)v 子查詢子查詢nSELECT子句目標列必須與子句目標列必須與INTO子句匹配子句匹配值的個數(shù)值的個數(shù)值的類型值的類型11插入子查詢結果（續(xù)）插入子查詢結果（續(xù)）

5、例4 對每一個系，求學生的平均年齡，并把結果對每一個系，求學生的平均年齡，并把結果存入數(shù)據(jù)庫。存入數(shù)據(jù)庫。 第一步：建表第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名系名*/ Avg_age SMALLINT)； /*學生平均學生平均年齡年齡*/12插入子查詢結果（續(xù)）插入子查詢結果（續(xù)）第二步：插入數(shù)據(jù)第二步：插入數(shù)據(jù) INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；13插入子查詢結果（續(xù)）插入子查詢結果（續(xù)）RD

6、BMSRDBMS在執(zhí)行插入語句時會檢查所插元組是在執(zhí)行插入語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則否破壞表上已定義的完整性規(guī)則 實體完整性實體完整性 參照完整性參照完整性 用戶定義的完整性用戶定義的完整性NOT NULLNOT NULL約束約束UNIQUEUNIQUE約束約束值域約束值域約束143.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù)3.5.2 修改數(shù)據(jù)修改數(shù)據(jù)3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) 153.4.2 修改數(shù)據(jù)修改數(shù)據(jù)v語句格式語句格式 UPDATE SET =，= WHERE ；v功能功能n修改指定表中滿足修改指定表中滿足WHEREWHERE子句條件的元組子

7、句條件的元組16修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）nSET子句子句指定修改方式指定修改方式要修改的列要修改的列修改后取值修改后取值nWHERE子句子句指定要修改的元組指定要修改的元組缺省表示要修改表中的所有元組缺省表示要修改表中的所有元組17修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）v三種修改方式三種修改方式1. 修改某一個元組的值修改某一個元組的值2. 修改多個元組的值修改多個元組的值3. 帶子查詢的修改語句帶子查詢的修改語句181. 修改某一個元組的值修改某一個元組的值例5 將學生200215121的年齡改為22歲 UPDATE StudentSET Sage=22WHERE Sno= 200215121 ；

8、 192. 修改多個元組的值修改多個元組的值例6 將所有學生的年齡增加1歲 UPDATE StudentSET Sage= Sage+1；203. 帶子查詢的修改語句帶子查詢的修改語句例7 將計算機科學系全體學生的成績置零。 UPDATE SCSET Grade=0WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；21修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）RDBMS在執(zhí)行修改語句時會檢查修改操作在執(zhí)行修改語句時會檢查修改操作是否破壞表上已定義的完整性規(guī)則是否破壞表上已定義的完整性規(guī)則n實體完整性實體完整性n主碼不允許修改主碼

9、不允許修改n用戶定義的完整性用戶定義的完整性 NOT NULL約束約束 UNIQUE約束約束 值域約束值域約束223.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù)3.5.2 修改數(shù)據(jù)修改數(shù)據(jù)3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) 233.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù)v語句格式語句格式 DELETE FROM WHERE ；v功能功能n刪除指定表中滿足刪除指定表中滿足WHEREWHERE子句條件的元組子句條件的元組vWHEREWHERE子句子句n指定要刪除的元組指定要刪除的元組n缺省表示要刪除表中的全部元組，表的定義仍在缺省表示要刪除表中的全部元組，表的定義仍在字典中字典中24刪除數(shù)據(jù)（續(xù)）刪除數(shù)

10、據(jù)（續(xù)）v三種刪除方式三種刪除方式1. 刪除某一個元組的值刪除某一個元組的值2. 刪除多個元組的值刪除多個元組的值3. 帶子查詢的刪除語句帶子查詢的刪除語句251. 刪除某一個元組的值刪除某一個元組的值例例8 刪除學號為刪除學號為200215128的學生記錄。的學生記錄。 DELETEFROM StudentWHERE Sno= 200215128 ；262. 刪除多個元組的值刪除多個元組的值例例9 刪除所有的學生選課記錄。刪除所有的學生選課記錄。 DELETEFROM SC；273. 帶子查詢的刪除語句帶子查詢的刪除語句 例例10 10 刪除計算機科學系所有學生的選課記錄。刪除計算機科學系所

11、有學生的選課記錄。 DELETEFROM SCWHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；28第三章第三章 關系數(shù)據(jù)庫標準語言關系數(shù)據(jù)庫標準語言SQL3.1 SQL概述概述3.2 學生學生-課程數(shù)據(jù)庫課程數(shù)據(jù)庫3.3 數(shù)據(jù)定義數(shù)據(jù)定義3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢3.5 數(shù)據(jù)更新數(shù)據(jù)更新3.6 視圖視圖3.7 小結小結293.6 視視 圖圖視圖的特點視圖的特點v 虛表，是從一個或幾個基本表（或視圖）導出的表虛表，是從一個或幾個基本表（或視圖）導出的表v 只存放視圖的定義，不存放視圖對應的數(shù)據(jù)只存放視圖的定義，不存放視圖

12、對應的數(shù)據(jù)v 基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變之改變303.6 視視 圖圖基于視圖的操作基于視圖的操作v 查詢查詢v 刪除刪除v 受限更新受限更新v 定義基于該視圖的新視圖定義基于該視圖的新視圖313.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用323.6.1 定義視圖定義視圖v建立視圖v刪除視圖33一、建立視圖一、建立視圖v 語句格式語句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；v 組成視圖的屬性列名：

13、全部省略或全部指定組成視圖的屬性列名：全部省略或全部指定 P119v 子查詢子查詢不允許含有不允許含有ORDER BY子句和子句和DISTINCT短語短語34 建立視圖（續(xù)）建立視圖（續(xù)）vRDBMS執(zhí)行執(zhí)行CREATE VIEW語句時只是把視圖語句時只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語語句。句。v在對視圖查詢時，按視圖的定義從基本表中將數(shù)在對視圖查詢時，按視圖的定義從基本表中將數(shù)據(jù)查出。據(jù)查出。35建立視圖（續(xù)）建立視圖（續(xù)） 例1 建立信息系學生的視圖。 CREATE VIEW IS_Student AS SELECT Sno，Sname

14、，Sage FROM Student WHERE Sdept= IS；36建立視圖（續(xù)）建立視圖（續(xù)）例例2建立信息系學生的視圖，并要求進行修改和插建立信息系學生的視圖，并要求進行修改和插入操作時仍需保證該視圖只有信息系的學生入操作時仍需保證該視圖只有信息系的學生 。 CREATE VIEW IS_StudentAS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION；37建立視圖（續(xù)）建立視圖（續(xù)）對對IS_Student視圖的更新操作：視圖的更新操作：v修改操作：自動加上修改操作：自動加上Sdept= I

15、S的條件的條件v刪除操作：自動加上刪除操作：自動加上Sdept= IS的條件的條件v插入操作：自動檢查插入操作：自動檢查Sdept屬性值是否為屬性值是否為IS 如果不是，則拒絕該插入操作如果不是，則拒絕該插入操作 如果沒有提供如果沒有提供Sdept屬性值，則自動定義屬性值，則自動定義Sdept為為IS38建立視圖（續(xù)）建立視圖（續(xù)）v基于多個基表的視圖例3 建立信息系選修了1號課程的學生視圖。 CREATE VIEW IS_S1(Sno，Sname，Grade) AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Sdept= IS

16、AND Student.Sno=SC.Sno AND SC.Cno= 1；39建立視圖（續(xù)）建立視圖（續(xù)）v基于視圖的視圖基于視圖的視圖例例4 建立信息系選修了建立信息系選修了1號課程且成績在號課程且成績在90分以分以上的學生的視圖。上的學生的視圖。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；40建立視圖（續(xù)）建立視圖（續(xù)）v帶表達式的視圖帶表達式的視圖例例5 定義一個反映學生出生年份的視圖。定義一個反映學生出生年份的視圖。 CREATE VIEW BT_S(Sno，Sname，Sbirth)AS

17、 SELECT Sno，Sname，2013-Sage FROM Student；41建立視圖（續(xù)）建立視圖（續(xù)）v分組視圖分組視圖例例6 將學生的學號及他的平均成績定義為一個將學生的學號及他的平均成績定義為一個視圖視圖,假設假設SC表中表中“成績成績”列列Grade為數(shù)字型為數(shù)字型 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；42 建立視圖（續(xù)）建立視圖（續(xù)）v 不指定屬性列例7將Student表中所有女生記錄定義為一個視圖 CREATE VIEW F_Student(F_Sno，name，se

18、x，age，dept) AS SELECT * FROM Student WHERE Ssex=女女；缺點： 修改基表Student的結構后，Student表與F_Student視圖的映象關系被破壞，導致該視圖不能正確工作。43二、刪除視圖二、刪除視圖v語句的格式：語句的格式：DROP VIEW ； 該語句從數(shù)據(jù)字典中刪除指定的視圖定義該語句從數(shù)據(jù)字典中刪除指定的視圖定義 如果該視圖上還導出了其他視圖，使用如果該視圖上還導出了其他視圖，使用CASCADE級聯(lián)刪除語句，把該視圖和由它導級聯(lián)刪除語句，把該視圖和由它導出的所有視圖一起刪除出的所有視圖一起刪除 刪除基表時，由該基表導出的所有視圖定義都

19、刪除基表時，由該基表導出的所有視圖定義都必須顯式地使用必須顯式地使用DROP VIEW語句刪除語句刪除 44刪除視圖刪除視圖(續(xù)）續(xù)）例例8 刪除視圖刪除視圖BT_S： DROP VIEW BT_S； 刪除視圖刪除視圖IS_S1：DROP VIEW IS_S1；拒絕執(zhí)行拒絕執(zhí)行級聯(lián)刪除：級聯(lián)刪除： DROP VIEW IS_S1 CASCADE; 453.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用463.6.2 查詢視圖查詢視圖v用戶角度：查詢視圖與查詢基本表相同用戶角度：查詢視圖與查詢基本表相同vRDBM

20、S實現(xiàn)視圖查詢的方法實現(xiàn)視圖查詢的方法 視圖消解法（視圖消解法（View Resolution） 進行有效性檢查進行有效性檢查 轉換成等價的對基本表的查詢轉換成等價的對基本表的查詢 執(zhí)行執(zhí)行修正修正后的查詢后的查詢47查詢視圖（續(xù)）查詢視圖（續(xù)）例例9 在信息系學生的視圖中找出年齡小于在信息系學生的視圖中找出年齡小于20歲的歲的學生。學生。 IS_Student視圖的定義視圖的定義 (參見視圖參見視圖定義例定義例1)SELECT Sno，SageFROM IS_StudentWHERE Sage20；48查詢視圖（續(xù)）查詢視圖（續(xù)）視圖消解轉換后的查詢語句為：視圖消解轉換后的查詢語句為： SE

21、LECT Sno，Sage FROM Student WHERE Sdept= IS AND Sage=90； S_G視圖的子查詢定義：視圖的子查詢定義： CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；52查詢轉換查詢轉換錯誤錯誤：SELECT Sno，AVG(Grade)FROM SCWHERE AVG(Grade)=90GROUP BY Sno；正確：正確：SELECT Sno，AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)=90；533.6 視視

22、圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用54更新視圖（續(xù)）更新視圖（續(xù)）例例12 將信息系學生視圖將信息系學生視圖IS_Student中學號中學號200215122的學生姓名改為的學生姓名改為“劉辰劉辰”。UPDATE IS_StudentSET Sname= 劉辰劉辰WHERE Sno= 200215122 ；轉換后的語句：轉換后的語句：UPDATE StudentSET Sname= 劉辰劉辰WHERE Sno= 200215122 AND Sdept= IS；55更新視圖（續(xù)）更新視圖（續(xù)）例例13 向信息系學生視圖向信息系學生視圖IS_S中插入一個新的學生記錄：中插入一個新的學生記錄：200215129，趙新，趙新，20歲歲INSERTINTO IS_StudentVALUES(95029，趙新趙新，20)；轉換為對基本表的更新：轉換為對基本表的更新：INSERTINTO Student(Sno，Sname，Sage，Sdept)VALUES(200215129 ，趙新趙新，20，IS )；56更新視圖（續(xù)）更新視圖（續(xù)）例例14刪除信息系學生視圖刪除信息系學生