數(shù)據(jù)庫(kù)系統(tǒng)概論課件：第3章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.3

1、中國(guó)人民大學(xué)信息學(xué)院 數(shù)據(jù)庫(kù)系統(tǒng)概論An Introduction to Database System第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL (續(xù)2）An Introduction to Database System第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1 SQL概述3.2 學(xué)生-課程數(shù)據(jù)庫(kù)3.3 數(shù)據(jù)定義3.4 數(shù)據(jù)查詢3.5 數(shù)據(jù)更新3.6 視圖3.7 小結(jié)An Introduction to Database System3.5 數(shù) 據(jù) 更 新 3.5.1 插入數(shù)據(jù)3.5.2 修改數(shù)據(jù)3.5.3 刪除數(shù)據(jù) An Introduction to Database System3.5.1 插入數(shù)

2、據(jù)兩種插入數(shù)據(jù)方式1. 插入元組2. 插入子查詢結(jié)果可以一次插入多個(gè)元組 An Introduction to Database System一、插入元組語(yǔ)句格式INSERTINTO (，)VALUES ( ， )功能將新元組插入指定表中An Introduction to Database System插入元組（續(xù)） INTO子句屬性列的順序可與表定義中的順序不一致沒有指定屬性列指定部分屬性列 VALUES子句 提供的值必須與INTO子句匹配值的個(gè)數(shù)值的類型An Introduction to Database System插入元組（續(xù)）例1 將一個(gè)新學(xué)生元組（學(xué)號(hào)：200215128；姓名

3、：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。 INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (200215128，陳冬，男，IS，18)；An Introduction to Database System插入元組（續(xù)）例2 將學(xué)生張成民的信息插入到Student表中。 INSERT INTO Student VALUES (200215126， 張成民， 男，18，CS); An Introduction to Database System插入元組（續(xù)）例3 插入一條選課記錄( 200215128，

4、1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 200215128 ， 1 )； RDBMS將在新插入記錄的Grade列上自動(dòng)地賦空值。 或者： INSERT INTO SC VALUES ( 200215128 ， 1 ，NULL)；An Introduction to Database System二、插入子查詢結(jié)果語(yǔ)句格式 INSERT INTO ( ， ) 子查詢；功能 將子查詢結(jié)果插入指定表中An Introduction to Database System插入子查詢結(jié)果（續(xù)） INTO子句(與插入元組類似) 子查詢SELECT子句目標(biāo)列必須與INTO子

5、句匹配值的個(gè)數(shù)值的類型An Introduction to Database System插入子查詢結(jié)果（續(xù)）例4 對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫(kù)。第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名*/ Avg_age SMALLINT)； /*學(xué)生平均年齡*/ An Introduction to Database System插入子查詢結(jié)果（續(xù)）第二步：插入數(shù)據(jù) INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY

6、Sdept；An Introduction to Database System插入子查詢結(jié)果（續(xù)）RDBMS在執(zhí)行插入語(yǔ)句時(shí)會(huì)檢查所插元組是否破壞表上已定義的完整性規(guī)則實(shí)體完整性參照完整性用戶定義的完整性NOT NULL約束UNIQUE約束值域約束An Introduction to Database System3.5 數(shù) 據(jù) 更 新 3.5.1 插入數(shù)據(jù)3.5.2 修改數(shù)據(jù)3.5.3 刪除數(shù)據(jù) An Introduction to Database System3.4.2 修改數(shù)據(jù)語(yǔ)句格式 UPDATE SET =，= WHERE ；功能修改指定表中滿足WHERE子句條件的元組An In

7、troduction to Database System修改數(shù)據(jù)（續(xù)）SET子句指定修改方式要修改的列修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組An Introduction to Database System修改數(shù)據(jù)（續(xù)）三種修改方式1. 修改某一個(gè)元組的值2. 修改多個(gè)元組的值3. 帶子查詢的修改語(yǔ)句An Introduction to Database System1. 修改某一個(gè)元組的值例5 將學(xué)生200215121的年齡改為22歲 UPDATE Student SET Sage=22 WHERE Sno= 200215121 ； An Introduct

8、ion to Database System2. 修改多個(gè)元組的值例6 將所有學(xué)生的年齡增加1歲 UPDATE Student SET Sage= Sage+1；An Introduction to Database System3. 帶子查詢的修改語(yǔ)句例7 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。 UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；An Introduction to Database System修改數(shù)據(jù)（續(xù)）RDBMS在執(zhí)行修改語(yǔ)句時(shí)會(huì)檢查修改操作是否破壞

9、表上已定義的完整性規(guī)則實(shí)體完整性主碼不允許修改用戶定義的完整性 NOT NULL約束 UNIQUE約束 值域約束An Introduction to Database System3.5 數(shù) 據(jù) 更 新 3.5.1 插入數(shù)據(jù)3.5.2 修改數(shù)據(jù)3.5.3 刪除數(shù)據(jù) An Introduction to Database System3.5.3 刪除數(shù)據(jù)語(yǔ)句格式 DELETE FROM WHERE ；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要?jiǎng)h除的元組缺省表示要?jiǎng)h除表中的全部元組，表的定義仍在字典中An Introduction to Database System刪除數(shù)據(jù)

10、（續(xù)）三種刪除方式1. 刪除某一個(gè)元組的值2. 刪除多個(gè)元組的值3. 帶子查詢的刪除語(yǔ)句An Introduction to Database System1. 刪除某一個(gè)元組的值例8 刪除學(xué)號(hào)為200215128的學(xué)生記錄。 DELETE FROM Student WHERE Sno= 200215128 ；An Introduction to Database System2. 刪除多個(gè)元組的值例9 刪除所有的學(xué)生選課記錄。 DELETE FROM SC；An Introduction to Database System3. 帶子查詢的刪除語(yǔ)句例10 刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。

11、 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；An Introduction to Database System第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1 SQL概述3.2 學(xué)生-課程數(shù)據(jù)庫(kù)3.3 數(shù)據(jù)定義3.4 數(shù)據(jù)查詢3.5 數(shù)據(jù)更新3.6 視圖3.7 小結(jié)An Introduction to Database System3.6 視 圖視圖的特點(diǎn)虛表，是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表只存放視圖的定義，不存放視圖對(duì)應(yīng)的數(shù)據(jù)基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變A

12、n Introduction to Database System3.6 視 圖基于視圖的操作 查詢 刪除 受限更新 定義基于該視圖的新視圖An Introduction to Database System3.6 視 圖3.6.1 定義視圖3.6.2 查詢視圖3.6.3 更新視圖3.6.4 視圖的作用An Introduction to Database System3.6.1 定義視圖建立視圖刪除視圖An Introduction to Database System一、建立視圖語(yǔ)句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；組成視圖的屬性列名：全部省

13、略或全部指定子查詢不允許含有ORDER BY子句和DISTINCT短語(yǔ)An Introduction to Database System 建立視圖（續(xù)）RDBMS執(zhí)行CREATE VIEW語(yǔ)句時(shí)只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語(yǔ)句。在對(duì)視圖查詢時(shí)，按視圖的定義從基本表中將數(shù)據(jù)查出。An Introduction to Database System建立視圖（續(xù)） 例1 建立信息系學(xué)生的視圖。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；An Introducti

14、on to Database System建立視圖（續(xù)）例2建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時(shí)仍需保證該視圖只有信息系的學(xué)生 。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION；An Introduction to Database System建立視圖（續(xù)）對(duì)IS_Student視圖的更新操作：修改操作：自動(dòng)加上Sdept= IS的條件刪除操作：自動(dòng)加上Sdept= IS的條件插入操作：自動(dòng)檢查Sdept屬性值是否為IS 如果不是，則拒絕

15、該插入操作如果沒有提供Sdept屬性值，則自動(dòng)定義Sdept為ISAn Introduction to Database System建立視圖（續(xù)）基于多個(gè)基表的視圖例3 建立信息系選修了1號(hào)課程的學(xué)生視圖。 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建立視圖（續(xù)）基于視圖的視圖例4 建立信息系選

16、修了1號(hào)課程且成績(jī)?cè)?0分以上的學(xué)生的視圖。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；An Introduction to Database System建立視圖（續(xù)）帶表達(dá)式的視圖例5 定義一個(gè)反映學(xué)生出生年份的視圖。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2000-Sage FROM Student；An Introduction to Database System建立視圖（續(xù)）分組視圖例6 將學(xué)生的學(xué)號(hào)及他的平均成績(jī)

17、定義為一個(gè)視圖 假設(shè)SC表中“成績(jī)”列Grade為數(shù)字型 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；An Introduction to Database System 建立視圖（續(xù)）不指定屬性列例7將Student表中所有女生記錄定義為一個(gè)視圖 CREATE VIEW F_Student(F_Sno，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女； 缺點(diǎn)： 修改基表Student的結(jié)構(gòu)后，Student表與F_Student視圖

18、的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。An Introduction to Database System二、刪除視圖語(yǔ)句的格式：DROP VIEW ；該語(yǔ)句從數(shù)據(jù)字典中刪除指定的視圖定義如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級(jí)聯(lián)刪除語(yǔ)句，把該視圖和由它導(dǎo)出的所有視圖一起刪除 刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用DROP VIEW語(yǔ)句刪除 An Introduction to Database System刪除視圖(續(xù)）例8 刪除視圖BT_S： DROP VIEW BT_S； 刪除視圖IS_S1：DROP VIEW IS_S1；拒絕執(zhí)行級(jí)聯(lián)刪除： DROP VI

19、EW IS_S1 CASCADE; An Introduction to Database System3.6 視 圖3.6.1 定義視圖3.6.2 查詢視圖3.6.3 更新視圖3.6.4 視圖的作用An Introduction to Database System3.6.2 查詢視圖用戶角度：查詢視圖與查詢基本表相同RDBMS實(shí)現(xiàn)視圖查詢的方法視圖消解法（View Resolution）進(jìn)行有效性檢查轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢執(zhí)行修正后的查詢An Introduction to Database System查詢視圖（續(xù)）例9 在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。 SELECT

20、Sno，Sage FROM IS_Student WHERE Sage20；IS_Student視圖的定義 (參見視圖定義例1)An Introduction to Database System查詢視圖（續(xù)）視圖消解轉(zhuǎn)換后的查詢語(yǔ)句為： SELECT 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；An Introduction to Database System查詢轉(zhuǎn)換

21、錯(cuò)誤：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.6 視 圖3.6.1 定義視圖3.6.2 查詢視圖3.6.3 更新視圖3.6.4 視圖的作用An Introduction to Database System更新視圖（續(xù)）例12 將信息系學(xué)生視圖IS_Student中學(xué)號(hào)200215122的學(xué)生姓名改為“劉辰”。UPDA

22、TE IS_StudentSET Sname= 劉辰WHERE Sno= 200215122 ；轉(zhuǎn)換后的語(yǔ)句：UPDATE StudentSET Sname= 劉辰WHERE Sno= 200215122 AND Sdept= IS；An Introduction to Database System更新視圖（續(xù)）例13 向信息系學(xué)生視圖IS_S中插入一個(gè)新的學(xué)生記錄：200215129，趙新，20歲INSERTINTO IS_StudentVALUES(95029，趙新，20)；轉(zhuǎn)換為對(duì)基本表的更新：INSERTINTO Student(Sno，Sname，Sage，Sdept)VALUES(200215129 ，趙新，20，IS )；