【3.16資料庫作業7】SQL練習4 - SELECT（連接配接查詢、嵌套查詢）

先看看今天學了個啥 （除了②.4）

①：連接配接查詢----中級

1.等值與非等值連接配接查詢

2.自身連接配接

3.外連接配接

4.多表連接配接

②：嵌套查詢----進階

相關子查詢

不相關子查詢

1.帶有IN謂詞的子查詢

2.帶有比較運算符的子查詢

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

4.帶有EXISTS謂詞的子查詢（重點、難點）

①：連接配接查詢----中級

1.等值與非等值連接配接查詢

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

連接配接條件：用來連接配接兩個表的條件

[<表名1>.]<列名1>  <比較運算符>  [<表名2>.]<列名2>
 Student.Sno           =             SC.Sno
           

例：

Student.Sno = SC.Sno

連接配接字段：連接配接條件中的列名稱

如：Sno為上面例子中的連接配接字段

注意：連接配接字段類型必須是可比的（即類型必須相同），但名字不必相同

[例 3.49] 查詢每個學生及其選修課程的情況-----等值連接配接

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

<=>

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

若将條件（WHERE Student.Sno = SC.Sno;）去掉則結果為

共55個元組

我的SC表有5個元組，Student表有11個元組，11*5=55

即為SC與Student表做笛卡爾積

而我們加上條件之後得出的結果隻是這個的一部分

[例 3.50] 對[例 3.49]用自然連接配接完成。

SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
 FROM    Student,SC
 WHERE   Student.Sno = SC.Sno;
           

[例 3.51 ]查詢選修2号課程且成績在90分以上的所有學生的學号和姓名。

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

一條SQL語句可以同時完成連接配接和查詢操作。先從Student中和SC表中滿足連接配接條件的元組進行連接配接後，再在SC中選擇出Cno='2’并且Grade>90的元組作為條件篩選得到最終的結果關系

這裡需要注意的是因為Sno在兩個表中都有，是以需要注明一下（Student.Sno）/（Sname.Sno）都可，而唯一的屬性例如Grade隻存在于SC中，是以無需标注表名

例如

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

2.自身連接配接：

一個表與其自己進行連接配接需要給表起别名以示差別，所有屬性名都是同名屬性，是以必須使用“别名”

[例 3.52]查詢每一門課的間接先修課（即先修課的先修課）

SELECT  FIRST_TABLE.Cno, SECOND_TABLE.Cpno
FROM    Course  FIRST_TABLE, Course  SECOND_TABLE
WHERE   FIRST_TABLE.Cpno = SECOND_TABLE.Cno;
           

我們可以将一張表看成兩張相同的表，再進行WHERE條件查詢

标準SQL不顯示空值，即無3、5行

3.外連接配接

外連接配接與普通連接配接的差別：

普通連接配接操作隻輸出滿足連接配接條件的元組

外連接配接操作以指定表為連接配接主體，将主體表中不滿足連接配接條件的元組一并輸出

外連接配接就好比說我們在錄入成績的時候不能把那些成績為NULL的學生去掉

左外連接配接

列出左邊關系中所有的元組

右外連接配接

列出右邊關系中所有的元組

[例 3. 53] 改寫[例 3.49]

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

對比一下【3.49】和【3.53】

可以看到在【3.49】中隻顯示選課的同學，而【3.53】左外連接配接後會顯示所有學生，即LEFT左邊Student中所有的元組

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

           

[例3.54]查詢每個學生的學号、姓名、選修的課程名及成績-------4.多表連接配接：兩個以上的表進行連接配接

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
           

SELECT *
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
           

厘清哪個屬性是唯一存在于某個表中的 比如選修的課程名唯一來自Course 成績唯一來自SC

②：嵌套查詢----進階

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

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

SELECT *	                           /*外層查詢/父查詢*/
FROM Student
WHERE Sno IN
	(SELECT Sno        /*内層查詢/子查詢*/
	FROM SC
	WHERE Cno= '2');
           

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

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

子查詢的限制：不能使用ORDER BY子句

這裡需要注意關于子查詢的兩類查詢

①：不相關子查詢：子查詢的查詢條件不依賴于父查詢

由裡向外 逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件。

②：相關子查詢：子查詢的查詢條件依賴于父查詢

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

是以子查詢是否依賴于父查詢是相關與否的判斷标準。

1.帶有比較運算符的子查詢 （>，<，=，>=，<=，!=或< >）

[例 3.55] 查詢與“劉晨”在同一個系學習的學生。

方案1：兩步走

① 先确定“劉晨”所在系名

SELECT  Sdept  
FROM    Student                            
WHERE   Sname= '劉晨';
           

得到結果為： CS

②再查找所有在CS系學習的學生。

SELECT   Sno, Sname, Sdept     
FROM      Student                 
WHERE   Sdept= 'CS'; 
           

方案2：一步即達（将①的查詢結果嵌套在②的查詢條件中）

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

2.因為一個學生隻可能在一個系學習，是以這裡可以用=代替IN，但如果學生和系是一對多的關系，則不可代替

此時報錯：子查詢傳回的值不止一個。當子查詢跟随在 =、!=、<、<=、>、>= 之後，或子查詢用作表達式時，這種情況是不允許的。

方案3：用自身連接配接完成

SELECT  S1.Sno, S1.Sname,S1.Sdept
FROM    Student S1,Student S2
WHERE   S1.Sdept = S2.Sdept  AND  S2.Sname = '劉晨';
           

這個感覺也很像兩步走①：先把同系的綁在一起②：再把和劉晨同系的綁起來

做一個對比吧

方案一邏輯很清晰先做這一步，後做那一步

方案二語句清晰，其實也還好；

方案三是利用别名做出來的 不過感覺在c中函數原型還需要提前聲明 在DB中别名直接寫出 後面再FROM中注明就好了（我這是個什麼聯想hhh ）

[例 3.56]查詢選修了課程名為“資訊系統”的學生學号和姓名

不相關子查詢

--不相關子查詢
SELECT Sno,Sname
FROM   Student
WHERE  Sno  IN
		    (SELECT Sno
   			 FROM   SC
     		 WHERE  Cno IN
					   (SELECT Cno
                        FROM   Course
                        WHERE  Cname= '資訊系統'
		               )
              );
           

分了三層，1-2-3 逐層向外

①：首先在Course關系中找出“資訊系統”的課程号，為3号

② 然後在SC關系中找出選修了3号課程的學生學号

③ 最後在Student關系中取出Sno和Sname

我在敲dangdangdang

用連接配接查詢實作

SELECT Student.Sno,Sname
FROM   Student,SC,Course
WHERE  Student.Sno = SC.Sno  AND
	   SC.Cno = Course.Cno   AND
	   Course.Cname='資訊系統';
           

[例 3.57 ]找出每個學生超過他選修課程平均成績的課程号。

相關子查詢

SELECT Sno, Cno
FROM   SC  x
WHERE  Grade >=(
		SELECT AVG(Grade) 
		FROM  SC y
		WHERE y.Sno=x.Sno
		);
           

子查詢執行中用到了父查詢的值（x）

每當從父查詢中取出SC的一個元組x，便将元組x的Sno值傳送給子查詢。

SELECT AVG(Grade)
FROM   SC y
WHERE  y.Sno='201215121';
           

執行内層查詢，得到值88（近似值），用該值代替内層查詢，得到外層查詢：

SELECT Sno,Cno
FROM   SC x
WHERE  Grade >=88; 
           

這個過程就是先求平均值再分别與各科成績作對比

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

這次作業最後一部分了 堅持住

使用ANY或ALL謂詞時必須同時使用比較運算

謂詞	含義
>ANY	大于子查詢結果中的某個值（大于最小值）
>ALL	大于子查詢結果中的所有值（大于最大值）
<ANY	小于子查詢結果中的某個值（小于最大值）
< ALL	小于子查詢結果中的所有值（小于最小值）
>=ANY	大于等于子查詢結果中的某個值（大于等于最小值）
>= ALL	大于等于子查詢結果中的所有值（大于等于最大值）

[例 3.58] 查詢非計算機科學系中比計算機科學系任意一個學生年齡小的學生姓名和年齡

SELECT Sname,Sage
FROM   Student
WHERE  Sage < ANY 
           (SELECT  Sage
			FROM    Student
			WHERE   Sdept= 'CS')
       AND Sdept <> 'CS';           /*父查詢塊中的條件 */
           

1、首先處理子查詢，找出CS系中所有學生的年齡，構成一個集合

2、然後處理父查詢，【<ANY （子查詢結果）】即為找所有年齡小于1中集合中年齡最大的學生，并且不是CS系的學生

或用聚集函數

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

1、首先處理子查詢，找出CS系中所有學生的年齡的最大值

2、然後處理父查詢，找所有不是CS系且年齡小于20的學生

一定要自己敲一下

一定要自己敲一下

一定要自己敲一下

記清一些概念，相關子查詢/不相關子查詢

注意點：IN和=在什麼情況下相等

再複習一下前天所學

①：連接配接查詢----中級

1.等值與非等值連接配接查詢

2.自身連接配接

3.外連接配接

4.多表連接配接

②：嵌套查詢----進階

相關子查詢

不相關子查詢

1.帶有IN謂詞的子查詢

2.帶有比較運算符的子查詢

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

ok 感謝觀看