介紹多表查詢等復(fù)雜SQL語句处面。

關(guān)系數(shù)據(jù)庫的查詢結(jié)果都是一個結(jié)果表（也是關(guān)系）

集聚函數(shù)

基本語法

• 統(tǒng)計元組個數(shù)
  • COUNT(*)
• 統(tǒng)計一列中值的個數(shù)
  • COUNT([DISTINCT|ALL]<列名>)
• 計算一列值的總和（此列必須為數(shù)值型）
  • SUM([DISTINCT|ALL]<列名>)
• 計算一列值的平均值（此列必須為數(shù)值型）
  • AVG([DISTINCT|ALL]<列名>)
• 求一列中的最大值和最小值
  • MAX([DISTINCT|ALL]<列名>)
  • MIN([DISTINCT|ALL]<列名>)

例子

• 查詢選修1號課程的學(xué)生最高分?jǐn)?shù)

  • SELECTMAX(Grade)
       FROM SC
       WHERE Cno='1';
    

• 查詢學(xué)生201215012選修課程的總學(xué)分?jǐn)?shù)

  • SELECT SUM(Ccredit)
        FROM  SC,Course
        WHERE Sno='201215012'                
        AND SC.Cno=Course.Cno; 
    

GROUP BY 子句

細(xì)化聚集函數(shù)的作用對象

• 如果未對查詢結(jié)果分組匕垫，聚集函數(shù)將作用于整個查詢結(jié)果
• 對查詢結(jié)果分組后，聚集函數(shù)將分別作用于每個組
• 按指定的一列或多列值分組睡腿，值相等的為一組

HAVING短語與WHERE子句的區(qū)別：

• 作用對象不同
• WHERE子句作用于基表或視圖，從中選擇滿足條件的元組
• HAVING短語作用于組捍歪，從中選擇滿足條件的組
• WHERE子句不能使用聚合函數(shù)畅姊！

例子

1. 求各個課程號及相應(yīng)的選課人數(shù)

      SELECT Cno, COUNT(Sno)
      FROM    SC
      GROUP BY Cno; 
   

2. 查詢選修了3門以上課程的學(xué)生學(xué)號

    SELECT Sno
        FROM  SC
        GROUP BY Sno
        HAVING  COUNT(*) >3;      
   

3. 查詢平均成績大于等于90分的學(xué)生學(xué)號和平均成績

       SELECT  Sno, AVG(Grade)
       FROM  SC
       GROUP BY Sno
       HAVING AVG(Grade)>=90;
   

   這里只能使用HAVING，不能使用WHERE卢肃。

ORDER BY子句

• 可以按一個或多個屬性列排序
  • 優(yōu)先級逐漸降低
• 升序：ASC;
• 降序：DESC;
• 缺省值為升序
• 對于空值疲迂，排序時顯示的次序由具體系統(tǒng)實(shí)現(xiàn)來決定

例子

1. 查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)降序排列

       SELECT Sno, Grade
       FROM    SC
       WHERE  Cno= ' 3 '
       ORDER BY Grade DESC;
   

2. 查詢?nèi)w學(xué)生情況莫湘，查詢結(jié)果按所在系的系號升序排列尤蒿，同一系中的學(xué)生按年齡降序排列

       SELECT  *
       FROM  Student
       ORDER BY Sdept, Sage DESC;  
   

連接查詢

• 連接查詢：同時涉及兩個以上的表的查詢

• 連接條件或連接謂詞：用來連接兩個表的條件

• 一般格式：

  [<表名1>.]<列名1> <比較運(yùn)算符> [<表名2>.]<列名2>

  [<表名1>.]<列名1>BETWEEN [<表名2>.]<列名2>AND[<表名2>.]<列名3>

• 連接字段：連接謂詞中的列名稱

  • 連接條件中的各連接字段類型必須是可比的，但名字不必相同

（非）等值連接查詢

等值連接：連接運(yùn)算符為=幅垮，這里與Join操作等價腰池。

例子

1. 查詢每個學(xué)生及其選修課程的情況

       SELECT  Student.*, SC.*
       FROM     Student, SC
       WHERE  Student.Sno = SC.Sno;
   

2. 一條SQL語句可以同時完成選擇和連接查詢，這時WHERE子句是由連接謂詞和選擇謂詞組成的復(fù)合條件军洼。

   查詢選修2號課程且成績在90分以上的所有學(xué)生的學(xué)號和姓名

       SELECT  Student.Sno, Sname
       FROM     Student, SC
       WHERE  Student.Sno=SC.Sno  AND                          
              SC.Cno=' 2 ' AND SC.Grade>90;
   

執(zhí)行過程

嵌套循環(huán)法（NESTED-LOOP）

• 首先在表1中找到第一個元組巩螃，然后從頭開始掃描表2，逐一查找滿足連接件的元組匕争，找到后就將表1中的第一個元組與該元組拼接起來避乏，形成結(jié)果表中一個元組。
• 表2全部查找完后甘桑，再找表1中第二個元組拍皮，然后再從頭開始掃描表2歹叮，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來铆帽，形成結(jié)果表中一個元組咆耿。
• 重復(fù)上述操作，直到表1中的全部元組都處理完畢

可以發(fā)現(xiàn)爹橱，等值連接的復(fù)雜度很高萨螺，為O(m* n)。

自身連接

• 自身連接：一個表與其自己進(jìn)行連接
• 需要給表起別名以示區(qū)別
• 由于所有屬性名都是同名屬性愧驱，因此必須使用別名前綴

例子

1. 查詢每一門課的間接先修課（即先修課的先修課）

    SELECT  FIRST.Cno, SECOND.Cpno
        FROM  Course  FIRST, Course  SECOND
        WHERE FIRST.Cpno = SECOND.Cno;
   

外連接

外連接與普通連接的區(qū)別

• 普通連接操作只輸出滿足連接條件的元組
• 外連接操作以指定表為連接主體慰技，將主體表中不滿足連接條件的元組一并輸出
• 左外連接
  • 列出左邊關(guān)系中所有的元組
• 右外連接
  • 列出右邊關(guān)系中所有的元組

例子

    SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
    FROM  Student  LEFT OUT JOIN SC ON    
                 (Student.Sno=SC.Sno); 

53960143076

多表連接

兩個以上的表進(jìn)行連接。

MongoDB不提供這種操作：

• JOIN很慢
• 多級擴(kuò)展能力差组砚，代價太高

例子

1. 查詢每個學(xué)生的學(xué)號吻商、姓名、選修的課程名及成績

     SELECT Student.Sno, Sname, Cname, Grade
     FROM   Student, SC, Course    /*多表連接*/
     WHERE Student.Sno = SC.Sno 
                  AND SC.Cno = Course.Cno;
   

嵌套查詢

• 一個SELECT-FROM-WHERE語句稱為一個查詢塊

• 將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢

• 上層的查詢塊稱為外層查詢或父查詢

• 下層查詢塊稱為內(nèi)層查詢或子查詢

• SQL語言允許多層嵌套查詢

  • 即一個子查詢中還可以嵌套其他子查詢

• 子查詢的限制

  • 不能使用ORDERBY子句
  • 因為ORDER BY 結(jié)果為有序的糟红，不滿足關(guān)系的定義艾帐，只能作為最后的生成結(jié)果

帶有IN謂詞的子查詢

1. 查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)聲

   SELECT Sno, Sname, Sdept
            FROM Student
            WHERE Sdept  IN
                     (SELECT Sdept
                      FROM Student
                      WHERE Sname= ' 劉晨 ');
   /*用自身連接表示*/
    SELECT  S1.Sno, S1.Sname,S1.Sdept
         FROM     Student S1,Student S2
         WHERE  S1.Sdept = S2.Sdept  AND
              S2.Sname = '劉晨';
   

2. 查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名

   SELECT Sno,Sname                
        FROM    Student                         
        WHERE Sno  IN
                (SELECT Sno                    
                 FROM    SC                         
                 WHERE  Cno IN
                        (SELECT Cno             
                          FROM Course           
                          WHERE Cname= '信息系統(tǒng)' )
                 );
   /*用連接查詢表示*/
   SELECT Sno,Sname
         FROM    Student,SC,Course
         WHERE   Student.Sno = SC.Sno  AND
                 SC.Cno = Course.Cno AND
                 Course.Cname='信息系統(tǒng)';            
   

帶有比較運(yùn)算符的子查詢

• 當(dāng)能確切知道內(nèi)層查詢返回單值時，可用比較運(yùn)算符（>盆偿，<柒爸，=，>=事扭，<=揍鸟，!=或< >）。

• 由于一個學(xué)生只可能在一個系學(xué)習(xí)句旱，則可以用 = 代替IN ：

      SELECT Sno,Sname,Sdept
      FROM    Student
      WHERE Sdept   =
                     (SELECT Sdept
                      FROM    Student
                      WHERE Sname= '劉晨');
  

• 注意，用比較運(yùn)算符取嵌套晰奖，只能SELECT一個屬性谈撒，且為數(shù)值類型。

• 不相關(guān)子查詢

  • 子查詢的查詢條件不依賴于父查詢
  • 由里向外逐層處理匾南。即每個子查詢在上一級查詢處理之前求解啃匿，子查詢的結(jié)果用于建立其父查詢的查找條件。

• 相關(guān)子查詢

  • 子查詢的查詢條件依賴于父查詢
  • 首先取外層查詢中表的第一個元組蛆楞，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢溯乒，若WHERE子句返回值為真，則取此元組放入結(jié)果表
  • 然后再取外層表的下一個元組
  • 重復(fù)這一過程豹爹，直至外層表全部檢查完為止

例子

1. 找出每個學(xué)生超過他選修課程平均成績的課程號

   SELECT Sno, Cno
       FROM    SC x
       WHERE Grade >= ( SELECT AVG(Grade) 
                        FROM  SC y
                        WHERE y.Sno = x.Sno );
   /*用連接查詢表示*/
   SELECT First.Sno, First.Cno
    FROM SC First JOIN (
        SELECT Sno, AVG(Grade) as A_Grade
           FROM SC
           GROUP BY Sno) SA
           ON First.Sno = SA.Sno
        WHERE First.Grade > SA.A_Grade
   

帶有ANY（SOME）或ALL謂詞的子查詢

使用ANY或ALL謂詞時必須同時使用比較運(yùn)算

若子查詢中不是唯一的裆悄，使用ANY/ALL可以使用比較運(yùn)算符

語義為：

> ANY 大于子查詢結(jié)果中的某個值

>ALL 大于子查詢結(jié)果中的所有值

>=ANY 大于等于子查詢結(jié)果中的某個值

<=ANY 小于等于子查詢結(jié)果中的某個值

=ANY 等于子查詢結(jié)果中的某個值

!=（或<>）ALL 不等于子查詢結(jié)果中的任何一個值

例子

1. 查詢非計算機(jī)科學(xué)系中比計算機(jī)科學(xué)系任意一個學(xué)生年齡小的學(xué)生姓名和年齡

   SELECT Sname,Sage
       FROM    Student
       WHERE Sage < ANY ( SELECT  Sage
                          FROM    Student
                          WHERE Sdept= ' CS ')
        AND Sdept <> ‘CS ' ;           /*父查詢塊中的條件 */
   
   /*用聚集函數(shù)實(shí)現(xiàn)*/
   
   SELECT Sname,Sage
        FROM   Student
        WHERE Sage < 
                     ( SELECT MAX（Sage）
                        FROM Student
                        WHERE Sdept= 'CS ')
         AND Sdept <> ' CS ';
   

2. 查詢非計算機(jī)科學(xué)系中比計算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡

   SELECT Sname,Sage
       FROM Student
       WHERE Sage < ALL
                    (SELECT Sage
                      FROM Student
                      WHERE Sdept= ' CS ')
         AND Sdept <> ' CS ’;
   /*用聚集函數(shù)實(shí)現(xiàn)*/
   SELECT Sname,Sage
       FROM Student
       WHERE Sage < 
                  (SELECT MIN(Sage)
                   FROM Student
                   WHERE Sdept= ' CS ')
       AND Sdept <>' CS ';
   

帶有EXISTS謂詞的子查詢

EXISTS謂詞

• 存在量詞
• 帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”臂聋。
• 若內(nèi)層查詢結(jié)果非空光稼，則外層的WHERE子句返回真值
• 若內(nèi)層查詢結(jié)果為空或南，則外層的WHERE子句返回假值
• 由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用* 艾君，因為帶EXISTS的子查詢只返回真值或假值采够，給出列名無實(shí)際意義。

例子

1. 查詢所有選修了1號課程的學(xué)生姓名冰垄。

   思路

   • 本查詢涉及Student和SC關(guān)系
   • 在Student中依次取每個元組的Sno值蹬癌，用此值去檢查SC表
   • 若SC中存在這樣的元組，其Sno值等于此Student.Sno值虹茶，并且其Cno=‘1’逝薪，則取此Student.Sname送入結(jié)果表

   SELECT Sname
        FROM Student
        WHERE EXISTS
                      (SELECT *
                       FROM SC
                       WHERE Sno=Student.Sno AND Cno= ' 1 ');
   

2. 查詢沒有選修1號課程的學(xué)生姓名。

   SELECT Sname
        FROM     Student
        WHERE NOT EXISTS
                      (SELECT *
                       FROM SC
                       WHERE Sno = Student.Sno AND    Cno='1');
   

難點(diǎn)

• 不同形式的查詢間的替換

  • 一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換

  • 所有帶IN謂詞写烤、比較運(yùn)算符翼闽、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換

  • 查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生

    • 可以用帶EXISTS謂詞的子查詢替換

    • SELECT Sno,Sname,Sdept
           FROM Student S1
            WHERE EXISTS
                   　   (SELECT *
                           FROM Student S2
                           WHERE S2.Sdept = S1.Sdept AND
                                 S2.Sname = '劉晨');
      

• 用EXISTS/NOT EXISTS實(shí)現(xiàn)全稱量詞（難點(diǎn)）

  • 查詢選修了全部課程的學(xué)生姓名

    • 不存在一門課,這個學(xué)生沒有選

    • SELECT Sname
              FROM Student
              WHERE NOT EXISTS
                            (SELECT *
                              FROM Course
                              WHERE NOT EXISTS
                                            (SELECT *
                                             FROM SC
                                             WHERE Sno= Student.Sno
                                                   AND Cno= Course.Cno
                                            )
                             );