**目錄**
[TOC]
# 1 一個敘述中的查詢敘述
子查詢(subquery)是一種很常見的應用,不論是查詢、新增、修改或刪除都有可能出現。子查詢是一個放在左右刮號中的“SELECT”敘述,而這個查詢敘述會放在另一個SQL敘述中。在執行一些工作的時候,使用子查詢可以簡化SQL敘述。以查詢“人口比美國多的國家”來說,你要先執行下列查詢美國人口數量的敘述:
[](http://box.kancloud.cn/2015-09-15_55f7eaffa8a17.png)
知道美國人口數量以后,再執行下列的敘述就可以傳回人口比美國多的國家了:
[](http://box.kancloud.cn/2015-09-15_55f7eb003beda.png)
以這樣的查詢來說,你要執行兩次查詢敘述來完成這個工作。不過遇到類似這樣的需求時:
[](http://box.kancloud.cn/2015-09-15_55f7eb008ee9b.png)
你就可以考慮把它們寫成一個敘述就可以了:
[](http://box.kancloud.cn/2015-09-15_55f7eb00f25cb.png)
上列的范例是一種很常見的子查詢應用,使用子查詢的好處是不用執行多次查詢就可以完成工作,這樣可以簡化查詢的工作;對于處理資料的應用程式來說,也可以節省一些程式碼。
# 2 WHERE、HAVING子句與子查詢
子查詢大部份使用在提供判斷條件用的資料,在“WHERE”和“HAVING”子句中,都可能出現子查詢:
[](http://box.kancloud.cn/2015-09-15_55f7eb01b3c00.png)
## 2.1 比較運算子
在“WHERE”和“HAVING”子句中,你會使用許多不同的運算子來判斷條件是否符合。這些運算子中的比較運算子都可以搭配子查詢來完成你的需求:
[](http://box.kancloud.cn/2015-09-15_55f7eb020d9ba.png)
使用比較運算子的時候,你要提供一個資料讓運算子判斷條件是否符合。在使用子查詢提供判斷用的資料時,要特別注意子查詢回傳的資料是否符合規定:
[](http://box.kancloud.cn/2015-09-15_55f7eb07681f5.png)
以下列“查詢GNP最大的國家”需求來說,子查詢傳回的數字是“country”表格中“GNP”字段的最大值,這個數字就給外層查詢當作“WHERE”子句中的條件設定:
[](http://box.kancloud.cn/2015-09-15_55f7eb07c5b9d.png)
使用在比較運算子的子查詢,在“SELECT”子句中不可以指定超過一個字段的回傳資料:
[](http://box.kancloud.cn/2015-09-15_55f7eb0843997.png)
子查詢也不可以回傳超過一筆以上的紀錄:
[](http://box.kancloud.cn/2015-09-15_55f7eb087d11b.png)
## 2.2 “IN”運算子
除了一般的比較運算子外,你可能很常使用“IN”運算子來執行多個資料的比較,你也可以使用子查詢提供“IN”運算子判斷的資料:
[](http://box.kancloud.cn/2015-09-15_55f7eb08c0e34.png)
如果你想要查詢“城市人口超過九百萬的國家”,“IN”運算子就會出現在這類的需求中:
[](http://box.kancloud.cn/2015-09-15_55f7eb0929e97.png)
這類的需求,也可以改成使用子查詢來完成:
[](http://box.kancloud.cn/2015-09-15_55f7eb0eae3be.png)
以上列的范例來說,如果你用錯運算子:
[](http://box.kancloud.cn/2015-09-15_55f7eb0f2773a.png)
“IN”運算子可以視需要搭配“NOT”運算子:
[](http://box.kancloud.cn/2015-09-15_55f7eb0f8a2ba.png)
## 2.3 其它運算子
比較運算子與子查詢搭配使用時,另外還提供“ALL”、“ANY”與“SOME”三個運算子,其中“ANY”和“SOME”運算子的效果是一樣的,所以只需要討論“ALL”與“ANY”這兩個運算子:
[](http://box.kancloud.cn/2015-09-15_55f7eb105b94b.png)
比較運算子與“ALL”與“ANY”搭配使用時,可以完成比較特殊的查詢需求,下列是兩個用來測試的表格:
[](http://box.kancloud.cn/2015-09-15_55f7eb10ce012.png)
下列是比較運算子與“ALL”搭配使用的范例:
[](http://box.kancloud.cn/2015-09-15_55f7eb11570e8.png)
“ALL”運算子從字面上來看,是“全部”的意思,所以你也可以這樣來看“ALL”運算子:
[](http://box.kancloud.cn/2015-09-15_55f7eb11d5887.png)
“ANY”運算子從字面上來看,是“任何一個”的意思,所以你也可以這樣來看“ANY”運算子:
[](http://box.kancloud.cn/2015-09-15_55f7eb122aa6c.png)
注:在MySQL中,“ANY”與“SOME”運算子的效果是一樣的。
在你了解“ALL”運算子的效果以后,如果在解決你的需求時,使用了“ ALL”這樣的運算子,它的效果其實跟“NOT IN”是一樣的:
[](http://box.kancloud.cn/2015-09-15_55f7eb12b144a.png)
另外“= ANY”運算子的效果跟“IN”是一樣的:
[](http://box.kancloud.cn/2015-09-15_55f7eb1335111.png)
## 2.4 多字段子查詢
在條件設定的時候,通常會也遇到比較復雜一點的設定,例如下列這個查詢“在亞洲而且政府型式為Republic的國家”敘述:
[](http://box.kancloud.cn/2015-09-15_55f7eb13732f9.png)
上列的條件設定,有另外一種比較簡單的設定方式:
[](http://box.kancloud.cn/2015-09-15_55f7eb1406c1b.png)
如果想要查詢“跟Iraq國家同一個地區,而且跟Iraq國家的政府型式一樣的國家”,因為判斷條件都要經由查詢才可以得到,所以你可能會寫出這樣的敘述:
[](http://box.kancloud.cn/2015-09-15_55f7eb1474a92.png)
遇到類似這樣的需求時,你也可以套用這種比較簡單的設定方式:
[](http://box.kancloud.cn/2015-09-15_55f7eb14a89fe.png)
如果想要查詢“每一洲GNP最高的國家”,你可以使用下列的敘述先查詢每一洲最高的GNP:
[](http://box.kancloud.cn/2015-09-15_55f7eb1556271.png)
跟單一資料的判斷一樣,子查詢傳回多比紀錄時就要使用“IN”運算子:
[](http://box.kancloud.cn/2015-09-15_55f7eb1594241.png)
# 3 SELECT子句與子查詢
如果需要的話,子查詢也可以使用在“SELECT”子句中。以查詢“國家Japan的GNP”的需求來說,下列的范例使用的是你已經熟悉的查詢敘述來完成這個需求:
[](http://box.kancloud.cn/2015-09-15_55f7eb163696f.png)
這類的需求也可以直接在“SELECT”子句中使用子查詢傳回你需要的資料:
[](http://box.kancloud.cn/2015-09-15_55f7eb1baadc7.png)
下列的敘述可以查詢“India國家占全世界人口的比例”:
[](http://box.kancloud.cn/2015-09-15_55f7eb1bda0f8.png)
# 4 FROM子句與子查詢
子查詢可以使用在“WHERE”與“HAVGIN”子句中用來設定條件,還有使用在“SELECT”子句中,用來傳回需要的資料;除了這兩種用法外,子查詢還可以使用在“FROM”子句。你通常會在在查詢敘述的“FROM”子句中,指定需要的表格名稱,有需要的話,你也可以使用子查詢,這個子查詢回傳的結果會被當成一個“表格”:
[](http://box.kancloud.cn/2015-09-15_55f7eb1e2f02a.png)
下列的范例可以查詢“亞洲GNP前十名國家”:
[](http://box.kancloud.cn/2015-09-15_55f7eb1e72e42.png)
注:要完成上列的需求,并不需要在“FROM”子句中使用子查詢,只要使用一般的查詢敘述就可以了。
如果以查詢“國家的官方語言與人口比例”的需求來說,你可以使用下列的查詢敘述來完成這個工作:
[](http://box.kancloud.cn/2015-09-15_55f7eb23c84a6.png)
注:要完成上列的需求,并不需要在“FROM”子句中使用子查詢,使用結合查詢也可以得到一樣的結果。
# 5 資料維護與子查詢
在使用“INSERT”、“UPDATE”與“DELETE”敘述執行新增、修改與刪除資料時,也可以依照需要使用子查詢來簡化資料維護的敘述。
## 5.1 新增
一般來說,使用“INSERT”敘述執行新增紀錄的工作時,通常是直接指定新增紀錄的資料;如果你要新增的資料,可以執行一個查詢來取得的話,就可以搭配子查詢來簡化新增紀錄的工作:
[](http://box.kancloud.cn/2015-09-15_55f7eb29cf80b.png)
以下列這個儲存國家資料的表格(world.mycountry)來說:
[](http://box.kancloud.cn/2015-09-15_55f7eb2a62fe3.png)
如果你想要新增亞洲國家的資料到“mycountry”表格中,你可以使用子查詢傳回新增紀錄需要的資料給“INSERT”敘述使用:
[](http://box.kancloud.cn/2015-09-15_55f7eb2ae4a0c.png)
使用子查詢提供“INSERT”敘述需要的資料,要特別注意子查詢回傳的字段資料:
[](http://box.kancloud.cn/2015-09-15_55f7eb30640a4.png)
注:搭配“ON DUPLICATE KEY UPDATE”的效果在“資料維護、新增、索引值與ON DUPLICATE KEY UPDATE”中討論。
MySQL另外一種新增紀錄的“REPLACE”敘述,也可以使用子查詢提供需要的資料:
[](http://box.kancloud.cn/2015-09-15_55f7eb42749af.png)
## 5.2 修改
使用“UPDATE”敘述執行修改資料時,如果沒有使用“WHERE”子句指定修改的條件,“UPDATE”敘述會修改表格中所有的紀錄;所以執行修改紀錄資料的時候,通常會使用“WHERE”子句指定修改的條件。在“UPDATE”敘述的“WHERE”子句中,也可以使用子查詢提供判斷條件的資料:
[](http://box.kancloud.cn/2015-09-15_55f7eb42e20f0.png)
如果要執行“SALES部門的員工加薪百分之五”,因為你需要先知道“SALES”部門的編號,所以你可以使用子查詢傳回“SALES”部門的編號,給“UPDATE”敘述中的“WHERE”子句設定部門編號的條件:
[](http://box.kancloud.cn/2015-09-15_55f7eb434b1f4.png)
MySQL在“UPDATE”敘述中的子查詢有一個特別的規定:
[](http://box.kancloud.cn/2015-09-15_55f7eb437f635.png)
## 5.3 刪除
使用“DELETE”敘述執行刪除紀錄時,如果沒有使用“WHERE”子句指定刪除的條件,“DELETE”敘述會刪除表格中所有的紀錄;所以執行刪除紀錄的時候,通常會使用“WHERE”子句指定刪除的條件。在“DELETE”敘述的“WHERE”子句中,也可以使用子查詢提供判斷條件的資料:
[](http://box.kancloud.cn/2015-09-15_55f7eb42e20f0.png)
如果要執行“刪除SALES部門員工”,因為你需要先知道“SALES”部門的編號,所以你可以使用子查詢傳回“SALES”部門的編號,給“DELETE”敘述中的“WHERE”子句設定部門編號的條件:
[](http://box.kancloud.cn/2015-09-15_55f7eb434b1f4.png)
MySQL在“DELETE”敘述中出現的子查詢有一個特別的規定:
[](http://box.kancloud.cn/2015-09-15_55f7eb4e7dbfd.png)
# 6 關聯子查詢
在查詢或維護的查詢中,都有可能會使用子查詢來提供執行敘述所需要的資料:
[](http://box.kancloud.cn/2015-09-15_55f7eb4ec20a9.png)
在使用子查詢的的時候,通常不會跟外層查詢有直接的關系,也就是子查詢不會使用外層查詢的資料;不過遇到一些比較特殊的需求時,在“WHERE”或“HAVING”子句中的子查詢,也需要使用外層查詢的資料來執行判斷的工作,這樣的敘述稱為“關聯子查詢、correlated subqueries”:
[](http://box.kancloud.cn/2015-09-15_55f7eb54281d1.png)
在“WHERE”或“HAVING”子句中用來設定條件的子查詢,可以依照需求使用像“IN”、“ANY”這些運算子來判斷條件是否符合。除了上列以經討論的比較運算子外,還有一個“EXISTS”運算子:
[](http://box.kancloud.cn/2015-09-15_55f7eb5a199f6.png)
“EXISTS”運算子判斷條件是否成立的依據比較不一樣,如果子查詢有任何紀錄資料回傳,條件就算成立:
[](http://box.kancloud.cn/2015-09-15_55f7eb5a77c2b.png)
“EXISTS”運算子通常會在使用關聯子查詢中:
[](http://box.kancloud.cn/2015-09-15_55f7eb5aabb74.png)
“EXISTS”與“NOT”一起使用時,就可以完成下列的查詢需求:
[](http://box.kancloud.cn/2015-09-15_55f7eb5b43fba.png)
# 7 子查詢與結合查詢
子查詢的應用通常可以簡化許多工作,而一些子查詢完成的工作,也可以改用其它的作法來完成。例如下列查詢“所有國家首都名稱”的敘述:
[](http://box.kancloud.cn/2015-09-15_55f7eb60a9d82.png)
把上列的需求改用結合查詢來完成的話,其實看起來會更簡單一些:
[](http://box.kancloud.cn/2015-09-15_55f7eb76002c6.png)
如果需求換成查詢“不是首都的城市名稱”,可以使用下列搭配子查詢的作法:
[](http://box.kancloud.cn/2015-09-15_55f7eb767cbf0.png)
上列的需求要改成使用結合查詢來完成的話,會比較不一樣。所以要先了解使用“LEFT JOIN”結合查詢的效果:
[](http://box.kancloud.cn/2015-09-15_55f7eb7793072.png)
根據“LEFT JOIN”結合查詢產生的效果,為這個結合查詢設定適當的條件,就可以完成查詢“不是首都的城市名稱”:
[](http://box.kancloud.cn/2015-09-15_55f7eb786f8ff.png)