数据库 基础笔记5

#进阶五:分组查询 /* 语法: select 分组函数,列(要求出现在group by的后面) from 表 where 筛选条件 group by 分组列表 order by `set_time` 子句 特点 1,分组查询中的筛选条件分为两类 数据源 位置 关键字 分组前筛选 原始表 group by子句的前面 where 分组后筛选 分组后的结果集 后面 having 注意: 1,分组函数做筛选条件肯定是放在having字句中 2，能用分组前筛选，就用分组前筛选 3,group by 支持单个字段分组，也支持多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或则函数 4,也可以添加排序，（排序放在整个语句的结尾） */ #查询每一个部门的平均工资 SELECT TRUNCATE(AVG(salary),2),department_id FROM employees GROUP BY department_id; #--------------------- #查询每一个工种的最高工资并升序排列 SELECT MAX(salary),job_id FROM employees GROUP BY job_id ORDER BY salary; #------------------------- #查询每个位置上的部门个数 SELECT COUNT(*), location_id FROM departments GROUP BY location_id; #分组前添加筛选条件 #查询邮箱中包含a字符的，每个部门的平均工资 SELECT AVG(salary), department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id; #--------------------------------------- #查询有奖金的每一个领导手下员工的最高工资 SELECT MAX(salary), manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id; #--------------------------------------- #分组后添加筛选条件 #查询哪个部门的员工个数 > 2 #思路：1，先查询每一部门员工个数，2，再筛选个数大于2的部门 SELECT COUNT(*), department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2; #分组前后筛选条件搭配使用 #查询每个工种有奖金的员工的最高工资 > 12000的工种编号和最高工资 SELECT MAX(salary), job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary) > 12000; #--------------------------------------------------------- #查询领导编号 > 102的每个领导手下的最低工资 > 5000 的领导编号及其最低工资 SELECT MIN(salary), manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000; #--------------------------------------------- #按表达式或函数分组 #按员工姓名长度分组，查询每一组的员工个数，筛选员工个数> 5的有哪些 SELECT COUNT(*), LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*) > 5; #----------------------------------------- #按多个字段分组 #案例：查询每一个部门每个工种的员工的平均工资 SELECT AVG(salary), department_id, job_id FROM employees GROUP BY department_id, job_id; #----------------------------------------------- #查询每个部门每个工种的员工的平均工资，并且按平均工资的高地显示 SELECT AVG(salary), department_id, job_id FROM employees #where # department_id is not null GROUP BY department_id, job_id #having # avg(salary) > 10000 ORDER BY AVG(salary) DESC; #-------------------------------------------案例- #1,查询各个job_id的员工工资的最大值，最小值，平均值，总和并按job_id升序 SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary), job_id FROM employees GROUP BY job_id ORDER BY job_id DESC; #2,查询员工最高工资和最低工资的差距 SELECT MAX(salary) - MIN(salary) AS 差距 FROM employees; #------------------- #3,查询各个管理者手下的员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内 SELECT MIN(salary), manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) >= 6000; #----------------------------- #4,查询所有部门的编号，员工数量和平均工资，并按平均工资降序 SELECT COUNT(*), AVG(salary), department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC; #------------------ #查询每一个job_id的员工人数 SELECT COUNT(*), job_id FROM employees GROUP BY job_id;