MySQL 学习笔记 —— 11、使用子查询

子查询

SELECT 语句是 SQL 的查询，从单个数据库表中检索数据的单条语句是一种简单查询；

此外，SQL 还允许创建子查询（subquery），即嵌套在其他查询中的查询

任何 SQL 语句都是查询，但是此术语一般指 SELECT 语句

---

利用子查询进行过滤

案例使用的数据库都是关系表

订单存储在两个表中
每个订单包含订单编号、客户ID、订单日期，在 Orders 表中存储为一行

各订单的物品存储在相关的 OrderItems 表中

Orders 表中不存储顾客信息，只存储顾客ID，顾客的实际信息存储在 Customers 表中

如果需要列出订购某个物品 ANV01 的所有顾客，则需要：

1. 检索包含物品 ANV01 的所有订单的编号
2. 检索具有前一步骤列出的订单编号的所有顾客的ID
3. 检索前一步骤返回的所有顾客ID的顾客信息

以上三个步骤，每一个步骤都可以单独作为一个查询来执行，则可以把一条 SELECT 语句返回的结果用于另一条 SELECT 语句的 WHERE 子句；
也可以使用子查询把 3个查询组合成一条语句

第一条 SELECT 语句含义很明确，对 prod_id 为 ANV01 的所有订单物品，检索其 order_num 列

SELECT order_num FROM OrderItems
WHERE prod_id = 'ANV01';

现在，我们知道了哪个订单包含要检索的物品，下一步查询与订单 20005 相关的顾客 ID
如果，顾客ID 比较多，可以在 WHERE 子句中使用 IN 子句，这里只有一个 ID ，所以不使用了

SELECT cust_id FROM Orders
WHERE order_num = 20005;

现在，结合这两个查询，把第一个查询（查询订单号）变为子查询

SELECT cust_id FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'ANV01');

在 SELECT 语句中，子查询总是从内向外处理

在处理上面的 SELECT 语句时，DBMS 实际上执行了两个操作
首先，它执行下面的查询

SELECT order_num FROM OrderItems
WHERE prod_id = 'ANV01';

此查询返回一个订单号，并传递给外部查询的 WHERE 子句
外部查询变成

SELECT cust_id FROM Orders
WHERE order_num IN (20005);

现在得到了订购物品 ANV01 的所有顾客的ID，下一步是检索这些顾客ID 的顾客信息

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

其中的 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 = 'ANV01'));

为了执行上述 SELECT 语句，DBMS 实际上必须执行三条 SELECT 语句

1. 最里面的子查询返回订单号列表，此列表用于其外面的子查询的 WHERE 子句
2. 外面的子查询返回顾客ID列表，此列表用于最外层查询的 WHERE 子句
3. 最外层查询返回所需的数据

在 WHERE 子句中使用子查询，应该保证 SELECT 语句具有与 WHERE 子句中相同数目的列
通常，子查询将返回单个列并且与单个列匹配，但是如果需要也可以使用多个列

子查询并不总是执行这类数据检索的最有效的方法，尽量不要嵌套太多

---

作为计算字段使用子查询

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

假如需要显示 Customers 表中每个顾客的订单总数，订单与相应的顾客ID存储在 Orders 表中

执行这个操作，需要以下步骤：

1. 从 Customers 表中检索顾客列表
2. 对于检索出的每个顾客，统计其在 Orders 表中的订单数目

可以使用 SELECT COUNT(*) 对表中的行进行计数，并且通过提供一条 WHERE 子句来过滤某个特定的顾客ID ，仅对该顾客的订单数进行计数，如下代码所示：

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

如果要对每个顾客执行 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 表中每个顾客返回三列：cust_name、cust_state 和 orders
orders 是一个计算字段，它是由圆括号中的子查询建立的
该子查询对检索出的每个顾客执行一次

子查询中的 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

相关子查询（correlated subquery）:
涉及外部查询的子查询

用一个句点分隔表名和列名，在有可能混淆列名时必须使用这种语法

在这个例子中，有两个 cust_id 列：一个在 Customers 中，另一个在 Orders 中，如果不采用完全限定列名，DBMS 会认为要对 Orders 表中的 cust_id 自身进行比较

SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id

总是返回 Orders 表中订单的总数，并不是我们期望的结果

子查询在构造这种 SELECT 语句时极为有用，但是必须注意限制有歧义的列
子查询常用于 WHERE 子句的 IN 操作符，以及用来填充计算列

用子查询建立（和测试）查询的最可靠的方法是逐渐进行，
这与MySQL处理它们的方法非常相同

1. 首先，建立和测试最内层的查询。
2. 然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。
3. 这时，再次测试它。
4. 对于要\增加的每个查询，重复这些步骤。
5. 这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性