本文重点是三个比较运算符
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)
补充:
mysql> SELECT NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT 'NULL' IS NULL;
+----------------+
| 'NULL' IS NULL |
+----------------+
| 0 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT 0 IS NULL;
+-----------+
| 0 IS NULL |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)