常用SQL函数

常用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)