資料庫 - 連線查詢、巢狀查詢、集合查詢

Simon.Wang發表於2015-05-05
資料庫巢狀

連線查詢

連線查詢：同時涉及多個表的查詢
連線條件或連線謂詞：用來連線兩個表的條件

     一般格式：
[<表名1>.]<列名1>  <比較運算子>  [<表名2>.]<列名2>
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

連線欄位：連線謂詞中的列名稱
連線條件中的各連線欄位型別必須是可比的，但名字不必是相同的

巢狀迴圈法(NESTED-LOOP)

首先在表1中找到第一個元組，然後從頭開始掃描表2，逐一查詢滿足連線件的元組，找到後就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。
表2全部查詢完後，再找表1中第二個元組，然後再從頭開始掃描表2，逐一查詢滿足連線條件的元組，找到後就將表1中的第二個元組與該元組拼接起來，形成結果表中一個元組。
重複上述操作，直到表1中的全部元組都處理完畢

排序合併法(SORT-MERGE)

常用於=連線
首先按連線屬性對錶1和表2排序
對錶1的第一個元組，從頭開始掃描表2，順序查詢滿足連線條件的元組，找到後就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。當遇到表2中第一條大於表1連線欄位值的元組時，對錶2的查詢不再繼續

找到表1的第二條元組，然後從剛才的中斷點處繼續順序掃描表2，查詢滿足連線條件的元組，找到後就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。直接遇到表2中大於表1連線欄位值的元組時，對錶2的查詢不再繼續
重複上述操作，直到表1或表2中的全部元組都處理完畢為止

索引連線(INDEX-JOIN)

對錶2按連線欄位建立索引
對錶1中的每個元組，依次根據其連線欄位值查詢表2的索引，從中找到滿足條件的元組，找到後就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組

等值與非等值連線查詢

等值連線：連線運算子為=
[例33]  查詢每個學生及其選修課程的情況
                  SELECT  Student.*，SC.*
                    FROM     Student，SC
            WHERE  Student.Sno = SC.Sno；

自然連線：

[例34]     對[例33]用自然連線完成。
 SELECT  Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade
 FROM     Student，SC
 WHERE  Student.Sno = SC.Sno；

自身連線：一個表與其自己進行連線
需要給表起別名以示區別
由於所有屬性名都是同名屬性，因此必須使用別名字首

[例35]查詢每一門課的間接先修課（即先修課的先修課）
    SELECT  FIRST.Cno，SECOND.Cpno
     FROM  Course  FIRST，Course  SECOND
     WHERE FIRST.Cpno = SECOND.Cno；

外連線與普通連線的區別
普通連線操作只輸出滿足連線條件的元組
外連線操作以指定表為連線主體，將主體表中不滿足連線條件的元組一併輸出
[例 36] 改寫[例33]
   SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade
    FROM  Student  LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)； 

複合條件連線：WHERE子句中含多個連線條件

[例37]查詢選修2號課程且成績在90分以上的所有學生     
        SELECT Student.Sno, Sname
        FROM    Student, SC
        WHERE Student.Sno = SC.Sno AND   
                                            /* 連線謂詞*/
                         SC.Cno= ‘2’ AND SC.Grade > 90；       
                    /* 其他限定條件 */

[例38]查詢每個學生的學號、姓名、選修的課程名及成績
  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短語的條件中的查詢稱為巢狀查詢


     SELECT Sname                         /*外層查詢/父查詢*/
     FROM Student
     WHERE Sno IN
                        （SELECT Sno             /*內層查詢/子查詢*/
                            FROM SC
                            WHERE Cno= ' 2 '）；

子查詢的限制
不能使用ORDER BY子句
層層巢狀方式反映了 SQL語言的結構化
有些巢狀查詢可以用連線運算替代
不相關子查詢：
子查詢的查詢條件不依賴於父查詢
由裡向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結果用於建立其父查詢的查詢條件。

巢狀查詢求解方法

相關子查詢：子查詢的查詢條件依賴於父查詢
首先取外層查詢中表的第一個元組，根據它與內層查詢相關的屬性值處理內層查詢，若WHERE子句返回值為真，則取此元組放入結果表
然後再取外層表的下一個元組
重複這一過程，直至外層表全部檢查完為止

帶有IN謂詞的子查詢

[例39]  查詢與“劉晨”在同一個系學習的學生。
         此查詢要求可以分步來完成
    ① 確定“劉晨”所在系名             
        SELECT  Sdept  
         FROM     Student                            
         WHERE  Sname= ' 劉晨 '；
        結果為： CS

將第一步查詢嵌入到第二步查詢的條件中
    SELECT Sno，Sname，Sdept
        FROM Student
    WHERE Sdept  IN
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname= ‘ 劉晨 ’)；
    此查詢為不相關子查詢。

 用自身連線完成[例39]查詢要求
     SELECT  S1.Sno，S1.Sname，S1.Sdept
      FROM     Student S1，Student S2
      WHERE  S1.Sdept = S2.Sdept  AND
                      S2.Sname = '劉晨'；

[例40]查詢選修了課程名為“資訊系統”的學生學號和姓名
    SELECT Sno，Sname                      ③ 最後在Student關係中
    FROM    Student                                    取出Sno和Sname
    WHERE Sno  IN
             (SELECT Sno                             ② 然後在SC關係中找出選
              FROM    SC                                    修了3號課程的學生學號
              WHERE  Cno IN
                     (SELECT Cno                      ① 首先在Course關係中找出
                       FROM Course                    “資訊系統”的課程號，為3號
                       WHERE Cname= ‘資訊系統’
                     )
              );

用連線查詢實現[例40]
     SELECT Sno，Sname
      FROM    Student，SC，Course
      WHERE Student.Sno = SC.Sno  AND
                     SC.Cno = Course.Cno AND
                     Course.Cname=‘資訊系統’；

帶有比較運算子的子查詢

當能確切知道內層查詢返回單值時，可用比較運算子（>，<，=，>=，<=，!=或< >）。
與ANY或ALL謂詞配合使用

例：假設一個學生只可能在一個系學習，並且必須屬於一個系，則在[例39]可以用 = 代替IN ：
     SELECT Sno，Sname，Sdept
     FROM    Student
     WHERE Sdept   =
                   (SELECT Sdept
                    FROM    Student
                    WHERE Sname= ‘劉晨’)；

 子查詢一定要跟在比較符之後
    錯誤的例子：
     SELECT  Sno，Sname，Sdept
     FROM     Student
     WHERE ( SELECT Sdept
                       FROM Student
                       WHERE Sname= ‘ 劉晨 ’ ) 
                       = Sdept；

［例41］找出每個學生超過他選修課程平均成績的課程號。
   SELECT Sno， Cno
    FROM  SC  x
    WHERE Grade >=(SELECT AVG(Grade) 
                                FROM  SC y
                                   WHERE y.Sno=x.Sno);

可能的執行過程： 
1. 從外層查詢中取出SC的一個元組x，將元組x的Sno值（200215121）傳送給內層查詢。
       SELECT AVG(Grade)
       FROM SC y
       WHERE y.Sno='200215121';
2. 執行內層查詢，得到值88（近似值），用該值代替內層查詢，得到外層查詢：
       SELECT Sno， Cno
       FROM  SC x
       WHERE Grade >=88； 

3. 執行這個查詢，得到
    （200215121，1）
    （200215121，3） 
4.外層查詢取出下一個元組重複做上述1至3步驟，直到外層的SC元組全部處理完畢。結果為:
    （200215121，1）
    （200215121，3）
    （200215122，2）

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

謂詞語義
ANY：任意一個值
ALL：所有值

需要配合使用比較運算子
> ANY   大於子查詢結果中的某個值       
 > ALL  大於子查詢結果中的所有值
< ANY   小於子查詢結果中的某個值    
< ALL   小於子查詢結果中的所有值
>= ANY  大於等於子查詢結果中的某個值    
>= ALL  大於等於子查詢結果中的所有值
<= ANY  小於等於子查詢結果中的某個值    
<= ALL  小於等於子查詢結果中的所有值
= ANY   等於子查詢結果中的某個值        
=ALL    等於子查詢結果中的所有值（通常沒有實際意義）
!=（或<>）ANY  不等於子查詢結果中的某個值
!=（或<>）ALL  不等於子查詢結果中的任何一個值

[例42]  查詢其他系中比電腦科學某一學生年齡小的學生姓名和年齡
    SELECT Sname，Sage
    FROM    Student
    WHERE Sage < ANY (SELECT  Sage
                                         FROM    Student
                                         WHERE Sdept= ' CS ')
           AND Sdept <> ‘CS ' ;           /*父查詢塊中的條件 */

用聚集函式實現[例42] 

    SELECT Sname，Sage
     FROM   Student
     WHERE Sage < 
                             (SELECT MAX(Sage)
                               FROM Student
                               WHERE Sdept= ‘CS ')
           AND Sdept <> ' CS ’;

[例43]  查詢其他系中比電腦科學系所有學生年齡都小的學生姓名及年齡。

方法一：用ALL謂詞
    SELECT Sname，Sage
    FROM Student
    WHERE Sage < ALL
                           (SELECT Sage
                            FROM Student
                            WHERE Sdept= ' CS ')
           AND Sdept <> ' CS ’;

       方法二：用聚集函式
        SELECT Sname，Sage
        FROM Student
        WHERE Sage < 
                               (SELECT MIN(Sage)
                                FROM Student
                                WHERE Sdept= ' CS ')
              AND Sdept <>' CS ’;

帶有EXISTS謂詞的子查詢

  1. EXISTS謂詞
    存在量詞
    帶有EXISTS謂詞的子查詢不返回任何資料，只產生邏輯真值“true”或邏輯假值“false”。
    若內層查詢結果非空，則外層的WHERE子句返回真值
    若內層查詢結果為空，則外層的WHERE子句返回假值
    由EXISTS引出的子查詢，其目標列表示式通常都用* ，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義
  2. NOT EXISTS謂詞
    若內層查詢結果非空，則外層的WHERE子句返回假值
    若內層查詢結果為空，則外層的WHERE子句返回真值
[例44]查詢所有選修了1號課程的學生姓名。

思路分析：
本查詢涉及Student和SC關係
在Student中依次取每個元組的Sno值，用此值去檢查SC關係
若SC中存在這樣的元組，其Sno值等於此Student.Sno值，並且其Cno= '1'，則取此Student.Sname送入結果關係

用巢狀查詢
     SELECT Sname
     FROM Student
     WHERE EXISTS
                   (SELECT *
                    FROM SC
                    WHERE Sno=Student.Sno AND Cno= ' 1 ')；

用連線運算
    SELECT Sname
    FROM Student, SC
    WHERE Student.Sno=SC.Sno AND SC.Cno= '1';

[例45]  查詢沒有選修1號課程的學生姓名。
     SELECT Sname
     FROM Student
     WHERE NOT EXISTS
                   (SELECT *
                    FROM SC
                    WHERE Sno = Student.Sno AND Cno='1')；

不同形式的查詢間的替換
一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換
所有帶IN謂詞、比較運算子、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換
用EXISTS/NOT EXISTS實現全稱量詞(難點)
SQL語言中沒有全稱量詞 （For all）
可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞：
(x)P ≡  ( x( P))

例：[例39]查詢與“劉晨”在同一個系學習的學生。
       可以用帶EXISTS謂詞的子查詢替換：
     SELECT Sno，Sname，Sdept
     FROM Student S1
      WHERE EXISTS
             　   (SELECT *
                     FROM Student S2
                     WHERE S2.Sdept = S1.Sdept AND
                                   S2.Sname = ‘劉晨’)；

[例46] 查詢選修了全部課程的學生姓名。
        SELECT Sname
        FROM Student
        WHERE NOT EXISTS
                    （SELECT *
                        FROM Course
                        WHERE NOT EXISTS
                                      (SELECT *
                                       FROM SC
                                       WHERE Sno= Student.Sno
                                             AND Cno= Course.Cno
                                       ）
                       ）；

   用EXISTS/NOT EXISTS實現邏輯蘊函(難點)
SQL語言中沒有蘊函(Implication)邏輯運算
可以利用謂詞演算將邏輯蘊函謂詞等價轉換為：
                   p  q ≡  p∨q 

 [例47]查詢至少選修了學生200215122選修的全部課程的學生號碼。
解題思路：
用邏輯蘊函表達：查詢學號為x的學生，對所有的課程y，只要200215122學生選修了課程y，則x也選修了y。
形式化表示：
    用P表示謂詞 “學生200215122選修了課程y”
    用q表示謂詞 “學生x選修了課程y”
    則上述查詢為: (y) pq 

用NOT EXISTS謂詞表示：     
     SELECT DISTINCT Sno
       FROM SC SCX
       WHERE NOT EXISTS
                     (SELECT *
                      FROM SC SCY
                      WHERE SCY.Sno = ' 200215122 '  AND
                                    NOT EXISTS
                                    (SELECT *
                                     FROM SC SCZ
                                     WHERE SCZ.Sno=SCX.Sno AND
                                                   SCZ.Cno=SCY.Cno))；

集合查詢

集合操作的種類
並操作UNION
交操作INTERSECT
差操作EXCEPT(MINUS)
參加集合操作的各查詢結果的列數必須相同；對應項的資料型別也必須相同 

[例48]  查詢電腦科學系的學生及年齡不大於19歲的學生。
方法一：
        SELECT *
        FROM Student
        WHERE Sdept= 'CS'
        UNION
        SELECT *
        FROM Student
        WHERE Sage<=19；
UNION：將多個查詢結果合併起來時，系統自動去掉重複元組。
UNION ALL：將多個查詢結果合併起來時，保留重複元組 

方法二：
       SELECT  DISTINCT  *
        FROM Student
        WHERE Sdept= 'CS'  OR  Sage<=19；

[例50]  查詢電腦科學系的學生與年齡不大於19歲的學生的交集

SELECT *
FROM Student
WHERE Sdept='CS' 
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19 

[例50] 實際上就是查詢電腦科學系中年齡不大於19歲的學生

        SELECT *
            FROM Student
            WHERE Sdept= 'CS' AND  Sage<=19；

[例51]  查詢選修課程1的學生集合與選修課程2的學生集合的交集
     SELECT Sno
    FROM SC
    WHERE Cno=' 1 ' 
    INTERSECT
    SELECT Sno
    FROM SC
    WHERE Cno='2 '
[例51]實際上是查詢既選修了課程1又選修了課程2   的學生
        SELECT Sno
          FROM SC
          WHERE Cno=' 1 ' AND Sno IN
                                                (SELECT Sno
                                                 FROM SC
                                                 WHERE Cno=' 2 ')；

[例52]  查詢電腦科學系的學生與年齡不大於19歲的學生的差集。

    SELECT *
    FROM Student
    WHERE Sdept='CS'
    EXCEPT
    SELECT  *
    FROM Student
    WHERE Sage <=19;

   [例52]實際上是查詢電腦科學系中年齡大於19歲的學生

        SELECT *
        FROM Student
        WHERE Sdept= 'CS' AND  Sage>19；

SELECT語句的一般格式

SELECT [ALL|DISTINCT]  
   <目標列表示式> [別名] [ ，<目標列表示式> [別名]] …
 FROM     <表名或檢視名> [別名] 
             [ ，<表名或檢視名> [別名]] …
 [WHERE <條件表示式>]
 [GROUP BY <列名1>
 [HAVING     <條件表示式>]]
 [ORDER BY <列名2> [ASC|DESC]

相關文章