第8章_聚合函数

芳华梦落 2022-9-27 88 9/27

#1WHERE子句可否使用组函数进行过滤

NO! 但是having可以

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

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;

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

SELECT job_id,MAX(salary),MIN(salary),AVG(salary),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) 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 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;

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

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

 SELECT 语句的完整结构

/*

#sql92语法:
SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

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

*/

SQL语句的执行过程:

#FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT ->
# ORDER BY -> LIMIT

- THE END -

芳华梦落

10月08日15:50

最后修改:2022年10月8日
0

非特殊说明,本博所有文章均为博主原创。