mysql数据库中的一些知识点
原创 朱歌亮 2020-08-11 14:50:48
与Java中的switch...case类比
SELECT salary,department_id, CASE department_id WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END "now"
FROM employees ORDER BY department_id
与Java中if...else...if...else..类比
SELECT salary ,
CASE
WHEN salary>20000 THEN ‘A‘
WHEN salary>15000 THEN ‘B‘
WHEN salary>10000 THEN ‘C‘
ELSE ‘D‘
END "de"
FROM employees
mysql中处理时间的函数
SELECT * FROM employees WHERE STR_TO_DATE(‘1992-4-3‘,‘%Y-%c-%d‘)=hiredate;
SELECT * FROM employees WHERE DATE_FORMAT(hiredate,‘%Y-%m-%d‘) =‘1992-04-03‘;
#求两个日期之间的相差天数
DATEDIFF参数为两个日期型
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;