先准备一张测试表方便所有示例复用
CREATE TABLE `user` (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
age INT,
salary DECIMAL(10,2),
create_time DATETIME,
remark TEXT
);
INSERT INTO `user` VALUES
(1,'Zhang San',25,8500.50,'2025-03-15 09:20:10','优秀员工'),
(2,'Li Si',32,12000.00,'2025-04-22 14:10:30',''),
(3,'Wang Wu',28,9600.80,'2025-05-10 16:45:00','潜力新人');
一、字符串函数
-- 常用场景：手机号中间4位脱敏
SELECT CONCAT(LEFT('13812345678',3),'****',RIGHT('13812345678',4));
二、数值函数
sql
-- 薪资统计实战
SELECT
AVG(salary) avg_salary,
MAX(salary) max_sal,
FLOOR(AVG(salary)) avg_floor
FROM user;
三、日期时间函数（开发高频）
sql
-- 日期格式化实战
SELECT DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s') FROM user;
-- %Y4位年 %m月 %d日 %H24小时 %i分钟 %s秒
-- 查询近30天创建的数据
SELECT * FROM user
WHERE create_time >= DATE_SUB(NOW(),INTERVAL 30 DAY);
四、流程控制函数（条件判断）
1. IF()
语法：IF(条件,满足值,不满足值)
sql
SELECT
username,
salary,
IF(salary>10000,'高薪','普通薪资') AS level
FROM user;
2. IFNULL()
IFNULL(字段,默认值)：字段为 NULL 时返回默认值
sql
SELECT username, IFNULL(remark,'无备注') remark FROM user;
3. CASE WHEN ⭐复杂条件（推荐）
sql
SELECT
username,salary,
CASE
WHEN salary >=12000 THEN 'A档'
WHEN salary >=9000 THEN 'B档'
ELSE 'C档'
END AS salary_level
FROM user;
五、聚合函数（搭配 GROUP BY）
COUNT() SUM() AVG() MAX() MIN()
sql
-- 统计薪资档位人数
SELECT
CASE
WHEN salary >=10000 THEN '高薪' ELSE '普通'
END salary_type,
COUNT(id) count_num,
AVG(salary) avg_sal
FROM user
GROUP BY salary_type;
注意：
COUNT(*)统计所有行；COUNT(字段)忽略 NULL
六、JSON 函数（MySQL5.7+/8.0）
适合存储 json 结构数据
sql
CREATE TABLE test_json(data JSON);
INSERT INTO test_json VALUES('{"name":"tom","age":20}');
-- -> 获取json值，->> 返回字符串
SELECT data->>'$.name' FROM test_json;
-- JSON_SET 修改json字段
UPDATE test_json SET data=JSON_SET(data,'$.age',21);
七、窗口函数 MySQL8.0+（面试高频）
必须开启 MySQL8，支持排序分组排名
sql
-- 薪资排名
SELECT
username,salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) rn, --连续排名不并列
RANK() OVER(ORDER BY salary DESC) rk, --并列跳号
DENSE_RANK() OVER(ORDER BY salary DESC) dr --并列不跳号
FROM user;
八、常用开发综合实战案例
案例 1：用户列表信息整合、脱敏、条件标签
sql
SELECT
id,
CONCAT_WS(' | ',username,IFNULL(remark,'无备注')) user_info,
IF(age>=30,'资深','青年') user_type,
DATE_FORMAT(create_time,'%Y-%m-%d') create_date
FROM user;
案例 2：统计月度新增用户
sql
SELECT
DATE_FORMAT(create_time,'%Y-%m') month,
COUNT(id) new_user_count
FROM user
GROUP BY month
ORDER BY month;
案例 3：计算员工工龄
sql
SELECT
username,create_time,
TIMESTAMPDIFF(YEAR,create_time,CURDATE()) work_year
FROM user;
⚠️ 重要避坑提醒
不要在索引列上包裹函数：
WHERE YEAR(create_time) = 2025无法走索引，优化写法：
sql
WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31 23:59:59'
IFNULL只判断 NULL，空字符串''不属于 NULLCHAR_LENGTH统计字符，LENGTH统计字节（中文 utf8mb4 占 3 字节）窗口函数仅支持 MySQL8.0，5.7 不兼容
原文链接
欢迎访问 小易撩挨踢