1、1 1北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節第第3 3章章 SQLSQL語言語言學而時習之，不亦樂乎。學而時習之，不亦樂乎。2022-6-272022-6-272 2北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節目目 錄錄嵌套子查詢嵌套子查詢 3.73.1簡單查詢簡單查詢 3.33.53.8聚合查詢聚合查詢 集合運算集合運算 SQL查詢一

2、般格式查詢一般格式 SQL數據定義語言數據定義語言 SQL數據更新語言數據更新語言 視圖視圖 3.23.43.63.93.10SQL概述概述 連接查詢連接查詢 2022-6-272022-6-273 3北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5嵌套子查詢嵌套子查詢n 在在SQL查詢中，一個查詢中，一個SELECT-FROM-WHERE查詢語句查詢語句稱為一個稱為一個查詢塊查詢塊n 將一個將一個查詢塊查詢塊嵌入到嵌入到另一個另一個查詢塊查詢塊的的WHERE子句子

3、句或或HAVING子句子句中，稱為中，稱為嵌套子查詢嵌套子查詢n 子查詢的結果是子查詢的結果是集合集合, 因此使用子查詢是因此使用子查詢是集合成員的檢查集合成員的檢查l如判斷元組是否屬于某個集合，集合的比較運算，以及測如判斷元組是否屬于某個集合，集合的比較運算，以及測試是否為空集等試是否為空集等l具體表現在如下幾個方面：具體表現在如下幾個方面：元素與集合間的元素與集合間的屬于屬于關系關系集合之間的集合之間的包含和相等包含和相等關系關系集合的集合的存在存在關系關系元素與集合元素之間的元素與集合元素之間的比較比較關系關系2022-6-272022-6-274 4北京理工大學珠海學院北京理工大學珠海

4、學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5嵌套子查詢嵌套子查詢n SQL允許多層嵌套子查詢，但在允許多層嵌套子查詢，但在子查詢中子查詢中，不允許不允許使用使用ORDER BY子句子句，該子句僅用于，該子句僅用于最后結果排序最后結果排序n 嵌套查詢分為嵌套查詢分為相關子查詢相關子查詢和和非相關子查詢非相關子查詢l非相關子查詢非相關子查詢指子查詢的結果不依賴于上層查詢指子查詢的結果不依賴于上層查詢l相關子查詢相關子查詢指當上層查詢的元組發生變化時，其子查詢指當上層查詢的元組發生變化時，其子查詢必須重

5、新執行必須重新執行n 3.5.1 使用使用IN的子查詢的子查詢n 3.5.2 使用使用比較運算符比較運算符的子查詢的子查詢n *3.5.3 使用使用存在量詞存在量詞EXISTS的子查詢的子查詢n *3.5.4 復雜查詢復雜查詢2022-6-272022-6-275 5北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢n 例例3.44 查詢查詢選修過課程選修過課程的學生的學生姓名姓名。n 本例查詢的含義是：本例查詢的含義是：l 在學生表

6、在學生表Student中，將學號出現在成績表中，將學號出現在成績表Score中中(表明該學生表明該學生選修過課程選修過課程)的學生姓名查詢出來的學生姓名查詢出來 SELECT studentName FROM Student WHERE Student.studentNo IN (SELECT Score.studentNo FROM Score)n 在本例中，在本例中，WHERE子句用于檢測元素與集合間的子句用于檢測元素與集合間的屬于屬于關系關系l 其中其中Student.studentNo為元素，為元素，IN為為“屬于屬于”l 嵌套語句嵌套語句“SELECT Score.studentNo

7、 FROM Score”的查詢結的查詢結果為選修過課程的所有學生的學號集合果為選修過課程的所有學生的學號集合l 該嵌套該嵌套SELECT語句稱為子查詢語句稱為子查詢2022-6-272022-6-276 6北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢n 該查詢屬于該查詢屬于非相關子查詢非相關子查詢，其查詢過程為：，其查詢過程為：(1) 從從Score表中查詢出學生的學號表中查詢出學生的學號studentNo，構成一個，構成一個中

8、中間結果關系間結果關系r；(2) 從從Student表中取出第一個表中取出第一個元組元組t；(3) 如果如果元組元組t的的studentNo屬性的值屬性的值包含在包含在中間結果關系中間結果關系r中中（即（即t.studentNor），則將），則將元組元組t的的studentName屬性的屬性的值值作為最終查詢結果關系的一個元組；否則作為最終查詢結果關系的一個元組；否則丟棄元組丟棄元組t；(4) 如果如果Student表中還有元組，則表中還有元組，則取取Student表的下一個元表的下一個元組組t，并轉第，并轉第(3)步；否則轉第步；否則轉第(5)步；步；(5) 將最終結果關系顯示出來。將最終結

9、果關系顯示出來。2022-6-272022-6-277 7北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢n 該查詢的執行過程可以通過圖該查詢的執行過程可以通過圖3-25來表示來表示圖圖3-252022-6-272022-6-278 8北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子

10、查詢的子查詢n 例例3.45 查找查找選修過課程名中包含選修過課程名中包含“系統系統”的課程的課程的同的同學學學號學號、姓名姓名和和班級編號班級編號。SELECT studentNo, studentName, classNoFROM StudentWHERE studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName LIKE %系統系統% ) )n WHERE子句中的子句中的IN可以實現多重嵌套，本例是一個三重嵌可以實現多重嵌套，本例是

11、一個三重嵌套的例子，該查詢的執行過程可以通過圖套的例子，該查詢的執行過程可以通過圖3-26來表示來表示學生表學生表與與課程表課程表無法無法直接關聯上，需要借直接關聯上，需要借助助成績表成績表！2022-6-272022-6-279 9北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢圖圖3-262022-6-272022-6-271010北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計

12、張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢n 該查詢也屬于該查詢也屬于非相關子查詢非相關子查詢n 使用使用IN的的非相關子查詢非相關子查詢的的查詢過程查詢過程歸納如下：歸納如下：l首先執行首先執行最底層的子查詢塊最底層的子查詢塊，將該子查詢塊的結果作為中，將該子查詢塊的結果作為中間關系；間關系；l執行執行上一層上一層(即外一層即外一層)查詢塊查詢塊，對于得到的每個元組，判，對于得到的每個元組，判斷該元組是否在它的子查詢結果中間關系中：斷該元組是否在它的子查詢結果中間關系中：如果在，取出該元組中的相關屬性作為最終輸出結果如果

13、在，取出該元組中的相關屬性作為最終輸出結果(或該或該查詢塊的查詢結果中間關系查詢塊的查詢結果中間關系)的一個元組的一個元組否則舍棄該元組否則舍棄該元組l如果已經執行完最上層查詢塊，則將最終結果作為一個新如果已經執行完最上層查詢塊，則將最終結果作為一個新關系輸出；否則返回第關系輸出；否則返回第(2)步重復執行步重復執行2022-6-272022-6-271111北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢n 例例3.46 查找查找

14、同時選修過同時選修過“計算機原理計算機原理”和和“高等數學高等數學”兩兩門課程門課程的同學的同學學號學號、姓名姓名以及該同學以及該同學所選修的所有課程的所選修的所有課程的課課程名程名和相應和相應成績成績，按學號，按學號(升序升序)、成績、成績(降序降序)排序輸出。排序輸出。n 分析：分析：l在在SELECT子句中必須包含子句中必須包含studentNo、studentName、courseName和和score四個屬性四個屬性l學號學號、姓名姓名在在學生表學生表中，課程中，課程成績成績在在成績表成績表中，中，課程名課程名在在課程表課程表中，在中，在FROM子句中必須包含子句中必須包含學生表學生

15、表、課程表課程表和和成績表成績表，分別為這三張表取元組變量，分別為這三張表取元組變量a、b、cl學生表學生表、成績表成績表和和課程表課程表需做連接操作，在需做連接操作，在WHERE子句子句中必須包含連接條件：中必須包含連接條件：a.studentNo=c.studentNo AND b.courseNo=c.courseNo2022-6-272022-6-271212北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢l要查詢要查詢同時

16、選修過同時選修過“計算機原理計算機原理”和和“高等數學高等數學”兩門兩門課程課程的同學，在的同學，在WHERE子句中必須包含如下的選擇條件子句中必須包含如下的選擇條件：對于對于學生表學生表，其，其學號學號必須是必須是選修過選修過“計算機原理計算機原理”課程課程的的學號學號，使用子查詢：使用子查詢：a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName=計算機原理計算機原理 ) )對于對于學生表學生表，其，其學號學號還必須是還必須是選

17、修過選修過“高等數學高等數學”課程課程的的學號學號，使用子查詢：使用子查詢：a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName=高等數學高等數學 ) ) 這兩個子查詢必須同時滿足，使用這兩個子查詢必須同時滿足，使用AND邏輯運算符邏輯運算符2022-6-272022-6-271313北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言

18、語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢l本查詢語句為：本查詢語句為：SELECT a.studentNo, studentName, courseName, scoreFROM Student a, Course b, Score cWHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName=

19、計算機原理計算機原理 ) ) AND a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName=高等數學高等數學 ) ) ORDER BY a.studentNo, score DESC2022-6-272022-6-271414北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子

20、查詢l該查詢也可以表示為如下形式：該查詢也可以表示為如下形式：SELECT a.studentNo, studentName, courseName, scoreFROM Student a, Course b, Score cWHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND a.studentNo IN ( SELECT studentNo FROM Score x, Course y WHERE x.courseNo=y.courseNo AND courseName=計算機原理計算機原理 ) AND a.student

21、No IN ( SELECT studentNo FROM Score x, Course y WHERE x.courseNo=y.courseNo AND courseName=高等數學高等數學 )ORDER BY a.studentNo, score DESC2022-6-272022-6-271515北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢n 例例3.47 查找查找同時選修過同時選修過“計算機原理計算機原理”和和“高

22、等數學高等數學”兩門課程兩門課程的同學的同學學號學號、姓名姓名以及以及所選修的這兩門課程所選修的這兩門課程的的課程名課程名和相應和相應成績成績，按學號，按學號(升序升序)、成績、成績(降序降序)排序排序輸出輸出n 分析：分析：l 只查詢該同學只查詢該同學所選修的這兩門課程的所選修的這兩門課程的課程名課程名和相應和相應成績成績，在，在WHERE子句中還必須包含選擇條件：子句中還必須包含選擇條件：課程名稱必須是課程名稱必須是“計計算機原理算機原理”或或“高等數學高等數學”，即即courseName=高等數學高等數學 OR courseName=計算機原理計算機原理2022-6-272022-6-2

23、71616北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.1 使用使用IN的子查詢的子查詢l 本查詢語句為：本查詢語句為：SELECT a.studentNo, studentName, courseName, scoreFROM Student a, Course b, Score cWHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND a.studentNo IN ( SELECT studen

24、tNo FROM Score x, Course y WHERE x.courseNo=y.courseNo AND courseName=計算機原理計算機原理 ) AND a.studentNo IN ( SELECT studentNo FROM Score x, Course y WHERE x.courseNo=y.courseNo AND courseName=高等數學高等數學 ) AND ( b.courseName=高等數學高等數學 OR b.courseName=計算機原理計算機原理 )ORDER BY a.studentNo, score DESCn 請將例請將例3.46、例

25、、例3.47的查詢要求及查詢語句的實現形式，與例的查詢要求及查詢語句的實現形式，與例3.29、例、例3.31進行比較。進行比較。2022-6-272022-6-271717北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.2 使用比較運算符的子查詢使用比較運算符的子查詢 n 元素與集合元素之間還存在更為復雜的關系，如比較關系，元素與集合元素之間還存在更為復雜的關系，如比較關系，常用到謂詞常用到謂詞ANY(或或SOME)和和ALLl ANY表示子查詢結果中的表示子查詢

26、結果中的某個值某個值l ALL表示子查詢結果中的表示子查詢結果中的所有值所有值 2022-6-272022-6-271818北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.2 使用比較運算符的子查詢使用比較運算符的子查詢n 注意：注意：l如果子查詢中的結果關系如果子查詢中的結果關系僅包含一個元組僅包含一個元組，則可將，則可將ALL和和ANY去掉，去掉，直接使用比較運算符直接使用比較運算符lANY也可以用也可以用SOME替代替代 2022-6-272022-6-27

27、1919北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.2 使用比較運算符的子查詢使用比較運算符的子查詢n 例例3.48 查詢所選修課程的成績查詢所選修課程的成績大于大于所有所有“002”號號課程成績的同學課程成績的同學學號學號及相應課程的及相應課程的課程號課程號和和成績成績。SELECT studentNo, courseNo, scoreFROM ScoreWHERE scoreALL ( SELECT score FROM Score WHERE cours

28、eNo=002 )2022-6-272022-6-272020北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.5.2 使用比較運算符的子查詢使用比較運算符的子查詢n 例例3.49 查詢年齡查詢年齡小于小于“計算機科學與技術計算機科學與技術07-01班班”某個某個同學同學年齡的所有同學的年齡的所有同學的學號學號、姓名姓名和和年齡年齡。SELECT studentNo, studentName, year(getdate()-year(birthday) AS ageFR

29、OM StudentWHERE year(getdate()-year(birthday)=28子查詢子查詢語句為：語句為：SELECT studentNo FROM Course x, Score yWHERE y.courseNo=x.courseNoGROUP BY studentNoHAVING sum(creditHour)=28本例將本例將分組聚合用于子查詢分組聚合用于子查詢中，該子查詢的含義是：中，該子查詢的含義是：查查詢詢選修了選修了28個以上個以上(含含28)學分學分的同學學號的同學學號2022-6-272022-6-274040北京理工大學珠海學院北京理工大學珠海學院 計算

30、機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節*3.5.4 復雜查詢復雜查詢在結果關系中的在結果關系中的學號學號必須是必須是子查詢子查詢中的學號，在中的學號，在WHERE子句中除了包含學生表、課程表、成績表的連接條件外子句中除了包含學生表、課程表、成績表的連接條件外，還必須有一個選擇條件，還必須有一個選擇條件該條件是該條件是學號學號必須是必須是子查詢子查詢結果集合中的學號結果集合中的學號由于由于Student 表中有學號，表中有學號，Score 表中也有學號，既可取表中也有學號，既可取Student 表中的學號表中

31、的學號a.studentNo，也可取，也可取Score 表中的學表中的學號號b.studentNo本例要求按學號排序輸出，需使用排序子句本例要求按學號排序輸出，需使用排序子句2022-6-272022-6-274141北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節*3.5.4 復雜查詢復雜查詢SELECT a.studentNo, studentName, courseName, score, creditHourFROM Student a, Course b, Sc

32、ore cWHERE a.studentNo=c.studentNo AND c.courseNo=b.courseNo AND a.studentNo IN ( SELECT studentNo FROM Course x, Score y WHERE y.courseNo=x.courseNo GROUP BY studentNo HAVING sum(creditHour)=28 ) ORDER BY a.studentNo 2022-6-272022-6-274242北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3

33、3 章章 SQL SQL語言語言_5-7_5-7節節*3.5.4 復雜查詢復雜查詢n 例例3.55 查詢查詢至少選修了至少選修了5門課程門課程且且課程平均分最高課程平均分最高的同學的的同學的學號學號和和課程平均分課程平均分。n 分析：分析：查詢同學的查詢同學的學號學號和和課程平均分課程平均分，使用，使用求平均值的聚合函數求平均值的聚合函數，在，在SELECT子句中包含子句中包含學號學號 studentNo和和課程平均分課程平均分avg(score)本例只要使用成績表，在本例只要使用成績表，在FROM子句中僅包含子句中僅包含成績表成績表查詢查詢至少選修了至少選修了5門課程門課程且且課程平均分最高

34、課程平均分最高的同學的的同學的學號學號，使，使用用分組運算分組運算，分組屬性為學號分組屬性為學號必須對必須對分組后的結果進行選擇運算分組后的結果進行選擇運算，選擇選擇至少選修了至少選修了5門課程門課程且且課程平均分最高課程平均分最高的同學的同學的的學號學號，使用，使用HAVING子句子句HAVING count(*)=5 -選課數大于等于選課數大于等于5第二個條件是第二個條件是課程平均分最高課程平均分最高，可按如下步驟構造：，可按如下步驟構造：2022-6-272022-6-274343北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申

35、勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節*3.5.4 復雜查詢復雜查詢首先構造一個首先構造一個查詢表查詢表，用于查詢，用于查詢至少選修了至少選修了5門課程門課程的同學的的同學的學號學號和和課程平均分課程平均分，該查詢表僅涉及成績表，語句為：，該查詢表僅涉及成績表，語句為：SELECT studentNo, avg(score) avgScoreavgScoreFROM ScoreGROUP BY studentNoHAVING count(*)=5再構造一個再構造一個子查詢子查詢，用于從上述，用于從上述查詢表查詢表中查詢出中查詢出最高的平均分最高的平均分子查詢子查詢為：

36、為：SELECT max(avgScoreavgScore)FROM ( SELECT studentNo, avg(score) avgScoreavgScore FROM Score GROUP BY studentNo HAVING count(*)=5) b因此，在上述因此，在上述HAVING子句中的第二個條件子句中的第二個條件就是：就是： 平均分平均分等于等于子查詢子查詢中查詢出來的中查詢出來的最高的平均分最高的平均分2022-6-272022-6-274444北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3

37、章章 SQL SQL語言語言_5-7_5-7節節*3.5.4 復雜查詢復雜查詢該查詢語句為：該查詢語句為：SELECT studentNo, avg(score) avgScoreFROM ScoreGROUP BY studentNoHAVING count(*)=5 AND avg(score)= ( SELECT max(avgScoreavgScore) FROM ( SELECT studentNo, avg(avg(scorescore) ) avgScoreavgScore FROM Score GROUP BY studentNo HAVING count(*)=5) b )l

38、 注意：在注意：在查詢表查詢表中，如果查詢的列是表達式，可以給該表達中，如果查詢的列是表達式，可以給該表達式取一個式取一個別名別名，這樣在，這樣在SELECT語句中就可直接使用該別名語句中就可直接使用該別名在本例的在本例的查詢表查詢表中，將表達式中，將表達式avg(avg(ScoreScore) )取別名取別名avgScoreavgScore2022-6-272022-6-274545北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節*3.5.4 復雜查詢復雜查詢n 例例3

39、.56 查詢查詢選修了所有選修了所有4學分課程學分課程(即學分為即學分為4的課的課程程)的同學的的同學的學號學號、姓名姓名以及以及所選修所選修4學分課程學分課程的的課課程名程名和和成績成績。n 分析：分析：與例與例3.52、例、例3.53類似，本例也要使用雙重否定。在第一類似，本例也要使用雙重否定。在第一重否定中重否定中查詢查詢4學分的課程學分的課程，在第二重否定中，在第二重否定中查詢某學生查詢某學生選修某門選修某門4學分的課程學分的課程該查詢表達的含義是：查詢這樣的學生，該查詢表達的含義是：查詢這樣的學生，不存在某門不存在某門4學學分的課程分的課程他沒有選修他沒有選修2022-6-27202

40、2-6-274646北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節*3.5.4 復雜查詢復雜查詢該查詢語句為：該查詢語句為： SELECT a.studentNo, studentName, courseName, score FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND NOT EXISTS ( SELECT * FROM Co

41、urse x WHERE creditHour=4 -查詢查詢4學分課程的情況學分課程的情況 AND NOT EXISTS -判斷學生判斷學生a.studentNo沒有選修課程沒有選修課程x.courseNo ( SELECT * FROM Score WHERE studentNo=a.studentNo AND courseNo=x.courseNo ) ) AND creditHour=4 -只顯示滿足上述要求的學生只顯示滿足上述要求的學生所選修所選修4學分課程學分課程的的課程名課程名和和成績成績2022-6-272022-6-274747北京理工大學珠海學院北京理工大學珠海學院 計算機

42、學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節目目 錄錄嵌套子查詢嵌套子查詢 3.73.1簡單查詢簡單查詢 3.33.53.8聚合查詢聚合查詢 集合運算集合運算 SQL查詢一般格式查詢一般格式 SQL數據定義語言數據定義語言 SQL數據更新語言數據更新語言 視圖視圖 3.23.43.63.93.10SQL概述概述 連接查詢連接查詢 2022-6-272022-6-274848北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL

43、語言語言_5-7_5-7節節3.6 集合運算集合運算 n SQL支持集合運算支持集合運算n SELECT語句查詢的結果是集合語句查詢的結果是集合n 傳統的集合操作主要包括并傳統的集合操作主要包括并UNION、交、交INTERSECT、差、差EXCEPT運算運算n 在執行集合運算時要求參與運算的查詢結果的在執行集合運算時要求參與運算的查詢結果的列數列數一樣，其對應列的數據類型必須一致一樣，其對應列的數據類型必須一致2022-6-272022-6-274949北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL S

44、QL語言語言_5-7_5-7節節3.6 集合運算集合運算n 例例3.57 查詢查詢“信息管理學院信息管理學院”1990年出生的同學年出生的同學的學號、出生日期、班級名稱和所屬學院以及的學號、出生日期、班級名稱和所屬學院以及“會會計學院計學院”1991年出生的同學的學號、出生日期、班年出生的同學的學號、出生日期、班級名稱和所屬學院。級名稱和所屬學院。SELECT studentNo, birthday, className, instituteFROM Student a, Class bWHERE a.classNo=b.classNo AND year(birthday)=1990 AND

45、institute=信息管理學院信息管理學院UNIONSELECT studentNo, birthday, className, instituteFROM Student a, Class bWHERE a.classNo=b.classNo AND year(birthday)=1991 AND institute=會計學院會計學院2022-6-272022-6-275050北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.6 集合運算集合運算l 該查詢實際上是查

46、詢該查詢實際上是查詢“信息管理學院信息管理學院”1990年出生的年出生的或或“會計學院會計學院”1991年出生的同學的學號、出生日期年出生的同學的學號、出生日期、班級名稱和所屬學院，上述、班級名稱和所屬學院，上述SQL語句可以改寫為：語句可以改寫為：SELECT studentNo, birthday, className, instituteFROM Student a, Class bWHERE a.classNo=b.classNo AND ( year(birthday)=1990 AND institute=信息管理學院信息管理學院 OR year(birthday)=1991 AN

47、D institute=會計學院會計學院 )ORDER BY institute2022-6-272022-6-275151北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.6 集合運算集合運算n 例例3.58 查詢同時選修了查詢同時選修了“001”號和號和“005”號課程號課程的同學的學號和姓名的同學的學號和姓名SELECT a.studentNo, studentNameFROM Student a, Score bWHERE a.studentNo=b.stude

48、ntNo AND courseNo=001INTERSECTSELECT a.studentNo, studentNameFROM Student a, Score bWHERE a.studentNo=b.studentNo AND courseNo=0052022-6-272022-6-275252北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.6 集合運算集合運算n 例例3.58 查詢同時選修了查詢同時選修了“001”號和號和“005”號課程號課程的同學的學號和

49、姓名的同學的學號和姓名l 本例也可用下面的本例也可用下面的SQL語句實現語句實現SELECT a.studentNo, studentNameFROM Student a, Score bWHERE a.studentNo=b.studentNo AND courseNo=001 AND a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo=005 ) l 注意：注意：SQL Server數據庫不支持交運算數據庫不支持交運算INTERSECT，交運，交運算完全可以用其它運算替代算完全可以用其它運算替代2022-6-272022-

50、6-275353北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.6 集合運算集合運算n 例例3.59 查詢沒有選修查詢沒有選修“計算機原理計算機原理”課程的同學課程的同學的學號和姓名。的學號和姓名。SELECT studentNo, studentNameFROM StudentEXCEPTSELECT DISTINCT a.studentNo, studentNameFROM Student a, Score b, Course cWHERE a.studentNo

51、=b.studentNo AND b.courseNo=c.courseNo AND courseName=計算機原理計算機原理2022-6-272022-6-275454北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.6 集合運算集合運算n 例例3.59 查詢沒有選修查詢沒有選修“計算機原理計算機原理”課程的同學課程的同學的學號和姓名。的學號和姓名。l 本例也可用下面的本例也可用下面的SQL語句實現語句實現SELECT studentNo, studentNameF

52、ROM StudentWHERE studentNo NOT IN ( SELECT studentNo FROM Score x, Course y WHERE x.courseNo=y.courseNo AND courseName=計算機原理計算機原理 ) l 注意：注意：SQL Server數據庫不支持差運算數據庫不支持差運算EXCEPT，差運算完全可以用其它運算替代差運算完全可以用其它運算替代2022-6-272022-6-275555北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語

53、言_5-7_5-7節節目目 錄錄嵌套子查詢嵌套子查詢 3.73.1簡單查詢簡單查詢 3.33.53.8聚合查詢聚合查詢 集合運算集合運算 SQL查詢一般格式查詢一般格式 SQL數據定義語言數據定義語言 SQL數據更新語言數據更新語言 視圖視圖 3.23.43.63.93.10SQL概述概述 連接查詢連接查詢 2022-6-272022-6-275656北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.7SQL查詢一般格式查詢一般格式n SELECT共有共有6個子句，其中

54、個子句，其中SELECT和和FROM是必是必須的，其它是可選項，須的，其它是可選項，必須嚴格按照如下順序排列必須嚴格按照如下順序排列：SELECT ALL | DISTINCT AS , AS , . FROM AS , AS , . WHERE GROUP BY , , . HAVING ORDER BY ASC | DESC , ASC | DESC, . 2022-6-272022-6-275757北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.7SQL查詢一般

55、格式查詢一般格式n其中：其中：(1) 可以是下面的可選格式：可以是下面的可選格式： .* . (2) FROM子句子句指定查詢所涉及的表、視圖或查詢表指定查詢所涉及的表、視圖或查詢表. 為操作方便，常給表取一個為操作方便，常給表取一個別名別名，稱為，稱為元組變量元組變量2022-6-272022-6-275858北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.7SQL查詢一般格式查詢一般格式(3) WHERE子句子句給出查詢條件，隨后的給出查詢條件，隨后的中可以使用下

56、面的謂詞運算符：中可以使用下面的謂詞運算符： 比較運算符：比較運算符：，=，=，=，!=； 邏輯運算符：邏輯運算符：AND，OR，NOT； 范圍運算符：范圍運算符：NOT BETWEEN.AND； 集合運算符：集合運算符：NOT IN； 空值運算符：空值運算符：IS NOT null； 字符匹配運算符：字符匹配運算符：NOT LIKE； 存在量詞運算符：存在量詞運算符：NOT EXISTS。l 在在WHERE 中可以包含中可以包含子查詢子查詢，但，但不可不可以直接使用聚合函數以直接使用聚合函數，若要使用聚合函數，必須引出，若要使用聚合函數，必須引出一個子查詢，如例一個子查詢，如例3.52所示。所示。2022-6-272022-6-275959北京理工大學珠海學院北京理工大學珠海學院 計算機學院計算機學院 數據庫原理與設計數據庫原理與設計 張申勇張申勇第第 3 3 章章 SQL SQL語言語言_5-7_5-7節節3.7SQL查詢一般格式查詢一般格式l 例例3.60 查詢每一個同學的查詢每一個同學的學號學號以及以及該同學該同學所修課所修課程中程中成績最高成績最高的課程的課程的的課程號課程號和相應和相應成績成績。SELECT studentNo, co