SQL 使用子查询

一、子查询：

  • SELECT 语句是SQL的查询。目前所看到的所有SELECT语句都是简单查询，单个数据库表中检索数据的单条语句。
  • 查询(query)：
    • 任何SQL语句都是查询。但此术语一般指SELECT语句。
  • SQL还允许创建子查询，嵌套再其他查询中的查询。下面举几个栗子来理解一下：
    • 注意：MySQL支持

      • 如果使用MySQL，应该知道对子查询的支持从4.1版本引入的。MySQL的早期版本不支持子查询。

二、利用子查询进行过滤：

  • 首先我们使用的数据库表都是关系表：

    • 下面是关于每个表的描述：

      在这里插入图片描述

    • 通过该表，分析一下接下来要举得栗子：

      1.订单存储再两个表中。

      2.每个订单包含订单编号、客户ID、订单日期，再Orders表中存储为一行。

      3.各订单的物品存储再相关的OrderItems表中。

      4.Orders表不存储顾客信息，只存储顾客ID。

      5.顾客的实际信息存储再Customers表中。

    • 需求：

      • 需要列出订购物品RGAN001的所有顾客，应该怎样检索？

        1.检索物品RGAN001的所有订单的编号。

        SELECT order_num FROM orderitems WHERE prod_id = \'RGAN01\';

        +-----------+
| order_num |
+-----------+
|     20007 |
|     20008 |
+-----------+
2 rows in set (0.01 sec)

        2.检索具有前一步骤列出的订单编号的所有顾客的ID（20007 , 20008）。

        SELECT cust_id FROM orders WHERE order_num IN (20007, 20008);

        +------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.00 sec)

        3.检索前一步骤返回的所有顾客的ID顾客信息。

        SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (\'1000000004\', \'1000000005\');

        +---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set (0.00 sec)

    • 现在结合1；2这两个查询，把第一个查询(返回订单号的那一个)变为子查询：

      SELECT cust_id FROM orders
WHERE order_num 
IN (SELECT order_num FROM orderitems WHERE prod_id=\'RGAN01\');

      +------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.00 sec)

      • 首先上面的子查询语句，先执行：

        SELECT order_num FROM orderitems WHERE prod_id=\'RGAN01\';

        +-----------+
| order_num |
+-----------+
|     20007 |
|     20008 |
+-----------+
2 rows in set (0.00 sec)

      • 此查询返回两个订单号：20007和20008。外部查询就变成了：

         SELECT cust_id FROM orders WHERE order_num IN (20007, 20008);

        +------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.00 sec)

      • 可以看到，输出是正确的，与前面硬编码WHERE子句返回的值相同。

    • 现在结合1；2；3这三个查询，更改为子查询：

      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=\'RGAN01\'));

      +---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set (0.01 sec)

      • DBMS实际上必须执行三条SELECT语句。

        1.最里面的子查询返回订单号列表，此列表用于其外面的子查询WHERE子句。

        2.外面的子查询返回顾客ID列表，此顾客ID列表用于最外层查询的WHERE子句。

        3.最外层查询返回所需的数据。

    • 注意：对于能嵌套的子查询的数目没有限制，不过再实际使用时由于性能的限制，不能嵌套太多的子查询。

    • 注意：只能是单列

      • 作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

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

  • 使用子查询的另一方法是创建计算字段。

  • 需要：需要显示customers表中每个顾客的订单总数。订单与相应的顾客ID存储再orders表中。

    • 分析上面需要要遵循的步骤：

      1.从customers表中检索顾客列表；对于检索出的每个顾客，统计其再orders表中的订单数目。

      2.使用SELECT COUNT（*）对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的顾客ID，仅对该顾客的订单进行计数。

    • 例如：下面的代码对顾客1000000001的订单进行计数：

    • 首先看一下orders表中的数据：

      mysql> SELECT * FROM orders;
+-----------+---------------------+------------+
| order_num | order_date          | cust_id    |
+-----------+---------------------+------------+
|     20005 | 2012-05-01 00:00:00 | 1000000001 |
|     20006 | 2012-01-12 00:00:00 | 1000000003 |
|     20007 | 2012-01-30 00:00:00 | 1000000004 |
|     20008 | 2012-02-03 00:00:00 | 1000000005 |
|     20009 | 2012-02-08 00:00:00 | 1000000001 |
+-----------+---------------------+------------+
5 rows in set (0.00 sec)

    • 对顾客1000000001的订单进行计数：

      SELECT COUNT(*) AS orders FROM orders WHERE cust_id= \'1000000001\';

    • 要对每个顾客执行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;

      +---------------+------------+--------+
| cust_name     | cust_state | orders |
+---------------+------------+--------+
| Fun4All       | IN         |      1 |
| Fun4All       | AZ         |      1 |
| Kids Place    | OH         |      0 |
| The Toy Store | IL         |      1 |
| Village Toys  | MI         |      2 |
+---------------+------------+--------+
5 rows in set (0.00 sec)

    • 这条SELECT 语句对customers表中每个顾客返回三列：cust_name、cust_state和orders。

      • orders是一个计算字段，它是圆括号中的子查询建立的。

      • 子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名，而不只是l列名(cust_id)。

      • 它指定表名和列名(orders.cust_id 和 customers.cust_id)。下面的WHERE子句告诉SQL，比较Orders表中的cust_id和当前正从Customers表中检索的cust_id：

        WHERE Orders.cust_id = Customers.cust_id
        • 用一个句点分隔表名和列名，这种语法必须在有可能混淆列名时使用。

      • 如果不采用`完全限定列名，DBMS会认为要对Orders表中的cust_id自身进行比较。

        SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id；
        • 而这个结果并不是我们想要
        SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE cust_id = cust_id) AS orders
FROM Customers
ORDER BY cust_name;

        +---------------+------------+--------+
| cust_name     | cust_state | orders |
+---------------+------------+--------+
| Fun4All       | IN         |      5 |
| Fun4All       | AZ         |      5 |
| Kids Place    | OH         |      5 |
| The Toy Store | IL         |      5 |
| Village Toys  | MI         |      5 |
+---------------+------------+--------+
5 rows in set (0.00 sec)

      • 虽然子查询再构造这种SELECT语句时极有用，但必须注意限制有歧义的列。

  • 注意：完全限定列名

    • 已经看到为什么要用完全限定列名，如果没有具体指定就会返回错误结果，因为DBMS会误解你的意思。
    • 由于出现冲突列名而导致的歧义性，会引起DBMS抛出错误信息。
版权声明：本文为匿名原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：