MySQL 常见函数 -------分组函数

功能：用作统计使用，又称为聚合函数或者统计函数或组函数

分类：SUM 求和、AVG 平均值、MAX 最大值、MIN 最小值、COUNT 计算个数

特点：

1.sum、AVG 一般用于处理数值型
MAX、MIN、COUNT 可以处理任何类型
2.忽略NULL值
3.可以和DISTINCT搭配实现去重的运算
4.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) AS 和,
AVG(salary) AS 平均值,
MAX(salary) AS 最大值,
MIN(salary) AS 最小值,
COUNT(salary) AS 个数
FROM
employees;

2.参数支持那些类型

SELECT MAX(last_name),MIN(last_name) FROM employees;

3.是否忽略NULL值 是的

SELECT SUM(commission_pct),AVG(commission_pct)FROM employees;
SELECT COUNT(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(commission_pct) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

6.和分组函数一同查询的字段有限制

SELECT AVG(salary) FROM employees;

练习题

1.查询公司员工工资的最大值、最小值、平均值、总和
SELECT MAX(salary),MIN(salary),AVG(salary) FROM employees;

2.查询员工表中最大入职时间和最小入职时间的相差天数（diffrence）
SELECT DATEDIFF(MAX(hiredate) ,MIN(hiredate)) AS 相差天数 FROM employees;

3.查询部门编号为90的员工数
SELECT COUNT(*) FROM employees WHERE department_id=90;