如何在SQL中使用比较和ISNULL运算符

介绍

在某些结构化查询语言 (SQL) 语句中，WHERE 子句可用于限制给定操作将影响的行。他们通过定义每行必须满足才能受到影响的特定标准来做到这一点，称为 搜索条件 。搜索条件由一个或多个谓词组成，它们是计算结果为“真”、“假”或“未知”的特殊表达式，并且操作只影响那些在WHERE 子句的计算结果为“真”。

SQL 允许用户编写包含各种不同类型谓词的搜索条件，每个谓词都使用特定的运算符来评估行。本指南将概述两种类型的谓词及其使用的运算符： 比较运算符 和 IS NULL 运算符。

尽管本指南将在其示例中专门使用 SELECT statements，但此处解释的概念可用于许多 SQL 操作。特别是，WHERE 子句及其搜索条件是 UPDATE 和 DELETE 操作的关键组成部分。

先决条件

为了遵循本指南，您需要一台运行某种类型的使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已使用以下环境进行验证：

运行 Ubuntu 20.04 的服务器，具有管理权限的非 root 用户和配置了 UFW 的防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南中所述。
如如何在 Ubuntu 20.04 上安装 MySQL 中所述，在服务器上安装并保护了 MySQL。如 Step 3 中所述，本指南已通过新创建的用户验证。
您还需要一个数据库，其中包含一些加载有示例数据的表，您可以使用这些表来练习使用比较和 IS NULL 运算符。我们鼓励您阅读以下 连接到 MySQL 并设置示例数据库 部分，详细了解如何连接到 MySQL 服务器并创建本指南示例中使用的测试数据库。

注意：请注意，许多 RDBMS 使用自己独特的 SQL 实现。尽管本教程中概述的命令适用于大多数 RDBMS，包括 PostgreSQL 和 SQLite，但如果您在 MySQL 以外的系统上测试它们，确切的语法或输出可能会有所不同。

您还可以使用嵌入在此页面上的交互式终端来试验本教程中的示例查询。单击下面的Launch an Interactive Terminal!按钮开始使用。

启动交互式终端！

连接到 MySQL 并设置示例数据库

如果您的 SQL 数据库系统在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：

ssh sammy@your_server_ip

然后打开 MySQL 服务器提示符，将 sammy 替换为您的 MySQL 用户帐户的名称。如果您在此页面上使用嵌入式交互式终端，请注意提示时使用的密码是单词 secret：

mysql -u sammy -p

根据提示，创建一个名为 comparison_null_db 的数据库：

CREATE DATABASE comparison_null_db;

如果数据库创建成功，您将收到如下输出：

OutputQuery OK, 1 row affected (0.01 sec)

要选择 comparison_null_db 数据库，请运行以下 USE 语句：

USE comparison_null_db;

OutputDatabase changed

选择 comparison_null_db 后，在其中创建一个表格。

为了遵循本指南中使用的示例，假设您和您的一群朋友都决定变得更加活跃，并将跑步作为一种锻炼方式。为此，您的朋友都设定了下个月要跑多少英里的个人目标。您决定在包含以下三列的 SQL 表中跟踪您朋友的里程目标以及他们实际跑的里程数：

name：你每个朋友的名字，用varchar数据类型表示，最多15个字符
goal：每个朋友过去一个月希望跑多少英里的目标，使用int数据类型表示为整数
result：每个朋友在一个月内最终跑的里程数，再次表示为 int

运行以下 CREATE TABLE 语句以创建一个名为 running_goals 的表，其中包含以下三列：

CREATE TABLE running_goals (
name varchar(15),
goal int,
result int
);

OutputQuery OK, 0 rows affected (0.012 sec)

然后用一些示例数据加载 running_goals 表。运行以下 INSERT INTO 操作以添加代表您的七个朋友、他们的跑步目标和结果的七行数据：

INSERT INTO running_goals
VALUES
('Michelle', 55, 48),
('Jerry', 25, NULL),
('Milton', 45, 52),
('Bridget', 40, NULL),
('Wanda', 30, 38),
('Stewart', 35, NULL),
('Leslie', 40, 44);

OutputQuery OK, 7 rows affected (0.004 sec)
Records: 7 Duplicates: 0 Warnings: 0

请注意，其中三行的 result 值为 NULL。出于本示例的目的，假设这些朋友只是没有报告他们在过去一个月跑的里程数，因此他们的 result 值输入为 NULL。

有了它，您就可以按照本指南的其余部分开始学习如何在 SQL 中使用比较和 IS NULL 运算符。

理解 `WHERE` 子句谓词

在从现有表读取数据的任何 SQL 操作中，您可以在 FROM 子句和 WHERE 子句之后限制操作将影响的数据。 WHERE 子句通过定义搜索条件来做到这一点；任何不满足搜索条件的行都被排除在操作之外，但任何满足的行都被包括在内。

搜索条件由一个或多个谓词或可以计算一个或多个值表达式并返回“真”、“假”或“未知”结果的表达式组成。在 SQL 中， 值表达式 — 有时也称为 标量表达式 — 是任何将返回单个值的表达式。值表达式可以是文字值，如字符串或数值、数学表达式或列名。请注意，在 WHERE 子句谓词中至少有一个值表达式是操作的 FROM 子句中引用的表中列的名称几乎总是这样。

在运行包含 WHERE 子句的 SQL 查询时，DBMS 会将搜索条件应用于由 FROM 子句定义的逻辑表中的每一行。然后它将只返回搜索条件中每个谓词的计算结果为“真”的行。

SQL 标准定义了 18 种类型的谓词，尽管并非每个 RDBMS 在其 SQL 实现中都包含它们中的每一种。以下是五种最常用的谓词类型，以及对每种谓词及其使用的运算符的简要说明：

Comparison：比较谓词将一个值表达式与另一个值表达式进行比较；在查询中，这些值表达式中至少有一个是列名的情况几乎总是如此。六个比较运算符是：

=：测试两个值是否相等
<>：测试两个值是否不等价
<：测试第一个值是否小于第二个
>：测试第一个值是否大于第二个
<=：测试第一个值是否小于或等于第二个
>=：测试第一个值是否大于等于第二个

Null：使用 IS NULL 运算符的谓词测试给定列中的值是否为 Null Range：范围谓词使用 BETWEEN 运算符测试是否一个值表达式落在另外两个之间 Membership：这种类型的谓词使用 IN 运算符来测试一个值是否是给定集合的成员 模式匹配：模式匹配谓词使用 LIKE 运算符来测试值是否与字符串模式匹配

正如介绍中提到的，本指南重点介绍了如何使用 SQL 的比较和 IS NULL 运算符来过滤数据。如果您想了解如何分别将 BETWEEN 或 IN 运算符与范围和成员谓词一起使用，我们建议您查看有关如何使用 BETWEEN 的指南和 SQL 中的 IN 运算符。或者，如果您想了解如何使用 LIKE 运算符根据包含通配符的字符串模式过滤数据，请按照我们的如何在 SQL 中使用通配符的指南进行操作。最后，如果您想了解更多关于 WHERE 子句的一般信息，您可能会对我们关于如何在 SQL 中使用 WHERE 子句的教程感兴趣。

比较谓词

WHERE 子句比较谓词使用六个比较运算符之一将一个值表达式与另一个值表达式进行比较。它们通常遵循如下语法：

SELECT column_list
FROM table_name
WHERE column_name OPERATOR value_expression;

WHERE 关键字后面是一个值表达式，在大多数 SQL 操作中，它是列的名称。在搜索条件中提供列名作为值表达式会告诉 RDBMS 使用该列中每一行的值作为该行的搜索条件迭代的值表达式。因为数据库系统按顺序对每一行应用搜索条件，所以比较运算符将根据搜索条件对于指定列的值是否为真来包含或过滤掉一行。

为了说明，运行以下查询，它将返回 running_goals 表的 name 和 goal 列的值。请注意 WHERE 子句如何使用比较谓词，该谓词将导致查询仅返回 goal 值等于 40 的行：

SELECT name, goal
FROM running_goals
WHERE goal = 40;

只有两个朋友的目标是在过去一个月内跑完 40 英里，所以查询只返回这两行：

Output+---------+------+
| name    | goal |
+---------+------+
| Bridget |   40 |
| Leslie  |   40 |
+---------+------+
2 rows in set (0.00 sec)

为了说明其他比较运算符的工作原理，请运行以下查询，这些查询与前面的示例相同，只是每个查询都使用不同的比较运算符。

<> 运算符测试两个值是否 not 等价，因此此查询返回 goal 值不等于 40 的每一行：

SELECT name, goal
FROM running_goals
WHERE goal <> 40;

Output+----------+------+
| name     | goal |
+----------+------+
| Michelle |   55 |
| Jerry    |   25 |
| Milton   |   45 |
| Wanda    |   30 |
| Stewart  |   35 |
+----------+------+
5 rows in set (0.00 sec)

< 运算符测试第一个值表达式是否小于第二个：

SELECT name, goal
FROM running_goals
WHERE goal < 40;

Output+---------+------+
| name    | goal |
+---------+------+
| Jerry   |   25 |
| Wanda   |   30 |
| Stewart |   35 |
+---------+------+
3 rows in set (0.00 sec)

> 运算符测试第一个值表达式是否大于第二个：

SELECT name, goal
FROM running_goals
WHERE goal > 40;

Output+----------+------+
| name     | goal |
+----------+------+
| Michelle |   55 |
| Milton   |   45 |
+----------+------+
2 rows in set (0.00 sec)

<= 运算符测试第一个值是否小于或等于第二个：

SELECT name, goal
FROM running_goals
WHERE goal <= 40;

Output+---------+------+
| name    | goal |
+---------+------+
| Jerry   |   25 |
| Bridget |   40 |
| Wanda   |   30 |
| Stewart |   35 |
| Leslie  |   40 |
+---------+------+
5 rows in set (0.00 sec)

>= 运算符测试第一个值是否大于或等于第二个：

SELECT name, goal
FROM running_goals
WHERE goal >= 40;

Output+----------+------+
| name     | goal |
+----------+------+
| Michelle |   55 |
| Milton   |   45 |
| Bridget  |   40 |
| Leslie   |   40 |
+----------+------+
4 rows in set (0.00 sec)

等价 (=) 和不等价 (<>) 操作符可以像预期的那样处理字符串值。以下查询返回等于 'Leslie' 的每一行的 name 值：

SELECT name
FROM running_goals
WHERE name = 'Leslie';

因为表中只有一个名为“Leslie”的朋友，所以查询只返回该行：

Output+--------+
| name   |
+--------+
| Leslie |
+--------+
1 row in set (0.00 sec)

在比较字符串值时，<、>、<= 和 >= 运算符都会评估字符串的字母顺序关系。换句话说，如果您编写一个谓词来测试一个字符串是否“小于”另一个字符串，那么您就是在测试第一个字符串是否按字母顺序排在第二个之前。同样，如果您的谓词测试一个字符串是否“大于”另一个字符串，您就是在测试第一个字符串是否按字母顺序排在第二个字符串之后。

为了说明，运行以下查询。这将返回 name 值“小于”字母 'M' 的每一行的 name 和 goal 值。换句话说，对于 name 值按字母顺序在 M 之前的每一行，搜索条件将评估为“真”：

SELECT name
FROM running_goals
WHERE name < 'M';

Output+---------+
| name    |
+---------+
| Jerry   |
| Bridget |
| Leslie  |
+---------+
3 rows in set (0.00 sec)

请注意，此结果集不包括 Michelle 或 Milton。这是因为，按字母顺序，单个字母“M”出现在任何以字母“M”开头且包含多个字母的字符串之前，因此这两个朋友被排除在此结果集中。

空谓词

在 SQL 中，NULL 是一个保留关键字，用于表示缺失值或未知值。 Null 是一个状态，而不是一个实际值；它不表示零或空字符串。

您可以使用 IS NULL 运算符来测试给定值表达式是否为 Null：

. . . 
WHERE column_name IS NULL
. . .

使用这种类型的谓词，数据库系统将查看指定列中每一行的值，并评估每一行是否为 Null。如果列中的值确实为 Null，则这些行的搜索条件将评估为“真”，并且它们将包含在结果集中。

为了说明，运行以下返回 name 和 result 列的查询：

SELECT name, result
FROM running_goals
WHERE result IS NULL;

此查询的 WHERE 子句中的搜索条件测试每一行的 result 值是否为 Null。如果是这样，则谓词评估为“真”并且该行包含在结果集中：

Output+---------+--------+
| name    | result |
+---------+--------+
| Jerry   |   NULL |
| Bridget |   NULL |
| Stewart |   NULL |
+---------+--------+
3 rows in set (0.00 sec)

因为您的三个朋友尚未报告他们在过去一个月中最终跑的里程数，所以当您在表格中加载数据时，这些值被记录为 NULL。因此，对于这三行，查询中的搜索条件评估为“真”，因此它们是唯一包含在结果集中的行。

结论

通过遵循本指南，您了解了如何在 WHERE 子句中使用 SQL 的比较和 IS NULL 运算符来限制操作将影响的行。虽然此处显示的命令应该适用于大多数关系数据库，但请注意每个 SQL 数据库都使用自己独特的 SQL 标准实现。您应该查阅您的 DBMS 的官方文档，以获得对每个命令及其全套选项的更完整描述。

如果您想了解有关使用 SQL 的更多信息，我们鼓励您查看本系列中关于如何使用 SQL 的其他教程。

如何在SQL中使用比较和ISNULL运算符

介绍

先决条件

连接到 MySQL 并设置示例数据库

理解 WHERE 子句谓词

比较谓词

空谓词

结论

理解 `WHERE` 子句谓词