數據和表的修改、視.ppt

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