mysql函数博客园_mysql函数

1. <=>安全等于 用于比较null 两者同为null时才为1 否则0

2.regexp或rlike 正则表达式匹配

3.not或！逻辑非　　and或&& 逻辑与　　or或|| 逻辑或　　xor 逻辑异或

4.&按位与 　　|按位或  　　<>按位右移

5.char_length(str)或CHARACTER_LENGTH(str)　　计算字符长度 大小写长度一样

6.concat('zhang', 'sna')  zhangsna　　concat_ws(',', 'zhang', 'sna')   zhang,sna 字符串连接

7.SELECT FIELD(null, "a", "b", "c", "d", "e") 0 　　返回第一个字符串在后续字符串列表中的位置 0为没找到 从1开始　　 不区分大小写

8.select FIND_IN_SET('ed', 'a……b_cd,ed') 2　　返回第一个字符串在字符串列表中的位置索引 mysql只能识别，分隔符　　 不区分大小写

9.select format(234.456, 7) 234.4560000　　select format(234.456, 2) 234.46 四舍五入  select truncate(234.456, 2) 234.45 不会四舍五入

10.insert('baidu.com', 2, 5, 'google')  bgooglecom

11.select LOCATE('LR', 'oorlrrpa')或select position('lr' in 'oorlrrpa') 4 不区分大小写

12.lower(str)或lcase(str)　　ucase(str)或upper(str)　　ltrim 去做空格 rtrim 去有空格 trim 去左右空格

13.select lpad('abc', 8, 'r g') 　　r gr abc　　select rpad('abc', 8, 'r g') 　　abcr gr  左右填充

14.repeat('10', 10)  10101010101010101010 　　space(10) 10个空格

15.REVERSE('zhangsan') nasgnahz

16.select strcmp('ach', 'tbo') 字符串比较大小 从第一个字母的ascii编码开始

17.left('avedefg', 2) av　　select right('avedefg', 3) efg　　select mid('avedefg', 2, 3)或select substr('avedefg', 2, 3)或select substring('avedefg', 2, 3) ved

substring_index('a*b*c*d*e','*',3) a*b*c 　　SUBSTRING_INDEX('a*b*c','*',-1) c 字符串截取

18.select REPLACE('avedafg', 'a', 'pp') ppvedppfg　　select REPLACE('avedafg', 't', 'pp') avedafg 字符串替换 找不到则不会替换

19.ceil(x)或ceiling(x) 返回大于或等于x的最小整数 floor(x)　返回小于或等于x的最大整数  round(3.5)=4  返回离 x 最近的整数 exp(3) e的三次方 pow(3, 2)或power(3, 2)=9 3的2次方 pi()=3.141593 圆周率 sqrt(25)=5 rand() 0-1随机数

20. least(5, 6) 5 greatest(5, 6) 6

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

日期函数

SELECT NOW() 2021-01-12 15:29:11

SELECT SYSDATE() 2021-01-12 15:44:45

SELECT CURDATE(); 2021-01-12

SELECT CURRENT_DATE(); 2021-01-12

SELECT CURTIME(); 15:25:39

SELECT CURRENT_TIME(); 15:24:59

SELECT LOCALTIME() 2021-01-12 15:25:12

SELECT LOCALTIMESTAMP() 2021-01-12 15:24:53

SELECT CURRENT_TIMESTAMP() 2021-01-12 15:24:48

SELECT MAKEDATE(2017, 3); 2017-01-03

SELECT MAKETIME(11, 35, 4); 11:35:04

SELECT FROM_DAYS(365) x > 365 x <= 365 0000-00-00 x必须大于等于366

SELECT DATE("2017-06-15"); 2017-06-15

SELECT TIME("19:30:10"); 19:30:10

SELECT TIMESTAMP("2017-07-23", "13:10:11"); 2017-07-23 13:10:11

SELECT TIMESTAMP("2017-07-23"); 2017-07-23 00:00:00

SELECT SEC_TO_TIME(4320) 01:12:00

SELECT TIME_TO_SEC('1:12:00') 4320

SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); 2017-08-10

SELECT STR_TO_DATE("2017-10-August", "%Y-%d-%M"); 2017-08-10

SELECT TO_DAYS('0001-01-01 01:01:01') 366

SELECT LAST_DAY("2017-06-20"); 2017-06-30 指定日期所属月份最后一天

SELECT PERIOD_ADD(201703, 5); 201708 不能加分隔符

SELECT PERIOD_DIFF(201703, 201710); 7 前-后

SELECT DATEDIFF('2001-01-01','2001-01-03')

SELECT TIMEDIFF("13:10:11", "13:10:12"); -00:00:01 前-后

SELECT ADDDATE('2011-11-11 11:11:11',1)

SELECT DATE_ADD('2011-11-11 11:11:11', INTERVAL 1 year) 时间日期可加

SELECT DATE_FORMAT('2011-11-11 14:11:11','%Y-%m-%d %h-%i-%s') 2011-11-11 02-11-11

SELECT DATE_FORMAT('2011-11-11','%Y-%m-%d %h-%i-%s') 2011-11-11 12-00-00 默认输出12点

SELECT DATE_FORMAT('2011-11-11 14:11:11','%Y-%m-%d %H-%i-%s') 2011-11-11 14-11-11

SELECT DATE_FORMAT('2011-11-11 14:11:11','%Y-%m-%d %T') 2011-11-11 14:11:11

SELECT DATE_FORMAT('2011-11-11 14:11:11','%Y-%m-%d %r') 2011-11-11 02:11:11 PM

SELECT TIME_FORMAT('11:11:11','%r')

SELECT TIME_FORMAT('11:11:11','%T')

SELECT DATE_SUB('2011-11-11 14:11:11', INTERVAL 1 hour) 2011-11-11 13:11:11

SELECT SUBDATE('2011-11-11 11:11:11', 1) 2011-11-10 11:11:11

SELECT SUBTIME('2011-11-11 11:11:11', 5) 2011-11-11 11:11:06

SELECT DAY("2017/06_5"); 5

SELECT HOUR('1:2:3')

SELECT SECOND('1:2:3')

SELECT MINUTE('1:02:3') 2

SELECT YEAR("2017-06-15"); 2017

SELECT YEARWEEK("2017-06-15"); 201724

SELECT MONTH('2011-10-12 13:14:15.456') 10

SELECT WEEK('2011-11-11 11:11:11') 45

SELECT WEEKDAY("2017-06-15"); 3

SELECT WEEKOFYEAR('2011-11-11 11:11:11') 45

SELECT DAYNAME('2011-11-11 11:11:11') Friday

SELECT DAYOFMONTH('2011-11-11 11:11:11') 11

SELECT DAYOFWEEK('2011-11-11 11:11:11') 6

SELECT DAYOFYEAR('2011-11-11 11:11:11')315

SELECT MONTHNAME('2011-11-11 11:11:11') November

SELECT MICROSECOND("2017-06-20 09:34:00.000023"); 23

SELECT QUARTER('2011-11-11 11:11:11') 4

SELECT EXTRACT(HOUR FROM '2011-11-12 13:14:15.456')

SELECT NOW() 2021-01-12 15:29:11

SELECT SYSDATE() 2021-01-12 15:44:45

SELECT CURDATE(); 2021-01-12

SELECT CURRENT_DATE(); 2021-01-12

SELECT CURTIME(); 15:25:39

SELECT CURRENT_TIME(); 15:24:59

SELECT LOCALTIME() 2021-01-12 15:25:12

SELECT LOCALTIMESTAMP() 2021-01-12 15:24:53

SELECT CURRENT_TIMESTAMP() 2021-01-12 15:24:48

SELECT MAKEDATE(2017, 3); 2017-01-03

SELECT MAKETIME(11, 35, 4); 11:35:04

SELECT FROM_DAYS(365) x > 365 x <= 365 0000-00-00 x必须大于等于366

SELECT DATE("2017-06-15"); 2017-06-15

SELECT TIME("19:30:10"); 19:30:10

SELECT TIMESTAMP("2017-07-23", "13:10:11"); 2017-07-23 13:10:11

SELECT TIMESTAMP("2017-07-23"); 2017-07-23 00:00:00

SELECT SEC_TO_TIME(4320) 01:12:00

SELECT TIME_TO_SEC('1:12:00') 4320

SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); 2017-08-10

SELECT STR_TO_DATE("2017-10-August", "%Y-%d-%M"); 2017-08-10

SELECT TO_DAYS('0001-01-01 01:01:01') 366

SELECT LAST_DAY("2017-06-20"); 2017-06-30 指定日期所属月份最后一天

SELECT PERIOD_ADD(201703, 5); 201708 不能加分隔符

SELECT PERIOD_DIFF(201703, 201710); 7 前-后

SELECT DATEDIFF('2001-01-01','2001-01-03')

SELECT TIMEDIFF("13:10:11", "13:10:12"); -00:00:01 前-后

SELECT ADDDATE('2011-11-11 11:11:11',1)

SELECT DATE_ADD('2011-11-11 11:11:11', INTERVAL 1 year) 时间日期可加

SELECT DATE_FORMAT('2011-11-11 14:11:11','%Y-%m-%d %h-%i-%s') 2011-11-11 02-11-11

SELECT DATE_FORMAT('2011-11-11','%Y-%m-%d %h-%i-%s') 2011-11-11 12-00-00 默认输出12点

SELECT DATE_FORMAT('2011-11-11 14:11:11','%Y-%m-%d %H-%i-%s') 2011-11-11 14-11-11

SELECT DATE_FORMAT('2011-11-11 14:11:11','%Y-%m-%d %T') 2011-11-11 14:11:11

SELECT DATE_FORMAT('2011-11-11 14:11:11','%Y-%m-%d %r') 2011-11-11 02:11:11 PM

SELECT TIME_FORMAT('11:11:11','%r')

SELECT TIME_FORMAT('11:11:11','%T')

SELECT DATE_SUB('2011-11-11 14:11:11', INTERVAL 1 hour) 2011-11-11 13:11:11

SELECT SUBDATE('2011-11-11 11:11:11', 1) 2011-11-10 11:11:11

SELECT SUBTIME('2011-11-11 11:11:11', 5) 2011-11-11 11:11:06

SELECT DAY("2017/06_5"); 5

SELECT HOUR('1:2:3')

SELECT SECOND('1:2:3')

SELECT MINUTE('1:02:3') 2

SELECT YEAR("2017-06-15"); 2017

SELECT YEARWEEK("2017-06-15"); 201724

SELECT MONTH('2011-10-12 13:14:15.456') 10

SELECT WEEK('2011-11-11 11:11:11') 45

SELECT WEEKDAY("2017-06-15"); 3

SELECT WEEKOFYEAR('2011-11-11 11:11:11') 45

SELECT DAYNAME('2011-11-11 11:11:11') Friday

SELECT DAYOFMONTH('2011-11-11 11:11:11') 11

SELECT DAYOFWEEK('2011-11-11 11:11:11') 6

SELECT DAYOFYEAR('2011-11-11 11:11:11')315

SELECT MONTHNAME('2011-11-11 11:11:11') November

SELECT MICROSECOND("2017-06-20 09:34:00.000023"); 23

SELECT QUARTER('2011-11-11 11:11:11') 4

SELECT EXTRACT(HOUR FROM '2011-11-12 13:14:15.456')

可选参数

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 bin(x) 返回x的二进制编码

SELECT BINARY 'dfiro' = 'DFIRO' 0

SELECT  'dfiro' = 'DFIRO' 1

select case when user_level=1 then '1' when user_level=2 then '2' else '3' end from tb_user

select case user_level when 1 then '1' when 2 then '2' else '3' end from tb_user

SELECT CAST("2017-08-29" AS DATE); 数据类型转换

SELECT COALESCE(NULL, '', ' 1', 'runoob.com', NULL, 'google.com'); 返回第一个非null的表达式值

SELECT CAST("2017-08-29" AS DATE); 数据类型转换

SELECT COALESCE(NULL, '', ' 1', 'runoob.com', NULL, 'google.com');

SELECT CONV(15, 10, 2); 15转化为二进制数

SELECT CHARSET('ABC') utf8mb4

SELECT CHARSET(CONVERT('ABC' USING gbk)) gbk

select user() 用户名@ip

select SYSTEM_USER() 用户名@本地ip

select version()

select CURRENT_USER() 用户名@%

select SESSION_USER() 用户名@ip

select DATABASE()

select isnull('') 0

select isnull(null) 1

select if(1=1, 2, 3) 2

select IFNULL(null, 2) 第一个不为null返回第一个 否则返回第二个

select NULLIF('ab','AB1') 两字符串相等(不区分大小写)返回NULL 否则返回第一个字符串