day11-使用子查询-CSDN博客

本文链接：https://blog.csdn.net/qq_42911262/article/details/148910568

day11-使用子查询

子查询

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

查询(query) 
任何 SQL 语句都是查询。但此术语一般指 SELECT 语句。

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

利用子查询进行过滤

所有课中使用的数据库表都是关系表(关于每个表及关系的描述，请参阅附录 A)。订单存储在两个表中。每个订单包含订单编号、客户 ID、订单日期，在 Orders 表中存储为一行。各订单的物品存储在相关的OrderItems 表中。Orders 表不存储顾客信息，只存储顾客 ID。顾客的
实际信息存储在 Customers 表中。

现在，假如需要列出订购物品 RGAN01 的所有顾客，应该怎样检索？下面列出具体的步骤。 
(1) 检索包含物品 RGAN01 的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有顾客的 ID。
(3) 检索前一步骤返回的所有顾客 ID 的顾客信息。
上述每个步骤都可以单独作为一个查询来执行。可以把一条 SELECT 语句返回的结果用于另一条 SELECT 语句的 WHERE 子句。 

也可以使用子查询来把 3 个查询组合成一条语句。
第一条 SELECT 语句的含义很明确，它对 prod_id 为 RGAN01 的所有订单物品，检索其 order_num 列。输出列出了两个包含此物品的订单：



SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';


输出：
mysql> SELECT order_num
    -> FROM OrderItems
    -> WHERE prod_id = 'RGAN01';
+-----------+
| order_num |
+-----------+
|     20007 |
|     20008 |
+-----------+
2 rows in set (0.01 sec)

mysql> 
mysql> 






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


输出：
mysql> SELECT cust_id
    -> FROM Orders
    -> WHERE order_num IN (20007,20008);
+------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.01 sec)

mysql> 








SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
 FROM OrderItems
 WHERE prod_id = 'RGAN01');
 
 
 输出：
 mysql> 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)

mysql> 


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

首先，它执行下面的查询： 
SELECT order_num FROM orderitems WHERE prod_id='RGAN01'

此查询返回两个订单号：20007 和 20008。然后，这两个值以 IN 操作符

要求的逗号分隔的格式传递给外部查询的 WHERE 子句。外部查询变成：

SELECT cust_id FROM orders WHERE order_num IN (20007,20008)
可以看到，输出是正确的，与前面硬编码 WHERE 子句所返回的值相同。

 
 
 提示：格式化 SQL
包含子查询的 SELECT 语句难以阅读和调试，它们在较为复杂时更是如此。如上所示，把子查询分解为多行并进行适当的缩进，能极大地简化子查询的使用。
顺便一提，这就是颜色编码起作用的地方，好的 DBMS 客户端正是出于这个原因使用了颜色代码 SQL。

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


SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (1000000004,1000000005);


输出：
mysql> 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.01 sec)

mysql> 







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'));
 
 
 
 输出：
 
 mysql> 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.00 sec)

mysql>  


 


为了执行上述 SELECT 语句，DBMS 实际上必须执行三条 SELECT 语句。
最里边的子查询返回订单号列表，此列表用于其外面的子查询的 WHERE子句。外面的子查询返回顾客 ID 列表，此顾客 ID 列表用于最外层查询的 WHERE 子句。最外层查询返回所需的数据。
可见，在 WHERE 子句中使用子查询能够编写出功能很强且很灵活的 SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。



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

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

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示 Customers 表中每个顾客的订单总数。订单与相应的顾客 ID 存储在 Orders 表中。


执行这个操作，要遵循下面的步骤： 
(1) 从 Customers 表中检索顾客列表；
(2) 对于检索出的每个顾客，统计其在 Orders 表中的订单数目。
正如前两课所述，可以使用 SELECT COUNT(*)对表中的行进行计数，并且通过提供一条 WHERE 子句来过滤某个特定的顾客 ID，仅对该顾客的订单进行计数。例如，下面的代码对顾客 1000000001 的订单进行计数：



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


输出：
mysql> SELECT COUNT(*) AS orders 
    -> FROM Orders 
    -> WHERE cust_id = 1000000001;
+--------+
| orders |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

mysql> 
mysql> 






SELECT cust_name, 
 cust_state,
 (SELECT COUNT(*) 
 FROM Orders 
 WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers 
ORDER BY cust_name;




输出：
mysql> 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)

mysql> 







这条 SELECT 语句对 Customers 表中每个顾客返回三列：cust_name、
cust_state 和 orders。orders 是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个顾客执行一次。在此例中，该子查询执行了 5 次，因为检索出了 5 个顾客。
子查询中的 WHERE 子句与前面使用的 WHERE 子句稍有不同，因为它使用了完全限定列名，而不只是列名(cust_id)。它指定表名和列名(Orders.cust_id和 Customers.cust_id)。下面的 WHERE子句告诉 SQL，

比较Orders表中的cust_id和当前正从Customers表中检索的cust_id： 
WHERE Orders.cust_id = Customers.cust_id 


用一个句点分隔表名和列名，在有可能混淆列名时必须使用这种语法。
在这个例子中，有两个 cust_id 列：一个在 Customers 中，另一个在Orders 中。如果不采用完全限定列名，DBMS 会认为要对 Orders 表中的 cust_id 自身进行比较。因为
SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id 

总是返回 Orders 表中订单的总数，而这个结果不是我们想要的：




SELECT cust_name, 
 cust_state, 
 (SELECT COUNT(*) 
 FROM Orders 
 WHERE cust_id = cust_id) AS orders 
FROM Customers 
ORDER BY cust_name;



输出：

mysql> 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)

mysql> 





注意：完全限定列名 
你已经看到了为什么要使用完全限定列名，没有具体指定就会返回错误结果，因为 DBMS 会误解你的意思。有时候，由于出现冲突列名而导致的歧义性，会引起 DBMS 抛出错误信息。例如，WHERE 或 ORDER BY 子句指定的某个列名可能会出现在多个表中。好的做法是，如果在SELECT 语句中操作多个表，就应使用完全限定列名来避免歧义。


提示：不止一种解决方案 
正如这一课前面所述，虽然这里给出的样例代码运行良好，但它并不是解决这种数据检索的最有效方法。在后面两课学习 JOIN 时，我们还会遇到这个例子。

小结

这一课学习了什么是子查询，如何使用它们。子查询常用于 WHERE 子
句的 IN 操作符中，以及用来填充计算列。我们举了这两种操作类型的
例子。

小练习

1.使用子查询，返回购买价格为 10 美元或以上产品的顾客列表。你需要使用 OrderItems 表查找匹配的订单号(order_num)，然后使用Order 表检索这些匹配订单的顾客 ID(cust_id)。

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE item_price >= 10);
                    
                    
                    
     
     
     
 输出：
 mysql> SELECT cust_id
    -> FROM Orders
    -> WHERE order_num IN (SELECT order_num
    ->                     FROM OrderItems
    ->                     WHERE item_price >= 10);
+------------+
| cust_id    |
+------------+
| 1000000001 |
| 1000000003 |
| 1000000004 |
| 1000000005 |
+------------+
4 rows in set (0.00 sec)

mysql>

2.你想知道订购 BR01 产品的日期。编写 SQL 语句，使用子查询来确定哪些订单(在 OrderItems 中)购买了 prod_id 为 BR01 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID(cust_id)和订单日期(order_date)。按订购日期对结果进行排序。

SELECT cust_id, order_date
FROM Orders
WHERE order_num IN (SELECT order_num
                   FROM OrderItems
                    WHERE prod_id = 'BR01')
ORDER BY order_date;





输出：
mysql> SELECT cust_id, order_date
    -> FROM Orders
    -> WHERE order_num IN (SELECT order_num
    ->                    FROM OrderItems
    ->                     WHERE prod_id = 'BR01')
    -> ORDER BY order_date;
+------------+---------------------+
| cust_id    | order_date          |
+------------+---------------------+
| 1000000003 | 2020-01-12 00:00:00 |
| 1000000001 | 2020-05-01 00:00:00 |
+------------+---------------------+
2 rows in set (0.00 sec)

mysql>

3.现在我们让它更具挑战性。在上一个挑战题，返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件(Customers 表中的 cust_email)。提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

SELECT cust_email FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = 'BR01'));
                                      
                                      
                                      
                                      
 
 
 输出：
 
 mysql> SELECT cust_email FROM Customers
    -> WHERE cust_id IN (SELECT cust_id
    ->                   FROM Orders
    ->                   WHERE order_num IN (SELECT order_num
    ->                                       FROM OrderItems
    ->                                       WHERE prod_id = 'BR01'));
+-----------------------+
| cust_email            |
+-----------------------+
| sales@villagetoys.com |
| jjones@fun4all.com    |
+-----------------------+
2 rows in set (0.00 sec)

mysql>

4.我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。编写 SQL 语句，返回顾客 ID(Orders 表中的 cust_id)，并使用子查询返回 total_ordered 以便返回每个顾客的订单总数。将结果按金额从大到小排序。提示：你之前已经使用 SUM()计算订单总数。

SELECT cust_id,
       (SELECT SUM(quantity * item_price)
        FROM OrderItems
        WHERE order_num IN (SELECT order_num
                            FROM Orders
                            WHERE Orders.cust_id = Customers.cust_id)) AS total_ordered
FROM Customers
ORDER BY total_ordered DESC;




输出：
ysql>                                       SELECT cust_id,
    ->        (SELECT SUM(quantity * item_price)
    ->         FROM OrderItems
    ->         WHERE order_num IN (SELECT order_num
    ->                             FROM Orders
    ->                             WHERE Orders.cust_id = Customers.cust_id)) AS total_ordered
    -> FROM Customers
    -> ORDER BY total_ordered DESC;
+------------+---------------+
| cust_id    | total_ordered |
+------------+---------------+
| 1000000001 |       3515.50 |
| 1000000004 |       1696.00 |
| 1000000003 |        329.60 |
| 1000000005 |        189.60 |
| 1000000002 |          NULL |
+------------+---------------+
5 rows in set (0.00 sec)

mysql>

5.再来。编写 SQL 语句，从 Products 表中检索所有的产品名称(prod_name)，以及名为 quant_sold 的计算列，其中包含所售产品的总数、(在 OrderItems 表上使用子查询和 SUM(quantity)检索)。

SELECT prod_name,
       (SELECT Sum(quantity)
        FROM OrderItems
        WHERE Products.prod_id=OrderItems.prod_id) AS quant_sold
FROM Products;



输出：
mysql> SELECT prod_name,
    ->        (SELECT Sum(quantity)
    ->         FROM OrderItems
    ->         WHERE Products.prod_id=OrderItems.prod_id) AS quant_sold
    -> FROM Products;
+---------------------+------------+
| prod_name           | quant_sold |
+---------------------+------------+
| Fish bean bag toy   |        360 |
| Bird bean bag toy   |        360 |
| Rabbit bean bag toy |        360 |
| 8 inch teddy bear   |        120 |
| 12 inch teddy bear  |         10 |
| 18 inch teddy bear  |        165 |
| Raggedy Ann         |         55 |
| King doll           |       NULL |
| Queen doll          |       NULL |
+---------------------+------------+
9 rows in set (0.00 sec)

mysql>