MySQL----分组函数、分组查询
分组函数
上面的分组函数都忽略了null值
简单使用:
select SUM(salary) from employees;
select AVG(salary) from employees;
select MAX(salary) from employees;
select MIN(salary) from employees;
select COUNT(salary) from employees;
SELECT SUM(salary) 和,AVG(salary) 平均 from employees;
和distinct 搭配
Select sum(distinct salary),sum(salary) from employees;
select count(*) from employees;统计一共有多少行
还可以使用count(1),相当于增加了一列里面全是1
select count(1) from employees;
分组查询
引入:查询每个部门的平均工资
select AVG(salary),department_id
from employees
group by department_id
可以使用GROUP BY 子句将表中的数据分成若干组
案例1:查询每个工种的最高工资
select max(salary),job_id
from employees
group by job_id
案例2:查询每个位置上的部门个数
select count(*),location_id
from departments
group by location_id
案例3:查询有奖金的每个领导手下的员工的最高工资
select MAX(salary),manage_id
from employees
where comission_pct is not null
group by manage_id;
稍微复杂一点的案例
案例四:查询哪个部门的员工个数>2
分析步骤:① 先查询出每个部门的员工个数
select count(*),department_id
from employees
group by department_id;
② 再根据结果查询员工个数大于2的部门
这里我们用到了一个新的关键字:having
select count(*),department_id
from employees
group by department_id
having count(*) >2;
案例:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
①查询每个工种有奖金的员工的最高工资
select Max(salary), job_id
from employees
where commission_pct is not null
group by job_id
②根据①的结果继续筛选,最高工资>12000
select Max(salary), job_id
from employees
where commission_pct is not null
group by job_id
Having Max(salary) > 12000;
查询各job_id 的员工工资的最大值,最小值,平均值,总和,并按job_id升序
select MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
from employees
group by job_id
order by job_id ASC;