第三课（MySQL基础知识）

2016年11月13学习内容：

分组语法

GROUP BY用于进行分组，HAVING用于对分组进行过滤

例如：查询不同供货商提供的产品个数

SELECT vend_id,count(*) FROM products GROUP BY vend_id;

• GROUP BY子句可以包含任意数目的列；
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）；
• SELECT语句中的每个列只能是GROUP BY子句列出的或者通过聚集函数计算的； 如果分组列中具有NULL值，则NULL将作为一个分组返回；
• HAVING用来对分组进行过滤，语法和where类似，唯一的区别是WHERE用来过滤行，HAVING用来过滤分组；

例如：查询不同供货商提供的产品个数,只显示产品个数大于3的，并且按产品个数进行从高到低进行排序。

SELECT vend_id，count(*) as sum  FROM products GROUP BY vend_id HAVING count(*)>3 ORDER BY sum;

注意：as只能用在SELECT中，不能用在GROUP BY中。

SELECT语句中子句的顺序如下：

SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

子查询

现有3张表，客户表包含客户ID、客户名称等信息，订单表包含订单ID、订单时间、客户ID，订单详情表包含订单ID、商品ID、商品价格、商品数量等信息。
要求：查询所有买过商品ID为“TNT2”的客户姓名，查询语句如下：

select c.cust_name 
from customers c 
inner join orders o on c.cust_id = o.cust_id 
and o.order_num in (select order_num from orderitems where prod_id = 'TNT2');

使用自然连接优化之后：

select c.cust_name 
from customers c 
inner join orders o on c.cust_id = o.cust_id 
inner join orderitems oi  on o.order_num = oi.order_num and oi.prod_id = 'TNT2';

子查询也可以作为计算字段来使用。
例如：如果想查询每个客户的订单数，需要显示客户名称，订单个数：

step1:select count(*) from orders o where o.cust_id = '10000';
step2：select c.cust_name, (select count(*) from orders o where o.cust_id = c.cust_id) as orders
from customers c
order by cust_name;

相关子查询（correlated subquery）：涉及外部查询的子查询。
如果使用分组，也可以写成下面这样：

select c.cust_name, t.num
from customers c 
inner join (select o.cust_id as cust_id, count(*) as num
from orders o
group by o.cust_id) as t
where c.cust_id = t.cust_id;

写多级查询的方法：

首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在认它正常后才嵌入子查询。错误容易定位，好调试。
子查询最常见的使用是在WHERE子句的IN操作符中，以及用来填充计算列。

连接表

现有3张表，供货商表包含供货商ID、供货商名称等信息，商品表包含商品ID、商品名称、商品价格、供货商ID。
要求：查询所有产品的产品名称、产品价格以及供货商名称。

select v.vend_name,p.prod_name,p.prod_price
from vendors v,products p
where v.vend_id = p.vend_id;

或者如下：

select v.vend_name,p.prod_name,p.prod_price
from vendors v
inner join products p
on v.vend_id = p.vend_id;

笛卡尔集：
如果上面的语句不写where子句，相当于把表vendors的每行和表products的每行都进行配对显示，总行数为m*n。

自联结

查询产品ID为’DTNTR’的供货商的所有产品

select products.prod_id, products.prod_name 
from products 
where vend_id = (select vend_id from products where prod_id = 'DTNTR');

或

select p1.prod_id,p1.prod_name
from products as p1
inner join products as p2
on p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

自连接一般情况下会比子查询效率更高。

自然连接

使用where或者inner join连接两张表时，如果直接用select *,则则查询结果中至少有两个相同列，自然连接是指没有相同列的查询结果，由我们自己去完成。
例如：

select v.*,p.prod_name,p.prod_price
from vendors v
inner join products p
on v.vend_id = p.vend_id;

外连接

例如：统计每个客户下了多少次订单，包括那些没有下过订单的客户。

select c.cust_id,count(order_num)
from customers c 
left outer join orders o
on c.cust_id=o.cust_id
group by c.cust_id;

注意：order_num不能用* 替代，不然会出错，因为count(*)会把null也当作一行。

组合查询

有两种基本情况，其中需要使用组合查询：
1. 在单个查询中从不同的表返回类似结构的数据；
2. 对单个表执行多个查询，按单个查询返回数据。
多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出，在以下段落中可以看到这一点。这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。

假如：统计需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）

select * 
from products 
where prod_price > 5 
union 
select * 
from products 
where vend_id = '1001' or vend_id= '1002';

或者

select * 
from products 
where prod_price > 5 or vend_id = '1001' or vend_id= '1002';

在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔;
• UNION中的每个查询必须包含相同的列、表达式或聚集函数;
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型

注意：union会自动把两条语句相同行过滤，如果不想过滤，则使用union all。

对union后的语句进行排序(order by只能写在最后一条语句后面，对合并后的语句进行排序)如下：

select * 
from products 
where prod_price > 5 
union 
select * 
from products 
where vend_id = '1001' or vend_id= '1002';
order by vend_id;