一、分组查询GROUP BY
1.语法
SELECT (列名) as '自由命名' , COUNT(*) as '自由命名';from (表名);GROUP BY (列名);
2. * 号里可以为列名
语法:SELECT COUNT(列名) as '自由命名';from (表名);
如:
3.order by 排序
3_1. order by (默认升序)
语法:SELECT (列名1) as '自由命名', AVG(列名2) as '自由命名';from (表名);GROUP BY (列名1);order by AVG(列名2);
如:
3_2. order by desc(降序)
语法:SELECT (列名1) as '自由命名', AVG(列名2) as '自由命名';from (表名);GROUP BY (列名1);order by AVG(列名2) desc;
如:
注意: ORGER BY 放在GROUP BY 之后
4. 多列分组查询
语法:SELECT COUNT(*) AS '自由命名' , (列名1) as '自由命名' ,(列名2) as '自由命名' ;from (表名);GROUP BY (列名1),(列名2);order by (列名1);
如:
5. HAVING子句分组筛选
5_1.在SELECT语句中,WHERE 、GROUP BY、HAVING子句和聚合函数的执行次序
HAVING 和WHERE 子句可以在同一个SELECT语句中一起使用,顺序WHERE------GROUP BY------HAVING在SELECT语句中,WHERE 、GROUP BY、HAVING子句和聚合函数的执行次序如下:Where语句从数据源中去掉不和其搜索条件的数据;Group by子句搜集数据行到各个组中,统计函数为各个组计算统计值;Having子句去掉不符合其组搜索条件的各组数据行。
5_2.语法:
SELECT COUNT(*) AS '自由命名' , AVG(列名) as '自由命名';from (表名);where (规定范围(<,>,<=,>=));GROUP BY (列名);HAVING (AVG(列名) 后接范围(<,>,<=,>=));