【MySQL】流程控制函数CASE

1.流程控制函数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')
# hello 

# 注意CASE和END成对使用
SELECT CASE 'a'
	WHEN 'a' THEN 
	  'aaaaa'
	WHEN 'b' THEN
		'bbbbb'
	ELSE
		'ccccc'
END;

SELECT CASE 
	WHEN 'a' > 'b' THEN 'a > b'
	ELSE 'a < b' END;

# 注意salary后面的逗号
SELECT employee_id, salary, CASE
	WHEN salary > 10000 THEN '1'
	WHEN salary > 5000 THEN '2'
	ELSE '3' 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;

2.练习

# 1.显示系统时间(注：日期+时间)
SELECT NOW()
FROM DUAL;

SELECT SYSDATE()
FROM DUAL;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id, last_name, salary, salary * 1.2 AS new_salary 
FROM employees;

# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name, LENGTH(last_name) length 
FROM employees
ORDER BY last_name DESC; --降序排序

# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id, ' ', last_name, ' ', salary ) OUT_PUT 
FROM employees;

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT last_name, DATEDIFF(SYSDATE(), hire_date) / 365 AS worked_years, DATEDIFF(SYSDATE(), hire_date) AS worked_days
FROM employees
ORDER BY worked_years DESC;

# 6.查询员工姓名，hire_date, department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空
SELECT last_name, hire_date, department_id
FROM employees
WHERE DATE_FORMAT(hire_date, '%Y') >= 1997
AND department_id IN (80,90,110)
AND commission_pct IS NOT NULL;

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name, hire_date
FROM employees
WHERE DATEDIFF(NOW(), hire_date) > 10000;

# 8.做一个查询，产生下面的结果 <last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name, ' earns ', salary, ' monthly but wants ' , salary * 3) AS 'Dream Salary'
FROM employees;  -- 结果有两位小数

SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary,0), ' monthly but wants ' , TRUNCATE(salary * 3, 0)) AS 'Dream Salary'
FROM employees;  -- 小数个数为0

# 9.使用case-when，按照下面的条件：
-- job grade
-- AD_PRES A
-- ST_MAN B
-- IT_PROG C
-- SA_REP D
-- ST_CLERK E
SELECT e.last_name, e.job_id,
CASE job_id
	WHEN 'AD_PRES' THEN 'A'
	WHEN 'ST_MAN' THEN 'B' 
	WHEN 'IT_PROG' THEN 'C' 
	WHEN 'SA_REP' THEN 'D' 
	WHEN 'ST_CLERKemployees.job_id' THEN 'E'
	ELSE 'F'
END 'grade'
FROM employees e;
# 注意此处jobid的when 和 then都要加''，因为是varchar
# 注意END后接'grade'为新列列名