4.1 IFNULL 与 NULLIF
1. IFNULL(expr1,expr2): 假如expr1 不为 NULL，则返回expr1; 否则返回expr2。
2. NULLIF(expr1,expr2): 如果 expr1 = expr2，那么返回 NULL，否则返回expr1。
实例:
[study_db]> SELECT book_author_name,
-> IFNULL(book_author_name, '匿名') AS '作者'
-> FROM new_books
-> WHERE book_id > 20;
+------------------+----------------+
| book_author_name | 作者 |
+------------------+----------------+
| 司马迁 | 司马迁 |
| Ken Follett | Ken Follett |
| 马克.李维 | 马克.李维 |
| NULL | 匿名 |
+------------------+----------------+
4 rows in set (0.01 sec)
[study_db]> SELECT NULLIF(1, 2), NULLIF(1, 1);
+--------------+--------------+
| NULLIF(1, 2) | NULLIF(1, 1) |
+--------------+--------------+
| 1 | NULL |
+--------------+--------------+
1 row in set (0.00 sec)
4.2 IF流程 -- IF(expr1,expr2,expr3)
如果expr1为真, 返回expr2, 否则返回expr3;
实例
MariaDB [study_db]> SELECT IF(1 > 2, 'Y', 'N');
+---------------------+
| IF(1 > 2, 'Y', 'N') |
+---------------------+
| N |
+---------------------+
1 row in set (0.00 sec)
4.3 CASE..THEN..END
先看CASE的用法：将case的值（或数据库里的字段）与条件进行匹配判断，如为真则将值置为THEN后面的值，表达式:
1. CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
2. CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
实例
MariaDB [study_db]> SELECT
-> CASE
-> WHEN book_price < 40 THEN 'A'
-> WHEN book_price > 50 AND book_price < 100 THEN 'B'
-> WHEN book_price > 100 THEN 'C'
-> ELSE 'D'
-> END
-> AS '价格分类',
-> book_price FROM new_books;
+--------------+------------+
| 价格分类 | book_price |
+--------------+------------+
| D | 44.00 |
| D | 44.44 |
| B | 79.00 |
| A | 26.00 |
| A | 26.00 |
| A | 26.60 |
| D | 40.00 |
| A | 30.00 |
| D | 44.00 |
| B | 80.00 |
| C | 123.00 |
| A | 30.00 |
| D | NULL |