MySQL比较运算符和函数

最新推荐文章于 2024-05-31 19:26:07 发布

原创最新推荐文章于 2024-05-31 19:26:07 发布 · 436 阅读

0 ·

CC 4.0 BY-SA版权

mysql数据库专栏收录该内容

35 篇文章

订阅专栏

本文详细介绍了MySQL中三种常用的比较运算符：BETWEEN...AND...、IN()及IS[NOT]NULL的使用方法，并通过具体示例展示了如何判断数值是否在指定范围内、是否在指定列表中以及字段是否为空。

本文重点是三个比较运算符

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)