MySQL学习笔记二：聚合、分组与排序

聚合函数

如count、sum、avg、min、max等称为聚合函数。select子句、having子句以及order by子句中可以包含聚合函数。沿用笔记一的product表格：

select count(*) from product;-- 会得到包含null的所有数据行数，8条
select count(purchase_price) from product;-- 不包含该列的null，6条
select sum(sale_price), sum(purchase_price) from product;-- 同上，null已经不包括了
select avg(sale_price), avg(purchase_price) from product;-- 同上，null已经不包括了,分母是6
select max(regist_date), min(regist_date) from product;-- min和max原则上适用于任何数据类型的列

select count(distinct product_type) from product;
-- 计算去除重复值后的数据行数，distinct在括号内，结果为3（即三种商品类型）
select sum(sale_price), sum(distinct sale_price) from product;-- 销售单价为500的商品有两种

分组：group by

语句书写顺序：select …from …where …group by…；执行顺序为from→where→group by→select。

select product_type, count(*) from product group by product_type;
-- 按照商品类别分组并且计算每一类的商品数量
select purchase_price, count(*) from product group by purchase_price;
-- 聚合键包含null时也算一类

select purchase_price, count(*) from product where product_type='厨房用具' group by purchase_price;
-- 看每一种购买价格下的厨房用具商品数有多少

注意事项：

  （1）select的列一定要包含在group by的列（聚合键）之中，虽然MySQL不会报错；

  （2）聚合键最好不要用句子中定义的别名，虽然MySQL不会报错；

  （3）group by的显示结果是随机的；

  （4）where子句中不能使用聚合函数；

  （5）想删除重复行时用distinct，想汇总计算结果时用group by。

select product_type as pt, count(*) from product group by pt;
 -- 聚合键最好不要用句子中定义的别名，虽然MySQL不会报错
 -- where子句中不能使用聚合函数，比如：
select product_type, count(*) from product where count(*)=2 group by product_type;

为聚合结果指定条件：having

having子句和select一样，只能包含聚合函数、常数以及聚合键（包含聚合键时呈现结果与where一样，但最好写在where子句里）。

having指定组条件，where指定行条件。执行顺序为from→where→group by→having→select。

select product_type, count(*) from product group by product_type having count(*)=2;
-- 数据行数为2的商品种类（包含聚合函数和常数）

select product_type, avg(sale_price) from product group by product_type having
 avg(sale_price)>=2500;-- 销售单价大于等于2500的商品种类

select product_type, avg(sale_price) from product group by product_type having
product_type='衣服';-- 包含聚合键

对结果排序：order by

order by的列（排序键）可以包含非select子句中的列和聚合函数。

书写顺序：select …from …where …group by…having…order by…;执行顺序：from→where→group by→having→select→order by。

select product_id,product_name,sale_price,purchase_price 
from product order by sale_price DESC;
-- 默认为升序ASC

select product_id,product_name,sale_price,purchase_price 
from product order by sale_price desc,product_id;
-- sale price降序排列，相同时按照id升序排列，即优先使用左键

select product_id,product_name,sale_price,purchase_price from product order by purchase_price;
-- null汇集在开头or末尾，升序汇集在开头，降序在末尾

select product_id as id,product_name,sale_price as sp,
purchase_price from product order by sp,id;
-- order by字句可以使用列的别名,不要用列编号

select product_name,sale_price,purchase_price from product 
order by product_id;-- 按照商品id排序但不展示id
select product_type,count(*) from product group by product_type 
order by count(*) desc;
-- order by的排序键可以包含非select子句中的列和聚合函数

综合练习

对所有商品类别的购买和销售价格进行分组求和，找出卖价高于买价1.5倍的商品类别，并且按照卖价的升序排序：

select product_type,sum(sale_price),sum(purchase_price) from 
product group by product_type/*分组求和*/ having sum(sale_price)/sum
(purchase_price)>=1.5 order by sale_price;