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')
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;
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.练习
SELECT NOW()
FROM DUAL;
SELECT SYSDATE()
FROM DUAL;
SELECT employee_id, last_name, salary, salary * 1.2 AS new_salary
FROM employees;
SELECT last_name, LENGTH(last_name) length
FROM employees
ORDER BY last_name DESC;
SELECT CONCAT(employee_id, ' ', last_name, ' ', salary ) OUT_PUT
FROM employees;
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;
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;
SELECT last_name, hire_date
FROM employees
WHERE DATEDIFF(NOW(), hire_date) > 10000;
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;
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;