mysql中IF和IFNULL兩個例子

下面本文章給大家介紹兩個非常不錯的mysql中IF和IFNULL應用例子，希望這兩個例子能給各位同學帶來幫助哦。

1.IFNULL語句：IFNULL（exp1, exp2）;如果exp1是null的話返回exp2，如果不是null的話返回exp1

 

 代碼如下 復制代碼

mysql> SELECT IFNULL(null, 100);
+-------------------+
| IFNULL(null, 100) |
+-------------------+
|               100 |
+-------------------+

mysql> SELECT IFNULL(0, 100);
+----------------+
| IFNULL(0, 100) |
+----------------+
|              0 |
+----------------+

mysql> SELECT IFNULL(-10, 100);
+------------------+
| IFNULL(-10, 100) |
+------------------+
|              -10 |
+------------------+

mysql> SELECT IFNULL(10, 100);
+-----------------+
| IFNULL(10, 100) |
+-----------------+
|              10 |
+-----------------+

mysql> SELECT IFNULL('null', 100);
+---------------------+
| IFNULL('null', 100) |
+---------------------+
| null                |
+---------------------+

mysql> SELECT IFNULL(false, 100);
+--------------------+
| IFNULL(false, 100) |
+--------------------+
|                  0 |
+--------------------+

mysql> SELECT IFNULL(true, 100);
+-------------------+
| IFNULL(true, 100) |
+-------------------+
|                 1 |
+-------------------+

2.IF語句：IF（exp1, exp2, exp3）如果exp1為true（exp1 <> 0 && exp1 <> null）

返回exp2，否則返回exp3

 代碼如下 復制代碼

mysql> SELECT IF(STRCMP('str', 'str1'), 'yes', 'no');
+----------------------------------------+
| IF(STRCMP('str', 'str1'), 'yes', 'no') |
+----------------------------------------+
| yes                                    |
+----------------------------------------+

mysql> SELECT IF(0, 'yes', 'www.111cn.net');
+--------------------+
| IF(0, 'yes', 'no') |
+--------------------+
| no                 |
+--------------------+

mysql> SELECT IF(null, 'yes', 'no');
+-----------------------+
| IF(null, 'yes', 'no') |
+-----------------------+
| no                    |
+-----------------------+

mysql> SELECT IF('null', 'yes', 'no');
+-------------------------+
| IF('null', 'yes', 'no') |
+-------------------------+
| no                      |
+-------------------------+

mysql> SELECT IF(false, 'yes', 'no');
+------------------------+
| IF(false, 'yes', 'no') |
+------------------------+
| no                     |
+------------------------+

mysql> SELECT IF(-10, 'yes', 'no');
+----------------------+
| IF(-10, 'yes', 'no') |
+----------------------+
| yes                  |
+----------------------+

mysql> SELECT IF(10, 'yes', 'no');
+---------------------+
| IF(10, 'yes', 'no') |
+---------------------+
| yes                 |
+---------------------+

mysql> SELECT IF('0', 'yes', 'no');
+----------------------+
| IF('0', 'yes', 'no') |
+----------------------+
| no                   |
+----------------------+