场景引入
做数据分析时，原始数据很少是"拿来就能用"的。日期字段一般是完整的 DATETIME，但你只需要月份；名字字段可能是 " 张三 "（带空格），或者 "Zhang San" 需要统一小写。你需要的是一套数据清洗与格式化的工具，这就是日期函数和字符串函数。
如果说 JOIN 和窗口函数是 SQL 的"大杀器"，那日期与字符串函数就是日常用得最频繁的"瑞士军刀"。
一、日期函数速览
1. 提取日期分量
从日期中提取年、月、日、季度、周等。
SELECT
NOW() AS 当前时间,
CURDATE() AS 当前日期,
YEAR('2026-07-23') AS 年份,
MONTH('2026-07-23') AS 月份,
DAY('2026-07-23') AS 日,
QUARTER('2026-07-23') AS 季度,
WEEKDAY('2026-07-23') AS 周几索引, -- 0=周一, 6=周日
DAYNAME('2026-07-23') AS 星期名称,
DAYOFYEAR('2026-07-23') AS 年中第几天;
执行结果：
| 当前时间 | 当前日期 | 年份 | 月份 | 日 | 季度 | 周几索引 | 星期名称 | 年中第几天 |
|---|---|---|---|---|---|---|---|---|
| 2026-07-23 20:59:00 | 2026-07-23 | 2026 | 7 | 23 | 3 | 3 | Thursday | 204 |
2. 日期计算
SELECT
DATE_ADD('2026-07-23', INTERVAL 30 DAY) AS 加30天,
DATE_SUB('2026-07-23', INTERVAL 1 MONTH) AS 减1个月,
DATEDIFF('2026-12-31', '2026-07-23') AS 相差天数,
TIMESTAMPDIFF(MONTH, '2026-01-01', '2026-07-23') AS 相差月数;
执行结果：
| 加30天 | 减1个月 | 相差天数 | 相差月数 |
|---|---|---|---|
| 2026-08-22 | 2026-06-23 | 161 | 6 |
常用 INTERVAL 单位： DAY, MONTH, YEAR, HOUR, MINUTE, WEEK, QUARTER
3. 日期格式化 — DATE_FORMAT
这是最实用的日期函数，没有之一。
SELECT
DATE_FORMAT('2026-07-23 14:30:00', '%Y-%m-%d') AS 日期,
DATE_FORMAT('2026-07-23 14:30:00', '%Y年%m月%d日') AS 中文日期,
DATE_FORMAT('2026-07-23 14:30:00', '%H:%i:%s') AS 时间,
DATE_FORMAT('2026-07-23 14:30:00', '%Y-%m') AS 月份聚合键,
DATE_FORMAT('2026-07-23 14:30:00', '%W') AS 星期几;
执行结果：
| 日期 | 中文日期 | 时间 | 月份聚合键 | 星期几 |
|---|---|---|---|---|
| 2026-07-23 | 2026年07月23日 | 14:30:00 | 2026-07 | Thursday |
常用格式化符一览：
| 符号 | 含义 | 示例 |
|---|---|---|
%Y | 4位年份 | 2026 |
%y | 2位年份 | 26 |
%m | 2位月份 | 07 |
%c | 月份(无前导0) | 7 |
%d | 2位日 | 23 |
%e | 日(无前导0) | 23 |
%H | 24小时(00-23) | 14 |
%i | 分钟 | 30 |
%s | 秒 | 00 |
%W | 星期全名 | Thursday |
%a | 星期缩写 | Thu |
%M | 月份全名 | July |
二、字符串函数速览
1. 大小写与空白处理 — 清洗必备
SELECT
UPPER('hello world') AS 大写,
LOWER('HELLO WORLD') AS 小写,
TRIM(' SQL 笔记 ') AS 去两端空格,
LTRIM(' 左边') AS 去左空格,
RTRIM('右边 ') AS 去右空格;
执行结果：
| 大写 | 小写 | 去两端空格 | 去左空格 | 去右空格 |
|---|---|---|---|---|
| HELLO WORLD | hello world | SQL 笔记 | 左边 | 右边 |
⚠️
TRIM默认去掉空格，也可以指定要去掉的字符：TRIM('x' FROM 'xxhelloxx')→'hello'
2. 截取与拼接
SELECT
CONCAT('SQL', ' ', '笔记') AS 拼接,
CONCAT_WS('-', '2026', '07', '23') AS 带分隔符拼接,
SUBSTRING('MySQL数据库', 2, 3) AS 截取, -- 从第2位取3个字符
LEFT('MySQL数据库', 4) AS 左侧取4位,
RIGHT('MySQL数据库', 3) AS 右侧取3位;
执行结果：
| 拼接 | 带分隔符拼接 | 截取 | 左侧取4位 | 右侧取3位 |
|---|---|---|---|---|
| SQL 笔记 | 2026-07-23 | ySQ | MySQ | 据库 |
补充说明：
SUBSTRING(s, pos)— 从 pos 取到末尾SUBSTRING(s, pos, len)— 从 pos 取 len 个字符SUBSTRING_INDEX(s, delim, count)— 按分隔符截取，超级实用
SELECT SUBSTRING_INDEX('a.b.c.d', '.', 2); -- 'a.b'
SELECT SUBSTRING_INDEX('a.b.c.d', '.', -2); -- 'c.d'
3. 查找与替换
SELECT
LOCATE('SQL', 'MySQL数据库教程') AS 位置, -- 返回 2
POSITION('SQL' IN 'MySQL数据库教程') AS 位置2, -- 等价
INSTR('MySQL数据库教程', 'SQL') AS 位置3, -- 等价
REPLACE('2026/07/23', '/', '-') AS 替换分隔符;
执行结果：
| 位置 | 位置2 | 位置3 | 替换分隔符 |
|---|---|---|---|
| 2 | 2 | 2 | 2026-07-23 |
字符串长度：
SELECT
LENGTH('SQL笔记') AS 字节数, -- UTF-8下中文占3字节, 结果 9
CHAR_LENGTH('SQL笔记') AS 字符数; -- 结果 5
✅ 处理中文字符串时，
CHAR_LENGTH()比LENGTH()更符合直觉。
三、实战：数据清洗与格式化
场景 — 用户注册表分析
-- 原始数据
CREATE TABLE users (
id INT,
full_name VARCHAR(50), -- 可能有空格、大小写混用
email VARCHAR(100), -- 可能有空格
registered_at DATETIME -- yyyy-MM-dd HH:mm:ss 格式
);
INSERT INTO users VALUES
(1, ' Zhang San ', 'zhangsan@example.com ', '2026-03-15 08:30:00'),
(2, 'LI SI', 'lisi@example.com', '2026-06-20 14:15:00'),
(3, ' Wang wu ', 'WANGWU@EXAMPLE.COM', '2026-07-01 09:00:00');
需求：清洗数据并输出月报格式
SELECT
id,
CONCAT(
UPPER(LEFT(TRIM(full_name), 1)), -- 姓氏首字母大写
LOWER(SUBSTRING(TRIM(full_name), 2)) -- 其余小写
) AS clean_name,
LOWER(TRIM(email)) AS clean_email,
DATE_FORMAT(registered_at, '%Y-%m') AS reg_month,
DATE_FORMAT(registered_at, '%Y年%c月%d日') AS reg_date_cn,
TIMESTAMPDIFF(DAY, registered_at, NOW()) AS reg_days_ago
FROM users;
执行结果：
| id | clean_name | clean_email | reg_month | reg_date_cn | reg_days_ago |
|---|---|---|---|---|---|
| 1 | zhang san | zhangsan@example.com | 2026-03 | 2026年3月15日 | 130 |
| 2 | li si | lisi@example.com | 2026-06 | 2026年6月20日 | 33 |
| 3 | wang wu | wangwu@example.com | 2026-07 | 2026年7月1日 | 22 |
💡 注意：
Zhang San经过处理后姓氏和名字之间的空格还在，名字的中间空格不会被 TRIM 去掉（这是正确的行为）。
四、综合实战：按月份统计并格式化输出
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
COUNT(*) AS order_count,
CONCAT('¥', FORMAT(SUM(amount), 2)) AS total_revenue,
CONCAT(
DATE_FORMAT(MIN(order_date), '%m/%d'),
' ~ ',
DATE_FORMAT(MAX(order_date), '%m/%d')
) AS date_range
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;
输出示例：
| month | order_count | total_revenue | date_range |
|---|---|---|---|
| 2026-07 | 156 | ¥48,320.50 | 07/01 ~ 07/23 |
| 2026-06 | 203 | ¥62,110.00 | 06/01 ~ 06/30 |
| 2026-05 | 178 | ¥55,899.80 | 05/01 ~ 05/31 |
这个查询同时用到了：
DATE_FORMAT→ 按月份分组DATE_SUB+CURDATE→ 筛选最近6个月CONCAT+FORMAT→ 格式化货币输出MIN/MAX+DATE_FORMAT→ 生成日期范围字符串
⚠️ 注意事项
-
DATE_FORMAT 大小写敏感
%Y= 4位年份，%y= 2位年份%m= 01-12，%M= January-December（月份名）%i= 分钟，别跟%s（秒）搞混
-
字符串函数在 WHERE 中的性能
-- ❌ 低效：无法使用索引 WHERE DATE_FORMAT(order_date, '%Y-%m') = '2026-07' -- ✅ 高效：可走索引 WHERE order_date >= '2026-07-01' AND order_date < '2026-08-01' -
CONCAT 遇到 NULL 返回 NULL
SELECT CONCAT('Hello', NULL, 'World'); -- NULL -- 用 CONCAT_WS 安全： SELECT CONCAT_WS(' ', 'Hello', NULL, 'World'); -- 'Hello World' -
不同数据库的兼容性差异
- MySQL：
DATE_FORMAT、LOCATE - PostgreSQL：
TO_CHAR（代替DATE_FORMAT）、POSITION（代替LOCATE） - SQL Server：
FORMAT、CHARINDEX - 迁移数据库时，日期格式化函数几乎必定要改
- MySQL：
-
中文字符串排序问题
ORDER BY name COLLATE utf8mb4_unicode_ci; -- 按拼音排序
总结
| 场景 | 核心函数 | 使用频率 |
|---|---|---|
| 按月/年分组统计 | DATE_FORMAT() | ⭐⭐⭐⭐⭐ |
| 日期加减计算 | DATE_ADD() / DATE_SUB() | ⭐⭐⭐⭐ |
| 计算时间差 | DATEDIFF() / TIMESTAMPDIFF() | ⭐⭐⭐⭐ |
| 拼接字段 | CONCAT() / CONCAT_WS() | ⭐⭐⭐⭐⭐ |
| 清洗空格 | TRIM() / LTRIM() / RTRIM() | ⭐⭐⭐⭐ |
| 大小写统一 | UPPER() / LOWER() | ⭐⭐⭐⭐ |
| 截取子串 | SUBSTRING() / SUBSTRING_INDEX() | ⭐⭐⭐⭐ |
| 替换字符 | REPLACE() | ⭐⭐⭐ |
Day 15 的核心心法是：日期函数帮你按时间做聚合和计算，字符串函数帮你清洗和重组数据。两者结合，能让任何脏数据变得规整、可分析。 这是每天写 SQL 都会用到的基本功，值得反复练习。
