MySQL 从入门到实践：掌握用好万事不求人

# 找到供应商提供的商品平均价格大于 10 的供应商，并且按平均价格降序排列
select vend_id, avg(prod_price) avgPrice from products group by vend_id having avgPrice > 10 order by avgPrice desc;

9. 子查询

子查询(subquery)，嵌套在其他查询中的查询。

9.1 使用子查询进行过滤

当一个查询语句中又嵌套了另一个完整的 select 语句，则被嵌套的 select 语句称为子查询或内查询，外面的 select 语句称为主查询或外查询。

之前所有查询都是在同一张表中的，如果我们想获取的信息分散在两张甚至多张表呢，比如要从订单表 orders 中获取顾客 ID，然后用顾客 ID 去顾客表 custormers 找到对应顾客信息。

# 首先在 orderitems 表中找到产品 TNT2 对应的订单编号
select order_num from orderitems where prod_id = 'TNT2'

# 然后在 orders 表中找到订单编号对应的顾客 id
select cust_id from orders where order_num in (
  select order_num from orderitems where prod_id = 'TNT2');
  
# 然后去 customers 表中找到顾客 id 对应的顾客名字
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'));

这里实际上有三条语句，最里边的子查询返回订单号列表，此列表用于其外面的子查询的 where 子句。外面的子查询返回顾客 ID 列表，此顾客 ID 列表用于最外层查询的 where 子句。最外层查询最终返回所需的数据。

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

9.2 相关子查询

相关子查询(correlated subquery) 涉及外部查询的子查询。

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

# 首先找到用户 ID 对应的订单数量
select count(*) from orders where cust_id = 10003;

# 然后将其作为一个 select 子句，将用户 id 
select cust_name, cust_state, (
  select count(*) from orders where orders.cust_id = customers.cust_id) as order_count 
  from customers order by order_count desc;

注意到上面这个 where orders.cust_id = customers.cust_id，这种类型的子查询叫做相关子查询，任何时候只要列名可能有多义性，就必须使用完全限定语法（表名和列名由一个句点分隔)。

10. 联结表

如果要查的数据分散在多个表中，如何使用单条 select 语句查到数据呢，使用联结可以做到。

联结是一种机制，用来在一条 select 语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

维护引用完整性 ：在使用关系表时，仅在关系列中插入合法的数据非常重要。如果在 products 表中插入拥有没有在 vendors 表中出现的供应商 ID 的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。

为防止这种情况发生，可指示 MySQL 只允许在 products 表的供应商 ID 列中出现合法值（即出现在 vendors 表中的供应商）。 这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的。

10.1 创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

# 列出产品的供应商及其价格
select vend_name, prod_name, prod_price from vendors, products 
where vendors.vend_id = products.vend_id order by prod_price desc;

这里在 where 后面用完全限定列名方式指定 MySQL 匹配 vender 表的 vend_id 列和 products 表的 vend_id 字段。

当引用的列可能有歧义时，必须使用完全限定列名的方式，因为 MySQL 不知道你指的是哪个列。

在联结两个表时，实际上做的是将一个表的每一行与另一个表的每一行配对，所以 where 子句作为过滤条件，过滤出只包含指定联结条件的列 where vendors.vend_id = products.vend_id，没有 where 子句，将返回两个表的长度乘积个字段，这叫笛卡尔积（cartesian product），可以运行一下这句看看：

# 返回两个表长度乘积行
select vend_name, prod_name, prod_price from vendors, products;

所有联结应该总是使用联结条件，否则会得出笛卡尔积。

10.2 联结多个表

一条 select 语句也可以联结多个表，比如需要把某个订单的产品信息、订单信息、供应商信息都列出来，要找的产品信息分散在供应商、产品、订单信息三个表中。

# 将订单 20005 的产品信息、订单信息、供应商信息找出来
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;

这里使用 and 来连接多个联结条件，定义了 3 个表之间用什么作为关联。

注意：MySQL 在运行时关联多个表以处理联结可能是非常耗费资源的，不要联结不必要的表。联结的表越多，性能下降越厉害。

这里可以使用联结来实现 9.1 节的例子，之前是使用子查询来实现的，从订单表 orders 中获取顾客 ID，然后用顾客 ID 去顾客表 custormers 找到对应顾客信息。

# 使用联结来实现 9.1 的例子
select customers.cust_id, cust_name
from orders, customers, orderitems
where orders.order_num = orderitems.order_num
  and customers.cust_id = orders.cust_id
  and prod_id = 'TNT2';                                # 由于三个表中只有一个表有 prod_id，所以不需要限定表名

这里提一句，不仅仅列可以起别名，表也可以起，用法跟列的别名一样：

# 把前面这个句子起别名
select c.cust_id, cust_name
from orders o, customers c, orderitems oi
where o.order_num = oi.order_num
  and c.cust_id = o.cust_id
  and prod_id = 'TNT2';

这样不仅仅可以缩短 SQL 语句，也允许在单条 select 语句中多次使用相同的表，同时起的别名不仅可以用在 select 子句，也可以使用在 where、order by 子句以及语句的其他部分。

10.3 内部联结 inner join

目前为止所用的联结称为等值联结(equijoin)，它基于两个表之间的相等测试，也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。下面的 select 语句返回与前面例子完全相同的数据：

# 列出产品的供应商及其价格
select vend_name, prod_name, prod_price
from vendors
         inner join products
                    on vendors.vend_id = products.vend_id;

这里的联结条件使用 on 子句而不是 where，这两种语法都可以达到效果。尽管使用 where 子句定义联结的确比较简单，但使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

10.4 自联结

比如某个产品出现了质量问题，现在希望找出这个产品的供应商提供的所有产品信息。按照之前介绍的子查询，我们可以先找到对应产品的供应商，然后找到具有这个供应商 ID 的产品列表：

# 先找到产品 ID 为 TNT1 的供应商 ID，然后找到对应供应商 ID 提供的产品列表
select prod_id, prod_name, vend_id
from products
where vend_id in (
    select vend_id
    from products
    where prod_id = 'TNT1'
);

使用子查询确实可以实现，使用联结也可以做到，这就是自联结：

# 自联结
select p1.prod_id, p1.prod_name, p1.vend_id
from products p1,
     products p2
where p1.vend_id = p2.vend_id
  and p2.prod_id = 'TNT1';

自联结查询的两个表是同一个表，因此 products 表需要分别起别名，以作为区分，而且 select 子句中出现的列名也需要限定表明，因为两个表都出现了相同的字段。

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

10.5 自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

自然联结就是你只选择那些唯一的列，这一般是通过对表使用通配符，对所有其他表的列使用明确的子集来完成的。

# 自选择唯一的通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。
select v.*, p.prod_id
from vendors v,
     products p
where v.vend_id = p.vend_id;

10.6 外部链接 outer join

有些情况下，联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结。

比如：

• 对每个顾客下了多少订单进行计数，包括那些至今尚未下订单的顾客;
• 列出所有产品以及订购数量，包括没有人订购的产品；
• 计算平均销售规模，包括那些至今尚未下订单的顾客。

此时联结需要包含哪些没有关联行的那些行。

比如检索所有用户，及其所下的订单，没有订单的也要列举出来：

# 内部联结，查找用户对应的订单
select c.cust_id, o.order_num
from customers c
         inner join orders o
                    on c.cust_id = o.cust_id;
                    
# 左外部联结，将没有下单过的顾客行也列出来
select c.cust_id, o.order_num
from customers c
         left outer join orders o
                         on c.cust_id = o.cust_id;
                         
# 右外部联结，列出所有订单及其顾客，这样没下单过的顾客就不会被列举出来
select c.cust_id, o.order_num
from customers c
         right outer join orders o
                          on c.cust_id = o.cust_id;

在使用 outer join 语法时，必须使用 right 或 left 关键字指定包括其所有行的表。right 指出的是 outer join 右边的表，而 left 指出的是 outer join 左边的表。上面使用 left outer join 从 from 子句的左边表 custermers 中选择所有行。为了从右边的表中选择所有行，应该使用 right outer join。

左外部联结可通过颠倒 from 或 where 子句中表的顺序转换为右外部联结，具体用哪个看你方便。

10.7 使用带聚集函数的联结

比如想检索一个顾客下过的订单数量，即使没有也要写 0，此时使用分组和 count 聚集函数来统计数量：

# 找到每个顾客所下订单的数量，并降序排列
select c.cust_id, c.cust_name, count(o.order_num) count_orders
from customers c
         left outer join orders o on c.cust_id = o.cust_id
group by c.cust_id
order by count_orders desc;

因为即使顾客没有下单，也要在结果里，所以把顾客表放在左边，用左外部联结。

11. 组合查询

MySQL 允许执行多条select语句，并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。

有两种情况需要使用组合查询：

多数情况下，组合查询可以使用具有多个 where 子句条件的单条查询代替。具体场景可以尝试一下这两种方式，看看对特定的查询哪一种性能更好。

11.1 创建组合查询 union

当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用组合查询。在每条 select 语句之间放上关键字 union 即可。

# 比如需要列出商品价格小于等于 10 而且是供应商 ID 为 1005 或 1003 的产品信息

select prod_id, prod_name, prod_price, vend_id from products where prod_price