MYSQL-流程控制函数

IF(expr1,expr2,expr3)

• expr1 为0或者null或者false，则返回 expr3 ; 否则，返回expr2

SELECT
	last_name,
	salary,

IF (
	salary > 10000,
	'高工资',
	'低工资'
) AS 工资水平
FROM
	employees;
/*
King	24000	高工资
Kochhar	17000	高工资
De Haan	17000	高工资
Hunold	9000	低工资
Ernst	6000	低工资
*/

SELECT IF(0,1,2);# 2
SELECT IF(NULL,'refine','lily'); # lily
SELECT IF(FALSE,'refine','lily'); # lily
SELECT IF(2>1,'refine','lily');# refine

IFNULL(expr1,expr2)

• 如果expr1 不是NULL，则返回第一个expr1 ，否则返回expr2；

# 如果commission_pct为NULL，则将commission_pct赋为0
SELECT last_name,commission_pct,IFNULL(commission_pct,0) FROM employees;
/*
King	NULL	0	
Kochhar	NULL	0	
De Haan	NULL	0
Hunold	NULL	0
...
*/

CASE WHEN … THEN …WHEN … THEN … ELSE … END

SELECT
	last_name,
	salary,
	CASE
WHEN salary >= 20000 THEN
	'精英'
WHEN salary >= 10000 THEN
	'白领'
WHEN salary >= 6000 THEN
	'工薪'
ELSE
	'贫民'
END
FROM
	employees;
/*
King	24000	精英
Kochhar	17000	白领
De Haan	17000	白领
*/

CASE … WHEN … THEN … WHEN … THEN … ELSE … END

SELECT
	employee_id,
	last_name,
	job_id,
	salary,
	CASE job_id
WHEN 'IT_PROG' THEN
	salary + 5000
WHEN 'PU_CLERK' THEN
	salary + 3000
WHEN 'ST_MAN' THEN
	salary + 2000
ELSE
	salary
END "details"
FROM
	employees;
/*
100	King	AD_PRES	24000	24000.00
101	Kochhar	AD_VP	17000	17000.00
102	De Haan	AD_VP	17000	17000.00
103	Hunold	IT_PROG	9000	14000.00
104	Ernst	IT_PROG	6000	11000.00
105	Austin	IT_PROG	4800	9800.00
...
*/