Mysql的分组函数

功能

• 用作统计使用，又称为聚合函数或统计函数或组函数

分类

• sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

特点

• 1、sum、avg一般用于处理数值型,max、min、count可以处理任何类型
• 2、以上分组函数都忽略null值
• 3、可以和distinct搭配实现去重的运算
• 4、count函数的单独介绍,一般使用count(*)用作统计行数
• 5、和分组函数一同查询的字段要求是group by后的字段
• 一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率(where)

简单的使用

SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

count函数的详细介绍

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

效率

• MYISAM存储引擎下 ，COUNT(*)的效率高
• INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些

进阶

• 语法：

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

• 引入：查询每个部门的员工个数

SELECT COUNT(*) FROM employees WHERE department_id=90;

• 案例1：查询每个工种的员工平均工资

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

• 案例2：查询每个位置的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

可以实现分组前的筛选

• 案例1：查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

• 案例2：查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

分组后筛选

• 查询每个部门的员工个数

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;

• 筛选刚才的结果

SELECT COUNT(*),department_id
FROM employees

GROUP BY department_id

HAVING COUNT(*)>5;

• 案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

• 案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

SELECT manager_id,MIN(salary)
FROM employees
where  manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

添加排序

• 案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;

按多个字段分组

• 案例：查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

练习

该资料来自于：https://www.cnblogs.com/xuzhendong-0302/p/10596163.html

建表

create table if not exists employee(eid int auto_increment primary key,name varchar(5),age int,salary int,depart varchar(5),workage int);

insert into employee (name,age,salary,depart,workage)

values ('崔铭','25',1500,'研发部',3),
       ('佳伟','23',1000,'市场部',2),
       ('刘涵','30',10600,'人事部',6),
       ('孙铭泽','25',2000,'运营部',5),
       ('张吉龙','21',15000,'生产部',12),
       ('从好平','22',1500,'质量部',3),
       ('杨忠','22',5000,'财务部',4),
       ('芦淞','24',6000,'采购部',7),
       ('马玉','25',450000,'销售部',29),
       ('成林','21',12000,'安全部',10),
       ('张龙','32',17000,'研发部',21),
       ('王建业','25',11000,'研发部',7),
       ('王佳敏','22',10000,'市场部',9),
       ('姜佳伟','27',10000,'人事部',13),
       ('王国栋','20',10012,'研发部',2),
       ('周昌洋','38',10560,'研发部',1),
       ('刘鑫鑫','18',1900,'人事部',6),
       ('刘博','21',2000,'研发部',11),
       ('乔鑫','19',13000,'运营部',12),
       ('宇航','20',10500,'生产部',21),
       ('赵浩然','33',10400,'总经理',21),
       ('常盛','24',1000,'生产部',2),
       ('刘麟','25',3000,'武装部',8);

1. 查询每个部门的总薪资

select depart,SUM(salary) from employee
group by depart;

2. 员工数超过3人的部门的最高薪资和最低薪资

select depart, MAX(salary),MIN(salary) from employee
group by depart
having count(*) >3;

3. 工龄超过3年的员工中,薪资最低的所有员工信息

select * from employee
where salary in (
select MIN(salary)from employee
where workage>3);

4. 工龄超过3年的员工数大于3的部门

select depart,count(*) from employee
where workage > 3
group by depart
having count(*)>3;

建表

create table if not exists score(sname varchar(10), cname varchar(5),grade int) charset=utf8;
insert into score(sname,cname,grade)
values ('张三','数学',80),
       ('张三','语文',90),
       ('张三','英语',70),
       ('张三','物理',60),
       ('李四','数学',66),
       ('李四','语文',60),
       ('李四','英语',80),
       ('李四','物理',90),
       ('刘志麟','语文',99),
       ('刘志麟','数学',50),
       ('刘志麟','英语',50),
       ('刘志麟','物理',89),
       ('罗宇航','语文',99),
       ('罗宇航','数学',80),
       ('罗宇航','物理',78),
       ('罗宇航','英语',96),
       ('许振东','数学',96),
       ('许振东','语文',96),
       ('许振东','英语',96),
       ('许振东','物理',96);

1. 查询90分以上的学生的课程名和成绩

select * from score
where grade > 90;

2. 查询每个学生的成绩在90分以上的各有多少门

select sname,count(cname) from score
where grade > 90
group by sname
having count(cname);

3. 至少有两门课程在90分以上的学员以及90分以上的课程数

select sname,count(cname) from socre
where grade > 90
group by sname
having count(cname) >2;

4. 平均成绩比张三的平均成绩高的学员和其平均分

select sname, AVG(grade) from score 
group by sname
having AVG(grade) > (select AVG(grade) from score
where sname = '张三');

5. 查询平均成绩大于90分并且语文课95分以上的学生名和平均成绩

select sname, AVG(grade) from score
where sname in (
select sname from score
where cname='语文'and grade>95)
group by sname
having AVG(grade)>90;

6. 查询每个学员的平均分和学生名

select sname, AVG(grade) from score
group by sname;

7. 查询每门课的最好成绩和平均分

select cname MAX(grade),AVG(grade) from score
group by cname;

8. 查询数学课成绩最好的学员的所有成绩

select * from score
where sname in (
select sname from score
where grade =(
select MAX(grade)from score
where cname='数学')and cname='数学');

9. 查询学员及其总分,按总分降序排列

select sname ,SUM(grade) from score
group by sname
order by SUM(grade) desc;