mysql高级函数FIND_IN_SET,ENUM和SET,LOCATE,ELT,FIELD,INTERVAL,COUNT,CAST,NULLIF,ISNULL,IFNULL,IF,CONVERT,COALESCE

# FIND_IN_SET FIND_IN_SET(needle,haystack);
/**
第一个参数needle是要查找的字符串。
第二个参数haystack是要搜索的逗号分隔的字符串列表。
**/
SELECT FIND_IN_SET('111','222,111,333,444'); #查询结果：2
SELECT FIND_IN_SET('111','222,333,444'); #查询结果：0

## 加法
SELECT 1|4|16|2

/**
ENUM和SET
ENUM只取单值，但要注意，他的索引是从1开始，加了引号就是值，不加就是索引。

设定enum的格式：
enum("选项1","选项2","选项3",...)；
实际上，enum的选项都会对应一个数字，依次是1，2，3，4，5...，最多有65535个选项
加了引号

设定set的格式：
set("选项1","选项2","选项3",...)
同样的，set的每个选项值也对应一个数字，依次是1，2，4，8，16...，最多有64个选项
**/

## LOCATE(substr,str) , LOCATE(substr,str,pos)
SELECT LOCATE('111','abcdef111222333'); # 7
SELECT LOCATE('111','abcdef111222333',10); # 0
SELECT LOCATE('111','abcdef111222333',6); # 7
# locate相对于like语句的执行效率较高，所以正常可以考虑使用locate代替like。

# MySQL ELT()返回指定索引的参数值，函数的第一个参数是索引值，第二个参数开始以后是字符串类型的参数值。当索引的值小于1或者大于后面参数的个数时，函数返回null
# ELT(N,str1,str2,str3,...)
SELECT ELT(3,1,100,200); # 200
SELECT ELT(2,1,100,200); # 100
SELECT ELT(5,1,100,200); # null

/**
FIELD()函数
MySQL中的field（）函数，可以用来对SQL中查询结果集进行指定顺序排序
函数使用格式如下：
order by field(str，str1，str2，str3，str4……)，str与str1，str2，str3，str4比较，其中str指的是字段名字，
意为：字段str按照字符串str1，str2，str3，str4的顺序返回查询到的结果集。如果表中str字段值不存在于str1，str2，str3，str4中的记录，放在结果集最前面返回。

select * from ta order by field(name,'seiki','iris','xut');
不在str1，str2，str3中的内容，放在最前面返回，str值相同按照主键的顺序
**/

/**
INTERVAL(N,N1,N2,N3,..........)
其中，N是要判断的数值，N1,N2,N3,...是分段的间隔。
这个函数的返回值是段的位置：
如果N<N1，则返回0，
如果N1<=N<N2，则返回1，
如果N2<=N<N3，则返回2。
所以，区间是前闭后开的。
**/
# INTERVAL(N,N1,N2,N3,..........) 列表值必须是　　　　N1<N2<N3的形式才能正常工作。
SELECT INTERVAL(33,20,30,40,50,60); # 2
SELECT INTERVAL(55,20,30,40,50,60); # 4
SELECT INTERVAL(3,20,30,40,50,60); # 0
SELECT INTERVAL(NULL,20,30,40,50,60); # -1
SELECT INTERVAL('c','b','d'); # 2

#elt函数与interval实现分组统计

CREATE TABLE `k1` (
`id` INT (11),
`yb` INT (11)
);

INSERT INTO `k1` (`id`, `yb`) VALUES('1','100');
INSERT INTO `k1` (`id`, `yb`) VALUES('2','11');
INSERT INTO `k1` (`id`, `yb`) VALUES('3','5');
INSERT INTO `k1` (`id`, `yb`) VALUES('4','501');
INSERT INTO `k1` (`id`, `yb`) VALUES('5','1501');
INSERT INTO `k1` (`id`, `yb`) VALUES('6','1');

SELECT * FROM k1;

SELECT ELT(INTERVAL(d.yb,0, 100, 500, 1000), '1/less100', '2/100to500', '3/500to1000', '4/more1000') AS yb_level, COUNT(d.id) AS cnt
FROM k1 d
GROUP BY ELT(INTERVAL(d.yb, 0, 100, 500, 1000), '1/less100', '2/100to500', '3/500to1000', '4/more1000K');

# COUNT 带条件计数
# count(*) 包含 null 值的条目，count(字段) 则不包含 null的字段
SELECT COUNT(yb > 200 OR NULL) FROM k1; # 2
SELECT COUNT(IF(yb > 200, 1, NULL)) FROM k1; # 2
SELECT COUNT(CASE WHEN yb > 200 THEN 1 END) FROM k1; # 2

# CAST(x AS type)转换数据类型
/**
类型可以为：
CHAR[(N)] 字符型
DATE 日期型
DATETIME 日期和时间型
DECIMAL float型
SIGNED int
TIME 时间型
**/
SELECT CAST("2021-05-18" AS DATE); # 2021-05-18
SELECT CAST("20210518" AS DATE); # 2021-05-18
SELECT CAST("2021/05/18" AS DATE); # 2021-05-18

SELECT CAST('2015110315312675555555555555555555555555555555777' AS SIGNED) # -1
SELECT CAST('2015-11-03 15:31:26' AS CHAR) # 2015-11-03 15:31:26
SELECT CAST('2015-11-03 15:31:26' AS DATETIME) # 2015-11-03 15:31:26
SELECT CAST('2015-11-03 15:31:26' AS DATE) # 2015-11-03
SELECT CAST('2015-11-03 15:31:26' AS TIME) # 15:31:26

SELECT CAST('20' AS DECIMAL(10, 2)) # 20.00

# NULLIF(expr1, expr2)比较两个字符串，如果字符串 expr1 与 expr2 相等返回 NULL，否则返回 expr1
SELECT NULLIF(25, 25) ### null
SELECT NULLIF(25, 251) ### 25

# ISNULL(expression)判断表达式是否为 NULL
SELECT ISNULL(NULL); # 1 (是)
SELECT ISNULL(1+1); # 0 (否)
SELECT ISNULL(1=2); # 0 (否)

#IFNULL(v1,v2)如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
SELECT IFNULL(NULL,'Hello World'); #Hello World
SELECT IFNULL('hello','Hello World'); # hello

# IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。
SELECT IF(1 > 0,'正确','错误') # 正确

# CONVERT(s USING cs)函数将字符串 s 的字符集变成 cs
SELECT CHARSET('ABC') # utf-8
SELECT CHARSET(CONVERT('ABC' USING gbk)) # gbk

# COALESCE(expr1, expr2, ...., expr_n)返回参数中的第一个非空表达式（从左向右）
SELECT COALESCE(NULL, NULL, NULL, 'csdn.com', NULL, 'google.com'); # csdn.com

posted on 2023-02-02 17:04 oktokeep 阅读(180) 评论(0) 编辑收藏举报