如何在 SQL 中使用 BETWEEN 和 IN 操作符

介绍

在某些 Structured Query Language (SQL) 语句中, WHERE 语句语句可以用来限制该操作会影响哪个行。

SQL 允许用户通过提供各种不同类型的序列来检索细分结果集,每个序列使用特定的运算符来评估行.本指南将概述两种类型的序列:使用BETWEEN运算符的范围序列,以及使用IN运算符的会员序列。

<$>[info] 虽然本指南将在其示例中仅使用 SELECT statements,但这里所解释的概念可以在一系列 SQL 操作中使用。

前提条件

要遵循本指南,您需要运行某种类型的关系数据库管理系统(RDBMS)的计算机,该系统使用SQL。

运行 Ubuntu 20.04 的服务器,具有非 root 用户的管理权限和与 UFW 配置的防火墙,如我们在 [Ubuntu 20.04 的初始服务器设置指南]中所描述的(https://andsky.com/tech/tutorials/initial-server-setup-with-ubuntu-20-04)。
MySQL 安装并保护在服务器上,如在 How To Install MySQL on Ubuntu 20.04中所述。此指南已与新创建的用户进行验证,如在 Step 3中所描述。
您还需要一个具有一些数据库表加载的样本数据,您可以使用使用不同的WHERE条款预言练习。我们鼓励您通过以下 连接到 MySQL 并设置样本数据库的细节,了解如何连

<$>[注] 注:请注意,许多RDBMS使用自己的独特的SQL实现,虽然本教程中描述的命令将在大多数RDBMS上工作,包括PostgreSQL和SQLite,但如果您在MySQL以外的系统上测试它们,确切的语法或输出可能会有所不同。

连接到MySQL并设置样本数据库

如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:

1[environment local]
2ssh sammy@your_server_ip

然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:

1mysql -u sammy -p

从提示中创建一个名为 between_in_db 的数据库:

1CREATE DATABASE between_in_db;

如果数据库创建成功,您将收到这样的输出:

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

若要选择between_in_db数据库,请运行以下USE语句:

1USE between_in_db;

1[secondary_label Output]
2Database changed

在选择between_in_db后,创建一个表。

若要跟随本指南中使用的示例,请想象您正在管理公司的销售团队. 该公司只销售三种产品: widgets、doodads 和 gizmos. 您开始跟踪每个产品每个团队成员在 SQL 数据库中销售的单位数量。

*「名稱」:您的銷售團隊的每一個成員的名字,用最多 20 個字符的「varchar」數據類型表示 *「widgets」:每個銷售者銷售的widgets總數,用「int」數據類型表示 *「doodads」:每個銷售者銷售的 doodads 數量,也表示為「int」 *「gizmos」:每個銷售者銷售的 gizmos 數量,再次表示為「int」

运行以下创建表语句,创建一个名为销售的表,其中包含以下四个列:

1CREATE TABLE sales (
2name varchar(20),
3widgets int,
4doodads int,
5gizmos int
6);

1[secondary_label Output]
2Query OK, 0 rows affected (0.01 sec)

然后加载销售表中一些样本数据,运行以下输入操作,添加代表团队销售人员和他们销售的每个产品数量的七行数据:

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

有了它,您可以随时跟随其余的指南,并开始学习如何使用BETWEEN和IN运算符来过滤数据。

理解`Where`条款预言

在任何从现有表中读取数据的 SQL 操作中,您可以按照从条款与哪里条款来限制该操作会影响哪些数据。

搜索条件由一个或多个 predicates 或表达式组成,可以评估一个或多个值表达式并返回一个结果为真实,假或未知。在 SQL 中,一个 _value 表达式 - 也有时被称为 _scalar 表达式 - 是返回单个值的任何表达式。

当运行包含WHERE条款的 SQL 查询时,DBMS 会将搜索条件应用到由FROM条款定义的逻辑表中的每个行,然后只会返回搜索条件中的每个预言评估为真实的行。

SQL 标准定义了 18 种类型的序列,虽然并非每个 RDBMS 都包括它们在其 SQL 实现中。

比较:比较预测将一个值表达式与另一个值表达式进行比较;在查询中,通常至少其中一个值表达式是列的名称。

=:測試兩個值是否等同
>:測試兩個值是否不等同
``:測試第一個值是否小於第二個值
>:測試第一個值是否大於第二個值
<=:測試第一個值是否小於或等於第二個值
>=:測試第一個值是否大於或等於第二個值

Null:使用IS NULL运算符测试的预测表明特定列中的值是否为Null Range:范围预测表使用BETWEEN运算符来测试一个值表达式是否在另外两个值表达式之间 会员性:此类型的预测表使用IN运算符来测试一个值是否是特定集的成员 Pattern Match:模式匹配预测使用LIKE运算符来测试一个值是否匹配一个字符串模式

正如介绍中提到的,本指南侧重于描述如何使用 SQL 的BETWEEN和IN操作员来过滤数据。如果您想学习如何使用比较或IS NULL操作员,我们鼓励您查看本指南中的如何在 SQL 中使用比较和IS NULL操作员(LINK0)。或者,如果您想学习如何使用LIKE操作员来过滤数据基于包含 wildcard 字符的字符串模式,请遵循我们的指南(How To Use Wildcards in SQL)(LINK1)。最后,如果您想了解更多关于Where条款的信息,您可能对我们的How To Use WHERE Clauses in SQL教程(LINK2)感兴趣。

预言范围

范围预测使用BETWEEN运算符来测试一个值表达式是否在其他两个值表达式之间。

1SELECT column_list
2FROM table_name
3WHERE column_name BETWEEN value_expression1 AND value_expression2;

由于数据库系统将搜索条件应用于每行序列中,提供列名作为搜索条件中的值表达式,告诉 RDBMS 使用从该列的每个行值作为该行搜索条件迭代的值表达式。

列名之后出现BETWEEN运算符和另外两个值表达式以AND分开,搜索条件将为真解决,如果从指定的列中的值大于或等于AND分开的两个值中的第一个值,但小于或等于第二个值的行。

要说明范围预测是如何工作的,请运行下面的查询. 这将返回任何有widgets值在14和19之间的行的名称和widgets列,包括:

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

 1[secondary_label Output]
 2+-------+---------+
 3| name  | widgets |
 4+-------+---------+
 5| Blair |      19 |
 6| Boris |      16 |
 7| Lisa  |      17 |
 8| Henry |      14 |
 9+-------+---------+
104 rows in set (0.00 sec)

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

下面的查询返回了销售表中的每个列,而不是列出要返回的每个列,而是用一个星座(*)跟随的SELECT关键字;这是每个列的SQL缩写;此查询的WHERE条款仅限于返回只有其gizmos值大于其doodads值但小于其widgets值的行:

1SELECT *
2FROM sales
3WHERE gizmos BETWEEN doodads AND widgets;

只有一个销售团队成员有gizmos值,其widgets和doodads值之间,所以只有该行出现在结果集中:

1[secondary_label Output]
2+-------+---------+---------+--------+
3| name  | widgets | doodads | gizmos |
4+-------+---------+---------+--------+
5| Blair |      19 |       8 |     13 |
6+-------+---------+---------+--------+
71 row in set (0.00 sec)

请注意列出定义范围的值表达式的顺序:在BETWEEN运算符之后的第一个值总是范围的底部,第二个总是范围的顶部。

1SELECT *
2FROM sales
3WHERE gizmos BETWEEN widgets AND doodads;

这次,查询返回两个行,其中gizmos值大于或等于行的widgets值,但小于或等于其doodads值。

1[secondary_label Output]
2+-------+---------+---------+--------+
3| name  | widgets | doodads | gizmos |
4+-------+---------+---------+--------+
5| Tyler |      12 |      22 |     18 |
6| Maya  |       5 |       9 |      7 |
7+-------+---------+---------+--------+
82 rows in set (0.00 sec)

如 <, >, <= 和 >= 比较运算符,当用于评估一个列持有字符串值时,BETWEEN运算符将确定这些值是否按字母值在两个字符串值之间。

为了说明,运行以下查询,从销售表中的任何行中返回名称值,其名称值在字母A和M之间,字母字母。

此示例使用两个字符串字母作为组成范围的任何一个端的值表达式. 请注意,这些字母值必须被包装成单个或双重引用;否则,DBMS 将搜索名为A和M的列,然后查询将失败:

1SELECT name
2FROM sales
3WHERE name BETWEEN 'A' AND 'M';

 1[secondary_label Output]
 2+-------+
 3| name  |
 4+-------+
 5| Blair |
 6| Lynn  |
 7| Boris |
 8| Lisa  |
 9| Henry |
10+-------+
115 rows in set (0.00 sec)

请注意,此结果集不包括Maya,尽管搜索条件中提供的范围从A到M 这是因为,从字母字母的角度来看,字母M在任何以字母M开头的字符串之前出现,并且有一个以上的字母,所以Maya被排除在这个结果集中,以及任何其他销售人员,他们的名字不在给定范围内。

会员预言

会员预测允许您根据一个值是否是指定的数据集的一员来过滤查询结果。

1. . .
2WHERE column_name IN (set_of_data)
3. . .

在WHERE关键字之后,有一个值表达式;再次,这个第一个值表达式通常是列的名称。接着是IN运算符,其次是数据集。

为了说明,运行下面的查询. 这将返回每个行的名称和gizmos列,其gizmos值是IN运算符后定义的集合成员:

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

只有销售团队的三名成员的deodads值分数等于此集中的任何值,因此只返回这些行:

1[secondary_label Output]
2+-------+---------+
3| name  | doodads |
4+-------+---------+
5| Tyler |      22 |
6| Lisa  |       2 |
7| Henry |       2 |
8+-------+---------+
93 rows in set (0.00 sec)

您可以通过使用子查询跟随IN运算符来编写一个集中的每个成员,而不是用子查询来编写一个集。 subquery - 也称为 nested 或 inner query - 是嵌入另一个SELECT语句中的一个条款中的一个SELECT语句的SELECT语句。

<$>[注意] 注意:当编写子查询以定义一组作为会员预测的一部分时,请确保您使用一个 scalar 子查询,或一个只返回单个列的子查询。

作为使用子查询来定义成员预兆中的集的示例,运行下列语句以创建一个名为example_set_table的表,该表只包含一个列。

1CREATE TABLE example_set_table (
2prime_numbers int
3);

然后用几个样本数据行加载此表. 按照表的单一列的名称,下面的输入语句将加载十行数据到表中,每行都包含前十个总数之一:

 1INSERT INTO example_set_table
 2VALUES
 3(2),
 4(3),
 5(5),
 6(7),
 7(11),
 8(13),
 9(17),
10(19),
11(23),
12(29);

然后运行以下查询. 这将返回从销售表的名称和小组件列的值,其WHERE条款会测试小组件列中的每个值是否位于由SELECT prime_numbers FROM example_set_table子查询衍生的集合中:

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

 1[secondary_label Output]
 2+-------+---------+
 3| name  | widgets |
 4+-------+---------+
 5| Blair |      19 |
 6| Lynn  |       7 |
 7| Lisa  |      17 |
 8| Maya  |       5 |
 9+-------+---------+
104 rows in set (0.00 sec)

由于仅有四名销售人员出售了等于存储在example_set_table中的任何一个主要数字的 widget 数,因此此查询只返回这些四行。

结论

通过遵循本指南,您了解如何使用 SQL 的BETWEEN运算符来测试列中的值是否属于特定范围;您还了解如何使用IN运算符来测试列中的值是否属于集合。

虽然这里显示的命令应该在大多数关系数据库上工作,但请注意,每个 SQL 数据库都使用其独特的语言实现,您应该参阅您的 DBMS 的官方文档,以获得对每个命令和其完整的选项的更完整的描述。

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