數(shù)據(jù)庫(kù)系統(tǒng)概論課件之第3章SQL(數(shù)據(jù)更新)

數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL2023/7/291數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言（SQL）3.1SQL概述3.2數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖3.6數(shù)據(jù)控制3.7嵌入式SQL小結(jié)2023/7/292數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.4數(shù)據(jù)更新3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)2023/7/293數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.4.1插入數(shù)據(jù)兩種插入數(shù)據(jù)方式插入單個(gè)元組插入子查詢結(jié)果2023/7/294數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)1.插入單個(gè)元組語(yǔ)句格式INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]VALUES(<常量1>[，<常量2>]…)功能

將新元組插入指定表中。2023/7/295數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)插入單個(gè)元組（續(xù)）[例1]將一個(gè)新學(xué)生記錄（學(xué)號(hào)：200215128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。

INSERTINTOStudentVALUES('200215128'，'陳冬'，'男'，'IS'，18)；2023/7/296數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)插入單個(gè)元組（續(xù)）[例2]插入一條選課記錄(200215128'，'1')。

INSERTINTOSC(Sno，Cno)VALUES('200215128'，'1')；新插入的記錄在Grade列上取空值2023/7/297數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)插入單個(gè)元組（續(xù)）

INTO子句指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒(méi)有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致指定部分屬性列：插入的元組在其余屬性列上取空值

VALUES子句

提供的值必須與INTO子句匹配值的個(gè)數(shù)值的類型2023/7/298數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)2.插入子查詢結(jié)果語(yǔ)句格式

INSERTINTO

<表名>[(<屬性列1>[，<屬性列2>…)]

子查詢；功能將子查詢結(jié)果插入指定表中2023/7/299數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)插入子查詢結(jié)果（續(xù)）[例3]對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫(kù)。第一步：建表

CREATETABLEDeptage(SdeptCHAR(15)/*系名*/AvgageSMALLINT)；/*學(xué)生平均年齡*/2023/7/2910數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)插入子查詢結(jié)果（續(xù)）第二步：插入數(shù)據(jù)

INSERTINTODeptage(Sdept，Avgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；2023/7/2911數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)插入子查詢結(jié)果（續(xù)）INTO子句(與插入單條元組類似)指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒(méi)有指定屬性列：表示要插入的是一條完整的元組指定部分屬性列：插入的元組在其余屬性列上取空值子查詢SELECT子句目標(biāo)列必須與INTO子句匹配值的個(gè)數(shù)值的類型2023/7/2912數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)插入子查詢結(jié)果（續(xù)）DBMS在執(zhí)行插入語(yǔ)句時(shí)會(huì)檢查所插元組是否破壞表上已定義的完整性規(guī)則實(shí)體完整性參照完整性用戶定義的完整性對(duì)于有NOTNULL約束的屬性列是否提供了非空值對(duì)于有UNIQUE約束的屬性列是否提供了非重復(fù)值對(duì)于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi)2023/7/2913數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.4數(shù)據(jù)更新3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)2023/7/2914數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.4.2修改數(shù)據(jù)語(yǔ)句格式

UPDATE<表名>SET<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[WHERE<條件>]；功能修改指定表中滿足WHERE子句條件的元組2023/7/2915數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)修改數(shù)據(jù)（續(xù)）三種修改方式修改某一個(gè)元組的值修改多個(gè)元組的值帶子查詢的修改語(yǔ)句2023/7/2916數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)1.修改某一個(gè)元組的值[例4]將學(xué)生200215121的年齡改為22歲。

UPDATEStudentSETSage=22WHERESno=

'200215121'；2023/7/2917數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)2.修改多個(gè)元組的值[例5]將所有學(xué)生的年齡增加1歲。

UPDATEStudentSETSage=Sage+1；Gz（sno，dept，Wage）比如所有基本工資漲15%2023/7/2918數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)修改多個(gè)元組的值(續(xù))[例6]將信息系所有學(xué)生的年齡增加1歲。

UPDATEStudentSETSage=Sage+1WHERESdept='IS'；2023/7/2919數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.帶子查詢的修改語(yǔ)句[例7]將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；2023/7/2920數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)修改數(shù)據(jù)（續(xù)）DBMS在執(zhí)行修改語(yǔ)句時(shí)會(huì)檢查修改操作是否破壞表上已定義的完整性規(guī)則實(shí)體完整性主碼不允許修改用戶定義的完整性

NOTNULL約束

UNIQUE約束值域約束2023/7/2921數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.4數(shù)據(jù)更新3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)

2023/7/2922數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.4.3刪除數(shù)據(jù)

DELETE

FROM<表名>[WHERE<條件>]；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要?jiǎng)h除的元組缺省表示要修改表中的所有元組2023/7/2923數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)刪除數(shù)據(jù)（續(xù)）三種刪除方式刪除某一個(gè)元組的值刪除多個(gè)元組的值帶子查詢的刪除語(yǔ)句2023/7/2924數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)1.刪除某一個(gè)元組的值[例8]刪除學(xué)號(hào)為200215128的學(xué)生記錄。

DELETEFROMStudentWHERESno='200215128'；2023/7/2925數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)2.刪除多個(gè)元組的值[例9]刪除2號(hào)課程的所有選課記錄。

DELETEFROMSC；

WHERECno='2'；[例10]刪除所有的學(xué)生選課記錄。

DELETEFROMSC；2023/7/2926數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.帶子查詢的刪除語(yǔ)句[例11]刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。DELETEFROMSCWHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；2023/7/2927數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)刪除數(shù)據(jù)(續(xù))DBMS在執(zhí)行插入語(yǔ)句時(shí)會(huì)檢查所插元組是否破壞表上已定義的完整性規(guī)則參照完整性不允許刪除級(jí)聯(lián)刪除2023/7/2928數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)更新數(shù)據(jù)與數(shù)據(jù)一致性DBMS在執(zhí)行插入、刪除、更新語(yǔ)句時(shí)必須保證數(shù)據(jù)庫(kù)一致性必須有事務(wù)的概念和原子性完整性檢查和保證2023/7/2929數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖3.6數(shù)據(jù)控制3.7嵌入式SQL小結(jié)2023/7/2930數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.5視圖視圖的特點(diǎn)虛表，是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表只存放視圖的定義，不會(huì)出現(xiàn)數(shù)據(jù)冗余基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變2023/7/2931數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.5視圖基于視圖的操作查詢刪除受限更新定義基于該視圖的新視圖2023/7/2932數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用2023/7/2933數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)1.建立視圖語(yǔ)句格式

CREATEVIEW

<視圖名>[(<列名>[，<列名>]…)]AS<子查詢>[WITHCHECKOPTION]；2023/7/2934數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)行列子集視圖

[例1]建立信息系學(xué)生的視圖。

CREATEVIEWIS_StudentAS

SELECTSno，Sname，SageFROMStudentWHERESdept='IS'；從單個(gè)基本表導(dǎo)出只是去掉了基本表的某些行和某些列保留了碼2023/7/2935數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)

建立視圖（續(xù)）WITHCHECKOPTION透過(guò)視圖進(jìn)行增刪改操作時(shí)，不得破壞視圖定義中的謂詞條件（即子查詢中的條件表達(dá)式）2023/7/2936數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)

WITHCHECKOPTION的視圖[例2]建立信息系學(xué)生的視圖，并要求透過(guò)該視圖進(jìn)行的更新操作只涉及信息系學(xué)生。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；2023/7/2937數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)對(duì)IS_Student視圖的更新操作修改操作：DBMS自動(dòng)加上Sdept='IS'的條件刪除操作：DBMS自動(dòng)加上Sdept='IS'的條件插入操作：DBMS自動(dòng)檢查Sdept屬性值是否為'IS'如果不是，則拒絕該插入操作如果沒(méi)有提供Sdept屬性值，則自動(dòng)定義Sdept為'IS'2023/7/2938數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)基于多個(gè)基表的視圖[例4]建立信息系選修了1號(hào)課程的學(xué)生視圖。

CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；2023/7/2939數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)基于視圖的視圖

[例5]建立信息系選修了1號(hào)課程且成績(jī)?cè)?0分以上的學(xué)生的視圖。

CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；2023/7/2940數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)帶表達(dá)式的視圖[例6]定義一個(gè)反映學(xué)生出生年份的視圖。

CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2000-SageFROMStudent設(shè)置一些派生屬性列,也稱為虛擬列--Sbirth

帶表達(dá)式的視圖必須明確定義組成視圖的各個(gè)屬性列名2023/7/2941數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)

建立分組視圖[例7]將學(xué)生的學(xué)號(hào)及他的平均成績(jī)定義為一個(gè)視圖

假設(shè)SC表中“成績(jī)”列Grade為數(shù)字型

CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；2023/7/2942數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)

建立視圖（續(xù)）[例8]將Student表中所有女生記錄定義為一個(gè)視圖

CREATEVIEWF_Student1(stdnum，name，sex，age，dept)ASSELECT*FROMStudentWHERESsex='女'；缺點(diǎn)：修改基表Student的結(jié)構(gòu)后，Student表與F_Student1視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。2023/7/2943數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)

建立視圖（續(xù)）

CREATEVIEW

F_Student2(stdnum，name，sex，age，dept)ASSELECTSno，Sname，Ssex，Sage，Sdept

FROMStudentWHERESsex='女'；為基表Student增加屬性列不會(huì)破壞Student表與F_Student2視圖的映象關(guān)系。2023/7/2944數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)2.刪除視圖DROPVIEW<視圖名>；該語(yǔ)句從數(shù)據(jù)字典中刪除指定的視圖定義如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級(jí)聯(lián)刪除語(yǔ)句，把該視圖和由它導(dǎo)出的所有視圖一起刪除刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用DROPVIEW語(yǔ)句刪除2023/7/2945數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)刪除視圖(續(xù)）

[例9]刪除視圖IS_S1

DROPVIEWIS_S1；

2023/7/2946數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用2023/7/2947數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.5.2查詢視圖從用戶角度：查詢視圖與查詢基本表相同DBMS實(shí)現(xiàn)視圖查詢的方法實(shí)體化視圖（ViewMaterialization）有效性檢查：檢查所查詢的視圖是否存在執(zhí)行視圖定義，將視圖臨時(shí)實(shí)體化，生成臨時(shí)表查詢視圖轉(zhuǎn)換為查詢臨時(shí)表查詢完畢刪除被實(shí)體化的視圖(臨時(shí)表)new2023/7/2948數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)查詢視圖（續(xù)）視圖消解法（ViewResolution）進(jìn)行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數(shù)據(jù)字典中取出視圖的定義把視圖定義中的子查詢與用戶的查詢結(jié)合起來(lái)，轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢執(zhí)行修正后的查詢2023/7/2949數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)查詢視圖（續(xù)）[例1]在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。

SELECTSno，SageFROMIS_StudentWHERESage<20；IS_Student視圖的定義(視圖定義例1)：

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS‘；2023/7/2950數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)查詢視圖（續(xù)）視圖實(shí)體化法視圖消解法轉(zhuǎn)換后的查詢語(yǔ)句為：SELECTSno，SageFROMStudentWHERESdept='IS'ANDSage<20；2023/7/2951數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)查詢視圖（續(xù)）[例2]查詢信息系選修了1號(hào)課程的學(xué)生SELECTSno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1'；2023/7/2952數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)查詢視圖（續(xù)）視圖消解法的局限有些情況下，視圖消解法不能生成正確查詢。采用視圖消解法的DBMS會(huì)限制這類查詢。

2023/7/2953數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)查詢視圖（續(xù)）[例3]在S_G視圖中查詢平均成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)和平均成績(jī)SELECT*FROMS_GWHEREGavg>=90；S_G視圖定義：

CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；2023/7/2954數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)查詢轉(zhuǎn)換錯(cuò)誤：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；正確：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；2023/7/2955數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用2023/7/2956數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)3.5.3更新視圖用戶角度：更新視圖與更新基本表相同DBMS實(shí)現(xiàn)視圖更新的方法視圖實(shí)體化法（ViewMaterialization）視圖消解法（ViewResolution）指定WITHCHECKOPTION子句后

DBMS在更新視圖時(shí)會(huì)進(jìn)行檢查，防止用戶通過(guò)視圖對(duì)不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)進(jìn)行更新2023/7/2957數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)更新視圖（續(xù)）[例1]將信息系學(xué)生視圖IS_Student中學(xué)號(hào)200215122

的學(xué)生姓名改為“劉辰”。UPDATEIS_StudentSETSname='劉辰'WHERESno=200215122'；轉(zhuǎn)換后的語(yǔ)句：UPDATEStudentSETSname='劉辰'WHERESno=200215122'ANDSdept='IS'；2023/7/2958數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)更新視圖（續(xù)）[例2]向信息系學(xué)生視圖IS_S中插入一個(gè)新的學(xué)生記錄：95029，趙新，20歲INSERTINTOIS_StudentVALUES(‘95029’，‘趙新’，20)；轉(zhuǎn)換為對(duì)基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES('95029'，'趙新'，20，'IS')；2023/7/2959數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)更新視圖（續(xù)）[例3]刪除視圖CS_S中學(xué)號(hào)為95029的記錄DELETEFROMIS_StudentWHERESno='95029'；轉(zhuǎn)換為對(duì)基本表的更新：DELETEFROMStudentWHERESno='95029'ANDSdept='IS'；2023/7/2960數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)更新視圖的限制一些視圖是不可更新的，因?yàn)閷?duì)這些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對(duì)相應(yīng)