常用SQL函数主要包括以下几类:数值类、字符串类、时间日期类、数据转换、流程控制和其它。
流程控制
SQL比较运算符:
=、<=>、<> (!=)、<=、>=、>、IS NULL、IS NOT NULL、LEAST、GREATEST、BETWEEN . . . AND. . . 、ISNULL、IN、NOT IN、LIKE、REGEXP
判断值为null或空字符串:
ISNULL(NAME)=1) || (LENGTH(trim(NAME))=0
if
语法:IF(表达式1,表达式2,表达式3)
如果表达式1位true,则执行表达式2,否则执行表达式3.
表达式1比较运算符:
(1)等于=
(2)不等于!=
(3)大于>
(4)小于<
表达式2、表达式3如果是SELECT子句,需要括号。
IF(tmp.internal = 3,(SELECT name FROM sys_org WHERE id= (SELECT parent_id FROM sys_org WHERE id = tmp.parent_id)),org.name) AS company
ifnull
null值与具体值计算,如相加,结果也是null,此时要给null一个备用值。
IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。
语法:IFNULL(表达式, 备用值)
如果第一个参数的表达式为 NULL,则返回第二个参数的备用值。
case…when
语法:
CASE开始,END结束
WHEN条件,TEHN执行符合条件的表达式
ELSE以上条件都不符合执行的表达式
示例:
SELECT
CASE
WHEN tmp.internal = 3 THEN (SELECT name FROM sys_org WHERE id= (SELECT parent_id FROM sys_org WHERE id = tmp.parent_id))
WHEN tmp.internal = 2 THEN (SELECT name FROM sys_org WHERE id = tmp.parent_id)
ELSE org.name
END
AS company
FROM
tmp
字符串类
长度length
语法:
LENGTH(参数)
注意:一个中文字符长度为3位。
# 查询列数据长度
SELECT LENGTH(code) FROM sys_org
SELECT LENGTH('hello');# 5
字符查找函数instr
简洁语法:instr(源字符串, 目标字符串)
获取目标字符串在源字符串中的索引,没有则返回0
【注意】索引从1开始
SELECT INSTR('hello', 'p');# 0
SELECT INSTR('hello', 'h');# 1
SELECT INSTR('hello', 'ell');# 2
语法:instr(str, substr, position, occurrence)
返回子字符串在源字符串中的位置(字符串位置从1开始,而不是从0开始) ,没找到返回0
(1)str 源字符串
(2)substr 子字符串
(3)position 可选(默认为1),检索位置,参数为正时,从左向右检索,参数为负时,从右向左检索
(4)occurrence 可选(默认为1),检索子串出现次数(即子串在源串第几次出现),值只能为正整数,否则会报错
【注意】字符串不区分大小写。
SELECT instr('province', 'P') // 1
instr与like
在SQL中,instr函数常用来替换like。
instr函数常用在where后面,与like类似,与like区别:在没有索引的前提下,当数据量比较大时,instr要比like效率高。
在where判断条件中“instr(源字符串、子字符串) !=0”和“instr(源字符串、子字符串)”效果等同,都是用于判断源字符串是否包含目标字符串。
示例:效果相同的like和instr
// like
a.contacts like concat('%',#{contacts},'%')
// instr
INSTR(a.contacts , #{contacts}) != 0
INSTR(a.contacts , #{contacts})
instr和like区别:
(1)instr是一个函数,可以建立函数索引,如果过滤的条件有索引,那么instr就可以提高性能。
(2)like查询时,以’%'开头,列所加的索引是不起作用的。
(3)在没有索引的前提下,当数据量比较大时,instr要比like效率高。
从左侧开始截取字符串
# 从左侧截取3个字符
SELECT LEFT('hello', 3);
结果:hel
从右侧开始截取字符串
# 从右侧截取3个字符串
SELECT RIGHT('hello', 3);
结果:llo
从任意位置截取字符串substring
substring(字符串, 开始截取索引, 截取长度)
字符串索引从1开始。
# substring(name,5,3) 截取name这个字段从第5个字符开始,只截取之后的3个字符
SELECT SUBSTRING('成都融资事业部',5,3)
结果:事业部
字符串拼接concat
MySQL 中的 CONCAT(sl,s2,…) 函数返回结果为连接参数产生的字符串,有一个或多个参数。
SELECT name, grade , CONCAT(name,'-',grade) AS res FROM score
字符串group_concat
将group by产生的同一个分组中的值连接起来,返回一个字符串结果.
常用与将一列的字符串拼接为一个数据。
语法:
group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
时间日期类
当前时间now()
获取的格式2021-10-28 16:21:42
SELECT NOW();
当前日期curdate()
获取的格式2021-10-28
SELECT curdate();
当前时间戳
select REPLACE(unix_timestamp(current_timestamp(3)),'.','');
SELECT unix_timestamp(current_timestamp(3))
时间格式化date_format
【语法】DATE_FORMAT(时间数据,格式)
# 获取当前年,4位数
SELECT DATE_FORMAT(NOW(),'%Y');
结果:2021
常用的格式
(1)年-月-日,形如2021-10-28
SELECT DATE_FORMAT(now(),'%Y-%m-%d')
(2)年-月-日 时:分:秒,形如2021-10-28 16:28:17
SELECT DATE_FORMAT(now(),'%Y-%m-%d %T')
所有格式链接https://www.w3school.com.cn/sql/func_date_format.asp
时间增加date_add
常用于获取前/后几时/天/月/年的时间。
【语法】DATE_ADD(date,INTERVAL expr type)
返回开始时间date增加后的时间,字符串类型。
date参数是合法的日期表达式。注意可以是时间,不一定是日期。
INTERVAL是关键字。
expr参数是添加的时间间隔。可以是正数,也可以是负数。
type 参数可以是下列值:
一秒:SECOND
一分钟:MINUTE
一小时:HOUR
一天:DAY
一周:WEEK
一月:MONTH
一季度:QUARTER
一年:YEAR
更多type值点击链接
# 获取下一小时时间
select date_add(now(), INTERVAL 1 HOUR); # 2021-10-28 17:52:34
# 获取10月后的日期
select date_add(CURDATE(), INTERVAL 10 MONTH); # 2022-08-28
# 获取下一年的日期
select date_add(CURDATE(), INTERVAL 1 YEAR); # 2022-10-28
# 获取明天的日期
select date_add(curdate(), INTERVAL 1 DAY); # 2021-10-29
# 获取昨天的日期
select date_add(curdate(), INTERVAL -1 DAY); # 2021-10-27
时间减少date_sub
和date_add类似,只不过date_sub是在基础只上减少。
# 获取昨天的日期
select date_sub(CURDATE(), INTERVAL 1 DAY); # 2021-10-27
# 获取明天的日期
select date_sub(CURDATE(), INTERVAL -1 DAY); # 2021-10-29
日期计算datediff
返回两个日期之间的时间。
【语法】datediff(string date1, string date2)
返回值: int
说明: 返回date1减去date2的天数。
前面时间减去后面时间,返回差值。
SELECT DATEDIFF('2021-09-01', '2021-09-08');
结果:-7
注意在MySQL里datediff只支持计算天数。如果想计算小时,使用TIMESTAMPDIFF
SELECT TIMESTAMPDIFF(hour , '2021-09-01', '2021-09-02') AS Result;
结果:24
数值类
绝对值函数abs
语法:abs(数值a)
# 返回-12.1的绝对值
SELECT ABS(-12.1)
结果:12.1
取模函数mod
语法:mod(数值a,数值b)
返回数值a除以数值b的余数。
# 求34,4除以23的余数
SELECT MOD(34.4,23)
结果:11.4
求平方根sqrt
# 求23的平方根
SELECT sqrt(23)
结果:4.795831523312719
对于负数的平方根,返回null。
符号函数sign
符号函数,数值是正数、负数或零。
语法:sign(数值1)
返回参数的符号,数值1的值为负、零和正时返回结果依次为-1、0、1。
# 求-6的符号函数值
SELECT sign(-6)
结果:-1
向上取整ceil
语法:ceil(数值a)
向上取整,返回不小于数值a的最小整数值。
示例:
# 求2.1的向上取整值,-2.1的向上取整值
SELECT SELECT ceil(2.1),ceil(-2.1)
结果:3,-2
向下取整floor
语法:floor(数值a)
向下取整,返回不大于数值a的最大整数值。
# 求2.1向下取整值,-2.1向下取整值
SELECT floor(2.1),floor(-2.1)
结果:2,-3
随机数rand
语法:rand()
返回0到1内的随机浮点数。
# 结合向下取整函数,如果想要获取0—99内的任意随机整数
SELECT floor(rand()*100)
结果:58
四舍五入round
语法1:round(数值a)
返回数值a四舍五入后的整数。
# 求123.123456、123.654321四舍五入的整数值
SELECT round(123.123456),round(123.654321)
结果:123、124
语法2:round(数值a,数值b)
数值a:要四舍五入的主体数据;
数值b:如果为整数,保留几位有效小数。如果为负数,则小数点左移相应位数。
# 四舍五入123.123456并保留2位小数,四舍五入123.123456并百位取整
SELECT round(123.123456,2),round(123.123456,-2)
结果:123.12和100
数据转换
cast
其它
去重distinct
DISTINCT用于返回唯一不同的值。
注意distinct必须放在开头。
示例:
SELECT DISTINCT name, age
FROM test
上句返回name和age都不同的查询结果。
查询结果:
find_in_set
场景:有个文章表里面有个type字段,它存储的是文章类型,有 1头条、2推荐、3热点、4图文等等 。
现在有篇文章他既是头条,又是热点,还是图文,type中以 1,3,4 的格式存储。那我们如何用sql查找所有type中有4的图文类型的文章呢??
这时候就可以使用find_in_set函数。
select * from article where FIND_IN_SET('4',type)