10.分组查询（select| from| where| group by| order by）

最新推荐文章于 2024-07-21 16:28:13 发布

漂酿男骸

最新推荐文章于 2024-07-21 16:28:13 发布

阅读量1.7k

点赞数

分类专栏： Java_jdbc操作数据库文章标签：数据库大数据

本文链接：https://blog.csdn.net/slslslslslsls/article/details/118557812

版权

Java_jdbc操作数据库专栏收录该内容

9 篇文章 4 订阅

订阅专栏

#二:分组函数
/*
	功能:用作统计处理,又称为聚合函数或统计函数或组函数
	分类:
		sum
		avg
		max
		min
		count
	特点:
		1.sum.avg处理数值型
		  max,min,count 处理任何里类型
		2.以上分组函数都忽略null值
		3.和distinct搭配
		4.count函数的详细介绍
		5.和分组查询一同查询的字段要求是group by后的字段
*/

#1.简单的使用
SELECT SUM(salary) FROM employees; # 得到薪水的总和
SELECT AVG(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT ROUND(AVG(salary),2) FROM employees;

# 2.参数类型支持哪些类型
SELECT SUM(last_name), AVG(last_name) FROM employees; # 不支持
SELECT MAX(last_name), MIN(last_name) FROM employees; # 支持
SELECT COUNT(last_name) FROM employees; # 计算非空的个数

# 3.都忽略null值

# 4.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; # 397900.00  691400.00
SELECT COUNT(DISTINCT salary) FROM employees;

# 5.count函数的详细介绍
SELECT COUNT(*) FROM employees; # 可以找到主键
SELECT COUNT(1) FROM employees; # 107

# 进阶5:分组查询
/*
	select 分组函数,列(出现在group by 后面) *********
	from 表名
	where 筛选条件
	group by 分组条件
	order by 排序条件
t特点:	1.分组查询中筛选条件分为两类
			数据源		位置		关键字
	分组前筛选		原始表		group by前面	where
	分组后筛选		分组后的表	group by后面	having
	
	分组函数做条件肯定是放在having子句中;
	能用分组前筛选优先使用分组前筛选
	
	2.group 支持多个字段分组,用逗号隔开,不区分先后,也支持表达式或者函数(用的少)
	3.也可以添加排序,放在最后
*/

# 引入:查询每个部门的平均工资
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id

在这里插入图片描述

# 案例1:查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;

在这里插入图片描述

# 案例2:每个位置上的部门个数
SELECT COUNT(*), location_id FROM departments GROUP BY location_id;

在这里插入图片描述

# 添加筛选条件
邮箱中包含a字符的每个部门的平均工资
SELECT AVG(salary) FROM employees WHERE email LIKE '%a%' GROUP BY department_id

在这里插入图片描述

2.有奖金的每个领导收下员工的最高工资：有奖金的领导
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

# 添加复杂的筛选条件
# 案例1:查询哪个部门的员工数大于2 --->先分组再筛选 having
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2;

在这里插入图片描述

# 案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary) > 12000;

# 案例3:领导编号大于102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
SELECT MIN(salary),manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000;

# 按表达式分组
按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数大于5的有哪些
SELECT COUNT(*) FROM employees GROUP BY LENGTH(CONCAT(`last_name`,`first_name`)) HAVING COUNT(*)>5

在这里插入图片描述

# 按多个字段分组
每个部门每个gongzhong的员工平均工资--->多个字段放在GROUP BY 后面
SELECT AVG(salary) FROM employees GROUP BY department_id,job_id
每个部门每个公众的员工平均工资,按平均工资高低显示
SELECT AVG(salary) ,department_id FROM employees GROUP BY department_id,job_id ORDER BY  AVG(salary) DESC

在这里插入图片描述

练习
1.查询员工最高,最低工资差距
SELECT MAX(salary),MIN(salary),MAX(salary)-MIN(salary) FROM employees
2.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT MIN(salary), manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) > 6000;
3.查询所有部门的编号,员工数量,工资的平均值(SELECT 员工数量,工资的平均值 GROUP BY部门编号)
4.选择具有各个job_id的员工人数(SELECT COUNT(*) FROM TABLE GROUP BY job_id)

进阶5:分组查询

	select 分组函数,列(出现在group by 后面) *********
	from 表名
	where 筛选条件
	group by 分组条件
	order by 排序条件
特点:1.分组查询中筛选条件分为两类
			数据源		位置		关键字
	分组前筛选	原始表		group by前面	where
	分组后筛选	分组后的表	group by后面	having
	
	分组函数做条件肯定是放在having子句中;
	能用分组前筛选优先使用分组前筛选
	
	2.group 支持多个字段分组,用逗号隔开,不区分先后,也支持表达式或者函数(用的少)
	3.也可以添加排序,放在最后

#引入
查询每个部门的平均工资:select avg from 表 group by 部门
select department_id,AVG(salary) FROM employees GROUP BY department_id
 案例1:查询每个工种的最高工资:select max from 表 group by 工种
 SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
 案例2:每个位置上的部门个数:select count(*) from 表 group by 部门
 SELECT COUNT(*), location_id FROM departments GROUP BY location_id;

添加筛选条件
邮箱中包含a字符的每个部门的平均工资:select avg from table where email like '%a%' group by 部门
SELECT AVG(salary) FROM employees WHERE email LIKE '%a%' GROUP BY department_id
2.有奖金的每个领导收下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
 添加复杂的筛选条件
案例1:查询哪个部门的员工数大于2 --->先分组再筛选
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2;
案例2:每个公众有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary) > 12000;
案例3:领导编号大于102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
SELECT MIN(salary),manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000;
按表达式分组
按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数大于5的有哪些
SELECT COUNT(*) FROM employees GROUP BY LENGTH(CONCAT(`last_name`,`first_name`)) HAVING COUNT(*)>5
按多个字段分组
每个部门每个gongzhong的员工平均工资--->多个字段放在GROUP BY 后面
SELECT AVG(salary) ,department_id FROM employees GROUP BY department_id,job_id
添加排序
每个部门每个公众的员工平均工资,按平均工资高低显示
SELECT AVG(salary) ,department_id,job_id FROM employees GROUP BY department_id,job_id ORDER BY  AVG(salary) DESC

查询格job_id的最大最小品均值总合,并按job_id升序
select max,min,sum,avg from table group by job_id order by job_id asc
查询员工最高,最低工资差距
SELECT MAX(salary),MIN(salary),MAX(salary)-MIN(salary) FROM employees
查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT MIN(salary), manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) > 6000;
查询所有部门的编号,员工数量,工资的平均值(SELECT 员工数量,工资的平均值 GROUP BY部门编号)
选择具有各个job_id的员工人数(SELECT COUNT(*) FROM TABLE GROUP BY job_id)