常用函数
1、字符串函数
2、数值函数
3、日期和时间函数
字符串函数
1、合并字符串函数
(1)CONCAT(s1,s2,s3,···)——返回将s1,s2···合并后的数据,若其中一个参数为null,则返回null。
(2)CONCAT_WS(sep,s1,s2,s3,···)——sep为分隔符参数,返回将s1,s2···合并后的数据,且每个参数间由sep分割开,若其中一个参数为null,则返回null。若sep为null,则返回null,其他参数为null不影响,分隔符会忽略值为null的参数。
举例:CONCAT_WS('/','s1',null,'s3')——返回s1/s3
(3)GROUP_CONCAT([DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC|DESC] [SEPARATOR '分隔符'])——将GROUP BY 产生的同一个分组中的值连接起来,返回一个字符串结果。通过DISTINCT可以排除重复值,ORDER BY可以进行排序,可以用SEPARATOR添加一个分隔符。若不用[SEPARATOR '分隔符']则默认分隔符为逗号(‘,’)。
举例:GROUP_CONCAT(DISTINCT id ORDER BY id DESC SEPARATOR '_')——将对应id数值按递减排序并用‘_’连接起来
2、比较字符串函数
STRCMP(s1,s2)——若s1>s2则返回1,若s1<s2则返回-1,若s1=s2则返回0。
3、获取字符串长度函数
(1)LENGTH(s)——获取字节数长度
(2)CHAR_LENGTH(s)——获取字符数长度
4、字母大小写转换函数
(1)转换为大写:UPPER(s)或UCASE(s)
(2)转换为小写:LOWER(s)或LCASE(s)
5、查找字符串函数
(1)FIND_IN_SET(s1,s2)——返回在s2中第一个与s1相匹配的字符串的位置,s2中包含若干个用逗号隔开的字符串。没有则返回0。
举例:
FIND_IN_SET('b','a,b,c,b')——返回2
FIND_IN_SET('b','abcb')——返回0
(2)FIELD(s,s1,s2,···)——返回第一个与s相匹配的字符串的位置。没有则返回0。
举例:FIELD('b','a','b','c','b')——返回2
(3)LOCATE(s1,s)、POSITION(s1 IN s)、INSTR(s,s1)——返回第一个与子字符串相匹配的开始位置(s1即子字符串)。没有则返回0。
举例:
LOCATE('abc','adabsabcsabc')——返回6
POSITION('abc' IN 'adabsabcsabc')——返回6
INSTR('adabsabcsabc','abc')——返回6
(4)ELT(num,s1,s2,s3···)——返回指定位置的字符串。
举例:ELT(2,'a','b','c')——返回b
6、截取字符串函数
(1)LEFT(s,len)——截取s中从左开始数,长度为len的字符串。
举例:
LEFT('abc',2)——返回ab
LEFT('abc',4)——返回abc
(2)RIGHT(s,len)——截取s中从右开始数,长度为len的字符串。
举例:RIGHT('abc',2)——返回bc
(3)SUBSTRING(s,num,len)、MID(s,num,len)——截取s中从第num个位置开始数,长度为len的字符串。
举例:
SUBSTRING('abcde',2,3)——返回bcd
MID('abcde',2,3)——返回bcd
7、去除字符串首尾空格函数
(1)LTRIM(s)——去除s首空格
(2)RTRIM(s)——去除s尾空格
(3)TRIM(s)——去除s首尾空格
8、替换字符串函数
(1)INSERT(s,pos,len,news)——s为原始字符串,pos为需要替换的起始位置,len表示需要被替换的长度,news表示用来替换原字符串的新字符串。
!注:当len超过剩余长度时会替换剩余的字符串;当pos刚好比原字符串长度大1时,会把新字符串加到末尾;当pos比原字符串长度大2以上时,字符串不变;当pos为0时,字符串不变;当len为0时,相当于在pos位置插入新字符串。
举例:
INSERT('abcde',2,3,'x')——返回axe
INSERT('abcde',2,5,'x')——返回ax
INSERT('abcde',6,3,'x')——返回abcdex
INSERT('abcde',7,3,'x')——返回abcde
INSERT('abcde',0,3,'x')——返回abcde
INSERT('abcde',3,0,'x')——返回abxcde
(2)REPLACE(s,froms,tos)——将s中的froms子字符串用字符串tos替换掉。
!注:若这个子字符串不在原字符串中,则字符串不变。
举例:
REPLACE('abcde','bcd','x')——返回axe
REPLACE('abcde','f','x')——返回abcde
数值函数
1、ABS(x)——返回x的绝对值
2、CEIL(x)——返回大于等于x的最小整数
3、FLOOR(x)——返回小于等于x的最大整数
4、MOD(x,y)——返回x/y的模
5、RAND()——返回0~1的随机值(每次执行结果都不一样);
RAND(x)——返回0~1的随机值(只要x相同,每次执行结果都一样)
6、ROUND(x)——返回x经过四舍五入的整数值(只看小数点后一位);
ROUND(x,y)——返回x经过四舍五入的值,保留小数点后y位小数(只看第y+1位)
7、TRUNCATE(x,y)——返回x保留小数点后y位小数的值
日期和时间函数
1、获取当前日期、时间
NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE()这四个函数实现效果一样。
2、只获取当前日期
CURDATE(),CURRENT_DATE()这两个函数实现效果一样。
3、只获取当前时间
CURTIME(),CURRENT_TIME()这两个函数实现效果一样。
4、获取当前日期、时间中各部分值
(1)YEAR(NOW())——返回当前日期中的年份
(2)QUARTER(NOW())——返回当前日期中的季度数
(3)MONTH(NOW())——返回当前日期中的月份
(4)WEEK(NOW())——返回当前日期中的周数
(5)DAY(NOW())、DAYOFMONTH(NOW())——返回当前日期中的天数
(6)HOUR(NOW())——返回当前时间中的小时
(7)MINUTE(NOW())——返回当前时间中的分钟
(8)SECOND(NOW())——返回当前时间中的秒
(9)EXTRACT(type FROM date)——返回date中type对应的值,比如:EXTRACT(YEAR FROM NOW())——返回当前日期中的年份
5、计算日期、时间的函数
(1)TO_DAYS(date)——计算从默认时间(0000年1月1日)到date的天数,date至少精确到日。举例:TO_DAYS('0000-01-02')——返回2
(2)FROM_DAYS(num)——返回从默认时间经过了num天的日期。举例:FROM_DAYS(366)——返回0001-01-01
!注:当num小于等于365时,返回的都是0000-00-00
(3)DATEDIFF(date1,date2)——两个日期相隔的天数(date1-date2),date至少精确到日。举例:DATEDIFF('2020-02-09','2020-02-10')——返回-1
(4)ADDDATE(date,num)——返回从date加上num天的日期,date至少精确到日。
(5)SUBDATE(date,num)——返回从date减去num天的日期,date至少精确到日。
(6)ADDDATE(date,INTERVAL expr type)——返回从date加上一段时间的日期,expr决定时间长度,type决定时间类型(年月日时分秒),date至少精确到日。举例:ADDDATE('2020-02-09',INTERVAL 2 DAY)——返回2020-02-11
(7)SUBDATE(date,INTERVAL expr type)——返回从date减去一段时间的日期,expr决定时间长度,type决定时间类型(年月日时分秒),date至少精确到日。举例:SELECT SUBDATE('2020-02-09',INTERVAL 2 DAY)——返回2020-02-07
(8)ADDTIME(time,n)——返回时间参数time加上n秒后的时间
举例:
ADDTIME('14:14:14',2)——返回14:14:16
ADDTIME('14:14',2)——返回14:14:02
ADDTIME('14',2)——返回00:00:16
(9)SUBTIME(time,n)——返回时间参数time减去n秒后的时间