如何在SQL中使用BETWEEN和IN运算符

介绍

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

SQL 允许用户通过提供各种不同类型的谓词来检索细粒度的结果集，每个谓词都使用特定的运算符来评估行。本指南将概述两种类型的谓词：使用 BETWEEN 运算符的范围谓词和使用 IN 运算符的集合成员谓词。

尽管本指南将在其示例中专门使用 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，包括 PostgreSQL 和 SQLite，但如果您在 MySQL 以外的系统上测试它们，确切的语法或输出可能会有所不同。

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

启动交互式终端！

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

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

ssh sammy@your_server_ip

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

mysql -u sammy -p

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

CREATE DATABASE between_in_db;

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

OutputQuery OK, 1 row affected (0.01 sec)

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

USE between_in_db;

OutputDatabase changed

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

为了遵循本指南中使用的示例，假设您管理着一家公司的销售团队。这家公司只销售三种产品：小部件、装饰物和小玩意。您开始在 SQL 数据库中跟踪团队中每个成员已售出的每种产品的单位数量。您决定此数据库将有一个包含四列的表：

name：您的销售团队的每个成员的姓名，使用 varchar 数据类型表示，最多 20 个字符
widgets：每个销售人员已售出的小部件总数，用int数据类型表示
doodads：每个销售人员已售出的饰品数量，也表示为 int
gizmos：每个销售人员已售出的 Gizmo 数量，再次表示为 int

运行以下 CREATE TABLE 语句以创建一个名为 sales 的表，该表具有以下四列：

CREATE TABLE sales (
name varchar(20),
widgets int,
doodads int,
gizmos int
);

OutputQuery OK, 0 rows affected (0.01 sec)

然后用一些示例数据加载 sales 表。运行以下 INSERT INTO 操作，添加七行数据，代表团队的销售人员以及他们销售的每种产品的数量：

INSERT INTO sales
VALUES
('Tyler', 12, 22, 18),
('Blair', 19, 8, 13),
('Lynn', 7, 29, 3),
('Boris', 16, 16, 15),
('Lisa', 17, 2, 31),
('Maya', 5, 9, 7),
('Henry', 14, 2, 0);

有了这个，您就可以按照指南的其余部分开始学习如何使用 BETWEEN 和 IN 运算符来过滤数据。

理解 `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 的 BETWEEN 和 IN 运算符来过滤数据。如果您想了解如何使用比较或 IS NULL 运算符，我们鼓励您查看有关如何在 SQL 中使用比较和 IS NULL 运算符的指南。或者，如果您想了解如何使用 LIKE 运算符根据包含通配符的字符串模式过滤数据，请按照我们的如何在 SQL 中使用通配符的指南进行操作。最后，如果您想了解更多关于 WHERE 子句的一般信息，您可能会对我们关于如何在 SQL 中使用 WHERE 子句的教程感兴趣。

范围谓词

范围谓词使用 BETWEEN 运算符来测试一个值表达式是否介于其他两个值表达式之间。在其搜索条件中包含范围谓词的 WHERE 子句将遵循以下通用语法：

SELECT column_list
FROM table_name
WHERE column_name BETWEEN value_expression1 AND value_expression2;

WHERE 关键字后面是一个值表达式，在大多数 SQL 操作中，它是列的名称。因为数据库系统按顺序将搜索条件应用于每一行，所以在搜索条件中提供列名作为值表达式会告诉 RDBMS 使用该列中每一行的值作为该行的搜索条件迭代的值表达式。

在列名之后是 BETWEEN 运算符和另外两个由 AND 分隔的值表达式。对于指定列中的值大于或等于由 AND 分隔的两个值中的第一个但小于或等于第二个的任何行，搜索条件将解析为“真”。

为了说明范围谓词如何工作，请运行以下查询。这将返回 widgets 值介于 14 和 19 之间的任何行的 name 和 widgets 列，包括：

SELECT name, widgets
FROM sales
WHERE widgets BETWEEN 14 AND 19;

Output+-------+---------+
| name  | widgets |
+-------+---------+
| Blair |      19 |
| Boris |      16 |
| Lisa  |      17 |
| Henry |      14 |
+-------+---------+
4 rows in set (0.00 sec)

请记住，您在 BETWEEN 运算符之后定义的范围可以由任何一对值表达式组成，包括列名。

以下查询返回 sales 表中的每一列。它没有列出要返回的每一列，而是在 SELECT 关键字后面加上一个星号 (*)；这是“每一列”的 SQL 简写。此查询的 WHERE 子句将其限制为仅返回 gizmos 值大于其 doodads 值但小于其 widgets 值的行：

SELECT *
FROM sales
WHERE gizmos BETWEEN doodads AND widgets;

只有一名销售团队成员的 gizmos 值介于其 widgets 和 doodads 值之间，因此只有该行出现在结果集中：

Output+-------+---------+---------+--------+
| name  | widgets | doodads | gizmos |
+-------+---------+---------+--------+
| Blair |      19 |       8 |     13 |
+-------+---------+---------+--------+
1 row in set (0.00 sec)

请注意列出定义范围的值表达式的顺序：BETWEEN 运算符之后的第一个值始终是范围的下限，第二个值始终是上限。以下查询与前一个查询相同，只是它翻转了定义范围两端的列的顺序：

SELECT *
FROM sales
WHERE gizmos BETWEEN widgets AND doodads;

这次，查询返回 gizmos 值大于或等于该行的 widgets 值但小于或等于其 doodads 值的两行。如此输出所示，像这样更改顺序将返回完全不同的结果集：

Output+-------+---------+---------+--------+
| name  | widgets | doodads | gizmos |
+-------+---------+---------+--------+
| Tyler |      12 |      22 |     18 |
| Maya  |       5 |       9 |      7 |
+-------+---------+---------+--------+
2 rows in set (0.00 sec)

像 <、>、<= 和 >= 比较运算符，当用于评估包含字符串值的列时BETWEEN运算符将确定这些值是否按字母顺序落在两个字符串值之间。

为了说明，运行以下查询，该查询从 sales 表中的 name 值介于字母 A 和 M，按字母顺序。

此示例使用两个字符串文字作为组成范围两端的值表达式。请注意，这些文字值必须用单引号或双引号括起来；否则，DBMS 将查找名为 A 和 M 的列，查询将失败：

SELECT name
FROM sales
WHERE name BETWEEN 'A' AND 'M';

Output+-------+
| name  |
+-------+
| Blair |
| Lynn  |
| Boris |
| Lisa  |
| Henry |
+-------+
5 rows in set (0.00 sec)

请注意，即使搜索条件中提供的范围是从 A 到 M，此结果集也不包括 Maya。这是因为，按字母顺序，字母“M”出现在任何以字母“M”开头且包含多个字母的字符串之前，因此 Maya 以及姓名不在给定范围。

成员谓词

成员关系谓词允许您根据值是否是指定数据集的成员来过滤查询结果。在 WHERE 子句中，它们通常遵循以下语法：

. . .
WHERE column_name IN (set_of_data)
. . .

WHERE 关键字之后是一个值表达式；同样，第一个值表达式通常是列的名称。紧随其后的是 IN 运算符，其本身后跟一组数据。您可以通过列出以逗号分隔的任意数量的有效值表达式（包括文字或列名，或涉及其中任何一个的数学表达式）来显式定义此集合。

为了说明，运行以下查询。这将为 gizmos 值是 IN 运算符之后定义的集合的成员的每一行返回 name 和 gizmos 列：

SELECT name, doodads
FROM sales
WHERE doodads IN (1, 2, 11, 12, 21, 22);

销售团队的 doodads 值分数中只有三个成员等于此集合中的任何值，因此仅返回这些行：

Output+-------+---------+
| name  | doodads |
+-------+---------+
| Tyler |      22 |
| Lisa  |       2 |
| Henry |       2 |
+-------+---------+
3 rows in set (0.00 sec)

您可以通过在 IN 运算符后跟一个子查询来派生一个集合，而不是自己写出集合的每个成员。 子查询 — 也称为嵌套或 内部查询 — 是嵌入在另一个 SELECT 声明。子查询可以从与“外部”操作的 FROM 子句中定义的表相同的数据库中的任何表中检索信息。

注意：在编写子查询以将集合定义为成员资格谓词的一部分时，请确保使用 标量子查询 或仅返回单个列的子查询。数据库管理系统通常不允许在成员资格谓词中返回多个列的子查询，因为数据库系统不清楚它应该评估哪个列作为集合。

作为使用子查询在成员资格谓词中定义集合的示例，运行以下语句以创建一个名为 example_set_table 的表，该表只有一列。此列将命名为 prime_numbers 并将保存 int 数据类型的值：

CREATE TABLE example_set_table (
prime_numbers int
);

然后用几行样本数据加载这个表。为了与表唯一列的名称保持一致，以下 INSERT 语句将向表中加载十行数据，每行包含前十个素数之一：

INSERT INTO example_set_table
VALUES
(2),
(3),
(5),
(7),
(11),
(13),
(17),
(19),
(23),
(29);

然后运行以下查询。这将返回来自 sales 表的 name 和 widgets 列的值，其 WHERE 子句测试 widgets 中的每个值是否列在子查询 SELECT prime_numbers FROM example_set_table 派生的集合中：

SELECT name, widgets
FROM sales
WHERE widgets IN (SELECT prime_numbers FROM example_set_table);

Output+-------+---------+
| name  | widgets |
+-------+---------+
| Blair |      19 |
| Lynn  |       7 |
| Lisa  |      17 |
| Maya  |       5 |
+-------+---------+
4 rows in set (0.00 sec)

因为只有四个销售人员销售了与存储在 example_set_table 中的任何素数相等的小部件，所以此查询仅返回这四行。

结论

通过遵循本指南，您了解了如何使用 SQL 的 BETWEEN 运算符来测试列中的值是否在给定范围内。您还学习了如何使用 IN 运算符来测试列中的值是否是集合的成员。

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

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