子查询与表联结
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 |
+---------+----------------+------------+