比较常用的mysql函数记录

SELECT FORMAT(250500.5634, 2) >= 251, round(250500.5634, 2)> 250500, FORMAT(250500.5634, 2) >2;

SELECT INSERT("google.com", 1, 6, "runoob");

SELECT LPAD('abc',20,'xx'), RPAD('abc',20,'xx');

SELECT MID("RUNOOB", 2, 3), SUBSTRING("RUNOOB", 2, 3), SUBSTR("RUNOOB", 2, 3);

SELECT POSITION('b' in 'abc'), LOCATE('b', 'abc');

SELECT REPLACE('abc','a','x'), REPLACE('abc','hh','x');

SELECT TRIM("  RUNOOB     ");

SELECT SPACE(10), REVERSE('abc') ;
SELECT STRCMP("runoob", "runoob"), STRCMP("runoob", "runoob2"), STRCMP("runoob", "runooa");

SELECT UPPER("runoob"), UCASE("runoob");
SELECT LOWER('RUNOOB'), LCASE("runoob");

select SUBSTRING_INDEX('a*b*c*d*e','*',3); # 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串


SELECT degrees(ATAN(1));
SELECT 10 DIV 0;

SELECT GREATEST(3, 12, 34, 8, 25), GREATEST("Google", "Runoob", "Apple"), LEAST(3, 12, 34, 8, 25), LEAST("Google", "Runoob", "Apple");

SELECT LN(2), EXP(3), LOG(2, 4);

SELECT MOD(5,2), PI(), SQRT(25);
SELECT RADIANS(180), DEGREES(PI());

SELECT RAND(), SIGN(-10), SIGN(0), SIGN(10);

SELECT TRUNCATE(1.23456,3), ROUND(1.23456, 3); # ROUND 会四舍五入；TRUNCATE 不会

SELECT CURRENT_TIMESTAMP(), DATEDIFF('2001-01-01','2001-02-02'), TIMESTAMPDIFF(DAY,'2001-01-01','2001-02-02'); # DATEDIFF(A, B)：return A-B
SELECT TIMEDIFF("13:10:11", "13:10:10"), TIMESTAMPDIFF(SECOND ,"2001-01-01 13:10:11", "2001-01-01 13:10:10"); # TIMEDIFF(A, B)：return A-B , TIMESTAMPDIFF(unit, A, B): return B -A

SELECT ADDDATE("2017-06-15", INTERVAL 15 MINUTE), DATE_ADD("2017-06-15", INTERVAL 15 MINUTE);
SELECT ADDDATE("2017-06-15 09:00:00", INTERVAL 15 MINUTE), DATE_ADD("2017-06-15 09:00:00", INTERVAL 15 MINUTE);
SELECT DATE_SUB("2017-06-15 09:00:00", INTERVAL 15 MINUTE), DATE_ADD("2017-06-15 09:00:00", INTERVAL -15 MINUTE), ADDDATE("2017-06-15 09:00:00", INTERVAL -15 MINUTE); # 三个效果一

SELECT SUBDATE('2017-06-15 09:00:00', 1 minute ); # 默认是天

SELECT DAYNAME('2011-11-11 11:11:11'), DAYOFMONTH('2011-11-11 11:11:11'), DAYOFWEEK('2011-11-11 11:11:11'), DAYOFYEAR('2011-11-11 11:11:11'), MONTHNAME('2011-11-11 11:11:11'), QUARTER('2011-11-11 11:11:11');

SELECT EXTRACT(SECOND_MICROSECOND FROM '2011-11-11 11:11:11.345'), EXTRACT(MICROSECOND FROM '2011-11-11 11:11:11.345');

SELECT TO_DAYS('0001-01-01 01:01:01'), TO_DAYS('0000-01-01 00:00:00'), TO_DAYS(NOW());

#高级函数
SELECT BIN(15);

SELECT (CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END) as e;
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); # 返回第一个非null的表达式

SELECT IF(1 > 0,'正确','错误'), IFNULL(null,'Hello Word'), ISNULL(NULL), LAST_INSERT_ID();
select NULLIF(25, 25), NULLIF(0, 1), NULLIF(NULL, 0);

# 2023-4-19
with rollup # group by 后再加上总的统计

rank() over(partition by a order by b) as rk # 根据a分组后再根据字段b排名


select id, taskType,@total:=@total + task.taskType as hh from task,(select @total:=0) tmp
where hh>20
limit 20; # 设置变量，然后统计计数

select 'A' as a
union
select 'B';

# group 分组后的统计其他列的函数 ：group_concat
select concat(upper(SUBSTR(from_which_table, 1, 1)), lower(SUBSTR(from_which_table, 2)))  from scenario_search_mapping;
select id, group_name, group_concat(from_which_table separator ';'),group_concat(id separator ';') from scenario_search_mapping group by group_name limit 3;