Re：Mysql数据库基础篇（七）：从内置函数到进阶架构思维：一文吃透MySQL常用函数、GROUP BY本质与WHERE/HAVING区别，领悟“MySQL一切皆表”的底层逻辑- 惊觉

在这里插入图片描述

◆ 博主名称：小此方-CSDN博客 大家好，欢迎来到小此方的博客。

⭐️Linux系列个人专栏：【主题曲】Mysql

⭐️此方的GitHub： github_此方

⭐️ Re系列专栏：我们思考 (Rethink) · 我们重建 (Rebuild) · 我们记录 (Record)

概要&序論

Hello大家好，我是此方，讲完了查询，我们进入一个新的章节，Mysql的函数。Mysql中也有很多函数，使用他们可以对我们的操作大有益助。

一、MySQL内置函数之聚合函数

在数据库的日常查询中，我们经常需要对一组数据进行统计计算，比如求总分、算平均分、找最高分等等。MySQL 提供了一类专门做这种统计操作的函数，我们称之为聚合函数。它们的作用是将多行数据合并计算，最终返回一个单行的结果。

常见的聚合函数主要有以下五个：

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

下面我们结合具体的案例来逐一了解它们的使用方法和细节避坑点。

1.1 COUNT 统计数量

COUNT 函数用来统计数量。我们有一张 students 表，如果想统计班级共有多少同学，可以使用 COUNT(*) 或者 COUNT(1)。

-- 使用 * 做统计
SELECT COUNT(*) FROM students;

-- 使用表达式做统计
SELECT COUNT(1) FROM students;

这两种写法都能得到相同的结果（当前表中有4条数据），而且它们都不受 NULL 影响，统计的是总行数。

1.1.1 COUNT 遇到 NULL 值的陷阱

如果我们要统计班级收集的 qq 号有多少，情况就不一样了。如果我们直接使用 COUNT(qq)，需要特别注意：NULL 值不会计入结果。

SELECT COUNT(qq) FROM students;

假设表里有4个同学，但只有1个同学填了 qq 号，其他三个是 NULL，那么返回的结果就是 1。这说明如果 COUNT 里面写的是具体的列名，它会忽略该列为 NULL 的行。

1.1.2 COUNT 结合 DISTINCT 去重

如果我们想统计本次考试的数学成绩分数个数，直接 COUNT(math) 统计的是所有人的成绩数量（比如6个）。但如果有多个人考了相同的分数，而我们想知道一共有几种不同的分数，就需要配合 DISTINCT 关键字进行去重。

-- 统计的是全部成绩
SELECT COUNT(math) FROM exam_result;

-- 统计的是去重成绩数量
SELECT COUNT(DISTINCT math) FROM exam_result;

1.2 SUM 求和

SUM 函数用来对数值列求和。比如统计数学成绩总分。

SELECT SUM(math) FROM exam_result;

这里有一个细节需要注意：如果统计的结果集为空（没有任何数据满足条件），SUM 函数的返回值是 NULL，而不是 0。

-- 不及格 < 60 的总分，如果没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

1.3 AVG 求平均值

AVG 函数用来计算平均值。它不仅可以对单个字段求平均，也可以对表达式求平均。比如我们要统计所有同学的平均总分，可以把各科成绩相加作为参数传给 AVG。

SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

1.4 MAX 和 MIN 求极值

MAX 和 MIN 分别用来求最大值和最小值。比如返回英语最高分和返回大于 70 分以上的数学最低分。

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

在使用 MAX 和 MIN 时，同样可以结合 WHERE 子句先进行条件过滤，然后在过滤后的结果集中寻找极值。

二、日期函数

在处理业务数据时，时间的记录和计算是非常常见的需求。MySQL 提供了一系列强大的日期和时间函数，帮助我们轻松获取当前时间、计算日期差以及进行日期的加减运算。

常见的日期函数及说明如下表：

函数名称	描述
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回datetime参数的日期部分
date_add(date, interval d_value_type)	在date中添加日期或时间，interval后的数值单位可以是：year, minute, second, day
date_sub(date, interval d_value_type)	在date中减去日期或时间，interval后的数值单位可以是：year, minute, second, day
datediff(date1, date2)	两个日期的差，单位是天
now()	当前日期时间

2.1 基础获取与运算

我们可以直接通过函数获取当前的年月日、时分秒以及完整的时间戳。

-- 获得年月日
select current_date();

-- 获得时分秒
select current_time();

-- 获得时间戳
select current_timestamp();

对于日期的加减运算，date_add 和 date_sub 是最常用的工具。比如在基础日期上加10天或减去2天。

-- 在日期的基础上加日期
select date_add('2017-10-28', interval 10 day);

-- 在日期的基础上减去时间
select date_sub('2017-10-1', interval 2 day);

如果我们需要计算两个日期之间相差多少天，可以使用 datediff 函数。

select datediff('2017-10-10', '2016-9-1');

2.2 综合案例：留言表查询

我们通过创建一个留言表来综合运用这些日期函数。表结构包含主键、留言内容和发送时间。

create table msg (
    id int primary key auto_increment,
    content varchar(30) not null,
    sendtime datetime
);

在插入数据时，我们可以直接调用 now() 函数来记录当前发送时间。

insert into msg(content, sendtime) values('hello1', now());
insert into msg(content, sendtime) values('hello2', now());

实际业务展示中，我们往往不需要精确到秒。如果只想显示留言的日期部分，可以用 date() 函数包裹字段。

select content, date(sendtime) from msg;

最经典的场景是查询在2分钟内发布的帖子。我们可以利用 date_add 将发送时间加上2分钟，如果这个时间大于当前时间 now()，就说明是在2分钟内发布的。

select * from msg where date_add(sendtime, interval 2 minute) > now();

三、字符串函数

字符串处理在数据库操作中极为频繁。MySQL 内置了丰富的字符串函数，涵盖了拼接、截取、替换、大小写转换等操作。

常用字符串函数及说明如下：

函数	说明
charset(str)	返回字符串字符集
concat(string2 [, …])	连接字符串
instr(string, substring)	返回substring在string中出现的位置，没有返回0
ucase(string2)	转换成大写
lcase(string2)	转换成小写
left(string2, length)	从string2中的左边起取length个字符
length(string)	string的长度（以字节为单位）
replace(str, search_str, replace_str)	在str中用replace_str替换search_str
strcmp(string1, string2)	逐字符比较两字符串大小
substring(str, position [, length])	从str的position开始，取length个字符
ltrim(string), rtrim(string), trim(string)	去除前空格、后空格或前后空格

3.1 基础用法与格式化输出

比如我们要求显示 exam_result 表中的信息，并且按照特定格式拼接出来：“XXX的语文是XXX分，数学XXX分，英语XXX分”。这时候就可以配合 concat 函数。

select concat(name, '的语文是', chinese, '分，数学是', math, '分') as '分数' from student;

如果需要求学生姓名占用的字节数，可以使用 length 函数。

select length(name), name from student;

这里需要特别注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字节字符则算作一个字节。比如：字母、数字算作一个字节，中文表示多个字节数（与字符集编码有关，比如 utf8 下一个中文占3个字节）。

3.2 替换、截取与大小写转换

我们以 EMP 表为例进行演示。要求将 EMP 表中所有名字中有 ‘s’ 的替换成 ‘上海’。

select replace(ename, 's', '上海'), ename from EMP;

截取 EMP 表中 ename 字段的第二个到第三个字符。

select substring(ename, 2, 2), ename from EMP;

一个比较复杂的组合用法：以首字母小写的方式显示所有员工的姓名。这里先截取出首字母，用 lcase 转成小写，再拼接上后面的字符串。

select concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) from EMP;

四、数学函数

数学函数主要用于数值的计算和格式化处理。

常用数学函数及说明如下：

函数名称	描述
abs(number)	绝对值函数
bin(decimal_number)	十进制转换二进制
hex(decimalNumber)	转换成十六进制
conv(number, from_base, to_base)	进制转换
ceiling(number)	向上去整
floor(number)	向下去整
format(number, decimal_places)	格式化，保留小数位数
rand()	返回随机浮点数，范围[0.0, 1.0)
mod(number, denominator)	取模，求余

我们可以直接 SELECT 调用这些函数进行测试。

-- 绝对值
select abs(-100.2);

-- 向上取整
select ceiling(23.04);

-- 向下取整
select floor(23.7);

-- 保留2位小数位数（小数四舍五入）
select format(12.3456, 2);

-- 产生随机数
select rand();

五、其他常用函数

除了上述分类，MySQL 还提供了一些非常实用的系统级函数和数据处理函数。

使用 user() 查询当前用户。
使用 md5(str) 对一个字符串进行 md5 摘要，摘要后得到一个 32 位字符串。
使用 database() 显示当前正在使用的数据库。
使用 password() 函数，MySQL 数据库使用该函数对用户加密。

此外，还有一个至关重要的处理空值的函数：ifnull(val1, val2)。如果 val1 为 null，返回 val2，否则返回 val1 的值。

-- 返回 abc
select ifnull('abc', '123');

-- 返回 123
select ifnull(null, '123');

六、分组聚合与条件筛选的深度思考

6.1 关于分组的本质理解

分组的根本目的，是为了在分组之后方便进行聚合统计。我们不要仅仅把 GROUP BY 看作是一个语法，它的逻辑本质是把一张表按照指定的条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计。

在分组时，指定的列名实际上是用该列的不同行数据来进行分组的。这就意味着，在同一个分组内，这个分组条件的值一定是相同的。这也是为什么当我们使用 GROUP BY 时，SELECT 中出现的非聚合列必须包含在 GROUP BY 子句中，否则就会触发 ONLY_FULL_GROUP_BY 报错，因为系统不知道该如何压缩那些组内不唯一的数据。

6.2 WHERE 与 HAVING 的核心区别

这两者最容易混淆，图片中给出了非常清晰的判断标准：条件筛选的阶段是不同的。

WHERE 作用于具体的、磁盘上的原始数据列，它在数据分组之前就完成了过滤。
HAVING 作用于分组聚合之后的结果，它是对统计出来的数据结果进行二次条件筛选。

只有深刻理解了 SQL 语句各个子句的执行先后顺序，才能明白为什么在有些场景下不能用 WHERE 去过滤聚合函数，而必须交给 HAVING 来做。

6.3 引申思考：“MySQL一切皆表”

在学习各种复杂的查询语句时，我们要建立一个宏大的认知：不要单纯地认为，只有磁盘上表结构导入到 MySQL 中真实存在的表，才叫做表。

无论是通过 WHERE 中间筛选出来的结果集，还是通过 GROUP BY 分组后得到的聚合视图，甚至是我们最终查询返回的数据，只要它是结构化输出的，在我们看来，逻辑上全都可以视为一张“表”。这就是所谓的“MySQL一切皆表”。

有了这个思维，未来只要我们能处理好多表连接和单表的 CRUD 操作，所有的 SQL 业务场景（无论多复杂），我们都可以用统一的方式去拆解和进行处理。

好的本文内容到此结束，我是此方，我们下期再见。

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/Z2314246476/article/details/169141700

Re：Mysql数据库基础篇（七）：从内置函数到进阶架构思维：一文吃透MySQL常用函数、GROUP BY本质与WHERE/HAVING区别，领悟“MySQL一切皆表”的底层逻辑

文章目录