August 15, 2022
By: cisco
sql常用函数
数学函数
ABS(x) 返回x的绝对值
SELECT ABS(98.77),ABS(20-16.8),ABS(5.6*2),ABS(-0.3),ABS(-8),ABS(-(8/2));
CEILING(x) 返回大于等于x的最小整数值(向上取整)
SELECT CEILING(1.8),CEILING(5),CEILING(-3.5),CEILING(-3)
=>
CEILING(1.8) | CEILING(5) |CEILING(-3.5)|CEILING(-3)
2 | 5 | -3 | -3
FLOOR(x) 返回小于等于x的最大整数值(向下取整)
SELECT FLOOR(1.8) ,FLOOR(5),FLOOR(-3.5),FLOOR(-3)
=>
FLOOR(1.8) |FLOOR(5)|FLOOR(-3.5)|FLOOR(-3)
1 | 5 | -4 | -3
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。
SELECT ROUND(1.23),ROUND(1.56),ROUND(1.65090384,3),ROUND(1.65,0)
=>
ROUND(1.23)|ROUND(1.56)|ROUND(1.65090384,3)|ROUND(1.65,0)
1 | 2 | 1.651 | 2
SIGN(x) 返回代表数字x的符号的值
对一个自然数进行判断，如果为零，返回0，如果为负数，统一返回-1，如果为正数，统一返回1
SELECT SIGN(-12),SIGN(0),SIGN(2)
=>
SIGN(-12)|SIGN(0)|SIGN(2)|SIGN(100)
-1 | 0 |1 |1
聚合函数
AVG(col) 返回指定列的平均值
COUNT(col) 返回指定列中非NULL值的个数
MIN(col) 返回指定列的最小值
MAX(col) 返回指定列的最大值
SUM(col) 返回指定列的所有值之和
GROUPCONCAT(col) 返回由属于一组的列值连接组合而成的结果
SELECT GROUP_CONCAT(size_no),body_type FROM t_spec WHERE company_id=550 AND category_id=5501002 GROUP BY body_type
=>
GROUP_CONCAT(size_no)|body_type
84.0,86.0,88.0,90.0 | A
92.0,94.0,96.0,98.0 | B
字符串函数
CONCAT(s1,s2…,sn)将s1,s2…,sn连接成字符串
SELECT CONCAT(category_id,body_type)as a,CONCAT_WS('-',category_id,body_type,size_spec)as b FROM t_spec WHERE company_id=550 AND category_id=5501002 AND body_type='A'
=>
a | b
5501002A |5501002-A-74/A
5501002A | 5501002-A-78/A
CONCATWS(sep,s1,s2…,sn)将s1,s2…,sn连接成字符串，并用sep字符间隔
SELECT CONCAT_WS('-',category_id,body_type,size_spec) as b,CONCAT_WS('@',category_id,body_type,size_spec) as c
FROM t_spec WHERE company_id=550 AND category_id=5501002 AND body_type='A'
=> b | c
5501002-A-74/A | 5501002@A@74/A
5501002-A-76/A | 5501002@A@76/A
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
SELECT INSERT('hello,world,verygood',7,5, '你好')
=>
hello,你好,verygood
FINDINSET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
起始数是从1开始计算起的; 若str存在list中,则返回该str所在位置的数字(大于0); 如果str不在strlist 或strlist 为空字符串,则返回值为 0,并且如任意一个参数为空，则返回值为 0 也可以说返回NULL
SELECT FIND_IN_SET('1','0,1,3,5')as a,FIND_IN_SET('重庆','北京,上海') as a1,FIND_IN_SET('青岛','青岛-济南-威海') as a2,FIND_IN_SET('', '1,2,3')as kongval,FIND_IN_SET(NULL, '1,3') as nullval
=> a | a1 | a2 | kongval | nullval
2 | 0 | 0 | 0 | null
;; find_in_set()与in()区别
select * from per where find_in_set(id,'2,3,4,5,6');
select * from per where id in(2,3,4,5,6);
find_in_set() like()
10
10,1,2
like %1% => 10 ,1
find_in_set(1,'10,1,2')
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果
LEFT(str,x)返回字符串str中最左边的x个字符
SELECT LEFT('hello', 1),LEFT('hello',0),LEFT('hello',5),LEFT('hello', 9),LEFT(NULL,1)
=>
LEFT('hello', 1) |LEFT('hello',0)|LEFT('hello',5)|LEFT('hello', 9)|LEFT(NULL,1)
h | | hello | hello | NULL
LENGTH(s) 返回字符串str中的字符数
LTRIM(str) 从字符串str中切掉开头的空格
select LTRIM(' he llo') as a,LTRIM(' world') as b
=>
a | b
he llo| world
RIGHT(str,x) 返回字符串str中最右边的x个字符
SELECT RIGHT('hello', 1),RIGHT('hello',0),RIGHT('hello',5),RIGHT('hello', 9)
=>
RIGHT('hello', 1) | RIGHT('hello',0)|RIGHT('hello',5)|RIGHT('hello', 9)
o | | hello | hello
STRCMP(s1,s2)比较字符串s1和s2
s1=s2,返回0; s1<s2,返回-1; s1>s2,返回1; 如果一个或两个字符串均为NULL,返回null
SELECT ASCII('A'),ASCII('B'),STRCMP('A', 'A'),STRCMP('A','B'),STRCMP('B','A'),STRCMP(NULL, 'a');
=>
ASCII('A')|ASCII('B')|STRCMP('A', 'A')|STRCMP('A','B')|STRCMP('B','A')|STRCMP(NULL, 'a')
65 | 66 | 0 | -1 | 1 | NULL
TRIM(str)去除字符串首部和尾部的所有空格
SELECT TRIM(' hello '),TRIM(' hello'),TRIM('hello '),TRIM('he llo')
=>
TRIM(' hello ')|TRIM(' hello')|TRIM('hello ')|TRIM('he llo')
hello | hello | hello | he llo
日期和时间函数
NOW() 返回当前的日期和时间
CURDATE()或CURRENTDATE() 返回当前的日期
CURTIME()或CURRENTTIME() 返回当前的时间
DATEADD(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化)
DATESUB(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化)
DATEDIFF(startdate,enddate) 返回结束日期减去开始日期的天数
DATEFORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名
FROMUNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
QUARTER(date) 返回date在一年中的季度(1~4)
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)
加密函数
控制流函数
CASE WHEN[test1] THEN [result1]…ELSE [default] END如果testN是真，则返回resultN，否则返回default
SELECT body_type,CASE
WHEN body_type ='A' THEN '紧身'
WHEN body_type ='B' THEN '合体'
WHEN body_type ='C' THEN '宽松'
ELSE'未知'
END as name
FROM t_spec WHERE company_id=550 AND category_id=5501002
=> body_type|name
A | 紧身
B | 合体
C | 宽松
NULL | 未知
CASE [test] WHEN[val1] THEN [result]…ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default
SELECT body_type,CASE body_type
WHEN 'A' THEN '紧身'
WHEN 'B' THEN '合体'
WHEN 'C' THEN '宽松'
ELSE'未知'
END as name
FROM t_spec WHERE company_id=550 AND category_id=5501002
=> body_type|name
A | 紧身
B | 合体
C | 宽松
NULL | 未知
IF(test,t,f) 如果test是真，返回t；否则返回f
select if('a' ,'a','b'),if(null,'a','b')
=> a | b
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
格式化函数
DATEFORMAT(date,fmt) 依照字符串fmt格式化日期date值
TIMEFORMAT(time,fmt) 依照字符串fmt格式化时间time值
FORMAT(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数
INETATON(ip) 返回IP地址的数字表示
INETNTOA(num) 返回数字所代表的IP地址
类型转化函数
CAST( expression AS datatype ) 它可以把一个值转化为指定的数据类型
类型有:BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED
CONVERT(datatype[(length)], expression [, style]) 把一个值转化为指定的数据类型
在时间转化中一般用到convert,因为它比cast多加了一个style,可以转化成不同时间的格式。