• Tags ,
  •         
  • www.breakyizhan.com
  •    

    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

     
    转载请保留页面地址:https://www.breakyizhan.com/sql/6081.html