聚合函数:
聚合函数在数据的查询分析中,应用十分广泛。聚合函数可以对数据求和、求最大值和最小值、求平均值等等。
聚合函数不能出现在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);