MySQL比較運算符和函數

本文重點是三個比較運算符

1 ，[NOT[ BETWEEN ...AND...

2  ,  [NOT] IN()

3  ,  IS [NOT] NULL

例1:BETWEEN...AND..

如果數字A在設定範圍之内，傳回TRUE，否則傳回FAUSE

mysql> SELECT 15 BETWEEN 1 AND 22;

+---------------------+

| 15 BETWEEN 1 AND 22 |

+---------------------+

|                   1 |

+---------------------+

1 row in set (0.01 sec)

mysql> SELECT 35 BETWEEN 1 AND 22;

+---------------------+

| 35 BETWEEN 1 AND 22 |

+---------------------+

|                   0 |

+---------------------+

1 row in set (0.00 sec)

例2: NOT BETWEEN ...AND...

如果數字A不在設定範圍之内，傳回TRUE，否則傳回FAUSE

mysql> SELECT 35 NOT BETWEEN 1 AND 22;

+-------------------------+

| 35 NOT BETWEEN 1 AND 22 |

+-------------------------+

|                       1 |

+-------------------------+

1 row in set (0.00 sec)

mysql> SELECT 15 NOT BETWEEN 1 AND 22;

+-------------------------+

| 15 NOT BETWEEN 1 AND 22 |

+-------------------------+

|                       0 |

+-------------------------+

1 row in set (0.00 sec)

例3:IN，在列出的幾個點之内，傳回1，否則為0

mysql> SELECT 10 IN (5,10,22,9);

+-------------------+

| 10 IN (5,10,22,9) |

+-------------------+

|                 1 |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT 13 IN (5,10,22,9);

+-------------------+

| 13 IN (5,10,22,9) |

+-------------------+

|                 0 |

+-------------------+

1 row in set (0.00 sec)

例4:IN，不在列出的幾個點之内，傳回1，否則為0

mysql> SELECT 13 NOT IN (5,10,22,9);

+-----------------------+

| 13 NOT IN (5,10,22,9) |

+-----------------------+

|                     1 |

+-----------------------+

1 row in set (0.00 sec)

mysql> SELECT 10 NOT IN (5,10,22,9);

+-----------------------+

| 10 NOT IN (5,10,22,9) |

+-----------------------+

|                     0 |

+-----------------------+

1 row in set (0.00 sec)

例5: IS[NOT] NULL 查找為空的字段

先看一張表

mysql> SELECT * FROM testwish;

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| QQ         | 121       |

| AC         | BD        |

| 121        | 725       |

| tom%       | 2016      |

| NULL       | 11        |

+------------+-----------+

5 rows in set (0.00 sec)

mysql> SELECT * FROM testwish WHERE first_name IS NULL;

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| NULL       | 11        |

+------------+-----------+

1 row in set (0.00 sec)

mysql> SELECT * FROM testwish WHERE first_name IS NOT NULL;

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| QQ         | 121       |

| AC         | BD        |

| 121        | 725       |

| tom%       | 2016      |

+------------+-----------+

4 rows in set (0.00 sec)

補充: