MYSQL内置函数

参考

https://www.runoob.com/mysql/mysql-functions.html https://dev.mysql.com/doc/refman/5.7/en/preface.html http://itsoku.com/course/3

字符串函数

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html

字符串连接

大多数 DBMS 使用（|| oracle）或者（+ mssql）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()

函数	描述	实例
ASCII(s)	返回字符串 s 的第一个字符的 ASCII 码。	返回 CustomerName 字段第一个字母的 ASCII 码： SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
CHAR_LENGTH(s)	返回字符串 s 的字符数	返回字符串 RUNOOB 的字符数 SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
LENGTH(s)	返回字符串 s 的字节数，一个汉字三个字节	SELECT LENGTH('abcd我')     -- 7
CHARACTER_LENGTH(s)	返回字符串 s 的字符数，等同于 CHAR_LENGTH(s)	返回字符串 RUNOOB 的字符数 SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串，有null则返回null	合并多个字符串 SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn)	同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符	合并多个字符串，并添加分隔符： SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FIELD(s,s1,s2...)	返回第一个字符串 s 在字符串列表(s1,s2...)中的位置	返回字符串 c 在列表值中的位置： SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置	返回字符串 c 在指定字符串中的位置： SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n)	函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。	格式化数字 "#,###.##" 形式： SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
INSERT(s1,x,len,s2)	字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串	从字符串第一个位置开始的 6 个字符替换为 runoob： SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com
LOCATE(s1,s)	从字符串 s 中获取 s1 的开始位置	获取 b 在字符串 abc 中的位置： SELECT LOCATE('st','myteststring'); -- 5 返回字符串 abc 中 b 的位置： SELECT LOCATE('b', 'abc') -- 2
LCASE(s)	将字符串 s 的所有字母变成小写字母	字符串 RUNOOB 转换为小写： SELECT LCASE('RUNOOB') -- runoob
LEFT(s,n)	返回字符串 s 的前 n 个字符	返回字符串 runoob 中的前两个字符： SELECT LEFT('runoob',2) -- ru
LOWER(s)	将字符串 s 的所有字母变成小写字母	字符串 RUNOOB 转换为小写： SELECT LOWER('RUNOOB') -- runoob
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	将字符串 xx 填充到 abc 字符串的开始处： SELECT LPAD('abc',5,'xx') -- xxabc
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 RUNOOB开始处的空格： SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
MID(s,n,len)	从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)	从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO
POSITION(s1 IN s)	从字符串 s 中获取 s1 的开始位置	返回字符串 abc 中 b 的位置： SELECT POSITION('b' in 'abc') -- 2
REPEAT(s,n)	将字符串 s 重复 n 次	将字符串 runoob 重复三次： SELECT REPEAT('runoob',3) -- runoobrunoobrunoob
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 abc 中的字符 a 替换为字符 x： SELECT REPLACE('abc','a','x') --xbc
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来： SELECT REVERSE('abc') -- cba
RIGHT(s,n)	返回字符串 s 的后 n 个字符	返回字符串 runoob 的后两个字符： SELECT RIGHT('runoob',2) -- ob
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	将字符串 xx 填充到 abc 字符串的结尾处： SELECT RPAD('abc',5,'xx') -- abcxx
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 RUNOOB 的末尾空格： SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB
SPACE(n)	返回 n 个空格	返回 10 个空格： SELECT SPACE(10);
STRCMP(s1,s2)	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	比较字符串： SELECT STRCMP("runoob", "runoob"); -- 0
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串，等同于 SUBSTR(s, start, length)	从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING_INDEX(s, delimiter, number)	返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。	SELECT SUBSTRING_INDEX('a*b','*',1) -- a SELECT SUBSTRING_INDEX('a*b','*',-1)    -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1)    -- c
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 RUNOOB 的首尾空格： SELECT TRIM(' RUNOOB ') AS TrimmedString;
UCASE(s)	将字符串转换为大写	将字符串 runoob 转换为大写： SELECT UCASE("runoob"); -- RUNOOB
UPPER(s)	将字符串转换为大写	将字符串 runoob 转换为大写： SELECT UPPER("runoob"); -- RUNOOB

字符串函数测试

-- 6.27 字符串函数
--CHAR_LENGTH是统计字符个数，类似于MSSQL的LEN(str)函数
SELECT CHAR_LENGTH('abcd我'),CHAR_LENGTH('abcd'), LENGTH('abcd'), LENGTH('abcd我');-- 5 4  4  7 一个汉字三个字节

-- 字符串连接CONCAT(x,y) 返回xy,有null返回null
SELECT CONCAT('ab', 'cd'), CONCAT('ab', NULL, 'cd');-- abcd null

-- 字符替换 insert(str1,index,char_length,str2) 在str1的index开始替换char_length个字符
SELECT 
    INSERT('abcd', 2, 2, 'XYZ'),
    INSERT('abcd', - 1, 2, 'XYZ'), 
    INSERT('abcd', 2, 100, 'XYZ'); --相当于删除后面所有字符
-- 返回 aXYZd  abcd aXYZ

--这个函数类似于MSSQL的stuff函数：
SELECT 
    STUFF('abcd', 2, 2, 'XYZ'),
    STUFF('abcd', -1, 2, 'XYZ'),
    STUFF('abcd', 2, 100, 'XYZ')
-- 返回 aXYZd  NULL aXYZ

SELECT UPPER('abcd'), UCASE('abcd'), LOWER('ABCD'), LCASE('ABCD');

-- 左右字符填充 lpad(str,len,str2) 用str2填充str左侧直到len个字符,如果str长度<len 返回len个str的字符
SELECT LPAD('hello', 4, '?'), LPAD('hello', 10, '?');-- hell ?????hello
SELECT RPAD('hello', 4, '?'), RPAD('hello', 10, '?');-- hell hello?????
-- 去除左空格，右空格，左右空格 
SELECT LTRIM('  ABCD'), RTRIM(' ABCD '), TRIM(' ABCD  ');

-- 6.39 重复字符 
SELECT repeat('ABC',3); -- ABCABCABC
-- 6.43 替换字符串函数
SELECT replace('ABCCD','C','1'); -- AB11D
-- 6.44 STRCMP(s1,s2)函数：比较字符串 s1 和 s2 的 ASCII 码值的大小。如果 s1 比 s2 小，那么返回-1；
--如果 s1 与 s2 相等，那么返回 0；如果 s1 比 s2 大，那么返回 1
SELECT strcmp('ABC','ABCD'),strcmp('ABCD','ABC'),strcmp('ABC','ABC'); -- -1 1 0


--搜索字符串的位置跟MSSQL的CHARINDEX相同
--LOCATE(substr,str) 返回substr在str中的位置，大小写部分
SELECT LOCATE('A','123ABCD'),LOCATE('a','123ABCD') -- 4
-- POSITION(substr IN str) 功能同上
SELECT POSITION('A' IN '123ABCD')-- 4

SELECT INSTR("abcd",'b'); --返回2，从索引1开始
SELECT INSTR("abcd",'f'); --返回0

--字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
SELECT find_in_set('a','b,c,a,d,l') ,find_in_set('a','bcadl') --3,0


--NULLIF(value1,value2)字符串相同返回null，否则返回value1
SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');

--A,B,C
SELECT CONCAT_WS(',','A','B','C') 

正则表达式

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
	n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
	m 和 n 均为非负整数，其中n

SELECT 
    'xyz' REGEXP '^x',
    'xyz' REGEXP 'z$',
    'ssxy' REGEXP '.sxy',
    'ssxy' REGEXP '[an]',
    'aok' REGEXP '^[aeiou]|ok$',
	'0ok' REGEXP '^([aeiou]|ok)$'
-- 		1  1  1  0 1 0
-- ^ 匹配文本开始字符 ; $ 匹配文本结束字符 ; . 匹配任何单个字符 ; * 匹配零个或多个在它前面的字符
-- + 匹配前面的1个或多个字符 ;  <字符串> 包含知道字符串的文本; [字符集合] 匹配与字符集合中任何一个字符;
-- 字符串{n,} 匹配前面的字符串至少n次 ;字符串{n,m} 匹配前面的字符串至少n次不多于m次 ;

字符串分割

SUBSTRING_INDEX

参数名	解释
str	需要拆分的字符串
delim	分隔符，通过某字符进行拆分
count	当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第 n 个分隔符之后的所有字符。

# 7654,7698
 SELECT SUBSTRING_INDEX('7654,7698,7782,7788',',',2);
 
 # 7782,7788
 select SUBSTRING_INDEX('7654,7698,7782,7788',',',-2);

SUBSTRING

字符串切割 SUBSTRING(str pos len):从str字符串的pos位置获取长度为len的字符串，len可以省略

pos:从1开始
SELECT SUBSTRING('ABCDEFG',3),SUBSTRING('ABCDEFG',3,2),SUBSTRING('ABCDEFG',-3),SUBSTRING('ABCDEFG',-3,2)
-- CDEFG CD EFG EF
--在MSSQL中SUBSTRING函数是三个参数，SUBSTRING('ABCDEFG',3,2)同样返回CD

数值函数

基本数值函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值： SELECT ABS(-1) -- 返回1
ACOS(x)	求 x 的反余弦值（单位为弧度），x 为一个数值	SELECT ACOS(0.25);
ASIN(x)	求反正弦值（单位为弧度），x 为一个数值	SELECT ASIN(0.25);
ATAN(x)	求反正切值（单位为弧度），x 为一个数值	SELECT ATAN(2.5);
ATAN2(n, m)	求反正切值（单位为弧度）	SELECT ATAN2(-0.8, 2);
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
COS(x)	求余弦值(参数是弧度)	SELECT COS(2);
COT(x)	求余切值(参数是弧度)	SELECT COT(6);
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或者 * 号	返回 Products 表中 products 字段总共有多少条记录： SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)	将弧度转换为角度	SELECT DEGREES(3.1415926535898) -- 180
n DIV m	整除，n 为被除数，m 为除数	计算 10 除于 5： SELECT 10 DIV 5; -- 2
EXP(x)	返回 e 的 x 次方	计算 e 的三次方： SELECT EXP(3) -- 20.085536923188
FLOOR(x)	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数： SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...)	返回列表中的最大值	返回以下数字列表中的最大值： SELECT GREATEST(3, 12, 34, 8, 25); -- 34 返回以下字符串列表中的最大值： SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob
LEAST(expr1, expr2, expr3, ...)	返回列表中的最小值	返回以下数字列表中的最小值： SELECT LEAST(3, 12, 34, 8, 25); -- 3 返回以下字符串列表中的最小值： SELECT LEAST("Google", "Runoob", "Apple"); -- Apple
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
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值： SELECT MAX(Price) AS LargestPrice FROM Products;
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
POW(x,y)	返回 x 的 y 次方	2 的 3 次方： SELECT POW(2,3) -- 8
POWER(x,y)	返回 x 的 y 次方	2 的 3 次方： SELECT POWER(2,3) -- 8
RADIANS(x)	将角度转换为弧度	180 度转换为弧度： SELECT RADIANS(180) -- 3.1415926535898
RAND()	返回 0 到 1 的随机数	SELECT RAND() --0.93099315644334
ROUND(x)	返回离 x 最近的整数	SELECT ROUND(1.23456) --1
SIGN(x)	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1	SELECT SIGN(-10) -- (-1)
SIN(x)	求正弦值(参数是弧度)	SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x)	返回x的平方根	25 的平方根： SELECT SQRT(25) -- 5
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和： SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)	求正切值(参数是弧度)	SELECT TAN(1.75); -- -5.52037992250933
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) -- 1.234

-- 6.1 函数
-- ABS() 绝对值
SELECT ABS(- 22), ABS(22);

-- PI() 圆周率3.141593
SELECT PI();

-- SQRT() 平方根
SELECT SQRT(9);

-- MOD(X,Y) 求余数
SELECT MOD(31, 8);-- 7 等同 SELECT 31%8,  31/7在mysql5.7中返回3.8750

-- CEIL CEILING 向上取整数
SELECT CEIL(- 3.15), CEIL(3.15);-- -3   4

-- FLOOR  向下取整数
SELECT FLOOR(- 3.15), FLOOR(3.15);-- -4 3 

-- rand()的取值范围为[0,1)
-- 若要在i ≤ R ≤ j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j – i + 1))
-- 例如， 若要在7 到 12 的范围（包括7和12）内得到一个随机整数, 可使用以下语句：
-- SELECT FLOOR(7 + (RAND() * 6));
-- 随机数0<=返回值<1 RAND(),RAND(X) 有X的话返回相同值
SELECT RAND(), RAND(10), RAND(10), RAND(11);

-- 生成≥a且≤b的随机数:select floor(x+rand()*y);  x=a y=(b-a)+1 例如生成≥1且≤10的随机数
SELECT FLOOR(1 + RAND() * 10);

--最大最小值函数
SELECT  LEAST(1,2,3,4),GREATEST(1,2,3,4)


SELECT
ROUND(12.53),  --没有小数位返回整数13
ROUND(12.345,2), --12.35
ROUND(12.324,-1), --10
TRUNCATE(12.66,1),--12.6
TRUNCATE(12.66,-1)--10
FROM DUAL;

角度弧度函数

三角函数

指数对数

进制转换

日期时间函数

基础函数

函数名	描述	实例
ADDDATE(d,n)	计算起始日期 d 加上 n 天的日期	SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25
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
CURDATE()	返回当前日期	SELECT CURDATE(); -> 2018-09-19
CURRENT_DATE()	返回当前日期	SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME	返回当前时间	SELECT CURRENT_TIME(); -> 19:59:02
CURRENT_TIMESTAMP()	返回当前日期和时间	SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43
CURTIME()	返回当前时间	SELECT CURTIME(); -> 19:59:02
DATE()	从日期或日期时间表达式中提取日期值	SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
DATE_ADD(d，INTERVAL expr type)	计算起始日期 d 加上一个时间段后的日期，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 DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06-25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE); -> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); ->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH); ->2017-04-15
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
DATE_SUB(date,INTERVAL expr type)	函数从日期减去指定的时间间隔。	Orders 表中 OrderDate 字段减去 2 天： SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
DAY(d)	返回日期值 d 的日期部分	SELECT DAY("2017-06-15"); -> 15
DAYNAME(d)	返回日期 d 是星期几，如 Monday,Tuesday	SELECT DAYNAME('2011-11-11 11:11:11') ->Friday
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
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
FROM_DAYS(n)	计算从 0000 年 1 月 1 日开始 n 天后的日期	SELECT FROM_DAYS(1111) -> 0003-01-16
HOUR(t)	返回 t 中的小时值	SELECT HOUR('1:2:3') -> 1
LAST_DAY(d)	返回给给定日期的那一月份的最后一天	SELECT LAST_DAY("2017-06-20"); -> 2017-06-30
LOCALTIME()	返回当前日期和时间	SELECT LOCALTIME() -> 2018-09-19 20:57:43
LOCALTIMESTAMP()	返回当前日期和时间	SELECT LOCALTIMESTAMP() -> 2018-09-19 20:57:43
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
MICROSECOND(date)	返回日期参数所对应的微秒数	SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -> 23
MINUTE(t)	返回 t 中的分钟值	SELECT MINUTE('1:2:3') -> 2
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
NOW()	返回当前日期和时间	SELECT NOW() -> 2018-09-19 20:57:43
PERIOD_ADD(period, number)	为 年-月 组合日期添加一个时段	SELECT PERIOD_ADD(201703, 5); -> 201708
PERIOD_DIFF(period1, period2)	返回两个时段之间的月份差值	SELECT PERIOD_DIFF(201710, 201703); -> 7
QUARTER(d)	返回日期d是第几季节，返回 1 到 4	SELECT QUARTER('2011-11-11 11:11:11') -> 4
SECOND(t)	返回 t 中的秒钟值	SELECT SECOND('1:2:3') -> 3
SEC_TO_TIME(s)	将以秒为单位的时间 s 转换为时分秒的格式	SELECT SEC_TO_TIME(4320) -> 01:12:00
STR_TO_DATE(string, format_mask)	将字符串转变为日期	SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10
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 (秒)
SYSDATE()	返回当前日期和时间	SELECT SYSDATE() -> 2018-09-19 20:57:43
TIME(expression)	提取传入表达式的时间部分	SELECT TIME("19:30:10"); -> 19:30:10
TIME_FORMAT(t,f)	按表达式 f 的要求显示时间 t	SELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM
TIME_TO_SEC(t)	将时间 t 转换为秒	SELECT TIME_TO_SEC('1:12:00') -> 4320
TIMEDIFF(time1, time2)	计算时间差值	mysql> SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01 mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001' mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001', -> '2008-12-30 01:01:01.000002'); -> '46:58:57.999999'
TIMESTAMP(expression, interval)	单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和	mysql> SELECT TIMESTAMP("2017-07-23", "13:10:11"); -> 2017-07-23 13:10:11 mysql> SELECT TIMESTAMP('2003-12-31'); -> '2003-12-31 00:00:00' mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00'); -> '2004-01-01 00:00:00'
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)	计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差	mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少天 -> 89 mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少月 -> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // 计算两个时间相隔多少年 -> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // 计算两个时间相隔多少分钟 -> 128885
TO_DAYS(d)	计算日期 d 距离 0000 年 1 月 1 日的天数	SELECT TO_DAYS('0001-01-01 01:01:01') -> 366
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
YEAR(d)	返回年份	SELECT YEAR("2017-06-15"); -> 2017
YEARWEEK(date, mode)	返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推	SELECT YEARWEEK("2017-06-15"); -> 201724

函数名称	作 用
curdate 和 current_date	两个函数作用相同，返回当前系统的日期值
curtime 和 current_time	两个函数作用相同，返回当前系统的时间值
now 和 sysdate	两个函数作用相同，返回当前系统的日期和时间值
unix_timestamp	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
from_unixtime	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
month	获取指定日期中的月份
monthname	获取指定日期中的月份英文名称
dayname	获取指定曰期对应的星期几的英文名称
dayofweek	获取指定日期是一周中是第几天，返回值范围是1~7,1=周日
week	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
dayofyear	获取指定曰期是一年中的第几天，返回值范围是1~366
dayofmonth	获取指定日期是一个月中是第几天，返回值范围是1~31
year	获取年份，返回值范围是 1970〜2069
time_to_sec	将时间参数转换为秒数
sec_to_time	将秒数转换为时间，与TIME_TO_SEC 互为反函数
date_add 和 adddate	两个函数功能相同，都是向日期添加指定的时间间隔
date_sub 和 subdate	两个函数功能相同，都是向日期减去指定的时间间隔
addtime	时间加法运算，在原始时间上添加指定的时间
subtime	时间减法运算，在原始时间上减去指定的时间
datediff	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
date_format	格式化指定的日期，根据参数返回指定格式的值
weekday	获取指定日期在一周内的对应的工作日索引

函数	功能
CURDATE()	返回当前日期，只包含年月日
CURTIME()	返回当前时间，只包含时分秒
NOW()	返回当前的日期和时间
CURRENT_TIMESTAMP()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期 date 的 UNIX 时间戳
FROM_UNIXTIME	返回 UNIX 时间戳的日期值
WEEK(date)	返回日期 date 为一年中的第几周
YEAR(date)	返回日期 date 的年份
HOUR(time)	返回 time 的小时值
MINUTE(time)	返回 time 的分钟值
MONTHNAME(date)	返回 date 的月份名
DATE_FORMAT(date,fmt)	返回按字符串 fmt 格式化日期 date 值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(enddate,startdate)	返回enddate-startdate的天数
TIMESTAMPDIFF(datepart,startdate,enddate)	datepart可以为：天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND）

-- 日期和时间函数
-- 返回当前日期 2019-06-09 运行结果一样
SELECT CURRENT_DATE(),CURDATE()

-- 返回当前时间 15:57:31
SELECT CURRENT_TIME,CURTIME()

-- 返回当前日期加时间 2019-06-09 15:58:34
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE()
--unix时间戳
select UNIX_TIMESTAMP(now()),now();
--1612665467	2021-02-07 10:37:47

-- DATE_ADD(date,INTERVAL expr unit) 时间增加
-- 2018-11-01 00:00:00
SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND),
DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 MONTH)

-- DATE_SUB(date,INTERVAL expr unit) 2018-10-31 23:59:58 时间减少
SELECT DATE_SUB('2018-10-31 23:59:59',INTERVAL 1 SECOND)

-- 时间的增加减少
-- ADDTIME(time,expr2) 2018-10-31 11:03:25   2018-10-31 08:59:19
SELECT ADDTIME('2018-10-31 10:01:22','1:2:3'),SUBTIME('2018-10-31 10:01:22','1:2:3')

-- DAT1EDIFF(date1,date2) date1-date2的天数 31
SELECT DATEDIFF('2018-12-31 10:01:22','2018-11-30 10:01:22')

--24hour
SELECT TIMESTAMPDIFF(HOUR,'2020-09-10 23:05:58','2020-09-11 23:56:51');

--20210108
SELECT DATE_FORMAT('2021-01-08 11:28:00','%Y%m%d')

获取时间毫秒数

select current_timestamp(3) ,
substr(current_timestamp(3),1,locate('.',current_timestamp(3))+3)  

2021-12-13 10:51:04.223000000    2021-12-13 10:51:04.223

日期与时间戳

时间加减函数

SELECT ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,        #2021-10-21 23:32:13
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,   #2021-10-21 23:33:13
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5,        #可以是负数2020-12-13 10:54:22
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6   #需要单引号2023-01-13 10:54:22
FROM DUAL;

SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,          #2020-12-21
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,                  #2020-12-21
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3 #2021-01-20 01:01:01
FROM DUAL;

SELECT
now(),                              #2021-12-13 10:58:26
ADDTIME(NOW(),20),#2021-12-13 10:58:46
SUBTIME(NOW(),30),#2021-12-13 10:57:56
SUBTIME(NOW(),'1:1:3'),#2021-12-13 09:57:23
DATEDIFF(NOW(),'2021-10-01'),#73
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),#838:59:59
FROM_DAYS(366),#0001-01-01
TO_DAYS('0000-12-25'),#359
LAST_DAY(NOW()),#2021-12-31
MAKEDATE(YEAR(NOW()),12),#2021-01-12
MAKETIME(10,21,23),#10:21:23
PERIOD_ADD(20200101010101,10)#869817111
FROM DUAL;

举例：查询 7 天内的新增用户数有多少？SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

获取月份、星期、星期数、天数

SELECT NOW(),EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW()) 
FROM DUAL;

日期时间格式化

时间转字符串

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');#2021-12-13 11:06:06

字符串转时间

SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;

GET_FORMAT

SELECT GET_FORMAT(DATETIME, 'USA');#%Y-%m-%d %H.%i.%s

流程控制函数

函数	功能
IF(value,t f)	如果 value 是真，返回 t；否则返回 f
IFNULL(value1,value2)	如果 value1 不为空返回 value1，否则返回 value2
CASE WHEN [value1] THEN[result1]…ELSE[default]END	如果 value1 是真，返回 result1，否则返回 default
CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END	如果 expr 等于 value1，返回 result1，否则返回 default

其他常用函数

函数名	描述	实例
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
CAST(x AS type)	转换数据类型	字符串日期转换为日期： SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29
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
CONVERT(s USING cs)	函数将字符串 s 的字符集变成 cs	SELECT CHARSET('ABC') ->utf-8 SELECT CHARSET(CONVERT('ABC' USING gbk)) ->gbk
CURRENT_USER()	返回当前用户	SELECT CURRENT_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); ->
SESSION_USER()	返回当前用户	SELECT SESSION_USER(); -> guest@%
SYSTEM_USER()	返回当前用户	SELECT SYSTEM_USER(); -> guest@%
USER()	返回当前用户	SELECT USER(); -> guest@%
VERSION()	返回数据库的版本号	SELECT VERSION() -> 5.6.34

转换函数

-- CAST(value as type); CONVERT(value, type);
-- 可以转换的类型是有限制的。这个类型可以是以下值其中的一个：
--     二进制，同带binary前缀的效果 : BINARY    
--     字符型，可带参数 : CHAR()     
--     日期 : DATE     
--     时间: TIME     
--     日期时间型 : DATETIME     
--     浮点数 : DECIMAL      
--     整数 : SIGNED     
--     无符号整数 : UNSIGNED 

SELECT CONVERT('12345',SIGNED);

SELECT CAST('12345' AS SIGNED),CAST('12345' AS UNSIGNED);
 

 SELECT 
 FORMAT(123.123, 2), #123.12
 FORMAT(123.523, 0), #124
 FORMAT(123.123, -2);#123

加密解密函数

mysql信息函数

SELECT VERSION()	  #服务器版本信息
SELECT DATABASE();	#当前数据库名 (或者返回空)
SELECT USER()			#当前用户名

SHOW DATABASES;	#列出所有数据库
SHOW STATUS					#服务器状态
SHOW VARIABLES			#服务器配置变量
SHOW CREATE TABLE <table_name>; ##返回创建表结构语句-很有用处
SHOW TABLES; 		#列出默认数据库中的表
SHOW TABLES FROM <database_name> #列出指定数据库中的所有表
SHOW COLUMNS FROM <table_name> #显示表的列结构

聚合函数

AVG()SUM()MAX()MIN()

COUNT()

ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

注意点

1：不能在where使用having

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;

sql执行顺序

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7