1、if函数
if(表达式1,表达式2,表达式3) 如果表达式1成立则执行表达式2,否则执行表达式3。
mysql> select if(5>=5,'true','false');
+-------------------------+
| if(5>=5,'true','false') |
+-------------------------+
| true |
+-------------------------+
1 row in set (0.00 sec)
#实例
mysql> select last_name,commission_pct,if(commission_pct is null,'没奖金,呵呵','有奖金,嘻嘻') as beizhu
-> from employees
-> where commission_pct is null and salary>=17000 or commission_pct>=0.4;
+-----------+----------------+--------------+
| last_name | commission_pct | beizhu |
+-----------+----------------+--------------+
| K_ing | NULL | 没奖金,呵呵 |
| Kochhar | NULL | 没奖金,呵呵 |
| De Haan | NULL | 没奖金,呵呵 |
| Russell | 0.40 | 有奖金,嘻嘻 |
+-----------+----------------+--------------+
4 rows in set (0.00 sec)
2、case
case 要判断的字段或函数表达式
when 常量1 then 要执行的语句或值;(为值是不要分号)
…
else 默认情况下执行;
end
示例一
mysql> select salary as 原始工资,department_id,
-> case department_id
-> when 30 then salary*1.1
-> when 40 then salary*1.2
-> when 50 then salary*1.3
-> end as 新工资
-> from employees
-> where department_id in(30,40,50) and salary>7500;
+----------+---------------+----------+
| 原始工资 | department_id | 新工资 |
+----------+---------------+----------+
| 11000.00 | 30 | 12100.00 |
| 8000.00 | 50 | 10400.00 |
| 8200.00 | 50 | 10660.00 |
| 7900.00 | 50 | 10270.00 |
+----------+---------------+----------+
4 rows in set (0.00 sec)
示例二
mysql> SELECT last_name,salary,
-> CASE
-> WHEN salary>20000 THEN 'A'
-> WHEN salary>15000 THEN 'B'
-> WHEN salary>10000 THEN 'C'
-> END AS dj
-> FROM employees
-> WHERE salary>=17000;
+-----------+----------+------+
| last_name | salary | dj |
+-----------+----------+------+
| K_ing | 24000.00 | A |
| Kochhar | 17000.00 | B |
| De Haan | 17000.00 | B |
+-----------+----------+------+
3 rows in set (0.00 sec)