MySQL函数详细解析
目录
前言
本文来讲解一下MySQL中的函数，本文适合用于预习，复习等
旨在帮助读者更好的理解和掌握MySQL的函数
个人主页：主页
系列专栏：专栏
一. MySQL函数简介
每个数据库的函数都会有些差异
SQL函数主要解决有以下目的：
- 执行数据计算（求和，平均值）
- 修改单个数据项
- 操纵输出进行行分组
- 格式化显示的日期和数字
- 转换列数据类型( 当前类型变为另一种类型，解决类型不匹配问题 )
分为：单行函数和多行函数（又叫 聚合函数）
二. 单行函数
单行函数仅仅只对单个行进行运算，并且每行返回一个结果
常见的函数类型：字符、数字、日期、转换、通用
(一) 字符函数
1. 大小写处理函数
| 函数 | 描述 | 例子🌰 |
| lower(s) 或者 lcase(s) | 将字符串 s 转换为小写 | 将字符串 OLDLU 转换为小写： |
| upper(s) 或者 ucase(s) | 将字符串 s 转换为大写 | 将字符串 oldlu 转换为大写： |
这里转换大小写都有两种写法
2. 字符处理
| 函数 | 描述 | 例子🌰 |
| length(s) | 返回字符串 s 的长度 | 返回字符串 oldlu 的字符数： |
| concat(s1,s2...sn) | 字符串 s1, s2 等多个字符串合并为一个字符串 | 合并多个字符串： |
| lpad(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len | 将字符串 x 填充到 oldlu 字符串的开始处：select lpad ('yibei',8,'x'); -- xxxyibei |
| ltrim(s) | 去掉字符串 s 开始处的空格 | 去掉字符串 oldlu 开始处的空格： |
| replace(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 | 将字符串 oldlu 中的字符 o 替换为字符 O：select replace ('yibei','y','M'); -- Mibei |
| reverse(s) | 将字符串 s 的顺序反过来 | 将字符串 abc 的顺序反过来： |
| rpad(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len | 将字符串 xx 填充到 oldlu 字符串的结尾处：select rpad ('yibei',8,'x'); --> yibeixxx |
| rtrim(s) | 去掉字符串 s 结尾处的空格 | 去掉字符串 yibei 的末尾空格： |
| substr(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 yibei 中的第 2 个位置截取 3 个字符：select substr ("yibei", 1, 3); -- yib |
| substring(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 yibei 中的第 2 个位置截取 3 个字符：select substring ("yibei", 1, 3); -- yib |
| trim(s) | 去掉字符串 s 开始和结尾处的空格 | 去掉字符串 yibei 的首尾空格： |
这里列举了10个常用字符函数，并加上了对应的例子
tips：在substr 和 substring 函数功能都是一样的，并且字符都是从1开始（不要受java的影响了O(∩_∩)O哈哈~）
(二) 数字函数
| 函数名 | 描述 | 例子🌰 |
| abs(x) | 返回 x 的绝对值 | 返回 -1 的绝对值： |
| avg(expression) | 返回一个表达式的平均值，expression 是一个字段 | 返回 products 表中 price 字段的平均值： |
| count(expression) | 返回查询的记录总数，expression 参数是一个字段或者 * 号 | 返回 products 表中 productid 字段总共有多少条记录： |
| n div m | 整除，n 为被除数，m 为除数 | 计算 10 除于 5： |
| exp(x) | 返回 e 的 x 次方 | 计算 e 的三次方： |
| greatest(expr1, expr2, expr3, ...) | 返回列表中的最大值 | 返回以下数字列表中的最大值： |
| least(expr1, expr2, expr3, ...) | 返回列表中的最小值 | 返回以下数字列表中的最小值： |
| ln | 返回数字的自然对数，以 e 为底。 | 返回 2 的自然对数： |
| max(expression) | 返回字段 expression 中的最大值 | 返回数据表 products 中字段 price 的最大值： |
| min(expression) | 返回字段 expression 中的最小值 | 返回数据表 products 中字段 price 的最小值：select min (price) as minprice from products; |
| mod(x,y) | 返回 x 除以 y 以后的余数 | 5 除于 2 的余数： |
| pi() | 返回圆周率 (3.141593） | select pi() --> 3.141593 |
| pow(x,y) | 返回 x 的 y 次方 | 2 的 3 次方： |
| rand() | 返回 0 到 1 的随机数 | select rand() -- >0.89279320 |
| round(x) | 对 x 四舍五入 | select round(1.23456) --> 1 |
| sign(x) | 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 | select sign(-10) --> (-1) |
| sqrt(x) | 返回 x 的平方根 | 25 的平方根： |
| sum(expression) | 返回指定字段的总和 | 计算 orderdetails 表中字段 quantity 的总和：select sum (quantity) as totalitemsordered from orderdetails; |
| truncate(x,y) | 返回数值 x 保留到小数点后 y 位的值（与 round 最大的区别是不会进行四舍五入） | select truncate(1.23456,3) --> 1.234 |
这里是常用的数字函数，最常用的就是标红的这三个求余数（mod），四舍五入（round），对浮点数保留多少位小数（truncate）
(三) 日期函数
在MySQL中可以直接使用字符串表示日期，但是要求字符串的日期格式必须要是：
‘YYYY-MM-DD HH:MI:SS’
'2025-3-31 10:10:24'
或者
‘YYYY/MM/DD HH:MI:SS’
'2025/3/31 10:10:24'
| 函数名 | 描述 | 例子🌰 |
| curdate () | 返回当前日期 | select curdate(); -> 2025-03-23 |
| curtime () | 返回当前时间 | select curtime (); |
| current_date () | 返回当前日期 | select current_date(); -> 2025-03-23 |
| current_time () | 返回当前时间 | select current_time (); |
| date () | 从日期或日期时间表达式中提取日期值 | select date(now()); |
| datediff (d1,d2) | 计算日期 d1 -> d2 之间相隔的天数 | select datediff ('2025-03-23', '2025-03-01') |
| day (d) | 返回日期值 d 的日期部分 | select day(curdate()); |
| dayname (d) | 返回日期 d 是星期几(英文名称)，如 monday,tuesday | select dayname(curdate()); |
| dayofmonth (d) | 计算日期 d 是本月的第几天 | select dayofmonth('2025-03-23'); |
| dayofweek (d) | 日期 d 是星期几，1 星期日，2 星期一，以此类推 | select dayofweek('2025-03-23'); |
| dayofyear (d) | 计算日期 d 是本年的第几天 | select dayofyear ('2025-03-23'); |
| hour (t) | 返回 t 中的小时值 | select hour ('22:50:00'); |
| last_day (d) | 返回给定日期的那一月份的最后一天 | select last_day('2025-03-23'); |
| monthname (d) | 返回日期当中的月份名称(英文)，如 november | select monthname('2025-03-23'); |
| month (d) | 返回日期 d 中的月份值，1 到 12 | select month(curdate()); |
| now () | 返回当前日期和时间 | select now (); |
| second (t) | 返回 t 中的秒钟值 | select second ('22:50:23'); |
| sysdate () | 返回当前日期和时间 | select sysdate (); |
| timediff (time1, time2) | 计算时间差值 | select timediff('22:50:23', '20:30:10'); |
| week (d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 | select week ('2025-03-23'); |
| weekday (d) | 日期 d 是星期几 | select weekday('2025-03-23'); |
| weekofyear (d) | 计算日期 d 是本年的第几个星期， | select weekofyear ('2025-03-23'); |
| year (d) | 返回年份 | select year('2025-03-23'); |
这里提供了常见的日期函数
(四) 转换函数
转换函数分为隐式和显式两种
1. 隐式转换
隐式数据类型转换就是MySQL服务器可以自动地进行类型转换。
如：可以将标准格式的字串日期自动转换为日期类型
当日期写成该日期类型时：
‘YYYY-MM-DD HH:MI:SS’ 或
‘YYYY/MM/DD HH:MI:SS’;
系统就会将这个字符串自动转换成日期
2. 显示数据类型转换 可以放什么图
| 函数名 | 例子🌰 |
| date_format (date, format) | select date_format ( now (), '% Y年% m月% d % H时% i分% s' ); |
| str_to_date ( str, format ) | select str_to_date('2025年03月23', '%Y年%m月%d'); |
常用格式
(五) 通用函数
通用函数也是很常用的，能够解决很多问题，例如相乘时，如果有null空值，那么就会出错
因为空值null不是一个值
| 函数名 | 描述 | 例子🌰 |
| if(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2 | select if (1> 0,' 正确 ',' 错误 ') |
| ifnull(v1, v2) | 如果 v1 的值不为 null，则返回 v1，否则返回 v2（返回第一个不为空的值） | select ifnull(null,'hello word') |
| isnull(expression) | 判断表达式是否为 null | select isnull(null); |
| nullif(expr1, expr2) | 比较两个参数是否相同，如果参数 expr1 与 expr2 相等 返回 null | select nullif(25, 25); |
| coalesce( expr1, expr2, ...., expr_n ) | 返回参数中的第一个非空表达式（从左向右） | select coalesce(null, null, null, 'Yibei', null, 'Mnihao' '); -> Yibei |
| case expression when value1 then result1 when value2 then result2 ... when valueN then resultN else default_result end | case 开启 ，end 结束。它会依次计算 when 子句的条件，首个为 true 的条件对应结果会被返回，后续 when 不再计算。若所有条件都为 false，则返回 else 结果（无 else 则返回 null） |
CASE表达式有两种方式
第一种，搜索 CASE 表达式
-- CASE 表达式
select
case
when score >= 90 then '优秀'
when score >= 80 then '良好'
when score >= 60 then '及格'
else '不及格'
-- 将结果命名为grade
end as grade
from students;第二种，普通 CASE 表达式
-- 普通CASE
-- 类似 java中的switch语句
-- 给一个值，来匹配
select
case
floor(score / 10)
when 9 then '优秀'
when 8 then '良好'
when 7 then '良好'
when 6 then '及格'
else '不及格'
-- 这里的as grade 是将这一列的列名设置为grade
end as grade
from
students; 三. 聚合函数（多行函数）
聚合函数就是对一组数据进行查询，返回一个结果
例如：求和等等
所以聚合函数的类型有：
AVG 平均值，COUNT 计数，MAX 最大值，MIN 最小值，SUM 合计
| 函数 | 功能 |
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
-- 对学生成绩求平均分
select avg(score) as student_score from students;
-- 统计学生记录
select count(*) as student_count from students;
-- 求最大成绩
select max(score) as max_score from students;
-- 求最小成绩
select min(score) as min_score from students;
-- 计算学生成绩的总和
select sum(score) as total_score from students;因为聚合函数是对一组数据进行查询的，所以要用到分组
我之前一篇文章里写过分组，这里就不再赘述了
传送门：分组查询
四. 总结
希望本文对你有所帮助😊
后续会推出专门函数练习的文章，敬请期待~~~
扫一扫
2991
到【灌水乐园】发言
