加载中...

(10) 子查询


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_41

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

mysql_10_snap_42

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


还没有评论.