MySQL高级查询(聚合函数与分组查询)

聚合函数：

　　聚合函数在数据的查询分析中，应用十分广泛。聚合函数可以对数据求和、求最大值和最小值、求平均值等等。

　　聚合函数不能出现在where子句中，这是一种语法错误，聚合函数执行需要一个数据范围，而在where子句执行时还未划出这个范围。

　　1.sum函数由于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加

　　2.max函数用于获得非空值的最大值。

　　3.min函数用于获得非空值的最小值。

　　4.avg函数用于获得非空值的平均值，非数字数据统计结果为0。

　　5.count(*)用于获得包含空值的记录数，count(列名)用于获得包含非空值的记录数。

#查询所有员工工资的平均值
select avg( sal+ifnull(comm,0) ) as avg from t_emp
#查询10,20部门员工的工资总和
select sum(sal) from t_emp where deptno in(10,20);
#查询10,20部门中，月收入最高的员工
select max(sal+ifnull(comm,0)) from t_emp where deptno in(10,20);
#查询员工名字最长的是几个字符
select max(length(ename)) from t_emp;
#查询员工表中所有记录的数量
select count(*) from t_emp;
#查询comm字段中非空的记录数量
select count(comm) from t_emp;
#查询10和20部门中，底薪超过2000元并且工龄超过15年的员工人数
select count(*) 
from t_emp where deptno in(10,20) 
and sal>=2000 
and datediff(now(),hiredate)/365>=15;

数据库表的分组查询：

　　默认情况下汇总函数是对全表范围内的数据做统计，group by子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理。

　　数据库支持多列分组条件，执行的时候逐级分组。

　　查询语句中如果含有group by子句，那么select子句中的内容就必须要遵守规定: select子句中可以包括聚合函数，或者group by子句的分组列，其余内容均不可以出现在select子句中。
　　 with rollup关键字：对汇总结果再次进行汇总计算。　　

　　group_concat函数可以把分组查询中的某个字段拼接成一个字符串

#查询每个部门的平均底薪, round四舍五入到整数位
select deptno,round(avg(sal))
from t_emp group by deptno;
#逐级分组，查询每个部门里，每种职位的人员数量和平均底薪
select deptno,job,count(*),avg(sal) as _avg
from t_emp group by deptno,job
order by _avg desc;
#对分组结果集再次做汇总计算,with rollup关键字：对汇总结果再次进行汇总计算
select deptno,count(*),avg(sal),sum(sal),max(sal),min(sal)
from t_emp 
group by deptno with rollup;
#查询每个部门内底薪超过2000元的人数和员工姓名
select deptno,group_concat(ename),count(*)
from t_emp where sal>=2000
group by deptno;

　　having子句功能和where子句一样，实现数据的筛选，having子句只能放在group by后面。

　　having子句的特殊用法：按照数字1分组，MySQL会依据select子句中的列进行分组，having子句也可以正常使用

#查询平均薪资超过2000的部门
select deptno
from t_emp
group by deptno having avg(sal)>=2000;
#查询每个部门中，1982年以后入职的员工超过2个人的部门编号
select deptno
from t_emp
where hiredate>="1982-01-01"
group by deptno having count(*)>=2;
#having子句的特殊用法
#1代表用select中第1个字段进行分组
select deptno,count(*) from t_emp
group by 1;
#可以用having替代where的内容，由于执行速度慢不建议这么写
select deptno,count(*) from t_emp
group by 1 having deptno in(10,20);