WindowsServer數據庫課件--第03章SQL語言.ppt

數據庫系統(tǒng)概論AnIntroductiontoDatabaseSystem第三章關系數據庫標準語言SQL 4 復習 1 統(tǒng)計每人不及格的門數 2 查詢顯示開設課程的名稱 及格人數及最高分 3 查詢顯示所有計算機系學生的姓名 以及他們所選課程和相應的成績 復習 單表查詢 SELECTSno count cno FROMSCWHEREgrade 60groupbysno 1 統(tǒng)計每人不及格的門數 復習 單表查詢 SELECTcno課程號 Count sno 及格人數 Max grade 最高分FROMSC CourseWHERESC cno Coandgrade 60groupbycno 2 查詢顯示開設課程的名稱 及格人數及最高分 3 顯示全部計算機系學生的姓名 以及他們所選課程名和相應的成績 復習 連接查詢 SELECTFROMWHERE sname cname gradestudent sc o oandstudent sno sc snoandsdept CS 復習 連接查詢 SELECT FROMStudent SC 4 描述下列語句的含義 WHEREStudent Sno SC Sno Student Sno Sname Ssex Sage Sdept Cno Grade SELECTC1 Cno C2 CpnoFROMCourseC1 CourseC2WHEREC1 Cpno C2 Cno 學習目標 了解外連接以及查詢實現掌握不相關子查詢 IN 比較運算符 ANY ALL 掌握相關子查詢 Exists謂詞 3 4查詢 3 4 1單表查詢3 4 2連接查詢3 4 3嵌套查詢3 4 4集合查詢 廣義笛卡爾積查詢等值連接 含自然連接 查詢非等值連接查詢自身連接查詢外連接查詢復合條件連接查詢 SQL中連接查詢的主要類型 4 外連接 1 外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組 外連接操作以指定表為連接主體 將主體表中的全部記錄輸出顯示 例 將每個學生的基本信息與其所選課程信息對應顯示 結果 Student SnoSnameSsexSageSdeptCnoGrade0215121李勇男20CS1920215121李勇男20CS2850215121李勇男20CS3880215122劉晨女19IS2900215122劉晨女19IS3800215123王敏女18MA0215125張立男19IS 示例 外連接 SELECT FROM 連接類型 JOIN JOIN ON ON ONWHERE INNER LEFT RIGHT FULL CROSS 提示 JOIN的順序和ON連接條件的順序相反 外連接只能對兩個表進行 2 語法格式 4 外連接 Student Sc SELECTstudent sc FROMstudentLEFTJOINscONstudent sno sc sno RIGHT FULL 例 查詢所有學生的個人信息及選課信息 4 外連接 4 外連接 SELECTsname gradeFROMstudentJOINscJOINcourseONo oONstudent sno sc snoWHEREcname 數學 例 查詢選修 數學 課程的學生的姓名和該課程成績 查詢顯示全部計算機系學生的姓名 以及他們所選課程的門數 練習 SELECTsname count cno 課程門數FROMstudentleftjoinscONstudent sno sc snoWHEREsdept CS GROUPBYSname 特點 WHERE子句中含多個條件 例35 查詢選修2號課程且成績在90分以上所有學生的學號 姓名 SELECTStudent Sno student SnameFROMStudent SCWHEREStudent Sno SC SnoANDSC Cno 2 ANDSC Grade 90 5 復合條件連接 特點 連接操作在兩個以上的表之間進行 例36 查詢每個學生的學號 姓名 選修的課程名及成績 SELECTStudent Sno Sname Cname GradeFROMStudent SC CourseWHEREStudent Sno SC SnoandSC Cno Course Cno 6 多表連接 3 4查詢 3 4 1單表查詢3 4 2連接查詢3 4 3嵌套查詢3 4 4集合查詢 3 4 3嵌套查詢 嵌套查詢概述嵌套查詢分類及求解方法引出子查詢的謂詞 1 一個SELECT FROM WHERE語句稱為一個查詢塊 2 將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語條件中的查詢稱為嵌套查詢 1 嵌套查詢概述 例 SELECTSname外層查詢 父查詢FROMStudentWHERESnoIN SELECTSno內層查詢 子查詢FROMSCWHERECno 2 嵌套查詢概述 3 說明 子查詢的限制注意 子查詢不能使用ORDERBY子句ORDERBY只對查詢最終結果的排序層層嵌套方式反映了SQL語言的結構化不同的DBMS允許的最大嵌套層數不同有些嵌套查詢可以用連接查詢替代 3 4 3嵌套查詢 嵌套查詢概述嵌套查詢分類及求解方法引出子查詢的謂詞 1 不相關子查詢 子查詢的查詢條件不依賴于父查詢查詢過程 由里向外逐層處理每個子查詢在上一級查詢處理之前先求解 子查詢的結果用于建立父查詢的查找條件 2 嵌套查詢的分類 1 不相關子查詢 2 嵌套查詢的分類 例 SELECTSname外層查詢 父查詢FROMStudentWHERESnoIN SELECTSno內層查詢 子查詢FROMSCWHERECno 2 2 相關子查詢 子查詢的查詢條件依賴于父查詢查詢過程 首先取外層查詢表的第一個元組 根據它與內層查詢相關的屬性值處理內層查詢 若WHERE子句返回值為真 則取此元組放入結果表 然后再取外層表的下一個元組 重復這一過程 直至外層表全部檢查完為止 嵌套查詢的分類 3 4 3嵌套查詢 嵌套查詢概述嵌套查詢分類及求解方法引出子查詢的謂詞 3 引出子查詢的謂詞 帶有IN謂詞的子查詢帶有比較運算符的子查詢帶有ANY或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢 例39 查詢與劉晨在同一個系學習的學生 可以分步實現 確定 劉晨 所在系名SELECTSdeptFROMStudentWHERESname 劉晨 1 帶有IN謂詞的子查詢 結果為 deptIS 例39 查詢與劉晨在同一個系學習的學生 查找所有在IS系學習的學生SELECTSno Sname SdeptFROMStudentWHERESdept IS 結果為 SnoSnameSdept0215122劉晨IS0215125張立IS 1 帶有IN謂詞的子查詢 續(xù) 1 帶有IN謂詞的子查詢 續(xù) 方法1 將第一步查詢嵌入到第二步查詢的條件中SELECTSno Sname SdeptFROMStudentWHERESdeptIN SELECTSdeptFROMStudentWHERESname 劉晨 構造嵌套查詢 說明 此查詢?yōu)椴幌嚓P子查詢 DBMS求解該查詢時也是分步去做的 方法2 也可用自身連接完成本查詢要求S1S2SELECTS1 Sno S1 Sname S1 SdeptFROMStudentS1 StudentS2WHERES1 Sdept S2 SdeptANDS2 Sname 劉晨 1 帶有IN謂詞的子查詢 續(xù) 例38 查詢選修了課程名為 信息系統(tǒng) 的學生學號和姓名 1 帶有IN謂詞的子查詢 續(xù) 最后在Student關系中取出Sno和SnameSELECTSno SnameFROMStudentWHERESnoIN SELECTCnoFROMCourseWHERECname 信息系統(tǒng) 首先在Course關系中找出 信息系統(tǒng) 的課程號 結果 3 然后在SC關系中找出選修了3號課程的學生學號SELECTSnoFROMSCWHERECnoIN 組合以后的代碼 SELECTSno SnameFROMStudentWHERESnoIN SELECTSnoFROMSCWHERECnoIN SELECTCnoFROMCourseWHERECname 信息系統(tǒng) 1 帶有IN謂詞的子查詢 續(xù) 結果 SnoSname0215121李勇0215122劉晨 1 帶有IN謂詞的子查詢 續(xù) 另 本例也可用連接查詢SELECTSno SnameFROMStudent SC CourseWHEREStudent Sno SC SnoANDSC Cno Course CnoANDCourse Cname 信息系統(tǒng) 是指父查詢和子查詢之間用比較運算符連接的查詢適用范圍 當能確切知道內層查詢返回單值時 內層查詢返回值是集合 要與ANY或ALL謂詞配合使用后 2 帶有比較運算符的子查詢 2 帶有比較運算符的子查詢 續(xù) 例 假設一個學生只可能在一個系學習 并且必須屬于一個系 則在例37可以改寫為 SELECTSno Sname SdeptFROMStudentWHERESdept SELECTSdeptFROMStudentWHERESname 劉晨 注意 1 子查詢一定要用括號括起來 2 帶有比較運算符的子查詢 續(xù) 注意 2 子查詢一定要跟在比較符之后 SELECTSno Sname SdeptFROMStudentWHERE SELECTSdeptFROMStudentWHERESname 劉晨 Sdept 謂詞語義 ANY 表示任意一個值 ALL 表示所有值 3 帶有ANY或ALL謂詞子查詢 需要配合使用比較運算符 ANY大于 大于等于 子查詢結果中的某個值 ALL大于 大于等于 子查詢結果中的所有值 ANY小于 小于等于 子查詢結果中的某個值 ALL小于 小于等于 子查詢結果中的所有值 ANY等于子查詢結果中的某個值 ALL等于子查詢結果中的所有值 通常無意義 ANY不等于子查詢結果中的某個值 ALL不等于子查詢結果中的任何一個值 3 帶有ANY或ALL謂詞子查詢 哪些無實際意義 例42 查詢其他系中比信息系任一個 其中某一個 學生年齡小的學生姓名和年齡 SELECTSname SageFROMStudentWHERESage ANY SELECTSageFROMStudentWHERESdept IS 執(zhí)行過程 3 帶有ANY或ALL謂詞子查詢 續(xù) ANDSdept IS 是哪個查詢的條件 ANY和ALL謂詞有時可以用集函數實現ANY ALL與集函數的對應關系 3 帶有ANY或ALL謂詞子查詢 續(xù) IN MAX MAX MIN MIN NOTIN MIN MIN MAX MAX 用ANY實現例39如下 SELECTSname SageFROMStudentWHERESage IS 3 帶有ANY或ALL謂詞子查詢 續(xù) 用集函數實現例39如下 SELECTSname SageFROMStudentWHERESage IS 例43 查詢其他系中比IS系所有學生年齡都小的學生姓名及年齡 方法一 用ALL謂詞SELECTSname SageFROMStudentWHERESage IS 3 帶有ANY或ALL謂詞子查詢 續(xù) 例40 查詢其他系中比IS系所有學生年齡都小的學生姓名及年齡 方法二 用集函數SELECTSname SageFROMStudentWHERESage IS 3 帶有ANY或ALL謂詞子查詢 續(xù) 用聚集函數實現子查詢能夠減少比較次數 提高查詢效率 CPU 比較兩種方法的效率 3 4 3嵌套查詢 帶有IN謂詞的子查詢帶有比較運算符的子查詢帶有ANY或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢 4 帶有EXISTS謂詞的子查詢 1 EXISTS謂詞 2 NOTEXISTS謂詞 3 不同形式的查詢間的替換 4 相關子查詢的效率 5 用EXISTS NOTEXISTS實現全稱量詞 6 用EXISTS NOTEXISTS實現邏輯蘊函 4 帶有EXISTS謂詞的子查詢 1 EXISTS謂詞相當于存在量詞 帶有EXISTS謂詞的子查詢不返回具體數據 只產生邏輯值 True 或 False 由EXISTS引出的子查詢 其目標列表達式通常都用 2 NOTEXISTS謂詞 例44 查詢所有選修了2號課程的學生姓名 4 帶有EXISTS謂詞的子查詢 用帶in謂詞的嵌套查詢SELECTSnameFROMStudentWHERESnoin SELECTSnoFROMSCWHERECno 2 例44 查詢所有選修了2號課程的學生姓名 用帶EXISTS謂詞的嵌套查詢SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 帶有EXISTS謂詞的子查詢 用帶EXISTS謂詞的嵌套查詢SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 帶有EXISTS謂詞的子查詢 外 內 帶EXISTS謂詞查詢的執(zhí)行過程 內 4 帶有EXISTS謂詞的子查詢 外 內 內 內 內 帶EXISTS謂詞查詢的執(zhí)行過程 用帶EXISTS謂詞的嵌套查詢SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 帶有EXISTS謂詞的子查詢 外 內 內 內 內 內 內 帶EXISTS謂詞查詢的執(zhí)行過程 用帶EXISTS謂詞的嵌套查詢SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 用帶EXISTS謂詞的嵌套查詢SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 帶有EXISTS謂詞的子查詢 帶EXISTS謂詞查詢的執(zhí)行過程 外 內 內 內 內 內 內 用帶EXISTS謂詞的嵌套查詢SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 帶有EXISTS謂詞的子查詢 帶EXISTS謂詞查詢的執(zhí)行過程 內 外 例44 查詢所有選修了2號課程的學生姓名 4 帶有EXISTS謂詞的子查詢 本查詢涉及Student和SC關系 在Student中依次取每個元組的Sno值 用此值去檢查SC關系 若SC中存在這樣的元組 其Sno值等于此Student Sno值 并且其Cno 2 則取此Student Sname送入結果關系 父查詢指針下移 子查詢回到首行 否則 子查詢移到底部才返回False值 總結 對每一個父查詢的元組 都要求解一遍子查詢 例44 查詢所有選修了2號課程的學生姓名 4 帶有EXISTS謂詞的子查詢 用ExistsSELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 用INSELECTSnameFROMStudentWHERESnoin SELECTSnoFROMSCWHERECno 2 例44 查詢所有選修了2號課程的學生姓名 4 帶有EXISTS謂詞的子查詢 用連接查詢SELECTSnameFROMStudent SCWHEREStudent Sno SC SnoANDSC Cno 2 例45 查詢沒有選修2號課程的學生姓名 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 帶有EXISTS謂詞的子查詢 該要求很難用連接查詢實現 4 帶有EXISTS謂詞的子查詢 用嵌套查詢SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 例45 查詢沒有選修2號課程的學生姓名 4 帶有EXISTS謂詞的子查詢 3 不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價替換所有帶IN謂詞 比較運算符 ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換 總結 EXISTS謂詞的表達能力高于其他謂詞的表達形式的 例37 查詢與劉晨在同一個系學習的學生 用帶EXISTS謂詞的子查詢替換 SELECTSno Sname SdeptFROMStudentS1WHEREEXISTS SELECT FROMStudentWHERESdept S1 SdeptANDSname 劉晨 4 帶有EXISTS謂詞的子查詢 4 帶有EXISTS謂詞的子查詢 4 相關子查詢的效率由于帶EXISTS謂詞的相關子查詢只關心內層查詢是否有返回值 并不需要查具體值 因此其效率并不一定低于其他查詢 4 帶有EXISTS謂詞的子查詢 5 用EXISTS NOTEXISTS實現全稱量詞 難點 SQL語言中沒有全稱量詞 Forall 可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞 x P x P 例43 查詢選修了全部課程的學生姓名 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 帶有EXISTS謂詞的子查詢 4 帶有EXISTS謂詞的子查詢 Student Sc 外 中 內 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 帶有EXISTS謂詞的子查詢 Student Sc 外 中 內 內 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 帶有EXISTS謂詞的子查詢 Student Sc 外 中 內 內 內 內 內 內 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 帶有EXISTS謂詞的子查詢 Student Sc 外 中 內 內 內 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 帶有EXISTS謂詞的子查詢 Student Sc 外 中 內 內 內 內 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 帶有EXISTS謂詞的子查詢 Student Sc 外 中 內 內 內 內 內 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 帶有EXISTS謂詞的子查詢 Student Sc 外 中 內 內 內 內 內 例 查詢選修了課程的學生姓名方法一 SelectsnameFromstudentWhereExists Select FromscWherestudent sno sc sno 方法二 SelectDISTINCTsnameFromstudent scWherestudent sno sc sno 方法三 SelectsnameFromstudentWheresnoin selectdistinctsnofromsc 請比較 本例中三種方法的查詢效率如何 3 4查詢 3 4 1單表查詢3 4 2連接查詢3 4 3嵌套查詢3 4 4集合查詢 3 4 4集合查詢 標準SQL直接支持的集合操作種類并操作 UNION 一般商用數據庫支持的集合操作種類并操作 UNION 交操作 INTERSECT 差操作 EXCEPT 3 4 4集合查詢 1 并操作 UNION 參加UNION操作的各結果表的列數必須相同 對應項的數據類型也必須相同 一般格式為 UNION 例48 查詢計算機系的學生及年齡不大于19歲的學生 方法一 SELECT FROMStudentWHERESdept CS UNIONSELECT FROMStudentWHERESage 19 3 4 4集合查詢 方法二 SELECTDISTINCT FROMStudentWHERESdept CS ORSage 19 例49 查詢選修了課程1或者選修了課程2的學生 方法一 SELECTSnoFROMSCWHERECno 1 UNIONSELECTSnoFROMSCWHERECno 2 3 4 4集合查詢 結果有幾條記錄 方法二 SELECTDISTINCTSnoFROMSCWHERECno 1 ORCno 2 補充 設數據庫中有教師表Teacher Tno Tname 查詢學校中所有師生的姓名 SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher 3 4 4集合查詢 還能用前面類似的等價方式表示嗎 2 交操作 INTERSECT 標準SQL沒有提供交操作 但SQLSERVER2008提供 例50 查詢計算機系的學生與年齡不大于19歲的學生的交集 SELECT FROMStudentWHERESdept CS ANDSage 19 SELECT FROMStudentWHERESdept CS INTERSECTSELECT FROMStudentWHERESage 19 3 4 4集合查詢 例51 查詢既選修1號課又選修2號課的學生集合 3 4 4集合查詢 SELECTSnoFROMSCWHERECno 1 ANDSnoIN SELECTSnoFROMSCWHERECno 2 思考 查詢教師和學生同名的集合 SELECTSnoFROMSCWHERECno 1 INTERSECTSELECTSnoFROMSCWHERECno 2 3 差操作 EXCEPT 標準SQL中沒有提供差操作 但SQLSERVER2008提供了此操作 3 4 4集合查詢 例52 查詢計算機科學系的學生與年齡不大于19歲的學生的差集 3 4 4集合查詢 SELECT FROMStudentWHERESdept CS EXCEPTSELECT FROMStudentWHERESage 19 SELECT FROMStudentWHERESdept CS ANDSage 19 例53 查詢學生和教師不重名的學生姓名 3 4 4集合查詢 SELECTSnameFROMStudentEXCEPTSELECTTnameFROMTeacher SELECTsnameFROMStudentWHEREsnamenotin selectsnamefromteacher 3 4 4集合查詢 SELECT FROMStudentORDERBYSnoUNIONSELECT FROMTeacherORDERBYTno new SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacherORDERBYTname 4 對集合操作結果的排序 4 對集合操作結果的排序ORDERBY子句通常用數字序號指定排序屬性 只用于對最終查詢結果排序 不能對中間結果排序任何情況下 ORDERBY子句只能出現在最后 new 3 4 4集合查詢 SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacherORDERBYSname 1 SELECT語句的一般格式 SELECT ALL DISTINCT 別名 FROM 別名 別名 WHERE GROUPBY HAVING ORDERBY ASC DESC 3 4查詢小結 3 5數據更新 3 5 1插入數據3 5 2修改數據3 5 3刪除數據 3 5 1插入數據 兩種插入數據方式 插入單個元組插入子查詢結果 3 5 1插入數據 1 插入單個元組語句格式 INSERTINTO VALUES 功能 將新元組插入指定表中 3 5 1插入數據 INTO子句指定要插入數據的表名及屬性列屬性列順序與表定義中的順序可以不一致省略屬性列表 表示插入一條完整元組 且屬性列屬性與表定義順序一致指定部分屬性列 插入的元組在其余屬性列上取空值 省略屬性時要注意 VALUES子句提供的值必須與INTO子句中的屬性匹配值的個數 類型 3 5 1插入數據 注意 DBMS在執(zhí)行插入語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則實體完整性參照完整性用戶定義的完整性對于有NOTNULL約束對于有UNIQUE約束對于有值域約束 例1 將一個新學生記錄插入到Student表中 內容如下 95020 陳冬 男 IS 18歲 INSERTINTOStudentVALUES 0215128 陳冬 男 IS 18 3 5 1插入數據 例2 向SC表插入一條選課記錄 0215128 1 INSERTINTOSC Sno Cno VALUES 0215128 1 新插入的記錄在Grade列上取空值 3 5 1插入數據 2 插入子查詢結果語句格式INSERTINTO 子查詢 功能 將子查詢結果插入指定表中 3 5 1插入數據 例3 對每一個系 求學生的平均年齡 并把結果存入數據庫 首先建表 CREATETABLEDept avgage SdeptCHAR 15 定義系名 AvgageSMALLINT 定義平均年齡 3 5 1插入數據 例3 對每一個系 求