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

介绍

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

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

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

先决条件

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

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

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

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

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

  • ssh sammy@your_server_ip

然后打开数据库服务器提示。如果您使用的是 MySQL，请运行以下命令，确保替换sammy为您的 MySQL 用户帐户的名称：

  • mysql -u sammy -p

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

  • CREATE DATABASE between_in_db;

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

Output
Query OK, 1 row affected (0.01 sec)

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

  • USE between_in_db;

Output
Database changed

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

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

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

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

  • CREATE TABLE sales (

  • name varchar(20),

  • widgets int,

  • doodads int,

  • gizmos int

  • );

Output
Query 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 实现中都包含它们中的每一种。以下是五种最常用的谓词类型，以及对每种类型及其使用的运算符的简要说明：

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

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

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

正如介绍中提到的，本指南重点介绍如何使用 SQLBETWEEN和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但小于或等于第二个值的任何行，搜索条件将解析为“真” 。

要说明范围谓词的工作原理，请运行以下查询。这将返回值在和之间的任何行的name和widgets列，包括：widgets1419

  • 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操作员将确定这些值是否两个字符串值之间下降的字母顺序。

为了说明这一点，请运行以下查询，该查询name从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)

请注意，Maya即使搜索条件中提供的范围是 fromA到，此结果集也不包括M。这是因为，按字母顺序，字母“M”出现在以字母“M”开头且包含多个字母的任何字符串之前，因此 Maya 与姓名不在此结果集中的任何其他销售人员一起被排除在此结果集之外。给定范围。

成员谓词

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

  • . . .

  • WHERE column_name IN (set_of_data)

  • . . .

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

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

  • 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嵌入在另一个的条款中的一个内语句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);

然后运行以下查询。这将返回表中name和widgets列的值sales，其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的其他教程。

觉得文章有用？

点个广告表达一下你的爱意吧 ！😁