SQL 函数

SQL 内置了大量函数,让你在查询里直接处理字符串、日期、数值——不用把数据拉回应用层。掌握常用函数,能让 SQL 干掉很多本该写在 Java/Python 里的逻辑,既快又省事。本篇按聚合、字符串、日期、数值、类型转换、NULL 处理分类介绍。

1. 聚合函数

聚合函数把多行值聚合成一个值,GROUP BY 篇已详讲。这里再列一次速查表:

-- 聚合函数:多行 → 单值(GROUP BY 篇已详讲)
SELECT
    COUNT(*)           AS row_count,    -- 行数
    SUM(price)         AS total,        -- 求和
    AVG(price)         AS avg_price,    -- 平均
    MAX(price)         AS max_price,    -- 最大
    MIN(price)         AS min_price     -- 最小
FROM products;

-- 聚合函数会自动忽略 NULL(SUM、AVG、MAX、MIN 都不计算 NULL)
-- 但 COUNT(*) 计算 NULL 行;COUNT(列) 不计算 NULL
-- 搭配 DISTINCT:SUM(DISTINCT price) 去重后再求和
-- 搭配 GROUP BY:对每组分别算

关键点:聚合函数自动忽略 NULL(SUM、AVG、MAX、MIN 都不算 NULL 行),但 COUNT(*) 会统计含 NULL 的行(它统计的是行本身)。

2. 字符串函数

字符串函数是日常用得最多的一类——拼接、截取、替换、查找、大小写转换:

-- 字符串函数(MySQL / PostgreSQL 大体一致,个别不同)

-- 拼接(MySQL 用 CONCAT,PostgreSQL 用 ||)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT first_name || ' ' || last_name FROM users;  -- PostgreSQL

-- 长度
SELECT LENGTH('hello');                  -- 5(字节数,中文算 3)
SELECT CHAR_LENGTH('你好');              -- 2(字符数)

-- 大小写
SELECT UPPER('hello');                   -- HELLO
SELECT LOWER('HELLO');                   -- hello

-- 截取
SELECT SUBSTRING('hello world', 1, 5);   -- hello(从第 1 位取 5 个字符)
SELECT SUBSTRING('hello world', 7);      -- world(从第 7 位取到末尾)

-- 替换
SELECT REPLACE('hello world', 'world', 'sql');   -- hello sql

-- 查找
SELECT INSTR('hello world', 'world');    -- 7(返回位置,找不到返回 0)

-- 去空格
SELECT TRIM('  hello  ');                -- hello
SELECT LTRIM('  hello');                 -- hello(只去左边)
SELECT RTRIM('hello  ');                 -- hello(只去右边)

-- 填充
SELECT LPAD('5', 3, '0');                -- 005(左填充 0 到 3 位)
SELECT RPAD('5', 3, '0');                -- 500(右填充)

几个注意点:拼接 MySQL 用 CONCAT,PostgreSQL 用 ||;LENGTH 和 CHAR_LENGTH 在中文字符串上不同(LENGTH 算字节数,UTF-8 中文是 3 字节;CHAR_LENGTH 算字符数)。做"截取前 N 个字符"的需求时,记得用 CHAR_LENGTH 而不是 LENGTH。

3. 日期时间函数

处理时间是 SQL 里最容易踩坑的地方——时区、格式、计算差异,每个数据库还有自己的方言。本节以 MySQL 为主:

-- 日期时间函数(MySQL 为主,PostgreSQL 个别不同)

-- 当前时间
SELECT NOW();                  -- 2024-08-05 14:30:00(日期 + 时间)
SELECT CURDATE();              -- 2024-08-05(仅日期)
SELECT CURTIME();              -- 14:30:00(仅时间)
SELECT CURRENT_TIMESTAMP;      -- 标准 SQL,等价于 NOW()

-- 提取部分
SELECT YEAR(NOW());            -- 2024
SELECT MONTH(NOW());           -- 8
SELECT DAY(NOW());             -- 5
SELECT DAYOFWEEK(NOW());       -- 2(周日=1,周六=7)
SELECT HOUR(NOW());
SELECT MINUTE(NOW());

-- 格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');           -- 2024-08-05
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');        -- 2024年08月05日
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');           -- 14:30:00

-- 计算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);          -- 明天
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);          -- 7 天前
SELECT DATEDIFF('2024-12-31', '2024-01-01');     -- 365(相差天数)
SELECT TIMESTAMPDIFF(YEAR, '2000-01-01', NOW()); -- 24(相差年数)

-- 转换字符串 → 日期
SELECT STR_TO_DATE('2024-08-05', '%Y-%m-%d');

跨库差异警告:MySQL 用 NOW(),PostgreSQL 用 CURRENT_TIMESTAMP;MySQL 用 DATE_ADD(d, INTERVAL 1 DAY),PostgreSQL 用 d + INTERVAL '1 day';日期格式化 MySQL 用 DATE_FORMAT,PostgreSQL 用 TO_CHAR。生产代码里这些都要按数据库调整。

4. 数值函数

数值函数处理数字:四舍五入、取整、绝对值、随机数等。需求最频繁的是 ROUND(保留小数)和 RAND(随机抽样):

-- 数值函数

-- 四舍五入
SELECT ROUND(3.14159);          -- 3
SELECT ROUND(3.14159, 2);       -- 3.14(保留 2 位)
SELECT ROUND(3.5);              -- 4
SELECT ROUND(2.5);              -- 3(银行家舍入,某些库是 2)

-- 上取整 / 下取整
SELECT CEIL(3.1);               -- 4
SELECT CEILING(3.1);            -- 4(同义)
SELECT FLOOR(3.9);              -- 3

-- 截断(不四舍五入)
SELECT TRUNCATE(3.14159, 2);    -- 3.14

-- 绝对值
SELECT ABS(-5);                 -- 5

-- 取模
SELECT MOD(10, 3);              -- 1(10 % 3)
SELECT 10 MOD 3;                -- 1
SELECT 10 % 3;                  -- 1

-- 幂与平方根
SELECT POWER(2, 10);            -- 1024
SELECT SQRT(16);                -- 4

-- 随机数
SELECT RAND();                  -- 0 到 1 之间的浮点数
SELECT RAND() * 100;            -- 0 到 100
SELECT FLOOR(RAND() * 100);     -- 0 到 99 的整数
SELECT FLOOR(RAND() * 6) + 1;   -- 1 到 6(掷骰子)

实战技巧:随机抽样用 ORDER BY RAND() LIMIT 10(小表可以,大表很慢);保留小数用 ROUND(x, 2) 而不是用应用层处理;掷骰子、生成测试数据用 FLOOR(RAND() * N)。

5. 类型转换函数

有时数据存成字符串但你要当数字用(或反过来),就要用类型转换:CAST(标准)、CONVERT(MySQL):

-- 类型转换函数

-- CAST(值 AS 类型)  标准 SQL
SELECT CAST('123' AS SIGNED);              -- 字符串转整数
SELECT CAST(3.14 AS DECIMAL(10,1));        -- 3.1
SELECT CAST(123 AS CHAR);                  -- 数字转字符串
SELECT CAST('2024-08-05' AS DATE);         -- 字符串转日期

-- CONVERT(值, 类型)  MySQL 写法
SELECT CONVERT('123', SIGNED);
SELECT CONVERT('2024-08-05', DATE);

-- 隐式转换(MySQL 比较宽松,但不推荐依赖)
SELECT '10' + 5;                           -- 15(字符串自动转数字)
SELECT '10' = 10;                          -- 1(true,类型不同也相等)

-- 实战:统计字符串里某个字符出现次数
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', ''))) / LENGTH(',') AS cnt;
-- 4 个逗号(LENGTH 差值除以目标长度)

建议:不要依赖隐式转换。MySQL 比较宽松('10' = 10 返回 true),但 PostgreSQL 严格报错。养成显式转换的习惯,迁移数据库时少踩坑。

6. NULL 处理函数(高频)

NULL 是 SQL 的"特殊公民",几乎所有项目都要处理它。下面三个函数是必须熟练的:

-- NULL 处理函数(非常常用!)

-- COALESCE:返回第一个非 NULL 的值
SELECT COALESCE(nickname, name, '匿名') FROM users;
-- nickname 不为 NULL 就用 nickname,否则用 name,再否则用"匿名"

-- IFNULL(MySQL):第一个参数为 NULL 就返回第二个
SELECT IFNULL(email, '未填写') FROM users;

-- NULLIF(a, b):a = b 时返回 NULL,否则返回 a
-- 常用于避免除以 0
SELECT NULLIF(score, 0);                   -- score 是 0 时返回 NULL
SELECT 100 / NULLIF(score, 0);             -- score 是 0 时返回 NULL 而不是报错

-- ⚠️ 聚合函数自动忽略 NULL
-- AVG(score) 不会把 NULL 算进去
-- 但 COUNT(*) 会统计 NULL 行(它统计的是行本身)

COALESCE 是最通用的,所有数据库都支持,推荐用它。NULLIF 看似小众,但防止除零错误时是神器:100 / NULLIF(score, 0) 当 score 是 0 时返回 NULL 而不是报错。

常用函数速查表

按使用频率排序的"必背清单":

聚合:COUNT、SUM、AVG、MAX、MIN。
字符串:CONCAT、SUBSTRING、REPLACE、LENGTH、UPPER/LOWER、TRIM。
日期:NOW、CURDATE、DATE_FORMAT、DATEDIFF、DATE_ADD。
数值:ROUND、CEIL、FLOOR、ABS、RAND、MOD。
转换:CAST、CONVERT。
NULL:COALESCE、IFNULL、NULLIF。

性能提示

函数包字段会让索引失效:WHERE YEAR(created_at) = 2024 用不上索引,改写成 WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'。
SELECT 里用函数没问题,WHERE 里要小心。
大表上的字符串操作(SUBSTRING、REPLACE)可能很慢,能用预计算字段就用。

小结

SQL 内置函数让查询直接处理业务逻辑,不用拉回应用层。但记住"函数包字段会让索引失效"这条铁律,WHERE 里的函数要慎用。下一篇我们看 SQL 性能优化的核心:索引。

← 上一篇子查询

下一篇索引 →

✈️💬