3.1 常用内置函数

📖 本节导读

• 认识 MySQL 的「内置函数」：像计算器上的功能键一样，拿来就能用，帮我们加工数据。
• 掌握最常用的字符串函数：CONCAT、LENGTH / CHAR_LENGTH、UPPER / LOWER、SUBSTRING、TRIM、REPLACE。
• 掌握数值函数（ROUND、CEIL、FLOOR、ABS、MOD）和日期函数（NOW、CURDATE、YEAR/MONTH/DAY、DATE_ADD、DATEDIFF、DATE_FORMAT）。
• 学会用 IFNULL、IF、CASE WHEN 做「流程控制」，比如把分数自动转换成"优秀 / 及格 / 不及格"。

什么是内置函数

函数可以理解成一台"数据加工机"：你把原料（数据）塞进去，它按固定的规则加工，然后吐出结果。

比如 UPPER('hello')，就是把 'hello' 塞进"转大写"这台机器，吐出来 'HELLO'。

「内置」的意思是：这些函数是 MySQL 自带的，不用安装、不用自己写，直接在 SQL 里调用即可。调用格式统一是：

函数名(参数1, 参数2, ...)

本节所有示例都基于我们的 school 库（class、student、course、score 四张表）。开始前先切换数据库：

USE school;

一、字符串函数

1. CONCAT —— 拼接字符串

CONCAT 就是"胶水"，把多个字符串粘成一个。

SELECT CONCAT(name, '（', gender, '生）') AS 介绍 FROM student;

+------------------+
| 介绍             |
+------------------+
| 张三（男生）     |
| 李四（男生）     |
| 王小红（女生）   |
| 赵敏（女生）     |
| 陈刚（男生）     |
+------------------+
5 rows in set (0.00 sec)

💡 只要有任何一个参数是 NULL，CONCAT 的结果就是 NULL。这一点后面讲 NULL 时会反复提到。

2. LENGTH 与 CHAR_LENGTH —— 两种"长度"

这两个函数是新手最容易混的一对：

• CHAR_LENGTH(str)：返回字符个数。一个汉字算 1 个字符。
• LENGTH(str)：返回字节数。在常用的 utf8mb4 编码下，一个汉字占 3 个字节。

SELECT name, CHAR_LENGTH(name) AS 字符数, LENGTH(name) AS 字节数 FROM student;

+-----------+-----------+-----------+
| name      | 字符数    | 字节数    |
+-----------+-----------+-----------+
| 张三      |         2 |         6 |
| 李四      |         2 |         6 |
| 王小红    |         3 |         9 |
| 赵敏      |         2 |         6 |
| 陈刚      |         2 |         6 |
+-----------+-----------+-----------+
5 rows in set (0.00 sec)

结论：想知道"几个字"，用 CHAR_LENGTH；LENGTH 算的是存储占用的字节。 纯英文时两者相等（一个英文字母占 1 字节），有汉字时就不一样了。

3. UPPER / LOWER —— 大小写转换

SELECT UPPER('hello mysql') AS 转大写, LOWER('HELLO MySQL') AS 转小写;

+-------------+-------------+
| 转大写      | 转小写      |
+-------------+-------------+
| HELLO MYSQL | hello mysql |
+-------------+-------------+
1 row in set (0.00 sec)

汉字没有大小写，这两个函数对汉字不起作用，原样返回。

4. SUBSTRING —— 截取子串

格式：SUBSTRING(str, 起始位置, 长度)。注意：位置从 1 开始数，不是从 0；按"字符"数，不按字节。省略"长度"就一直截到末尾。

SELECT name,
       SUBSTRING(name, 1, 1) AS 姓,
       SUBSTRING(name, 2)    AS 名
FROM student;

+-----------+------+--------+
| name      | 姓   | 名     |
+-----------+------+--------+
| 张三      | 张   | 三     |
| 李四      | 李   | 四     |
| 王小红    | 王   | 小红   |
| 赵敏      | 赵   | 敏     |
| 陈刚      | 陈   | 刚     |
+-----------+------+--------+
5 rows in set (0.00 sec)

5. TRIM —— 去掉两端空格

用户输入的数据经常带着多余空格，TRIM 像"修剪机"，把字符串两端的空格剪掉（中间的不管）。

SELECT TRIM('   张三   ') AS 修剪后, CHAR_LENGTH(TRIM('   张三   ')) AS 长度;

+-----------+--------+
| 修剪后    | 长度   |
+-----------+--------+
| 张三      |      2 |
+-----------+--------+
1 row in set (0.00 sec)

6. REPLACE —— 替换

格式：REPLACE(str, 要找的内容, 换成的内容)，会替换所有出现的位置。

SELECT teacher, REPLACE(teacher, '老师', '先生') AS 改称呼 FROM course;

+-----------+-----------+
| teacher   | 改称呼    |
+-----------+-----------+
| 王老师    | 王先生    |
| 李老师    | 李先生    |
| 张老师    | 张先生    |
+-----------+-----------+
3 rows in set (0.00 sec)

二、数值函数

ROUND / CEIL / FLOOR —— 三种"取整"

• ROUND(x, d)：四舍五入，保留 d 位小数（d 省略则取整数）。
• CEIL(x)：向上取整（天花板，ceiling），只要有小数就进一位。
• FLOOR(x)：向下取整（地板），直接砍掉小数部分往小取。

SELECT ROUND(79.75)     AS r1,
       ROUND(79.75, 1)  AS r2,
       CEIL(79.1)       AS c,
       FLOOR(79.9)      AS f;

+------+------+------+------+
| r1   | r2   | c    | f    |
+------+------+------+------+
|   80 | 79.8 |   80 |   79 |
+------+------+------+------+
1 row in set (0.00 sec)

ABS / MOD —— 绝对值与取余

SELECT ABS(-7) AS 绝对值, MOD(10, 3) AS 余数;

+-----------+--------+
| 绝对值    | 余数   |
+-----------+--------+
|         7 |      1 |
+-----------+--------+
1 row in set (0.00 sec)

MOD(10, 3) 就是 10 除以 3 的余数。常见用途：MOD(id, 2) = 0 可以筛出 id 为偶数的行。

SELECT id, name FROM student WHERE MOD(id, 2) = 0;

+----+--------+
| id | name   |
+----+--------+
|  2 | 李四   |
|  4 | 赵敏   |
+----+--------+
2 rows in set (0.00 sec)

三、日期函数

NOW / CURDATE —— 现在几点、今天几号

SELECT NOW() AS 当前时间, CURDATE() AS 今天;

+---------------------+------------+
| 当前时间            | 今天       |
+---------------------+------------+
| 2026-07-26 10:30:00 | 2026-07-26 |
+---------------------+------------+
1 row in set (0.00 sec)

这两个函数的结果随执行时间变化，你运行时看到的会是你自己的当前时间。

YEAR / MONTH / DAY —— 拆日期

从一个日期里分别取出年、月、日：

SELECT name, birthday,
       YEAR(birthday)  AS 年,
       MONTH(birthday) AS 月,
       DAY(birthday)   AS 日
FROM student;

+-----------+------------+------+------+------+
| name      | birthday   | 年   | 月   | 日   |
+-----------+------------+------+------+------+
| 张三      | 2005-03-15 | 2005 |    3 |   15 |
| 李四      | 2005-07-01 | 2005 |    7 |    1 |
| 王小红    | 2006-01-20 | 2006 |    1 |   20 |
| 赵敏      | 2005-11-08 | 2005 |   11 |    8 |
| 陈刚      | 2006-05-30 | 2006 |    5 |   30 |
+-----------+------------+------+------+------+
5 rows in set (0.00 sec)

顺手算个"周岁"（粗略版）：

SELECT name, YEAR(CURDATE()) - YEAR(birthday) AS 年龄 FROM student WHERE name = '张三';

+--------+--------+
| name   | 年龄   |
+--------+--------+
| 张三   |     21 |
+--------+--------+
1 row in set (0.00 sec)

DATE_ADD —— 日期加减

格式：DATE_ADD(日期, INTERVAL 数量 单位)，单位可以是 DAY、MONTH、YEAR 等。

SELECT name, birthday, DATE_ADD(birthday, INTERVAL 18 YEAR) AS 成年日期
FROM student WHERE name = '张三';

+--------+------------+------------+
| name   | birthday   | 成年日期   |
+--------+------------+------------+
| 张三   | 2005-03-15 | 2023-03-15 |
+--------+------------+------------+
1 row in set (0.00 sec)

想做减法，可以用负数：DATE_ADD('2026-07-26', INTERVAL -7 DAY) 得到 2026-07-19。

DATEDIFF —— 两个日期差几天

DATEDIFF(日期1, 日期2) = 日期1 − 日期2 的天数。比如陈刚比张三小多少天：

SELECT DATEDIFF('2006-05-30', '2005-03-15') AS 相差天数;

+--------------+
| 相差天数     |
+--------------+
|          441 |
+--------------+
1 row in set (0.00 sec)

DATE_FORMAT —— 把日期"化妆"成想要的样子

格式符就像填空模板，常用的有：

格式符	含义	示例结果
%Y	四位年份	2005
%m	两位月份	03
%d	两位日期	15
%H	小时（24 制）	18
%i	分钟	05
%s	秒	09

SELECT name, DATE_FORMAT(birthday, '%Y年%m月%d日') AS 生日 FROM student;

+-----------+------------------+
| name      | 生日             |
+-----------+------------------+
| 张三      | 2005年03月15日   |
| 李四      | 2005年07月01日   |
| 王小红    | 2006年01月20日   |
| 赵敏      | 2005年11月08日   |
| 陈刚      | 2006年05月30日   |
+-----------+------------------+
5 rows in set (0.00 sec)

⚠️ 注意分钟是 %i 不是 %m，%m 已经被"月份"占用了——这是最经典的写错点。

四、流程控制函数

1. IFNULL —— 给 NULL 一个"兜底值"

IFNULL(a, b)：a 不是 NULL 就返回 a，是 NULL 就返回 b。

我们的 score 表里，陈刚（student_id=5）的计算机成绩是 NULL（缺考没录入）。直接查很难看，可以兜底成 0：

SELECT id, student_id, course_id, IFNULL(score, 0) AS score
FROM score
WHERE course_id = 3;

+----+------------+-----------+-------+
| id | student_id | course_id | score |
+----+------------+-----------+-------+
|  3 |          1 |         3 |  88.0 |
|  8 |          3 |         3 |  70.0 |
| 10 |          4 |         3 |  81.0 |
| 12 |          5 |         3 |   0.0 |
+----+------------+-----------+-------+
4 rows in set (0.00 sec)

最后一行本来是 NULL，被 IFNULL 换成了 0.0。

2. IF —— 二选一

IF(条件, 值1, 值2)：条件成立返回值1，否则返回值2，类似"如果…就…否则…"。

SELECT student_id, score, IF(score >= 60, '及格', '不及格') AS 结果
FROM score
WHERE course_id = 1;

+------------+-------+-----------+
| student_id | score | 结果      |
+------------+-------+-----------+
|          1 |  90.0 | 及格      |
|          2 |  76.0 | 及格      |
|          3 |  95.0 | 及格      |
|          4 |  58.0 | 不及格    |
+------------+-------+-----------+
4 rows in set (0.00 sec)

3. CASE WHEN —— 多选一（分数转等级）

IF 只能二选一，条件多了就要用 CASE WHEN，像一串"如果…否则如果…否则…"：

SELECT student_id, course_id, score,
       CASE
           WHEN score >= 85 THEN '优秀'
           WHEN score >= 60 THEN '及格'
           ELSE '不及格'
       END AS 等级
FROM score;

+------------+-----------+-------+-----------+
| student_id | course_id | score | 等级      |
+------------+-----------+-------+-----------+
|          1 |         1 |  90.0 | 优秀      |
|          1 |         2 |  85.0 | 优秀      |
|          1 |         3 |  88.0 | 优秀      |
|          2 |         1 |  76.0 | 及格      |
|          2 |         2 |  92.0 | 优秀      |
|          3 |         1 |  95.0 | 优秀      |
|          3 |         2 |  60.0 | 及格      |
|          3 |         3 |  70.0 | 及格      |
|          4 |         1 |  58.0 | 不及格    |
|          4 |         3 |  81.0 | 及格      |
|          5 |         2 |  73.0 | 及格      |
|          5 |         3 |  NULL | 不及格    |
+------------+-----------+-------+-----------+
12 rows in set (0.00 sec)

注意最后一行：陈刚的计算机成绩是 NULL，却被判成了"不及格"！因为 NULL >= 85、NULL >= 60 的结果都是 NULL（不成立），于是掉进了 ELSE。更严谨的写法是先把 NULL 单独拦下来：

SELECT student_id, course_id, score,
       CASE
           WHEN score IS NULL THEN '缺考'
           WHEN score >= 85   THEN '优秀'
           WHEN score >= 60   THEN '及格'
           ELSE '不及格'
       END AS 等级
FROM score
WHERE student_id = 5;

+------------+-----------+-------+--------+
| student_id | course_id | score | 等级   |
+------------+-----------+-------+--------+
|          5 |         2 |  73.0 | 及格   |
|          5 |         3 |  NULL | 缺考   |
+------------+-----------+-------+--------+
2 rows in set (0.00 sec)

CASE WHEN 按顺序从上往下匹配，命中第一个成立的条件就停，所以要把"缺考"放在最前面。

⚠️ 新手常见坑

1. LENGTH 和 CHAR_LENGTH 混用：数汉字个数要用 CHAR_LENGTH，LENGTH 返回的是字节数（utf8mb4 下一个汉字 3 字节）。
2. SUBSTRING 从 1 开始数：很多编程语言从 0 开始，SQL 里第一个字符的位置是 1。
3. DATE_FORMAT 里 %m 是月份、%i 才是分钟，写错不会报错，只会得到莫名其妙的结果。
4. NULL 参与比较的结果还是 NULL：所以 CASE WHEN 里 NULL 会掉进 ELSE 分支，需要用 WHEN score IS NULL 提前拦截。
5. CONCAT 遇到 NULL 整体变 NULL：拼接可能为 NULL 的列时，先用 IFNULL 兜底，如 CONCAT('成绩：', IFNULL(score, '无'))。

📝 小结

• 函数是 MySQL 自带的数据加工机，格式为 函数名(参数, ...)，可以出现在 SELECT、WHERE 等位置。
• 字符串：CONCAT 拼接、CHAR_LENGTH 数字符、LENGTH 数字节、UPPER/LOWER 转大小写、SUBSTRING 截取（从 1 开始）、TRIM 去两端空格、REPLACE 替换。
• 数值：ROUND 四舍五入、CEIL 向上取整、FLOOR 向下取整、ABS 绝对值、MOD 取余。
• 日期：NOW/CURDATE 取当前时间、YEAR/MONTH/DAY 拆日期、DATE_ADD 加减、DATEDIFF 算天数差、DATE_FORMAT 按格式符输出。
• 流程控制：IFNULL 兜底 NULL、IF 二选一、CASE WHEN 多选一（注意 NULL 要单独处理、条件按顺序匹配）。

✍️ 练习题

1. 查询所有课程，输出形如"数学课由王老师授课"的一句话（列别名为"介绍"）。

参考答案

SELECT CONCAT(name, '课由', teacher, '授课') AS 介绍 FROM course;

输出：数学课由王老师授课、英语课由李老师授课、计算机课由张老师授课。

2. 查询姓名恰好是 3 个字的学生姓名。

参考答案

SELECT name FROM student WHERE CHAR_LENGTH(name) = 3;

只有"王小红"。注意不能用 LENGTH，否则汉字按字节算，3 个字是 9 字节。

3. 查询每个学生的姓名和出生月份，出生月份显示成"03月"这种两位数字格式。

参考答案

SELECT name, DATE_FORMAT(birthday, '%m月') AS 出生月份 FROM student;

张三 03月、李四 07月、王小红 01月、赵敏 11月、陈刚 05月。

4. 查询英语（course_id = 2）的所有成绩，并用 IF 标注是否达到 80 分（输出"达标"/"未达标"）。

参考答案

SELECT student_id, score, IF(score >= 80, '达标', '未达标') AS 是否达标
FROM score WHERE course_id = 2;

张三 85.0 达标、李四 92.0 达标、王小红 60.0 未达标、陈刚 73.0 未达标。

5. 用 CASE WHEN 查询计算机（course_id = 3）成绩等级：NULL 显示"缺考"，>= 80 显示"良好"，其余显示"继续努力"。

参考答案

SELECT student_id, score,
       CASE
           WHEN score IS NULL THEN '缺考'
           WHEN score >= 80   THEN '良好'
           ELSE '继续努力'
       END AS 等级
FROM score WHERE course_id = 3;

张三 88.0 良好、王小红 70.0 继续努力、赵敏 81.0 良好、陈刚 NULL 缺考。