作为一名写了多年SQL的老兵我见过太多开发者在面对复杂查询时第一反应就是“把数据捞出来用Java/Python慢慢算”。其实MySQL内置了极其丰富的系统函数掌握了它们你不仅能写出更优雅的SQL还能让数据库替你完成大量计算工作性能远超应用层处理。今天我带你系统梳理MySQL的系统预定义函数。从简单的数学运算到复杂的窗口分析从字符串截取到日期计算每一类函数我都会结合实际业务场景给出示例。读完这篇文章你会彻底明白原来那些需要几十行代码才能搞定的业务逻辑用一条SQL就能轻松拿下。一、函数是什么在MySQL中函数是一个封装好的、可复用的功能单元。你可以把它想象成一个“黑盒”扔进参数也可以不扔它经过内部运算后吐出一个结果。函数必须有返回值即使没有任何参数也必须返回一个值函数可以接受参数也可以不接受函数不会修改原始数据只对查询结果进行加工根据处理方式不同MySQL的系统预定义函数分为两大类单行函数对每一行记录分别处理输入多少行输出多少行。例如把每个员工的名字转成大写或者计算每个员工的实发工资。分组函数聚合函数对多行记录一起运算最终只返回一行或少数几行结果。例如计算全公司平均薪资或者统计每个部门的员工人数。下面我们逐一深入。二、单行函数逐行处理的利器单行函数像生产线上的工人每经过一行数据就处理一行最终输出同样行数的结果。1. 数学函数让数字计算更精确函数说明示例abs(x)绝对值ABS(-10) → 10ceil(x)向上取整CEIL(3.14) → 4floor(x)向下取整FLOOR(3.99) → 3mod(x,y)求余数MOD(10,3) → 1rand()返回0~1之间的随机数常用于随机抽样round(x,y)四舍五入保留y位小数ROUND(3.1415,2) → 3.14truncate(x,y)直接截断不四舍五入TRUNCATE(3.1415,2) → 3.14format(x,y)格式化数字每三位加逗号返回字符串FORMAT(1234567.89,2) → 1,234,567.89sqrt(x)平方根SQRT(9) → 3pow(x,y)x的y次方POW(2,3) → 8实战场景计算员工旷工一天的扣款金额假设当月22个工作日。SELECT ename, salary/22 AS 原始扣款, CEIL(salary/22) AS 向上取整扣款, FLOOR(salary/22) AS 向下取整扣款, ROUND(salary/22,2) AS 四舍五入扣款, TRUNCATE(salary/22,2) AS 截断扣款 FROM t_employee;在实际业务中薪资计算通常使用ROUND因为四舍五入更符合财务规范而CEIL和FLOOR可能用于某些特殊场景比如罚款最低按元计。另一个场景统计公司平均薪资并分别用不同取整方式展示方便汇报。SELECT AVG(salary) AS 原始平均, CEIL(AVG(salary)) AS 向上取整平均, FLOOR(AVG(salary)) AS 向下取整平均, ROUND(AVG(salary),2) AS 四舍五入平均, TRUNCATE(AVG(salary),2) AS 截断平均 FROM t_employee;2. 字符串函数数据清洗的瑞士军刀字符串处理是数据分析中最耗时的一环MySQL提供了丰富的字符串函数让你在SQL层面完成清洗、格式化、截取等操作。函数说明示例concat(s1,s2,...)拼接字符串CONCAT(Hello, , World) → Hello Worldconcat_ws(a,s1,s2,...)用指定分隔符拼接CONCAT_WS(-, 2026, 03, 28) → 2026-03-28char_length(s)返回字符数CHAR_LENGTH(你好) → 2length(s)返回字节数与字符集相关LENGTH(你好) → 6UTF8locate(s,str) 或 instr(str,s)返回子串首次出现的位置LOCATE(ab, abcab) → 1upper(s) / lower(s)转大写/小写UPPER(mysql) → MYSQLleft(s,n) / right(s,n)取左边/右边n个字符LEFT(abcdef,3) → abclpad(str,len,pad) / rpad(str,len,pad)用指定字符左/右填充至指定长度LPAD(123,5,0) → 00123trim(s)去除两侧空格TRIM( hello ) → helloreplace(str,a,b)将str中的a替换为bREPLACE(abcabc,a,x) → xbcxbcrepeat(s,n)重复s n次REPEAT(ab,3) → abababreverse(s)反转字符串REVERSE(abc) → cbasubstring(str,index,len)从index位置截取len个字符SUBSTRING(abcdef,2,3) → bcdsubstring_index(str,分隔符,count)按分隔符截取count为正从左数为负从右数SUBSTRING_INDEX(www.atguigu.com,.,2) → www.atguigu实战场景1脱敏显示姓名将员工姓名处理成“张xx”的样式只保留姓氏其余用x替代。SELECT ename, RPAD(LEFT(ename,1), CHAR_LENGTH(ename), x) AS 脱敏姓名 FROM t_employee;实战场景2统计字符串中逗号的数量员工的work_place字段是用逗号分隔的工作地点比如“北京,上海,广州”。要统计工作地点个数可以用REPLACE巧妙实现。SELECT ename, work_place, CHAR_LENGTH(work_place) - CHAR_LENGTH(REPLACE(work_place, ,, )) 1 AS 地点数量 FROM t_employee;原理原字符串长度减去去掉逗号后的长度得到逗号个数再加1就是地点数量。实战场景3提取邮箱前缀从email字段中提取前面的用户名。SELECT ename, email, SUBSTRING(email, 1, LOCATE(, email) - 1) AS 邮箱前缀 FROM t_employee;实战场景4去掉字符串两端指定字符TRIM不仅可以去除空格还可以去除任意字符。SELECT TRIM(BOTH FROM hello); -- 结果hello SELECT TRIM(LEADING FROM hello); -- 结果hello SELECT TRIM(TRAILING FROM hello);-- 结果hello3. 日期时间函数时间维度的分析利器日期时间处理是报表和统计中最常见的需求MySQL提供了从获取当前时间到计算时间差的完整工具集。函数说明示例curdate() / current_date()当前系统日期CURDATE() → 2026-03-28curtime() / current_time()当前系统时间CURTIME() → 14:30:00now() / sysdate() / localtime()当前日期时间NOW() → 2026-03-28 14:30:00utc_date() / utc_time()UTC日期/时间UTC_DATE() → 2026-03-28unix_timestamp(date)返回Unix时间戳UNIX_TIMESTAMP(2000-01-01) → 946684800year(date) / month(date) / day(date)提取年/月/日YEAR(NOW()) → 2026hour(time) / minute(time) / second(time)提取时/分/秒HOUR(NOW()) → 14extract(type from date)提取指定部分EXTRACT(YEAR_MONTH FROM NOW()) → 202603datediff(date1, date2)计算日期差天数DATEDIFF(2026-03-28,2000-01-01) → 9582timediff(time1, time2)计算时间差时分秒TIMEDIFF(12:00:00,09:00:00) → 03:00:00date_add(date, interval expr type)日期加法DATE_ADD(NOW(), INTERVAL 1 DAY)date_format(datetime, fmt)格式化日期DATE_FORMAT(NOW(), %Y年%m月%d日) → 2026年03月28日str_to_date(str, fmt)字符串转日期STR_TO_DATE(2026-03-28, %Y-%m-%d)实战场景1查询本月生日的员工使用MONTH()提取生日月份与当前月份比较。SELECT ename, birthday FROM t_employee WHERE MONTH(CURDATE()) MONTH(birthday);实战场景2查询入职超过5年的员工使用DATEDIFF计算天数差与5*365比较更精确的方式是用DATE_ADD。SELECT ename, hiredate FROM t_employee WHERE DATEDIFF(CURDATE(), hiredate) 365 * 5;但注意如果考虑闰年更严谨的写法是WHERE hiredate DATE_SUB(CURDATE(), INTERVAL 5 YEAR);实战场景3格式化日期输出报表中经常需要将日期显示为特定格式。SELECT DATE_FORMAT(NOW(), %Y年%m月%d日 %H:%i:%s) AS 当前时间;常用格式符%Y四位年份%m两位月份%d两位日期%H24小时制小时%i分钟%s秒实战场景4计算距离中午放学还有多久学校场景假设中午放学是12:00。SELECT TIMEDIFF(12:00:00, CURTIME()) AS 距离放学;4. 加密函数数据安全的守门员在用户登录、敏感信息存储等场景加密函数必不可少。需要注意的是MySQL 8.0已移除PASSWORD()函数推荐使用更安全的SHA2。函数说明返回值长度md5(str)返回32位十六进制字符串32字符sha(str)返回40位十六进制字符串40字符sha2(str, hash_length)hash_length可以是224/256/384/512/00等同于256根据参数而定实战场景用户密码加密存储假设用户注册时密码是“123456”我们存储其MD5值登录时比对加密后的结果。-- 创建用户表 CREATE TABLE t_user ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20), password VARCHAR(100) ); -- 插入时加密 INSERT INTO t_user VALUES(NULL, chai, MD5(123456)); -- 登录验证 SELECT * FROM t_user WHERE usernamechai AND passwordMD5(123456);实际生产环境建议使用SHA2因为MD5已不再安全可通过彩虹表快速破解。5. 系统信息函数了解当前环境有时我们需要在SQL中获取当前数据库名、版本号等信息这些系统信息函数非常有用。函数说明database()当前使用的数据库名version()MySQL服务器版本user()当前登录用户名和主机名SELECT DATABASE(), VERSION(), USER();在编写自动化脚本或调试时这些函数可以帮助你快速确认当前环境。6. 条件判断函数让SQL拥有分支逻辑条件判断函数是SQL实现复杂业务逻辑的关键。它们不是用来筛选记录的而是根据条件的不同返回不同的显示结果。函数说明if(a, x, y)如果a为真返回x否则返回yifnull(x, y)如果x不为NULL返回x否则返回ycase when ... then ... else ... end多条件判断case 表达式 when 常量1 then 值1 ... end等值判断实战场景1根据薪资水平显示等级SELECT ename, salary, CASE WHEN salary 20000 THEN 羡慕级别 WHEN salary 15000 THEN 努力级别 WHEN salary 10000 THEN 平均级别 ELSE 保底级别 END AS 等级 FROM t_employee;注意CASE按顺序匹配一旦满足条件就停止所以条件顺序很重要。实战场景2处理NULL值计算实发工资时如果commission_pct为NULL则按0计算。SELECT ename, salary, commission_pct, salary salary * IFNULL(commission_pct, 0) AS 实发工资 FROM t_employee;IFNULL在这里发挥了关键作用避免了计算结果变成NULL。实战场景3用CASE表达式进行等值匹配假设要根据工作地点数量判断员工出差频率。SELECT ename, work_place, CASE CHAR_LENGTH(work_place) - CHAR_LENGTH(REPLACE(work_place, ,, )) 1 WHEN 1 THEN 只在本地工作 WHEN 2 THEN 两地奔波 WHEN 3 THEN 三地流动 ELSE 频繁出差 END AS 出差情况 FROM t_employee;三、分组函数聚合函数多行合并的力量分组函数与单行函数不同它们对多行数据进行聚合最终返回一行结果。最常见的分组函数有函数说明avg(x)求平均值sum(x)求和max(x)求最大值min(x)求最小值count(x)计数重要规则count(*) 和 count(1) 都统计所有行包括NULL值。count(字段) 统计该字段非NULL的行数。分组函数会忽略NULL值除了count(*)。实战场景1统计公司员工总数SELECT COUNT(*) FROM t_employee; SELECT COUNT(1) FROM t_employee; SELECT COUNT(eid) FROM t_employee; -- 如果eid为主键结果相同 SELECT COUNT(commission_pct) FROM t_employee; -- 只统计有奖金的员工数实战场景2计算公司平均薪资、最高薪资、最低薪资、薪资总和SELECT AVG(salary) AS 平均薪资, MAX(salary) AS 最高薪资, MIN(salary) AS 最低薪资, SUM(salary) AS 总薪资 FROM t_employee;实战场景3与GROUP BY结合实现分组统计查询每个部门的平均薪资并保留两位小数。SELECT did, ROUND(AVG(salary), 2) AS 部门平均薪资 FROM t_employee GROUP BY did;这里GROUP BY将数据按部门分组分组函数在每个组内分别计算最终每个部门返回一行结果。四、窗口函数OLAP函数SQL中的数据分析神器窗口函数是MySQL 8.0引入的强大特性它能在不减少结果集行数的情况下对每一行数据进行分组排序、前后行访问等操作。窗口函数也属于单行函数因为输入多少行输出还是多少行。语法格式函数名(参数) OVER ( [PARTITION BY 列名] -- 分组 [ORDER BY 列名] -- 排序 [ROWS BETWEEN start AND end] -- 定义窗口范围 )常用窗口函数函数说明row_number()顺序编号1,2,3,...rank()跳跃排序相同值编号相同但会跳过后续编号如1,1,3dense_rank()密集排序相同值编号相同不跳过如1,1,2lag(expr, offset, default)访问当前行前offset行的值lead(expr, offset, default)访问当前行后offset行的值first_value(expr)窗口内第一个值last_value(expr)窗口内最后一个值sum() over() 等聚合函数在窗口内做聚合计算实战场景1给每行记录编号查询薪资在8000~10000之间的员工并为每一行添加序号。SELECT ROW_NUMBER() OVER() AS rn, ename, salary FROM t_employee WHERE salary BETWEEN 8000 AND 10000;实战场景2分组排序比较三种编号函数的区别查询女员工按部门分组在组内按薪资升序排序分别用三种编号函数。SELECT ename, did, salary, ROW_NUMBER() OVER(PARTITION BY did ORDER BY salary) AS row_num, RANK() OVER(PARTITION BY did ORDER BY salary) AS rank_num, DENSE_RANK() OVER(PARTITION BY did ORDER BY salary) AS dense_rank_num FROM t_employee WHERE gender 女;通过对比结果你会清晰看到三者差异ROW_NUMBER 始终连续即使薪资相同也会给不同编号。RANK 遇到相同薪资编号相同但会跳过后续编号。DENSE_RANK 相同薪资编号相同但后续编号连续。实战场景3查询每个部门薪资排名前三的员工利用DENSE_RANK可以实现不跳过的排名确保前三名可能包含并列。SELECT * FROM ( SELECT ename, did, salary, DENSE_RANK() OVER(PARTITION BY did ORDER BY salary DESC) AS dr FROM t_employee ) t WHERE dr 3;实战场景4查询全公司薪资排名前三的员工不需要PARTITION BY直接对整个结果集排序编号。SELECT * FROM ( SELECT ename, did, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS dr FROM t_employee ) t WHERE dr 3;实战场景5访问前后行数据使用LAG和LEAD可以获取排序后的前后行信息非常适合对比分析。SELECT ename, salary, LAG(ename, 1, -) OVER(ORDER BY salary) AS 上一位姓名, LAG(salary, 1, 0) OVER(ORDER BY salary) AS 上一位薪资, LEAD(ename) OVER(ORDER BY salary) AS 下一位姓名, LEAD(salary) OVER(ORDER BY salary) AS 下一位薪资, FIRST_VALUE(salary) OVER(ORDER BY salary) AS 首位薪资, LAST_VALUE(ename) OVER(ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 末位姓名 FROM t_employee;注意LAST_VALUE默认窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW即当前行及之前所有行。若要获取整个分组的最后一行必须显式指定窗口范围为UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。实战场景6计算部门平均薪资与全公司平均薪资的差值使用聚合函数作为窗口函数可以在保留所有行的情况下同时获得分组聚合值和整体聚合值。SELECT DISTINCT did, AVG(salary) OVER(PARTITION BY did) AS 部门平均, AVG(salary) OVER() AS 公司平均, ROUND(AVG(salary) OVER(PARTITION BY did) - AVG(salary) OVER(), 2) AS 差值 FROM t_employee;DISTINCT是为了避免每个员工都重复显示相同部门的平均值因为部门平均值在同一个部门内是相同的。五、总结通过上面的梳理你应该已经看到MySQL的系统函数涵盖了从基础运算到高级分析的方方面面。掌握这些函数意味着你可以在数据库层面完成绝大部分数据加工任务而不必依赖外部编程语言。回顾一下核心要点数学函数处理数值计算尤其注意ROUND、CEIL、FLOOR在财务和统计场景中的差异。字符串函数数据清洗的核心SUBSTRING、REPLACE、TRIM是日常高频操作。日期时间函数时间维度的分析利器DATEDIFF、DATE_ADD、DATE_FORMAT必须熟练。加密函数用户密码等敏感信息一定要用SHA2等强加密算法存储。条件判断函数IF、IFNULL、CASE让SQL拥有分支逻辑实现复杂展示需求。分组函数配合GROUP BY实现聚合统计注意COUNT与COUNT(*)的区别。窗口函数数据分析的进阶武器在不减少行数的情况下进行排序、前后行访问、分组聚合。最后我想说的是SQL不是简单的取数工具而是一门功能完备的数据处理语言。当你遇到复杂需求时先想一想这个逻辑能不能用MySQL函数解决很多时候答案都是肯定的。希望这篇文章能帮你建立起系统函数的全局认知并在实际工作中大胆应用。如果你觉得有用欢迎收藏、转发也欢迎在评论区交流你在SQL使用中遇到的坑和技巧。
掌握MySQL这些函数，SQL水平直接起飞！
发布时间：2026/5/27 4:01:21
作为一名写了多年SQL的老兵我见过太多开发者在面对复杂查询时第一反应就是“把数据捞出来用Java/Python慢慢算”。其实MySQL内置了极其丰富的系统函数掌握了它们你不仅能写出更优雅的SQL还能让数据库替你完成大量计算工作性能远超应用层处理。今天我带你系统梳理MySQL的系统预定义函数。从简单的数学运算到复杂的窗口分析从字符串截取到日期计算每一类函数我都会结合实际业务场景给出示例。读完这篇文章你会彻底明白原来那些需要几十行代码才能搞定的业务逻辑用一条SQL就能轻松拿下。一、函数是什么在MySQL中函数是一个封装好的、可复用的功能单元。你可以把它想象成一个“黑盒”扔进参数也可以不扔它经过内部运算后吐出一个结果。函数必须有返回值即使没有任何参数也必须返回一个值函数可以接受参数也可以不接受函数不会修改原始数据只对查询结果进行加工根据处理方式不同MySQL的系统预定义函数分为两大类单行函数对每一行记录分别处理输入多少行输出多少行。例如把每个员工的名字转成大写或者计算每个员工的实发工资。分组函数聚合函数对多行记录一起运算最终只返回一行或少数几行结果。例如计算全公司平均薪资或者统计每个部门的员工人数。下面我们逐一深入。二、单行函数逐行处理的利器单行函数像生产线上的工人每经过一行数据就处理一行最终输出同样行数的结果。1. 数学函数让数字计算更精确函数说明示例abs(x)绝对值ABS(-10) → 10ceil(x)向上取整CEIL(3.14) → 4floor(x)向下取整FLOOR(3.99) → 3mod(x,y)求余数MOD(10,3) → 1rand()返回0~1之间的随机数常用于随机抽样round(x,y)四舍五入保留y位小数ROUND(3.1415,2) → 3.14truncate(x,y)直接截断不四舍五入TRUNCATE(3.1415,2) → 3.14format(x,y)格式化数字每三位加逗号返回字符串FORMAT(1234567.89,2) → 1,234,567.89sqrt(x)平方根SQRT(9) → 3pow(x,y)x的y次方POW(2,3) → 8实战场景计算员工旷工一天的扣款金额假设当月22个工作日。SELECT ename, salary/22 AS 原始扣款, CEIL(salary/22) AS 向上取整扣款, FLOOR(salary/22) AS 向下取整扣款, ROUND(salary/22,2) AS 四舍五入扣款, TRUNCATE(salary/22,2) AS 截断扣款 FROM t_employee;在实际业务中薪资计算通常使用ROUND因为四舍五入更符合财务规范而CEIL和FLOOR可能用于某些特殊场景比如罚款最低按元计。另一个场景统计公司平均薪资并分别用不同取整方式展示方便汇报。SELECT AVG(salary) AS 原始平均, CEIL(AVG(salary)) AS 向上取整平均, FLOOR(AVG(salary)) AS 向下取整平均, ROUND(AVG(salary),2) AS 四舍五入平均, TRUNCATE(AVG(salary),2) AS 截断平均 FROM t_employee;2. 字符串函数数据清洗的瑞士军刀字符串处理是数据分析中最耗时的一环MySQL提供了丰富的字符串函数让你在SQL层面完成清洗、格式化、截取等操作。函数说明示例concat(s1,s2,...)拼接字符串CONCAT(Hello, , World) → Hello Worldconcat_ws(a,s1,s2,...)用指定分隔符拼接CONCAT_WS(-, 2026, 03, 28) → 2026-03-28char_length(s)返回字符数CHAR_LENGTH(你好) → 2length(s)返回字节数与字符集相关LENGTH(你好) → 6UTF8locate(s,str) 或 instr(str,s)返回子串首次出现的位置LOCATE(ab, abcab) → 1upper(s) / lower(s)转大写/小写UPPER(mysql) → MYSQLleft(s,n) / right(s,n)取左边/右边n个字符LEFT(abcdef,3) → abclpad(str,len,pad) / rpad(str,len,pad)用指定字符左/右填充至指定长度LPAD(123,5,0) → 00123trim(s)去除两侧空格TRIM( hello ) → helloreplace(str,a,b)将str中的a替换为bREPLACE(abcabc,a,x) → xbcxbcrepeat(s,n)重复s n次REPEAT(ab,3) → abababreverse(s)反转字符串REVERSE(abc) → cbasubstring(str,index,len)从index位置截取len个字符SUBSTRING(abcdef,2,3) → bcdsubstring_index(str,分隔符,count)按分隔符截取count为正从左数为负从右数SUBSTRING_INDEX(www.atguigu.com,.,2) → www.atguigu实战场景1脱敏显示姓名将员工姓名处理成“张xx”的样式只保留姓氏其余用x替代。SELECT ename, RPAD(LEFT(ename,1), CHAR_LENGTH(ename), x) AS 脱敏姓名 FROM t_employee;实战场景2统计字符串中逗号的数量员工的work_place字段是用逗号分隔的工作地点比如“北京,上海,广州”。要统计工作地点个数可以用REPLACE巧妙实现。SELECT ename, work_place, CHAR_LENGTH(work_place) - CHAR_LENGTH(REPLACE(work_place, ,, )) 1 AS 地点数量 FROM t_employee;原理原字符串长度减去去掉逗号后的长度得到逗号个数再加1就是地点数量。实战场景3提取邮箱前缀从email字段中提取前面的用户名。SELECT ename, email, SUBSTRING(email, 1, LOCATE(, email) - 1) AS 邮箱前缀 FROM t_employee;实战场景4去掉字符串两端指定字符TRIM不仅可以去除空格还可以去除任意字符。SELECT TRIM(BOTH FROM hello); -- 结果hello SELECT TRIM(LEADING FROM hello); -- 结果hello SELECT TRIM(TRAILING FROM hello);-- 结果hello3. 日期时间函数时间维度的分析利器日期时间处理是报表和统计中最常见的需求MySQL提供了从获取当前时间到计算时间差的完整工具集。函数说明示例curdate() / current_date()当前系统日期CURDATE() → 2026-03-28curtime() / current_time()当前系统时间CURTIME() → 14:30:00now() / sysdate() / localtime()当前日期时间NOW() → 2026-03-28 14:30:00utc_date() / utc_time()UTC日期/时间UTC_DATE() → 2026-03-28unix_timestamp(date)返回Unix时间戳UNIX_TIMESTAMP(2000-01-01) → 946684800year(date) / month(date) / day(date)提取年/月/日YEAR(NOW()) → 2026hour(time) / minute(time) / second(time)提取时/分/秒HOUR(NOW()) → 14extract(type from date)提取指定部分EXTRACT(YEAR_MONTH FROM NOW()) → 202603datediff(date1, date2)计算日期差天数DATEDIFF(2026-03-28,2000-01-01) → 9582timediff(time1, time2)计算时间差时分秒TIMEDIFF(12:00:00,09:00:00) → 03:00:00date_add(date, interval expr type)日期加法DATE_ADD(NOW(), INTERVAL 1 DAY)date_format(datetime, fmt)格式化日期DATE_FORMAT(NOW(), %Y年%m月%d日) → 2026年03月28日str_to_date(str, fmt)字符串转日期STR_TO_DATE(2026-03-28, %Y-%m-%d)实战场景1查询本月生日的员工使用MONTH()提取生日月份与当前月份比较。SELECT ename, birthday FROM t_employee WHERE MONTH(CURDATE()) MONTH(birthday);实战场景2查询入职超过5年的员工使用DATEDIFF计算天数差与5*365比较更精确的方式是用DATE_ADD。SELECT ename, hiredate FROM t_employee WHERE DATEDIFF(CURDATE(), hiredate) 365 * 5;但注意如果考虑闰年更严谨的写法是WHERE hiredate DATE_SUB(CURDATE(), INTERVAL 5 YEAR);实战场景3格式化日期输出报表中经常需要将日期显示为特定格式。SELECT DATE_FORMAT(NOW(), %Y年%m月%d日 %H:%i:%s) AS 当前时间;常用格式符%Y四位年份%m两位月份%d两位日期%H24小时制小时%i分钟%s秒实战场景4计算距离中午放学还有多久学校场景假设中午放学是12:00。SELECT TIMEDIFF(12:00:00, CURTIME()) AS 距离放学;4. 加密函数数据安全的守门员在用户登录、敏感信息存储等场景加密函数必不可少。需要注意的是MySQL 8.0已移除PASSWORD()函数推荐使用更安全的SHA2。函数说明返回值长度md5(str)返回32位十六进制字符串32字符sha(str)返回40位十六进制字符串40字符sha2(str, hash_length)hash_length可以是224/256/384/512/00等同于256根据参数而定实战场景用户密码加密存储假设用户注册时密码是“123456”我们存储其MD5值登录时比对加密后的结果。-- 创建用户表 CREATE TABLE t_user ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20), password VARCHAR(100) ); -- 插入时加密 INSERT INTO t_user VALUES(NULL, chai, MD5(123456)); -- 登录验证 SELECT * FROM t_user WHERE usernamechai AND passwordMD5(123456);实际生产环境建议使用SHA2因为MD5已不再安全可通过彩虹表快速破解。5. 系统信息函数了解当前环境有时我们需要在SQL中获取当前数据库名、版本号等信息这些系统信息函数非常有用。函数说明database()当前使用的数据库名version()MySQL服务器版本user()当前登录用户名和主机名SELECT DATABASE(), VERSION(), USER();在编写自动化脚本或调试时这些函数可以帮助你快速确认当前环境。6. 条件判断函数让SQL拥有分支逻辑条件判断函数是SQL实现复杂业务逻辑的关键。它们不是用来筛选记录的而是根据条件的不同返回不同的显示结果。函数说明if(a, x, y)如果a为真返回x否则返回yifnull(x, y)如果x不为NULL返回x否则返回ycase when ... then ... else ... end多条件判断case 表达式 when 常量1 then 值1 ... end等值判断实战场景1根据薪资水平显示等级SELECT ename, salary, CASE WHEN salary 20000 THEN 羡慕级别 WHEN salary 15000 THEN 努力级别 WHEN salary 10000 THEN 平均级别 ELSE 保底级别 END AS 等级 FROM t_employee;注意CASE按顺序匹配一旦满足条件就停止所以条件顺序很重要。实战场景2处理NULL值计算实发工资时如果commission_pct为NULL则按0计算。SELECT ename, salary, commission_pct, salary salary * IFNULL(commission_pct, 0) AS 实发工资 FROM t_employee;IFNULL在这里发挥了关键作用避免了计算结果变成NULL。实战场景3用CASE表达式进行等值匹配假设要根据工作地点数量判断员工出差频率。SELECT ename, work_place, CASE CHAR_LENGTH(work_place) - CHAR_LENGTH(REPLACE(work_place, ,, )) 1 WHEN 1 THEN 只在本地工作 WHEN 2 THEN 两地奔波 WHEN 3 THEN 三地流动 ELSE 频繁出差 END AS 出差情况 FROM t_employee;三、分组函数聚合函数多行合并的力量分组函数与单行函数不同它们对多行数据进行聚合最终返回一行结果。最常见的分组函数有函数说明avg(x)求平均值sum(x)求和max(x)求最大值min(x)求最小值count(x)计数重要规则count(*) 和 count(1) 都统计所有行包括NULL值。count(字段) 统计该字段非NULL的行数。分组函数会忽略NULL值除了count(*)。实战场景1统计公司员工总数SELECT COUNT(*) FROM t_employee; SELECT COUNT(1) FROM t_employee; SELECT COUNT(eid) FROM t_employee; -- 如果eid为主键结果相同 SELECT COUNT(commission_pct) FROM t_employee; -- 只统计有奖金的员工数实战场景2计算公司平均薪资、最高薪资、最低薪资、薪资总和SELECT AVG(salary) AS 平均薪资, MAX(salary) AS 最高薪资, MIN(salary) AS 最低薪资, SUM(salary) AS 总薪资 FROM t_employee;实战场景3与GROUP BY结合实现分组统计查询每个部门的平均薪资并保留两位小数。SELECT did, ROUND(AVG(salary), 2) AS 部门平均薪资 FROM t_employee GROUP BY did;这里GROUP BY将数据按部门分组分组函数在每个组内分别计算最终每个部门返回一行结果。四、窗口函数OLAP函数SQL中的数据分析神器窗口函数是MySQL 8.0引入的强大特性它能在不减少结果集行数的情况下对每一行数据进行分组排序、前后行访问等操作。窗口函数也属于单行函数因为输入多少行输出还是多少行。语法格式函数名(参数) OVER ( [PARTITION BY 列名] -- 分组 [ORDER BY 列名] -- 排序 [ROWS BETWEEN start AND end] -- 定义窗口范围 )常用窗口函数函数说明row_number()顺序编号1,2,3,...rank()跳跃排序相同值编号相同但会跳过后续编号如1,1,3dense_rank()密集排序相同值编号相同不跳过如1,1,2lag(expr, offset, default)访问当前行前offset行的值lead(expr, offset, default)访问当前行后offset行的值first_value(expr)窗口内第一个值last_value(expr)窗口内最后一个值sum() over() 等聚合函数在窗口内做聚合计算实战场景1给每行记录编号查询薪资在8000~10000之间的员工并为每一行添加序号。SELECT ROW_NUMBER() OVER() AS rn, ename, salary FROM t_employee WHERE salary BETWEEN 8000 AND 10000;实战场景2分组排序比较三种编号函数的区别查询女员工按部门分组在组内按薪资升序排序分别用三种编号函数。SELECT ename, did, salary, ROW_NUMBER() OVER(PARTITION BY did ORDER BY salary) AS row_num, RANK() OVER(PARTITION BY did ORDER BY salary) AS rank_num, DENSE_RANK() OVER(PARTITION BY did ORDER BY salary) AS dense_rank_num FROM t_employee WHERE gender 女;通过对比结果你会清晰看到三者差异ROW_NUMBER 始终连续即使薪资相同也会给不同编号。RANK 遇到相同薪资编号相同但会跳过后续编号。DENSE_RANK 相同薪资编号相同但后续编号连续。实战场景3查询每个部门薪资排名前三的员工利用DENSE_RANK可以实现不跳过的排名确保前三名可能包含并列。SELECT * FROM ( SELECT ename, did, salary, DENSE_RANK() OVER(PARTITION BY did ORDER BY salary DESC) AS dr FROM t_employee ) t WHERE dr 3;实战场景4查询全公司薪资排名前三的员工不需要PARTITION BY直接对整个结果集排序编号。SELECT * FROM ( SELECT ename, did, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS dr FROM t_employee ) t WHERE dr 3;实战场景5访问前后行数据使用LAG和LEAD可以获取排序后的前后行信息非常适合对比分析。SELECT ename, salary, LAG(ename, 1, -) OVER(ORDER BY salary) AS 上一位姓名, LAG(salary, 1, 0) OVER(ORDER BY salary) AS 上一位薪资, LEAD(ename) OVER(ORDER BY salary) AS 下一位姓名, LEAD(salary) OVER(ORDER BY salary) AS 下一位薪资, FIRST_VALUE(salary) OVER(ORDER BY salary) AS 首位薪资, LAST_VALUE(ename) OVER(ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 末位姓名 FROM t_employee;注意LAST_VALUE默认窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW即当前行及之前所有行。若要获取整个分组的最后一行必须显式指定窗口范围为UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。实战场景6计算部门平均薪资与全公司平均薪资的差值使用聚合函数作为窗口函数可以在保留所有行的情况下同时获得分组聚合值和整体聚合值。SELECT DISTINCT did, AVG(salary) OVER(PARTITION BY did) AS 部门平均, AVG(salary) OVER() AS 公司平均, ROUND(AVG(salary) OVER(PARTITION BY did) - AVG(salary) OVER(), 2) AS 差值 FROM t_employee;DISTINCT是为了避免每个员工都重复显示相同部门的平均值因为部门平均值在同一个部门内是相同的。五、总结通过上面的梳理你应该已经看到MySQL的系统函数涵盖了从基础运算到高级分析的方方面面。掌握这些函数意味着你可以在数据库层面完成绝大部分数据加工任务而不必依赖外部编程语言。回顾一下核心要点数学函数处理数值计算尤其注意ROUND、CEIL、FLOOR在财务和统计场景中的差异。字符串函数数据清洗的核心SUBSTRING、REPLACE、TRIM是日常高频操作。日期时间函数时间维度的分析利器DATEDIFF、DATE_ADD、DATE_FORMAT必须熟练。加密函数用户密码等敏感信息一定要用SHA2等强加密算法存储。条件判断函数IF、IFNULL、CASE让SQL拥有分支逻辑实现复杂展示需求。分组函数配合GROUP BY实现聚合统计注意COUNT与COUNT(*)的区别。窗口函数数据分析的进阶武器在不减少行数的情况下进行排序、前后行访问、分组聚合。最后我想说的是SQL不是简单的取数工具而是一门功能完备的数据处理语言。当你遇到复杂需求时先想一想这个逻辑能不能用MySQL函数解决很多时候答案都是肯定的。希望这篇文章能帮你建立起系统函数的全局认知并在实际工作中大胆应用。如果你觉得有用欢迎收藏、转发也欢迎在评论区交流你在SQL使用中遇到的坑和技巧。
相关文章
抖音弹幕抓取终极指南：3分钟掌握系统代理抓包技术
抖音弹幕抓取终极指南：3分钟掌握系统代理抓包技术 【免费下载链接】DouyinBarrageGrab 基于系统代理的抖音弹幕wss抓取程序，能够获取所有数据来源，包括chrome，抖音直播伴侣等，可进行进程过滤 项目地址: https://gitc…
文脉定序系统压力测试与性能监控方案
文脉定序系统压力测试与性能监控方案 最近不少朋友在部署完文脉定序系统后，都会来问我同一个问题：“服务上线了，心里还是没底，怎么知道它能扛住多少用户同时访问？平时运行稳不稳定？” 这确实是个很实际的问…
无需代码！DCT-Net人像卡通化WebUI界面使用全解析
无需代码！DCT-Net人像卡通化WebUI界面使用全解析 1. 为什么选择DCT-Net人像卡通化？ 在当今社交媒体和数字内容创作蓬勃发展的时代，人像卡通化技术已经从专业领域走向大众视野。不同于传统需要Photoshop技巧的复杂操作，DCT-Net通…
InsTagger部署指南：从本地安装到云端服务的完整教程
InsTagger部署指南：从本地安装到云端服务的完整教程 【免费下载链接】InsTagger 项目地址: https://ai.gitcode.com/hf_mirrors/Rose/InsTagger InsTagger是一款基于HuggingFace生态的智能标签生成工具，能够为文本内容自动生成精准标签ÿ…
CANN算子注册表访问器库
libttk_op_registry_accessor.so 【免费下载链接】ops-test-kit TTK（Ops Test Tool Kit）是CANN算子库提供的全链路、自动化、批量化算子测试框架，帮助开发者快速完成算子批量功能验证、性能评估以及Golden值比对，提升算子开发质量…
认知无线电入门：不懂复杂公式？用能量检测法快速理解频谱感知核心
认知无线电入门：不懂复杂公式？用能量检测法快速理解频谱感知核心想象一下你正在一个拥挤的停车场寻找空位。传统无线电就像固定车位的车主——无论是否使用，车位都专属一人。而认知无线电则像智能停车系统，能实时感知空闲车位并灵…
如何优化TinyLlama-1.1B-Chat-v0.4性能：10个实用技巧提升对话质量
如何优化TinyLlama-1.1B-Chat-v0.4性能：10个实用技巧提升对话质量 【免费下载链接】TinyLlama-1.1B-Chat-v0.4 项目地址: https://ai.gitcode.com/hf_mirrors/LF_AICC/TinyLlama-1.1B-Chat-v0.4 TinyLlama-1.1B-Chat-v0.4是一个紧凑高效的1.1B参数对话模型&…
从TensorFlow到PyTorch：CICC/gtr-t5-large模型转换的完整技术指南
从TensorFlow到PyTorch：CICC/gtr-t5-large模型转换的完整技术指南 【免费下载链接】gtr-t5-large 项目地址: https://ai.gitcode.com/hf_mirrors/CICC/gtr-t5-large 在深度学习领域，模型框架转换是一个常见但技术性很强的任务。今天我们将深入解…
30.全品牌救砖教程！Bootloader 解锁 + 分区重刷 + 底层故障修复实操
摘要 本文针对主流品牌手机（华为、小米、OPPO、vivo、一加、苹果）的刷机与维修操作，提供一套基于底层原理的通用方法论与可复现的工程流程。内容涵盖Bootloader解锁、分区写入、固件签名校验绕过、基带修复等核心环节，并附完整可运行的Python自动化脚本（基于ADB与Fastboo…
LVGL绘制平滑曲线避坑指南：为什么你的贝塞尔函数有毛刺？
LVGL绘制平滑曲线避坑指南：为什么你的贝塞尔函数有毛刺？ 在嵌入式GUI开发中，贝塞尔曲线是实现流畅动画和优雅界面的核心工具。但许多开发者在使用LVGL绘制曲线时，总会遇到令人头疼的锯齿和毛刺问题。这背后隐藏着嵌入式设备特有的…
告别手动输入！用Burpsuite插件captcha-killer-modified+ddddocr，5分钟搞定登录爆破验证码
自动化验证码识别实战：Burpsuite与ddddocr的高效联动方案验证码机制作为现代Web应用的基础安全防线，其对抗自动化攻击的能力直接影响系统安全性。但在安全测试领域，验证码往往成为效率瓶颈——传统手工识别方式让渗透测试人员每天浪费数小时在…
中国AI岗位暴涨12倍，13种你没听过的AI岗位
2026年，中国AI岗位数量同比增长12倍，AI科学家月薪高达13.7万，高性能计算工程师出现“7个岗位抢1个人”的荒诞场面。与此同时，数据录入、基础财务分析、一线客服等岗位大幅下降。全球范围内，AI/ML岗位招聘量同比增长88%…
施工现场安全事故预警准确率达94.6%？——解密某央企AI Agent边缘计算部署架构与3个月落地实录
更多请点击： https://codechina.net 第一章：施工现场安全事故预警准确率达94.6%？——解密某央企AI Agent边缘计算部署架构与3个月落地实录 在华北某大型地铁盾构施工现场，一套轻量化AI Agent系统于2024年Q2完成全栈部署ÿ…
附录 B：术语表
本术语表面向“从 MM 到 HMM”专栏阅读过程中的快速查阅。它不是内核 API 手册，而是把文章中反复出现的概念放到同一张地图上：先给出直观含义，再说明它在 Linux MM/HMM 语境里的作用。建议阅读方式： 初读专栏时，把它当…
Midjourney渐变美学的神经渲染原理（附RGB-HSV-LCH三空间渐变映射对照表·行业首曝）
更多请点击： https://kaifayun.com 第一章：Midjourney渐变美学的神经渲染原理（附RGB-HSV-LCH三空间渐变映射对照表行业首曝） Midjourney 的渐变美学并非传统插值实现，而是由其隐式神经渲染器（Implicit Neu…
MPC-BE：基于DirectShow架构的专业级开源媒体播放解决方案
MPC-BE：基于DirectShow架构的专业级开源媒体播放解决方案 【免费下载链接】MPC-BE MPC-BE – универсальный проигрыватель аудио и видеофайлов для операционной системы Windows. 项目地址:…
如何快速计算3D模型体积和重量：STL-Volume-Model-Calculator终极指南
如何快速计算3D模型体积和重量：STL-Volume-Model-Calculator终极指南 【免费下载链接】STL-Volume-Model-Calculator STL Volume Model Calculator Python 项目地址: https://gitcode.com/gh_mirrors/st/STL-Volume-Model-Calculator 你是否曾经为3D打印项目…
通过Taotoken CLI工具一键配置团队开发环境与模型密钥
通过Taotoken CLI工具一键配置团队开发环境与模型密钥 1. CLI工具安装与基本使用 Taotoken提供的CLI工具可通过npm全局安装或直接使用npx运行。对于需要频繁使用CLI的团队，推荐全局安装： npm install -g taotoken/taotoken对于临时使用或项目级配置&a…