如何在SQL中使用WHERE子句

介绍

在结构化查询语言 (SQL) 语句中，WHERE 子句限制给定操作将影响的行。他们通过定义特定条件（称为 搜索条件 ）来做到这一点，每行必须满足这些条件才能使其受到操作的影响。

本指南将介绍 WHERE 子句中使用的一般语法。它还将概述如何在单个 WHERE 子句中组合多个搜索条件谓词以更精细地过滤数据，以及如何使用 NOT 运算符排除而不是包含行满足给定搜索条件的。

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

先决条件

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

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

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

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

启动交互式终端！

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

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

ssh sammy@your_server_ip

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

mysql -u sammy -p

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

CREATE DATABASE where_db;

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

OutputQuery OK, 1 row affected (0.01 sec)

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

USE where_db;

OutputDatabase changed

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

为了遵循本指南中使用的示例，假设您在当地的高尔夫球场举办高尔夫联赛。您决定跟踪有关联盟球员在他们参加的比赛中的个人表现的信息。为此，您决定将信息存储在 SQL 数据库中。

您决定此表需要六列：

name：每个球手的名字，用varchar数据类型表示，最多20个字符
rounds_played：每个高尔夫球手打过的完整回合的总数，用 int 数据类型表示
best：每位高尔夫球手在单场比赛中的最佳或最低得分，也表示为 int
worst：每位高尔夫球手在一次个人郊游中的最差或最高得分，再次表示为 int
average：每个高尔夫球手在他们打过的几轮比赛中得分的近似平均值。此列将保存 decimal 类型的值，限制为最多 4 位，其中一位在小数点右侧
wins：每个球手在比赛组中每个人得分最低的回合数，用int类型表示

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

CREATE TABLE golfers (
name varchar(20),
rounds_played int,
best int,
worst int,
average decimal (4,1),
wins int
);

然后用一些示例数据加载 golfers 表。运行以下 INSERT INTO 操作以添加代表联盟中七名高尔夫球手的七行数据：

INSERT INTO golfers
VALUES
('George', 22, 68, 103, 84.6, 3),
('Pat', 25, 65, 74, 68.7, 9),
('Grady', 11, 78, 118, 97.6, 0),
('Diane', 23, 70, 92, 78.8, 1),
('Calvin', NULL, 63, 76, 68.5, 7),
('Rose', NULL, 69, 84, 76.7, 4),
('Raymond', 18, 67, 92, 81.3, 1);

请注意，其中两行的 rounds_played 值为 NULL。出于本教程的目的，假设这些高尔夫球手没有报告他们打了多少轮，因此这些值记录为 NULL。

您可能还注意到每个高尔夫球手的 best 值小于他们的 worst。这是因为，在常见的高尔夫规则中，高尔夫球手的分数取决于他们将球打入球场每个洞所需的击球次数，获胜者是总击球次数最少的人。因此，与大多数其他运动不同，高尔夫球手的最佳成绩将低于最差成绩。

这样，您就可以按照本指南的其余部分开始学习如何在 SQL 中使用 WHERE 子句了。

使用 `WHERE` 子句过滤数据

在 SQL 中，语句是发送到数据库系统的任何操作，它将执行某种任务，例如创建表、插入或删除数据，或更改列或表的结构。 SQL 语句由各种子句组成，这些子句由某些关键字及其所需的信息组成。

如简介中所述，WHERE 子句允许您过滤掉某些数据行，使其不受 SQL 操作的影响。在查询中，WHERE 子句位于 FROM 子句之后，如下例所示：

SELECT columns_to_query
FROM table_to_query
WHERE search_condition;

WHERE关键字后面是搜索条件。搜索条件是一组一个或多个谓词，或者可以计算一个或多个值表达式并返回“真”、“假”或“未知”结果的表达式。请注意，在搜索条件仅包含单个谓词的情况下，术语“搜索条件”和“谓词”是同义词。

WHERE 子句搜索条件中的谓词可以采用多种形式，但它们通常遵循以下语法：

. . .
WHERE column_name OPERATOR value_expression
. . .

在 SQL 中， 值表达式 — 有时也称为 标量表达式 — 是任何将返回单个值的表达式。值表达式可以是文字值，如字符串或数值、数学表达式。但是，大多数情况下，WHERE 子句的搜索条件中的至少一个值表达式将是列名。

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

为了说明这个想法，运行以下查询。这将返回 golfers 表的 name 列中的每个值：

SELECT name
FROM golfers
WHERE (2 + 2) = 4;

此查询包含一个 WHERE 子句，但它没有指定列名，而是使用 (2 + 2) 作为第一个值表达式并测试它是否等于第二个值表达式 4。因为 (2 + 2) 是 always 等于 4，因此对于每一行，此搜索条件的计算结果为“真”。因此，每一行都会在结果集中返回：

Output+---------+
| name    |
+---------+
| George  |
| Pat     |
| Grady   |
| Diane   |
| Calvin  |
| Rose    |
| Raymond |
+---------+
7 rows in set (0.01 sec)

这个 WHERE 子句不是很有用，因为它总是评估为“真”并且总是返回表中的每一行。如前所述，您通常会在 WHERE 子句搜索条件中使用至少一个列名作为值表达式。运行查询时，数据库系统会将搜索条件依次单独应用于每一行。通过在搜索条件中提供列名作为值表达式，您就是在告诉 DBMS 使用该列中每一行的值作为该行的搜索条件迭代的值表达式。

以下查询的 WHERE 子句对每一行应用比上一个示例更排他的搜索条件。它将从 wins 列值等于 1 的任何行返回 name 和 wins 值：

SELECT name, wins
FROM golfers
WHERE wins = 1;

只有两名高尔夫球手恰好赢了一轮，所以查询只返回这两行：

Output+---------+------+
| name    | wins |
+---------+------+
| Diane   |    1 |
| Raymond |    1 |
+---------+------+
2 rows in set (0.01 sec)

前面的示例使用等号 (=) 来测试两个值表达式是否等价，但是您使用的运算符取决于您要使用哪种类型的谓词来过滤结果集。

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

比较

比较谓词使用比较运算符将一个值（在查询中，通常是指定列中的值）与另一个值进行比较。六个比较运算符是：

=：测试两个值是否相等

SELECT name
FROM golfers
WHERE name = 'George';

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

<>：测试两个值是否不相等

SELECT name, wins
FROM golfers
WHERE wins <> 1;

Output+--------+------+
| name   | wins |
+--------+------+
| George |    3 |
| Pat    |    9 |
| Grady  |    0 |
| Calvin |    7 |
| Rose   |    4 |
+--------+------+
5 rows in set (0.00 sec)

<：测试第一个值是否小于第二个

SELECT name, wins
FROM golfers
WHERE wins < 1;

Output+-------+------+
| name  | wins |
+-------+------+
| Grady |    0 |
+-------+------+
1 row in set (0.00 sec)

>：测试第一个值是否大于第二个

SELECT name, wins
FROM golfers
WHERE wins > 1;

Output+--------+------+
| name   | wins |
+--------+------+
| George |    3 |
| Pat    |    9 |
| Calvin |    7 |
| Rose   |    4 |
+--------+------+
4 rows in set (0.00 sec)

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

SELECT name, wins
FROM golfers
WHERE wins <= 1;

Output+---------+------+
| name    | wins |
+---------+------+
| Grady   |    0 |
| Diane   |    1 |
| Raymond |    1 |
+---------+------+
3 rows in set (0.00 sec)

>=：测试第一个值是否大于等于第二个

SELECT name, wins
FROM golfers
WHERE wins >= 1;

Output+---------+------+
| name    | wins |
+---------+------+
| George  |    3 |
| Pat     |    9 |
| Diane   |    1 |
| Calvin  |    7 |
| Rose    |    4 |
| Raymond |    1 |
+---------+------+
6 rows in set (0.00 sec)

空值

使用 IS NULL 运算符的谓词测试给定列中的值是否为 Null。如果是这样，则谓词评估为“真”并且该行包含在结果集中：

SELECT name, rounds_played
FROM golfers
WHERE rounds_played IS NULL;

Output+--------+---------------+
| name   | rounds_played |
+--------+---------------+
| Calvin |          NULL |
| Rose   |          NULL |
+--------+---------------+
2 rows in set (0.00 sec)

范围

范围谓词使用 BETWEEN 运算符来测试指定的列值是否介于两个值表达式之间：

SELECT name, best
FROM golfers
WHERE best BETWEEN 67 AND 73;

Output+---------+------+
| name    | best |
+---------+------+
| George  |   68 |
| Diane   |   70 |
| Rose    |   69 |
| Raymond |   67 |
+---------+------+
4 rows in set (0.00 sec)

会员资格

成员关系谓词使用 IN 运算符来测试一个值是否是给定集合的成员：

SELECT name, best
FROM golfers
WHERE best IN (65, 67, 69, 71);

Output+---------+------+
| name    | best |
+---------+------+
| Pat     |   65 |
| Rose    |   69 |
| Raymond |   67 |
+---------+------+
3 rows in set (0.00 sec)

模式匹配

模式匹配谓词使用 LIKE 运算符来测试一个值是否与包含一个或多个通配符（也称为通配符）的字符串模式匹配。 SQL 定义了两个通配符，% 和 _：

_：下划线表示单个未知字符

SELECT name, rounds_played
FROM golfers
WHERE rounds_played LIKE '2_';

Output+--------+---------------+
| name   | rounds_played |
+--------+---------------+
| George |            22 |
| Pat    |            25 |
| Diane  |            23 |
+--------+---------------+
3 rows in set (0.00 sec)

%：百分号表示零个或多个未知字符

SELECT name, rounds_played
FROM golfers
WHERE name LIKE 'G%';

Output+--------+---------------+
| name   | rounds_played |
+--------+---------------+
| George |            22 |
| Grady  |            11 |
+--------+---------------+
2 rows in set (0.00 sec)

更详细地介绍每种谓词类型超出了本教程的范围。但是，如果您想了解有关它们的更多信息，我们鼓励您查看以下指南：

使用 `AND` 和 `OR` 组合多个谓词

有时，您可能需要比具有单个搜索条件谓词的 WHERE 子句提供的更精细过滤的结果。另一方面，有时满足多个搜索条件之一的行在结果集中也可以接受。在这种情况下，您可以编写包含多个谓词的 WHERE 子句，分别使用 AND 或 OR 运算符。

要开始使用这些运算符，请运行以下查询，该查询从 golfers 表的 name、best、worst 和 average 中返回值] 列。它的 WHERE 子句包括两个谓词，由 AND 分隔：

SELECT name, best, worst, average
FROM golfers
WHERE best < 70 AND worst < 96;

第一个谓词测试每一行的 best 值是否小于 70，而第二个谓词测试每一行的 worst 值是否小于 96。如果任一测试对某行的评估结果为“假”，则该行将不会在结果集中返回：

Output+---------+------+-------+---------+
| name    | best | worst | average |
+---------+------+-------+---------+
| Pat     |   65 |    74 |    68.7 |
| Calvin  |   63 |    76 |    68.5 |
| Rose    |   69 |    84 |    76.7 |
| Raymond |   67 |    92 |    81.3 |
+---------+------+-------+---------+
4 rows in set (0.00 sec)

接下来，运行以下查询。这与前面的示例相同，只是它使用 OR 运算符而不是 AND 分隔两个谓词：

SELECT name, best, worst, average
FROM golfers
WHERE best < 70 OR worst < 96;

因为只有一个谓词必须评估为“真”才能返回一行，所以这个结果集比上一个示例多包含两行：

Output+---------+------+-------+---------+
| name    | best | worst | average |
+---------+------+-------+---------+
| George  |   68 |   103 |    84.6 |
| Pat     |   65 |    74 |    68.7 |
| Diane   |   70 |    92 |    78.8 |
| Calvin  |   63 |    76 |    68.5 |
| Rose    |   69 |    84 |    76.7 |
| Raymond |   67 |    92 |    81.3 |
+---------+------+-------+---------+
6 rows in set (0.00 sec)

您可以在单个 WHERE 子句中包含任意数量的谓词，只要将它们与正确的语法结合即可。但是，随着您的搜索条件变得越来越复杂，预测它们将过滤哪些数据可能会变得困难。

需要注意的是，数据库系统通常优先考虑 AND 运算符。这意味着任何由 AND 运算符分隔的谓词（或两个以上谓词的运算符）都被视为单个独立的搜索条件，在 [X226X 中的任何其他谓词之前进行测试] 子句。

为了说明，运行以下查询，该查询从 name、average、worst 和 rounds_played 列中返回满足搜索条件的任何行的值在 WHERE 子句中定义：

SELECT name, average, worst, rounds_played
FROM golfers 
WHERE average < 85 OR worst < 95 AND rounds_played BETWEEN 19 AND 23;

此查询首先测试由 AND 运算符分隔的谓词 — worst < 95 和 rounds_played BETWEEN 19 AND 23 — 对于当前迭代中的行是否都评估为“真”。如果是这样，那么该行将出现在结果集中。但如果任一计算结果为“假”，则查询将检查当前行的 average 值是否小于 85。如果是这样，将返回该行：

Output+---------+---------+-------+---------------+
| name    | average | worst | rounds_played |
+---------+---------+-------+---------------+
| George  |    84.6 |   103 |            22 |
| Pat     |    68.7 |    74 |            25 |
| Diane   |    78.8 |    92 |            23 |
| Calvin  |    68.5 |    76 |          NULL |
| Rose    |    76.7 |    84 |          NULL |
| Raymond |    81.3 |    92 |            18 |
+---------+---------+-------+---------------+
6 rows in set (0.00 sec)

您可以通过将一组两个或多个谓词括在括号中来确定它们的优先级。以下示例与上一个示例相同，但它将 average < 85 和 worst < 95 谓词包装在括号中，由 OR 运算符分隔：

SELECT name, average, worst, rounds_played
FROM golfers
WHERE (average < 85 OR worst < 95) AND rounds_played BETWEEN 19 AND 23;

因为前两个谓词用括号括起来，所以后面的 AND 运算符将它们视为必须评估为“真”的离散搜索条件。如果这两个谓词 - average < 85 和 worst < 95 - 计算结果为“假”，则整个搜索条件的计算结果为“假”，并且查询在继续之前立即从结果集中删除该行评估下一个。

但是，如果前两个谓词中的任何一个评估为“真”，则查询将测试给定高尔夫球手的 rounds_played 值是否介于 19 和 23 之间。如果是这样，该行将在结果集中返回：

Output+--------+---------+-------+---------------+
| name   | average | worst | rounds_played |
+--------+---------+-------+---------------+
| George |    84.6 |   103 |            22 |
| Diane  |    78.8 |    92 |            23 |
+--------+---------+-------+---------------+
2 rows in set (0.00 sec)

正如此输出所示，通过对谓词集进行优先排序并将它们包装在括号中，否则相同的查询可能会返回显着不同的结果集。

尽管并不总是需要这样做，但建议您在单个搜索条件中组合两个以上谓词时始终包含括号。这样做有助于使查询更具可读性和更易于理解。

使用 `NOT` 排除结果

到目前为止，本指南的所有示例都集中在如何使用 WHERE 子句编写查询，这些子句仅包含在其结果集中满足指定搜索条件的行。但是，您可以通过在 WHERE 子句中包含 NOT 运算符来编写排除特定行的查询。

包含 NOT 运算符的范围、成员资格和模式匹配谓词子句通常遵循以下语法：

. . .
WHERE column_name NOT OPERATOR value_expression
. . .

为了说明，运行以下查询。这将从 golfers 表的 name 列返回值，但 NOT 运算符在其 WHERE 子句中将导致 DBMS 排除与通配符模式：

SELECT name
FROM golfers
WHERE name NOT LIKE 'R%';

Output+--------+
| name   |
+--------+
| George |
| Pat    |
| Grady  |
| Diane  |
| Calvin |
+--------+
5 rows in set (0.00 sec)

将 NOT 运算符添加到 IS NULL 谓词时，情况会有所不同。在这种情况下，您可以将 NOT 放置在 IS 和 NULL 之间，如下例所示。此查询返回 rounds_played 值不为 Null 的每个高尔夫球手的 name 和 rounds_played 值：

SELECT name, rounds_played
FROM golfers
WHERE rounds_played IS NOT NULL;

Output+---------+---------------+
| name    | rounds_played |
+---------+---------------+
| George  |            22 |
| Pat     |            25 |
| Grady   |            11 |
| Diane   |            23 |
| Raymond |            18 |
+---------+---------------+
5 rows in set (0.00 sec)

您也可以将 NOT 运算符紧跟在 WHERE 关键字之后。如果您根据行是否满足多个搜索条件来排除行，这将很有用，如在此示例查询中返回高尔夫球手的 name、average、best 和wins 值：

SELECT name, average, best, wins
FROM golfers
WHERE NOT (average < 80 AND best < 70) OR wins = 9;

Output+---------+---------+------+------+
| name    | average | best | wins |
+---------+---------+------+------+
| George  |    84.6 |   68 |    3 |
| Pat     |    68.7 |   65 |    9 |
| Grady   |    97.6 |   78 |    0 |
| Diane   |    78.8 |   70 |    1 |
| Raymond |    81.3 |   67 |    1 |
+---------+---------+------+------+
5 rows in set (0.00 sec)

记下此结果集的第二行。 Pat 的 average 分数低于 80，她的 best 分数低于 70。但是，她的行仍然包含在结果集中，因为 NOT 运算符仅否定括在括号中的搜索条件。

回想一下，当您将由 AND 或 OR 分隔的多个谓词包装在括号中时，SQL 将优先考虑这些谓词并将它们视为单个孤立的搜索条件。因此，NOT 运算符仅根据前两个谓词 average < 80 和 best < 70 排除行。但它 包含基于第三个谓词 wins = 9 的 行。

您可以重写查询以排除基于第三个谓词以及前两个谓词的行，方法是将所有三个谓词括在括号中，如下所示：

SELECT name, average, best, wins
FROM golfers
WHERE NOT ((average < 80 AND best < 70) OR wins = 9);

Output+---------+---------+------+------+
| name    | average | best | wins |
+---------+---------+------+------+
| George  |    84.6 |   68 |    3 |
| Grady   |    97.6 |   78 |    0 |
| Diane   |    78.8 |   70 |    1 |
| Raymond |    81.3 |   67 |    1 |
+---------+---------+------+------+
4 rows in set (0.00 sec)

根据其 SQL 实现，如果在比较运算符之前包含 NOT，您的数据库系统可能会认为查询的语法无效。例如，尝试运行以下查询：

SELECT name
FROM golfers
WHERE name NOT = 'Grady';

在 MySQL 及其衍生产品上，这将导致错误：

OutputERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Grady'' at line 1

此错误的原因是 NOT 运算符通常不与比较运算符（=、<>、<、[X142X ]、> 和 >=），因为您可以通过将一个比较运算符替换为另一个返回第一个将排除的行的比较运算符来实现相反的效果。例如，您可以将等价运算符 (=) 替换为不等价运算符 (<>)。

结论

通过阅读本指南，您了解了如何编写 WHERE 子句，以便查询仅返回满足指定条件的行。您还学习了如何在单个查询中组合多个谓词和搜索条件，以及如何使用 NOT 关键字从结果集中排除信息。

虽然此处显示的命令应该适用于大多数关系数据库，但请注意，每个 SQL 数据库都使用自己独特的语言实现。您应该查阅您的 DBMS 的官方文档，以获得对每个命令及其全套选项的更完整描述。

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