MySQL 内置函数详解：聚合、日期、字符串及数学运算

1、统计班级共有多少同学

SELECT COUNT(*) FROM students;

2、统计班级有多少 qq 号

SELECT COUNT(qq) FROM students;

对比上表可以看到 COUNT 函数，对于 NULL 值，不做统计。

3、统计本次考试的数学成绩分数个数

SELECT COUNT(math) FROM exam_result;

对比上表可以看到 COUNT 函数，对于重复值，不做统计。

4、统计数学成绩不及格人数

SELECT COUNT(math) FROM exam_result WHERE math < 60;

COUNT 函数可以配合其他语句使用。

5、统计平均总分

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

6、返回英语最高分

SELECT MAX(english) FROM exam_result;

7、返回 > 70 分以上的数学最低分

SELECT MIN(math) FROM exam_result WHERE math > 70;

二、日期函数

1、获取当前年月日

SELECT CURRENT_DATE();

2、获取当前时分秒

SELECT CURTIME();

3、获取时间戳

SELECT NOW();

4、在时间中提取日期部分

SELECT DATE(NOW());

5、在日期的基础上加上日期

SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 10 DAY);

INTERVAL 后可以根据需要使用不同单位（年、月、日、分、秒）

6、在日期的基础上减去日期

SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY);

7、计算两个日期之间相差多少天

SELECT DATEDIFF(CURRENT_DATE(), '1949-10-01');

8、创建留言表并查询

CREATE TABLE msg (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(30) NOT NULL,
    sendtime DATETIME
);

INSERT INTO msg(content, sendtime) VALUES ('hello1', NOW());
INSERT INTO msg(content, sendtime) VALUES ('hello2', NOW());
SELECT * FROM msg;

显示所有留言信息，发布日期只显示日期，不用显示时间：

SELECT content, DATE(sendtime) FROM msg;

查询在 1 分钟内发布的帖子：

SELECT * FROM msg WHERE TIMESTAMPDIFF(MINUTE, sendtime, NOW()) <= 1;

三、字符串函数

1、查看字符串的字符集

SELECT CHARSET('test');

2、格式化输出

SELECT CONCAT(name, '的语文分：', chinese, ', 数学分：', math, ', 英语分：', english) FROM exam_result LIMIT 1;

3、在字符串中查找字符串

SELECT INSTR('Hello World', 'World');

在 string 中查找字符串 substring 出现的位置，找到返回下标（从 1 开始），未找到返回 0。

4、字符串转为大写

SELECT UCASE('hello');

5、字符串转为小写

SELECT LCASE('HELLO');

6、从字符串左端提取 len 个字符

SELECT LEFT('Hello World', 5);

7、从字符串右端提取 len 个字符

SELECT RIGHT('Hello World', 5);

8、求字符串占用的字节数

SELECT LENGTH('你好');

LENGTH() 函数在 MySQL 中计算的是字符串的字节长度，而不是字符个数，当前所使用的字符集汉字占三个字节。

9、在字符串中进行替换

SELECT REPLACE('Hello World', 'World', 'MySQL');

在 substring 中查找 string，并将其替换为 str。若未找到则不做处理。

10、字符串截取

SELECT SUBSTRING('Hello World', 7, 5);

从字符串 string 的 pos 处开始，向后截取 len 个字符。

11、去除空格

• TRIM：去除字符串两端空格
• LTRIM：去除字符串最左边的空格
• RTRIM：去除字符串右边的空格

SELECT TRIM('  Hello  '), LTRIM('  Hello'), RTRIM('Hello  ');

在保存用户信息数据时，一般先对数据执行去除空格操作。由于网络传输过程可能引入不可见空字符，若直接存储含此类字符的数据，后续用户登录时，比如输入密码因存在空格匹配不上，会引发登录失败问题，且排查难度极大。所以，要先过滤掉字符串中的空格，再将处理后的数据存入数据库，以此规避因隐性空格导致的登录故障。

四、数学函数

1、ABS 取绝对值

SELECT ABS(-10);

2、BIN 转二进制

SELECT BIN(15);

可以看到在对小数进行二进制转换时，会将小数进行向下取整后再操作。

3、HEX 转十六进制

SELECT HEX(15);

4、CONV 进制转换

SELECT CONV(15, 10, 2);

将数字 N，从 from_base 进制转换成 to_base 进制。

5、FORMAT 格式化，保留小数

SELECT FORMAT(1234.567, 2);

将 N 保留 D 位小数，处理小数部分遵循四舍五入，若小数部分不够就补 0。

6、MOD 取模

SELECT MOD(10, 3);

MOD 返回 x 对 y 取模的值。

7、RAND 生成随机数

SELECT RAND();

生成的数是从 0.0 ~ 1.0，若想要生成指定范围的我们就直接乘以 10^n 即可实现。

8、CEILING 向上取整

SELECT CEILING(1.5);

9、FLOOR 向下取整

SELECT FLOOR(1.5);

五、其他函数

1、查看当前用户

SELECT USER();

获取当前连接到 MySQL 服务器的用户信息，返回结果的格式为 用户名@主机名。

2、DATABASE 查看当前数据库

SELECT DATABASE();

返回当前会话中使用的数据库名称。

3、MD5 加密

在实际开发中，密码通常不会以明文形式直接存储在数据库中，而 MD5 哈希算法是常用的密码加密方案之一。其核心作用是将原始密码通过加密计算转换为一段固定长度（32 位）的哈希字符串，从而避免明文密码在存储或传输过程中泄露的风险。

SELECT MD5('password');

这种加密方式缺点较多，实际生产中建议使用更安全的加密方案。

4、IFNULL(val1, val2)

当 val1 为 NULL 时返回 val2，否则返回 val1 本身。

SELECT IFNULL(NULL, 'default');