高级函数
高级函数
🌍 生活类比
SQL就像一个瑞士军刀，除了前面学过的"刀"（查询）和"锯"（聚合），还有很多实用的小工具：
- 字符串函数 = 文字编辑器——拼接、截取、大小写转换
- 数值函数 = 计算器——四舍五入、取整、绝对值
- 日期函数 = 日历——获取当前日期、计算天数差、提取年月日
- 类型转换 = 翻译官——在数字、文本、日期之间转换
掌握这些函数，能让你在SQL中完成更多数据处理工作，减少在应用层的额外加工。
🎯 核心概念
字符串函数
CONCAT — 字符串拼接
将多个字符串连接成一个。
SQL
-- 拼接姓名
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
-- MySQL也支持用||拼接（需开启PIPES_AS_CONCAT）
-- SELECT first_name || ' ' || last_name FROM employees;
SUBSTRING — 截取子串
从字符串中提取一部分。
SQL
-- 截取邮箱的用户名部分（@之前）
SELECT email, SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM employees
WHERE email IS NOT NULL;
-- 简单截取：从第1个字符开始，取3个
SELECT SUBSTRING('Hello World', 1, 3); -- 结果：Hel
UPPER / LOWER — 大小写转换
SQL
SELECT UPPER('hello') AS 大写, LOWER('WORLD') AS 小写;
-- 结果：HELLO, world
-- 实际应用：不区分大小写搜索
SELECT * FROM employees WHERE LOWER(email) = 'zhangsan@company.com';
TRIM — 去除空格
SQL
-- 去除两端空格
SELECT TRIM(' hello ') AS trimmed; -- 结果：hello
-- 去除指定字符
SELECT TRIM(LEADING '0' FROM '0012300'); -- 结果：12300
REPLACE — 替换字符串
SQL
-- 将手机号中间4位替换为****
SELECT phone, REPLACE(phone, SUBSTRING(phone, 4, 4), '****') AS masked
FROM employees;
LENGTH — 字符串长度
SQL
SELECT first_name, LENGTH(first_name) AS name_len
FROM employees;
数值函数
ROUND — 四舍五入
SQL
SELECT ROUND(3.14159, 2); -- 结果：3.14
SELECT ROUND(3.14159, 0); -- 结果：3
SELECT ROUND(1234.5, -2); -- 结果：1200（对百位四舍五入）
CEIL / FLOOR — 向上/向下取整
SQL
SELECT CEIL(3.1); -- 结果：4（向上取整）
SELECT CEIL(3.0); -- 结果：3
SELECT FLOOR(3.9); -- 结果：3（向下取整）
SELECT FLOOR(3.0); -- 结果：3
💡 注意：不同数据库中函数名不同。SQL Server用
CEILING()，Oracle用 CEIL()。
ABS — 绝对值
SQL
SELECT ABS(-100); -- 结果：100
SELECT ABS(50); -- 结果：50
MOD — 取模（求余数）
SQL
SELECT MOD(10, 3); -- 结果：1
SELECT MOD(100, 7); -- 结果：2
-- SQL Server不支持MOD函数，用%代替
-- SELECT 10 % 3; -- 结果：1
日期函数
NOW — 当前日期时间
SQL
SELECT NOW(); -- 结果：2026-06-28 14:30:00（当前时间）
SELECT CURDATE(); -- 结果：2026-06-28（仅日期）
SELECT CURTIME(); -- 结果：14:30:00（仅时间）
💡 注意：SQL Server用
GETDATE() 代替 NOW()。
DATE — 提取日期部分
SQL
SELECT DATE(NOW()); -- 结果：2026-06-28
-- 提取年、月、日
SELECT EXTRACT(YEAR FROM order_date) AS 年份,
EXTRACT(MONTH FROM order_date) AS 月份,
EXTRACT(DAY FROM order_date) AS 日
FROM orders;
DATEDIFF — 日期差
SQL
-- 计算两个日期之间相差的天数
SELECT DATEDIFF('2026-12-31', '2026-01-01'); -- 结果：364
-- 计算员工入职天数
SELECT first_name, hire_date,
DATEDIFF(CURDATE(), hire_date) AS 入职天数
FROM employees;
💡 注意：MySQL的
DATEDIFF(日期1, 日期2) 是 日期1 - 日期2。SQL Server也是同样顺序。PostgreSQL用 日期1 - 日期2 直接相减。
DATE_FORMAT — 日期格式化
SQL
-- MySQL
SELECT DATE_FORMAT(order_date, '%Y年%m月%d日') AS 格式化日期
FROM orders;
-- SQL Server
-- SELECT FORMAT(order_date, 'yyyy年MM月dd日') FROM orders;
EXTRACT — 提取日期部分
SQL
SELECT EXTRACT(YEAR FROM order_date) AS 年,
EXTRACT(MONTH FROM order_date) AS 月,
EXTRACT(DAY FROM order_date) AS 日,
EXTRACT(HOUR FROM NOW()) AS 时
FROM orders;
类型转换
CAST — 类型转换（标准SQL）
SQL
-- 字符串转整数
SELECT CAST('123' AS SIGNED INTEGER); -- MySQL
SELECT CAST('123' AS INT); -- SQL Server
-- 数字转字符串
SELECT CAST(12345 AS CHAR);
-- 日期转字符串
SELECT CAST(order_date AS CHAR) FROM orders;
-- 字符串转日期
SELECT CAST('2026-06-28' AS DATE);
CONVERT — 类型转换（MySQL / SQL Server）
SQL
-- MySQL
SELECT CONVERT('123', SIGNED INTEGER);
SELECT CONVERT(123, CHAR);
-- SQL Server
SELECT CONVERT(INT, '123');
SELECT CONVERT(VARCHAR, 12345);
💡 建议：
CAST 是SQL标准，兼容性更好，推荐优先使用。CONVERT 在不同数据库中语法差异较大。
📝 基本语法
SQL
-- 字符串函数
SELECT CONCAT(字符串1, 字符串2) AS 拼接结果,
SUBSTRING(字符串, 起始位置, 长度) AS 截取结果,
UPPER(字符串) AS 大写,
LOWER(字符串) AS 小写,
TRIM(字符串) AS 去空格,
REPLACE(字符串, 旧值, 新值) AS 替换结果,
LENGTH(字符串) AS 长度;
-- 数值函数
SELECT ROUND(数值, 小数位) AS 四舍五入,
CEIL(数值) AS 向上取整,
FLOOR(数值) AS 向下取整,
ABS(数值) AS 绝对值,
MOD(数值1, 数值2) AS 取模;
-- 日期函数
SELECT NOW() AS 当前时间,
CURDATE() AS 当前日期,
DATEDIFF(日期1, 日期2) AS 天数差,
EXTRACT(YEAR FROM 日期) AS 年份;
-- 类型转换
SELECT CAST(值 AS 目标类型) AS 转换结果;
💡 提示：
- 不同数据库的函数名和参数可能不同，使用前查阅对应数据库的文档
CAST是SQL标准，推荐使用；CONVERT在MySQL和SQL Server中语法不同- 日期函数差异最大，跨数据库开发时要特别注意
📌 示例
示例：字符串处理——员工信息格式化
SQL
SELECT
CONCAT(UPPER(last_name), ', ', first_name) AS 姓名,
LOWER(email) AS 邮箱,
SUBSTRING(phone, 1, 3) AS 区号,
LENGTH(first_name) AS 名字长度,
REPLACE(department_id, NULL, '未分配') AS 部门
FROM employees
ORDER BY last_name;
输出：
TEXT
姓名 邮箱 区号 名字长度 部门
----------- ---------------------- ----- -------- ------
LI, 四 lisi@company.com 138 2 2
WANG, 五 wangwu@company.com 139 2 2
ZHANG, 三 zhangsan@company.com 137 2 1
💡 解读：
CONCAT + UPPER 格式化姓名，LOWER 统一邮箱为小写，SUBSTRING 提取手机号前3位。
示例：数值计算——订单金额处理
SQL
SELECT
order_id,
total_amount AS 原始金额,
ROUND(total_amount * 0.9, 2) AS 折后金额,
CEIL(total_amount / 1000) AS 千元进位,
FLOOR(total_amount / 1000) AS 千元取整,
MOD(total_amount, 100) AS 零头
FROM orders
WHERE status = 'completed';
输出：
TEXT
order_id 原始金额 折后金额 千元进位 千元取整 零头
------- --------- --------- ------- ------- -----
1 15000.00 13500.00 15 15 0.00
2 8500.00 7650.00 9 8 0.00
3 3200.00 2880.00 4 3 0.00
💡 解读：
ROUND 计算9折后的价格，CEIL 和 FLOOR 分别向上和向下取整到千元，MOD 计算余数。
示例：日期计算 + 类型转换
SQL
SELECT
first_name AS 姓名,
hire_date AS 入职日期,
DATEDIFF(CURDATE(), hire_date) AS 入职天数,
EXTRACT(YEAR FROM hire_date) AS 入职年份,
CONCAT(CAST(salary AS CHAR), ' 元') AS 工资显示,
DATE_FORMAT(hire_date, '%Y年%m月') AS 入职月份
FROM employees
ORDER BY hire_date;
输出：
TEXT
姓名 入职日期 入职天数 入职年份 工资显示 入职月份
------ ---------- -------- ------- ----------- ----------
张三 2023-03-15 1199 2023 15000.00 元 2023年03月
李四 2023-07-01 1089 2023 18000.00 元 2023年07月
王五 2024-01-10 899 2024 12000.00 元 2024年01月
💡 解读：
DATEDIFF 计算入职天数，EXTRACT 提取年份，CAST 将工资转为字符串后拼接单位，DATE_FORMAT 格式化日期。
🎬 场景演练
场景一：数据清洗——统一格式
数据库中的数据格式不统一，需要清洗后展示。
SQL
SELECT
employee_id,
CONCAT(UPPER(TRIM(last_name)), ', ', TRIM(first_name)) AS 标准姓名,
LOWER(TRIM(email)) AS 标准邮箱,
REPLACE(REPLACE(phone, '-', ''), ' ', '') AS 标准电话,
CAST(salary AS DECIMAL(10,2)) AS 标准工资
FROM employees
WHERE email IS NOT NULL;
💡 思路：
TRIM 去除多余空格，UPPER/LOWER 统一大小写，REPLACE 清理电话号码中的特殊字符，CAST 确保数据类型一致。
场景二：报表统计——按时间维度分析
按季度统计订单数据。
SQL
SELECT
EXTRACT(YEAR FROM order_date) AS 年份,
CEIL(EXTRACT(MONTH FROM order_date) / 3.0) AS 季度,
COUNT(*) AS 订单数,
ROUND(SUM(total_amount), 2) AS 总金额,
ROUND(AVG(total_amount), 2) AS 平均订单金额,
DATEDIFF(MAX(order_date), MIN(order_date)) AS 跨度天数
FROM orders
WHERE status != 'cancelled'
GROUP BY EXTRACT(YEAR FROM order_date), CEIL(EXTRACT(MONTH FROM order_date) / 3.0)
ORDER BY 年份, 季度;
💡 思路：
CEIL(MONTH / 3.0) 将月份转换为季度（1-3月→1，4-6月→2，以此类推）。DATEDIFF 计算每个季度的时间跨度。
❓ 常见问题
Q：CONCAT遇到NULL会怎样？ A： MySQL中
CONCAT遇到NULL会返回NULL。如果需要将NULL当作空字符串处理，使用COALESCE：CONCAT(COALESCE(first_name, ''), ' ', last_name)。PostgreSQL中||运算符遇到NULL也返回NULL。
Q：ROUND和CEIL/FLOOR有什么区别？ A：
ROUND是四舍五入到指定小数位，CEIL是向上取整到最近整数，FLOOR是向下取整到最近整数。例如 3.5：ROUND(3.5)= 4，CEIL(3.1)= 4，FLOOR(3.9)= 3。
Q：不同数据库的日期函数差异大吗？ A： 差异很大。MySQL用
NOW()、DATEDIFF()、DATE_FORMAT()；SQL Server用GETDATE()、DATEDIFF()、FORMAT()；PostgreSQL用NOW()、日期相减、TO_CHAR()。跨数据库开发时建议查阅对应文档。
Q：CAST和CONVERT该用哪个？ A： 推荐用
CAST，它是SQL标准，所有主流数据库都支持。CONVERT在MySQL和SQL Server中语法不同，兼容性较差。
📖 小节
| 分类 | 常用函数 | 用途 |
|---|---|---|
| 字符串 | CONCAT, SUBSTRING, UPPER, LOWER, TRIM, REPLACE, LENGTH | 拼接、截取、转换、清理 |
| 数值 | ROUND, CEIL, FLOOR, ABS, MOD | 四舍五入、取整、取模 |
| 日期 | NOW, CURDATE, DATEDIFF, EXTRACT, DATE_FORMAT | 获取时间、计算差值、提取部分 |
| 类型转换 | CAST, CONVERT | 数据类型转换 |
- 字符串函数用于数据清洗和格式化
- 数值函数用于数学计算和精度控制
- 日期函数用于时间计算和报表统计
CAST是标准SQL的类型转换函数，推荐优先使用- 不同数据库的函数名和语法可能有差异，跨数据库开发时要注意
📝 作业
作业1（⭐）：查询 employees 表，将 first_name 和 last_name 拼接为全名（大写），并计算每个人的入职天数。
作业2（⭐⭐）：查询 orders 表，按月份统计订单数量和总金额，使用 EXTRACT 提取年月，ROUND 保留2位小数。
作业3（⭐⭐⭐）：查询 employees 表，生成一个"员工名片"：格式为 [部门名] 姓名 (入职年份年) - 月薪:xxxxx元。要求使用字符串函数拼接，CAST 转换数字为字符串。
下一课
👉 16-case-when - 条件表达式：学习 CASE WHEN 条件表达式，掌握在SQL中实现条件逻辑的技能！
