#日期、时间函数
SELECT
CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;

#代码测试
#日期与时间戳的转换
SELECT UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP(CURDATE()),UNIX_TIMESTAMP(CURTIME()),FROM_UNIXTIME(1677118000)
FROM DUAL;

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

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

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL1 SECOND) AS col2,
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
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;

SELECT DATE_FORMAT(NOW(), '%H:%i:%s'),STR_TO_DATE('09/01/2009','%m/%d/%Y'),STR_TO_DATE('20140422154706','%Y%m%d%H%i%s'),
STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s'),GET_FORMAT(DATE, 'USA'),DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') FROM DUAL;

/*
练习1：查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门打印其工资的 1.3 倍数。
其他部门，打印其工资的1.4倍数
*/
#实现方式一：
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary *1.1
     WHEN 20 THEN salary *1.2
     WHEN 30 THEN salary *1.3
     ELSE salary * 1.4 END "detalis"
FROM employees;
#实现方式二：
SELECT employee_id,last_name,department_id,salary,CASE WHEN department_id = 10 THEN salary * 1.1
       WHEN department_id = 20 THEN salary * 1.2
       WHEN department_id = 30 THEN salary * 1.3
       ELSE salary * 1.4 END "detalis"
FROM employees;

SELECT MD5('mysql'),SHA('mysql'),MD5(MD5('mysql'))
FROM DUAL;

# ENCODE() \ DECODE() PASSWORD()在mysql1  8.0 中弃用
SELECT ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql')
FROM DUAL;

SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER(),CHARSET('ABC'),COLLATION('ABC')
FROM DUAL;

SELECT INET_ATON('192.168.1.100'),CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'))
FROM DUAL;