MySQL-聚合函数

一.五大常用聚合函数

1. AVG / SUM

SELECT AVG(salary),SUM(salary)
FROM employees
# 适用于数值类型的变量

3. MAX / MIN

SELECT MAX(salary),MIN(salary),MAX(last_name),MIN(hire_date)
FROM employees
#可以是数值,字符串,时间类型变量

5. COUNT

#计算指定字段在查询结果中出现的个数
SELECT COUNT(*),COUNT(salary*2),COUNT(2)
FROM employees  #107 107 (与行数有关,计算无关) 107(一行数据代表2)
/**
查询一个表中有多少行
方式一: count(*)
方式二: count(1)
方式三: count(字段名)  //不包含空值的,不计算null
*/
SELECT COUNT(commission_pct)
FROM employees   #35

注意: 以上都不计算空值

#需求 : 查询工资的平均奖金率(有人没有奖金也要算)
#错误的
SELECT AVG(commission_pct)
FROM employees
#正确的   avg(ifnull(commission_pct,0))
SELECT SUM(commission_pct)/COUNT(*)
FROM employees

用count()，count(1)，count(列名)谁好呢?
1.使用MyISAM存储引擎 ,三者效率相同,都是O(1)
2.使用 InnoDB存储引擎, count() = count(1) > count (字段)

二. group by 使用

#需求:查询各部门的平均工资,最高工资
SELECT department_id,AVG(salary),MAX(salary)
FROM employees
GROUP BY department_id
#需求: 查询各job_id的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id
#多个列分组
#需求:查询各department_id,job_id的平均工资
#方式一
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id
#方式二:
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id
#错误,一个部门有多个job_id
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id
#结论:select 出现的非组函数字段必须声明在group中,反之不用
# group by 声明在from后,where后order by 前面,limit前面
# mySQl中 group by 中使用 with rollup
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY job_id WITH ROLLUP 
#计算了整个工作的平均工资(会右计算整体的平均,不能与order by 同时使用)

三. HAVING 使用(用来过滤数据)

#HAVING 
#练习: 查询各部门中最高工资比10000好的部门信息
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary > 10000)
#如果过滤条件中使用了聚合函数,必须使用having替换where
#having 必须在group by 后面
#开发中,使用having的前提是使用group by(去掉group by不报错)
#练习 : 查询部门ID为10,20,30,40这4各部门中最高工资比10000高的部门信息
#方式一: 推荐,方式一执行效率高
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary)> 10000
#方式二:
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)> 10000 AND  department_id IN (10,20,30,40)
#结论: 当过滤条件中有聚合函数时,则此过滤条件必须声明在having中
#没有聚合函数,声明在where,having中都可以,建议where中
/*
where与having对比
从适用范围:having更广
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接
后筛选。
*/

四.SQL底层执行原理

SELECT ...,...,(可能有聚合函数)
FROM ... JOIN ...
ON ...
JOIN  ... ON ...
WHERE ...过滤条件
GROUP BY
HAVING 
ORDER BY
LIMIT
#sql语句执行过程
FROM ... ->ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

聚合函数练习

#1.where子句可否使用组函数进行过滤?
不可以
 #2.查询公司员工工资的最大值，最小值，平均值，总和 
 SELECT last_name,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
 FROM employees
 #3.查询各job_id的员工工资的最大值，最小值，平均值，总和 
 SELECT job_id,MAX(salary),MIN(salary),SUM(salary)/COUNT(*),SUM(salary)
 FROM employees
 GROUP BY job_id
 #4.选择具有各个job_id的员工人数
 SELECT job_id,COUNT(*)
  FROM employees
  GROUP BY job_id
 # 5.查询员工最高工资和最低工资的差距（DIFFERENCE） 
 SELECT MAX(salary)-MIN(salary) "DIFFERENCE"
 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 
 # 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
 SELECT d.department_name,d.location_id,COUNT(employee_id),AVG(salary)
 FROM departments d LEFT JOIN employees e
 ON d.department_id = e.department_id
 GROUP BY d.department_name,d.location_id
 ORDER BY AVG(salary) DESC; 
 #注意是所有部门,还有员工没有部门
 SELECT department_name, location_id, COUNT(employee_id), AVG(salary) avg_sal 
 FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`
  GROUP BY department_name, location_id ORDER BY avg_sal DESC;
 # *代表一条记录, null 也算一条记录用employ-id
 # 8.查询每个工种、每个部门的部门名、工种名和最低工资
 SELECT department_name,job_id,MIN(salary) 
 FROM departments d LEFT JOIN employees e 
 ON e.`department_id` = d.`department_id` 
 GROUP BY department_name,job_id