MySQL的聚合函数和内置函数

1.聚合函数

函数	说明
COUNT(DISTINCT expr)	返回查询到的数据的不同值的 数量
SUM(expr)	返回查询到的数据的 总和，如果数据不是数字则没有意义
AVG(expr)	返回查询到的数据的 平均值，如果数据不是数字则没有意义
MAX(expr)	返回查询到的数据的 最大值，如果数据不是数字则没有意义
MIN(expr)	返回查询到的数据的 最小值，如果数据不是数字则没有意义

在 SQL ��，聚合函数（如 MAX()、MIN()、AVG()、SUM()）用于对一组数据进行计算，并返回单个值。聚合函数通常应用于整列数据，而不是单个行或单个数据项。聚合函数的条件通常是对多个数据行进行计算，然后生成一个汇总结果。

（）

通常情况下，聚合函数的条件是：

1. 对多个数据行进行计算：聚合函数应用于数据集中的多行，而不是单个行。例如，你可以计算一列的总和、平均值、最大值或最小值。

   （）

2. 返回单个值：聚合函数计算结果是单个值，而不是每个行的值。例如，SUM 函数计算一列的总和，返回一个总和值。

3. 不包括 GROUP BY 子句中的列：如果查询中包含 GROUP BY 子句，聚合函数将对分组后的数据进行计算。每个分组将产生一个结果行。在这种情况下，聚合函数的条件是应用于每个分组，并返回每个分组的汇总结果。

总的来说，聚合函数的条件是对多个数据行进行计算，生成单个值的结果，通常在 SELECT 语句的末尾或包含 GROUP BY 子句的查询中使用。

上述函数使用起来很简单，直接看下面例子即可：

# 使用各种聚合函数mysql> select * from exam_result;+----+-------+---------+------+---------+| id | name  | chinese | math | english |+----+-------+---------+------+---------+|  1 | limou |     134 |  196 |     112 ||  2 | swk   |     174 |  216 |     154 ||  3 | zwn   |     176 |  256 |     180 ||  4 | cmd   |     164 |  228 |     134 ||  5 | lxd   |     110 |  170 |      90 ||  6 | sq    |     140 |  146 |     156 ||  7 | sgm   |     150 |  130 |      60 ||  8 | dimou |    NULL |  196 |     116 |+----+-------+---------+------+---------+7 rows in set (0.00 sec)mysql> select count(*) from exam_result; # 统计所有行数，不受 null 影响+----------+| count(*) |+----------+|        8 |+----------+1 row in set (0.01 sec)mysql> select count(chinese) from exam_result; # 统计 chinese 字段的行数（出去 null）+----------------+| count(chinese) |+----------------+|              7 |+----------------+1 row in set (0.00 sec)mysql> select count(distinct math) from exam_result; # 统计出不重复的数学成绩+----------------------+| count(distinct math) |+----------------------+|                    7 |+----------------------+1 row in set (0.00 sec)mysql> select sum(math) from exam_result; # 统计数学成绩总分+-----------+| sum(math) |+-----------+|      1538 |+-----------+1 row in set (0.00 sec)mysql> select sum(math)/count(*) from exam_result; # 计算数学成绩平均分+--------------------+| sum(math)/count(*) |+--------------------+|             192.25 |+--------------------+1 row in set (0.00 sec)mysql> select count(*) from exam_result where english select avg(chinese+english+math) as '平均分' from exam_result where # 求班级的总成绩平均分english select max(chinese+english+math) as ' 高分 ' from exam_result; # 得到总分最高分+-----------+| 最高分    |+-----------+|       612 |+-----------+1 row in set (0.00 sec)mysql> select max(math) as '最高分' from exam_result; # 得到数学最高分+-----------+| 最高分    |+-----------+|       256 |+-----------+1 row in set (0.00 sec)

3.聚合函数分组聚合

聚合函数可以结合分组子句来使用，首先需要明白，分组的目的是为了方便进行聚合统计。

而另外一种角度来理解分组聚合统计就是“分表”，把一张表按照条件，在逻辑上拆分成多个子表，然后分别进行聚合统计，这种理解方法可以和之前针对单表的聚合统计衔接起来。

例如下列 SQL 语句：

# 尝试使用分组查询mysql> select max(sal) 最高工资, avg(sal) 平均工资 from emp; # 显示员工中的最高工资和平均工资mysql> select deptno, max(sal) 最高工资, avg(sal) 平均工资 from emp group by deptno; # 按 group by 后的列（也就是部门）来分组显示员工中的最高工资和平均工资mysql> select deptno, job, min(sal) 最低工资, avg(sal) 平均工资 from emp group by deptno, job;  # 显示每个部分的的每种岗位的平均工资和最低工资mysql> select ename, deptno, job, min(sal) 最低工资, avg(sal) 平均工资 from emp group by deptno, job;  # 报错，ename 不能以压缩聚合（重复的列数据进行压缩）方式出现# 一般只有聚合函数和分组子句出现的列依据可以出现在 select 后面，其他的均会报错

另外补充一个和 WHERE 类似的语法 HAVING，该语法可以筛选聚合结果，经常和聚合语法搭配使用。

# 尝试使用 having 来筛选聚合结果mysql> select deptno, avg(sal) 'deptavg' from emp group by deptno haning deptavg select current_date();+----------------+| current_date() |+----------------+| 2024-02-25     |+----------------+1 row in set (0.00 sec)mysql> select current_time();+----------------+| current_time() |+----------------+| 18:30:50       |+----------------+1 row in set (0.00 sec)mysql> select current_timestamp();+---------------------+| current_timestamp() |+---------------------+| 2024-02-25 18:31:17 |+---------------------+1 row in set (0.00 sec)mysql> select current_date();+----------------+mysql> select date('1949-10-01 00:00:00');+-----------------------------+| date('1949-10-01 00:00:00') |+-----------------------------+| 1949-10-01                  |+-----------------------------+1 row in set (0.00 sec)# (2)加减时间mysql> select date_add('1949-10-01 00:00:00', interval 10 day); +--------------------------------------------------+| date_add('1949-10-01 00:00:00', interval 10 day) |+--------------------------------------------------+| 1949-10-11 00:00:00                              |+--------------------------------------------------+1 row in set (0.00 sec)mysql> select date_add('1949-10-01 00:00:00', interval 10 second);+-----------------------------------------------------+| date_add('1949-10-01 00:00:00', interval 10 second) |+-----------------------------------------------------+| 1949-10-01 00:00:10                                 |+-----------------------------------------------------+1 row in set (0.00 sec)mysql> select date_sub('1949-10-01 00:00:00', interval 100 minute);+------------------------------------------------------+| date_sub('1949-10-01 00:00:00', interval 100 minute) |+------------------------------------------------------+| 1949-09-30 22:20:00                                  |+------------------------------------------------------+1 row in set (0.00 sec)# (3)时间差值mysql> select datediff(now(), '1949-10-01 00:00:00');+----------------------------------------+| datediff(now(), '1949-10-01 00:00:00') |+----------------------------------------+|                                  27175 |+----------------------------------------+1 row in set (0.00 sec)

# 尝试使用日期函数(2)mysql> create table tmp(id int primary key auto_increment, birthday date);Query OK, 0 rows affected (0.03 sec)mysql> show create table tmp\G*************************** 1. row ***************************       Table: tmpCreate Table: CREATE TABLE `tmp` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `birthday` date DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> insert into tmp (birthday) value ('2000-02-04');Query OK, 1 row affected (0.00 sec)mysql> insert into tmp (birthday) value ('2002-03-04');Query OK, 1 row affected (0.00 sec)mysql> insert into tmp (birthday) value ('1998-09-01');Query OK, 1 row affected (0.00 sec)mysql> insert into tmp (birthday) value (current_date());Query OK, 1 row affected (0.00 sec)mysql> select * from tmp;+----+------------+| id | birthday   |+----+------------+|  1 | 2000-02-04 ||  2 | 2002-03-04 ||  3 | 1998-09-01 ||  4 | 2024-02-25 |+----+------------+4 rows in set (0.00 sec)# 下面结果说明无论是时间、日期、时间戳，其背后都是同一个时间格式，只是返回的显示结果有所不同而已（类似隐式转化），当然，这种操作在当前版本是成立的mysql> insert into tmp (birthday) value (current_time());Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert into tmp (birthday) value (current_timestamp());Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from tmp;+----+------------+| id | birthday   |+----+------------+|  1 | 2000-02-04 ||  2 | 2002-03-04 ||  3 | 1998-09-01 ||  4 | 2024-02-25 ||  5 | 2024-02-25 ||  6 | 2024-02-25 |+----+------------+6 rows in set (0.00 sec)

3.字符函数

函数	说明
charset(str)	返回字符串 str 的字符集
concat(string1, string2, ...)	连接两个或多个字符串
instr(string, substring)	如果 substring 在 string 中存在，则返回其位置；如果不存在，返回 0
ucase(string)	将字符串 string 转换成大写
lcase(string)	将字符串 string 转换成小写
left(string, length)	从字符串 string 的左边开始，取 length 个字符
right(string, length)	从字符串 string 的右边开始，取 length 个字符
length(string)	返回字符串 string 的长度（长度的单位是字节而不是字符）
replace(str, search_str, replace_str)	在字符串 str 中，用 replace_str 替换所有的 search_str
strcmp(string1, string2)	逐字符比较两个字符串 string1 和 string2 的大小
substring(str, position [, length])	从字符串 str 的 position 位置开始，取 length 个字符
rtrim(string)	去除字符串 string 末尾的空格
ltrim(string)	去除字符串 string 开头的空格
trim(string)	去除字符串 string 开头和末尾的空格

# 尝试使用字符函数mysql> select chinese, math, english, chinese+math+english from exam_result;+---------+------+---------+----------------------+| chinese | math | english | chinese+math+english |+---------+------+---------+----------------------+|     134 |  196 |     112 |                  442 ||     174 |  216 |     154 |                  544 ||     176 |  256 |     180 |                  612 ||     164 |  228 |     134 |                  526 ||     110 |  170 |      90 |                  370 ||     140 |  146 |     156 |                  442 ||     150 |  130 |      60 |                  340 ||    NULL |  196 |     116 |                 NULL |+---------+------+---------+----------------------+8 rows in set (0.00 sec)mysql> select concat(chinese, math, english, chinese+math+english) from exam_result;+------------------------------------------------------+| concat(chinese, math, english, chinese+math+english) |+------------------------------------------------------+| 134196112442                                         || 174216154544                                         || 176256180612                                         || 164228134526                                         || 11017090370                                          || 140146156442                                         || 15013060340                                          || NULL  |+------------------------------------------------------+8 rows in set (0.00 sec)mysql> select concat('计算总分', chinese, '+', math, '+',english, '=', chinese+math+english) from exam_result\G*************************** 1. row ***************************concat('计算总分', chinese, '+', math, '+',english, '=', chinese+math+english): 计算总分134+196+112=442*************************** 2. row ***************************concat('计算总分', chinese, '+', math, '+',english, '=', chinese+math+english): 计算总分174+216+154=544*************************** 3. row ***************************concat('计算总分', chinese, '+', math, '+',english, '=', chinese+math+english): 计算总分176+256+180=612*************************** 4. row ***************************concat('计算总分', chinese, '+', math, '+',english, '=', chinese+math+english): 计算总分164+228+134=526*************************** 5. row ***************************concat('计算总分', chinese, '+', math, '+',english, '=', chinese+math+english): 计算总分110+170+90=370*************************** 6. row ***************************concat('计算总分', chinese, '+', math, '+',english, '=', chinese+math+english): 计算总分140+146+156=442*************************** 7. row ***************************concat('计算总分', chinese, '+', math, '+',english, '=', chinese+math+english): 计算总分150+130+60=340*************************** 8. row ***************************concat('计算总分', chinese, '+', math, '+',english, '=', chinese+math+english): NULL8 rows in set (0.01 sec)# 替换表中字符mysql> select * from exam_result;+----+-------+---------+------+---------+| id | name  | chinese | math | english |+----+-------+---------+------+---------+|  1 | limou |     134 |  196 |     112 ||  2 | swk   |     174 |  216 |     154 ||  3 | zwn   |     176 |  256 |     180 ||  4 | cmd   |     164 |  228 |     134 ||  5 | lxd   |     110 |  170 |      90 ||  6 | sq    |     140 |  146 |     156 ||  7 | sgm   |     150 |  130 |      60 ||  8 | dimou |    NULL |  196 |     116 |+----+-------+---------+------+---------+8 rows in set (0.00 sec)mysql> select replace(name, 's', 'S') from exam_result;+-------------------------+| replace(name, 's', 'S') |+-------------------------+| limou                   || Swk                     || zwn                     || cmd                     || lxd                     || Sq                      || Sgm                     || dimou                   |+-------------------------+8 rows in set (0.00 sec)mysql> select * from exam_result;+----+-------+---------+------+---------+| id | name  | chinese | math | english |+----+-------+---------+------+---------+|  1 | limou |     134 |  196 |     112 ||  2 | swk   |     174 |  216 |     154 ||  3 | zwn   |     176 |  256 |     180 ||  4 | cmd   |     164 |  228 |     134 ||  5 | lxd   |     110 |  170 |      90 ||  6 | sq    |     140 |  146 |     156 ||  7 | sgm   |     150 |  130 |      60 ||  8 | dimou |    NULL |  196 |     116 |+----+-------+---------+------+---------+8 rows in set (0.00 sec)mysql> select concat(ucase(substring(name, 1, 1)), substring(name, 2)) from exam_result;+----------------------------------------------------------+| concat(ucase(substring(name, 1, 1)), substring(name, 2)) |+----------------------------------------------------------+| Limou     || Swk       || Zwn       || Cmd       || Lxd       || Sq        || Sgm       || Dimou     |+----------------------------------------------------------+8 rows in set (0.00 sec)

4.数学函数

函数	说明
abs(number)	返回 number 的绝对值
bin(decimal_number)	将十进制数 decimal_number 转换为二进制。
hex(decimalNumber)	将十进制数 decimal_number 转换成十六进制
conv(number, from_base, to_base)	将数字 number 从 from_base 进制转换到 to_base 进制。
ceiling(number)	将 number 向上（+∞）取整到最近的整数
floor(number)	将 number 向下（-∞）取整到最近的整数
format(number, decimal_places)	格式化数字 number，保留 decimal_places 个指定的小数位数
rand()	返回一个随机浮点数，范围是 [0.0, 1.0)
mod(number, denominator)	返回 number 除以 denominator 的余数

这几个函数也比较简单，您自己验证一下即可。

5.其他函数

函数名	描述
user()	查询当前用户
database()	显示当前正在使用的数据库
md5(str)	对一个字符串进行 MD5 摘要，摘要后统一得到一个 32 位字符串，可以用来加密密码，如果需要就行密码比对，都需要经过 md5() 的最终结果进行比较
password()	MySQL 数据库使用该函数对用户密码进行加密，保证密码不是明文存储在数据库中，防止信息被盗取和泄漏（但是 password() 函数在最新的 MySQL 版本中已被弃用）
ifnull(val1, val2)	如果 val1 为 null，返回 val2，否则返回 val1 的值

补充：这里还有个小细节，如果使用插入语句中带有 password 的字眼，就无法通过上下键找不到历史上该操作的语句（包括 password() 函数的使用历史）。