MySQL_Z8聚合函数

【题目】

#1.where子句可否使用组函数进行过滤?
#No，需要使用having替换where

#2.查询公司员工工资的最大值，最小值，平均值，总和
max(), min(), avg(), sum();

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
select job_id, max(job_id), min(job_id), avg(job_id), sum(job_id)
from employees
group by job_id;

#4.选择具有各个job_id的员工人数
select job_id, count(job_id) 
from employees
group by job_id;

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
select max(salary) - min(salary) "differences"
from employees;

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
select manager_id, min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) >= 6000; 
#havingl里放聚合函数，where做日常索引，提升效率

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
select e.department_name, d.location_id, count(e.employee_id) c, avg(e.salary) avg_salary
from employees e right join departments d
on e.department_id = d.department_id
group by e.department_name, d.location_id
order by avg_salary desc;
#【注意】此处count(e.employee_id) 如果使用count(*) 则会导致没有员工的部门显示员工数量是1，是错误的（其实1指代一个记录）

# 8.查询每个工种、每个部门的部门名、工种名和最低工资
select d.department_name, d.job_name, min(e.salary)
from departments d left join employees e
on d.department_id = e.department_id
group by d.department_name, d.job_name;