MYSQL 判断函数

--流程函数。if ，ifnull ，case 语句！

mysql> create table sal (id int,sal decimal (9,2));

Query OK, 0 rows affected (0.06 sec)
mysql> insert into sal values (1,1000),(2,2000),(3,3000),(4,4000),(5,null);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
--if(EXP,T,F) EXP 为真则返回T,EXP为假，则返回F
mysql> select * from sal;
+------+---------+
| id   | sal     |
+------+---------+
|    1 | 1000.00 |
|    2 | 2000.00 |
|    3 | 3000.00 |
|    4 | 4000.00 |
|    5 |    NULL |
+------+---------+
5 rows in set (0.00 sec)

mysql> select id, if(sal > 3000,'high','low') from sal;
+------+-----------------------------+
| id   | if(sal > 3000,'high','low') |
+------+-----------------------------+
|    1 | low                         |
|    2 | low                         |
|    3 | low                         |
|    4 | high                        |
|    5 | low                         |--这里null >3000 为假！
+------+-----------------------------+
5 rows in set (0.01 sec)
--IFNULL(VAL,N)如果val 为null 则返回N
mysql> select id, ifnull(sal,0) from sal;
+------+---------------+
| id   | ifnull(sal,0) |
+------+---------------+
|    1 |       1000.00 |
|    2 |       2000.00 |
|    3 |       3000.00 |
|    4 |       4000.00 |
|    5 |          0.00 |
+------+---------------+
5 rows in set (0.01 sec)
--case 语句和oracle的一样了！
mysql> select case when sal<=3000 then 'low' else 'high' end from sal;
+------------------------------------------------+
| case when sal<=3000 then 'low' else 'high' end |
+------------------------------------------------+
| low                                            |
| low                                            |
| low                                            |
| high                                           |
| high                                           |
+------------------------------------------------+
5 rows in set (0.00 sec)
mysql> select id, case sal when 2000 then 'low' when 3000 then 'mid' else 'high' end from sal;
+------+--------------------------------------------------------------------+
| id   | case sal when 2000 then 'low' when 3000 then 'mid' else 'high' end |
+------+--------------------------------------------------------------------+
|    1 | high                                                               |
|    2 | low                                                                |
|    3 | mid                                                                |
|    4 | high                                                               |
|    5 | high                                                               |
+------+--------------------------------------------------------------------+
5 rows in set (0.01 sec)