4.5 时间和秒钟转换的函数
| 函数 | 用法 |
| TIME_TO_SEC(time) | 将 time 转化为秒并返回结果值。转化的公式为： 小时3600+分钟60+秒 |
| SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 |
| TIME_TO_SEC(NOW()) | +--------------------+ | 78774 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT SEC_TO_TIME(78774); +--------------------+ | SEC_TO_TIME(78774) | +--------------------+ | 21:52:54 | +--------------------+ 1 row in set (0.12 sec)
4.6 计算日期和时间的函数
第1组：
| 函数 | 用法 |
| DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
| DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 |
上述函数中type的取值：
举例:
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3, DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4, DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数 DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号 FROM DUAL; SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1, SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2, DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3 FROM DUAL;
第2组：
| 函数 | 用法 |
| ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数 |
| SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时，代表的是 秒 ，可以为负数 |
| DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
| TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
| FROM_DAYS(N) | 返回从0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 |
| MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
| PERIOD_ADD(time,n) | 返回time加上n后的时间 |
举例：
SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10- 01'), TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'), LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101, 10) FROM DUAL;
mysql> SELECT ADDTIME(NOW(), 50); +---------------------+ | ADDTIME(NOW(), 50) | +---------------------+ | 2019-12-15 22:17:47 | +---------------------+ 1 row in set (0.00 sec) +-------------------------+ | 2019-12-15 23:18:46 | +-------------------------+ 1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '1:1:1'); +-------------------------+ | SUBTIME(NOW(), '1:1:1') | +-------------------------+ | 2019-12-15 21:23:50 | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBTIME(NOW(), '-1:-1:-1'); +----------------------------+ | SUBTIME(NOW(), '-1:-1:-1') | +----------------------------+ | 2019-12-15 22:25:11 | +----------------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> SELECT FROM_DAYS(366); +----------------+ | FROM_DAYS(366) | +----------------+ | 0001-01-01 | +----------------+ 1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,1); +------------------+ | MAKEDATE(2020,1) | +------------------+ | 2020-01-01 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT MAKEDATE(2020,32); +-------------------+ | MAKEDATE(2020,32) | +-------------------+ | 2020-02-01 | +-------------------+ 1 row in set (0.00 sec)
mysql> SELECT MAKETIME(1,1,1); +-----------------+ | MAKETIME(1,1,1) | +-----------------+ | 01:01:01 | +-----------------+ 1 row in set (0.00 sec) | PERIOD_ADD(20200101010101,1) | +------------------------------+ | 20200101010102 | +------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT TO_DAYS(NOW()); +----------------+ | TO_DAYS(NOW()) | +----------------+ | 737773 | +----------------+ 1 row in set (0.00 sec)
举例：查询 7 天内的新增用户数有多少？
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
4.7 日期的格式化与解析
| 函数 | 用法 |
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
| GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
| STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
上述 非GET_FORMAT 函数中fmt参数常用的格式符：
| 式符 | 说明 | 式符 | 说明 |
| %Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
| %M | 月名表示月份（January,…） | %m | 两位数字表示月份（01,02,03。。。） |
| %b | 缩写的月名（Jan.，Feb.，…） | %c | 数字表示月份（1,2,3,…） |
| %D | 英文后缀表示月中的天数（1st,2nd,3rd,…） | %d | 两位数字表示月中的天数(01,02…) |
| %e | 数字形式表示月中的天数（1,2,3,4,5…） | ||
| %H | 两位数字表示小数，24小时制（01,02…） | %h和%I | 两位数字表示小时，12小时制（01,02…） |
| %k | 数字形式的小时，24小时制(1,2,3) | %l | 数字形式表示小时，12小时制（1,2,3,4…） |
| %i | 两位数字表示分钟（00,01,02） | %S和%s | 两位数字表示秒(00,01,02…) |
| %W | 一周中的星期名称（Sunday…） | %a | 一周中的星期缩写（Sun.，Mon.,Tues.，…） |
| %w | 以数字表示周中的天数(0=Sunday,1=Monday…) | ||
| %j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天 |
| %u | 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天 | ||
| %T | 24小时制 | %r | 12小时制 |
| %p | AM或PM | %% | 表示% |
GET_FORMAT函数中date_type和format_type参数取值如下：
举例：
mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s'); +--------------------------------+ | DATE_FORMAT(NOW(), '%H:%i:%s') | +--------------------------------+ | 22:57:34 | +--------------------------------+ 1 row in set (0.00 sec)
SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y') FROM DUAL; SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s') FROM DUAL; SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s') FROM DUAL;
SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y') FROM DUAL; SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s') FROM DUAL; SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s') FROM DUAL;
mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d'); +-----------------------------------------------+ | STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') | +-----------------------------------------------+ | 2020-01-01 | +-----------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。
MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
| 函数 | 用法 |
| IF(value,value1,value2) | 如果value的值为TRUE，返回value1，否则返回value2 |
| IFNULL(value1, value2) | 如果value1不为NULL，返回value1，否则返回value2 |
| CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE resultn] END | 相当于Java的if…else if…else… |
| CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN值1 … [ELSE 值n] END | 相当于Java的switch…case… |
SELECT IF(1 > 0,'正确','错误') ->正确
SELECT IFNULL(null,'Hello Word') ->Hello Word
SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END ->1 > 0
SELECT CASE 1 WHEN 1 THEN '我是1' WHEN 2 THEN '我是2' ELSE '你是谁'
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' WHEN salary>=10000 THEN '潜力股' WHEN salary>=8000 THEN '屌丝' ELSE '草根' END "描述" FROM employees;
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款' WHEN 2 THEN '已付款' WHEN 3 THEN '已发货' WHEN 4 THEN '确认收货' ELSE '无效订单' END FROM t_order;
mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END; +---------------------------------------------------------------------+ | CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END | +---------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END; +--------------------------------------------------------------------+ | CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END | +--------------------------------------------------------------------+ | unknown | +--------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END; +------------------------------------------------+ | CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END | +------------------------------------------------+ | 1 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END; +-------------------------------------------------+ | CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END | +-------------------------------------------------+ | -1 | +-------------------------------------------------+ 1 row in set (0.00 sec) SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) FROM employees; SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;
6. 加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。
| 函数 | 用法 |
| PASSWORD(str) | 返回字符串str的加密版本，41位长的字符串。加密结果 不可逆 ，常用于用户的密码加密 |
| MD5(str) | 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL |
| SHA(str) | 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。 SHA加密算法比MD5更加安全 。 |
| ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value |
| DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value |
可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。
举例：
mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL); +-------------------------------------------+----------------+ | PASSWORD('mysql') | PASSWORD(NULL) | +-------------------------------------------+----------------+ | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | | +-------------------------------------------+----------------+ 1 row in set, 1 warning (0.00 sec) SELECT md5('123') ->202cb962ac59075b964b07152d234b70 SELECT SHA('Tom123') ->c7c506980abc31cc390a2438c90861d0f1216d50 mysql> SELECT ENCODE('mysql', 'mysql'); +--------------------------+ | ENCODE('mysql', 'mysql') | +--------------------------+ | íg ¼ ìÉ | +--------------------------+ 1 row in set, 1 warning (0.01 sec) mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql'); +-----------------------------------------+ | DECODE(ENCODE('mysql','mysql'),'mysql') | +-----------------------------------------+ | mysql | +-----------------------------------------+ 1 row in set, 2 warnings (0.00 sec)
7. MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
| VERSION() | 返回当前MySQL的版本号 |
| CONNECTION_ID() | 返回当前MySQL服务器的连接数 |
| DATABASE()，SCHEMA() | 返回MySQL命令行当前所在的数据库 |
| USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER() | 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名” |
| CHARSET(value) | 返回字符串value自变量的字符集 |
| COLLATION(value) | 返回字符串value的比较规则 |
举例：
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | test | +------------+ 1 row in set (0.00 sec) mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | test | +------------+ 1 row in set (0.00 sec) mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER(); +----------------+----------------+----------------+----------------+ | USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER() | +----------------+----------------+----------------+----------------+ | root@localhost | root@localhost | root@localhost | root@localhost | +----------------+----------------+----------------+----------------+ mysql> SELECT CHARSET('ABC'); +----------------+ | CHARSET('ABC') | +----------------+ | utf8mb4 | +----------------+ 1 row in set (0.00 sec) mysql> SELECT COLLATION('ABC'); +--------------------+ | COLLATION('ABC') | +--------------------+ | utf8mb4_general_ci | +--------------------+ 1 row in set (0.00 sec)
MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视
的。
| 函数 | 用法 |
| FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位 |
| CONV(value,from,to) | 将value的值进行不同进制之间的转换 |
| INET_ATON(ipvalue) | 将以点分隔的IP地址转化为一个数字 |
| INET_NTOA(value) | 将数字形式的IP地址转化为以点分隔的IP地址 |
| BENCHMARK(n,expr) | 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间 |
| CONVERT(value USINGchar_code) | 将value所使用的字符编码修改为char_code |
举例：
# 如果n的值小于或者等于0，则只保留整数部分 mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2); +--------------------+--------------------+---------------------+ | FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) | +--------------------+--------------------+---------------------+ | 123.12 | 124 | 123 | +--------------------+--------------------+---------------------+ 1 row in set (0.00 sec) mysql> SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2); +-----------------+------------------+-------------------+ | CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) | +-----------------+------------------+-------------------+ | 10000 | 22B8 | NULL | +-----------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT INET_ATON('192.168.1.100'); +----------------------------+ | INET_ATON('192.168.1.100') | +----------------------------+ | 3232235876 | +----------------------------+ 1 row in set (0.00 sec) # 以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
| INET_NTOA(3232235876) | +-----------------------+ | 192.168.1.100 | +-----------------------+ 1 row in set (0.00 sec) mysql> SELECT BENCHMARK(1, MD5('mysql')); +----------------------------+ | BENCHMARK(1, MD5('mysql')) | +----------------------------+ | 0 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT BENCHMARK(1000000, MD5('mysql')); +----------------------------------+ | BENCHMARK(1000000, MD5('mysql')) | +----------------------------------+ | 0 | +----------------------------------+ 1 row in set (0.20 sec) mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8')); +------------------+----------------------------------------+ | CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) | +------------------+----------------------------------------+ | utf8mb4 | utf8 | +------------------+----------------------------------------+ 1 row in set, 1 warning (0.00 sec)
未完待续；
