第14章 使用子查询

    子查询
    利用子查询进行过滤
    作为计算字段使用子查询

  

一、子查询

  select语句是SQL的查询。迄今为止我们所看到的所有select语句都是简单查询,即从单个数据库表中检索数据的单条语句。

  SQL还允许创建子查询,即嵌套在其他查询中的查询。为什么要这么做呢?理解这个概念的最好方法是考察几个例子。

  

二、利用子查询进行过滤

  所有章节中使用的数据库表都是关系表(关于每个表及关系的描述)。订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单,orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户ID。实际的客户信息存储在customers表中。

  现在加入需要列出订购物品TNT2的所有客户,应该怎样检索?下面列出具体的步骤。

(1)检索包含物品TNT2的所有订单的编号。 (2)检索具有前一步骤列出的订单编号的所有客户ID。 (3)检索前一步骤返回的所有客户ID的客户信息。

  上述每个步骤都可以单独作为一个查询来执行。可以把一条select语句返回的结果用于另一条select语句的where子句。

  也可以使用子查询来把3个查询组合成一条语句。

  第一条select语句的含义很明确,对于prod_id为TNT2的所有订单物品,它检索其order_num列。输出列出两个包含此物品的订单:

    select order_num
    from orderitems
    where prod_id = 'TNT2';

输出:

    order_num
    20005
    20007

  下一步,查询具有订单2000520007的客户ID。利用IN子句,编写如下的select语句:

    select cust_id
    from orders
    where order_num in (20005, 20007);

输出:

    cust_id
    10001
    10004

  现在,把第一个查询(返回订单号的那一个)变为子查询组合两个查询。请看下面的select语句:

    select cust_id
    from orders
    where order_num in (select order_num
                        from orderitems
                        where prod_id = 'TNT2');

输出:

    cust_id
    10001
    10004

  在select语句中,子查询总是从内向外处理。在处理上面的select语句时,MySQL实际上执行了两个操作。

  首先,它执行下面的查询

    select order_num from orderitems where prod_id = 'TNT2';

  此查询返回两个订单号:2000520007。然后,这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的where子句。外部查询变为:

    select cust_id from orders where order_num in (20005, 20007);

  可以看到,输出是正确的并且与前面硬编码where子句所返回的值相同。

  现在得到了订购物品TNT2的所有客户的ID。下一步是检索这些客户ID的客户信息。检索两列的SQL语句为:

    select cust_name, cust_contact
    from customers
    where cust_id in (10001, 10004);

  可以把其中的where子句转换为子查询而不是硬编码这些客户ID:

    select cust_name, cust_contact
    from customers
    where cust_id in (select cust_id
                      from orders
                      where order_num in (select order_num
                                          from orderitems
                                          where prod_id = 'TNT2'));

  为了执行上述select语句,MySQL实际上必须执行3条select语句。最里边的子查询返回订单号列表,此列表用于其他外面的子查询的where子句。外面的子查询返回客户ID列表,此客户ID列表用于最外层查询的where子句。最外层查询确实返回所需的数据。

  可见,在where子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。

列必须匹配where子句中使用子查询(如这里所示),应该保证select语句具有与where子句中相同数目的列。通常,子查询将返回单个列并且与单个列匹配,但如果需要也可以使用多个列。

  虽然子查询一般与IN操作符结合使用,但也可以用于测试等于、不等于等。

子查询和性能 这里给出的代码有效并获得所需的结果。但是,使用子查询并不总是执行这种类型的数据检索的最有效的方法。更多的论述,请参阅第15章,其中将再次给出这个例子。

  

三、作为计算字段使用子查询

  使用子查询的另一方法是创建计算字段。加入需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

  为执行这个操作,遵循下面的步骤。

  (1)从customers表中检索客户列表。   (2)对于检索出的每个客户,统计其在orders表中的订单数目。

  正如前两章所述,可使用select count(*)对表中的行进行计数,并且通过提供一条where子句来过滤某个特定的客户ID,可仅对该客户的订单进行计数。

    select count(*) as orders
    from orders
    where cust_id = 10001;

  为了对每个客户执行count(*)计算,应该将count(*)作为一个子查询。

    select cust_name,
           cust_state,
           (select count(*)
            from orders
            where orders.cust_id = customers.cust_id) as orders
    from customers
    order by cust_name;

  这条select语句对customers表中每个客户返回3列:cust_namecust_stateordersorders是一个计算字段,它由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。

  子查询中的where子句与前面使用的where子句稍有不同,因为它使用了完全限定列名。下面的语句告诉SQL比较orders表中的cust_id与当前customers表中检索的cust_id

    where orders.cust_id = customers.cust_id

相关子查询: 涉及外部查询的子查询。

  这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性,就必须使用这种语法(表名和列名由一个句点分隔)。

不止一种解决方案 正如本章前面所述,虽然这里给出的样例代码运行良好,但它并不是解决这种数据检索的最有效的方法。在后面的章节中我们还要遇到这个例子。

  

逐渐增加子查询来建立查询 用子查询测试和调试查询很有技巧性,特别是在这些语句的复杂性不断增加的情况下更是如此,用子查询建立(和测试)查询的最可靠的方法是逐渐进行,这与MySQL处理它们的方法非常相同。首先,建立和测试最内层的查询。然后,用硬编码数据建立和测试外层查询,并且仅在确认它正常后才嵌入子查询。这时,再次测试它。对于要增加的每个查询,重复这些步骤。这样做仅给构造查询增加了一点点时间,但节省了以后(找出查询为什么不正常)的大量时间,并且极大地提高了查询一开始就正常工作的可能性。

  

🔚