MySQL-表联结查询

子查询与表联结

SQL最强大的功能之一就是能在数据检索查询的执行中**联结（join）**表。

##子查询（嵌套SQL）

SELECT语句是SQL的查询。迄今为止我们所看到的所有SELECT语句句都是简单查询，即从单个数据库表中检索数据的单条语句。SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

###利用子查询进行过滤

订单存储在两个表中。对于包含订单号、客户ID、订单⽇日期的每个订单，orders表存储一行。 各订单的物品存储在相关的orderitems表中。orders表不不存储客户信息。它只存储客户的ID。
实际的客户信息存储在customers表中。
现在，假如需要列列出订购物品TNT2的所有客户，应该怎样检索？

--（1） 查询包含物品TNT2的所有订单编号
select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+

-- (2) 查询对应订单编号的用户ID
select cust_id from orders where order_num in(20005,20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+

-- (3) 查询购买对应物品的用户信息
select cust_id,cust_name from customers where cust_id in(10001,10004);

+---------+----------------+
| cust_id | cust_name      |
+---------+----------------+
|   10001 | Coyote Inc.    |
|   10004 | Yosemite Place |
+---------+----------------+

可以把其中的WHERE子句转换为子查询而不是硬编码这些SQL返回的数据：

-- 转换为嵌套SQL，子查询
SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (
    SELECT cust_id
    FROM orders
    WHERE order_num IN (
        SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' )
    );
    
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
-- 为了执行上述SELECT语句句，MySQL实际上必须执行3条SELECT语句。
-- 最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。
-- 外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。
-- 最外层查询确实返回所需的数据。

什么是嵌套查询，子查询：
就是在一个sql当中，它的where条件来源于另外一个sql，
或者反过来理解，一个sql语句的结果，作为外层sql语句的条件。

这里给出的代码有效并获得所需的结果。
但是，使用子查询并不总是执行这种类型的数据检索的最有效的方法。

###作为计算字段使用子查询

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

-- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
-- (1) 从customers表中检索客户列列表。
select cust_id,cust_name from customers;
+---------+----------------+
| cust_id | cust_name      |
+---------+----------------+
|   10001 | Coyote Inc.    |
|   10002 | Mouse House    |
|   10003 | Wascals        |
|   10004 | Yosemite Place |
|   10005 | E Fudd         |
+---------+----------------+

-- 2.先获取一个用户在orders表中的订单数
select count(*) as orders_num from orders where cust_id = 10001;
+------------+
| orders_num |
+------------+
|          2 |
+------------+

为了对每个客户进行count()计算，应该将count()作为一个子查询：

-- 把count()作为一个子查询
select cust_id,cust_name,
    (select count(*) from orders where orders.cust_id = customers.cust_id) as orders_num
from customers;

+---------+----------------+------------+
| cust_id | cust_name      | orders_num |
+---------+----------------+------------+