全部章節 >>>>

本章目錄

3.1 子查詢定義和單行子查詢

3.1.1 子查詢定義

3.1.2 單行子查詢應用

3.1.4 實踐練習

3.2 多行子查詢應用

3.2.1 in 比較符

3.2.3 any|some 關鍵字子查詢

3.2.4 實踐練習

3.3 子查詢特殊應用

3.3.1 from 子句中的子查詢

3.3.2 select 子句中的子查詢

3.3.4 實踐練習

3.4 DML 陳述句中的子查詢

3.4.1 update 子句中的子查詢

3.4.3 實踐練習

總結：

3.1 子查詢定義和單行子查詢

3.1.1 子查詢定義

子查詢（subquery）是資料庫經常用到的一個操作，它不僅用在資料查詢陳述句中，在 DML 陳述句中也都會用到子查詢

子查詢將一個查詢陳述句嵌套（nest）在另一個查詢陳述句中，在特定情況下，一個查詢陳述句的條件需要另一個查詢陳述句來獲取，內層查詢陳述句的查詢結果可以為外層查詢陳述句提供查詢條件

子查詢的實質：一個 select 陳述句的查詢結果能夠作為另一個陳述句的輸入值，子查詢不僅可用于 where 子句中，還能夠用于 from 子句中，此時子查詢的結果將作為一個臨時表（temporary table）來使用

子查詢還能以欄位的形式出現在 select 陳述句的選擇列中，根據子查詢所回傳的結果行數，可以將其分為單行子查詢和多行子查詢

3.1.2 單行子查詢應用

單行子查詢指子查詢的回傳結果只有一行資料，當在主查詢的條件陳述句中參考子查詢的結果時，可使用單行比較符（如＝、>、<、>=、<= 和 < >）進行比較

查詢“戰爭”類題材電影的具體資訊，要求輸出片名和導演名

示例：

（1）電影（movie）表包含電影名、導演名和電影型別編號資訊，但并不包含電影型別名稱資訊；電影型別（movie_type）表既包含電影型別編號資訊又包含電影型別名稱資訊，上述這兩張表的共同資訊是電影型別編號，所以查詢時需連接電影表和電影型別表，并以電影型別編號作為兩表的連接，首先，從電影型別表查詢出型別名稱為“戰爭”的電影型別編號，使用 SQL1 作為標記

select id from movie_type where typeName=' 戰爭 '

（2）根據 SQL1 查詢出的地區編號，在電影表中檢索出電影名和導演名資訊，使用 SQL2 作為標記，并將 SQL1作為查詢條件代入 SQL2

select movieName 電影名 ,director 導演名 from movie where typeId=
(select id from movie_type where typeName=' 戰爭 ')

由于在 movie_type 表中 typeName 是唯一約束列，因而子查詢 SQL1 的執行結果只能有 1 條（單行子查詢）或 0 條記錄，

此例還可以采用連接查詢實作

select movieName 電影名 ,director 導演名 from movie m, movie_type mt
where m.typeId=mt.movie_type and typeName=' 戰爭 '

示例：

查詢票價高于平均票價的電影資訊，要求輸出電影名和導演名

（1）獲得平均票價，使用 SQL1 標記

select avg(ticketPrice) from movie

（2）查詢票價大于平均票價的電影資訊，要求輸出電影名和導演名，使用 SQL2 標記，并將 SQL1 作為查詢條件代入 SQL2

select movieName 電影名 ,director 導演名 from movie  where ticketPrice>( select avg(ticketPrice) from movie)

示例：

查詢票價高于“戰爭”類題材的電影資訊，要求輸出電影名和導演名

（1）查詢型別名為“戰爭”的電影型別編號，使用 SQL1 標記

select id from movie_type where typeName=' 戰爭 '

（2）查詢“戰爭”類題材電影的平均票價，使用 SQL2 標記，執行時將 SQL1 作為查詢條件代入 SQL2

select avg(ticketPrice) from movie where typeID=(select id from movie_type where typeName=' 戰爭 ')

（3）查詢票價大于“戰爭”類題材電影平均票價的電影資訊，要求輸出電影名和導演名，查詢陳述句使用 SQL3 標記，使用時將 SQL2 作為條件代入 SQL3

select movieName 電影名 ,director 導演名 from movie where ticketPrice>
(select avg(ticketPrice) from movie where typeID=(
select id from movie_type where typeName=' 戰爭 '))

子查詢應用經驗初步歸納如下：

• 子查詢一般用于 select 陳述句的 where 子句中，且可以嵌套
• 撰寫復雜的子查詢的解決思路是逐層分解查詢，即從最內層的子查詢開始分解，將嵌套的 SQL 陳述句拆分為一個個獨立的 SQL 陳述句
• 子查詢的執行程序遵循“由里及外”的原則，即先執行最內層的子查詢陳述句，然后將執行結果與外層的陳述句進行合并，依次逐層向外擴展并最終形成完整的 SQL 陳述句
• 一般情況下，連接查詢可改為子查詢實作；但子查詢卻不一定可改為連接查詢實作
• 子查詢與連接查詢執行效率的比較：當子查詢執行結果的行數較大，而主查詢執行結果的行數較小時，子查詢執行效率較高；反之，則連接查詢執行效率較高

3.1.4 實踐練習

3.2 多行子查詢應用

3.2.1 in 比較符

使用多行比較符 in 時，主查詢會與子查詢中的每一個值進行比較，如果與其中的任意一個值相同，則回傳，not in 與 in 的含義恰好相反

查詢“戰爭”和“喜劇”類題材電影的相關資訊，要求輸出片名和導演名

示例：

（1）查詢型別名為“戰爭”和“喜劇”的電影型別編號

select id from movie_type where typeName=' 戰爭 ' or typeName=' 喜劇 '

（2）查詢“戰爭”和“喜劇”類題材電影的相關資訊

select movieName 電影名 ,director 導演名 from movie where typeID in
(select id from movie_type where typeName=' 戰爭 ' or typeName=' 喜劇 ')

由于多行子查詢回傳的結果行數可以為一個，因而單行子查詢也是多行子查詢的一種特殊情況，所以單行子查詢的“=”比較符可以替換為多行子查詢的“in”比較符，但不能將多行子查詢的“in”比較符替換為單行子查詢的“=”比較符，

查詢客戶“zhang01”顧客所預訂電影的具體資訊，要求輸出電影名、導演名、票價和片長，并按照票價升序排列

示例：

（1）查詢客戶“zhang01”的客戶編號

select id from customer where username='zhang01'

（2）查詢客戶“zhang01”所預訂的所有電影的排片編號

select scheduleId from ticket_sell where customerID in
(select id from customer where username='zhang01')

（3）查詢客戶“zhang01”所預訂的所有電影的電影編號

select movieId from `schedule` where id in (select scheduleId from ticket_sell where customerID in  (select id from customer where username='zhang01'))

（4）查詢客戶“zhang01”所預訂電影的具體資訊

select movieName 電影名 ,director 導演名 ,ticketPrice 票價（元）,filmLength 片長（分鐘）
from movie where id in(select movieId from `schedule` where id in
(select scheduleId from ticket_sell where customerID in
(select id from customer where username='zhang01'))) order by ticketPrice

此示例嵌套較深，如果采用連接查詢寫法會相對簡潔，特別是當商品記錄數較大的情況下，連接查詢的效率會更高，使用連接查詢實作的 SQL 陳述句如下：

select distinct movieName 電影名 ,director 導演名 ,ticketPrice 票價（元）,filmLength   片長（分鐘） from movie m,`schedule` s,ticket_sell ts,customer c where m.id=s.movieId 
and  s.id=ts.scheduleId and ts.customerId=c.id and c.username='zhang01' order by ticketPrice

上面例子的子查詢實作很好地體現了子查詢應用的解決之道──“由里及外”的原則，即逐層生成包含子查詢在內的主查詢，逐層生成的主查詢又成為上一層主查詢所包含的子查詢，如此依次遞進（recursion），最終生成最上層的包含所有子查詢的主查詢，

all 關鍵字位于多行比較運算子之后，通過 all 關鍵字將一個運算式或列的值與子查詢所回傳的一列值中的每一行進行比較，只要有一次比較的結果為 false（假），則 all 測驗回傳 false，主查詢不執行；否則回傳 true，執行主查詢

all 運算子的含義如下：

• 當 <all 時，表示小于最小值
• 當 >all 時，表示大于最大值

語法：

運算式或欄位?多行比較運算子?all( 子查詢 )

查詢比所有“喜劇”類題材電影的票價都高的電影資訊，要求輸出片名和導演名

示例：

（1）查詢型別為“喜劇”的電影型別編號

select id from movie_type where typeName=' 喜劇 '

（2）查詢所有“喜劇”類題材電影的票價

select ticketPrice from movie where typeID=(
select id from movie_type where typeName=' 喜劇 ')

（4）查詢比所有“戰爭”類題材電影的票價都高的電影資訊

select movieName 電影名 ,director 導演名 from movie where ticketPrice > all  (select ticketPrice from movie where typeID=(select id from movie_type where typeName=' 喜劇 '))

由于“>all（子查詢）”的含義是“大于子查詢回傳結果的最大值”，所以還可以采用“>（子查詢所獲取的最大列值）”的方式求解上面這個例子，該方法的 SQL 陳述句如下：

select movieName 電影名 ,director 導演名 from movie where ticketPrice > (select max(ticketPrice) from movie where typeID=( select id from movie_type where typeName=' 喜劇 ' ))

3.2.3 any|some 關鍵字子查詢

any 與 some 的查詢功能相同

any 或 some 用于子查詢之前，通過 any|some 比較運算子，將一個運算式或列的值與子查詢所回傳的一列值中的每一行進行比較，只要有一次比較的結果為 true，則 any 或 some 測驗回傳 true，主查詢執行；否則結果為false，主查詢不執行

any|some 運算子的含義如下：

• 當 <any|some 時，表示小于最大值
• 當 =any|some 時，表示與 in 運算子等價
• 當 >any|some 時，表示大于最小值

語法：

運算式或欄位??多行比較運算子??any|some（子查詢）

查詢比任意一個“喜劇”類題材電影的票價高的電影資訊，要求輸出電影名和導演名

示例：

select movieName 電影名 ,director 導演名 from movie where ticketPrice > any
(select ticketPrice from movie where typeID=(
select id from movie_type where typeName=' 喜劇 '))

由于“>any（子查詢）”的含義是“大于子查詢回傳結果的最小值”，所以還可以采用“>（子查詢所獲取的最小列值）”的方式求解上面這個例子，該方法的 SQL 陳述句如下：

select movieName 電影名 ,director 導演名 from movie where ticketPrice > (select min(ticketPrice) from movie where typeID=( select id from movie_type where typeName=' 喜劇 ' ))

3.2.4 實踐練習

3.3 子查詢特殊應用

3.3.1 from 子句中的子查詢

子查詢通常用于 where 子句中，但其也可在 from 子句和 select 子句中使用

示例：

影院在線售票系統為了提升影片的上座率，優化票價定價的科學性，為了了解每一個影片的票價與該類題材電影整體票價的對照關系，比較合適的做法是在顯示每一個影片票價的同時，顯示所屬型別電影的平均票價

select mt.typeName 電影型別 , m.movieName 電影名 , m.director 導演名 , round(m.ticketPrice,2)
票價（元）, round(A.avgPrice,2) 該電影型別平均票價（元） from movie m, movie_type mt,
(select typeId,avg(ticketPrice) avgPrice from movie group by typeId) A where m.typeId=mt.id and m. typeId=A.typeId order by mt.id

3.3.2 select 子句中的子查詢

在 select 子句中使用子查詢，其實質是將子查詢的執行結果作為 select 子句的列，可以起到與連接查詢異曲同工的作用

示例：

分別獲取張藝謀所導演影片的上映數量以及上映班次

（1）獲取張藝謀所導演影片的上映數量

select count(*) 張藝謀所導演影片的上映數量 from movie where director=' 張藝謀 '

（2）獲取張藝謀所導演影片的上映班次

select count(movieId) 張藝謀所導演影片的上映班次 from `schedule` where movieId in
(select id from movie where director=' 張藝謀 ')

（3）將（1）和（2）獲得的查詢結果，即張藝謀所導演影片的上映數量和上映班次作為 select 子句的查詢列，其形式即為 select 子句中的子查詢，

select count(*) 張藝謀所導演影片的上映數量 , (select count(movieId) from `schedule` where
movieId in (select id from movie where director=' 張藝謀 ')) 張藝謀所導演影片的上映班次
from movie where director=' 張藝謀 '

exists 用于檢查子查詢是否至少會回傳一行資料，該子查詢實際上并不回傳任何資料，而是回傳值 true 或false，exists 指定一個子查詢，用于檢測行的存在，當子查詢的行存在時，則執行主查詢運算式，否則不執行

查詢所有通過影院在線售票系統預訂電影票的客戶姓名

語法：

主查詢運算式??[not]?exists?( 子查詢 )

示例：

（1）主查詢用于從客戶表獲取客戶姓名，

（2）exists 指定的子查詢將從售票表中獲取滿足“客戶編號 = 客戶表 . 客戶編號”條件的任意資料，

（3）只要 exists 子查詢的結果集中有資料行回傳，exists 子查詢的回傳結果若為 true，則執行主查詢獲得所有預訂電影票的客戶姓名；exists 子查詢的回傳結果若為 false，則不執行主查詢，最終的 SQL 陳述句如下：

select customerName 客戶姓名 from customer c where exists
(select * from orders where customerID=c.customerID)

3.3.4 實踐練習

3.4 DML 陳述句中的子查詢

3.4.1 update 子句中的子查詢

• 子查詢不僅可在 select 陳述句中使用，以實作需要嵌套的查詢功能，還可以維護資料，完成復雜的更新、洗掉和插入功能
• 為了完成上述資料維護功能，需要在 DML 的 update 陳述句、delete 陳述句和 insert 陳述句中使用子查詢
• 在 DML 陳述句中使用子查詢與在 select 陳述句中使用子查詢的原理是一致的，均為將內層子查詢的結果作為外層主查詢中 where 條件的參考值來使用

示例：

為回應政府提升公民災難意識和應對能力的號召，院線將所有災難片電影的票價降低 20%

（1）在子查詢中獲取型別為“災難”的電影型別編號，

（2）在主查詢中，使用 update 陳述句將所有災難片電影的票價降低 20%

update product set currentPrice=currentPrice*0.9 where categoryID in(
select categoryID from category where categoryName=' 災難 ')

示例：

計算所有客戶預訂電影票的總金額，并使用該金額更新客戶表中“累計訂票金額”的欄位值

（1）在售票表中，根據客戶編號進行分組，并使用“sum( 實際票價 )”匯總出每個客戶的總訂票金額

select customerID 客戶編號 , sum(purchasePrice) 總訂票金額（元） from ticket_sellgroup by customerID

（2）因為要將（1）中匯總出的每個客戶的總訂票金額賦給客戶表中的“累計訂票金額”欄位，所以可以將（1）中的 SQL 作為子查詢，并在主查詢中執行“update 客戶表 set 累計購票金額 =( 子查詢中獲取的每個客戶的總訂票金額 )”，

（3）為實作 update 陳述句，需在子查詢中洗掉選擇列“客戶編號”，并且為了建立主查詢與子查詢的關聯，還需要在子查詢的 where 條件中設定“客戶編號 = 售票表 . 客戶編號”

update customer c set totalFee=(select sum(purchasePrice) from ticket_sell where customerID=c.ID group by customerID)

使用子查詢洗掉客戶“chen01”

示例：

洗掉資料時需要考慮表的主從關系，正確的做法是先洗掉從表資料，再洗掉主表資料，

（1）使用子查詢洗掉售票表中客戶“chen01”所有的訂票記錄

delete from ticket_sell where customerID in
(select id from customer where username='chen01')

（2）洗掉客戶表中客戶“chen01”的記錄

delete from customer where username='chen01'

3.4.3 實踐練習

總結：

• 子查詢將一個查詢陳述句嵌套在另一個查詢陳述句中，在特定情況下，一個查詢陳述句的條件需要另一個查詢陳述句來獲取
• 比較運算子 all 關鍵字用于子查詢之前，通過該關鍵字將一個運算式或列的值，與子查詢所回傳的一列值中的每一行進行比較
• exists 用于檢測行的存在，該子查詢實際上并不回傳任何資料，而是回傳值 true 或false，當子查詢的行存在時，則執行主查詢運算式，否則不執行
• 在 DML 陳述句中使用子查詢與在 select 陳述句中使用子查詢的原理是一致的，均為將內層子查詢的結果作為外層主查詢中 where 條件的參考值來使用