一文让你搞懂mysql分组函数(sum,avg,max,min,count）的用法及简介

本篇博客所使用到的数据库和表请看我前面的博客文章里面有表的建立

分组函数

功能：

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

分类：

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

特点：

1，sum，avg一般用于处理数值型 max，min，count可以处理任何类型

2，以上分组函数都可以忽略null值

3，可以和distinct搭配实现去重的运算

4，count函数的单独介绍，一般用于coumt(*)用作统计行数

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

简单的使用

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;

和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;

count的效率

MYISAM存储引擎，count()的效率高

INNODB存储引擎，count()和count(1)的效率差不多，比count(字段)要高一些

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

SELECT
	max( salary ) AS 最高工资,
	min( salary ) AS 最小工资,
	avg( salary ) AS 平均值,
	sum( salary ) AS 总合 
FROM
	employees;

DATEDIFF 计算相差时间的函数

查询员工表中的最大入职时间和最小入职时间的相差天数(DIFFENCE)

SELECT
	max( hiredate ) AS 最大入职时间,
	min( hiredate ) AS 最小入职时间,
	datediff(
		max( hiredate ),
	min( hiredate )) AS DIFFERENCE 
FROM
	employees;

查询部门编号为90的员工个数

SELECT
	count(*) AS '编号 90员工个数'
FROM
	employees 
WHERE
	department_id = 90;

分组查询

语法：

select	分组函数，列(要求出现在group by 的后面)

from 表

where	筛选条件

group by 分组列表

order by 子句

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

特点：

一：分组查询中的筛选条件分为两类

一：分组函数做条件肯定是放在having子句中
二：能用分组前筛选，就优先考虑使用分组前筛选
group by 子句支持单个字段的分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求)，表达式或函数(用的较少)
三：也可以添加排序(排序放在整个分组查询的最后)

查询每个部门的平均工资

select avg(salary) from employees;

查询每个工种的最高工资

select job_id as 部门id,max(salary) as 最高工资
from employees
group by job_id;

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

select count(*),location_id
from departments
grouy by location_id;

添加分组前筛选条件

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

select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;

案例二：查询有奖金的每个领导手下员工的最高工资

select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;

添加分组后的筛选条件

案例一：查询哪个部门的员工个数>2

分为两步：第一步—先查询每个部门的员工个数

select count(*) as '员工个数', department_id from employees
group by department_id;

第二步： 根据第一步的结果再进行筛选

select count(*) as '员工个数' ,department_id from employees
group by department_id
having count(*)>2;

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

第一步，先查询每个工种有奖金的员工的公众编号和最高工资

select job_id,max(salary)
from employees
where commission_pct is not null
group by job_id;

第二步在第一步的基础上筛选最高工资>12000

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

案例三：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

第一步先查询每个领导编号>102手下员工的最低工资

SELECT
	min( salary ),
	manager_id 
FROM
	employees 
WHERE
	manager_id > 102 
GROUP BY
	manager_id;

第二步在第一步的基础上查询最低工资>5000的领导编号

SELECT
	min( salary ),
	manager_id 
FROM
	employees 
WHERE
	manager_id > 102 
GROUP BY
	manager_id
	having min(salary)>5000;

按表达式或函数分组

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

第一步：先查询按员工姓名的长度分组，查询每一组员工的个数

select length(last_name),count(*)
from employees
group by length(last_name);

第二步在第一步的基础上查询员工个数>5

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

按多个字段分组

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

select avg(salary),department_id,job_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
	job_id,
	max( salary ),
	min( salary ),
	sum( salary ) 
FROM
	employees 
GROUP BY
	job_id 
ORDER BY
	job_id;

查询员工最高工资和最低工资的差距(difference)

SELECT
	max( salary )- min( salary ) AS difference 
FROM
	employees;

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

SELECT
	manager_id,
	min( salary ) 
FROM
	employees 
WHERE
	manager_id IS NOT NULL 
GROUP BY
	manager_id 
HAVING
	min( salary )>= 6000;

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

SELECT
	department_id,
	count(*),
	avg( salary ) 
FROM
	employees 
GROUP BY
	department_id 
ORDER BY
	avg( salary ) DESC;

选择具有各个job_id的员工个数

SELECT
	count(*),
	job_id 
FROM
	employees 
WHERE
	job_id IS NOT NULL 
GROUP BY
	job_id;

连接查询

**含义：**又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询

产生笛卡尔乘积现象的语句：

select name,boyName from boys,beauty;

笛卡尔乘积现象：表1有M行，表2有N行，结果=M*N行

发生原因：

没有有效的连接条件

如何避免：

添加有效的连接条件

分类：

按年代分类：

sql92标准：仅仅支持内连接
sql99标准：支持所有的内连接+外连接(左外+右外）+交叉连接

按功能分类：

内连接：

	等职连接
	
	非等值连接
	
	自连接
	
外连接：

	左外连接
	
	右外连接
	
	全外连接

交叉连接

避免产生笛卡尔乘积现象的sql语句：

select name,botName from boys,
beauty
where beauty.boyfriend_id=boys.id;