mysql中IF和IFNULL两个例子

下面本文章给大家介绍两个非常不错的mysql中IF和IFNULL应用例子,希望这两个例子能给各位同学带来帮助.

1.IFNULL语句:IFNULL(exp1, exp2);如果exp1是null的话返回exp2,如果不是null的话返回exp1,代码如下:

1. mysql>SELECTIFNULL(null,100);
2. +-------------------+
3. |IFNULL(null,100)|
4. +-------------------+
5. |100|
6. +-------------------+
7. mysql>SELECTIFNULL(0,100);
8. +----------------+
9. |IFNULL(0,100)|
10. +----------------+
11. |0|
12. +----------------+
13. mysql>SELECTIFNULL(-10,100);
14. +------------------+
15. |IFNULL(-10,100)|
16. +------------------+
17. |-10|
18. +------------------+
19. mysql>SELECTIFNULL(10,100);
20. +-----------------+
21. |IFNULL(10,100)|
22. +-----------------+
23. |10|
24. +-----------------+
25. mysql>SELECTIFNULL('null',100);
26. +---------------------+
27. |IFNULL('null',100)|
28. +---------------------+
29. |null|
30. +---------------------+
31. mysql>SELECTIFNULL(false,100);
32. +--------------------+
33. |IFNULL(false,100)|
34. +--------------------+
35. |0|
36. +--------------------+
37. mysql>SELECTIFNULL(true,100);
38. +-------------------+
39. |IFNULL(true,100)|
40. +-------------------+
41. |1|
42. +-------------------+

2.IF语句:IF(exp1, exp2, exp3)如果exp1为true（exp1 <> 0 && exp1 <> null),返回exp2,否则返回exp3,代码如下:

1. mysql>SELECTIF(STRCMP('str','str1'),'yes','no');
2. +----------------------------------------+
3. |IF(STRCMP('str','str1'),'yes','no')|
4. +----------------------------------------+
5. |yes|
6. +----------------------------------------+
7. mysql>SELECTIF(0,'yes','www.111cn.net');
8. +--------------------+
9. |IF(0,'yes','no')|
10. +--------------------+
11. |no|
12. +--------------------+
13. mysql>SELECTIF(null,'yes','no');
14. +-----------------------+
15. |IF(null,'yes','no')|
16. +-----------------------+
17. |no|
18. +-----------------------+
19. mysql>SELECTIF('null','yes','no');
20. +-------------------------+
21. |IF('null','yes','no')|
22. +-------------------------+
23. |no|
24. +-------------------------+
25. mysql>SELECTIF(false,'yes','no');
26. +------------------------+
27. |IF(false,'yes','no')|
28. +------------------------+
29. |no|
30. +------------------------+
31. mysql>SELECTIF(-10,'yes','no');
32. +----------------------+
33. |IF(-10,'yes','no')|
34. +----------------------+
35. |yes|
36. +----------------------+
37. mysql>SELECTIF(10,'yes','no');
38. +---------------------+
39. |IF(10,'yes','no')|
40. +---------------------+
41. |yes|
42. +---------------------+
43. mysql>SELECTIF('0','yes','no');
44. +----------------------+
45. |IF('0','yes','no')|
46. +----------------------+
47. |no|
48. +----------------------+
49. //开源软件:phpfensi.com