• if(value,t f) ，如果value为真，返回t，否则返回f。

    • ifnull(value1, value2) ，如果value1不为空，返回value1，否则返回value2。

    • case when [value1] then [result1]...else[default] end，如果value1为真，返回result1，否则返回default。

    • case [expr] when [value1] then [result1]...else[default] end，如果expr等于value1，返回result1，否则返回default。

      1. create table salary (userid int, salary decimal(8,2));
      2. insert into salary values(1, 1000), (2, 2000), (3, 3000), (4, 4000);
      3. insert into salary values(5, null);
      4. select * from salary;
      5. +--------+---------+
      6. | userid | salary  |
      7. +--------+---------+
      8. |      1 | 1000.00 |
      9. |      2 | 2000.00 |
      10. |      3 | 3000.00 |
      11. |      4 | 4000.00 |
      12. |      5 |    NULL |
      13. +--------+---------+
      14. select if(salary > 2000, 'high', 'low') from salary;
      15. +----------------------------------+
      16. | if(salary > 2000, 'high', 'low') |
      17. +----------------------------------+
      18. | low                              |
      19. | low                              |
      20. | high                             |
      21. | high                             |
      22. | low                              |
      23. +----------------------------------+
      24. select ifnull(salary, 0) from salary;
      25. +-------------------+
      26. | ifnull(salary, 0) |
      27. +-------------------+
      28. |           1000.00 |
      29. |           2000.00 |
      30. |           3000.00 |
      31. |           4000.00 |
      32. |              0.00 |
      33. +-------------------+
      34. select userid, case when salary<=2000 then 'low' when salary=3000 then 'mid' else 'high' end as salary_level from salary;
      35. +--------+--------------+
      36. | userid | salary_level |
      37. +--------+--------------+
      38. |      1 | low          |
      39. |      2 | low          |
      40. |      3 | mid          |
      41. |      4 | high         |
      42. |      5 | high         |
      43. +--------+--------------+
      44. select userid, case salary when 1000 then 'low' when 2000 then 'low' when 3000 then 'mid'when 4000 then 'high' end as salary_level from salary;
      45. +--------+--------------+
      46. | userid | salary_level |
      47. +--------+--------------+
      48. |      1 | low          |
      49. |      2 | low          |
      50. |      3 | mid          |
      51. |      4 | high         |
      52. |      5 | NULL         |
      53. +--------+--------------+