# mysql中的函数

# 厉害的函数单独列出

group_contact

# MySQL 字符串函数

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

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

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

1
2
3
4
5

# CHAR_LENGTH-返回字符串的字符数

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

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

1
2
3
4
5
6

# CHARACTER_LENGTH-返回字符串的字符数

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

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

1
2
3
4

# 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

1
2
3
4
5

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

1
2
3
4

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

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

1
2

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

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

1
2

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

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

1
2
3
4

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

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

1
2

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

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

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

1
2
3
4
5

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

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

1
2

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

select lcase('asdfAJLFDjsdF');
-- asdfajlfdjsdf

1
2

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

select lower('htRINg');
-- htring

1
2

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

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

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

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

1
2

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

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

1
2

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

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

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

1
2
3
4
5

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

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

1
2

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

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

1
2

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

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

1
2

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

select trim('   htring limin ');

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

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

1
2

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

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

1
2

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

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

1
2

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

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

1
2

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

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

1
2

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

select reverse('htring');
-- gnirth

1
2

# 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

1
2
3
4
5
6

# 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

1
2
3
4
5
6

# 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
		SELECT LOG(2, 4); -- 2
`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
		返回以下字符串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
`LEAST(expr1, expr2, expr3, ...)`	返回列表中的最小值	返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3
		返回以下字符串列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); -- Apple
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`
		select adddate('2020-12-12', interval 8 day );`2020-12-20`
		SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE);-> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)
`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 (秒)`
		添加 2 小时, 10 分钟, 5 秒:SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); `-> 2020-06-15 11:44:26`
`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可取值为：
		MICROSECOND
		SECOND
		MINUTE
		HOUR
		DAY
		WEEK
		MONTH
		QUARTER
		YEAR
		SECOND_MICROSECOND
		MINUTE_MICROSECOND
		MINUTE_SECOND
		HOUR_MICROSECOND
		HOUR_SECOND
		HOUR_MINUTE
		DAY_MICROSECOND
		DAY_SECOND
		DAY_MINUTE
		DAY_HOUR
		YEAR_MONTH
		SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11');`-> 11`
`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

1
2
3
4
5
6
7
8
9
10

# 常用日期信息获取

# 本年第一天（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);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

# 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

1
2
3
4
5
6
7

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

1
2
3
4
5
6
7
8

函数名	描述	实例
`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);

1
2
3
4
5
6
7

# 窗口函数

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

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

窗口函数

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

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

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

书写原则

因为窗口函数是对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

1
2
3

先按照单位分组，再按照创建时间降序，然后更新时间降序（如果排序的字段值相同，则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 班级表

1
2
3
4
5

成绩	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 班级表

1
2
3
4
5
6
7

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

聚合函数作为窗口函数

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

# 窗口函数后续

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

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

1
2
3

# 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

1
2
3
4
5
6
7

# 实战演练

需求：将数据库中每分钟一条的数据表，从 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');

1
2
3
4
5
6