MySql（11）聚合函数

Mysql专栏入口

链接：https://pan.baidu.com/s/1zAhDUNv-yuJiWmaFLvTk4w
提取码：ttst
提取码：ttst
专栏中有mysql的导入导出，里面有教如何导入

一、常用的聚合函数

AVG / SUM
SELECT AVG(salary),SUM(salary) FROM employees

MAX / MIN
SELECT MIN(salary),MAX(salary) FROM employees

COUNT
SELECT COUNT(employee_id) FROM employees

二、GROUP BY

查询各个部门的平均工资和最高工资

SELECT
	AVG( salary ),
	MAX( salary ),
	department_id 
FROM
	employees 
GROUP BY
	department_id

查询各个工种的平均工资和最高工资

SELECT
	AVG( salary ),
	MAX( salary ),
	job_id 
FROM
	employees 
GROUP BY
	job_id

查询各个部门的不同工种的平均工资和最高工资

SELECT
	AVG( salary ),
	MAX( salary ),
	job_id,
	department_id
FROM
	employees 
GROUP BY
	department_id,job_id

三、having

出现聚合函数则必须使用HAVING来替换WHERE

查询各个部门中最高工资比1w高的部门信息

SELECT
	department_id,
	MAX( salary ) 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	MAX( salary ) > 10000

查询id为10、20、30、40这4个部门中最高工资比1w高的部门信息

SELECT
	department_id,
	MAX( salary ) 
FROM
	employees 
WHERE department_id IN (10,20,30,40)
GROUP BY
	department_id 
HAVING
	MAX( salary ) > 10000

3.1 having总结

当过滤条件中有聚合函数时，此过滤条件必须声明在HAVING中
当过滤条件中没有聚合函数时，此过滤条件建议声明在WHERE中

sql 的执行流程

FROM ...,... -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT 

四、小练习

-- 1. 查询公司员工工资的最大值，最小值，平均值，总和
SELECT
	MAX( salary ),
	MIN( salary ),
	AVG( salary ),
	SUM( salary ) 
FROM
	employees
	
-- 2. 查询各job_id的员工工资的最大值,最小值,平均值,总和
SELECT
	MAX( salary ),
	MIN( salary ),
	AVG( salary ),
	SUM( salary ),
	job_id 
FROM
	employees 
GROUP BY
	job_id
	
-- 3. 查询各job_id的员工人数
SELECT
	job_id,
	COUNT( 1 ) 
FROM
	employees 
GROUP BY
	job_id
	
-- 4. 查询员工最高工资和最低工资的差距
SELECT
	MAX( salary )- MIN( salary ) 
FROM
	employees
	
-- 5. 查询各个管理者手下员工的最低工资,其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT
	manager_id,
	MIN( salary ) 
FROM
	employees 
WHERE
	manager_id IS NOT NULL 
GROUP BY
	manager_id
HAVING MIN(salary)>=6000

-- 6. 查询所有部门的名字,location_id员工数量和平均工资,并按平均工资降序
SELECT
	d.department_name,
	d.location_id,
	COUNT(employee_id),
	AVG( e.salary ) 
FROM
	employees e
	RIGHT JOIN departments d ON e.department_id = d.department_id 
GROUP BY
	d.department_id
ORDER BY
	AVG(e.salary) DESC
	
-- 7. 查询每个工种、每个部门的部门名、和最低工资
SELECT
	d.department_name,
	e.job_id,
	MIN( e.salary ) 
FROM
	employees e
	RIGHT JOIN departments d ON e.department_id = d.department_id
GROUP BY	d.department_name,job_id