數(shù)據(jù)庫概論第三章課件

1、An Introduction to Database System數(shù)據(jù)庫系統(tǒng)概論An Introduction to Database System第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL (續(xù)2）An Introduction to Database System第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1 SQL概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖3.6 數(shù)據(jù)控制3.7 嵌入式SQL3.8 小結(jié) An Introduction to Database System3.4 數(shù) 據(jù) 更 新 3.4.1 插入數(shù)據(jù)3.4.2 修改數(shù)據(jù)3.4.3 刪除數(shù)據(jù) An Introduction

2、 to Database System3.4.1 插入數(shù)據(jù)三種插入數(shù)據(jù)方式 插入單個(gè)元組 插入子查詢結(jié)果 插入新表An Introduction to Database System1. 插入單個(gè)元組語句格式INSERTINTO (，)VALUES ( ， )功能 將新元組插入指定表中。An Introduction to Database System插入單個(gè)元組（續(xù)）例1 將一個(gè)新學(xué)生記錄（學(xué)號：201915128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。 INSERT INTO Student VALUES (201915128，陳冬，男，IS，18)

3、；An Introduction to Database System插入單個(gè)元組（續(xù)）例2 插入一條選課記錄( 201915128，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 201915128 ， 1 )； 新插入的記錄在Grade列上取空值A(chǔ)n Introduction to Database System插入單個(gè)元組（續(xù)） INTO子句指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致指定部分屬性列：插入的元組在其余屬性列上取空值 VALUES子句 提供的值必須與

4、INTO子句匹配值的個(gè)數(shù)值的類型An Introduction to Database System2. 插入子查詢結(jié)果語句格式 INSERT INTO ( ， ) 子查詢；功能 將子查詢結(jié)果插入指定表中An Introduction to Database System插入子查詢結(jié)果（續(xù)）例3 對每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。第一步：建表 CREATE TABLE Deptage (Sdept CHAR(15) /* 系名*/ Avgage SMALLINT)； /*學(xué)生平均年齡*/ An Introduction to Database System插入子查詢結(jié)果（續(xù)）第

5、二步：插入數(shù)據(jù) INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；An Introduction to Database System插入子查詢結(jié)果（續(xù)）INTO子句(與插入單條元組類似)指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒有指定屬性列：表示要插入的是一條完整的元組指定部分屬性列：插入的元組在其余屬性列上取空值 子查詢SELECT子句目標(biāo)列必須與INTO子句匹配值的個(gè)數(shù)值的類型An Introduction to Database System插

6、入子查詢結(jié)果（續(xù)）DBMS在執(zhí)行插入語句時(shí)會檢查所插元組是否破壞表上已定義的完整性規(guī)則實(shí)體完整性參照完整性用戶定義的完整性對于有NOT NULL約束的屬性列是否提供了非空值 對于有UNIQUE約束的屬性列是否提供了非重復(fù)值 對于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi)An Introduction to Database System3.向新表中插入數(shù)據(jù)使用select語句：格式Select into Where An Introduction to Database System3.4 數(shù) 據(jù) 更 新 3.4.1 插入數(shù)據(jù)3.4.2 修改數(shù)據(jù)3.4.3 刪除數(shù)據(jù) An Introdu

7、ction to Database System3.4.2 修改數(shù)據(jù)語句格式 UPDATE SET =，= WHERE ；功能修改指定表中滿足WHERE子句條件的元組An Introduction to Database System修改數(shù)據(jù)（續(xù)）三種修改方式修改某一個(gè)元組的值修改多個(gè)元組的值帶子查詢的修改語句An Introduction to Database System1. 修改某一個(gè)元組的值例4 將學(xué)生201915121的年齡改為22歲。 UPDATE Student SET Sage=22 WHERE Sno= 201915121 ； An Introduction to Data

8、base System2. 修改多個(gè)元組的值例5 將所有學(xué)生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1；An Introduction to Database System修改多個(gè)元組的值(續(xù))例6 將信息系所有學(xué)生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；An Introduction to Database System3. 帶子查詢的修改語句例7 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績置零。 UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FR

9、OM Student WHERE Student.Sno = SC.Sno)；An Introduction to Database System修改數(shù)據(jù)（續(xù)）SET子句 指定修改方式 要修改的列 修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組An Introduction to Database System修改數(shù)據(jù)（續(xù)）DBMS在執(zhí)行修改語句時(shí)會檢查修改操作是否破壞表上已定義的完整性規(guī)則實(shí)體完整性主碼不允許修改用戶定義的完整性 NOT NULL約束 UNIQUE約束 值域約束An Introduction to Database System3.4 數(shù) 據(jù) 更 新 3

10、.4.1 插入數(shù)據(jù)3.4.2 修改數(shù)據(jù)3.4.3 刪除數(shù)據(jù) An Introduction to Database System3.4.3 刪除數(shù)據(jù) DELETE FROM WHERE ；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要?jiǎng)h除的元組缺省表示要修改表中的所有元組An Introduction to Database System刪除數(shù)據(jù)（續(xù)）三種刪除方式刪除某一個(gè)元組的值刪除多個(gè)元組的值帶子查詢的刪除語句An Introduction to Database System1. 刪除某一個(gè)元組的值例8 刪除學(xué)號為201915128的學(xué)生記錄。 DELETE FROM

11、Student WHERE Sno= 2019151218；An Introduction to Database System2. 刪除多個(gè)元組的值例9 刪除2號課程的所有選課記錄。 DELETE FROM SC； WHERE Cno=2；例10 刪除所有的學(xué)生選課記錄。 DELETE FROM SC；An Introduction to Database System3. 帶子查詢的刪除語句例11 刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；

12、An Introduction to Database System刪除數(shù)據(jù)(續(xù))DBMS在執(zhí)行插入語句時(shí)會檢查所插元組是否破壞表上已定義的完整性規(guī)則參照完整性不允許刪除級聯(lián)刪除An Introduction to Database System更新數(shù)據(jù)與數(shù)據(jù)一致性DBMS在執(zhí)行插入、刪除、更新語句時(shí)必須保證數(shù)據(jù)庫一致性必須有事務(wù)的概念和原子性完整性檢查和保證An Introduction to Database System第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1 SQL概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖3.6 數(shù)據(jù)控制3.7 嵌入式SQL3.8 小結(jié) An Intr

13、oduction to Database System3.5 視 圖視圖的特點(diǎn)虛表，是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表只存放視圖的定義，不會出現(xiàn)數(shù)據(jù)冗余基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變An Introduction to Database System3.5 視 圖基于視圖的操作 查詢 刪除 受限更新 定義基于該視圖的新視圖An Introduction to Database System3.5 視 圖3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.5.4 視圖的作用An Introduction to Database System1. 建立視圖語句

14、格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；An Introduction to Database System 建立視圖（續(xù)）DBMS執(zhí)行CREATE VIEW語句時(shí)只是把視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。在對視圖查詢時(shí)，按視圖的定義從基本表中將數(shù)據(jù)查出。An Introduction to Database System組成視圖的屬性列名全部省略或全部指定省略: 由子查詢中SELECT目標(biāo)列中的諸字段組成明確指定視圖的所有列名:(1) 某個(gè)目標(biāo)列是集函數(shù)或列表達(dá)式(2) 多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段(3) 需要在視圖中

15、為某個(gè)列啟用新的更合適的名字An Introduction to Database System行列子集視圖 例1 建立信息系學(xué)生的視圖。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；從單個(gè)基本表導(dǎo)出,只是去掉了基本表的某些行和某些列,保留了碼An Introduction to Database System 建立視圖（續(xù)）WITH CHECK OPTION透過視圖進(jìn)行增刪改操作時(shí)，不得破壞視圖定義中的謂詞條件（即子查詢中的條件表達(dá)式）An Introduction to Datab

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

17、拒絕該插入操作如果沒有提供Sdept屬性值，則自動(dòng)定義Sdept為ISAn Introduction to Database System基于多個(gè)基表的視圖例4 建立信息系選修了1號課程的學(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基于視圖的視圖例5 建立信息系選修了1號課程且成績在90

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

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

20、um，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女；缺點(diǎn)：修改基表Student的結(jié)構(gòu)后，Student表與F_Student1視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。An Introduction to Database System 建立視圖（續(xù)） CREATE VIEW F_Student2 (stdnum，name，sex，age，dept) AS SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student WHERE Ssex=女；為基表Student增加屬性列不會破壞Stude

21、nt表與F_Student2視圖的映象關(guān)系。An Introduction to Database System（總結(jié)）常見的視圖形式行列子集視圖WITH CHECK OPTION的視圖基于多個(gè)基表的視圖基于視圖的視圖帶表達(dá)式的視圖分組視圖An Introduction to Database System2. 刪除視圖DROP VIEW ；該語句從數(shù)據(jù)字典中刪除指定的視圖定義由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中，但已不能使用，必須顯式刪除刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯式刪除An Introduction to Database System刪除視圖(續(xù)） 例9 刪除視圖I

22、S_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 查詢視圖從用戶角度：查詢視圖與查詢基本表相同DBMS實(shí)現(xiàn)視圖查詢的兩種方法實(shí)體化視圖（View Materialization）有效性檢查：檢查所查詢的視圖是否存在執(zhí)行視圖定義，將視圖臨時(shí)實(shí)體化，生成臨時(shí)表查詢視圖轉(zhuǎn)換為查詢臨時(shí)表查詢完畢刪除被實(shí)體化的視圖(臨時(shí)表)newAn Introduction to D

23、atabase System查詢視圖（續(xù)）視圖消解法（View Resolution）進(jìn)行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數(shù)據(jù)字典中取出視圖的定義把視圖定義中的子查詢與用戶的查詢結(jié)合起來，轉(zhuǎn)換成等價(jià)的對基本表的查詢執(zhí)行修正后的查詢An Introduction to Database System查詢視圖（續(xù)）例1 在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。 SELECT Sno，Sage FROM IS_Student WHERE Sage20；IS_Student視圖的定義 (視圖定義例1)： CREATE VIEW IS_Student AS SELECT S

24、no，Sname，Sage FROM Student WHERE Sdept= IS；An Introduction to Database System查詢視圖（續(xù)）視圖實(shí)體化法（略）視圖消解法轉(zhuǎn)換后的查詢語句為：SELECT Sno，Sage FROM StudentWHERE 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)換錯(cuò)誤：SELECT Sno，AVG(

25、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 Introduction to Database System3.5.3 更新視圖（更新內(nèi)容）用戶角度：更新視圖與更新基本表相同對視圖的更新的最終結(jié)果是對基本表的更新！DBMS實(shí)現(xiàn)視圖更新的方法視圖實(shí)體化法（Vi

26、ew Materialization）視圖消解法（View Resolution）指定WITH CHECK OPTION子句后， DBMS在更新視圖時(shí)會進(jìn)行檢查，防止用戶通過視圖對不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)進(jìn)行更新An Introduction to Database System更新視圖（續(xù)）例1 將信息系學(xué)生視圖IS_Student中學(xué)號95002 的學(xué)生姓名改為“劉辰”。UPDATE IS_StudentSET Sname= 劉辰WHERE Sno= 2019151212；轉(zhuǎn)換后的語句：UPDATE StudentSET Sname= 劉辰WHERE Sno= 95002 AND Sd

27、ept= IS；An Introduction to Database System更新視圖（續(xù)）例2 向信息系學(xué)生視圖IS_S中插入一個(gè)新的學(xué)生記錄：201915129，趙新，20歲INSERTINTO IS_StudentVALUES(201915129，趙新，20)；轉(zhuǎn)換為對基本表的更新：INSERTINTO Student(Sno，Sname，Sage，Sdept)VALUES(95029，趙新，20，IS )；An Introduction to Database System更新視圖（續(xù)）例3 刪除視圖CS_S中學(xué)號為201915129的記錄DELETEFROM IS_Studen

28、tWHERE Sno= 201915129 ；轉(zhuǎn)換為對基本表的更新：DELETEFROM StudentWHERE Sno= 201915129 AND Sdept= IS；An Introduction to Database System更新視圖的限制一些視圖是不可更新的，因?yàn)閷@些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對相應(yīng)基本表的更新(對兩類方法均如此)例：視圖S_G為不可更新視圖。 CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；An Introduction to Database Syste

29、m更新視圖（續(xù)）對于如下更新語句：UPDATE S_GSET Gavg=90WHERE Sno= 2019151291；無論實(shí)體化法還是消解法都無法將其轉(zhuǎn)換成對基本表SC的更新An Introduction to Database System實(shí)際系統(tǒng)對視圖更新的限制允許對行列子集視圖進(jìn)行更新對其他類型視圖的更新不同系統(tǒng)有不同限制DB2對視圖更新的限制：(1) 若視圖是由兩個(gè)以上基本表導(dǎo)出的，則此視圖不允許更新。(2) 若視圖的字段來自字段表達(dá)式或常數(shù)，則不允許對此視圖執(zhí)行INSERT和UPDATE操作，但允許執(zhí)行DELETE操作。An Introduction to Database Sys

30、tem更新視圖（續(xù)）(3) 若視圖的字段來自集函數(shù)，則此視圖不允許更新。(4) 若視圖定義中含有GROUP BY子句，則此視圖不允許更新。(5) 若視圖定義中含有DISTINCT短語，則此視圖不允許更新。(6) 若視圖定義中有嵌套查詢，并且內(nèi)層查詢的FROM子句中涉及的表也是導(dǎo)出該視圖的基本表，則此視圖不允許更新。(7) 一個(gè)不允許更新的視圖上定義的視圖也不允許更新。An Introduction to Database System更新視圖（續(xù)）例：視圖GOOD_SC(修課成績在平均成績之上的元組) CREATE VIEW GOOD_SC AS SELECT Sno，Cno，Grade FROM SC WHERE Grade (SELECT AVG(Grade) FROM SC)；這個(gè)視圖就不能更新。（p128）更新視圖更新視圖結(jié)構(gòu)：alter viewAn Introduction to Database System3.5 視 圖3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.5.4 視圖的作用An Introduction to Database System1. 視圖能夠簡化用戶的操