8.1 聚合函数介绍
聚合函数作用域一组数据,并且对一组数据返回一个值。
函数 | 用法 |
---|---|
AVG() | 平均值 |
SUM() | 和 |
MAX() | 最大值 |
MIN() | 最小值 |
COUNT() | 计数 |
-
聚合函数传参需要是一个表中的字段名
-
聚合函数无法嵌套使用
# 例如这样是不行的 AVG(SUM(字段名)) # SUM(字段名)是一个数值,但是AVG需要传入字段名,所以无法嵌套使用
基本使用
# 只有数值型数据可以使用AVG()和SUM()
# 查看工作带有REP的员工的平均工资、总工资、最大工资、最小工资
SELECT AVG(salary),SUM(salary),MIN(salary),MAX(salary)
FROM employees
WHERE job_id LIKE "%REP%";
# 任何数据都可以用MIN()和MAX(),例如数值,字符串和日期
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
# COUNT()计数函数,作用于任何类型
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
8.2 GROUP BY
单列分组
GROUP BY的作用就是分组,可以将表中的数据分为若干组
# 先来看看分组后的表格是什么样的
SELECT *
FROM employees
GROUP BY department_id;
最终的结果却是只有单一的department_id,但是其实对应的数据是表中出现的对应department_id对应的第一条数据。
# 实际使用
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
/*
+---------------+--------------+
| department_id | AVG(salary) |
+---------------+--------------+
| NULL | 7000.000000 |
| 10 | 4400.000000 |
| 20 | 9500.000000 |
| 30 | 4150.000000 |
| 40 | 6500.000000 |
| 50 | 3475.555556 |
| 60 | 5760.000000 |
| 70 | 10000.000000 |
| 80 | 8955.882353 |
| 90 | 19333.333333 |
| 100 | 8600.000000 |
| 110 | 10150.000000 |
+---------------+--------------+
12 rows in set (0.04 sec)
*/
分类的字段不用包含在SELECT中
多列分组
# GROUP BY可以对多个列进行分组操作
# 多个列分组操作实质上就是排列组合
# 假设字段一有A、B,字段二有a,b,这两个分组后会有四个组Aa,Ab,Ba,Bb
# 根据部门和工作分组查询工资
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;
/*
+---------------+------------+-------------+
| department_id | job_id | SUM(salary) |
+---------------+------------+-------------+
| 90 | AD_PRES | 24000.00 |
| 90 | AD_VP | 34000.00 |
| 60 | IT_PROG | 28800.00 |
| 100 | FI_MGR | 12000.00 |
| 100 | FI_ACCOUNT | 39600.00 |
| 30 | PU_MAN | 11000.00 |
| 30 | PU_CLERK | 13900.00 |
| 50 | ST_MAN | 36400.00 |
| 50 | ST_CLERK | 55700.00 |
| 80 | SA_MAN | 61000.00 |
| 80 | SA_REP | 243500.00 |
| NULL | SA_REP | 7000.00 |
| 50 | SH_CLERK | 64300.00 |
| 10 | AD_ASST | 4400.00 |
| 20 | MK_MAN | 13000.00 |
| 20 | MK_REP | 6000.00 |
| 40 | HR_REP | 6500.00 |
| 70 | PR_REP | 10000.00 |
| 110 | AC_MGR | 12000.00 |
| 110 | AC_ACCOUNT | 8300.00 |
+---------------+------------+-------------+
20 rows in set (0.06 sec)
*/
WITH ROLLUP
使用WITH ROLLUP之后,查询的分组记录之后会增加一条记录,该记录会对聚合函数求所有记录的聚合函数,其余字段为NULL。
SELECT department_id,AVG(salary),COUNT(department_id)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;
/*
+---------------+--------------+----------------------+
| department_id | AVG(salary) | COUNT(department_id) |
+---------------+--------------+----------------------+
| 90 | 19333.333333 | 3 |
| 100 | 8600.000000 | 6 |
| 110 | 10150.000000 | 2 |
| NULL | 11809.090909 | 11 |
+---------------+--------------+----------------------+
4 rows in set (0.06 sec)
*/
注:使用ROLLUP的时候,不能用ORDER BY字句进行排序,两者互斥
8.3 HAVING
HAVING的使用
HAVING需要和GROUP BY配合使用,相当于GROUP BY中的WHERE条件,但是WHERE不能使用聚合函数,因此需要用HAVING。
HAVING使用
- 行已经被分组
- 使用了聚合函数
- 满足HAVING的条件的分组会被显示
- HAVING不能单独使用,需要配合GROUP BY
# 查询最大工资大于10000的部门和其最大工资
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
/*
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
| 20 | 13000.00 |
| 30 | 11000.00 |
| 80 | 14000.00 |
| 90 | 24000.00 |
| 100 | 12000.00 |
| 110 | 12000.00 |
+---------------+-------------+
6 rows in set (0.04 sec)
*/
HAVING和WHERE的比较
-
WHERE可以直接用字段作为条件,不能用分组的聚合函数作为条件;
HAVING必须要和GROUP配合使用,可以把分组计算的函数和分组字段作为条件
-
WHERE是先筛选后连接
HAVING是先连接后筛选
这决定了WHERE比HAVING效率更高,因为连接表后,数据成倍数上升,再筛选效率低
开发中使用,分组相关用HAVING,普通条件用WHERE可以提高效率
8.4 SELECT执行顺序
SQL查询语句顺序
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...
SQL的执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
在这七个过程中,每一个过程都会产生一个虚拟表,然后将这个虚拟表传递到下一个步骤。
在FROM结果如果涉及到多表查询
- 直接查询多表会在FORM阶段获取到一个笛卡尔积表
- 然后通过ON来筛选获取筛选后的表
- 如果添加外部行(左连接、右连接),这里会在上面表的基础上增加外部行。
8.5 练习
【题目】
#1.where子句可否使用分组组函数进行过滤?
不能
#2.查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees;
#3.查询各job_id的员工工资的最大值,最小值,平均值,总和
SELECT employees.job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY employees.job_id;
#4.选择具有各个job_id的员工人数
SELECT employees.job_id, COUNT(employees.job_id)
FROM employees
GROUP BY employees.job_id;
#5.查询员工最高工资和最低工资的差距(DIFFERENCE)
SELECT MAX(salary) - MIN(salary)
FROM employees;
# 6.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT e.manager_id, MIN(e.salary)
FROM employees e
WHERE e.manager_id IS NOT NULL AND e.salary > 6000
GROUP BY e.manager_id;
# 7.查询所有部门的名字,location_id,员工数量和平均工资,并按平均工资降序
SELECT d.department_name, d.location_id, COUNT(e.employee_id),AVG(e.salary)
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_id;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT e.job_id, d.department_name, j.job_title, MIN(e.salary)
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN jobs j
ON e.job_id = j.job_id
GROUP BY d.department_id, e.job_id ;