第05章：級聯(lián)查詢、子查詢

1、第5章級聯(lián)查詢、子查詢本章內容本章內容1級聯(lián)查詢2子查詢3相關子查詢講解時間：30分鐘實踐時間：60分鐘級聯(lián)查詢：級聯(lián)查詢：內容預覽內容預覽 笛卡爾積笛卡爾積 內連接內連接理論 內連接內連接實踐聯(lián)接查詢的作用和分類 通過聯(lián)接查詢可以將多個表作為一個表進行處理。 當檢索數(shù)據(jù)時，通過聯(lián)接查詢可檢索出源于不同表的信息，提高用戶操作的靈活性。 聯(lián)接查詢分為：交叉聯(lián)接、內聯(lián)接和外聯(lián)接。聯(lián)接查詢笛卡爾積 含義：兩個集合中的每一個成員，都與對方集合中的任意一個成員有關聯(lián)。即第一個表的行數(shù)乘以第二個表的行數(shù)等于笛卡爾積大小。交叉聯(lián)接SELECT fieldlist FROM table1CROSS JOIN

2、table2表table1和表table2中的字段列表不帶WHERE條件的交叉聯(lián)接中的列數(shù)=表table1與table2中列數(shù)之。不帶WHERE條件的交叉聯(lián)接中的行數(shù)=表table1與table2中行數(shù)之。沒有WHERE條件的交叉聯(lián)接所生成的結果集即笛卡爾積。交叉聯(lián)接交叉聯(lián)接SELECT * FROM Employee CROSS JOIN Rank獲取Employee表和Rank表的笛卡爾積交叉聯(lián)接266為Employee表的行數(shù)與Rank表的行數(shù)之積。SELECT fieldlistFROM table1 INNER JOIN table2ON table1.column1=table2.

3、column2表table1和表table2進行內聯(lián)接，INNER可省略。聯(lián)接條件。其中column1和column2為table1表與table2表的關聯(lián)列，通常一個為主鍵，另一個為外鍵。 在進行兩張表的聯(lián)接查詢中，僅返回匹配數(shù)據(jù)的聯(lián)接方式稱為內聯(lián)接。內聯(lián)接通過內聯(lián)接獲取兩張表的全部數(shù)據(jù)。內聯(lián)接通過內聯(lián)接獲取兩張表的部分列數(shù)據(jù)。SELECT * FROM Employee INNER JOIN Rank ON Employee.RankID=Rank.RankID 兩表聯(lián)接的條件 SELECT e.Name, r.RankID, r.RankName FROM Employee e JOIN

4、 Rank r ON e.RankID=r.RankID來源于兩張表的部分列內聯(lián)接查詢職級名為“工程師”的員工人數(shù)。內聯(lián)接SELECT COUNT(e.EmployeeID) 工程師員工數(shù) FROM Employee e Rank re.RankID=r.RankID WHERE r.RankName=工程師l員工表Employee有職級編號，卻沒有職級名，所以不能僅通過查詢Employee表獲取職級名為“工程師”的員工人數(shù)。l職級表Rank既有職級號，也有職級名，卻沒有員工信息，所以也不能僅通過查詢Rank表來完成任務。l可以通過Employee.RankID=Rank.RankID， 來建

5、立Employee表和Rank表的聯(lián)接，從而解決該查詢任務。查詢每個職級的員工數(shù)和平均月基本工資，要求列出職級名。內聯(lián)接SELECT r.RankName 職級名, COUNT(e.EmployeeID) 員工數(shù), AVG(Salary) 平均月基本工資 FROM Employee e Rank r e.RankID=r.RankID GROUP BY r.RankName帶聚合函數(shù)內聯(lián)接查詢每個職級的員工數(shù)和平均月基本工資，要求列出職級名，但僅顯示月平均基本工資低于30000的分組信息。內聯(lián)接SELECT r.RankName 職級名, COUNT(e.EmployeeID) 員工數(shù), AV

6、G(Salary) 平均月基本工資 FROM Employee e Rank r e.RankID=r.RankID GROUP BY r.RankNameHAVING AVG(Salary)30000自聯(lián)接定義如果在一個聯(lián)接查詢中，涉及的兩個表均為同一張表，那么這種聯(lián)接就是自聯(lián)接。自聯(lián)接是一種特殊的內聯(lián)接，它是指相互聯(lián)接的兩個表在物理上為同一張表，但可以在邏輯上分為兩張表。自聯(lián)接自聯(lián)接應用場景 自聯(lián)接通常在具有上下層級關系的數(shù)據(jù)查詢中應用。 某公司部門信息表中，DeptID為部門編號，ParentDeptID為上級部門編號，DeptName為部門名。 獲取每個部門的名稱以及上級部門的名稱。自

7、連接自連接SELECT d1.DeptName 部門名, d2.DeptName 上級部門名FROM Dept d1 JOIN Dept d2 ON d1.ParentDeptID=d2.DeptIDd1和d2邏輯上為兩張表，但物理上為同一張表。自連接某公司部門數(shù)據(jù)l 查詢周杰倫所唱的歌曲名，要求列出歌手名和歌曲名。l 統(tǒng)計每位歌手所唱的歌曲數(shù)，要求列出歌手名和歌曲數(shù)，并按歌曲數(shù)量升序排列。l 計算每位歌手所唱歌曲的總點歌數(shù)，要求列出歌手名和歌曲總點歌數(shù)，并按總點歌數(shù)升序排列。l 列出每位歌手所唱歌曲的總點歌數(shù)超過30的統(tǒng)計信息，要求列出歌手名和歌曲總點歌數(shù)。l 查詢熱門流行歌曲的歌曲名稱。l

8、 根據(jù)歌曲類型統(tǒng)計歌曲數(shù)量，要求列出歌曲類型名，并按歌曲數(shù)量升序排列。l 計算每一類歌曲的總點歌數(shù)，要求列出歌曲類型名，并按總點歌數(shù)升序排列。l 計算每一類歌曲的總點歌數(shù)超過100的統(tǒng)計信息，要求列出歌曲類型名，并按總點歌數(shù)升序排列。實實踐練習踐練習實踐時間：60分鐘講解時間：30分鐘實踐時間：40分鐘級聯(lián)查詢：級聯(lián)查詢：內容預覽內容預覽 外連接外連接 簡單多表查詢簡單多表查詢理論 多表聯(lián)合查詢數(shù)據(jù)多表聯(lián)合查詢數(shù)據(jù)實踐外聯(lián)接定義和分類 外聯(lián)接是相對于內聯(lián)接的一種查詢方式。 外聯(lián)接除了顯示符合聯(lián)接條件的記錄外，還會顯示所有左表或右表或左右表中的記錄。 顯示時根據(jù)數(shù)據(jù)來源于表的不同，將外聯(lián)接分為：

9、左外聯(lián)接、右外聯(lián)接和全聯(lián)接。外連接左外聯(lián)接（LEFT OUTER JOIN）的含義 左外聯(lián)接的結果集包括LEFT JOIN子句中指定的左表的所有行。如果左表的某行在右表中沒有匹配行，則結果集中的右表列均顯示為空值。左外聯(lián)接SELECT r.RankID, r.RankName, e.Name FROM Rank r Employee e r.RankID=e.RankID ORDER BY e.Name左外聯(lián)接，OUTER可省略。左外鏈接非匹配行，右表列顯示為空。右外聯(lián)接（RIGHT OUTER JOIN）的含義 左外聯(lián)接的反向聯(lián)接。 右外聯(lián)接的結果集包括RIGHT JOIN子句中指定的右表的

10、所有行。如果右表的某行在左表中沒有匹配行，則結果集中的左表列均顯示為空值。右外連接SELECT r.RankID, r.RankName, e.Name FROM Rank r Employee e r.RankID=e.RankID ORDER BY e.Name右外連接非匹配行，左表列顯示為空。全外聯(lián)接（FULL OUTER JOIN）的含義 全外聯(lián)接的結果集包括左表和右表的所有行。當某一行在另一個表中沒有匹配行時，則另一個表的列數(shù)據(jù)顯示為空值。全外連接SELECT r.RankID, r.RankName, e.Name FROM Rank r Employee e r.RankID=e

11、.RankID ORDER BY e.Name全外連接SELECT fieldlist FROM table1，table2在FROM子句中，直接列出所有要連接的表。在WHERE子句中指定連接條件，與內聯(lián)接功能相同。如果沒有WHERE子句，則結果為笛卡爾積。簡單多表查詢SELECT e.Name, r.RankID, r.RankName FROM Employee e， Rank r WHERE e.RankID=r.RankIDSELECT r.RankName 職級名, COUNT(e.EmployeeID) 員工數(shù), MAX(Salary) 最高月基本工資 FROM Employee

12、e, Rank r WHERE e.RankID=r.RankID AND r.RankID LIKE 1%GROUP BY r.RankNam ORDER BY 最高月基本工資計算不同行政職級的員工數(shù)和最高月基本工資，按最高月基本工資升序排列。簡單多表查詢簡單多表查詢l 練習鞏固上述查詢語法l 查詢月薪大于10k的員工姓名以及級別名稱l 查詢級別名為工程師的員工的平均工資l 查詢各總監(jiān)的出生日期n KTV數(shù)據(jù)表l 查詢周杰倫所唱的歌曲名，要求列出歌手名和歌曲名。l 統(tǒng)計每位歌手所唱的歌曲數(shù)，要求列出歌手名和歌曲數(shù)，并按歌曲數(shù)量升序排列。l 計算每位歌手所唱歌曲的總點歌數(shù)，要求列出歌手名和歌曲

13、總點歌數(shù)，并按總點歌數(shù)升序排列。實實踐練習踐練習實踐時間：40分鐘講解時間：20分鐘實踐時間：40分鐘內容內容預覽預覽 單行子查詢單行子查詢理論 使用單行子查詢使用單行子查詢實踐子查詢（Subquery）是一個嵌套（nest）在SELECT、INSERT、UPDATE和DELETE語句或其他子查詢中的查詢，任何允許使用表達式的地方均可使用子查詢，但子查詢通常用于WHERE子句中。子查詢的實質是：一個 SELECT 子句的查詢結果能夠作為另一個子句的。子查詢不僅可用在WHERE子句中，還能夠用于FROM子句和SELECT子句中。根據(jù)子查詢所返回的結果行數(shù)，可以將其分為和。子查詢單行子查詢是指子查

14、詢的返回結果只有一行數(shù)據(jù)。當在主查詢的條件語句中引用子查詢的結果時，可使用單行比較符（、=、=和）進行比較。單行子查詢單行子查詢l 查詢查詢“江漢路江漢路”地區(qū)的商品信息，要求輸出商品標題和商品團購價。地區(qū)的商品信息，要求輸出商品標題和商品團購價。l 從地區(qū)表查詢出地區(qū)名稱為從地區(qū)表查詢出地區(qū)名稱為“江漢路江漢路”的地區(qū)編號，使用的地區(qū)編號，使用SQL1作為標作為標記。記。SELECT areaID FROM Area WHERE areaName=江漢路l根據(jù)根據(jù)SQL1SQL1查詢出的地區(qū)編號，在商品表中檢索出商品標題和商品團購查詢出的地區(qū)編號，在商品表中檢索出商品標題和商品團購價信息。使

15、用價信息。使用SQL2SQL2作為標記，并將作為標記，并將SQL1SQL1作為查詢條件代入作為查詢條件代入SQL2SQL2。SELECT title 商品標題, currentPrice 商品團購價 FROM Product WHERE areaID= ( SELECT areaID FROM Area WHERE areaName=江漢路 )由于areaID為Area表主鍵，所以子查詢SQL1的查詢結果為一條記錄。單行子查詢僅能夠返回一條記錄。單行子查詢單行子查詢查詢查詢團購價大于平均團購價的商品信息，要求輸出商品團購價大于平均團購價的商品信息，要求輸出商品標題和商品團購價標題和商品團購價。

16、1.1. 查詢出商品平均團購價，使用查詢出商品平均團購價，使用SQL1SQL1標記。標記。 SELECT AVG(currentPrice) FROM Product2.2. 查詢團購價大于平均團購價的商品信息，要求輸出商品標題和商品查詢團購價大于平均團購價的商品信息，要求輸出商品標題和商品團購價。使用團購價。使用SQL2SQL2標記，并將標記，并將SQL1SQL1作為查詢條件代入作為查詢條件代入SQL2SQL2。SELECT title 商品標題, currentPrice 商品團購價 FROM Product WHERE currentPrice(SELECT AVG(currentPri

17、ce) FROM Product)單行子查詢單行子查詢查詢查詢團購價大于團購價大于“服裝服裝”類最高團購價的商品信息，要求類最高團購價的商品信息，要求輸出商品標題和商品團購價。輸出商品標題和商品團購價。1. 查詢商品類型名為查詢商品類型名為“服裝服裝”的類型編號，使用的類型編號，使用SQL1標記。標記。SELECT categoryID FROM Category WHERE categoryName=服裝2.2. 查詢查詢“服裝服裝”類商品的最高團購價，使用類商品的最高團購價，使用SQL2SQL2標記，執(zhí)行時將標記，執(zhí)行時將SQL1SQL1作為作為查詢條件代入查詢條件代入SQL2SQL2。S

18、ELECT MAX(currentPrice) FROM Product WHERE categoryID=(SELECT categoryID FROM Category WHERE categoryName=服裝)單行子查詢查詢團購價大于查詢團購價大于“服裝服裝”類最高團購價的商品信息，要求類最高團購價的商品信息，要求輸出商品標題和商品團購價。輸出商品標題和商品團購價。3.3. 查詢團購價大于查詢團購價大于“服裝服裝”類商品最高團購價的商品信息，要求輸出商類商品最高團購價的商品信息，要求輸出商品標題和商品團購價。查詢語句使用品標題和商品團購價。查詢語句使用SQL3SQL3標記，使用時將標記

19、，使用時將SQL2SQL2作為條作為條件代入件代入SQL3SQL3。SELECT title 商品標題, currentPrice 商品團購價 FROM Product WHERE currentPrice (SELECT MAX(currentPrice) FROM Product WHERE categoryID=(SELECT categoryID FROM Category WHERE categoryName=服裝)單行子查詢單行子查詢子查詢通常用于SELECT語句的WHERE子句中，且可以嵌套。編寫復雜的子查詢的解決思路是：查詢。即從最內層的子查詢開始分解，將嵌套的SQL語句拆分為

20、一個個獨立的SQL語句。子查詢的執(zhí)行過程遵循“由里及外”原則，即先執(zhí)行最內層的子查詢語句，然后將執(zhí)行結果與外層的語句進行合并，依次逐層向外擴展并最終形成完整的SQL語句。一般情況下，聯(lián)接查詢可改為子查詢實現(xiàn)；但子查詢卻不一定可改為聯(lián)接查詢實現(xiàn)。子查詢與聯(lián)接查詢執(zhí)行效率的比較：當子查詢執(zhí)行結果的行數(shù)較大，而主查詢執(zhí)行結果的行數(shù)較小時，子查詢執(zhí)行效率較高；而情況相反時，則聯(lián)接查詢執(zhí)行效率較高。但由于SQL Server 2008之后的查詢引擎優(yōu)化相當高超，這兩種查詢執(zhí)行，所以大家可依據(jù)自己的編寫習慣來選擇子查詢或聯(lián)接查詢。單行子查詢l 練習鞏固上述查詢語法n journey數(shù)據(jù)庫l使用子查詢獲取“

21、國內短線游”線路信息，按照線路價格升序顯示線路編號、線路名和價格。 l使用子查詢統(tǒng)計“國內短線游”線路數(shù)量、線路最高價格和線路最低價格。 單行子查詢：從線路類型表獲取類型名為“國內短線游”的類型編號。主查詢：使用聚合函數(shù)從線路表獲取線路統(tǒng)計信息，將子查詢所獲取的l 使用子查詢統(tǒng)計客戶“郝瓊瓊”預訂線路的數(shù)量。l 使用子查詢獲取比所有“國內短線游”線路價格都高的線路信息，按照線路類型、價格升序顯示線路編號、線路名和價格。實實踐練習踐練習實踐時間：40分鐘講解時間：20分鐘實踐時間：60分鐘內容內容預覽預覽 多行子查詢多行子查詢理論 使用多行子查詢使用多行子查詢實踐多行子查詢是指子查詢的返回結果是

22、多行數(shù)據(jù)。多行比較符包括：IN、ALL和ANY|SOME。多行子查詢使用使用ININ時，主查詢會與子查詢中的每一個值進行比較，如果與其中的時，主查詢會與子查詢中的每一個值進行比較，如果與其中的任意一個值相同，則返回任意一個值相同，則返回。NOT NOT ININ與與ININ的含義恰好相反。的含義恰好相反。in 比較比較符符1.查詢出地區(qū)名為查詢出地區(qū)名為“江漢路江漢路”、“司門口司門口”和和“西北湖西北湖”的地區(qū)編號。的地區(qū)編號。SELECT areaID FROM Area WHERE areaName=江漢路 OR areaName=司門口 OR areaName=西北湖2.查詢查詢“江漢

23、路江漢路”、“司門口司門口”和和“西北湖西北湖”地區(qū)的商品信息。地區(qū)的商品信息。SELECT title 商品標題, currentPrice 商品團購價 FROM Product WHERE areaID ( SELECT areaID FROM Area WHERE areaName=江漢路 OR areaName=司門口 OR areaName=西北湖)查詢查詢“江漢路江漢路”、“司門口司門口”和和“西北湖西北湖”地區(qū)的商品信地區(qū)的商品信息，要求輸出商品標題、商品團購價和區(qū)域編號。息，要求輸出商品標題、商品團購價和區(qū)域編號。in 比較符比較符由于多行子查詢返回的結果行數(shù)可以為一個，因而單

24、行子查詢也是多行子查詢的一種特殊情況，所以單行子查詢的“=”比較符可以替換為多行子查詢的“IN”比較符。但不能將多行子查詢的“IN”比較符替換為單行子查詢的“=”比較符。in 比較符比較符查詢所有男性顧客所購商品信息，要求輸出商品標題和查詢所有男性顧客所購商品信息，要求輸出商品標題和商品團購價，并按照團購價升序排列。商品團購價，并按照團購價升序排列。1.1. 查詢所有男性顧客的客戶編號。查詢所有男性顧客的客戶編號。SELECT customerID FROM Customer WHERE gender=男2.查詢所有男性顧客所生成訂單的全部訂單編號查詢所有男性顧客所生成訂單的全部訂單編號。SE

25、LECT ordersID FROM Orders WHERE customerID IN(SELECT customerID FROM Customer WHERE gender=男)in 比較符比較符3.查詢所有男性顧客所訂購商品的全部商品編號。查詢所有男性顧客所訂購商品的全部商品編號。SELECT ProductID FROM OrdersDetail WHERE ordersID IN(SELECT ordersID FROM Orders WHERE customerID IN(SELECT customerID FROM Customer WHERE gender=男)4.4. 按

26、團購價升序顯示按團購價升序顯示所有男性顧客所訂購商品的商品標題和團購價所有男性顧客所訂購商品的商品標題和團購價。SELECT title, currentPrice FROM Product WHERE productID IN(SELECT ProductID FROM OrdersDetail WHERE ordersID IN(SELECT ordersID FROM Orders WHERE customerID IN(SELECT customerID FROM Customer WHERE gender=男)ORDER BY currentPricein 比較符比較符in 比較符比

27、較符表達式或字段單行比較運算符表達式或字段單行比較運算符 ALL ALL（子查詢）（子查詢）l ALL關鍵字（比較運算符）放置于子查詢之前。l 通過ALL比較運算符將一個表達式或列的值，與子查詢所返回的一列值中的進行比較，只要有一次比較的結果為FALSE（假），則ALL測試返回FALSE，主查詢不執(zhí)行；否則返回TRUE，執(zhí)行主查詢。l ALL運算符的含義如下：lALL，大于最大值。使用使用 all 關鍵字的子查關鍵字的子查詢詢查詢團購價比所有服裝類商品團購價都高的商品信息，查詢團購價比所有服裝類商品團購價都高的商品信息，要求輸出商品標題和商品團購價。要求輸出商品標題和商品團購價。1.1. 查詢

28、查詢所有服裝類商品的商品編號。所有服裝類商品的商品編號。SELECT categoryID FROM Category WHERE categoryName=服裝2.2. 查詢所有服裝類商品的團購價。查詢所有服裝類商品的團購價。SELECT currentPrice FROM Product WHERE categoryID =(SELECT categoryID FROM Category WHERE categoryName=服裝)使用使用 all 關鍵字的子查詢關鍵字的子查詢3.查詢團購價比所有服裝類商品團購價高的商品信息查詢團購價比所有服裝類商品團購價高的商品信息。SELECT tit

29、le 商品標題, currentPrice 商品團購價 FROM ProductWHERE currentPrice all(SELECT currentPrice FROM Product WHERE categoryID =(SELECT categoryID FROM Category WHERE categoryName=服裝) 使用使用 all 關鍵字的子查詢關鍵字的子查詢使用使用 all 關鍵字的子查詢關鍵字的子查詢查詢團購價比所有服裝類商品團購價都高的商品信息，要查詢團購價比所有服裝類商品團購價都高的商品信息，要求輸出商品標題和商品團購價。求輸出商品標題和商品團購價。n“ALL

30、(“ALL (子查詢子查詢)”)”的含義是的含義是“大于子查詢返回結果的最大值大于子查詢返回結果的最大值”。n采用采用“（子查詢所獲取的最大列值）（子查詢所獲取的最大列值）”方式，方式，SQLSQL語句如下：語句如下：SELECT title 商品標題, currentPrice 商品團購價 FROM ProductWHERE currentPrice (SELECT MAX(currentPrice) FROM Product WHERE categoryID =(SELECT categoryID FROM Category WHERE categoryName=服裝)使用使用 all 關

31、鍵字的子查詢關鍵字的子查詢表達式或字段 單行比較運算符 ANY|SOME（子查詢）。ANY與SOME的查詢功能相同。早期的SQL僅允許使用ANY，后期的版本為了和英語中的ANY相區(qū)別，引入了SOME，同時保留了ANY關鍵字。l ANY或SOME用于子查詢之前，將一個表達式或列的值與子查詢所返回的一列值中的每一行進行比較，只要有一次比較的結果為TRUE，則ANY或SOME測試返回TRUE；否則結果為FALSE。l ANY|SOME運算符的含義如下： ANY|SOME ，大于最小值。使用使用 any/some 關鍵字的子查詢關鍵字的子查詢查詢團購價比任意一款服裝類商品團購價都高的商品信查詢團購價比

32、任意一款服裝類商品團購價都高的商品信息，要求輸出商品標題和商品團購價。息，要求輸出商品標題和商品團購價。SELECT title 商品標題, currentPrice 商品團購價 FROM ProductWHERE currentPrice (SELECT currentPrice FROM Product WHERE categoryID =(SELECT categoryID FROM Category WHERE categoryName=服裝) 使用使用 any/some 關鍵字的子查詢關鍵字的子查詢使用使用 any/some 關鍵字的子查詢關鍵字的子查詢l 練習鞏固上述查詢語法n j

33、ourney數(shù)據(jù)庫1. 使用子查詢獲取“國內短線游”以及“國內長線游”線路信息，按照線路類型、線路價格升序顯示線路編號、線路名和價格。多行子查詢：從線路類型表獲取類型名為“國內短線游”和“國內長線游”的類型編號。主查詢：從線路表獲取相關線路信息，將子查詢所獲取的類型編號作為主查詢的條件比較值。2. 使用子查詢獲取比所有“國內短線游”價格高的線路信息，按照線路類型、線路價格升序顯示線路編號、線路名和價格。1.單行子查詢：從線路類型表獲取類型名為“國內短線游”的類型編號。2.多行子查詢：從線路表獲取所有“國內短線游”線路的價格，將從單行子查詢中獲取的類型編號作為條件比較值。3.主查詢：從線路表獲取

34、所有價格高于全部“國內短線游”價格的線路信息，將從多行子查詢中獲取的線路價格作為條件比較值。實實踐練習踐練習實踐時間：60分鐘l使用子查詢獲取線路數(shù)量超過“出境游”線路數(shù)的線路類型信息，要求按照線路數(shù)升序顯示線路類型編號和線路數(shù)。 1.單行子查詢：從線路類型表獲取線路名為“出境游”的線路類型編號。2.多行子查詢：使用COUNT(線路編號)從線路表獲取“出境游”線路個數(shù)，將從單行子查詢中獲取的線路類型編號作為比較條件。3.主查詢：在線路表中，依據(jù)線路類型編號進行分組，使用COUNT(線路編號)計算出不同類型線路的個數(shù)，并使用HAVING子句對分組數(shù)據(jù)進行限制，將從多行子查詢中獲取的“出境游”線路

35、數(shù)作為限制條件。l統(tǒng)計訂購“華東五市”線路的訂單個數(shù)。l使用子查詢匯總所有男性客戶所預訂旅游線路的數(shù)量。實實踐練習踐練習實踐練習實踐練習l 使用子查詢獲取客戶“郝瓊瓊”預訂線路的基本信息，要求顯示線路編號和出行日期，按照出行日期升序顯示。1. 單行子查詢：從客戶表獲取“郝瓊瓊”的客戶編號。2. 多行子查詢：在訂單客戶表中，將從單行子查詢中取得的客戶編號作為比較條件，獲取“郝瓊瓊”所有預訂線路的訂單編號。3. 主查詢：在訂單線路表中，將從多行子查詢中取得的所有訂單編號作為比較條件，獲取“郝瓊瓊”所有預訂線路的線路編號和出行日期。SELECT lineID 線路編號, travelDate 出行日

36、期 FROM OL_Detail WHERE ordersID IN( SELECT ordersID FROM OC_Detail WHERE travelCustomerID= (SELECT customerID FROM Customer WHERE name=郝瓊瓊) ORDER BY travelDate實踐練習實踐練習l 使用子查詢獲取客戶“郝瓊瓊”預訂線路的詳細信息，要求顯示線路編號、線路名、線路價格和出行日期，按照線路價格升序顯示。 1. 從線路表獲取所有線路編號、線路名、價格和出行日期。2. 實現(xiàn)上一題，可以獲取“郝瓊瓊”所有預訂線路的線路編號和出行日期。3. 步驟（2）生

37、成的查詢結果作為FROM子句的虛擬表A，通過聯(lián)接條件“線路表.線路編號=A.線路編號”，取得“郝瓊瓊”預訂線路的詳細信息。實踐練習實踐練習SELECT L.lineID 線路編號, L.lineName 線路名, L.price 價格, A.travelDate 出行日期 FROM Line L, ( SELECT lineID,travelDate FROM OL_Detail WHERE ordersID IN (SELECT ordersID FROM OC_Detail WHERE travelCustomerID= (SELECT customerID FROM Customer W

38、HERE name=郝瓊瓊 ) ) A WHERE L.lineID=A.lineID ORDER BY L.price講解時間：10分鐘實踐時間：20分鐘內容內容預覽預覽 在在 from 子句中使用子查詢子句中使用子查詢 在在 select 子句中使用子查詢子句中使用子查詢理論 在在 from 子句中使用子查詢子句中使用子查詢 在在 select 子句中使用子查詢子句中使用子查詢實踐子查詢通常用于子查詢通常用于WHEREWHERE子句中，但其也可在子句中，但其也可在FROMFROM子句和子句和SELECTSELECT子句中子句中使用。在這些場合下使用子查詢，有時會實現(xiàn)一些特殊的查詢應用。使用

39、。在這些場合下使用子查詢，有時會實現(xiàn)一些特殊的查詢應用。在在 from 子句中使用子查子句中使用子查詢詢?yōu)闉閹椭碳姨嵘▋r能力，優(yōu)化產(chǎn)品銷售策略，美幫助商家提升定價能力，優(yōu)化產(chǎn)品銷售策略，美淘網(wǎng)淘網(wǎng)平臺平臺在向每位商家提供產(chǎn)品信息的同時，還提供了該類在向每位商家提供產(chǎn)品信息的同時，還提供了該類商品的平均團購價。商品的平均團購價。SELECT A.categoryID SELECT A.categoryID 商品類型編號商品類型編號, A.title , A.title 商品標題商品標題, , A.currentPrice A.currentPrice 商品團購價商品團購價, B.avgPri

40、ce , B.avgPrice 該類商品平均團購價該類商品平均團購價 FROM Product A, ( SELECT categoryID, AVG(currentPrice) avgPrice FROM Product GROUP BY categoryID ) WHERE A.categoryID=B.categoryID在在FROMFROM子句中使用子查詢子句中使用子查詢子查詢結果的別名，其類似于一個虛擬表ProductProduct表和虛擬表表和虛擬表B B的聯(lián)接條件的聯(lián)接條件在在 from 子句中使用子查詢子句中使用子查詢在在 from 子句中使用子查詢子句中使用子查詢?yōu)闉閹椭碳?/p>

41、提升定價能力，優(yōu)化產(chǎn)品銷售策略，美幫助商家提升定價能力，優(yōu)化產(chǎn)品銷售策略，美淘網(wǎng)淘網(wǎng)平臺平臺在向每位商家提供產(chǎn)品信息的同時，還提供了該類在向每位商家提供產(chǎn)品信息的同時，還提供了該類商品的平均團購價。商品的平均團購價。SELECT A.categoryID SELECT A.categoryID 商品類型編號商品類型編號, A.title , A.title 商品標題商品標題, , A.currentPrice A.currentPrice 商品團購價商品團購價, B.avgPrice , B.avgPrice 該類商品平均團購價該類商品平均團購價 FROM Product A, ( SELEC

42、T categoryID, AVG(currentPrice) avgPrice FROM Product GROUP BY categoryID ) B WHERE A.categoryID=B.categoryID在FROM子句中使用子查詢子查詢結果的別名，其類似于一個虛擬表Product表和虛擬表B的聯(lián)接條件在在 from 子句中使用子查詢子句中使用子查詢在在 from 子句中使用子查詢子句中使用子查詢在SELECT子句中使用子查詢，其實質是將子查詢的執(zhí)行結果作為SELECT子句的，可以起到與聯(lián)接查詢異曲同工的作用。在一些復雜的多表聯(lián)接查詢場合，如果在SELECT子句中使用子查詢，其句法

43、結構與使用多表聯(lián)接查詢相比，會顯得更加清晰。在在 select 子句中使用子查詢子句中使用子查詢查詢商品數(shù)、已訂購查詢商品數(shù)、已訂購商品個數(shù)商品個數(shù)和已訂購商品件和已訂購商品件數(shù)。數(shù)。SELECT COUNT(productID) 商品個數(shù),(SELECT COUNT(DISTINCT productID) FROM OrdersDetail) 已訂購商品個數(shù),(SELECT SUM(salesCount) FROM Product) 已訂購商品件數(shù) FROM Product在在 select 子句中使用子查詢子句中使用子查詢在在 select 子句中使用子查詢子句中使用子查詢使用WITH A

44、S短語，可以把子查詢抽出來，增強可讀性使用WITH ASSubQuery (SELECT areaID FROM Area WHERE areaName=江漢路 OR areaName=司門口 OR areaName=西北湖)SELECT title 商品標題, currentPrice 商品團購價 FROM Product WHERE areaID IN (SELECT areaID FROM SubQuery)B (SELECT categoryID, AVG(currentPrice) avgPrice FROM Product GROUP BY categoryID)SELECT A.

45、categoryID 商品類型編號, A.title 商品標題, A.currentPrice 商品團購價, B.avgPrice 該類商品平均團購價 FROM Product A, B WHERE A.categoryID=B.categoryIDl 練習鞏固上述查詢語法n journey數(shù)據(jù)庫l獲取不同類型的線路個數(shù)和平均價，要求輸出線路類型編號、類型名、線路個數(shù)和平均價格，按照線路個數(shù)、平均價格升序排列。FROM子句中的子查詢：在線路表中，依據(jù)線路類型編號分組計算每個線路類型的線路數(shù)和平均價格。主查詢：聯(lián)接線路類型表和由FORM子句中的子查詢所生成的虛擬表，將線路類型編號作為聯(lián)接列。查詢

46、結果顯示的排序列依次為線路數(shù)量和平均價格。l使用子查詢獲取每一種線路類型的線路數(shù)及其最高價、最低價，并輸出最高價大于2000元的線路信息。要求顯示線路類型編號、線路名、線路數(shù)、最高價和最低價，要求按照線路數(shù)、線路最高價升序排列。實實踐練習踐練習實踐時間：20分鐘實踐練習實踐練習l 使用子查詢統(tǒng)計每個客戶預訂線路的數(shù)量，要求顯示客戶姓名和預訂線路數(shù)，按照預訂線路數(shù)升序顯示。1. 在主查詢中，從客戶表獲取所有的客戶姓名。2. 在子查詢中，從訂單客戶表中，依據(jù)出行客戶編號，分組獲取每一個客戶的預訂線路數(shù)。子查詢結果作為FROM子句的虛擬表A。3. 在主查詢中，通過聯(lián)接條件“客戶表.客戶編號=A.客戶

47、編號”，取得每個客戶預訂線路的數(shù)量。SELECT name 客戶姓名, A.num 預訂線路數(shù) FROM Customer, ( SELECT travelCustomerID, COUNT(*) num FROM OC_Detail GROUP BY travelCustomerID ) AWHERE Customer.customerID=A.travelCustomerID ORDER BY A.num講解時間：30分鐘實踐時間：60分鐘子查詢：子查詢：內容預覽內容預覽 非相關子查詢非相關子查詢 相關子查詢相關子查詢理論 相關子查詢相關子查詢實踐于（independent）外部主查詢而存

48、在的子查詢，稱為。非相關子查詢僅執(zhí)行（execute）一次，并將子查詢的執(zhí)行結果傳遞給外部主查詢。前面所學的子查詢都為非相關子查詢。非相關子查非相關子查詢詢于外部主查詢的子查詢稱為相關子查詢（correlation subquery）。相關子查詢的執(zhí)行過程如下：l 1. 從主查詢中取出一條記錄，將該行記錄相關列的值傳給子查詢 。l 2. 執(zhí)行子查詢，得到子查詢執(zhí)行完畢后的值。l 3. 主查詢依據(jù)子查詢返回的結果或結果集得到滿足條件的行。l 4. 主查詢取出下一條記錄，重復步驟13，直至外層主查詢的記錄全部處理完畢。相關子查詢l 查詢查詢“江漢路江漢路”地區(qū)的商品信息，要求輸出商品標題和商品團地

49、區(qū)的商品信息，要求輸出商品標題和商品團購價購價l 使用非相關子查詢。使用非相關子查詢。SELECT title 商品標題, currentPrice 產(chǎn)品團購價 FROM Product WHERE areaID=( SELECT areaID FROM Area WHERE areaName=江漢路 )子查詢僅執(zhí)行一次相關子查詢相關子查詢SELECT title 商品標題, currentPrice 產(chǎn)品團購價 FROM Product WHERE 江漢路=( SELECT areaName FROM Area WHERE areaID= )l 查詢查詢“江漢路江漢路”地區(qū)的商品信息，要求輸

50、出商品標題和商品團購價地區(qū)的商品信息，要求輸出商品標題和商品團購價。l 使用相關子查詢。使用相關子查詢。子查詢執(zhí)行次數(shù)為主查詢執(zhí)行次數(shù)相關子查詢相關子查詢上述示例的執(zhí)行過程如下： 1. 執(zhí)行主查詢。從Product表依次取出一條記錄，并將該記錄的areaID傳遞給（transfer）子查詢。 2. 執(zhí)行子查詢。在Area表中，取出滿足“areaID=主查詢傳入的areaID”的某個areaName，并將該areaName傳給主查詢。 3. 執(zhí)行主查詢。從Product表取出滿足“江漢路=子查詢傳入的areaName”的某條記錄。 4. 再執(zhí)行步驟1，從Procuct表取出下一條記錄，并將該記錄

51、的areaID傳給子查詢，并循環(huán)執(zhí)行步驟1、2。相關子查詢獲取獲取屬于同一個區(qū)域的所有商品標題，要求輸出商品標屬于同一個區(qū)域的所有商品標題，要求輸出商品標題和商家所在區(qū)域編號，并按照區(qū)域編號升序排列。題和商家所在區(qū)域編號，并按照區(qū)域編號升序排列。n 為主查詢和子查詢中所用的商品表分別取別名。為主查詢和子查詢中所用的商品表分別取別名。n 在主查詢中使用在主查詢中使用ININ判斷區(qū)域編號是否屬于子查詢取出的區(qū)域編號。判斷區(qū)域編號是否屬于子查詢取出的區(qū)域編號。n 在子查詢中，過濾掉主查詢所取出的重復數(shù)據(jù)在子查詢中，過濾掉主查詢所取出的重復數(shù)據(jù)。 SELECT title, areaID FROM P

52、roduct p1 WHERE AreaID IN (SELECT areaID FROM Product p2 WHERE ductIDductID ) ORDER BY areaID相關子查詢相關子查詢相關子查詢相關子查詢主查詢表達式 NOT EXISTS (子查詢)：l EXISTS用于檢查子查詢是否至少會返回一行數(shù)據(jù)。l EXISTS子查詢實際上并不返回任何數(shù)據(jù)，而是返回值或l EXISTS 指定一個子查詢，用于檢測行的存在。當子查詢的行存在時，則主查詢表達式，否則不執(zhí)行。EXISTS子查詢查詢查詢所有訂購過商品的客戶姓名所有訂購過商品的客戶姓名。n 主查詢用于

53、從客戶表獲取客戶姓名。主查詢用于從客戶表獲取客戶姓名。n EXISTSEXISTS指定的子查詢用于從商品表獲取產(chǎn)品編號，子查詢的限定條件為指定的子查詢用于從商品表獲取產(chǎn)品編號，子查詢的限定條件為“商品表商品表. .客戶編號客戶編號= =客戶表客戶表. .客戶編號客戶編號”。n 只要子查詢的結果集有數(shù)據(jù)行，其返回值就為只要子查詢的結果集有數(shù)據(jù)行，其返回值就為TRUETRUE，則主查詢返回相應，則主查詢返回相應的記錄。的記錄。SELECT customerName 客戶姓名 FROM Customer c WHERE EXISTS(SELECT ordersID FROM Orders o WHE

54、RE o.customerID=c.customerID)EXISTS子查詢EXISTS子查詢采用非相關子采用非相關子查詢查詢實現(xiàn)前述示例？實現(xiàn)前述示例？SELECT customerName FROM Customer c WHERE customerID IN (SELECT customerID FROM Orders o)EXISTSEXISTS與與ININ的使用場合，通常遵循的原則是：的使用場合，通常遵循的原則是：nIN適用于主查詢結果行數(shù)較大，子查詢結果行數(shù)較小的情況（俗稱“外表大內表小”）。nEXISTS適用于主查詢結果行數(shù)比較小，子查詢結果行數(shù)比較大的情況（俗稱“外表小內表大”

55、）。EXISTS子查詢分析步驟：分析步驟：n主查詢用于從商品表獲取商品信息。主查詢用于從商品表獲取商品信息。nEXISTSEXISTS指定的子查詢用于從訂單詳細表獲取商品編號，為建立主查詢中指定的子查詢用于從訂單詳細表獲取商品編號，為建立主查詢中的商品表與子查詢中的訂單詳細表之間的關聯(lián)，需在子查詢中設置條件的商品表與子查詢中的訂單詳細表之間的關聯(lián)，需在子查詢中設置條件“訂單詳細表訂單詳細表. .商品編號商品編號= =商品表商品表. .商品編號商品編號”。n由于由于要要查詢服裝類商品的訂購情況，所以需要在子查詢中設置條件查詢服裝類商品的訂購情況，所以需要在子查詢中設置條件“商商品表品表. .類型

56、名類型名= =服裝服裝”，這這可以通過在子查詢中嵌套子查詢來實現(xiàn)?？梢酝ㄟ^在子查詢中嵌套子查詢來實現(xiàn)。SELECT title 商品標題, originalPrice 原價 FROM Product p WHERE EXISTS (SELECT ductID FROM OrdersDetail od WHERE ductID=ductID ANDp.categoryID=(SELECT categoryID FROM Category WHERE categoryName=服裝 ) ORDER BY originalPrice查詢所有參與訂購的服裝類商品信息，

57、要求輸出商品標查詢所有參與訂購的服裝類商品信息，要求輸出商品標題和原價，按照原價升序排列。題和原價，按照原價升序排列。EXISTS子查詢EXISTS子查詢l 練習鞏固上述查詢語法n journey數(shù)據(jù)庫l 使用相關子查詢獲取“國內短線游”線路信息，按照線路價格升序顯示線路編號、線路名和價格。l 使用相關子查詢統(tǒng)計“國內短線游”線路數(shù)、最高線路價和最低線路價。 主查詢：使用聚合函數(shù)從線路表獲取線路的統(tǒng)計信息，WHERE條件為“國內短線游=(子查詢獲取的線路類型名)”。 子查詢：從線路類型表中獲取滿足WHERE條件為“線路類型編號=線路表.線路類型編號”的線路名。l 使用相關子查詢統(tǒng)計客戶“郝瓊瓊

58、”預訂線路的數(shù)量。 主查詢：在訂單客戶表中，使用COUNT(*)獲取預訂線路數(shù)，在WHERE條件中使用“郝瓊瓊=(子查詢獲取的客戶姓名)”。 子查詢：通過WHERE條件“客戶編號=訂單客戶表.客戶編號”，獲取客戶表客戶姓名。 實踐實踐練習練習實踐時間：60分鐘l 使用相關子查詢和EXISTS（子查詢）獲取超過“出境游”線路數(shù)的線路信息，按照線路數(shù)升序顯示線路類型編號和線路數(shù)。p 使用相關使用相關子子查詢查詢 主查詢：在線路表中，根據(jù)線路類型編號分組統(tǒng)計每個線路類型的數(shù)量，并使用“HAVING 線路數(shù)子查詢1獲得的線路數(shù)”。 子查詢1：從線路表獲取滿足WHERE條件為“出境游=(子查詢2獲得的線

59、路類型名)”的“出境游”線路數(shù)。 子查詢2：從線路類型表獲取滿足WHERE條件為“線路類型表.線路類型編號=線路表.線路類型編號”的線路類型名。p 使用使用EXISTSEXISTS子子查詢查詢 將前述相關子查詢中“出境游=(子查詢2獲得的線路類型名)”修改為“EXISTS（子查詢2獲得線路類型記錄）”，EXISTS所含子查詢將獲取線路類型名為“出境游”，且滿足“線路類型表.線路類型編號=線路表.線路類型編號”的線路類型記錄 如果有記錄則返回TRUE，主查詢執(zhí)行；否則返回FALSE，主查詢不執(zhí)行。實實踐練習踐練習l 使用相關子查詢和EXISTS分別獲取價格高于“黃山”線路的“國內長線游”線路信息

60、。l 使用子查詢獲取預訂線路“鳳凰古城”的客戶姓名。 主查詢：從客戶表獲取客戶姓名，WHERE條件為“客戶編號 IN(子查詢1獲取客戶編號列表)”。 子查詢1：從訂單客戶表獲取客戶編號，WHERE條件為“訂單編號 IN(子查詢2獲取訂單編號列表)”。 子查詢2：從訂單線路表獲取訂單編號，WHERE條件為“鳳凰古城=(子查詢3獲取線路名)”。 子查詢3：從線路表獲取線路名，WHERE條件為“線路編號=訂單線路表.線路編號”。l 使用相關子查詢獲取預訂“境外游”類型旅行線路的客戶姓名。實實踐練習踐練習子查詢不僅可在SELECT語句中使用，用于實現(xiàn)需要嵌套的查詢功能，還可以維護數(shù)據(jù)，完成復雜的更新、