mysql函数总结

1 字符串函数

SELECT title,LENGTH(title),SUBSTRING(title,1,1),LEFT(title,1),RIGHT(title,1),UPPER(title),LOWER(title),REPLACE(title, 'g' ,'l'),CONCAT('title:',title) FROM blog_note

 

 

SELECT title,LENGTH(title),SUBSTR(title,1,1),CONCAT('title',':',title),TRIM(title),LPAD(title,16,' '),RPAD(title,16,' ') FROM blog_note

 

 

SELECT CHARSET('A'),CHARSET(CONVERT('A' USING GBK)), ASCII('C'),REPEAT('ABC',3),REVERSE('abc'),POSITION('b' in 'abc'),STRCMP("A", "B")

 

 

2 数学函数

SELECT ROUND(PI(),4),FLOOR(PI()),CEIL(PI()),TRUNCATE(PI(),5)

 

 

SELECT MOD(10,3),POW(2,3)

 

 

 3 IF(expr,val1,val2)、IFNULL(val1,val2)、NULLIF(val1,val2)、ISNULL(val)

if(表达式,结果1,结果2)   表达式为true，则返回结果1，否则返回结果2

SELECT if(TRUE,1,0),if(false,1,0)

 

IFNULL(值1,值2)  如果值1位null则返回值2，否则返回值1

SELECT IFNULL(1,2),IFNULL(NULL,2)

  

NULLIF(值1,值2)   如果值1和值2相等则返回null，否则返回值1

SELECT NULLIF('1','2'),NULLIF('1','1')

  

ISNULL(值) 判断值是否为null

SELECT ISNULL(''),ISNULL(NULL),ISNULL('null')

 

 4 FIND_IN_SET(needle,haystack) 、LOCATE(needle,haystack)

FIND_IN_SET(needle,haystack)函数 返回指定字符串在以逗号分隔的字符串列表中的位置

LOCATE(needle,haystack)函数 返回指定子字符串在字符串中的位置

INSTR(haystack,needle)函数 返回指定子字符串在字符串中的位置

SELECT FIND_IN_SET('sport','eat,health,sport,truth'),FIND_IN_SET('eat','eat,health,sport,truth'),FIND_IN_SET('sleep','eat,health,sport,truth')

 

 

SELECT LOCATE('sport','eat,health,sport,truth'),LOCATE('eat','eat,health,sport,truth'),LOCATE('sleep','eat,health,sport,truth')

 

 

SELECT  INSTR('eat,health,sport,truth','sport'), INSTR('eat,health,sport,truth','eat'), INSTR('eat,health,sport,truth','sleep')

  

 5 ELT(N,str1,str2,str3...)、INTERVAL(N,N1,N2,N3...)

ELT(N,str1,str2,str3...)  依N的值返回第N+1个参数，不存在则返回null
INTERVAL(N,N1,N2,N3...) 依N的值是否大于N(i)小于N(i+1)，返回i

SELECT ELT(1,'a','b','c'),ELT(2,'a','b','c'),ELT(4,'a','b','c')

 

 

SELECT INTERVAL(50,60,90,100),INTERVAL(80,60,90,100),INTERVAL(93,60,90,95),INTERVAL(96,60,90,95)

 

SELECT id,score,interval(score,0,60,85,90), ELT(interval(score,0,60,85,90),'不及格','及格','良好','优秀') FROM s_score 

 

SELECT score ,case when score<60 then '不及格' when score>=60 AND score<85 then '及格' when score>=85 AND score<90 then '良好' else '优秀' END AS level FROM s_score  

 

  6 类型转换

SELECT CAST(PI() AS SIGNED),CAST("2022-08-29" AS DATE)

 

SELECT CONVERT("2022-12-20",DATE),CONVERT("08:30:00",TIME),CONVERT("2017-08-29 08:30:00",DATETIME),CONVERT(97,CHAR),CONVERT('A',BINARY)

 7 进制转换

SELECT BIN(10),BIN(15),CONV(15,10,2),CONV(1111,2,10),CONV(1111,2,16),BINARY 10,BINARY 'A'

 

8  COALESCE返回元素中第一个非null的值

SELECT COALESCE(NULL,'NULL', 'title'),COALESCE(NULL,'', 'title'),COALESCE(NULL,NULL, 'title');