MySQL 函数

一、数值函数

1.1 基本函数

求绝对值

select ABS(-123) from DUAL;

取符号

正数返回1，负数返回-1，0返回0

select SIGN(-23),SIGN(43) from DUAL;

返回圆周率的值

select PI() from DUAL;

返回大于或等于某个值的最小整数

取天花板函数，32.32距离最小的整数是33，-43.23距离最小的整数是43

select CEIL(32.32),CEILING(-43.23)  from DUAL;

返回小于或等于某个值的最大整数

取地板函数，32.32距离最小的整数是32，-43.23距离最小的整数是44

select FLOOR(32.32),FLOOR(-43.23)  from DUAL;

返回X除以Y后的余数

select MOD(12,5),12 MOD 5,12 % 5  from DUAL;

返回列表中最小的值

select LEAST(12,5,3,9) from DUAL;

返回列表中最大的值

select GREATEST(12,5,3,9) from DUAL;

取0~1随机数

可带参数，如参数一致所得结果随机数是一样的

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

四舍五入，截断操作

ROUND(X) 默认舍掉小数位，ROUND(X,Y) Y保留小数位，ROUND(X,-Y) Y为负数则舍掉小数点前的位数，ROUND(123.456,-1) 结果为120，ROUND(153.456,-2)结果为200

SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2),
ROUND(123.456,-1),ROUND(153.456,-2)
FROM DUAL;

--小数点前后截断
SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1)
FROM DUAL;

1.2 角度与弧度互换函数

将角度转化为弧度

参数X为角度值

SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90)
FROM DUAL;

将弧度转化为角度

参数X为弧度值

SELECT DEGREES(2*PI()),DEGREES(RADIANS(60))
FROM DUAL;

1.3 三角函数

求X的正弦值

参数X为弧度值

SELECT SIN(RADIANS(30)) FROM DUAL;

求X的反正弦值

即获取正弦为X的值，如果X的值不在 -1 到 1 之间，则返回NULL

SELECT DEGREES(ASIN(1)) FROM DUAL;

求X的正切值

SELECT TAN(RADIANS(45)) FROM DUAL;

求X的反正切值

返回正切值为X的值

SELECT DEGREES(ATAN(1)) FROM DUAL;

求两个参数的反正切值

SELECT ATAN2(1，2) FROM DUAL;

1.4 指数与对数

POW(x,y)，POWER(X,Y) ：返回x的y次方
EXP(x)：返回e的X次方，其中e是常数：2.718281828459045
LN(X)， LOG(X) ：返回以e为底的X的对数，当x<=0时，返回的结果为NULL
LOG10(X)：返回以10为底的X的对数，当x<=0时，返回的结果为NULL
LOG2(X)：返回以2为底的X的对数，当x<=0时，返回NULL

--指数
SELECT POW(2,5),POWER(2,4),EXP(2)
FROM DUAL;
--对数
SELECT LN(EXP(2)),LOG(EXP(2)),LOG10(10),LOG2(4)
FROM DUAL;

1.4 进制间的转换

BIN(x)：返回x的二进制编码
HEX(x)：返回x的十六进制编码
OCT(x)：返回x的八进制编码
CONV(x,f1,f2)：返回f1进制数变成f2进制数

SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,8)
FROM DUAL;

二、字符串函数

ASCII(S) 返回字符串S中的第一个字符的ASCI码值

SELECT ASCII('Abcdfsf') FROM DUAL;

CHAR_LENGTH(s) 返回字符串s的字符数。

作用与CHARACTER_LENGTH(s)相同

SELECT CHAR_LENGTH('hello'),CHAR_LENGTH('我们') FROM DUAL;

LENGTH(s) 返回字符串s的字节数，和字符集有关

SELECT LENGTH('hello'),LENGTH('我们') FROM DUAL;

CONCAT(s1,s2,…sn) 连接s1,s2.…,sn为一个字符串

# xxx worked for yyy
SELECT CONCAT(emp.last_name,' worked for ',mgr.last_name) "details"
FROM employees emp JOIN employees mgr
WHERE emp.`manager_id` = mgr.employee_id;

CONCAT WS(x,s1,s2,…sn) 同CONCAT(s1,s2…)函数，但是每个字符串之间要加上x

SELECT CONCAT_WS('-','hello','world','hello','beijing') FROM DUAL;

INSERT(str, idx, len,replacestr) 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr

#字符串的索引是从1开始的！
SELECT INSERT('helloworld',2,3,'aaaaa') FROM DUAL;

REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a

字符串如果匹配不上，则会替换失败，结果为原字符串

SELECT REPLACE('hello','lol','mmm') FROM DUAL;

UPPER(s)或UCASE(S) 将字符串s的所有字母转成大写字母

SELECT LOWER('HelLo') FROM DUAL;

LOWER(s)或LCASE(s) 将字符串s的所有字母转成小写字母

SELECT LOWER('HelLo') FROM DUAL;SELECT last_name,salary
FROM employees
WHERE LOWER(last_name) = LOWER('King'）;

LEFT(str,n) 返回字符串str最左边的n个字符

SELECT LEFT('hello',2)  FROM DUAL;

RIGHT(str,n) 返回字符串str最右边的n个字符

SELECT RIGHT('hello',3),RIGHT('hello',13)
FROM DUAL;

LPAD(str, len, pad) 用字符串pad对str最左边进行填充，直到str的长度为len个字符

# LPAD:实现右对齐效果
SELECT employee_id,last_name,LPAD(salary,10,'*')
FROM employees;

RPAD(str ,len, pad) 用字符串pad对str最右边进行填充，直到str的长度为len个字符

# RPAD:实现左对齐效果
SELECT employee_id,last_name,RPAD(salary,10,'*')
FROM employees;

LTRIM(s) 去掉字符串s左侧的空格

SELECT CONCAT('---',LTRIM('    h  el  lo   '),'***') FROM DUAL;

RTRIM(s) 去掉字符串s右侧的空格

SELECT CONCAT('---',RTRIM('    h  el  lo   '),'***') FROM DUAL;

TRIM(s) 去掉字符串s开始与结尾的空格

SELECT CONCAT('---',TRIM('    h  el  lo   '),'***') FROM DUAL;

TRIM(S1 FROM s) 去掉字符串s开始与结尾的s1

SELECT TRIM('oo' FROM 'ooheollo') FROM DUAL;

TRIM(LEADING S1 FROM s) 去掉字符串s开始处的s1

SELECT TRIM( LEADING  'oo' FROM 'ooheollo') FROM DUAL;

TRIM(TRAILING s1 FROM s) 去掉字符串s结尾处的s1

SELECT TRIM( TRAILING 'o' FROM 'ooheollo') FROM DUAL;

REPEAT(str, n) 返回str重复n次的结果

SELECT REPEAT('hello',4) FROM DUAL;

SPACE(n) 返回n个空格

SELECT LENGTH(SPACE(5)) FROM DUAL;

STRCMP(s1,s2) 比较字符串s1,s2的ASCII码值的大小

-- 结果为正数则参数1大，结果为负数则参数2大，结果为0则一样大
SELECT STRCMP('abc','abe') FROM DUAL;

SUBSTR(s,index,len) 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同

SELECT SUBSTR('hello',2,2),SUBSTRING('lll',1,1),MID('lll',2,2) FROM DUAL;

LOCATE(substr,str) 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0

SELECT LOCATE('lll','hello') FROM DUAL;

ELT(m,s1,s2,sn) 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn

SELECT ELT(2,'a','b','c','d') FROM DUAL;

FIELD(s,s1,s2,…sn) 返回字符串s在字符串列表中第一次出现的位置

SELECT FIELD('mm','gg','jj','mm','dd','mm') FROM DUAL;

FIND_IN_SET(S1,S2) 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串

SELECT  FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg') FROM DUAL;

REVERSE(S) 返回s反转后的字符串

SELECT REVERSE('mmgg,mm,jj,dd,mm,gg') FROM DUAL;

NULLIF(value1,value2) 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) "compare"
FROM employees;

三、日期与时间函数

3.1 获取日期、时间

返回当前日期，只包含年、月、日

SELECT CURDATE(),CURRENT_DATE() FROM DUAL;

返回当前时间，只包含时、分、秒

SELECT CURTIME(),CURRENT_TIME() FROM DUAL;

返回当前系统日期和时间

SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP()
FROM DUAL;

返回UTC(世界标准时间)日期

SELECT UTC_DATE() FROM DUAL;

返回UTC(世界标准时间)时间

SELECT UTC_TIME() FROM DUAL;

3.2 日期与时间戳的转换

以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP()->1634348884

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-10-01 12:12:32')
FROM DUAL;

将时间date以UNIX时间戳的形式返回。

SELECT UNIX_TIMESTAMP('2021-10-01 12:12:32') FROM DUAL;

将UNIX时间戳的时间转换为普通格式的时间

SELECT FROM_UNIXTIME(1635173853),FROM_UNIXTIME(1633061552) FROM DUAL;

3.3 获取月份、星期、星期数、天数等函数

YEAR(date)/MONTH(date) / DAY(date) 返回具体的日期值

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()) FROM DUAL;

HOUR(time)/MINUTE(time)/SECOND(time) 返回具体的时间值

SELECT HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) FROM DUAL;

MONTHNAME(date) 返回月份:January

SELECT MONTHNAME('2024-03-11') FROM DUAL;

DAYNAME(date) 返回星期几：MONDAY，TUESDAY…SUNDAY

SELECT DAYNAME('2024-03-11') FROM DUAL;

WEEKDAY(date) 返回周几

注意，周1是0，周2是1，。。。周日是6

SELECT WEEKDAY('2024-03-11') FROM DUAL;

QUARTER(date) 返回日期对应的季度，范围为1~4

SELECT QUARTER(CURDATE()) FROM DUAL;

WEEK(date)，WEEKOFYEAR(date) 返回一年中的第几周

SELECT WEEK(CURDATE()),WEEKOFYEAR(NOW()) FROM DUAL;

DAYOFYEAR(date) 返回日期是一年中的第几天

SELECT DAYOFYEAR(NOW()) FROM DUAL;

DAYOFMONTH(date) 返回日期位于所在月份的第几天

SELECT DAYOFMONTH(NOW()) FROM DUAL;

DAYOFWEEK(date) 返回周几

注意:周日是1，周一是2，。。。周六是7

SELECT DAYOFWEEK(NOW()) FROM DUAL;

3.4 日期的操作函数

• MICROSECOND 返回毫秒数
• SECOND 返回秒数
• MINUTE 返回分钟数
• HOUR 返回小时数
• DAY 返回天数
• WEEK 返回日期在一年中的第几个星期
• MONTH 返回日期在一年中的第几个月
• QUARTER 返回日期在一年中的第几个季度
• YEAR 返回日期的年份
• SECOND_MICROSECOND 返回秒和毫秒值
• MINUTE_MICROSECOND 返回分钟和毫秒值
• MINUTE_SECOND 返回分钟和秒值
• HOUR_MICROSECOND 返回小时和毫秒值
• HOUR_SECOND 返回小时和秒值
• HOUR_MINUTE 返回小时和分钟值
• DAY_MICROSECOND 返回天和毫秒值
• DAY_SECOND 返回天和秒值
• DAY_MINUTE 返回天和分钟值
• DAY_HOUR 返回天和小时
• YEAR_MONTH 返回年和月

SELECT EXTRACT(SECOND FROM NOW()) FROM DUAL;

3.5 时间和秒钟转换的函数

TIME_TO_SEC(time) 将 time转化为秒并返回结果值。

转化的公式为:小时3600+分钟60+秒

SELECT TIME_TO_SEC(CURTIME()) FROM DUAL;

SEC TO _TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间

SELECT SEC_TO_TIME(83355) FROM DUAL;

3.6 计算日期和时间的函数

• MICROSECOND 毫秒数
• SECOND 秒
• HOUR 小时
• DAY 日
• WEEK
• MONTH 月
• YEAR 年
• SECOND_MICROSECOND 秒和毫秒
• MINUTE_MICROSECOND 分钟和毫秒
• MINUTE_SECOND 分钟和秒
• HOUR_MICROSECOND 小时和毫秒
• HOUR_SECOND 小时和秒
• HOUR_MINUTE 小时和分钟
• DAY_MICROSECOND 日和毫秒
• DAY_SECOND 日和秒
• DAY_MINUTE 日和分钟
• DAY_HOUR 日和小时
• YEAR_MONTH 年和月

返回与给定日期时间相差INTERVAL时间段的日期时间

DATE_ADD(dateiime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),DATE_ADD(NOW(),INTERVAL -1 YEAR) FROM DUAL;

返回与date相差INTERVAL时间间隔的日期

DATE_SUB(date,INTERVAL expr type), SUBDATE(date,INTERVALexpr type)

SELECT DATE_SUB(NOW(),INTERVAL 1 YEAR) FROM DUAL;

--其他例子
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

ADDTIME(tinhe1,time2) 返回time1加上time2的时间。

当time2为一个数字时，代表的是 秒，可以为负数

SELECT ADDTIME(NOW(),20) FROM DUAL;

SUBTIME(time1,time2) 返回time1减去time2的时间。

当time2为一个数字时，代表的是 秒，可以为负数

SELECT SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3') FROM DUAL;

DATEDIFF(date1,date2) 返回date1-date2的日期间隔天数

SELECT DATEDIFF(NOW(),'2021-10-01') FROM DUAL;

TIMEDIFF(timel, time2) 返回time1-time2的时间间隔

SELECT TIMEDIFF(NOW(),'2021-10-25 22:10:10') FROM DUAL;

FROM_DAYS(N) 返回从0000年1月1日起，N天以后的日期

SELECT FROM_DAYS(366)  FROM DUAL;

TO_DAYS(date) 返回日期date距离0000年1月1日的天数

SELECT TO_DAYS('0000-12-25') FROM DUAL;

LAST_DAY(date) 返回date所在月份的最后一天的日期

SELECT LAST_DAY(NOW()) FROM DUAL;

MAKEDATE(year,n) 针对给定年份与所在年份中的天数返回一个日期

SELECT MAKEDATE(YEAR(NOW()),32)  FROM DUAL;

MAKETIME(hour,minute,second) 将给定的小时、分钟和秒组合成时间并返回

SELECT MAKETIME(10,21,23) FROM DUAL;

PERIOD_ADD(time,n) 返回time加上n后的时间

SELECT PERIOD_ADD(20200101010101,10) FROM DUAL;

3.7 日期的格式化与解析

格式化：日期 ——> 字符串，解析： 字符串 ——> 日期

函数中fmt参数常用的格式符

DATE FORMAT(date,fmt) 按照字符串fmt格式化日期date值

SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),DATE_FORMAT(NOW(),'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r')
FROM DUAL;

TIME FORMAT(time,fmt) 按照字符串fmt格式化时间time值

SELECT TIME_FORMAT(CURTIME(),'%h:%i:%S') FROM DUAL;

GET FORMAT(date_type,format_type) 返回日期字符串的显示格式

SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;

STR TO_DATE(str, fmt) 按照字符串fmt对str进行解析，解析为一个日期

SELECT STR_TO_DATE('2021-October-25th 11:37:30 Monday 1','%Y-%M-%D %h:%i:%S %W %w')
FROM DUAL;

四、流程控制函数

IF(value,value1,value2)

如果value的值为TRUE，返回value1，否则返回value2

SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details"
FROM employees;SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details",
salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal"
FROM employees;

IFNULL(value1, value2)

如果value1不为NULL，返回value1，否则返回value2

SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END

相当于Java的if…else if…else…

SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股'WHEN salary >= 8000 THEN '小屌丝'ELSE '草根' END "details",department_id
FROM employees;SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股'WHEN salary >= 8000 THEN '小屌丝'END "details"
FROM employees;

CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1…[ELSE 值n] END

相当于Java的switch…case…

/*练习1
查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门,打印其工资的 1.3 倍数,
其他部门,打印其工资的 1.4 倍数*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1WHEN 20 THEN salary * 1.2WHEN 30 THEN salary * 1.3ELSE salary * 1.4 END "details"
FROM employees;/*练习2
查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门打印其工资的 1.3 倍数*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1WHEN 20 THEN salary * 1.2WHEN 30 THEN salary * 1.3END "details"
FROM employees
WHERE department_id IN (10,20,30);

五、加密与解密函数

PASSWORD(str)

返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密

# PASSWORD()在mysql8.0中弃用。

MD5(str)

返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL

SELECT MD5('mysql'),MD5(MD5('mysql')) FROM DUAL;

SHA(str)

从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。

SELECT SHA('mysql') FROM DUAL;

ENCODE(value,password_seed) DECODE(value,password_seed)

ENCODE(value,password_seed) ：返回使用password_seed作为加密密码加密value
DECODE(value,password_seed) ：返回使用password_seed作为加密密码解密value

#ENCODE()\DECODE() 在mysql8.0中弃用。
/*
SELECT ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql')
FROM DUAL;
*/

六、MySQL信息函数

VERSION() 返回当前MySQL的版本号

SELECT VERSION() FROM DUAL;

CONNECTION_ID() 返回当前MySQL服务器的连接数

SELECT CONNECTION_ID() FROM DUAL;

DATABASE()，SCHEMA() 返回MySQL命令行当前所在的数据库

SELECT DATABASE(),SCHEMA() FROM DUAL;

USER()，CURRENT USER()、SYSTEM USER() SESSION_USER() 返回当前连接MySQL的用户名

返回结果格式为“主机名@用户名”

SELECT USER(),CURRENT_USER() FROM DUAL;

CHARSET(value) 返回字符串value自变量的字符集

SELECT CHARSET('尚硅谷') FROM DUAL;

COLLATION(value) 返回字符串value的比较规则

SELECT COLLATION('尚硅谷') FROM DUAL;

六、其他函数

FORMAT(value,n) 返回对数字value进行格式化后的结果数据。

n表示四舍五入后保留到小数点后n位

#如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2) FROM DUAL;

CONV(value,from,to) 将value的值进行不同进制之间的转换

SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;

INET ATON(ipvalue) 将以点分隔的IP地址转化为一个数字

#以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;

INET NTOA(value) 将数字形式的IP地址转化为以点分隔的IP地址

#以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
SELECT INET_NTOA(3232235876) FROM DUAL;

BENCHMARK(n,expr) 将表达式expr重复执行n次。

用于测试MySQL处理expr表达式所耗费的时间

#BENCHMARK()用于测试表达式的执行效率
SELECT BENCHMARK(100000,MD5('mysql'))
FROM DUAL;

CONVERT(value USING char_code) 将value所使用的字符编码修改为char_code

# CONVERT():可以实现字符集的转换
SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'gbk'))
FROM DUAL;