MySQL（5）

聚合函数

GROUP BY 的使用

需求：查询各个部门的平均工资，最高工资SELECT department_id,AVG(salary),SUM(salary)FROM employeesGROUP BY department_id;需求：查询各个job_id的平均工资SELECT job_id,AVG(salary)FROM employeesGROUP BY job_id;需求：查询各个department_id,job_id的平均工资SELECT department_id,job_id,AVG(salary)FROM employeesGROUP BY  department_id,job_id;GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面需求：查询各个部门的平均工资，按照平均工资升序排列SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_sal ASC;

HAVING的使用 (作用：用来过滤数据的)

#练习：查询各个部门中最高工资比10000高的部门信息

#错误的写法：SELECT department_id,MAX(salary)FROM employeesWHERE MAX(salary) > 10000GROUP BY department_id;

#要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。

#要求2：HAVING 必须声明在 GROUP BY 的后面。

SELECT department_id,MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary) > 10000;

#要求3：开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

练习：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息

#推荐，执行效率高

SELECT department_id,MAX(salary)FROM employeesWHERE department_id IN (10,20,30,40)GROUP BY department_idHAVING MAX(salary) > 10000;

结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。

当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。

WHERE 与 HAVING 的对比

1. 从适用范围上来讲，HAVING的适用范围更广。

2. 如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING

SELECT ....,....,....(存在聚合函数)FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 (LEFT / RIGHT)JOIN ... ON ....WHERE 不包含聚合函数的过滤条件GROUP BY ...,....HAVING 包含聚合函数的过滤条件ORDER BY ....,...(ASC / DESC )LIMIT ...,....

聚合函数的课后练习

1.where子句可否使用组函数进行过滤?

2.查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary) max_sal ,MIN(salary) mim_sal,AVG(salary) avg_sal,SUM(salary) sum_salFROM employees;

3.查询各job_id的员工工资的最大值，最小值，平均值，总和

SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)FROM employeesGROUP BY job_id;

4.选择具有各个job_id的员工人数

SELECT job_id,COUNT(*)FROM employeesGROUP BY job_id;

5.查询员工最高工资和最低工资的差距（DIFFERENCE） #DATEDIFF

SELECT MAX(salary) - MIN(salary) "DIFFERENCE"FROM employees;

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT manager_id,MIN(salary)FROM employeesWHERE  manager_id IS NOT NULLGROUP BY manager_idHAVING MIN(salary)>=6000;

7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

SELECT d.department_name,d.location_id,COUNT(employee_id),AVG(salary)FROM departments d LEFT JOIN employees eON d.`department_id` = e.`department_id`GROUP BY department_name,location_id

8.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT d.department_name,e.job_id,MIN(salary)FROM departments d LEFT JOIN employees eON d.`department_id` = e.`department_id`GROUP BY department_name,job_id