如何在 SQL 中使用 WHERE 子句

介绍

在 Structured Query Language (SQL) 语句中,‘WHERE’条款限制了该操作会影响哪个行。

本指南还将概述如何将多个搜索条件预测组合到一个单一的WHERE条款中来过滤数据以更为细微,以及如何使用NOT运算符来排除而不是包括符合特定搜索条件的行。

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

前提条件

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

运行 Ubuntu 20.04 的服务器,具有管理权限的非根用户和与 UFW 配置的防火墙,如我们在 Ubuntu 20.04 的初始服务器设置指南中所述。
MySQL 安装并在服务器上安全,如在 How To Install MySQL on Ubuntu 20.04中所述。本指南与新创建的用户进行验证,如在 Step 3中所述。

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

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

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

1[environment local]
2ssh sammy@your_server_ip

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

1mysql -u sammy -p

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

1CREATE DATABASE where_db;

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

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

若要選擇「where_db」資料庫,請執行下列「USE」聲明:

1USE where_db;

1[secondary_label Output]
2Database changed

在选择where_db后,创建表中。

要跟随本指南中使用的示例,想象一下,你在当地的高尔夫球场上运行一场高尔夫联赛。你决定跟踪有关联盟球员在他们参加的比赛中表现的信息。

您决定这个表需要六个列:

名称:每个高尔夫球手的名字,用 varchar 数据类型表示,最多 20 个字符
rounds_played:每个高尔夫球员所玩的完整轮数,用 int 数据类型表示
best:每个高尔夫球员在单个比赛中获得的最佳或最低分数,也表示为 int
worst:每个高尔夫球员在单个比赛中获得的最差或最高分数,再次表示为 int
average:他们所玩的轮子中每个高尔夫球员的平均分数。

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

1CREATE TABLE golfers (
2name varchar(20),
3rounds_played int,
4best int,
5worst int,
6average decimal (4,1),
7wins int
8);

然后加载一些样本数据的高尔夫球手表,执行以下INSERT INTO操作,添加7行数据,代表联盟的7名高尔夫球手:

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

请注意,这些行中的两个圆_玩值是NULL。为本教程的目的,假设这些高尔夫球手没有报告他们玩了多少轮,因此这些值被记录为NULL。

您也可能注意到每个高尔夫球手的最佳值小于他们的最坏值,这是因为在常见的高尔夫球规则中,高尔夫球员的分数取决于他们在赛道中的每一个洞中获得球的打击次数,而赢家是总打击次数最少的人。

有了它,您可以随时遵循本指南的其余部分,并开始学习如何在SQL中使用WHERE条款。

使用`Where`条款过滤数据

在 SQL 中,一个 statement 是发送到数据库系统的任何操作,该操作将执行某种类型的任务,例如创建表、插入或删除数据或更改列或表的结构。

正如介绍中提到的,‘WHERE’条款允许您过滤某些数据行,以免受到 SQL 操作的影响。

1SELECT columns_to_query
2FROM table_to_query
3WHERE search_condition;

搜索条件是由一个或多个 predicates 或表达式组成的,可以评估一个或多个值表达式,并返回一个结果为真实、假或未知。

在Where条款搜索条件中的预言可以采取许多形式,但它们通常遵循以下语法:

1. . .
2WHERE column_name OPERATOR value_expression
3. . .

在 SQL 中,一个 _value 表达式(有时也称为 _scalar 表达式)是返回单个值的任何表达式. 一个值表达式可以是一个字面值,如字符串或数值,一个数学表达式。

当运行包含WHERE条款的 SQL 查询时,数据库管理系统会将搜索条件应用到由FROM条款定义的逻辑表中的每个行,然后只会返回搜索条件中的每个预测值为真的行。

要说明这个想法,请运行以下查询. 这将返回golfers表的名称列中的每个值:

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

此查询包含一个WHERE条款,但而不是指定一个列名称,它使用(2 + 2)作为第一个值表达式,并测试它是否等于第二个值表达式4。

 1[secondary_label Output]
 2+---------+
 3| name    |
 4+---------+
 5| George  |
 6| Pat     |
 7| Grady   |
 8| Diane   |
 9| Calvin  |
10| Rose    |
11| Raymond |
12+---------+
137 rows in set (0.01 sec)

此WHERE条款并不非常有用,因为它总是评估为真,并总是返回表中的每个行。如前所述,您通常会在WHERE条款搜索条件中使用至少一个列名作为值表达式。运行查询时,数据库系统将对每个行单独应用搜索条件。

以下查询的WHERE条款将比上一个示例更具排他性的搜索条件应用于每个行,它将返回任何一行中win列值等于1的名称和赢值:

1SELECT name, wins
2FROM golfers
3WHERE wins = 1;

只有两名高尔夫球手赢得了完全一轮比赛,因此查询只返回这两行:

1[secondary_label Output]
2+---------+------+
3| name    | wins |
4+---------+------+
5| Diane   |    1 |
6| Raymond |    1 |
7+---------+------+
82 rows in set (0.01 sec)

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

SQL 标准定义了 18 种类型的序列,虽然并非所有类型的序列都包含在每个 SQL 实现中. 以下是五种最常用的序列类型,以及对每个序列和它们使用的操作员的简要解释:

比较

比较预测使用比较运算器将一个值(在查询中,通常是指定的列中的值)与另一个进行比较。

=:测试两个值是否等于

1SELECT name
2FROM golfers
3WHERE name = 'George';

1[secondary_label Output]
2+--------+
3| name   |
4+--------+
5| George |
6+--------+
71 row in set (0.00 sec)

<>:测试两个值是否不等于

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

 1[secondary_label Output]
 2+--------+------+
 3| name   | wins |
 4+--------+------+
 5| George |    3 |
 6| Pat    |    9 |
 7| Grady  |    0 |
 8| Calvin |    7 |
 9| Rose   |    4 |
10+--------+------+
115 rows in set (0.00 sec)

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

1SELECT name, wins
2FROM golfers
3WHERE wins < 1;

1[secondary_label Output]
2+-------+------+
3| name  | wins |
4+-------+------+
5| Grady |    0 |
6+-------+------+
71 row in set (0.00 sec)

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

1SELECT name, wins
2FROM golfers
3WHERE wins > 1;

 1[secondary_label Output]
 2+--------+------+
 3| name   | wins |
 4+--------+------+
 5| George |    3 |
 6| Pat    |    9 |
 7| Calvin |    7 |
 8| Rose   |    4 |
 9+--------+------+
104 rows in set (0.00 sec)

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

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

1[secondary_label Output]
2+---------+------+
3| name    | wins |
4+---------+------+
5| Grady   |    0 |
6| Diane   |    1 |
7| Raymond |    1 |
8+---------+------+
93 rows in set (0.00 sec)

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

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

 1[secondary_label Output]
 2+---------+------+
 3| name    | wins |
 4+---------+------+
 5| George  |    3 |
 6| Pat     |    9 |
 7| Diane   |    1 |
 8| Calvin  |    7 |
 9| Rose    |    4 |
10| Raymond |    1 |
11+---------+------+
126 rows in set (0.00 sec)

零

使用 IS NULL 运算符测试,预测给定的列中的值是否为 Null. 如果是,则预测值评估为 "true" 并将行包含在结果集中:

1SELECT name, rounds_played
2FROM golfers
3WHERE rounds_played IS NULL;

1[secondary_label Output]
2+--------+---------------+
3| name   | rounds_played |
4+--------+---------------+
5| Calvin |          NULL |
6| Rose   |          NULL |
7+--------+---------------+
82 rows in set (0.00 sec)

范围

范围预测使用BETWEEN运算符来测试指定的列值是否在两个值表达式之间:

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

 1[secondary_label Output]
 2+---------+------+
 3| name    | best |
 4+---------+------+
 5| George  |   68 |
 6| Diane   |   70 |
 7| Rose    |   69 |
 8| Raymond |   67 |
 9+---------+------+
104 rows in set (0.00 sec)

会员

会员预测使用IN运算符来测试一个值是否是特定集的成员:

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

1[secondary_label Output]
2+---------+------+
3| name    | best |
4+---------+------+
5| Pat     |   65 |
6| Rose    |   69 |
7| Raymond |   67 |
8+---------+------+
93 rows in set (0.00 sec)

模式比赛

模式匹配预测使用LIKE运算符来测试一个值是否匹配包含一个或多个 wildcard 字符,也称为 wildcards的字符串模式。

_:一个 underscore 代表一个未知字符

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

1[secondary_label Output]
2+--------+---------------+
3| name   | rounds_played |
4+--------+---------------+
5| George |            22 |
6| Pat    |            25 |
7| Diane  |            23 |
8+--------+---------------+
93 rows in set (0.00 sec)

%:百分比符号代表零或更多未知字符

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

1[secondary_label Output]
2+--------+---------------+
3| name   | rounds_played |
4+--------+---------------+
5| George |            22 |
6| Grady  |            11 |
7+--------+---------------+
82 rows in set (0.00 sec)

它超出了本教程的范围,以更详细地探讨这些预言类型中的每个类型. 但是,如果你想了解更多关于它们的信息,我们鼓励你查看以下指南:

将多个预言与`AND`和`OR`相结合

有些时候,你可能需要比单个搜索条件先定数的WHERE条款提供更多的细节过滤结果,另一方面,也可能有时候,在结果集中可以接受满足多个搜索条件之一的行。

要开始使用这些运算符,请运行以下查询,该查询将返回来自高尔夫表的名称,最佳,最差和平均列的值。

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

第一个预测测试是否每个行的最佳值小于70,而第二个测试测试是否每个行的最坏值小于96。

 1[secondary_label Output]
 2+---------+------+-------+---------+
 3| name    | best | worst | average |
 4+---------+------+-------+---------+
 5| Pat     |   65 |    74 |    68.7 |
 6| Calvin  |   63 |    76 |    68.5 |
 7| Rose    |   69 |    84 |    76.7 |
 8| Raymond |   67 |    92 |    81.3 |
 9+---------+------+-------+---------+
104 rows in set (0.00 sec)

接下来,运行下面的查询. 这与上一个示例相同,但它用 OR代替AND的运算符分离了两个预兆:

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

由于只有一个前提必须评估为真,以便返回一行,此结果集包含比上一个示例更多的两行:

 1[secondary_label Output]
 2+---------+------+-------+---------+
 3| name    | best | worst | average |
 4+---------+------+-------+---------+
 5| George  |   68 |   103 |    84.6 |
 6| Pat     |   65 |    74 |    68.7 |
 7| Diane   |   70 |    92 |    78.8 |
 8| Calvin  |   63 |    76 |    68.5 |
 9| Rose    |   69 |    84 |    76.7 |
10| Raymond |   67 |    92 |    81.3 |
11+---------+------+-------+---------+
126 rows in set (0.00 sec)

只要你将它们与正确的语法相结合,你可以在单个WHERE条款中包含你想要的尽可能多的预言,但是随着搜索条件变得更加复杂,你可能会很难预测他们将过滤哪些数据。

重要的是要注意,数据库系统通常优先考虑AND运算符,这意味着任何由AND运算符分开的预测符(或在两个以上预测符的情况下运算符)都被视为单一的、孤立的搜索条件,在WHERE条款中的任何其他预测符之前进行测试。

为了说明,运行以下查询,该查询将返回来自名称、平均值、最差和旋转_播放列的值,用于满足WHERE条款中定义的搜索条件的任何行:

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

此查询首先测试了由AND运算符分开的预测值(最差 < 95和rounds_played BETWEEN 19 AND 23)是否为当前迭代中的行评估为真。

 1[secondary_label Output]
 2+---------+---------+-------+---------------+
 3| name    | average | worst | rounds_played |
 4+---------+---------+-------+---------------+
 5| George  |    84.6 |   103 |            22 |
 6| Pat     |    68.7 |    74 |            25 |
 7| Diane   |    78.8 |    92 |            23 |
 8| Calvin  |    68.5 |    76 |          NULL |
 9| Rose    |    76.7 |    84 |          NULL |
10| Raymond |    81.3 |    92 |            18 |
11+---------+---------+-------+---------------+
126 rows in set (0.00 sec)

以下示例与前一种相同,但它将平均 < 85 和最差 < 95 序列,由一个 OR 运算符分开,包裹在序列中:

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

由於前兩個預言被關節包圍,接下來的「AND」運算器將其視為一個分散的搜尋條件,必須評估為「真」。如果這兩個預言──「平均 < 85」和「最壞 < 95」─評估為「虛假」,那麼整個搜尋條件評估為「虛假」,然後查詢會立即從設定的結果中放下行,然後繼續評估下一個。

但是,如果这些前两个预测中的任何一个评估为真,请求然后测试给定的高尔夫球员的轮_played值是否在19和23之间。

1[secondary_label Output]
2+--------+---------+-------+---------------+
3| name   | average | worst | rounds_played |
4+--------+---------+-------+---------------+
5| George |    84.6 |   103 |            22 |
6| Diane  |    78.8 |    92 |            23 |
7+--------+---------+-------+---------------+
82 rows in set (0.00 sec)

正如此输出所表明的,通过优先分配序列集并将它们包装为,否则相同的查询可以返回显著不同的结果集。

虽然并不总是需要这样做,但建议在在单个搜索条件中组合两个以上的序列时,始终包括序列,这样做可以帮助使查询更易于阅读和更容易理解。

排除`不`的结果

迄今为止,本指南的所有示例都专注于如何使用WHERE条款来编写查询,这些条款只包含在其结果集中满足指定的搜索条件的行,但是,您可以通过在WHERE条款中包含NOT运算符来编写排除特定行的查询。

范围、会员和模式匹配预测条款,其中包括NOT运算符通常遵循以下语法:

1. . .
2WHERE column_name NOT OPERATOR value_expression
3. . .

这将返回来自golfers表的名称列的值,但在其WHERE条款中的NOT运算符将导致DBMS排除任何匹配wildcard模式的行:

1SELECT name
2FROM golfers
3WHERE name NOT LIKE 'R%';

 1[secondary_label Output]
 2+--------+
 3| name   |
 4+--------+
 5| George |
 6| Pat    |
 7| Grady  |
 8| Diane  |
 9| Calvin |
10+--------+
115 rows in set (0.00 sec)

当您将NOT运算符添加到IS NULL预测时,情况会有所不同。在这种情况下,您将NOT列入IS和NULL之间,如下示例所示。

1SELECT name, rounds_played
2FROM golfers
3WHERE rounds_played IS NOT NULL;

 1[secondary_label Output]
 2+---------+---------------+
 3| name    | rounds_played |
 4+---------+---------------+
 5| George  |            22 |
 6| Pat     |            25 |
 7| Grady   |            11 |
 8| Diane   |            23 |
 9| Raymond |            18 |
10+---------+---------------+
115 rows in set (0.00 sec)

您还可以将NOT运算符立即放置在WHERE关键字后面,如果您基于它们是否符合多个搜索条件而排除行,如在本示例查询中,该查询返回了高尔夫球手的名称,平均值,最佳值和获胜值:

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

 1[secondary_label Output]
 2+---------+---------+------+------+
 3| name    | average | best | wins |
 4+---------+---------+------+------+
 5| George  |    84.6 |   68 |    3 |
 6| Pat     |    68.7 |   65 |    9 |
 7| Grady   |    97.6 |   78 |    0 |
 8| Diane   |    78.8 |   70 |    1 |
 9| Raymond |    81.3 |   67 |    1 |
10+---------+---------+------+------+
115 rows in set (0.00 sec)

Pat 的平均分数小于 80,她的最佳分数小于 70,但是,她的行仍然包含在结果集中,因为NOT运算器只否定了包裹在窗口中的搜索条件。

請記住,當您將「AND」或「OR」分開的多個預言包裝成關節時,SQL 會將這些預言優先,並將其視為單一孤立的搜尋條件。

您可以重写查询,以排除基于第三个序列的行,以及前两个通过将所有三个序列包装到序列中,如下:

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

 1[secondary_label Output]
 2+---------+---------+------+------+
 3| name    | average | best | wins |
 4+---------+---------+------+------+
 5| George  |    84.6 |   68 |    3 |
 6| Grady   |    97.6 |   78 |    0 |
 7| Diane   |    78.8 |   70 |    1 |
 8| Raymond |    81.3 |   67 |    1 |
 9+---------+---------+------+------+
104 rows in set (0.00 sec)

根据其 SQL 实现,您的数据库系统可能会认为查询的语法无效,如果您在比较运算器之前包含NOT。

1SELECT name
2FROM golfers
3WHERE name NOT = 'Grady';

在 MySQL 及其衍生品上,这会导致错误:

1[secondary_label Output]
2ERROR 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 运算符通常不会与比较运算符(=, <>, <, <=, >,和 >=)一起使用,因为你可以通过用另一个返回第一个排除的行来实现一个比较运算符的相反效果,例如,你可以用不等式运算符(<>)来代替等价运算符(=)。

结论

通过阅读本指南,您了解如何编写WHERE条款,以便查询只返回满足特定条件的行;您还了解如何将多个预言和搜索条件合并到单个查询中,以及如何使用NOT关键字将信息从结果集中排除。

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

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