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数据

今天介绍函数比较多，大家收藏一下，慢慢消化，如果以后项目中需要用到本篇介绍的函数，可以回到这里看看。