BETWEEN … AND …

在范围内，存在返回1，不存在返回0

  1. -- 在范围内返回 1
  2. mysql> SELECT 55 BETWEEN 50 AND 60;
  3. +----------------------+
  4. | 55 BETWEEN 50 AND 60 |
  5. +----------------------+
  6. |                    1 |
  7. +----------------------+
  8. -- 不在范围内返回 0
  9. mysql> SELECT 55 BETWEEN 50 AND 52;
  10. +----------------------+
  11. | 55 BETWEEN 50 AND 52 |
  12. +----------------------+
  13. |                    0 |
  14. +----------------------+
  15. -- 查询联系
  16. mysql> SELECT * FROM user WHERE age BETWEEN 19 and 20;
  17. +----+--------+------+------+
  18. | id | name   | age  | sex  |
  19. +----+--------+------+------+
  20. |  1 | 小明   |   19 | 男   |
  21. |  3 | 小环   |   19 | 女   |
  22. +----+--------+------+------+

NOT BETWEEN … AND …

不在范围内

  1. mysql> SELECT * FROM user WHERE age NOT BETWEEN 19 and 20;
  2. +----+--------+------+------+
  3. | id | name   | age  | sex  |
  4. +----+--------+------+------+
  5. |  2 | 小红   |   11 | 女   |
  6. |  4 | 小白   |   21 | 男   |
  7. +----+--------+------+------+

IN()

在列出值范围内，多选

  2. -- 存在返回 1
  3. mysql> SELECT 3 IN (2,3,4,5);
  4. +----------------+
  5. | 3 IN (2,3,4,5) |
  6. +----------------+
  7. |              1 |
  8. +----------------+
  9. -- 不存在返回 0
  10. mysql> SELECT 1 IN (2,3,4,5);
  11. +----------------+
  12. | 1 IN (2,3,4,5) |
  13. +----------------+
  14. |              0 |
  15. +----------------+
  16. -- 查询
  17. mysql> SELECT * FROM user WHERE age in(11,21);
  18. +----+--------+------+------+
  19. | id | name   | age  | sex  |
  20. +----+--------+------+------+
  21. |  2 | 小红   |   11 | 女   |
  22. |  4 | 小白   |   21 | 男   |
  23. +----+--------+------+------+

NOT IN()

不在列出值访问内

  1. mysql> SELECT * FROM user WHERE age NOT IN (11,21);
  2. +----+--------+------+------+
  3. | id | name   | age  | sex  |
  4. +----+--------+------+------+
  5. |  1 | 小明   |   19 | 男   |
  6. |  3 | 小环   |   19 | 女   |
  7. +----+--------+------+------+

IS NULL

为NULL，只能判断是否为NULL

  1. mysql> SELECT NULL IS NULL;
  2. +--------------+
  3. | NULL IS NULL |
  4. +--------------+
  5. |            1 |
  6. +--------------+
  8. mysql> SELECT 'NULL' IS NULL;
  9. +----------------+
  10. | 'NULL' IS NULL |
  11. +----------------+
  12. |              0 |
  13. +----------------+
  14. -- 查询数据
  15. mysql> SELECT * FROM user WHERE sex IS NULL;
  16. Empty set (0.00 sec)

IS NOT NULL

不为NULL，只能判断是否为NULL

  1. mysql> SELECT * FROM user WHERE sex IS NOT NULL;
  2. +----+--------+------+------+
  3. | id | name   | age  | sex  |
  4. +----+--------+------+------+
  5. |  1 | 小明   |   19 | 男   |
  6. |  2 | 小红   |   11 | 女   |
  7. |  3 | 小环   |   19 | 女   |
  8. |  4 | 小白   |   21 |      |
  9. +----+--------+------+------+