mysql系列——常用的几十个函数详解（七）
文章目录
- 一、Mysql数值型函数
- 二、Mysql字符串函数
- 三、Mysql日期和时间函数
- 四、Mysql聚合函数
- 五、Mysql流程控制函数
- 六、其他函数
本篇主要介绍一下Mysql中常用的一些函数，篇幅较长，请读者耐心阅读和实操，这样收获多多哦！！
一、Mysql数值型函数
| 函数名称 | 作用 |
|---|---|
| abc | 求绝对值 |
| sqrt | 求二次方根 |
| mod | 求余数 |
| ceil 和 ceiling | 功能一样，都是返回不小于参数的最小整数，即向上取整 |
| floor | 向下取整，返回值转化为一个bigint |
| rand | 生成一个0~1之间的随机数，传入整数参数是用来重复序列 |
| round | 对所参数进行四舍五入 |
| sign | 返回参数的符号 |
| pow 和 power | 功能一样，都是输出所传参数的次方的结果值 |
| sin | 求正弦值 |
| asin | 求反正弦值，与函数sin互为反函数 |
| cos | 求余弦值 |
| acos | 求反余弦值，与函数cos互为反函数 |
| tan | 求正切值 |
| atan | 求反正切值，与函数tan互为反函数 |
| cot | 求余切值 |
1、abs()：求绝对值
函数abs(x)返回x的绝对值。正数的绝对值是其本身，负数的绝对值为其相反数，0的绝对值是0本身。
2、sqrt()：求二次方根（开平方）
函数sqrt(x)返回非负数x的二次方根。负数没有平方根，返回结果为null。
3、mod：求余数
函数mode(x,y)返回x被y除后的余数，mod()对带有小数部分的数组也起作用，它返回除法运算后的余数。
4、ceil和ceiling：向上取整
函数ceil(x)和ceiling(x)的意义相同，返回不下于x的最小整数值，返回值转化为一个bigint。
5、floor：向下取整
floor(x)函数返回小于x的最大整数值。
6、rand：随机生成一个0~1的随机数
生成一个0~1的随机数，传入整数参数是用来产生重复序列。
7、round：四舍五入函数
返回最接近于参数X的整数；round(x,y)函数对参数进行四舍五入的操作，返回值保留小数点后面指定的y位。
8、sign：返回参数的符号
函数sign(x)返回参数的符号，X的值为负、零和正时，返回结果依次为：-1、0和1。
9、pow 和 power：次方函数
pow(x,y)函数和power(x,y)函数用于计算x的y次方。
10、sin：正弦函数
sin(x)返回x的正弦值，其中X为弧度值。
注：pi()函数返回圆周率（3.141593）
其他几个三角函数在此就不一一举例说明，，有兴趣的读者可自己练习一下。
二、Mysql字符串函数
| 函数名称 | 作用 |
|---|---|
| length | 计算字符串长度函数，返回字符串的字节长度 |
| concat | 合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个 |
| insert | 替换字符串函数 |
| lower | 将字符串中的字母转换为小写 |
| upper | 将字符串中的字母转换为大写 |
| left | 从左侧截取字符串，返回字符串左边的若干字符 |
| right | 从右侧截取字符串，返回字符串右边的若干字符 |
| trim | 删除字符串左右两侧的空格 |
| replace | 字符串替换函数，返回替换后的新字符串 |
| substr 和substring | 截取字符串，返回从指定位置开始的指定长度的字符串 |
| reverse | 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 |
1、length：返回字符串字节长度
返回值为字符串的字节长度，使用utf8（unicode的一种变长字符编码，称万国码）编码字符集时，一个汉字是3个字节，一个数字或字母是一个字节。
2、concat：合并字符串
函数concat(s1,s2,…)返回结果为连接参数产生的字符串，可有一个或多个连接参数。
- 若有任何一个连接参数为null，则返回值为null；
- 若所有参数均为非二进制字符串，则结果为非二进制字符串；
- 若自变量中含有任一二进制字符串，则结果为一个二进制字符串。
3、insert：替换字符串函数
函数insert(s1,x,len,s2)返回字符串s1，s1子字符串起始于x位置，并且len个字符的子字符串被代替s2。x的值从1开始，第一个字符的x=1。
- 若x超过字符串长度，则返回值为原始字符串s1；
- 若len的长度大于s2字符串长度，则s1字符串从位置x开始被s2替换；
- 若任何一个参数为null，则返回值为null
4、lower：将字母转换成小写
lower(str)函数可以将字符串str中的字母字符全部转换成小写。
5、upper：将字母转换成大写
upper(str)函数可以将字符串str中的字母字符全部转换成大写。
6、left：从左侧截取字符串
left(str,n)函数返回字符串str最左边的n个字符，str=1表示1个字符。
7、right：从右侧截取字符串
right(str,n)函数返回字符串str最右边的n个字符。
8、tirm：删除字符串两侧空格
tiem(str)函数删除字符串str两侧的空格。
9、replace：字符串替换
函数replace(s,s1,s2)使用字符串s2替换字符串s中所有的字符串s1。
10、substr 和 substring：截取字符串
- substr(str,pos)
- substr(str from pos)
- substr(str,pos,len)
- substr(str from pos for len)
substr是和substring()的同义词。
- 没有len参数的形式是字符串str从pos位置开始返回一个子字符串；
- 带有len参数的形式是字符串str从pos位置开始返回长度为len的子字符串；
- 使用from的形式是标准的SQL语法；
- 也可以对pos使用负值，在这种情况下，子字符串的开头是字符串末尾的pos字符，而不是开头。
- 在这个函数的任何形式中pos可以使用负值；
- 对于所有形式的substring()，从中提取子串的字符串中第一个字符的位置被认为是1。
/** 第三个字符之后的子字符串：inese **/
SELECT substring('chinese', 3);
/** 倒数第三个字符之后的子字符串：ese **/
SELECT substring('chinese', -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese', 3, 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese', -3, 2);
/** 第三个字符之后的子字符串：inese **/
SELECT substring('chinese' FROM 3);
/** 倒数第三个字符之后的子字符串：ese **/
SELECT substring('chinese' FROM -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese' FROM 3 FOR 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese' FROM -3 FOR 2);11、reverse：字符串反转函数
reverse(s)可以将字符串s反转，返回的字符串顺序与s字符串顺序相反。
三、Mysql日期和时间函数
| 函数名称 | 作用 |
|---|---|
| curdate 和 current_date | 作用相同，返回当前系统的日期值 |
| curtime 和 current_time | 作用相同，返回当前系统的时间值 |
| now 和 sysdate | 作用相同，返回当前系统的日期值和时间值 |
| unix_timestamp | 获取unix时间戳函数，返回一个以unix时间戳为基础的无符号整数 |
| from_unixtime | 将unix时间戳转换为时间格式，与unix_timestamp互为反函数 |
| day | 获取指定日期中的日期 |
| dayname | 获取指定日期中对应的星期几的英文名称 |
| month | 获取指定日期中的月份 |
| monthname | 获取指定日期中的月份英文名称 |
| year | 获取指定日期中的年份，范围1970 ~ 2069 |
| dayofweek | 获取指定日期是一周中的第几天，返回值范围是1~7，1=周日 |
| dayofmonth | 获取指定日期是一月中的第几天，返回值范围是1~31 |
| dayofyear | 获取指定日期是一年中的第几天，返回值范围是1~366 |
| week | 获取指定日期是一年中的第几周，返回值的范围是0 ~ 52或1 ~ 53 |
| time_to_sec | 将时间参数转换为秒数 |
| sec_to_time | 将秒数转换为时间，与time_to_sec互为反函数 |
| date_add 和 adddate | 功能相同，都是向日期添加指定的时间间隔 |
| date_sub 和 subdate | 功能相同，都是向日期减去指定的时间间隔 |
| addtime | 时间加法运算，在原始时间上添加指定的时间 |
| subtime | 时间减法运算，在原始时间上减去指定的时间 |
| datediff | 获取两个日期之间间隔，返回参数1减去参数2的值 |
| date_format | 格式化指定日期，根据参数返回指定格式的值 |
| weekday | 获取指定日期在一周内的对应的工作索引 |
1、curdate 和 current_date：返回当前系统的日期值
curdate()和current_date()函数的作用相同，将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回，具体格式根据函数用在字符串或数字语境中而定，返回的 date 类型。
2、curtime 和 current_time：返回当前系统的时间值
curtime()和current_time()函数的作用相同，将当前日期按照“HH-MM-SS”或“HHMMSS”格式的值返回，具体格式根据函数用在字符串或数字语境中而定，返回的 time 类型。
3、now 和 sysdate：获取当前系统时间日期
now()和sysdate()函数作用相同，都是返回当前日期和时间值，格式为“YYYY-MM-DD HH:MM:SS”或“YYMMDDHHMMSS”，具体格式根据函数用在字符串或数字语境中而定，返回的 datetime 类型。
4、unix_timestamp：获取UNIX时间戳
unix_timestamp(date)若无参数调用，返回一个无符号整数类型的unix时间戳（'1970-01-01 00:00:00’GMT之后的秒数）。
5、from_unixtime：时间戳转日期
from_unixtime(unix_timestamp[,format])函数把unix时间戳转换为普通格式的日期时间值，与unix_timestamp()函数互为反函数。
有两个参数：
- unix_timestamp：时间戳（秒）
- format：要转换的格式。比如“%Y-%m-%d”，这样的格式化之后就是 xxxx-xx-xx的模样。
可以有的形式：
| 格式 | 说明 |
|---|---|
| %M | 月名字（January ~ December） |
| %W | 星期名字（Sunday ~ Saturday） |
| %w | 一个星期中的天数（0=Sunday ~ 6=Saturday） |
| %D | 有英语前缀的月份的日期（1st，2nd，3rd，等等） |
| %Y | 年，数字，4位 |
| %y | 年，数字，2位 |
| %a | 缩写的星期名字（Sun ~ sat |
| %d | 月份中的天数（00 ~ 31） |
| %e | 月份中的天数（0 ~ 32） |
| %m | 月，数字（01 ~ 12) |
| %c | 月，数字（1 ~ 12） |
| %b | 缩写的月份名字（Jan ~ Dec） |
| %j | 一年中的天数（001 ~ 366） |
| %H | 小时（00 ~ 23） |
| %k | 小时（0 ~ 23） |
| %h | 小时（01 ~ 12） |
| %I（i的大写） | 小时（01 ~ 12） |
| %l（L的小写） | 小时（1 ~ 12） |
| %i | 分钟，数字（00 ~ 59） |
| %r | 时间，12小时（hh:mm:ss[AP]M） |
| %T | 时间，24小时（hh:mm:ss） |
| %S | 秒（00 ~ 59） |
| %s | 秒（00 ~ 59） |
| %p | AM或PM |
| %U | 星期（0 ~ 52），这里星期天是星期的第一天 |
| %u | 星期（0 ~ 52），这里星期天是星期的第一天 |
| %% | 输出% |
6、month：获取指定日期的月份
month(date)函数返回指定date对应的月份。范围为1 ~ 12。
7、monthname：获取指定日期的月份的英文名称
monthname(date)函数返回指定date对应的月份的英文全名。
8、dayname：获取指定日期的星期英文名称
dayname(date)函数返回指定date对应的工作日的英文全名。
9、dayofweek：获取日期对应的周索引
dayofweek(date)函数返回date对应的一周的索引（位置）。1表示周日，…，7表示周六。这些索引值对应ODBC标准。
10、week：获取指定日期是一年中的第几周
week(date[,mode])函数计算日期date是一年中的第几周。week(date,mode)函数允许指定星期是否起始于周日或周一，以及返回值的返回是否为 0 ~ 52 或 1 ~ 53。
week函数接受两个参数：
- date 是要获取周数的日期；
- mode 是一个可选参数，用于确定周数计算的逻辑。它允许你指定本周是从星期一还是星期日开始，返回的周数应在0 ~ 52 或 1 ~ 53。
如果忽略mode参数，默认情况下week函数将使用default_week_format系统变量的值。要获取default_week_format变量的当前值，请使用 show variables 语句。如下：
在我们的Mysql服务器中，default_week_format的默认值为0，下表格说明了mode参数如何影响week函数：
| 模式 | 一周的第一天 | 范围 |
|---|---|---|
| 0 | 星期日 | 0 - 53 |
| 1 | 星期一 | 0 - 53 |
| 2 | 星期日 | 1 - 53 |
| 3 | 星期一 | 1 - 53 |
| 4 | 星期日 | 0 - 53 |
| 5 | 星期一 | 0 - 53 |
| 6 | 星期日 | 1 - 53 |
| 7 | 星期一 | 1 - 53 |
上表中“今年有4天以上”表示：
- 如果星期包含了1月1日，并且在新的一年中有4天或更多天，那么这周是第1周；
- 否则，这一周的数字是前一年的最后一周，下周是第一周。
11、dayofyear：获取指定日期在一年中的位置
dayofyear(date)函数返回date是一年中的第几天，范围1 ~ 366。
12、dayofmonth：获取指定日期在一月中的位置
dayofmonth(date)函数返回date是一月中的第几天，范围1 ~ 31。
13、year：获取年份
year()函数可以从指定日期值中来获取年份值。
14、time_to_sec：将时间转换为秒值
time_to_sec(time)函数返回将参数time转换为秒数的时间值，转换公式为“ 小时 * 3600 + 分钟 * 60 + 秒 ”。
15、sec_to_time：将秒值转换为时间
sec_to_time(seconds)函数返回将参数seconds转换为小时、分钟和秒数的时间值。
16、date_add 和 adddate：向日期添加指定时间间隔
date_add(date,INTERVAL expr type)
- date：参数是合法的日期表达式；
- expr：参数是你希望添加的时间间隔；
- type：参数可以是下列值：
- 对应复合型的type，需要使用引号对两个参数进行引用起来，中间用任何非数字字符作为间隔即可，并且不能使用负数
| Type值 | 含义 |
|---|---|
| MICROSECOND | 间隔单位：毫秒 |
| SECOND | 间隔单位：秒 |
| MINUTE | 间隔单位：分钟 |
| HOUR | 间隔单位：小时 |
| DAY | 间隔单位：天 |
| WEEK | 间隔单位：星期 |
| MONTH | 间隔单位：月 |
| QUARTER | 间隔单位：季度 |
| YEAR | 间隔单位：年 |
| SECOND_MICROSECOND | 复合型，间隔单位：秒、毫秒，expr可以用两个值来分别指定秒和毫秒 |
| MINUTE_MICROSECOND | 复合型，间隔单位：分、毫秒 |
| MINUTE_SECOND | 复合型，间隔单位：分、秒 |
| HOUR_MICROSECOND | 复合型，间隔单位：小时、毫秒 |
| HOUR_SECOND | 复合型，间隔单位：小时、秒 |
| HOUR_MINUTE | 复合型，间隔单位：小时、分 |
| DAY_MICROSECOND | 复合型，间隔单位：天、毫秒 |
| DAY_SECOND | 复合型，间隔单位：天、秒 |
| DAY_MINUTE | 复合型，间隔单位：天、分 |
| DAY_HOUR | 复合型，间隔单位：天、小时 |
| YEAR_MONTH | 复合型，间隔单位：年、月 |
17、date_sub 和 subdate：日期减法运算
date_sub(date,INTERVAL expr type)
- date：参数是合法的日期表达式；
- expr：参数是你希望添加的时间间隔；
- type和date_add函数中type一样
18、addtime：时间加法运算
addtime(time,expr)函数用于执行时间的加法运算。添加expr到time并返回结果。
- time是一个时间或日期时间表达式；
- expr是一个时间表达式
19、subtime：时间减法运算
subtime(time,expr)函数用于执行时间的减法运算。
- time是一个时间或日期时间表达式；
- expr是一个时间表达式
20、datediff：获取两个日期的时间间隔
datediff(date1,date2)返回起始时间date1和结束时间date2之间的天数。date1个date2为日期或date-and-time表达式。计算时只用到这些值日期部分。
21、date_format：格式化指定的日期
date_format(date,format)函数是根据format指定的格式显示date的值。date_format()函数接受两个参数：
- date：是要被格式化的有效日期值；
- format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比符号%。格式和form_unixtime中的format一样。
22、weekday：获取指定日期在一周内的索引位置
weekday(date)函数返回date的星期索引（0=星期1，…，6=星期日）。
四、Mysql聚合函数
| 函数名称 | 作用 |
|---|---|
| max | 查询指定列的最大值 |
| min | 查询指定列的最小值 |
| count | 统计查询结果的行数 |
| sum | 求和，返回指定列的总和 |
| avg | 求平均值，返回指定列数据的平均值 |
聚合函数用法简单，也较为常用，请读者自己练习，这里就不一一讲解了。
五、Mysql流程控制函数
| 函数名称 | 作用 |
|---|---|
| if | 判断，流程控制 |
| ifnull | 判断是否为空 |
| case | 搜索语句 |
1、if：判断
if(expr,v1,v2)函数，当expr为真时，返回v1，否则返回v2。
2、ifnull：判断是否为空
ifnull(v1,v2)：v1为空返回v2，否则返回v1。
3、case：搜索语句，类似于java中的if…else if…else
类似java中的if…else if…else。有两种写法。
（1）方式 1
case <表达式>
when <值1> then <操作>
when <值2> then <操作>
...
else <操作>
end case;（2）方式 2
case
when <条件1> then <命令>
when <条件2> then <命令>
...
else 命令
end case;示例：
准备数据：
需求：查询所有学生信息，输出：姓名，性别（男、女、未知），如下：
六、其他函数
| 函数名称 | 作用 |
|---|---|
| version | 数据库版本号 |
| database | 当前的数据库 |
| user | 当前连接用户 |
| password | 返回字符串密码 |
| md5 | 返回字符串的md5数据 |
今天介绍函数比较多，大家收藏一下，慢慢消化，如果以后项目中需要用到本篇介绍的函数，可以回到这里看看哦。
