8、分组函数

一、分组函数

功能：用作统计使用，又称聚合函数或统计函数或组函数，只可用在select或者having后，不可用在where后面

分类：sum 求和

只支持数值类型的求和（字符型或日期型求和结果等于0）

avg 平均值

只支持数值类型的平均值（字符型或日期型平均值结果等于0）

max 最大值

支持数值、字符、日期类型的比较

min 最小值

支持数值、字符、日期类型的比较

count 计算个数

支持数值、字符、日期类型的个数计算

以上分组函数在统计时都会忽略null值

可以和distinct()搭配去重使用

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

 

二、count

/*
myisam存储引擎下，count(*)的效率高
innodb存储引擎下，count(*)和count(1)的效率差不多，比count(字段)效率要高一些
*/
select count(*) from employees;
# 补借1的个数，表中有多少行就会补多少行1，然后统计表中1的函数，效果等同于count(*)
select count(1) from employees;

 

三、datediff：计算日期差

# 查询员工表中的最大入职时间和最小入职时间的相差天数
select datediff(max(hiredate),min(hiredate)) from employees;  -- 结果：8735

 

四、分组查询

语法：

select 分组函数,列（要求出现在group by的后面）

from 表

【where 筛选条件】

group by 分组的列表

【order by 子句】

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

特点：

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

数据源 位置 关键字

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

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

2、分组函数做条件肯定放在having子句中

3、能用分组前筛选的，就优先考虑使用分组前筛选（性能考虑）

# 查询哪个部门的员工个数>2
select department_id,count(*) 个数
from employees
group by department_id having 个数>2;

# 查询每个工种有奖金的员工的最高工资>12000的最高工资和工种编号
select max(salary) 最高工资大于12000,job_id
from employees
where commission_pct is not null
group by job_id
having 最高工资大于12000>12000;

# 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是那个，以及最低工资
select min(salary) 最低工资,manager_id
from employees
where manager_id>102
group by manager_id
having 最低工资>5000;

# 按表达式或函数分组
# 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的数据
select count(*) 员工个数,length(last_name) 姓名长度
from employees
group by length(last_name) 
having 员工个数>5;

# 按多个字段分组
# 查询每个部门每个工种的员工的平均工资，并且按照平均工资排序
select avg(salary) 平均工资,department_id,job_id
from employees
group by department_id,job_id
order by 平均工资 desc;

 

四、练习

# 查询公司员工工资的最大值、最小值、平均值、总和
select max(salary),min(salary),avg(salary),sum(salary)
from employees;

select * from employees;
# 查询员工表中的最大入职时间和最小入职时间的相差天数
select datediff(max(hiredate),min(hiredate)) from employees;  -- 结果：8735

# 查询部门编号为90的员工个数
select count(1) 部门为90的编号总和
from employees where department_id='90';

# 按多个字段分组
# 查询每个部门每个工种的员工的平均工资，并且按照平均工资排序
select avg(salary) 平均工资,department_id,job_id
from employees
group by department_id,job_id
order by 平均工资 desc;

use myemployees
# 查询各job_id的员工工资的最大值、最小值、平均值、总和，并按job_id升序
select max(salary) 最高工资,min(salary) 最低工资,avg(salary) 平均值,sum(salary) 总和,job_id
from employees
group by job_id
order by job_id asc;


# 查询员工最高工资和最低工资的差距
select max(salary)-min(salary) diffrence
from employees;

# 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
select min(salary) 最低工资,manager_id
from employees
where manager_id is not null
group by manager_id
having 最低工资>=6000;

# 查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
select department_id,count(*) 员工数量,avg(salary) 平均工资
from employees
GROUP BY department_id
order by 平均工资 desc;


# 选择具有各个job_id的员工人数
select job_id,count(*) 员工人数
from employees
GROUP BY job_id;