數(shù)據(jù)和表的修改、視.pptVIP

AnIntroductiontoDatabaseSystem 1 數(shù)據(jù)更新 插入數(shù)據(jù)修改數(shù)據(jù)刪除數(shù)據(jù) AnIntroductiontoDatabaseSystem 2 插入數(shù)據(jù) 兩種插入數(shù)據(jù)方式插入單個(gè)元組插入子查詢結(jié)果 AnIntroductiontoDatabaseSystem 3 1 插入單個(gè)元組 語(yǔ)句格式INSERTINTO VALUES 功能將新元組插入指定表中 AnIntroductiontoDatabaseSystem 4 插入單個(gè)元組 續(xù) 例1 將一個(gè)新學(xué)生記錄 學(xué)號(hào) 95020 姓名 陳冬 性別 男 所在系 IS 年齡 18歲 插入到Student表中 INSERTINTOStudentVALUES 95020 陳冬 男 IS 18 AnIntroductiontoDatabaseSystem 5 插入單個(gè)元組 續(xù) 例2 插入一條選課記錄 95020 1 INSERTINTOSC Sno Cno VALUES 95020 1 新插入的記錄在Grade列上取空值 AnIntroductiontoDatabaseSystem 6 插入單個(gè)元組 續(xù) INTO子句指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒(méi)有指定屬性列 表示要插入的是一條完整的元組 且屬性列屬性與表定義中的順序一致指定部分屬性列 插入的元組在其余屬性列上取空值VALUES子句提供的值必須與INTO子句匹配值的個(gè)數(shù)值的類型 AnIntroductiontoDatabaseSystem 7 2 插入子查詢結(jié)果 語(yǔ)句格式INSERTINTO 子查詢 功能將子查詢結(jié)果插入指定表中 AnIntroductiontoDatabaseSystem 8 插入子查詢結(jié)果 續(xù) 例3 對(duì)每一個(gè)系 求學(xué)生的平均年齡 并把結(jié)果存入數(shù)據(jù)庫(kù) 第一步 建表CREATETABLEDeptage SdeptCHAR 15 系名 AvgageSMALLINT 學(xué)生平均年齡 AnIntroductiontoDatabaseSystem 9 插入子查詢結(jié)果 續(xù) 第二步 插入數(shù)據(jù)INSERTINTODeptage Sdept Avgage SELECTSdept AVG Sage FROMStudentGROUPBYSdept AnIntroductiontoDatabaseSystem 10 插入子查詢結(jié)果 續(xù) INTO子句 與插入單條元組類似 指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒(méi)有指定屬性列 表示要插入的是一條完整的元組指定部分屬性列 插入的元組在其余屬性列上取空值子查詢SELECT子句目標(biāo)列必須與INTO子句匹配值的個(gè)數(shù)值的類型 AnIntroductiontoDatabaseSystem 11 插入子查詢結(jié)果 續(xù) DBMS在執(zhí)行插入語(yǔ)句時(shí)會(huì)檢查所插元組是否破壞表上已定義的完整性規(guī)則實(shí)體完整性參照完整性用戶定義的完整性對(duì)于有NOTNULL約束的屬性列是否提供了非空值對(duì)于有UNIQUE約束的屬性列是否提供了非重復(fù)值對(duì)于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi) AnIntroductiontoDatabaseSystem 12 修改數(shù)據(jù) 語(yǔ)句格式UPDATESET WHERE 功能修改指定表中滿足WHERE子句條件的元組 AnIntroductiontoDatabaseSystem 13 修改數(shù)據(jù) 續(xù) 三種修改方式修改某一個(gè)元組的值修改多個(gè)元組的值帶子查詢的修改語(yǔ)句 AnIntroductiontoDatabaseSystem 14 1 修改某一個(gè)元組的值 例4 將學(xué)生95001的年齡改為22歲 UPDATEStudentSETSage 22WHERESno 95001 AnIntroductiontoDatabaseSystem 15 2 修改多個(gè)元組的值 例5 將所有學(xué)生的年齡增加1歲 UPDATEStudentSETSage Sage 1 AnIntroductiontoDatabaseSystem 16 修改多個(gè)元組的值 續(xù) 例6 將信息系所有學(xué)生的年齡增加1歲 UPDATEStudentSETSage Sage 1WHERESdept IS AnIntroductiontoDatabaseSystem 17 3 帶子查詢的修改語(yǔ)句 例7 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零 UPDATESCSETGrade 0WHERE CS SELETESdeptFROMStudentWHEREStudent Sno SC Sno AnIntroductiontoDatabaseSystem 18 修改數(shù)據(jù) 續(xù) SET子句指定修改方式要修改的列修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組 AnIntroductiontoDatabaseSystem 19 修改數(shù)據(jù) 續(xù) DBMS在執(zhí)行修改語(yǔ)句時(shí)會(huì)檢查修改操作是否破壞表上已定義的完整性規(guī)則實(shí)體完整性主碼不允許修改用戶定義的完整性NOTNULL約束UNIQUE約束值域約束 AnIntroductiontoDatabaseSystem 20 3 4 3刪除數(shù)據(jù) DELETEFROM WHERE 功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要?jiǎng)h除的元組缺省表示要修改表中的所有元組 AnIntroductiontoDatabaseSystem 21 刪除數(shù)據(jù) 續(xù) 三種刪除方式刪除某一個(gè)元組的值刪除多個(gè)元組的值帶子查詢的刪除語(yǔ)句 AnIntroductiontoDatabaseSystem 22 1 刪除某一個(gè)元組的值 例8 刪除學(xué)號(hào)為95019的學(xué)生記錄 DELETEFROMStudentWHERESno 95019 AnIntroductiontoDatabaseSystem 23 2 刪除多個(gè)元組的值 例9 刪除2號(hào)課程的所有選課記錄 DELETEFROMSC WHERECno 2 例10 刪除所有的學(xué)生選課記錄 DELETEFROMSC AnIntroductiontoDatabaseSystem 24 3 帶子查詢的刪除語(yǔ)句 例11 刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄 DELETEFROMSCWHERE CS SELETESdeptFROMStudentWHEREStudent Sno SC Sno AnIntroductiontoDatabaseSystem 25 刪除數(shù)據(jù) 續(xù) DBMS在執(zhí)行插入語(yǔ)句時(shí)會(huì)檢查所插元組是否破壞表上已定義的完整性規(guī)則參照完整性不允許刪除級(jí)聯(lián)刪除 AnIntroductiontoDatabaseSystem 26 更新數(shù)據(jù)與數(shù)據(jù)一致性 DBMS在執(zhí)行插入 刪除 更新語(yǔ)句時(shí)必須保證數(shù)據(jù)庫(kù)一致性必須有事務(wù)的概念和原子性完整性檢查和保證 AnIntroductiontoDatabaseSystem 27 刪除基本表 DROPTABLE 基本表刪除時(shí) 表上的數(shù)據(jù) 索引和完整性約束條件都刪除 但表上的視圖往往仍然保留 只是無(wú)法引用 刪除基本表時(shí) 系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該基本表及其索引的描述 標(biāo)準(zhǔn)中沒(méi)有 認(rèn)為表建立后就永久存在 AnIntroductiontoDatabaseSystem 28 例題 例5 刪除Student表DROPTABLEStudent AnIntroductiontoDatabaseSystem 29 修改基本表 ALTERTABLE ADD 完整性約束 DROP MODIFY 要修改的基本表ADD子句 增加新列和新的完整性約束條件DROP子句 刪除指定的完整性約束條件MODIFY子句 用于修改列名和數(shù)據(jù)類型 AnIntroductiontoDatabaseSystem 30 例題 例4 向Student表增加 入學(xué)時(shí)間 列 其數(shù)據(jù)類型為日期型 ALTERTABLEStudentADDScomeDATE 不論基本表中原來(lái)是否已有數(shù)據(jù) 新增加的列一律為空值 AnIntroductiontoDatabaseSystem 31 例題 例5 增加課程名稱必須取唯一值的約束 ALTERTABLECourseADDUNIQUE Cname AnIntroductiontoDatabaseSystem 32 語(yǔ)句格式 續(xù) 刪除屬性列間接刪除把表中要保留的列及其內(nèi)容復(fù)制到一個(gè)新表中刪除原表再將新表重命名為原表名直接刪除屬性列 新 例 ALTERTABLEStudentDropScome AnIntroductiontoDatabaseSystem 33 例6 將年齡的數(shù)據(jù)類型改為半字長(zhǎng)整數(shù) ALTERTABLEStudentMODIFYSageSMALLINT 注 修改原有的列定義有可能會(huì)破壞已有數(shù)據(jù) AnIntroductiontoDatabaseSystem 34 視圖 視圖的特點(diǎn)虛表 是從一個(gè)或幾個(gè)基本表 或視圖 導(dǎo)出的表只存放視圖的定義 不會(huì)出現(xiàn)數(shù)據(jù)冗余基表中的數(shù)據(jù)發(fā)生變化 從視圖中查詢出的數(shù)據(jù)也隨之改變 AnIntroductiontoDatabaseSystem 35 視圖 基于視圖的操作查詢刪除受限更新定義基于該視圖的新視圖 AnIntroductiontoDatabaseSystem 36 1 建立視圖 語(yǔ)句格式CREATEVIEW AS WITHCHECKOPTION AnIntroductiontoDatabaseSystem 37 建立視圖 續(xù) DBMS執(zhí)行CREATEVIEW語(yǔ)句時(shí)只是把視圖的定義存入數(shù)據(jù)字典 并不執(zhí)行其中的SELECT語(yǔ)句 在對(duì)視圖查詢時(shí) 按視圖的定義從基本表中將數(shù)據(jù)查出 AnIntroductiontoDatabaseSystem 38 組成視圖的屬性列名 全部省略或全部指定省略 由子查詢中SELECT目標(biāo)列中的諸字段組成明確指定視圖的所有列名 1 某個(gè)目標(biāo)列是集函數(shù)或列表達(dá)式 2 目標(biāo)列為 3 多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段 4 需要在視圖中為某個(gè)列啟用新的更合適的名字 AnIntroductiontoDatabaseSystem 39 行列子集視圖 例1 建立信息系學(xué)生的視圖 CREATEVIEWIS StudentASSELECTSno Sname SageFROMStudentWHERESdept IS 從單個(gè)基本表導(dǎo)出只是去掉了基本表的某些行和某些列保留了碼 AnIntroductiontoDatabaseSystem 40 4 select statement 用來(lái)創(chuàng)建視圖的SELECT語(yǔ)句 可在SELECT語(yǔ)句中查詢多個(gè)表或視圖 以表明新創(chuàng)建的視圖所參照的表或視圖 但對(duì)SELECT語(yǔ)句有以下限制 定義視圖的用戶必須對(duì)所參照的表或視圖有查詢 即可執(zhí)行SELECT語(yǔ)句 權(quán)限 不能使用COMPUTE或COMPUTEBY子句 不能使用ORDERBY子句 如果使用必須有Top關(guān)鍵詞 不能使用INTO子句 不能在臨時(shí)表或表變量上創(chuàng)建視圖 AnIntroductiontoDatabaseSystem 41 子查詢 As后的子查詢是用來(lái)創(chuàng)建視圖的SELECT語(yǔ)句 可在SELECT語(yǔ)句中查詢多個(gè)表或視圖 以表明新創(chuàng)建的視圖所參照的表或視圖 但對(duì)SELECT語(yǔ)句有以下限制 定義視圖的用戶必須對(duì)所參照的表或視圖有查詢 即可執(zhí)行SELECT語(yǔ)句 權(quán)限 不能使用ORDERBY子句 不能在臨時(shí)表或表變量上創(chuàng)建視圖 AnIntroductiontoDatabaseSystem 42 建立視圖 續(xù) WITHCHECKOPTION透過(guò)視圖進(jìn)行增刪改操作時(shí) 不得破壞視圖定義中的謂詞條件 即子查詢中的條件表達(dá)式 AnIntroductiontoDatabaseSystem 43 WITHCHECKOPTION的視圖 例2 建立信息系學(xué)生的視圖 并要求透過(guò)該視圖進(jìn)行的更新操作只涉及信息系學(xué)生 CREATEVIEWIS StudentASSELECTSno Sname SageFROMStudentWHERESdept IS WITHCHECKOPTION AnIntroductiontoDatabaseSystem 44 對(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 AnIntroductiontoDatabaseSystem 45 基于多個(gè)基表的視圖 例4 建立信息系選修了1號(hào)課程的學(xué)生視圖 CREATEVIEWIS S1 Sno Sname Grade ASSELECTStudent Sno Sname GradeFROMStudent SCWHERESdept IS ANDStudent Sno SC SnoANDSC Cno 1 AnIntroductiontoDatabaseSystem 46 基于視圖的視圖 例5 建立信息系選修了1號(hào)課程且成績(jī)?cè)?0分以上的學(xué)生的視圖 CREATEVIEWIS S2ASSELECTSno Sname GradeFROMIS S1WHEREGrade 90 AnIntroductiontoDatabaseSystem 47 帶表達(dá)式的視圖 例6 定義一個(gè)反映學(xué)生出生年份的視圖 CREATEVIEWBT S Sno Sname Sbirth ASSELECTSno Sname 2000 SageFROMStudent設(shè)置一些派生屬性列 也稱為虛擬列 Sbirth帶表達(dá)式的視圖必須明確定義組成視圖的各個(gè)屬性列名 AnIntroductiontoDatabaseSystem 48 建立分組視圖 例7 將學(xué)生的學(xué)號(hào)及他的平均成績(jī)定義為一個(gè)視圖假設(shè)SC表中 成績(jī) 列Grade為數(shù)字型CREATVIEWS G Sno Gavg ASSELECTSno AVG Grade FROMSCGROUPBYSno AnIntroductiontoDatabaseSystem 49 建立視圖 續(xù) 一類不易擴(kuò)充的視圖以SELECT 方式創(chuàng)建的視圖可擴(kuò)充性差 應(yīng)盡可能避免 AnIntroductiontoDatabaseSystem 50 建立視圖 續(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)致該視圖不能正確工作 AnIntroductiontoDatabaseSystem 51 建立視圖 續(xù) CREATEVIEWF Student2 stdnum name sex age dept ASSELECTSno Sname Ssex Sage SdeptFROMStudentWHERESsex 女 為基表Student增加屬性列不會(huì)破壞Student表與F Student2視圖的映象關(guān)系 AnIntroductiontoDatabaseSystem 52 常見(jiàn)的視圖形式 行列子集視圖WITHCHECKOPTION的視圖基于多個(gè)基表的視圖基于視圖的視圖帶表達(dá)式的視圖分組視圖 AnIntroductiontoDatabaseSystem 53 2 刪除視圖 DROPVIEW 該語(yǔ)句從數(shù)據(jù)字典中刪除指定的視圖定義由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中 但已不能使用 必須顯式刪除刪除基表時(shí) 由該基表導(dǎo)出的所有視圖定義都必須顯式刪除 AnIntroductiontoDatabaseSystem 54 刪除視圖 續(xù) 例9 刪除視圖IS S1DROPVIEWIS S1 AnIntroductiontoDatabaseSystem 55 查詢視圖 從用戶角度 查詢視圖與查詢基本表相同DBMS實(shí)現(xiàn)視圖查詢的方法實(shí)體化視圖 ViewMaterialization 有效性檢查 檢查所查詢的視圖是否存在執(zhí)行視圖定義 將視圖臨時(shí)實(shí)體化 生成臨時(shí)表查詢視圖轉(zhuǎn)換為查詢臨時(shí)表查詢完畢刪除被實(shí)體化的視圖 臨時(shí)表 new AnIntroductiontoDatabaseSystem 56 查詢視圖 續(xù) 視圖消解法 ViewResolution 進(jìn)行有效性檢查 檢查查詢的表 視圖等是否存在 如果存在 則從數(shù)據(jù)字典中取出視圖的定義把視圖定義中的子查詢與用戶的查詢結(jié)合起來(lái) 轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢執(zhí)行修正后的查詢 AnIntroductiontoDatabaseSystem 57 查詢視圖 續(xù) 例1 在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生 SELECTSno SageFROMIS StudentWHERESage 20 IS Student視圖的定義 視圖定義例1 CREATEVIEWIS StudentASSELECTSno Sname SageFROMStudentWHERESdept IS AnIntroductiontoDatabaseSystem 58 查詢視圖 續(xù) 視圖實(shí)體化法視圖消解法轉(zhuǎn)換后的查詢語(yǔ)句為 SELECTSno SageFROMStudentWHERESdept IS ANDSage 20 AnIntroductiontoDatabaseSystem 59 查詢視圖 續(xù) 例2 查詢信息系選修了1號(hào)課程的學(xué)生SELECTSno SnameFROMIS Student SCWHEREIS Student Sno SC SnoANDSC Cno 1 AnIntroductiontoDatabaseSystem 60 查詢視圖 續(xù) 視圖消解法的局限有些情況下 視圖消解法不能生成正確查詢 采用視圖消解法的DBMS會(huì)限制這類查詢 AnIntroductiontoDatabaseSystem 61 查詢視圖 續(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 AnIntroductiontoDatabaseSystem 62 查詢轉(zhuǎn)換 錯(cuò)誤 SELECTSno AVG Grade FROMSCWHEREAVG Grade 90GROUPBYSno 正確 SELECTSno AVG Grade FROMSCGROUPBYSnoHAVINGAVG Grade 90 AnIntroductiontoDatabaseSystem 63 更新視圖 用戶角度 更新視圖與更新基本表相同DBMS實(shí)現(xiàn)視圖更新的方法視圖實(shí)體化法 ViewMaterialization 視圖消解法 ViewResolution 指定WITHCHECKOPTION子句后DBMS在更新視圖時(shí)會(huì)進(jìn)行檢查 防止用戶通過(guò)視圖對(duì)不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)進(jìn)行更新 AnIntroductiontoDatabaseSystem 64 更新視圖 續(xù) 例1 將信息系學(xué)生視圖IS Student中學(xué)號(hào)95002的學(xué)生姓名改為 劉辰 UPDATEIS StudentSETSname 劉辰 WHERESno 95002 轉(zhuǎn)換后的語(yǔ)句 UPDATEStudentSETSname 劉辰 WHERESno 95002 ANDSdept IS AnIntroductiontoDatabaseSystem 65 更新視圖 續(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 AnIntroductiontoDatabaseSystem 66 更新視圖 續(xù) 例3 刪除視圖CS S中學(xué)號(hào)為95029的記錄DELETEFROMIS StudentWHERESno 95029 轉(zhuǎn)換為對(duì)基本表的更新 DELETEFROMStudentWHERESno 95029 ANDSdept IS AnIntroductiontoDatabaseSystem 67 更新視圖的限制 一些視圖是不可更新的 因?yàn)閷?duì)這些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新 對(duì)兩類方法均如此 例 視圖S G為不可更新視圖 CREATEVIEWS G Sno Gavg ASSELECTSno AVG Grade FROMSCGROUPBYSno AnIntroductiontoDatabaseSystem 68 更新視圖 續(xù) 對(duì)于如下更新語(yǔ)句 UPDATES GSETGavg 90WHERESno 95001 無(wú)論實(shí)體化法還是消解法都無(wú)法將其轉(zhuǎn)換成對(duì)基本表SC的更新 AnIntroductiontoDatabaseSystem 69 實(shí)際系統(tǒng)對(duì)視圖更新的限制 允許對(duì)行列子集視圖進(jìn)行更新對(duì)其他類型視圖的更新不同系統(tǒng)有不同限制DB2對(duì)視圖更新的限制 1 若視圖是由兩個(gè)以上基本表導(dǎo)出的 則此視圖不允許更新 2 若視圖的字段來(lái)自字段表達(dá)式或常數(shù) 則不允許對(duì)此視圖執(zhí)行INSERT和UPDATE操作 但允許執(zhí)行DELETE操作 AnIntroductiontoDatabaseSystem 70 更新視圖 續(xù) 3 若視圖的字段來(lái)自集函數(shù) 則此視圖不允許更新 4 若視圖定義中含有GROUPBY子句 則此視圖不允許更新 5 若視圖定義中含有DISTINCT短語(yǔ) 則此視圖不允許更新 6 若視圖定義中有嵌套查詢 并且內(nèi)層查詢的FROM子句中涉及的表也是導(dǎo)出該視圖的基本表 則此視圖不允許更新 7 一個(gè)不允許更新的視圖上定義的視圖也不允許更新 AnIntroductiontoDatabaseSystem 71 更新視圖 續(xù) 例 視圖GOOD SC 修課成績(jī)?cè)谄骄煽?jī)之上的元組 CREATEVIEWGOOD SCASSELECTSno Cno GradeFROMSCWHEREGrade SELECTAVG Grade FROMSC AnIntroductiontoDatabaseSystem 72 YoucannotremovearowiftheviewcontainsGroupfunctionsAGROUPBYclauseTheDISTINCTcommandYoucannotmodifydatainaviewifitcon