北京著名的网站制作公司,手机网站后台管理系统,网站建设小组五类成员,电子商务网站建设认识2023.9.4 MySQL 分组查询的学习笔记如下#xff1a;
#分组查询
/*
分组查询中的筛选条件分为两类#xff1a;数据源 位置 关键字
分组前筛选 原始表 group by前面 where
分组后筛选 分组后的结果集 group by后面 having
*/
#查询每…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_id102 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 ;
