MySQL——分组查询

2023.9.4

        MySQL 分组查询的学习笔记如下：

#分组查询
/*
分组查询中的筛选条件分为两类：数据源		   位置		   关键字
分组前筛选    原始表           group by前面        where
分组后筛选    分组后的结果集   group by后面        having
*/
#查询每个工种的最高工资
SELECT MAX(salary),`job_id` FROM `employees` GROUP BY `job_id`;
#查询每个位置上的部门个数
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
SELECT COUNT(*),`department_id` FROM `employees` GROUP BY `department_id` HAVING COUNT(*)>2;
#查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),`job_id` FROM `employees` WHERE `commission_pct` IS NOT NULL GROUP BY `job_id`;
#根据上述结果继续筛选，最高工资>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 `manager_id`,MIN(salary) FROM `employees` WHERE `manager_id`>102 GROUP BY `manager_id` HAVING MIN(salary)>5000;
#按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些。
SELECT LENGTH(`last_name`),COUNT(*) FROM `employees` GROUP BY LENGTH(`last_name`) HAVING COUNT(*)>5;
#查询每个部门每个工种的员工的平均工资,并按平均工资的高低显示。
SELECT `department_id`,`job_id`,AVG(salary) FROM `employees` GROUP BY `department_id`,`job_id` ORDER BY AVG(salary) DESC;

        课后习题：

#1、查询各job_id的员工工资的最大值，最小值，平均值，总和。并按job_id升序
SELECT `job_id`,MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM `employees` GROUP BY `job_id` ORDER BY `job_id` ASC;
#2、查询员工最高工资和最低工资的差距
SELECT MAX(salary)-MIN(salary)  FROM `employees`;
#3、查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内。
SELECT `manager_id`,MIN(salary) FROM `employees` WHERE `manager_id` IS NOT NULL GROUP BY `manager_id`  HAVING MIN(salary)>=6000;
#4、查询所有部门的编号，员工数量和工资平均值，并按平均工资降序。
SELECT `department_id`,COUNT(*),AVG(salary) FROM `employees` GROUP BY `department_id` ORDER BY AVG(salary) DESC;
#5、选择具有各个job_id的员工人数
SELECT COUNT(*),`job_id` FROM `employees` GROUP BY `job_id` ;