数据库之常用函数

函数	功能
ABS(x)	返回数值x的绝对值
MOD(x,y)	返回数值x除以数值y后的余数
CEIL(x)	返回大于数值x的最小整数值
FLOOR(x)	返回小于数值x的最大整数值
RAND()	返回0~1内的随机数
ROUND(x)	返回对参数x进行四舍五入后的值，ROUND(x)返回整数值，ROUND(x,y)返回参数x四舍五入后保留y位小数的值
TRUNCATE(x,y)	对数值x进行截取，保留小数点后y位数字

1.求绝对值函数

函数ABS(x)的返回值是数值x的绝对值。正数的绝对值是其本身，负数的绝对值是其相反数。

2.求余函数

函数MOD(x,y)的返回值是数值x除以数值y后的余数。与x%y的结果相同，除数和被除数任何一个为NULL，返回结果都将为NULL；除数为0将是非法运算，返回结果为NULL。

3.用于获取整数的函数

1.CEIL(x)

函数CEIL(x)的返回值是大于数值x的最小整数值。

2.FLOOR(x)

函数FLOOR(x)的返回值是小于数值x的最大整数值。

4.获取随机数的函数

函数RAND()的返回值是0~1内的小数，并且每次的运行结果都不同。

5.四舍五入函数

函数ROUND()的作用是对数值执行四舍五入操作，当函数格式为ROUND(x)时，返回值为整数；当函数格式为ROUND(x,y)时，对数值x进行四舍五入并保留小数点后y位。

6.截取小数函数

函数TRUNCATE(x,y)的作用是对数值x进行截取，保留小数点后y位。其与ROUND()函数的区别是，ROUND()函数在截取值时会四舍五入；而TRUNCATE(x,y)函数直接截取值，并不进行四舍五入。

二、字符串函数

函数	功能
LENGTH(str)，CHAR_LENGTH(str)	返回字符串长度或字符个数
CONCAT(str1,str2...strn)，CONCAT_WS(x,str1,str2...strn)	合并字符串
INSERT(str,x,y,instr)，REPLACE(str,a,b)	替换字符串
LOWER(str)，UPPER(str)	字符大小写转换
LEFT(str,x)，RIGHT(str,x)，SUBSTRING(str,x,y)	获取字符串的一部分
LPAD(str1,n,str2)，RPAD(str1,n,str2)	填充字符串
LTRIM(str)，RTRIM(str)，TRIM(str)	删除字符串左侧、右侧或两侧空格
REPEAT(str,n)	返回字符串str重复n次的结果
LOCATE(str1,str)	返回子字符串的开始位置
REVERSE(str)	反转字符串

1.返回字符串长度和字符串中字符个数的函数

函数LENGTH(str)用于返回字符串的长度，一个汉字占用2个字节，一个英文字符和数字占用1个字节。

函数CHAR_LENGTH(str)用于返回字符串中的字符个数。

2.合并字符串的函数

函数CONCAT(str1,str2...strn)可以将多个字符串拼接称为一个字符串，但如果参数中有一个NULL值，则返回结果都将为NULL。

函数CONCAT_WS(x,str1,str2...strn)是函数CONCAT(str1,str2...strn)的特殊形式，作用是以第一个参数为分隔符，连接后面的多个字符串。

函数CONCAT_WS(x,str1,str2...strn)会忽略分隔符后的NULL值，但如果分隔符为NULL，则返回结果为NULL。

3.替换字符串的函数

函数INSERT(str,x,y,instr)的作用是将字符串str从第x位置开始，y个字符长的子串替换为字符串instr。

函数REPLACE(str,a,b)的作用是将字符串str中的子串a替换为字符串b。

4.字母大小写转换函数

函数LOWER(str)用于将字符串str中的字母全部转换为小写字母，函数UPPER(str)用于将字符串str中的字母全部转换为大写字母。

5.获取指定长度字符串的函数

函数LEFT(str,x)用于获取字符串str中最左边的x个字符，函数RIGHT(str,x)用于获取字符串str中最右边的x个字符。

函数SUBSTRING(str,x,y)用于获取字符串str中从x位置开始，后面y个字符长度的子串。该函数常用于在给定字符串中提取子串。

6.填充字符串的函数

函数LPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最左边进行填充，直到字符串str1总长度达到n个字符长度。如果str1的字符长度大于或等于n，则不填充。

函数RPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最右边进行填充，直到字符串str1总长度达到n个字符长度。

对于这两个函数来说，当参数n小于str1字符串个数时，字符串str1会被截取；当参数n大于str1字符串个数时，字符串str1才会被填充。

7.删除字符串中空格的函数

函数LTRIM(str)用于删除字符串左侧的空格字符，函数RTRIM(str)用于删除字符串右侧的空格字符。

函数TRIM(str)用于删除字符串开头和结尾的空格，另外，它还可以删除字符串两侧的指定字符。

用法：

TRIM(‘str1’ from ‘str2’);

8.重复生成字符串的函数

函数REPEAT(str,n)返回字符串str重复n次的结果。

9.获取字符串中子串开始位置的函数

函数LOCATE(str1,str)返回子串str1在字符串str中的开始位置，返回值的最小值为1，如果字符串str中不包含字符串str1，则返回0。

10.反转字符串的函数

函数REVERSE(str)返回将字符串str中字符倒序排列后的结果。

三、日期与时间函数

函数	功能
CURDATE()	获取当前日期
CURTIME()	获取当前时间
NOW()	获取当前的日期和时间
UNIX_TIMESTAMP(date)	获取日期date的NUIX时间戳
YEAR(d)，MONTH(d)，WEEK(d)，DAY(d)，HOUR(d)，MINUTE(d)，SECOND(d)	返回指定日期的年份、月份、日期、日、时、分和秒
DATE_FORMAT(d,format)	按format指定的格式显示日期d的值
ADDDATE(date,INTERVAL expr unit)，SUBDATE(date,INTERVAL expr unit)	获取一个日期或时间值加上一个时间间隔的时间值
TIME_TO_SEC(d)，SEC_TO_TIME(d)	获取将“HH:MM:SS”格式的时间换算为秒，或将秒数换算为“HH:MM:SS”格式的值

1.获取当前日期的函数

函数CURDATE()返回的包含年月日的当前日期。

2.获取当前时间的函数

函数CURTIME()返回“HH:MM:SS”格式的当前时间。

3.获取当前日期和时间的函数

函数NOW()返回当前日期和时间（同时包含年月日和时分秒）。

4.获取UNIX时间戳函数

UNIX时间戳是从1970年1月1日（UTC/GMT的午夜）开始到当前时间所经过的秒数（不考虑闰秒）。一分钟表示为UNIX时间戳为60秒，一小时表示为UNIX时间戳为3600秒，一天表示为UNIX时间戳为86400秒。

函数UNIX_TIMESTAMP(date)返回日期date的UNIX时间戳。

5.获取年份、月份、星期、日、时、分和秒的函数

1.YEAR(d)

函数YEAR(d)返回所给的日期d是哪一年。

2.MONTH(d)和WEEK(d)

函数MONTH(d)返回所给的日期d是一年中的第几个月，函数WEEK(d)返回所给的日期d是一年中的第几周。

3.HOUR(d)，MINUTE(d)和SECOND(d)

函数HOUR(d)返回所给时间d的小时，函数MINUTE(d)返回所给时间d的分钟，函数SECOND(d)返回所给时间d的秒。

6.格式化日期和时间的函数

函数DATE_FORMAT(d,format)按字符format格式化日期d的值。

参数值	意义
%Y	四位数形式的年份
%y	两位数形式的年份
%c	数字形式（0~12）的月份
%M	英文形式（January~December）的月份名
%m	数字形式（00~12）的月份
%W	一周中每天为周几，用英文表示
%D	月中的第几天，英文后缀形式，如0th,1st...
%d	两位数字表示月中第几天，形式为00~31
%j	一年的第几日（001~366）
%H	24小时形式的小时（00~23）
%h	12小时形式的小时（01~12）
%r	12小时形式的小时，后缀为上午（AM）或下午（PM）
%i	两位数字形式的分（00~59）
%S	两位数字形式的秒（00~59）

7.计算日期和时间的函数

1.函数ADDDATE()与SUBDATE()分别用于执行日期和时间的加运算与减运算。

其语法形式如下：

ADDDATE(date,INTERVAL expr unit)，

SUBDATE(date,INTERVAL expr unit)

参数date是一个日期或时间值，用于指定起始时间；INTERVAL是间隔类型关键字；参数expr是一个字符串，用于指定日期增加或减去的间隔值，该值对应后面的类型unit；参数unit是间隔类型，或者叫单位。

间隔类型值	描述	格式
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
YEAR_MONTH	年和月	YY-MM
DAY_HOUR	日和小时	DD hh
DAY_MINUTE	日和分钟	DD hh:mm
DAY_SECOND	日和秒	DD hh:mm:ss
HOUR	小时	hh
MINUTE	分	mm
SECOND	秒	ss
HOUR_MINUTE	小时和分	hh:mm
HOUR_SECOND	小时和秒	hh:ss
MINUTE_SECOND	分钟和秒	mm:ss

2.DATEDIFF()函数

函数DATEDIFF()用于计算两个日期之间相差的天数。

8.时间和秒相互转换的函数

函数TIME_TO_SEC(d)可将指定的时间d换算为秒，函数SEC_TO_TIME(d)可将指定的秒换算为“HH:MM:SS”形式的时间格式。

四、条件判断函数

条件判断函数又称为流程控制函数。

函数	功能
IF(expr,v1,v2)	如果expr为真，返回v1，否则返回v2
IFNULL(v1,v2)	如果v1不为NULL，返回v1，否则返回v2
CASE WHEN expr1 THEN r1 [WHEN expr2 THEN r2] [ELSE rn] END	根据条件将数据分为几个档次
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END	根据条件将数据分为几个档次

1.IF()函数

IF(expr,v1,v2)函数的意义是，如果表达式expr的结果为真，函数的返回值为v1，如果表达式expr的结果为假，则返回值为v2。

2.IFNULL()函数

IFNULL(v1,v2)函数的意义是，如果v1不为NULL，则函数的返回值为v1，否则返回值为v2。NULL值是不能参与数值运算的，实际应用中常用该函数来替换掉NULL值。

3.CASE函数

CASE函数的第1种形式如下：

CASE WHEN expr1 THEN r1 [WHEN expr2 THEN r2] [ELSE rn] END

该形式的意义是，如果表达式expr1为真，则返回v1；如果表达式expr2为真，则返回r2；如果expr1和expr2都不为真，则返回rn。

CASE函数的第2种形式如下：

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END

该形式的意义是，如果表达式expr的结果等于v1,那么返回r1；如果expr的结果等于v2，那么返回r2；如果表达式expr的结果既不等于v1也不等于v2，则返回rn。

五、JSON函数

JSON函数就是用于处理JSON类型数据的一组数据。

函数	功能
JSON_ARRAY()	创建JSON数组
JSON_OBJECT()	创建JSON对象
JSON_ARRAY_APPEND()	向JSON数组中追加数据
JSON_SET()	修改JSON对象中的数据
JSON_REMOVE()	删除JSON数组和JSON对象中的数据
JSON_EXTRACT()	返回JSON数组中KEY所对应的数据
JSON_SEARCH()	返回JSON数组中给定数据的路径

1.创建JSON值的函数

在MySQL中创建JSON值的函数有两个，一个用于创建数组形式的JSON值，另一个用于创建对象形式的JSON值。

1.创建JSON数组

JSON_ARRAY()函数用于创建组数形式的JSON值，其语法形式如下：

JSON_ARRAY(val1,val2,......,valn)

2.创建JSON对象

JSON_OBJECT()函数用于创建对象形式的JSON值，其语法形式如下：

JSON_OBJECT(key1,val1,key2,val2,......keyn,valn)

2.修改JSON值的函数

1.JSON_ARRAY_APPEND()

使用JSON_ARRAY_APPEND()函数，可以将值附加到JSON文档中指示数组的结尾并返回结果。其语法形式如下：

JSON_ARRAY_APPEND(json_doc,key,val[,key,val]......)

其中json_doc表示JSON文档，key指示键，val表示要附加的值。

2.JSON_SET()

使用JSON_SET()函数，可以在JSON文档中插入或更新数据并返回结果，其语法形式如下：

JSON_SET(json_doc,key,val[,key,val]......)

其中json_doc表示JSON文档，key指示键，val表示要插入或更新的值。

在使用JSON_SET()函数，如果key已经存在，修改值将会将原值替换；如果key不存在，会在原JSON对象中追加不存在的key和相对应的值。

3.从JSON文档中删除数据的函数

如果用户需要删除JSON数组或者JSON对象中的数据，可以使用JSON_REMOVE()函数，其语法形式如下：

JSON_REMOVE(json_doc,key,val[,key,val]......)

4.返回JSON文档中数据和路径的函数

1.JSON_EXTRACT()

使用JSON_EXTRACT()函数，可以根据给出的key，返回JSON文档中其所对应的数据。

语法形式如下：

JSON_EXTRACT(json_doc,key1,[,key2]......)

2.JSON_SEARCH()

使用JSON_SEARCH()函数可以根据给出的数据，返回JSON文档中其所对应的路径。

语法形式如下：

JSON_SEARCH(json_doc,one_or_all,str)

当参数one_or_all的值为one时，返回第一次匹配字符串str的key；值为all时，返回所有匹配字符串str的key。

六、其他函数

函数	功能
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名和主机名的组合
MD5(str)	返回字符串str的MD5值
PASSWORD(str)	返回字符串str的加密版本
CONV(val,from_base,to_base)	不同进制之间相互转换
INTE_ATON(IP)，INTE_NTOA(val)	IP和数字之间相互转换

1.返回数据库信息的函数

MySQL中常用返回数据库信息的函数有DATABASE()，VERSION()和USER()，其中DATABASE()函数返回使用UTF8字符集的当前数据库名，VERSION()函数返回当前数据库版本，USER()函数返回当前登录用户名和主机名的组合。

如果没有选择任何数据库，DATABASE()函数将返回NULL值。