mysql分组查询

2023-08-06 22:13:40

一、分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum 求和，avg 平均值，max最大值,min最小值，count计算个数

1.简单使用

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;

统计个数：SELECT COUNT(salary) FROM employees;

2.参数支持哪些类型

统计非空值的个数：
SELECT COUNT(salary) FROM employees;

特点
1.sum,avg 一般用于处理数值类型
max,min,count 可以处理任何类型
2.是否忽略null值（以上分组函数都忽略了null值）
null + 任何值 == null
sum，avg,max,min ,count 都忽略了null值
3.和distinct搭配

select sum(distinct salary),sum(salary) from emloyees;

4.和分组函数一同查询的字段要求是group by后的字段

3.count函数详细介绍

select count(*) from employees;
select count(1) from
employees;

[（1）括号里面的1其实可以是任何常量值，相当于在表上加了一列]
效率：
myisam存储引擎下，count(*)效率高
innodb存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高一些，因为count字段要先判断是否为空
总结：一般用作count(*)统计行数

二、分组查询

1.语法

select 分组函数，列(要求出现在group by 的后面)
from 表
[where 筛选条件]
grop by 分组的列表
[order by 子句]

注意：
查询表必须特殊，要求是分组函数和group by后出现的字段

案例1：

查询每个工种的最高工资
select max(salary),job_id
from employees
grop by job_id;

案例2：

查询每个位置上的部门个数
select count(*),location_id
from departments
group by location_id;

2.添加筛选条件(分组前的筛选)

1.查询邮箱中包含a字符的,每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id

2.查询有奖金的每个领导手下员工的最高工资
select max(salary),manage_id
from employees 
where commission_pct 
is not null 
group by manage_id;

3.添加复杂的筛选条件（分组后的筛选）

1.查询哪个部门的员工个数>2
思路：
①.查询每个部门的员工个数
②.根据①的结果进行筛选，查询哪个部门的员工个数>2

select count(*),department_id from 
employees  
group by department_id
having count(*)>2;
因为是要判断 筛选后的结果，所以在 group by 后面追加 语句

2.查询每个工种有奖金的员工的最高工资>12000的工资编号和最高工资
思路：
①.查询每个工种有奖金的员工的最高工资(筛选字段在原始表中由，就可以在from后面使用where关键字)

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

3.查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
思路：
①.查询每个领导手下的员工的最低工资

select min(salary),manage_id
from employees
group by manage_id

②.添加筛选条件:编号>102

select min(salary),manage_id
from employees
where manage_id >102
group by manage_id

③.添加筛选条件，最低工资>5000

select min(salary),manage_id
from employees
where manage_id >102
group by manage_id
having min(salary)>5000;

4.按表达式或函数分组

案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
思路：
①.查询每个长度的员工个数

select count(*)，length(last_name) len_name
from employees
group by length(last_name)；

②.添加筛选条件

select count(*) ，length(last_name) len_name
from employees
group by length(last_name)
having count(*)>5;

5.按多个字段分组

案例：查询每个部门每个工种的员工平均工资，并且按平均工资高低显示

select avg(salary),
department_id,job_id
from employees
group by department_id,job_id
order by avg(salary) desc;

补充说明：(group by后面的顺序是可以颠倒的)

6.特点总结：

1.分组查询中的筛选条件分为两类

	数据源	位置	关键字
分组前筛选	原始表	group by 子句的前面	where
分组后筛选	分组后的结果集	group by子句的后面	having

2.分组函数做条件肯定是放在having子句中
3.能用分组前筛选的，就有限考虑使用分组前筛选
4.group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或者函数用的少
5.也可以添加排序（排序放在整个分组查询的最后）
练习1：查询员工最高工资和最低工资的差距

select max(salary)-min(salary) diference
from employees;

练习2：查询各个管理者手下员工的最低工资，其中最低工资不能低于6000,没有管理者的员工不计算在内

select min(salary),manage_id
from employees
where manage_id is not null
group by manage_id
having min(salary)>=6000

练习3：查询所有部门的编号，员工数量和工资平均值，并按平均工资降序

select avg(salary) a,department_id,count(*)
from employess
group by department_id
order by a desc;

练习4：选择各个具有job_id的员工人数(题目意思就是按照job_id进行分组)

select count(*) 个数，job_id
from employees
group by job_id；

码农公寓