專欄作者新書出版：Android App程式開發剖析 第三版（適用Android 8 Oreo與Android Studio 3）

MySQL 超新手入門（9）表格與索引 << 前情

1 一個敘述中的查詢敘述

子查詢(subquery)是一種很常見的應用，不論是查詢、新增、修改或刪除都有可能出現。子查詢是一個放在左右刮號中的「SELECT」敘述，而這個查詢敘述會放在另一個SQL敘述中。在執行一些工作的時候，使用子查詢可以簡化SQL敘述。以查詢「人口比美國多的國家」來說，你要先執行下列查詢美國人口數量的敘述：

知道美國人口數量以後，再執行下列的敘述就可以傳回人口比美國多的國家了：

以這樣的查詢來說，你要執行兩次查詢敘述來完成這個工作。不過遇到類似這樣的需求時：

你就可以考慮把它們寫成一個敘述就可以了：

上列的範例是一種很常見的子查詢應用，使用子查詢的好處是不用執行多次查詢就可以完成工作，這樣可以簡化查詢的工作；對於處理資料的應用程式來說，也可以節省一些程式碼。

2 WHERE、HAVING子句與子查詢

子查詢大部份使用在提供判斷條件用的資料，在「WHERE」和「HAVING」子句中，都可能出現子查詢：

2.1 比較運算子

在「WHERE」和「HAVING」子句中，你會使用許多不同的運算子來判斷條件是否符合。這些運算子中的比較運算子都可以搭配子查詢來完成你的需求：

使用比較運算子的時候，你要提供一個資料讓運算子判斷條件是否符合。在使用子查詢提供判斷用的資料時，要特別注意子查詢回傳的資料是否符合規定：

以下列「查詢GNP最大的國家」需求來說，子查詢傳回的數字是「country」表格中「GNP」欄位的最大值，這個數字就給外層查詢當作「WHERE」子句中的條件設定：

使用在比較運算子的子查詢，在「SELECT」子句中不可以指定超過一個欄位的回傳資料：

子查詢也不可以回傳超過一筆以上的紀錄：

2.2 「IN」運算子

除了一般的比較運算子外，你可能很常使用「IN」運算子來執行多個資料的比較，你也可以使用子查詢提供「IN」運算子判斷的資料：

如果你想要查詢「城市人口超過九百萬的國家」，「IN」運算子就會出現在這類的需求中：

這類的需求，也可以改成使用子查詢來完成：

以上列的範例來說，如果你用錯運算子：

「IN」運算子可以視需要搭配「NOT」運算子：

2.3 其它運算子

比較運算子與子查詢搭配使用時，另外還提供「ALL」、「ANY」與「SOME」三個運算子，其中「ANY」和「SOME」運算子的效果是一樣的，所以只需要討論「ALL」與「ANY」這兩個運算子：

比較運算子與「ALL」與「ANY」搭配使用時，可以完成比較特殊的查詢需求，下列是兩個用來測試的表格：

下列是比較運算子與「ALL」搭配使用的範例：

「ALL」運算子從字面上來看，是「全部」的意思，所以你也可以這樣來看「ALL」運算子：

「ANY」運算子從字面上來看，是「任何一個」的意思，所以你也可以這樣來看「ANY」運算子：

註：在MySQL中，「ANY」與「SOME」運算子的效果是一樣的。

在你瞭解「ALL」運算子的效果以後，如果在解決你的需求時，使用了「<> ALL」這樣的運算子，它的效果其實跟「NOT IN」是一樣的：

另外「= ANY」運算子的效果跟「IN」是一樣的：

2.4 多欄位子查詢

在條件設定的時候，通常會也遇到比較複雜一點的設定，例如下列這個查詢「在亞洲而且政府型式為Republic的國家」敘述：

上列的條件設定，有另外一種比較簡單的設定方式：

如果想要查詢「跟Iraq國家同一個地區，而且跟Iraq國家的政府型式一樣的國家」，因為判斷條件都要經由查詢才可以得到，所以你可能會寫出這樣的敘述：

遇到類似這樣的需求時，你也可以套用這種比較簡單的設定方式：

如果想要查詢「每一洲GNP最高的國家」，你可以使用下列的敘述先查詢每一洲最高的GNP：

跟單一資料的判斷一樣，子查詢傳回多比紀錄時就要使用「IN」運算子：

3 SELECT子句與子查詢

如果需要的話，子查詢也可以使用在「SELECT」子句中。以查詢「國家Japan的GNP」的需求來說，下列的範例使用的是你已經熟悉的查詢敘述來完成這個需求：

這類的需求也可以直接在「SELECT」子句中使用子查詢傳回你需要的資料：

下列的敘述可以查詢「India國家佔全世界人口的比例」：

4 FROM子句與子查詢

子查詢可以使用在「WHERE」與「HAVGIN」子句中用來設定條件，還有使用在「SELECT」子句中，用來傳回需要的資料；除了這兩種用法外，子查詢還可以使用在「FROM」子句。你通常會在在查詢敘述的「FROM」子句中，指定需要的表格名稱，有需要的話，你也可以使用子查詢，這個子查詢回傳的結果會被當成一個「表格」：

下列的範例可以查詢「亞洲GNP前十名國家」：

註：要完成上列的需求，並不需要在「FROM」子句中使用子查詢，只要使用一般的查詢敘述就可以了。

如果以查詢「國家的官方語言與人口比例」的需求來說，你可以使用下列的查詢敘述來完成這個工作：

註：要完成上列的需求，並不需要在「FROM」子句中使用子查詢，使用結合查詢也可以得到一樣的結果。

5 資料維護與子查詢

在使用「INSERT」、「UPDATE」與「DELETE」敘述執行新增、修改與刪除資料時，也可以依照需要使用子查詢來簡化資料維護的敘述。

5.1 新增

一般來說，使用「INSERT」敘述執行新增紀錄的工作時，通常是直接指定新增紀錄的資料；如果你要新增的資料，可以執行一個查詢來取得的話，就可以搭配子查詢來簡化新增紀錄的工作：

以下列這個儲存國家資料的表格(world.mycountry)來說：

如果你想要新增亞洲國家的資料到「mycountry」表格中，你可以使用子查詢傳回新增紀錄需要的資料給「INSERT」敘述使用：

使用子查詢提供「INSERT」敘述需要的資料，要特別注意子查詢回傳的欄位資料：

註：搭配「ON DUPLICATE KEY UPDATE」的效果在「資料維護、新增、索引值與ON DUPLICATE KEY UPDATE」中討論。

MySQL另外一種新增紀錄的「REPLACE」敘述，也可以使用子查詢提供需要的資料：

5.2 修改

使用「UPDATE」敘述執行修改資料時，如果沒有使用「WHERE」子句指定修改的條件，「UPDATE」敘述會修改表格中所有的紀錄；所以執行修改紀錄資料的時候，通常會使用「WHERE」子句指定修改的條件。在「UPDATE」敘述的「WHERE」子句中，也可以使用子查詢提供判斷條件的資料：

如果要執行「SALES部門的員工加薪百分之五」，因為你需要先知道「SALES」部門的編號，所以你可以使用子查詢傳回「SALES」部門的編號，給「UPDATE」敘述中的「WHERE」子句設定部門編號的條件：

MySQL在「UPDATE」敘述中的子查詢有一個特別的規定：

5.3 刪除

使用「DELETE」敘述執行刪除紀錄時，如果沒有使用「WHERE」子句指定刪除的條件，「DELETE」敘述會刪除表格中所有的紀錄；所以執行刪除紀錄的時候，通常會使用「WHERE」子句指定刪除的條件。在「DELETE」敘述的「WHERE」子句中，也可以使用子查詢提供判斷條件的資料：

如果要執行「刪除SALES部門員工」，因為你需要先知道「SALES」部門的編號，所以你可以使用子查詢傳回「SALES」部門的編號，給「DELETE」敘述中的「WHERE」子句設定部門編號的條件：

MySQL在「DELETE」敘述中出現的子查詢有一個特別的規定：

6 關聯子查詢

在查詢或維護的查詢中，都有可能會使用子查詢來提供執行敘述所需要的資料：

在使用子查詢的的時候，通常不會跟外層查詢有直接的關係，也就是子查詢不會使用外層查詢的資料；不過遇到一些比較特殊的需求時，在「WHERE」或「HAVING」子句中的子查詢，也需要使用外層查詢的資料來執行判斷的工作，這樣的敘述稱為「關聯子查詢、correlated subqueries」：

在「WHERE」或「HAVING」子句中用來設定條件的子查詢，可以依照需求使用像「IN」、「ANY」這些運算子來判斷條件是否符合。除了上列以經討論的比較運算子外，還有一個「EXISTS」運算子：

「EXISTS」運算子判斷條件是否成立的依據比較不一樣，如果子查詢有任何紀錄資料回傳，條件就算成立：

「EXISTS」運算子通常會在使用關聯子查詢中：

「EXISTS」與「NOT」一起使用時，就可以完成下列的查詢需求：

7 子查詢與結合查詢

子查詢的應用通常可以簡化許多工作，而一些子查詢完成的工作，也可以改用其它的作法來完成。例如下列查詢「所有國家首都名稱」的敘述：

把上列的需求改用結合查詢來完成的話，其實看起來會更簡單一些：

如果需求換成查詢「不是首都的城市名稱」，可以使用下列搭配子查詢的作法：

上列的需求要改成使用結合查詢來完成的話，會比較不一樣。所以要先瞭解使用「LEFT JOIN」結合查詢的效果：

根據「LEFT JOIN」結合查詢產生的效果，為這個結合查詢設定適當的條件，就可以完成查詢「不是首都的城市名稱」：

後續 >> MySQL 超新手入門（11）Views