常见函数之分组函数

1.功能：用作统计使用，又称为聚合函数，统计函数，组函数；

分类：常见的函数有；sum求和、avg平均值、max最大值、min最小值、count计算个数

特点：（1）sum/avg一般用于处理数值型；

（2）Max/min/count可以处理任何类型；

1. Sum/avg/max/min/count都忽略null值；
2. 可以和distinct搭配实现去重的运算；
3. 一般使用count(*)用做统计行数；
4. 和分组函数一同查询的字段，要求是grounp by后的字段

2、简单的用

Select sum(salary) from employees;单个使用；

Select sum(salary) 和,avg(salary) 平均 ,max(salary) 最高,min(salary) 最低,count(salary) 个数 from employees;   一起用；

Select sum(salary) 和,round(avg(salary),2) 平均 ,max(salary) 最高,min(salary) 最低,count(salary) 个数 from employees;   一起用；

3、参数支持类型

Select sum(last_name) ,avg(last_name)from employees; 不建议，不是错，但不可以，没意义；

Select max(last_name) ,min(last_name)from employees;支持的

是否忽略null

Select sum(commission_pct) ,avg(commission_pct)from employees;查看奖金，结果忽略null值；

Select min(commission_pct) ,max(commission_pct)from employees;结果忽略null值；

Select count(commission_pct)from employees;结果忽略null值；

和distinct的搭配

Select sum(distinct salary) ,sum( salary) from employees;

Select count(distinct salary),count(salary)from employees;

结果显示可以实现去重的运算；

count函数的详细介绍

Select count（*） from employees； 统计行数；

Select count（1） from employees；

效率：

MYISAM存储引擎下，count（*）的效率高；

INNODB存储引擎下，count（*）和count(1)的效果差不多，比count(字段)要高一些；

综上所述，用count（*）比较好；

测试题：

1. 查询公司员工工资最大，最小，平均值，总和

Select max(salary) mx_sal,min(salary) mi_sal,round(avg(salary),2) ag_sal,sum(salary) sum_sal from employees;

1. 查询员工表中的最大、最小入职时间相差多少天(diffrence）

Select datediff(‘1993-1-1’,’2022-12-1’);

Select datediff(max(hiredate),min(hiredate)) diffrence from employees;

1. 查询部门编号为90的员工数量

Select count(*) 个数 from employees where department_id=90;

分组查询

Ground by 子句语法；

Select 分组函数 ，列（要求出现在ground by 的后面）

 from  表

  where[筛选条件]  

ground by[分组的列表]

Orde byr [子句]

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

特点：

1、分组查询分为两类：

                数据源                 位置                   关键字

一个分组前筛选：原始表                 group by 子句的前面    where

一个分组后筛选：分组后的结果集         group by 子句的后面    having

1. 分组函数做条件肯定是放在having子句中；
2. 能用分组前筛选，优先考虑分组前筛选；

1. group by 子句支持单个、多个字段分组（多个字段用逗号隔开，没有顺序要求），表达式或函数（用得较少）
2. 也可添加排序（排序放在整个分组查询的最后）

例题1：

查询每个工种的最高工资

Select max(salary),job_id from employees group by job_id;

例题2：

查询每个位置上的部门个数

Select count(*),location_id from employees group by location_id ;

难度增加：分组前的筛选

例题3：

查询邮箱中包含a字符的，每个部门的平均工资

Select avg(salary),department_id from employees where email like’%a%’

group by department_id;

例题4：

查询有奖金的每个领导手下员工最高工资是

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;

按照表达式或函数分组

例题1：按员工姓名的长度分组，查询每组员工个数，筛选员工个数大于5的

Select count(*) ,length(last_name) len_name from employees  group by length(last_name) having count(*) >5;

按多个字段分组

例题：查询每个部门每个工种的员工平均工资

Select avg(salary) ,department_id,job_id from employees  group by job_id,department_id;

添加排序

例题：查询每个部门每个工种员工平均工资，按平均工资高低排序

Select avg(salary) ,department_id,job_id from employees where department_id is not null  group by job_id,department_id order by avg(salary) desc;