子查询

一个叙述种的查询叙述

子查询(subquery)是一种很常见的应用，不论是查询、新增、修改或删除都有可能出现。子查询是一个放在左右刮号中的“SELECT”叙述，而这个查询叙述会放在另一个SQL叙述中


SELECT Population FROM country WHERE Code = 'AGO';    # 12878000

知道这个国家的人口数量后，在执行下列查询就可以传回比美国人口多的国家了


SELECT CODE,Population FROM country WHERE Population  > 12878000;

以这样的查询来说，你需要执行两次查询来完成这个工作，你可以考虑把他们写成一个叙述就可以了


SELECT CODE,Population FROM country WHERE Population  > (SELECT Population FROM country WHERE Code = 'AGO')

WHERE、HAVING 子句与子查询

比较运算子

在“WHERE”和“HAVING”子句中，你会使用许多不同的运算子来判断条件是否符合。这些运算子中的比较运算子都可以搭配子查询来完成你的需求：

使用比较运算子的时候，你要提供一个资料让运算子判断条件是否符合

下列“查询GNP最大的国家”需求来说，子查询传回的数字是“country”表格中“GNP”字段的最大值，这个数字就给外层查询当作“WHERE”子句中的条件设定：

使用在比较运算子的子查询，在“SELECT”子句中不可以指定~~超过一个字段的回传资料：~~

子查询也不可以回传超过一笔以上的纪录：

2.2 “IN”运算子

除了一般的比较运算子外，你可能很常使用“IN”运算子来执行多个资料的比较，你也可以使用子查询提供“IN”运算子判断的资料：

如果你想要查询“城市人口超过九百万的国家”，“IN”运算子就会出现在这类的需求中：

同样，你也可以改用子查询来完成：


SELECT Name
FROM country
WHERE Code IN (SELECT CountryCode
               FROM city
               WHERE city.Population > 900000)

“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 是一样的：

多字段子查询

在条件设定的时候，通常会也遇到比较复杂一点的设定，例如下列这个查询“在亚洲而且政府型式为Republic的国家”叙述：

上列的条件设定，有另外一种比较简单的设定方式：

SELECT
  Name,
  GNP
FROM country
WHERE (Continent, GovernmentForm) = ('Asia', 'Republic')

如果想要查询“跟Iraq国家同一个地区，而且跟Iraq国家的政府型式一样的国家”，因为判断条件都要经由查询才可以得到，所以你可能会写出这样的叙述：

你也可以套用这种比较简单的设定方式：

如果想要查询“每一洲GNP最高的国家”，你可以使用下列的叙述先查询每一洲最高的GNP：

跟单一资料的判断一样，子查询传回多比纪录时就要使用“IN”运算子：

SELECT
  Continent,
  Name,
  GNP
FROM country
WHERE (Continent, GNP) IN (SELECT
                             Continent,
                             MAX(GNP)
                           FROM country
                           GROUP BY Continent)

SELECT子句与子查询

如果需要的话，子查询也可以使用在“SELECT”子句中

以查询“国家Japan的GNP”

SELECT CONCAT('The GNP of Aruba is ', GNP)
FROM country
WHERE Name = 'Aruba'

这样的需求也可以在在 SELECT 子句中查询传回你需要的资料

SELECT CONCAT('The GNP of Aruba is ', (SELECT GNP
                                       FROM country
                                       WHERE Name = 'Aruba'));

SELECT CONCAT('The GNP of Aruba is ', (SELECT GNP FROM country WHERE Name = 'Aruba'));
The GNP of Aruba is 828.00

FROM子句与子查询

子查询可以使用在“WHERE”与“HAVGIN”子句中用来设定条件，还有使用在“SELECT”子句中，用来传回需要的资料,子查询还可以使用在“FROM”子句

查询“亚洲GNP前十名国家”：

注：

要完成上列的需求，并不需要在“FROM”子句中使用子查询，只要使用一般的查询叙述就可以了。

如果以查询“国家的官方语言与人口比例”的需求来说，你可以使用下列的查询叙述来完成这个工作：

注：

要完成上列的需求，并不需要在“FROM”子句中使用子查询，使用结合查询也可以得到一样的结果。

资料维护与子查询

在使用 INSERT、UPDATE 与 DELETE叙述执行新增、修改与删除资料时，也可以依照需要使用子查询来简化资料维护的叙述。

新增

一般来说，使用“INSERT”叙述执行新增纪录的工作时，通常是直接指定新增纪录的资料；如果你要新增的资料，可以执行一个查询来取得的话，就可以搭配子查询来简化新增纪录的工作：

以下列这个储存国家资料的表格(world.mycountry)来说：

如果你想要新增亚洲国家的资料到“mycountry”表格中，你可以使用子查询传回新增纪录需要的资料给“INSERT”叙述使用：

使用子查询提供“INSERT”叙述需要的资料，要特别注意子查询回传的字段资料：

注：

搭配“ON DUPLICATE KEY UPDATE”的效果在“资料维护、新增、索引值与ON DUPLICATE KEY UPDATE”中讨论。

MySQL另外一种新增纪录的“REPLACE”叙述，也可以使用子查询提供需要的资料：

修改

使用“UPDATE”叙述执行修改资料时，如果没有使用“WHERE”子句指定修改的条件，“UPDATE”叙述会修改表格中所有的纪录；所以执行修改纪录资料的时候，通常会使用“WHERE”子句指定修改的条件。在“UPDATE”叙述的“WHERE”子句中，也可以使用子查询提供判断条件的资料：

如果要执行“SALES部门的员工加薪百分之五”，因为你需要先知道“SALES”部门的编号，所以你可以使用子查询传回“SALES”部门的编号，给“UPDATE”叙述中的“WHERE”子句设定部门编号的条件：

MySQL在“UPDATE”叙述中的子查询有一个特别的规定：

删除

使用 DELETE 叙述执行删除记录时，如果没有使用 WHERE 子句指定删除的条件， DELETE 会删除表格中的所有记录。所以指定删除记录的时候，通常会使用WHERE子句指定删除的条件。在 DELETE 叙述的 WHERE 子句中，也可以使用子查询提供判断条件的资料

删除和子查询不可以出现相同的表格

关联子查询

在使用子查询的的时候，通常不会跟外层查询有直接的关系，也就是子查询不会使用外层查询的资料；不过遇到一些比较特殊的需求时，在“WHERE”或“HAVING”子句中的子查询，也需要使用外层查询的资料来执行判断的工作，这样的叙述称为“关联子查询、correlated subqueries”：