链接: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