#二、分组函数
/*
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点:
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段
*/
#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;
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;
#2、参数支持哪些类型
SELECT
MAX (last_name),
MIN (last_name),
COUNT (last_name)
FROM
employees;
SELECT
MAX (hiredate),
MIN (hiredate),
COUNT (last_name)
FROM
employees;
SELECT
COUNT (commission_pct)
FROM
employees;
#3、是否忽略null
SELECT
SUM (commission_pct) / 35
FROM
employees;
SELECT
AVG (commission_pct)
FROM
employees;
#4、和distinct搭配
SELECT
SUM (DISTINCT salary),
SUM (salary)
FROM
employees;
SELECT
COUNT (DISTINCT salary),
COUNT (salary)
FROM
employees;
#5、count函数的详细介绍
SELECT
COUNT (salary)
FROM
employees;
SELECT
COUNT (*)
FROM
employees;
SELECT
COUNT (1)
FROM
employees;
/*
效率:
MYISAM存储引擎下 ,COUNT(*)的效率高
INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些
*/
#6、和分组函数一同查询的字段有限制
SELECT
AVG (salary),
employee_id
FROM
employees;
#这样写没有意义
#练习
#1.查询公司员工工资的最大值,最小值,平均值,总和
SELECT
SUM (salary) 最大值,
SIN (salary) 最小值,
AVG (salary) 平均值,
SUM (salary) 总和
FROM
employees;
#2.查询员工表中的最大入职时间和最小入职时间的相差天数 (DIFFRENCE)
SELECT
MAX (hiredate),
MIN (hiredate),
DATEDIFF (MAX (hiredate), MIN (hiredate)) DIFFRENCE
FROM
employees;
SELECT
DATEDIFF ('2000-1-1', '2000-1-5');
#3.查询部门编号为90的员工个数
SELECT
COUNT (*)
FROM
employees
WHERE department_id = 90;