[TOC]
日期函数
MySQL对时间的格式无具体要求，只要格式是 YYYY-MM-DD HH:MM:SS 其中:可用其它特殊字符替换。
- ADDDATE(d,INTERVAL n type) : 在起始日期d加上n的日期（n 默认单位天）
SELECT ADDDATE("2019-04-10 11:11:11",10); -- 2019-04-20 11:11:11
SELECT ADDDATE("2019-04-10 11:11:11",INTERVAL 5 HOUR); -- 2019-04-10 16:11:11
- ADDTIME(t,n)：在时间t加上秒数s（s可正可负，但在区间[60,99]和[-60,-99]结果会为null？）
SELECT ADDTIME("2019-04-10 11:11:11",-59); -- 2019-04-10 11:10:12
- DATE_ADD(d,INTERVAL n type)：在起始时间d加上n的日期（n无默认单位）
SELECT DATE_ADD('2019-04-15 11:11:11',INTERVAL 3 HOUR) -- 2019-04-15 14:11:11
- SUBDATE(d,n):在起始日期d减去n天的日期
SELECT SUBDATE("2019-04-15 11:11:11" , 5) -- 2019-04-10 11:11:11
- SUBTIME(d,n)：在起始日期d减去n秒的时间
SELECT SUBTIME("2019-04-15 11:11:11" , 5) -- 2019-04-15 11:11:06
- DATE_SUB(d,INTERVAL n type)：在起始时间d减去n的日期（n无默认单位）
SELECT DATE_SUB('2019-04-15 11:11:11',INTERVAL 3 HOUR) --2019-04-15 08:11:11
- CURRENT_TIME/CURTIME()： 返回当前时间
SELECT CURRENT_TIME(); -- 10:15:49
SELECT CURTIME(); -- 10:15:49
- UTC_DATE()/UTC_TIME()/UTC_TIMESTAMP()： 国际标准日期/时间
SELECT UTC_DATE(); -- 2019-04-17 国际标准日期
SELECT UTC_TIME() ; -- 03:37:19 国际标准时间
SELECT UTC_TIMESTAMP(); -- 2019-04-17 03:37:44 国际标准时间戳
- SYSDATE():返回系统时间
SELECT SYSDATE() -- 2019-04-15 09:01:20
- CURDATE()/CURRENT_DATE()：返回当前日期
SELECT CURDATE(); -- 2019-04-13
SELECT CURRENT_DATE(); -- 2019-04-13
- CURRENT_TIMESTAMP()/SELECT NOW(): 返回当前时间戳
SELECT CURRENT_TIMESTAMP(); -- 2019-04-13 10:24:09
SELECT NOW(); -- 2019-04-13 10:24:09
- DATE(d)：提取d里面的日期格式为 YYYY-MM-DD;
SELECT DATE("2019-11-13 11-11-11"); -- 2019-11-13
- DATEDIFF(d1,d2)：计算 d1到d2间隔的天数，d1-d2
SELECT DATEDIFF("2019-11-23","2019-11-13") ; -- 10
- DATE_FORMAT(d,format)：根据格式format显示日期d ，（format格式详见@2）
SELECT DATE_FORMAT("2019-04-11 11:11:11",'%Y-%m-%d %r'); -- 2019-04-11 11:11:11 AM
- YEAR(d)：返回日期值d的年份部分
SELECT YEAR("2019-04-13 11:11:11") -- 2019
- MONTH(d)：返回日期值d的月份部分
SELECT DAY("2019-04-13 11:11:11") -- 4
- DAY(d)：返回日期值d的日期部分
SELECT DAY("2019-04-13 11:11:11") -- 13
- HOUR(d):返回日期值d的小时部分
SELECT DAY("2019-04-13 08:09:11") -- 08
- MINUTE(d)：返回日期值d的分钟部分
SELECT MINUTE("2019-04-13 08:09:11") -- 09
- SECOND(d)：返回日期值d的秒数部分
SELECT SECOND("2019-04-13 08:09:11") -- 11
- MICROSECOND(d)：返回日期d是的毫秒数
SELECT MICROSECOND("2019-04-15 11:11:11.1234") -- 123400
- WEEK(d)：返回日期值d的处于本年第几周
SELECT WEEK("2019-04-13 08:09:11"); -- 14
- DAYNAME(d)：返回日期值d处于星期几(如 Monday,Tuesday)
SELECT DAYNAME("2019-04-13 11:21:11") -- Saturday
- DAYOFWEEK(d):日期 d 今天是星期几，1 星期日，2 星期一，以此类推
SELECT DAYOFWEEK("2019-04-13 11:21:11"); -- 6
- DAYOFMONTH(d):计算日期 d 是本月的第几天
SELECT DAYOFMONTH("2019-04-13 11:21:11"); -- 13
- DAYOFYEAR(d)：计算日期 d 是本年的第几天
SELECT DAYOFYEAR("2019-04-13 11:21:11"); -- 103
- QUARTER(d)：计算日期d是本年的第几个季度
SELECT QUARTER("2019-04-15 11:11:11") -- 2
- LAST_DAY(d):返回给定日期的那一分月的最后一天
SELECT LAST_DAY("2019-04-15 11:11:11") -- 2019-04-30
- EXTRACT(type FROM d):从日期 d 中获取指定的值，type 指定返回的值。(type类型详见@1)
SELECT EXTRACT(**MINUTE** FROM "2019-04-13 08:21:11"); -- 21
- STR_TO_DATE(string,format_mask):将字符串转换为日期
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -- 2019-04-15
- TO_DAYS(d):计算日期d距离0000年1月1日的天数
SELECT TO_DAYS("0001-04-15") -- 105
@ 1 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
@2 format格式
| %a | 缩写星期名 |
|---|---|
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值（00-31） |
| %e | 月的天，数值（0-31） |
| %f | 微秒 |
| %H | 小时（00-23） |
| %h | 小时（01-12） |
| %I | 小时（01-12） |
| %i | 分钟，数值（00-59） |
| %j | 年的天（001-366） |
| %k | 小时（0-23） |
| %l | 小时（1-12） |
| %M | 月名 |
| %m | 月，数值（00-12） |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒（00-59） |
| %s | 秒（00-59） |
| %T | 时间, 24-小时（hh:mm:ss） |
| %U | 周（00-53）星期日是一周的第一天 |
| %u | 周（00-53）星期一是一周的第一天 |
| %V | 周（01-53）星期日是一周的第一天，与 %X 使用 |
| %v | 周（01-53）星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天（0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
字符串函数
ps：MySQL下标从1开始计算
- 返回字符c的ASCII编码 ASCII(c)
SELECT ASCII('A'); -- 65
-
返回数字i的所对应的ASCII编码字符 CHAR(i)
- SELECT CHAR(65); -- A
- 返回字符串长度：CHAR_LENGTH(s)/CHARACTER_LENGTH(s)
SELECT CHAR_LENGTH("HelloWorld"); -- 10
SELECT CHARACTER_LENGTH("HelloWorld"); -- 10
- 合并字符串s1,s2...sn为一个字符串：CONCAT(s1,s2...sn)
SELECT CONCAT("Hello","World","!") ; -- helloworld!
- 合并字符串并添加分隔符x：CONCAT_WS(x,s1,s2...sn)
SELECT CONCAT_WS("-","Hello","World","!") ; -- hello-world-!
- 返回字符串s在s1,s2...sn的位置(没找到返回0)：FIELD(s,s1,s2...sn)
SELECT FIELD("c","b","c","d") ; -- 2
- 返回在字符串s2中与s1匹配的字符串的位置(没找到返回0)：FIND_IN_SET(s1,s2)
SELECT FIND_IN_SET("ac", "a,b,ac,d,e"); -- 3
- 返回字符串s1在字符s开始的位置 POSITION(s1 IN s)
SELECT POSITION("o" in "helloworld"); -- 5
- 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入:FORMAT(x,n)
SELECT FORMAT(20190415.35581, 2); -- 20,190,415.36
- 将字符串s2替换字符s中的字符串s1：REPLACE(s,s1,s2)
SELECT REPLACE("helloworld","ell","MySQL"); -- hMySQLoworld
- 字符串s2替换s1在x处的位置，替换长度为len:INSERT(s1,x,len,s2)
SELECT INSERT("helloworld",6,4,"MySQL") ; -- helloMySQLd
- 在字符串s1处填充字符串s2，使其长度为len: LPAD(s1,len,s2)
SELECT LPAD("helloworld",16,"MySQL"); -- MySQLMhelloworld
- 在字符串s1结尾处填充字符串s2，使其长度为lenRPAD(s1,len,s2)
SELECT RPAD("helloworld",16,"MySQL"); -- helloworldMySQLM
- 从字符串s获取s1的位置：LOCATE(s1,s)
SELECT LOCATE("ell","helloworld"); -- 2
- 将s中的字母全部转换为小写：LOWER(s)/LCASE(s)
SELECT LOWER("HELLOWORLD"); -- helloworld
SELECT LCASE("HELLOWORLD"); -- helloworld
- 将s中的字母全部转换为大写：UPPER(s)/UCASE(s)
SELECT UPPER("helloworld"); -- HELLOWORLD
SELECT UCASE("helloworld"); -- HELLOWORLD
- 去掉字符串s开始处的空格：LTRIM(s)
SELECT LTRIM(" hello") ; -- hellow
- 去掉字符串s结尾处空格： RTRIM(s)
SELECT RTRIM("hello "); -- hello
- 去掉字符串s开始和结尾处的空格： TRIM(s)
SELECT RTRIM(" hello "); -- hello
- 返回n个空格： SPACE(n)
SELECT SPACE(5); --
- 从左开始截取字符串： left（str, length）
SELECT LEFT("helloworld",7); --hellowo
- 从右开始截取字符串： right（str, length）
SELECT RIGHT("helloworld",7); --loworld
- 从字符串 s 的 start 位置截取长度为 length 的子字符串: MID(s,start ,len)/SUBSTRING(s,start ,len)
SELECT MID("helloworld", 2, 3) ; -- ell
SELECT SUBSTRING("helloworld", 3, 5); -- llowo
- 将字符串s重复n次：REPEAT(s,n)
SELECT REPEAT("hello",3); -- hellohellohello
- 反转字符串s:REVERSE(s)
SELECT REVERSE("hello"); -- olleh
- 返回字符串s后n个字符：RIGHT("helloworld",6)
SELECT RIGHT("helloworld",6); -- oworld
- 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1：STRCMP(s1,s2)
SELECT STRCMP("runoob", "runooa"); -- 1
SELECT STRCMP("runoob", "runoob"); -- 0
SELECT STRCMP("runoob", "runooc"); -- -1
数学函数
- 取绝对值：ABS(x)
SELECT ABS(-1); -- 1
- 返回圆周率：PI()
SELECT PI(); --3.141593
-
返回0到1的伪随机数： RAND([seed])
seed：种子值，相同的种子值会有相同的输出
SELECT RAND(); --0.8503592115364589
SELECT RAND(); --0.26865792299495816
SELECT RAND(100)； --0.17353134804734155 试几次都一样
- 返回大于或等于x的最小整数： CEIL(x)/CEILING(x)
SELECT CEIL(1.5); -- 2
SELECT CEIL(-1.5); -- -1
-
返回小于或等于 x 的最大整数: FLOOR(x)
- SELECT FLOOR(1.5); -- 1
- SELECT FLOOR(1.5) -- -2
返回离x最近的整数： ROUND(x)
SELECT ROUND(1.1235); -- 1
SELECT ROUND(-1.5235); -- -2
-
返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）:TRUNCATE(x,y)
y:是n的舍入精度，如果y为整数，则n舍入到y指定的小数位数，如果y为负数，则n小数点左边舍入到y的指定位数
SELECT TRUNCATE(1.1235,3); -- 1.123
SELECT TRUNCATE(123.1235,-1); -- 120
SELECT TRUNCATE(-1.5235,3); -- -1.523
-
n 除 m: n DIV m
- SELECT 5 DIV 2 ; -- 2
返回n除以m以后的余数： MOD(n,m)
SELECT MOD(5,2); -- 1
-
返回x的y次方 POW(x,y)/POWER(x,y)
- SELECT POW(2,3); -- 8
返回x的平方根：SQRT(x)
SELECT SQRT(0.25); -- 0.5
- 返回x的符号，x是负数返回-1，正数返回1,0返回0 SIGN(x)
SELECT SIGN(0.25); -- 1
SELECT SIGN(0); -- 0
SELECT SIGN(-0.25); -- -1
聚合函数
- 返回一个表达式的平均值，expression 是一个字段: AVG(expression)
SELECT AVG(FSL) FROM table-- 取table表FSL字段的平均值
- 返回查询的总记录数，expression 是一个字段或号：* COUNT(expression)
SELECT COUNT(*) form table -- 取table表的总记录数
- 返回字段expression的总和：SUM(expression)
SELECT SUM(FSL) -- 返回table表字段FSL的总和
- 返回字段expression的最大值： MAX(expression)
SELECT MAX(FSL) FROM table; --返回table表FSL字段的最大值
- 返回字段expression的最小值： MIN(expression)
SELECT MIN(FSL) FROM table; --返回table表FSL字段的最小值
- HAVING子句仅用于带有GROUP BY子句的查询语句中，WHERE子句用于每一行（在变成一个组的某一部分之前），而HAVING子句用于分组的聚合值。 HAVING
-
分组合并字符串：GROUP_CONCAT()
根据名字分组，求出该名字的所有id值
mysql>SELECT SNAME,GROUP_CONCAT(SNO) as ids from student GROUP BY SNAME
| SNAME | ids |
| ----- | ----------- |
| 匡明 | 105 |
| 曾华 | 108 |
| 李军 | 101,110,111 |
| 王丽 | 107,112 |
| 王芳 | 109 |
| 陆君 | 103 |根据名字分组，求出该名字的所有id值，并根据id的值从大到小排序，使用分隔符-隔开
mysql>SELECT SNAME,GROUP_CONCAT(SNO ORDER BY SNO DESC SEPARATOR '-') as ids from student GROUP BY SNAME
| SNAME | ids |
|---|---|
| 匡明 | 105 |
| 曾华 | 108 |
| 李军 | 111-110-101 |
| 王丽 | 112-107 |
| 王芳 | 109 |
| 陆君 | 103 |
数据类型转换函数
把x转换成type类型：CAST(x AS type)/CONVERT(x,type)
- 字符串转数字
SELECT CONVERT("123",SIGNED)； -- 123
SELECT CAST("123",SIGNED)； -- 123
SELECT CONVERT("1a123",SIGNED); -- 返回1；从左往右匹配遇到非数字返回，如果开头没数字返回0
SELECT CAST("sa123" as SIGNED) ; -- 返回 0
SELECT '123'+0; -- 返回数字类型 123
- 数字转字符串
SELECT CONCAT(123) ； --返回字符串 "123"
SELECT CAST(123 AS CHAR); -- 返回"123"
- 字符串转日期
SELECT CONVERT("20190415",DATE) -- 2019-04-15
SELECT CAST("20190415",DATE); -- 2019-04-15
type类型：
二进制,同带binary前缀的效果 : BINARY
字符型,可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
排序函数
自定义排序， field(value,str1,str2,str3,str4)
mysql> select * from student order by field(Sname,'李军','王丽','曾华');
| SNO | SNAME | SSEX | SBIRTHDAY | CLASS |
|---|---|---|---|---|
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 110 | 李军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 111 | 李军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 112 | 王丽 | 女 | 1976-02-20 00:00:00 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
没有排序的字段放在最前面，排序字段根据field里的顺序来进行排序；
mysql> SELECT * from student ORDER BY FIELD(SNAME,'李军','王丽','曾华') DESC;
| SNO | SNAME | SSEX | SBIRTHDAY | CLASS |
|---|---|---|---|---|
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 112 | 王丽 | 女 | 1976-02-20 00:00:00 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 110 | 李军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 111 | 李军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
当加上DESC后，先filed()参数从后往前，再是未经field()排序的字段。
系统函数
- 查看版本信息：VERSION()
SELECT VERSION(); -- 5.5.28
- 查看服务器的连接数:CONNECTION_ID()
SELECT CONNECTION_ID(); -- 27
- 查看当前用户的名字： CURRENT_USER()/SYSTEM_USER()/USER()/SESSION_USER()
SELECT CURRENT_USER(); --root@localhost
- 查看当前数据库的名字 DATABASE()/SCHEMA()
SELECT DATABASE(); --ycz_test
其它函数
- 从N1开始,比较N和N1的值，如果N>Ni返回i的下标(从N1开始从0计算)，如果N最大返回0，如果N为null返回-1:INTERVAL(N,N1,N2,N3,N4...Ni)
SELECT INTERVAL(1,2,3,4); -- 0
SELECT INTERVAL(5,2,3,4,5,6,7,8); -- 4
SELECT INTERVAL(null,2,3,4); -- -1
- 返回x的二进制编码：BIN(x)
SELECT BIN(14); -- 110
- 返回x的八进制编码：OCT(x)
SELECT OCT(14); -- 16
- 返回x的十六进制编码：HEX(x)
SELECT HEX(14); -- E
- 将x从f1进制数变成f2进制数：CONV(x,f1,f2)
SELECT CONV(14,10,2)；--1110
- 如果表达式expr成立，返回r1,否则返回r2: IF(expr,r1,r2)
SELECT IF(1>2,"正确","错误"); -- 错误
- 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 IFNULL(r1,r2)
SELECT IFNULL(null,"helloworld"); -- helloworld
SELECT IFNULL("hello","helloworld"); -- hello
- 比较两个字符串，如果相等返回null,否则返回r1: NULLIF(r1,r2)
SELECT NULLIF("hello","helloworld"); -- hello
SELECT NULLIF("helloworld","helloworld") ; --
-
IP地址与数字相互转换的函数
把数字转换为ip地址：INET_ATON(IP)
和
把ip地址转换为数字：INET_NTOA(n)
SELECT INET_ATON('192.168.3.126') ; -- 3232236414
SELECT INET_NTOA(3232236414); -- 192.168.3.126
- 返回列表的最大值： GREATEST(expr1, expr2, expr3, ...)
SELECT GREATEST(34, 12, 26, 8, 55, 47); -- 55
SELECT LEAST("baidu", "alibaba", "tencent"); -- alibaba
- 返回列表中的最小值: LEAST(expr1, expr2, expr3, ...)
SELECT GREATEST(34, 12, 26, 8, 55, 47); -- 8
SELECT LEAST("baidu", "alibaba", "tencent"); -- tencent
加密函数
- 返回MD5加密后的字符串s MD5(s)
SELECT MD5("hello"); --5d41402abc4b2a76b9719d911017c592
- 对字符串s进行加密，一般用来给用户密码做加密的。PASSWORD(s)
SELECT PASSWORD("hello"); --*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119
使用字符串pswd_str作为密匙来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。ENCODE(str,pswd_str)
-
使用密匙pswd_str对二进制函数crypt_str进行解密，和ENCODE()配对使用。DECODE(crypt_str,pswd_str)
- 使用ENCODE()加密，并使用DECODE()进行解密。加密字符串为hello,密匙为world
SELECT DECODE(ENCODE("hello","world"),"world"); -- hello
