MySQL入门 (九) : 子查询 Subquery

1 一个叙述中的查询叙述

子查询(subquery)是一种很常见的应用,不论是查询、新增、修改或删除都有可能出现。 子查询是一个放在左右刮号中的「SELECT」叙述,而这个查询叙述会放在另一个SQL叙述中。 在执行一些工作的时候,使用子查询可以简化SQL叙述。 以查询「人口比美国多的国家」来说,你要先执行下列查询美国人口数量的叙述:

mysql_10_snap_01

知道美国人口数量以后,再执行下列的叙述就可以传回人口比美国多的国家了:

mysql_10_snap_02

以这样的查询来说,你要执行两次查询叙述来完成这个工作。 不过遇到类似这样的需求时:

mysql_10_snap_03

你就可以考虑把它们写成一个叙述就可以了:

mysql_10_snap_04

上列的范例是一种很常见的子查询应用,使用子查询的好处是不用执行多次查询就可以完成工作,这样可以简化查询的工作;对于处理资料的应用程式来说,也可以节省一些程式码。

2 WHERE、HAVING子句与子查询

子查询大部份使用在提供判断条件用的资料,在「WHERE」和「HAVING」子句中,都可能出现子查询:

mysql_10_snap_05

2.1 比较运算子

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

mysql_10_snap_06

使用比较运算子的时候,你要提供一个资料让运算子判断条件是否符合。 在使用子查询提供判断用的资料时,要特别注意子查询回传的资料是否符合规定:

mysql_10_snap_07

以下列「查询GNP最大的国家」需求来说,子查询传回的数字是「country」表格中「GNP」栏位的最大值,这个数字就给外层查询当作「WHERE」子句中的条件设定:

mysql_10_snap_08

使用在比较运算子的子查询,在「SELECT」子句中不可以指定超过一个栏位的回传资料:

mysql_10_snap_09

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

mysql_10_snap_10

2.2 「IN」运算子

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

mysql_10_snap_11

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

mysql_10_snap_12

这类的需求,也可以改成使用子查询来完成:

mysql_10_snap_13

以上列的范例来说,如果你用错运算子:

mysql_10_snap_55

「IN」运算子可以视需要搭配「NOT」运算子:

mysql_10_snap_56

2.3 其它运算子

比较运算子与子查询搭配使用时,另外还提供「ALL」、「ANY」与「SOME」三个运算子,其中「ANY」和「SOME」运算子的效果是一样的,所以只需要讨论「 ALL」与「ANY」这两个运算子:

mysql_10_snap_14

比较运算子与「ALL」与「ANY」搭配使用时,可以完成比较特殊的查询需求,下列是两个用来测试的表格:

mysql_10_snap_15

下列是比较运算子与「ALL」搭配使用的范例:

mysql_10_snap_16

「ALL」运算子从字面上来看,是「全部」的意思,所以你也可以这样来看「ALL」运算子:

mysql_10_snap_17

「ANY」运算子从字面上来看,是「任何一个」的意思,所以你也可以这样来看「ANY」运算子:

mysql_10_snap_18

注:在MySQL中,「ANY」与「SOME」运算子的效果是一样的。

在你了解「ALL」运算子的效果以后,如果在解决你的需求时,使用了「 ALL」这样的运算子,它的效果其实跟「NOT IN」是一样的:

mysql_10_snap_20

另外「= ANY」运算子的效果跟「IN」是一样的:

mysql_10_snap_19

2.4 多栏位子查询

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

mysql_10_snap_21

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

mysql_10_snap_22

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

mysql_10_snap_23

遇到类似这样的需求时,你也可以套用这种比较简单的设定方式:

mysql_10_snap_24

如果想要查询「每一洲GNP最高的国家」,你可以使用下列的叙述先查询每一洲最高的GNP:

mysql_10_snap_25

跟单一资料的判断一样,子查询传回多比纪录时就要使用「IN」运算子:

mysql_10_snap_26

3 SELECT子句与子查询

如果需要的话,子查询也可以使用在「SELECT」子句中。 以查询「国家Japan的GNP」的需求来说,下列的范例使用的是你已经熟悉的查询叙述来完成这个需求:

mysql_10_snap_27

这类的需求也可以直接在「SELECT」子句中使用子查询传回你需要的资料:

mysql_10_snap_28

下列的叙述可以查询「India国家占全世界人口的比例」:

mysql_10_snap_29

4 FROM子句与子查询

子查询可以使用在「WHERE」与「HAVGIN」子句中用来设定条件,还有使用在「SELECT」子句中,用来传回需要的资料;除了这两种用法外,子查询还可以使用在「FROM」子句。 你通常会在在查询叙述的「FROM」子句中,指定需要的表格名称,有需要的话,你也可以使用子查询,这个子查询回传的结果会被当成一个「表格」:

mysql_10_snap_30

下列的范例可以查询「亚洲GNP前十名国家」:

mysql_10_snap_31

注:要完成上列的需求,并不需要在「FROM」子句中使用子查询,只要使用一般的查询叙述就可以了。

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

mysql_10_snap_32

注:要完成上列的需求,并不需要在「FROM」子句中使用子查询,使用结合查询也可以得到一样的结果。

5 资料维护与子查询

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

5.1 新增

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

mysql_10_snap_33

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

mysql_10_snap_34

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

mysql_10_snap_35

使用子查询提供「INSERT」叙述需要的资料,要特别注意子查询回传的栏位资料:

mysql_10_snap_36

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

MySQL另外一种新增纪录的「REPLACE」叙述,也可以使用子查询提供需要的资料:

mysql_10_snap_37

5.2 修改

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

mysql_10_snap_38

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

mysql_10_snap_39

MySQL在「UPDATE」叙述中的子查询有一个特别的规定:

mysql_10_snap_40

5.3 删除

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

mysql_10_snap_41

如果要执行「删除SALES部门员工」,因为你需要先知道「SALES」部门的编号,所以你可以使用子查询传回「SALES」部门的编号,给「DELETE」叙述中的「WHERE」子句设定部门编号的条件:

mysql_10_snap_42

MySQL在「DELETE」叙述中出现的子查询有一个特别的规定:

mysql_10_snap_43

6 关联子查询

在查询或维护的查询中,都有可能会使用子查询来提供执行叙述所需要的资料:

mysql_10_snap_44

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

mysql_10_snap_45

在「WHERE」或「HAVING」子句中用来设定条件的子查询,可以依照需求使用像「IN」、「ANY」这些运算子来判断条件是否符合。 除了上列以经讨论的比较运算子外,还有一个「EXISTS」运算子:

mysql_10_snap_51

「EXISTS」运算子判断条件是否成立的依据比较不一样,如果子查询有任何纪录资料回传,条件就算成立:

mysql_10_snap_52

「EXISTS」运算子通常会在使用关联子查询中:

mysql_10_snap_53

「EXISTS」与「NOT」一起使用时,就可以完成下列的查询需求:

mysql_10_snap_54

7 子查询与结合查询

子查询的应用通常可以简化许多工作,而一些子查询完成的工作,也可以改用其它的作法来完成。 例如下列查询「所有国家首都名称」的叙述:

mysql_10_snap_46

把上列的需求改用结合查询来完成的话,其实看起来会更简单一些:

mysql_10_snap_47

如果需求换成查询「不是首都的城市名称」,可以使用下列搭配子查询的作法:

mysql_10_snap_48

上列的需求要改成使用结合查询来完成的话,会比较不一样。 所以要先了解使用「LEFT JOIN」结合查询的效果:

mysql_10_snap_49

根据「LEFT JOIN」结合查询产生的效果,为这个结合查询设定适当的条件,就可以完成查询「不是首都的城市名称」:

mysql_10_snap_50

翻译自:  http://www.codedata.com.tw/database/mysql-tutorial-9-subquery/

更多实例:

WHERE clause

SELECT c.contact_id, c.last_name
FROM contacts c
WHERE c.site_name IN
   (SELECT a.site_name
    FROM address_book a
    WHERE a.address_book_id < 50);
SELECT c.contact_id, c.last_name
FROM contacts c
INNER JOIN address_book a
ON c.site_name = a.site_name
WHERE a.address_book_id < 50;

FROM clause

SELECT contacts.last_name, subquery1.total_size
FROM contacts,
 (SELECT site_name, SUM(file_size) AS total_size
  FROM pages
  GROUP BY site_name) subquery1
WHERE subquery1.site_name = contacts.site_name;

SELECT clause

SELECT p1.site_name,
  (SELECT MAX(file_size)
   FROM pages p2
   WHERE p1.site_id = p2.site_id) subquery2
FROM pages p1;

EXISTS – With SELECT Statement

SELECT *
FROM customers
WHERE EXISTS (SELECT *
              FROM order_details
              WHERE customers.customer_id = orders.customer_id);

EXISTS – With SELECT Statement using NOT EXISTS

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT *
                  FROM order_details
                  WHERE customers.customer_id = orders.customer_id);

EXISTS – With INSERT Statement

INSERT INTO contacts
(contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);

EXISTS – With UPDATE Statement

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT *
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

EXISTS -With DELETE Statement

DELETE FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);

 

更多参考:

MySQL 超新手入门教程系列

MySQL入门 (一) : 资料库概论与MySQL的安装

MySQL入门 (二) : SELECT 基础查询

MySQL入门 (三) : 运算式与函式

MySQL入门 (四) : JOIN 与UNION 查询

MySQL入门 (五) : CRUD 与资料维护

MySQL入门 (六) : 字元集与资料库

MySQL入门 (七) : 储存引擎与资料型态

MySQL入门 (八) : 表格与索引

MySQL入门 (十) : Views

 

本文: MySQL入门 (九) : 子查询 Subquery