本文列举数据库的聚合函数及分组查询的实际使用，以员工表和部门表作为举例：
部门表：dept
点击查看创建部门表sql
create table `dept` (
`deptno` int(2) not null,
`dname` varchar(14),
`loc` varchar(13),
primary key (`deptno`)
);
员工表：emp
点击查看创建员工表sql
create table `emp` (
`empno` int(4) not null,
`ename` varchar(10),
`job` varchar(9),
`mgr` int(4),
`hiredate` date,
`sal` int(7),
`comm` int(7),
`deptno` int(2),
primary key (`empno`),
index `fk_deptno`(`deptno`),
constraint `fk_deptno` foreign key (`deptno`) references `dept` (`deptno`) on delete restrict on update restrict
);
聚合函数
min() max() avg() count() sum()
查询30号部门最少工资
select min(sal) from emp where deptno=30;
查询20号部门最高工资
select max(sal) from emp where deptno=20;
查询10号部门平均工资
select avg(sal) from emp where deptno=10;
统计有奖金的员工人数
select count(bonus) from emp;select count(*) from emp where bonus is not null;
统计10月份入职员工的薪资总和
select sum(sal) from emp where month(hiredate)=10;
分组查询
group by：分组查询
查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
having：分组之后进行检索
查询平均工资大于1000的部门及平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>1000;