[TOC]

第十四章子查询

在我们的数据库中，有三个表：

orders表存储订单编号，订单日期，顾客编号；

+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
|     20009 | 2005-10-08 00:00:00 |   10001 |
+-----------+---------------------+---------+

customers表存储顾客信息，包括姓名，地址，联系方式等；

+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       | NULL                |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+

orderitems表存储订单编号，订单数量，产品编号，价格等。

+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | ANV01   |       10 |       5.99 |
|     20005 |          2 | ANV02   |        3 |       9.99 |
|     20005 |          3 | TNT2    |        5 |      10.00 |
|     20005 |          4 | FB      |        1 |      10.00 |
|     20006 |          1 | JP2000  |        1 |      55.00 |
|     20007 |          1 | TNT2    |      100 |      10.00 |
|     20008 |          1 | FC      |       50 |       2.50 |
|     20009 |          1 | FB      |        1 |      10.00 |
|     20009 |          2 | OL1     |        1 |       8.99 |
|     20009 |          3 | SLING   |        1 |       4.49 |
|     20009 |          4 | ANV03   |        1 |      14.99 |
+-----------+------------+---------+----------+------------+

14.1 子查询

现在我们想要知道所有订购了TNT2的所有用户的基本信息，应该怎么办呢？

基本的流程如下：

TNT2产品是来自orderitems表，我们可以找到订单编号order_num；
从订单编号order_num可以找到顾客编号cust_id;
从顾客编号cust_id可以查到估计的姓名地址信息。

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';

+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+

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

+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+

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

+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

-- 经过上述步骤，我们最终找到了购买力TNT2产品的顾客信息，但是你也能发现每一步操作的输出都作为下一步的输入
-- 每次都需要我们根据输出值人为地写查询，非常不方便
-- 所以能不能直接将上一步的输出作为下一步的输入呢，这就是子查询

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

-- 也能得到相同的结果，但是阅读就是那么容易了，性能往往也不好，有点类似于递归之类的东西

14.2 计算字段使用子查询

下面看如下的例子，嵌套了一个子查询，但是这个子查询是一个计算字段。

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 |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+

我们先看最外层，从customers表中，对每一位顾客，获取姓名，所在州以及一个计算字段。

这个计算字段是什么呢，是在orders表中的一个聚集函数，统计每位顾客cust_id 的订单总数，取别名为orders.

这里为什么采用了完全限定名呢，是因为orders,customers里都有名为cust_id的列，为了避免歧义和错误，使用完全限定名。

第十五章联结表

我们先来看两个表：第一个表存储的是供应商vendors信息，第二个表存储的是产品products目录。

+---------+----------------+-----------------+-------------+------------+----------+--------------+
| vend_id | vend_name      | vend_address    | vend_city   | vend_state | vend_zip | vend_country |
+---------+----------------+-----------------+-------------+------------+----------+--------------+
|    1001 | Anvils R Us    | 123 Main Street | Southfield  | MI         | 48075    | USA          |
|    1002 | LT Supplies    | 500 Park Street | Anytown     | OH         | 44333    | USA          |
|    1003 | ACME           | 555 High Street | Los Angeles | CA         | 90046    | USA          |
|    1004 | Furball Inc.   | 1000 5th Avenue | New York    | NY         | 11111    | USA          |
|    1005 | Jet Set        | 42 Galaxy Road  | London      | NULL       | N16 6PS  | England      |
|    1006 | Jouets Et Ours | 1 Rue Amusement | Paris       | NULL       | 45678    | France       |
+---------+----------------+-----------------+-------------+------------+----------+--------------+

+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+

我们来想一想，为什么需要将供应商信息和产品信息分开存储成两个表呢？

假设在一个表中，那么每一个供应商的产品都会有一条记录供应商地址，联系方式之列的条目，那么就会出现很多的重复；
重复多了，出错的概率就会很大了，也会出现不一致的情况；
需要改动的时候，比如供应商搬家了，只需要改一个地方就行了。

所以关系数据库涉及的一个基础就是不允许相同的数据出现多次，那么在把信息分解成多个表之后，就必须靠某种关系(relation)将表进行联结（join）。

15.1 创建联结

products只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。vendors表的主键又叫做products的外键，它是两个表的联结。

外键（foreign key）：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的联系。

可伸缩性（scale)：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）.

虽然分解数据能够有效地存储，更方便处理数据并且具有很好的伸缩性，但是数据存储在多个表中，无疑给检索带来了困难，所以我们需要用联结来解决多个表的信息检索问题。

SELECT vend_name,prod_name,prod_price 				-- 来自两个表的列
FROM vendors,products													-- 要联结的两个表
WHERE vendors.vend_id = products.vend_id 			-- 利用主键与外键进行联结
ORDER BY vend_name,prod_name;

+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+

15.1.1 背后的逻辑

SELECT vend_name,prod_name,prod_price
FROM vendors,products
ORDER BY vend_name,prod_name;

-- 总共有84=6*14行输出
-- 6和14分别是vendors,products表的行数
-- 这说明所谓表的联结不是物理上的联结，而是在查询中，将一个表的每行与另一个表的每行进行合并
-- 类似于笛卡尔积

所以上文中WHERE vendors.vend_id = products.vend_id实际上就是对笛卡尔积的结果进行过滤。

15.1.2 创建内部联结

前面的联结是基于相等测试，所以称为等值联结（equijoin），或者内部联结。

1
2
3

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

与15.1的检索语句结果只是顺序不同而已。

15.2 联结多个表

SELECT prod_name,vend_name,prod_price,quantity
FROM orderitems,products,vendors
WHERE products.vend_id=vendors.vend_id
	AND orderitems.prod_id = products.prod_id
  AND order_num = 20005;
  
+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+

联结表相当于笛卡尔积，就像是找到了所有的联结方式，这些联结里很多是错误的，于是必须要用过滤条件，不然性能下降很厉害。

现在回顾下第十四章中返回订购产品TNT2的客户列表，我们现在有了如下的解决方案：

SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
	AND orders.order_num = orderitems.order_num
	AND prod_id = 'TNT2';

第十六章创建高级联结

除了内部联结之外，我么还有其他3种常见的联结方式，分别为自连接，自然联结和外部联结。

16.1 使用表别名

在对不同的表进行联结时，给表名起别名可以有如下作用：

别名往往更简单，所以每次不需要写表的全频，而只是别名，这样就会缩短SQL语句；
MySQL允许在单条SELECT语句中多次使用相同的表，比如表与自身进行联结（就会出现至少两个相同的表名），此时可以取别名p1,p2进行区分。

下面给出别名的例子，

SELECT cust_name,cust_contact
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'TNT2';
	
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

-- 这里联结了三个表，分别取别名为c,o,oi,可想而知，当相同名字的表进行联结时，会造成误解，到底是哪个表的列。

16.2 不同类型的联结

下面我们介绍三种类型的联结。

16.2.1 自联结

自联结就是表与自己进行联结，此时别名很重要。现在假设产品DTNTR出现了质量问题，我们想看看这个产品的供应商的其他产品是不是也有问题，所以我们需要找到产品DTNTR的供应商，然后找这个供应商所有的产品。

SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = "DTNTR");
                 
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

上面是通过子查询得到呢，那么如果要通过联结呢？（子查询和联结都可以完成任务，关键是性能上的差异）

SELECT p1.prod_id,p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
	AND p2.prod_id = "DTNTR";
	
-- 设想有两个相同的表，左边的叫p1,右边的叫p2
-- 满足p2.prod_id = "DTNTR"的p2行与p1所有行进行笛卡尔积
-- 去除掉不满足vend_id相同的
-- 剩下的就是我们要的部分
	
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

16.2.2 自然联结

所谓自然联结，就是将不同列取出来，把重复的列扔掉。那么可以取一个表的所有列，其他表不取之前表中取过的列。

SELECT c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = "FB";
	
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 | FB      |        1 |      10.00 |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 | FB      |        1 |      10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+

16.2.3 外部联结

我们通过比较一个内部联结和外部联结的例子进行说明。

SELECT customers.cust_id,orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

-- 两个表作笛卡尔积，不满足cust_id相同的被扔掉

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

-- orders表被加在customers表上，cust_id为10002的顾客没有订单信息，我们依然会将它算在联结中
-- 故外联结包含了那些在相关表中没有关联的行

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

下面是两个表的信息，我们看结果是怎么来的？

mysql> select * from orders;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
|     20009 | 2005-10-08 00:00:00 |   10001 |
+-----------+---------------------+---------+
5 rows in set (0.00 sec)

mysql> select * from customers;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       | NULL                |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+

16.3 使用带聚集函数的联结

SELECT customers.cust_name,customers.cust_id
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

+----------------+---------+
| cust_name      | cust_id |
+----------------+---------+
| Coyote Inc.    |   10001 |
| Coyote Inc.    |   10001 |
| Wascals        |   10003 |
| Yosemite Place |   10004 |
| E Fudd         |   10005 |
+----------------+---------+


SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

第十七章组合查询

所谓组合查询，就是将多条select语句的结果合并成一个，使用Union命令，和集合的并集是一样的，不包含重复元素，如果想要重复元素，可以使用Union All命令。

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5;

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+

SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5 OR vend_id IN (1001,1002); -- 用where子句可以将上面两个语句合并

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+

SELECT vend_id,prod_id,prod_price -- 用Union将上述查询结果合并
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+

SELECT vend_id,prod_id,prod_price -- 用Union将上述查询结果合并，但包括重复值
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+

Union的使用规则：

两条及以上的SELECT语句，用Union分隔；
每个查询必须用相同的列，表达式和聚集函数；
列数据类型必须兼容。

第十四章 子查询