MySQL 函数总结

MySQL 函数总结

数字函数

1、ABS(X) 返回X的绝对值

select ABS(-1)   -- 结果 1

2、AVG(age) 返回平均值， age为表中的字段

select AVG(age) from user   -- 结果为 age的平均值

3、CEIL(X)/CEILING(X) 返回大于或等于 X 的最小整数

select CEIL(2);  -- 结果为 2
select CEILING(2.5); -- 结果为 3

4、FLOOR(X) 返回小于或等于X的最大整数

select FLOOR(1.5) -- 结果为 1

5、EXP(X) 返回 e 的 X 次方

select EXP(2) -- 结果为 7.38905609893065

6、GREATEST(expr1,expr2…) 返回列表中的最大值

select GREATEST(1,2,20)  -- 结果为 20

7、LEAST(expr1,expr2…) 返回列表中的最小值

select LEAST(1,2,20) -- 结果为 1

8、LN(X) 返回 X 的自然对数 || LOG(X) 返回 X 自然对数（以 e 为底的对数）

select LN(2); -- 结果为 0.6931471805599453
select LOG(2)

9、MAX(字段) 返回该字段的最大值

select MAX(age) from user  -- 返回 age 的最大值

10、MIN(字段) 返回字段的最小值

select MIN(age) from user -- 返回 age 的最小值

11、POW(x, y)/POWER(x, y)返回 x 的 y 次方

select POW(3, 2);
select POWER(3, 2); -- 结果为 9

12、RAND() 返回从 0 到 1 的随机数

select RAND();
select a + RAND() % (b - a + 1)  -- 取[a, b]之间的随机数

13、ROUND(X) 返回离X最近的整数

select ROUND(1.23456)

14、SIGN(X) 返回 X 的符号

select SIGN(-5) -- 返回 -1 ， 0 ， 1（负数，0，正数）

15、SQRT(X) 返回 X 的平方根

select SQRT(36)  -- 结果为 6

16、SUM(字段) 返回字段的总和

select SUM(age) from user

17、TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值

select TRUNCATE(1.2345,2) -- 结果为 1.23

字符串函数

1、返回字符串 s 的第一个字符的ASCII码

select ASCII('BC')

2、返回字符串的字符数

select LENGTH('student'); -- 结果为 7
select CHAR_LENGTH('12121'); -- 结果为 5
select CHARACTER_LENGTH('ACDX') -- 结果为 4

3、合并字符串

select CONCAT('stu','den','t') -- 结果为 student

4、匹配字符串位置

select FIND_IN_SET('t', 's,t,u,d,e,n,t') -- 结果为 2

5、FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入

select FORMAT(123456789.123, 2) -- 结果为 123,456,789.12

6、INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

select INSERT('student',2,3,'pp') -- 结果为 sppent

7、LOCATE(s1,s)/POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置

select LOCATE('st','student'); -- 结果为 1
select POSITION('st' IN 'student') -- 结果为 1

8、TRIM(s)去掉字符串 s 开始和结尾处的空格

select TRIM('   student               ')

9、LTRIM(s)去掉字符串 s 开始处的空格 || RTRIM(s)去掉字符串 s 结尾处的空格

select LTRIM('   student    ');  -- 结果为 ’student    ‘
select RTRIM('   student    ')  -- 结果为 ’   student‘

10、SUBSTR(s, start, length)/SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串

select SUBSTR('student', 2, 3); -- 结果为 tud
select SUBSTRING('student', 2, 3) -- 结果为 tud

11、REPEAT(s,n)将字符串 s 重复 n 次

select REPEAT('stu', 3) -- 结果为 stustustu

12、REVERSE(s)将字符串s的顺序反过来

select REVERSE('stu') -- 结果为 uts

13、拼接函数LPAD(字段名,7,‘xo’)

select LPAD(count(id)+1,4,'0') as '左填充' from user 

14、REPLACE函数：替换字符串中的指定字符

select REPLACE('student','tu','td') -- 结果为 stddent

日期函数

1、CURDATE()/CURRENT_DATE返回当前日期

select CURDATE(); -- 结果为 2024-01-11
select CURRENT_DATE -- 结果为 2024-01-11

2、CURRENT_TIME/CURTIME()返回当前时间

select CURRENT_TIME; -- 结果为 16:46:33
select CURTIME() -- 结果为 16:46:33

3、CURRENT_TIMESTAMP()返回当前日期和时间

select CURRENT_TIMESTAMP -- 结果为 2024-01-11 16:58:16

4、ADDDATE(d,n)计算起始日期 d 加上 n 天的日期

select ADDDATE('2024-01-11', INTERVAL 10 DAY) -- 结果为 2024-01-21

5、ADDTIME(t,n)时间 t 加上 n 秒的时间

select ADDTIME('2024-01-11 17:25:00', 10) -- 结果为 2024-01-11 17:25:10

6、DATE()从日期或日期时间表达式中提取日期值

select DATE('2024-01-11 17:25:10') -- 结果为 2024-01-11 date类型

7、DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数

select DATEDIFF('2023-12-31','2024-01-12') -- 结果为 -12

8、DATE_FORMAT按表达式 f的要求显示日期 d

select DATE_FORMAT('2024.01.12 11:18', '%Y-%m-%d %r'); -- 结果为 2024-01-12 11:18:00 AM

9、DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday

select DAYNAME('2024-01-12') -- 结果为 Friday

10、DAYOFMONTH(d)计算日期 d 是本月的第几天

select DAYOFMONTH('2024-01-12') -- 结果为 12

11、DAYOFYEAR(d)计算日期 d 是本年的第几天

select DAYOFYEAR('2024-03-04') -- 结果为 64

12、EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值

select EXTRACT(SECOND from '2024-01-12 15:28:24');  -- 结果为 24
select EXTRACT(MINUTE from '2024-01-12 15:28:24');  -- 结果为 28
select EXTRACT(HOUR from '2024-01-12 15:28:24');  -- 结果为 15
select EXTRACT(DAY from '2024-01-12 15:28:24');  -- 结果为 12
select EXTRACT(WEEK from '2024-01-14 15:28:24');  -- 结果为 2  以星期天--星期六为一周 且 从0周开始
select EXTRACT(MONTH from '2024-01-14 15:28:24');  -- 结果为 1  
select EXTRACT(QUARTER from '2024-01-14 15:28:24');  -- 结果为 1 （季度） 
select EXTRACT(YEAR from '2024-01-14 15:28:24');  -- 结果为 2024
select EXTRACT(HOUR_MINUTE from '2024-01-14 15:28:24');  -- 结果为 1528

13、UNIX_TIMESTAMP()得到时间戳

select UNIX_TIMESTAMP('2024-01-01') -- 结果为 1704038400

14、FROM_UNIXTIME()时间戳转日期

select FROM_UNIXTIME(0) -- 结果为 1970-01-01 08:00:00

高级函数

1、IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2

select IF(1 > 0, 'true', 'false') -- 结果为 true

2、CASE when THEN else end

select CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END  -- 结果为 1 > 0

3、返回参数中的第一个非空表达式（从左向右）

SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); -- 结果为 runoob.com

4、IFNULL(v1,v2) 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2

select IFNULL(NULL,'132425') -- 结果为 132425