第八章:聚合函数
聚合函数,它是对一组数据进行汇总的函数,输入的是一组数据的集合,输出的是单个数值
1.常见的几个聚合函数
AVG() SUM() MAX() MIN() COUNT()
2.GRUOP BY 的使用
3.HAVING的使用
HAVING的使用(作用:用来过滤数据的)
查询各个部门中最高工资比10000高的部门信息
SELECT department_id,MAX(salary) FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;
结论1:如果过滤条件中使用了聚合函数,则必须使用having来替代where。
否则,报错
结论2:HAVING 必须声明在 GROUP BY的后面
结论3:开发中,我们使用 HAVING的前提是SQL中会用 GROUP BY
查询部门id为10 20 30 40这4个部门中最高工资比10000高的部门信息
方式1:
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 AND department_id IN (10,20,30,40);
方式2:
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary)>10000;
结论:当过滤条件中有聚合函数时,则过滤条件必须声明在having中
当过滤条件中没有聚合函数时,可以使用where或having,但是建议声明再where中。
HAVING and WHERE
在开发中:WHERE和having也不是相互排斥的,我们可以在一个查询里同时使用where和having。
在包含分组查询中使用having,普通查询中使用where,有聚合函数使用having,无聚合函数使用where。
WHERE是先筛选后连接,而having是先连接后筛选。所有where更加高效
SQL92 语法
SELECT ...,...,...,(存在聚合函数)
FROM ...,...,...,
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,...,
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...,(ASC/DESC)
LIMIT ...
SQL 99语法
SELECT...存在聚合函数
FROM ...(LEFT /RIGHT )JOIN ...ON
WHERE ... 不包含聚合函数的过滤条件
GROUP BY ...
HAVING 包含聚合函数的过滤条件
ORDER BY...(ASC/DESC)
LIMIT...
4.SQL底层执行原理
sql 语句的执行过程:
FROM ...-> ON ->(LEFT /RIGHT JOIN) ->WHERE ->GROUP BY ->HAVING ->SELECT ->DISTINCT->ORDER BY->LIMIT
聚合函数课后练习题:
1.WHERE字句是否可以使用组函数进行过滤。
no
2.查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(salary),MIN(salary),AVG(salary),COUNT(salary)*AVG(salary) AS "sum",SUM(salary)
FROM employees
3.查询job_id各员工工资的最大值,最小值,平均值,总和
SELECT job_id, MAX(salary),MIN(salary),AVG(salary),COUNT(salary)*AVG(salary) AS "sum",SUM(salary)
FROM employees
GROUP BY job_id;
4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;
5.查询员工最高工资和最低工资之差
SELECT MAX(salary),MIN(salary),MAX(salary)-MIN(salary) as gap_of_salary
FROM employees
6.查询各个管理者手下员工的最低工资,其中最低工资不能低于
6000,没有管理者的员工不计算在类
SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id
HAVING MIN(salary)>6000 AND manager_id is NOT NULL;
7.查询所有部门的名字,location_id,员工的数量和平均工资,并按平均工资降序
SELECT department_name,location_id,COUNT(*),AVG(salary)
FROM employees e right JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name,location_id ;
8.查询每个工种,每个部门名的工种名和最低工资
SELECT d.department_name,e.job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON
d.department_id =e.department_id
GROUP BY department_name,job_id