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
