mysql中的函数

厉害的函数单独列出

group_contact

MySQL 字符串函数

ASCII-返回字符串的第一个字符的ASCII码

select ascii(ddd.NAME), ddd.NAME from dev_device ddd;

231,电气火灾探测器
231,电气火灾探测器
231,电气火灾探测器
231,电气火灾探测器
233,金阳医院5号楼2层过道-电气火灾探测器

CHAR_LENGTH-返回字符串的字符数

select char_length(ddd.NAME), ddd.NAME from dev_device ddd;

19,金阳医院5号楼2层过道-电气火灾探测器
6,烟感010B
8,一体化压力变送器
6,1层生产车间
7,电气火灾探测器
6,烟感020E

CHARACTER_LENGTH-返回字符串的字符数

select character_length(ddd.NAME), ddd.NAME from dev_device ddd;

25,市公卫救治中心（花果园）JB-TTL-SH2112
9,红星美凯龙智慧用电
2,43
18,千禧物业负一层11楼道出口旁喷淋末端

CONCAT(s1,s2...sn)-字符串 s1,s2 等多个字符串合并为一个字符串

select concat(ddd.NAME,ddd.TYPE), ddd.NAME,ddd.TYPE from dev_device ddd;

烟感020EsmokeDetector,烟感020E,smokeDetector
电气火灾探测器electricalFire,电气火灾探测器,electricalFire
一体化压力变送器waterPipe,一体化压力变送器,waterPipe
市公卫救治中心（花果园）JB-TTL-SH2112fireControlHost,市公卫救治中心（花果园）JB-TTL-SH2112,fireControlHost
红星美凯龙智慧用电electricalFire,红星美凯龙智慧用电,electricalFire

::: warning CONCAT(‘MySQL’，NULL) 中有一个参数为 NULL，返回结果将为 NULL :::

CONCAT_WS(x, s1,s2...sn)-同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符

select concat_ws('||123||',ddd.NAME,ddd.TYPE), ddd.NAME,ddd.TYPE from dev_device ddd;

烟感020E||123||smokeDetector,烟感020E,smokeDetector
电气火灾探测器||123||electricalFire,电气火灾探测器,electricalFire
一体化压力变送器||123||waterPipe,一体化压力变送器,waterPipe
市公卫救治中心（花果园）JB-TTL-SH2112||123||fireControlHost,市公卫救治中心（花果园）JB-TTL-SH2112,fireControlHost

FIELD(s,s1,s2...)-返回第一个字符串 s 在字符串列表(s1,s2...)中的位置

select field('2','4','6','7','7','2','9') as position;
-- 5

FIND_IN_SET(s1,s2)-返回在字符串s2中与s1匹配的字符串的位置

select find_in_set('2','121,2334,4556,2,55f,f,56') as position;
-- 4

LOCATE(s1,s)-从字符串 s 中获取 s1 的开始位置

select locate('23','asdfasfjrltj34323');
-- 16
select locate('23','34323re');
-- 4

POSITION(s1 IN s)-从字符串 s 中获取 s1 的开始位置

select position('ht' in 'lihtringmin');
-- 3

FORMAT(x,n)-函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入

select format(34565.676334,3);
-- 34,565.676

select format(34565.676734,3);
-- 34,565.677

INSERT(s1,x,len,s2)-字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

select insert('fourfaith.com',1,7,'lim');
-- limth.com

LCASE(s)-将字符串s的所有字母变成小写字母

select lcase('asdfAJLFDjsdF');
-- asdfajlfdjsdf

LOWER(s)-将字符串 s 的所有字母变成小写字母

select lower('htRINg');
-- htring

UCASE(s)-将字符串转换为大写

UPPER(s)-将字符串转换为大写

LEFT(s,n)-返回字符串 s 的前 n 个字符

select left('asdfAJLFDjsdF',3);
-- asd

RIGHT(s,n)-返回字符串 s 的后 n 个字符

select right('htring',2);
-- ng

LPAD(s1,len,s2)-在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len

select lpad('htRINg', 9, 'l');
-- lllhtRINg

select lpad('htRINg', 9, 'liminlimin');
-- limhtRINg

RPAD(s1,len,s2)-在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len

select rpad('htring',9,'l');
-- htringlll

LTRIM(s)-去掉字符串 s 开始处的空格

select ltrim('  lm    htRINg');
-- lm    htRINg

RTRIM(s)-去掉字符串 s 结尾处的空格

select rtrim('htring    limin   ');
-- htring    limin

TRIM(s)-去掉字符串 s 开始和结尾处的空格

select trim('   htring limin ');

MID(s,n,len)-从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)

select mid('htRINgLimin',2,5);
-- tRINg

SUBSTR(s, start, length)-从字符串 s 的 start 位置截取长度为 length 的子字符串

select substr('htRINgLimin',2,5);
-- tRINg

SUBSTRING(s, start, length)-从字符串 s 的 start 位置截取长度为 length 的子字符串

select substring('htRINgLimin',2,5);
-- tRINg

REPEAT(s,n)-将字符串 s 重复 n 次

select repeat('htring',5);
-- htringhtringhtringhtringhtring

REPLACE(s,s1,s2)-将字符串 s2 替代字符串 s 中的字符串 s1

select replace('htring','h','limin');
-- limintring

REVERSE(s)-将字符串s的顺序反过来

select reverse('htring');
-- gnirth

SPACE(n)-返回 n 个空格

select space(9);

STRCMP(s1,s2)-比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1

select strcmp('htring','limin');
-- -1
select strcmp('mi','li');
-- 1
select strcmp('ht','ht');
-- 0

SUBSTRING_INDEX(s, delimiter, number)-返回从字符串 s 的第 number 个出现的分隔符 delimiter 之前的子串

如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。

select substring_index('h,t,r,i,n,g',',',3);
-- h,t,r
select substring_index('h,t,r,i,n,g',',',-2);
-- n,g
select substring_index('h,t,r,i,n,g',',',1);
-- h

MySQL 数字函数

函数名	描述	实例
`ABS(x)`	返回 x 的绝对值
`ACOS(x)`	求 x 的反余弦值(参数是弧度)
`ASIN(x)`	求反正弦值(参数是弧度)
`ATAN(x)`	求反正切值(参数是弧度)
`TAN(x)`	求正切值(参数是弧度)	SELECT TAN(1.75); -- -5.52037992250933
`COS(x)`	求余弦值(参数是弧度)	SELECT COS(2);
`SIN(x)`	求正弦值(参数是弧度)	SELECT SIN(RADIANS(30)) -- 0.5
`COT(x)`	求余切值(参数是弧度)	SELECT COT(6);
`ATAN2(n, m)`	求反正切值(参数是弧度)	SELECT ATAN2(-0.8, 2);
`DEGREES(x)`	将弧度转换为角度	SELECT DEGREES(3.1415926535898) -- 180
`RADIANS(x)`	将角度转换为弧度	180 度转换为弧度：SELECT RADIANS(180) -- 3.1415926535898
AVG(expression)	返回一个表达式的平均值，expression 是一个字段	返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products;
`CEIL(x)`	返回大于或等于 x 的最小整数	SELECT CEIL(1.5) -- 返回2
`CEILING(x)`	返回大于或等于 x 的最小整数	SELECT CEILING(1.5); -- 返回2
`FLOOR(x)`	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1
`ROUND(x)`	返回离 x 最近的整数	SELECT ROUND(1.23456) --1
`TRUNCATE(x,y)`	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) -- 1.234
`COUNT(expression)`	返回查询的记录总数，expression 参数是一个字段或者 * 号	返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
`n DIV m`	整除，n 为被除数，m 为除数	计算 10 除于 5：SELECT 10 DIV 5; -- 2
`POW(x,y)`	返回 x 的 y 次方	2 的 3 次方：SELECT POW(2,3) -- 8
`POWER(x,y)`	返回 x 的 y 次方	2 的 3 次方：SELECT POWER(2,3) -- 8
`EXP(x)`	返回 e 的 x 次方	计算 e 的三次方：SELECT EXP(3) -- 20.085536923188
`SQRT(x)`	返回x的平方根	25 的平方根：SELECT SQRT(25) -- 5
`LN`	返回数字的自然对数，以 e 为底。	返回 2 的自然对数：SELECT LN(2); -- 0.6931471805599453
`LOG(x)` 或 `LOG(base, x)`	返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。	SELECT LOG(20.085536923188) -- 3

`LOG10(x)`	返回以 10 为底的对数	SELECT LOG10(100) -- 2
`LOG2(x)`	返回以 2 为底的对数	返回以 2 为底 6 的对数：SELECT LOG2(6); -- 2.584962500721156
`GREATEST(expr1, expr2, expr3, ...)`	返回列表中的最大值	返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34

MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
`LEAST(expr1, expr2, expr3, ...)`	返回列表中的最小值	返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3

MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products;
`MOD(x,y)`	返回 x 除以 y 以后的余数	5 除于 2 的余数：SELECT MOD(5,2) -- 1
`PI()`	返回圆周率(3.141593）	SELECT PI() --3.141593
`RAND()`	返回 0 到 1 的随机数	SELECT RAND() --0.93099315644334
`SIGN(x)`	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1	SELECT SIGN(-10) -- (-1)
`SUM(expression)`	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

MySQL 日期函数

函数名	描述	实例
`ADDDATE(d,n)`	计算起始日期 d 加上 n 天的日期	select adddate('2020-12-12', 8);`2020-12-20`


`DATE_ADD(d，INTERVAL expr type)`	计算起始日期 d 加上一个时间段后的日期	SELECT ADDDATE('2011-11-11 11:11:11',1);`-> 2011-11-12 11:11:11`(默认是天)
`ADDTIME(t,n)`	n 是一个时间表达式，时间 t 加上时间表达式 n	加 5 秒：SELECT ADDTIME('2011-11-11 11:11:11', 5);`->2011-11-11 11:11:16 (秒)`

`DATE_SUB(date,INTERVAL expr type)`	函数从日期减去指定的时间间隔。	Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
`CURDATE()`	返回当前日期	SELECT CURDATE();`-> 2018-09-19`
`CURRENT_DATE()`	返回当前日期	SELECT CURRENT_DATE();-> 2018-09-19
`CURRENT_TIME()`	返回当前时间	SELECT CURRENT_TIME();`-> 19:59:02`
`CURTIME()`	返回当前时间	SELECT CURTIME();`-> 19:59:02`
`CURRENT_TIMESTAMP()`	返回当前日期和时间	SELECT CURRENT_TIMESTAMP();`-> 2018-09-19 20:57:43`
`LOCALTIME()`	返回当前日期和时间	SELECT LOCALTIME();`-> 2018-09-19 20:57:43`
`LOCALTIMESTAMP()`	返回当前日期和时间	SELECT LOCALTIMESTAMP();`-> 2018-09-19 20:57:43`
`NOW()`	返回当前日期和时间	SELECT NOW();`-> 2018-09-19 20:57:43`
`SYSDATE()`	返回当前日期和时间	SELECT SYSDATE();`-> 2018-09-19 20:57:43`
`LAST_DAY(d)`	返回给给定日期的那一月份的最后一天	SELECT LAST_DAY("2017-06-20");`-> 2017-06-30`
`MAKEDATE(year, day-of-year)`	基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期	SELECT MAKEDATE(2017, 3);`-> 2017-01-03`
`MAKETIME(hour, minute, second)`	组合时间，参数分别为小时、分钟、秒	SELECT MAKETIME(11, 35, 4);`-> 11:35:04`
`DATE()`	从日期或日期时间表达式中提取日期值	SELECT DATE("2017-06-15");`-> 2017-06-15`
`TIME(expression)`	提取传入表达式的时间部分	SELECT TIME("19:30:10");`-> 19:30:10`
`PERIOD_ADD(period, number)`	为年-月组合日期添加一个时段	SELECT PERIOD_ADD(201703, 5);`-> 201708`
`PERIOD_DIFF(period1, period2)`	返回两个时段之间的月份差值	SELECT PERIOD_DIFF(201710, 201703);`-> 7`
`DATEDIFF(d1,d2)`	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF('2001-01-01','2001-02-02');`-> -32`
`TIMEDIFF(time1, time2)`	计算时间差值	SELECT TIMEDIFF("13:10:11", "13:10:10");`-> 00:00:01`
`DATE_FORMAT(d,f)`	按表达式 f的要求显示日期 d	SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r');`-> 2011-11-11 11:11:11 AM`
`FROM_DAYS(n)`	计算从 0000 年 1 月 1 日开始 n 天后的日期	SELECT FROM_DAYS(1111);`-> 0003-01-16`
`TO_DAYS(d)`	计算日期 d 距离 0000 年 1 月 1 日的天数	SELECT TO_DAYS('0001-01-01 01:01:01');`-> 366`
`YEAR(d)`	返回年份	SELECT YEAR("2017-06-15");`-> 2017`
`QUARTER(d)`	返回日期d是第几季节，返回 1 到 4	SELECT QUARTER('2011-11-11 11:11:11');`-> 4`
`DAY(d)`	返回日期值 d 的日期部分	`SELECT DAY("2017-06-15");`-> 15
`HOUR(t)`	返回 t 中的小时值	SELECT HOUR('1:2:3');`-> 1`
`MINUTE(t)`	返回 t 中的分钟值	SELECT MINUTE('1:2:3');`-> 2`
`SECOND(t)`	返回 t 中的秒钟值	SELECT SECOND('1:2:3');`-> 3`
`MICROSECOND(date)`	返回日期参数所对应的微秒数	SELECT MICROSECOND("2017-06-20 09:34:00.000023");`-> 23`
`DAYNAME(d)`	返回日期 d 是星期几，如 Monday,Tuesday	SELECT DAYNAME('2011-11-11 11:11:11');`->Friday`
`MONTHNAME(d)`	返回日期当中的月份名称，如 November	SELECT MONTHNAME('2011-11-11 11:11:11');`-> November`
`MONTH(d)`	返回日期d中的月份值，1 到 12	SELECT MONTH('2011-11-11 11:11:11');`->11`
`WEEK(d)`	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEK('2011-11-11 11:11:11');`-> 45`
`WEEKDAY(d)`	日期 d 是星期几，0 表示星期一，1 表示星期二	SELECT WEEKDAY("2017-06-15");`-> 3`
`WEEKOFYEAR(d)`	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEKOFYEAR('2011-11-11 11:11:11');`-> 45`
`DAYOFMONTH(d)`	计算日期 d 是本月的第几天	`SELECT DAYOFMONTH('2011-11-11 11:11:11');`->11
`DAYOFWEEK(d)`	日期 d 今天是星期几，1 星期日，2 星期一，以此类推	SELECT DAYOFWEEK('2011-11-11 11:11:11');`->6`
`DAYOFYEAR(d)`	计算日期 d 是本年的第几天	SELECT DAYOFYEAR('2011-11-11 11:11:11');`->315`
`YEARWEEK(date, mode)`	返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推	SELECT YEARWEEK("2017-06-15");`-> 201724`
`EXTRACT(type FROM d)`	从日期 d 中获取指定的值，type 指定返回的值。	type可取值为：





















`SEC_TO_TIME(s)`	将以秒为单位的时间 s 转换为时分秒的格式	SELECT SEC_TO_TIME(4320);`-> 01:12:00`
`TIME_TO_SEC(t)`	将时间 t 转换为秒	SELECT TIME_TO_SEC('1:12:00');`-> 4320`
`STR_TO_DATE(string, format_mask)`	将字符串转变为日期	SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");`-> 2017-08-10`
`TIME_FORMAT(t,f)`	按表达式 f 的要求显示时间 t	SELECT TIME_FORMAT('11:11:11','%r');`11:11:11 AM`
`SUBDATE(d,n)`	日期 d 减去 n 天后的日期	SELECT SUBDATE('2011-11-11 11:11:11', 1);`->2011-11-10 11:11:11 (默认是天)`
`SUBTIME(t,n)`	时间 t 减去 n 秒的时间	SELECT SUBTIME('2011-11-11 11:11:11', 5);`->2011-11-11 11:11:06 (秒)`
`TIMESTAMP(expression, interval)`	单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和	SELECT TIMESTAMP("2017-07-23", "13:10:11");`-> 2017-07-23 13:10:11`
`timestampdiff`	返回两个时间的差值

timestampdiff

TIMESTAMPDIFF（时间单位，参数1，参数2）

可以设置参数，可以精确到天（DAY），小时（HOUR），分钟（MINUTE），秒（SECOND），毫秒（FRAC_SECOND（低版本不支持，可以用秒，再乘1000）），计算机制为参数2-参数1，最后结果为数字，单位为第一个参数设置的值

--相差1天
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
--相差49小时
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差2940分钟
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差176400秒
select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差大于等于15秒
SELECT * FROM 表名 WHERE   TIMESTAMPDIFF(SECOND,start_time(较小的时间),stop_time(较大的时间)) >= 15

常用日期信息获取

# 本年第一天（dayofyear返回日期在本年的第几天）
select date_sub(curdate(), interval dayofyear(curdate())-1 day);
SELECT curdate() - INTERVAL(dayofyear(curdate()) - 1) DAY;

# 本年最后一天
select concat(year(curdate()),'-12-31');

# 上年最后一天
select date_sub(curdate(), interval dayofyear(curdate()) day);

# 下年第一天（本年第一天加一年）
SELECT (curdate() - INTERVAL(dayofyear(curdate()) - 1) DAY) + INTERVAL 1 YEAR;

# 本月第一天
select date_add(curdate(), interval - day(curdate()) + 1 day);

# 本月最后一天
select last_day(curdate());

# 上月第一天
select date_add(curdate()-day(curdate())+1,interval -1 month);

# 上月最后一天
select last_day(date_sub(now(),interval 1 month));

# 下月第一天
select date_add(curdate()-day(curdate())+1,interval 1 month);

# 下月最后一天
select last_day(date_sub(now(),interval -1 month));

# 本月天数
select day(last_day(curdate()));

# 上月今天的当前日期
select date_sub(curdate(), interval 1 month);

# 上月今天的当前时间（时间戳）
select unix_timestamp(date_sub(now(),interval 1 month));

# 获取当前时间与上个月之间的天数
select datediff(curdate(), date_sub(curdate(), interval 1 month));

# 本周星期一：  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) DAY);

# 本周星期天：  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 6 DAY);

# 上周星期一：  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 7 DAY);

# 上周星期天：  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY);

MySQL 高级函数

函数名	描述	实例
`BIN(x)`	返回 x 的二进制编码	15 的 2 进制编码:`SELECT BIN(15); -- 1111`
`BINARY(s)`	将字符串 s 转换为二进制字符串	`SELECT BINARY "RUNOOB";`-> RUNOOB

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
-- ->1 > 0

函数名	描述	实例
`COALESCE(expr1, expr2, ...., expr_n)`	返回参数中的第一个非空表达式（从左向右）	`SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com');`-> runoob.com
`CONNECTION_ID()`	返回唯一的连接 ID	`SELECT CONNECTION_ID();`-> 4292835
`CONV(x,f1,f2)`	返回 f1 进制数变成 f2 进制数	`SELECT CONV(15, 10, 2);`-> 1111
`CAST(x AS type)`	转换数据类型	字符串日期转换为日期：`SELECT CAST("2017-08-29" AS DATE);`-> 2017-08-29
`CONVERT(s USING cs)`	函数将字符串 s 的字符集变成 cs	`SELECT CHARSET('ABC')`->utf-8;`SELECT CHARSET(CONVERT('ABC' USING gbk))`->gbk
`CURRENT_USER()`	返回当前用户	`SELECT CURRENT_USER();`-> guest@%
`SESSION_USER()`	返回当前用户	`SELECT SESSION_USER();`-> guest@%
`SYSTEM_USER()`	返回当前用户	`SELECT SYSTEM_USER();`-> guest@%
`USER()`	返回当前用户	`SELECT USER();`-> guest@%
`DATABASE()`	返回当前数据库名	`SELECT DATABASE();`-> runoob
`IF(expr,v1,v2)`	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	`SELECT IF(1 > 0,'正确','错误')`->正确
`IFNULL(v1,v2)`	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。	`SELECT IFNULL(null,'Hello Word')`->Hello Word
`ISNULL(expression)`	判断表达式是否为 NULL	`SELECT ISNULL(NULL);`->1
`LAST_INSERT_ID()`	返回最近生成的 AUTO_INCREMENT 值	`SELECT LAST_INSERT_ID();`->6
`NULLIF(expr1, expr2)`	比较两个字符串，`如果字符串 expr1 与 expr2 相等返回 NULL`，否则`返回 expr1`	`SELECT NULLIF(25, 25);`->
`VERSION()`	返回数据库的版本号	`SELECT VERSION()`-> 5.6.34

CONVERT & CAST

可用的类型
二进制,同带binary前缀的效果 : BINARY
字符型,可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
具体语法
CAST(value as type);
CONVERT(value, type);

-- 23
SELECT CONVERT('23',SIGNED);

-- 125
SELECT CAST('125e342.83' AS signed);
-- 3
SELECT CAST('3.35' AS signed);

窗口函数

日常工作中，经常会遇到需要在每组内排名

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

::: tip 窗口函数也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

1）专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2）聚合函数，如sum, avg, count, max, min等 :::

::: warning 书写原则因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。 :::

专用窗口函数rank

select *, 
rank() over ( partition by scc.COMPANY_ID order by scc.CREATE_TIME desc, scc.UPDATE_TIME desc) as ranking
 from sys_company_check scc

先按照单位分组，再按照创建时间降序，然后更新时间降序（如果排序的字段值相同，则ranking的值也相同）

partition by用来对表分组
order by子句的功能是对分组后的结果进行排序

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。

为什么叫“窗口”函数呢？这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。

其他专业窗口函数dense_rank, row_number

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

成绩	ranking	dense_rank	row_num
100	1	1	1
100	1	1	2
100	1	1	3
98	4	2	4

聚合函数作为窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。
不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算
如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可

::: tip 聚合函数作为窗口函数可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。 :::

窗口函数后续

partition子句可是省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序
但是，这就失去了窗口函数的功能，所以一般不要这么使用

select *,
   rank() over (order by 成绩 desc) as ranking
from 班级表

IP与整数互转

SELECT INET_ATON('192.168.172.3') from dual; -- 3232279555（192X256³+168X256²+172X256¹+3）
SELECT INET_NTOA(3232279555) dual; -- 192.168.172.3


-- 需要DECIMAL或两个bigint来存储
select inet6_aton('fc00:0:130f::9c0:876a:130b'); -- 0xFC000000130F0000000009C0876A130B
select inet6_ntoa(0xFC000000130F0000000009C0876A130B); -- fc00:0:130f::9c0:876a:130b

实战演练

需求：将数据库中每分钟一条的数据表，从 9:30 取到 22:00 ，以半小时为单位汇总，并输出 Excel。

数据表字段：id（序号）、incount（计数）、cdate（数据时间）
表名：m_temp

难点：时间处理

解决办法：使用 DATE_FORMAT、CONCAT、Date、Hour、Minute、Floor 函数将时间处理成半小时，在将这段时间的数据查询时输出 cdate 统一变为 09:30，然后在 group by cdate ，用 sum（incount）求和。

1.处理时间：

示例：2018-08-21 09:30:00 至 2018-08-21 22:00:00

第一个半小时 09:30 至 10:00

将这段时间的数据查询时输出 cdate 统一变为 09:30，然后在 group by cdate ，sum（incount）

将分钟取出 /30 , 0-29 分结果为 0 ，30-59分结果为 1 再乘以 30 分钟即变为 00 或者 30

date(cdate)，取出年月日；

hour(cdate)，取出小时；

minute(cdate)，取出分钟；

计算（minute(cdate)）/30 )*30，结果为 0 或30；

floor( （minute(cdate)）/30 )*30)，处理成整数；

concat(date(cdate),' ',hour(cdate),':',floor( （minute(cdate)）/30 )*30) ，按照日期格式进行拼接

DATE_FORMAT( concat(date(cdate),' ',hour(cdate),':',floor( minute(cdate)/30 )*30+12) ,'%Y-%m-%d %H:%i') ，转换成date类型

完整 SQL

select sum(incount),dataStartTime from (
select incount, DATE_FORMAT( concat(date(cdate),' ',hour(cdate),':',floor( minute(cdate)/30 )*30+12) ,'%Y-%m-%d %H:%i')  as dataStartTime   
 from m_temp WHERE cdate>='2018-08-21 09:30' and cdate<='2018-08-21 22:00'  ORDER BY dataStartTime
) a
group by DATE_FORMAT( dataStartTime ,'%Y-%m-%d %H:%i')
into outfile('c:/2018-08-21Data.xls');

Keys	Action
`?`	Open this help
`n`	Next page
`p`	Previous page
`s`	Search