聚合函数
MySQL8.0版本提供的聚合函数类型一共有19个,本文结合具体实例介绍其中较常用的前9个。剩余聚合函数可点击函数名跳转到MySQL官网查看具体解释。
名字 | 描述 |
---|---|
AVG() | 返回参数的平均值 |
MAX() | 返回最大值 |
MIN() | 返回最小值 |
SUM() | 返回总和 |
COUNT() | 返回返回的行数的计数 |
COUNT(DISTINCT) | 返回多个不同值的计数 |
GROUP_CONCAT() | 返回串联字符串 |
JSON_ARRAYAGG() | 将结果集作为单个JSON数组返回 |
JSON_OBJECTAGG() | 将结果集作为单个JSON对象返回 |
BIT_AND() | 按位返回 AND |
BIT_OR() | 按位返回OR |
BIT_XOR() | 按位返回异或 |
STD() | 返回总体标准差 |
STDDEV() | 返回总体标准差 |
STDDEV_POP() | 返回总体标准差 |
STDDEV_SAMP() | 返回样本标准偏差 |
VAR_POP() | 返回总体标准方差 |
VAR_SAMP() | 返回样本方差 |
VARIANCE() | 返回总体标准方差 |
数据准备
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY,-- 员工id
name VARCHAR (50),-- 员工姓名
join_date DATE,-- 入职日期
salary DECIMAL (7, 2),-- 工资
bonus DECIMAL (7, 2),-- 绩效奖金
dept VARCHAR(50)-- 所属部门
);
-- 添加员工
INSERT INTO emp (id, name, join_date, salary, bonus, dept)
VALUES
(1001, '孙悟空', '2000-12-17', '8000.00', NULL, '学工部'),
(1002, '卢俊义', '2001-02-20', '16000.00', '3000.00', '销售部'),
(1003, '林冲', '2001-02-22', '12500.00', '5000.00', '销售部'),
(1004, '唐僧', '2001-04-02', '29750.00', NULL, '学工部'),
(1005, '李逵', '2001-09-28', '12500.00', '14000.00', '销售部'),
(1006, '宋江', '2001-05-01', '28500.00', NULL, '销售部'),
(1007, '刘备', '2001-09-01', '24500.00', NULL, '教研部'),
(1008, '猪八戒', '2007-04-19', '30000.00', NULL, '学工部'),
(1009, '罗贯中', '2001-11-17', '50000.00', NULL, '教研部'),
(1010, '吴用', '2001-09-08', '15000.00', '6000.00', '销售部'),
(1011, '沙僧', '2007-05-23', '11000.00', NULL, '学工部'),
(1012, '李逵', '2001-12-03', '9500.00', NULL, '销售部'),
(1013, '小白龙', '2001-12-03', '30000.00', NULL, '学工部'),
(1014, '关羽', '2002-01-23', '13000.00', NULL, '教研部');
AVG()
例:查询所有员工的平均工资,查询结果保留两位小数
-- AVG()返回参数的平均值,ROUND(参数, 保留位数)
SELECT
ROUND(AVG(salary), 2) '平均工资'
FROM
emp;
MAX()
例:查询工资最高的员工信息
-- MAX() 子查询
-- 不能直接将MAX(salary)作为条件 否则会报Invalid use of group function聚合函数不可用的错误
-- 需要先从emp表中查出最高工资,再查询对应员工信息
SELECT
*
FROM
emp
WHERE
emp.salary = (SELECT MAX(salary) FROM emp);
-- 错误示例
SELECT
*
FROM
emp
WHERE
emp.salary = MAX(salary);
MIN()
例:查询工资最低的员工信息
-- MIN() 子查询(同上MAX)
SELECT
*
FROM
emp
WHERE
emp.salary = (SELECT MIN(salary) FROM emp);
SUM()
例:查询所有员工的工资总和
-- SUM(),返回指定参数列的值总和
SELECT
SUM(salary) '工资总和'
FROM
emp;
COUNT()
例:查询拿到绩效奖金的员工人数
-- COUNT()
-- 如果没有匹配的行,COUNT() 将返回0
-- COUNT(*) 略有不同,它返回检索到的行数的计数,无论它们是否包含NULL
SELECT
COUNT(bonus) '获得绩效奖金的人数'
FROM
emp;
COUNT(DISTINCT)
例:查询emp表中的员工共来自几个部门
-- COUNT(DISTINCT) DISTINCT去除重复记录
-- 错误示例(不去除重复记录,查询结果为14)
SELECT
COUNT(dept) '部门总数'
FROM
emp;
-- 正确
SELECT
COUNT(DISTINCT dept) '部门总数'
FROM
emp;
GROUP_CONCAT()
例:查询emp表中各部门员工姓名
-- GROUP_CONCAT()
-- 函数返回一个字符串结果
SELECT
dept '部门',
GROUP_CONCAT(name) '员工姓名'
FROM
emp
GROUP BY
dept;
JSON_ARRAYAGG()
例:查询emp表中各部门员工姓名
-- JSON_ARRAYAGG(col or expr),参数可以是 列名 也可以是一个 表达式
-- 将结果集聚合为单个 JSON 数组,其元素由行组成。此数组中元素的顺序未定义
SELECT
dept '部门',
JSON_ARRAYAGG(name) '部门员工姓名'
FROM
emp
GROUP BY
dept;
JSON_OBJECTAGG()
例:查询emp表中所有的员工及其薪资,返回姓名:薪资的 JSON 对象
-- JSON_ARRAYAGG(key, value),
-- 将两个列名或表达式作为参数,其中第一个用作键,第二个用作值,并返回一个包含键值对的 JSON 对象
SELECT
JSON_OBJECTAGG(name, salary) '员工:薪资'
FROM
emp
GROUP BY
id;