介绍
在某些 结构化查询语言 (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 的其他教程。