如何在 SQL 中使用 WHERE 子句

介绍

在结构化查询语言(SQL)语句中，WHERE子句限制给定操作将影响的行。他们通过定义特定条件（称为搜索条件）来实现这一点，每一行必须满足这些条件才能受到操作的影响。

本指南将介绍WHERE子句中使用的一般语法。它还将概述如何在单个WHERE子句中组合多个搜索条件谓词以更精细地过滤数据，以及如何使用NOT运算符排除而不是包含满足给定搜索条件的行。

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

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

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

  • ssh sammy@your_server_ip

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

  • mysql -u sammy -p

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

  • CREATE DATABASE where_db;

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

Output
Query OK, 1 row affected (0.01 sec)

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

  • USE where_db;

Output
Database changed

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

为了跟随本指南中使用的示例，假设您在当地的高尔夫球场举办高尔夫联赛。您决定跟踪有关联盟球员在参加的郊游中的个人表现的信息。为此，您决定将信息存储在 SQL 数据库中。

您决定此表需要六列：

  • name: 每个高尔夫球手的名字，使用varchar最多 20 个字符的数据类型表示
  • rounds_played：每个高尔夫球手打完的完整回合总数，用int数据类型表示
  • best：每位高尔夫球手在单次郊游中的最佳或最低得分，也表示为 int
  • worst：每个高尔夫球手在单次郊游中的最差或最高得分，再次表示为 int
  • average：每个高尔夫球手在他们所玩的回合中得分的近似平均值。此列将保存decimal类型的值，限制为最多 4 位数字，其中一位位于小数点右侧
  • wins：每个高尔夫球手在比赛组中的每个人中得分最低的回合数，用int类型表示

运行以下CREATE TABLE语句以创建一个名为的表golfers，该表包含这六列：

  • CREATE TABLE golfers (

  • name varchar(20),

  • rounds_played int,

  • best int,

  • worst int,

  • average decimal (4,1),

  • wins int

  • );

然后golfers用一些示例数据加载表。运行以下INSERT INTO操作以添加代表联盟七名高尔夫球手的七行数据：

  • INSERT INTO golfers

  • VALUES

  • ('George', 22, 68, 103, 84.6, 3),

  • ('Pat', 25, 65, 74, 68.7, 9),

  • ('Grady', 11, 78, 118, 97.6, 0),

  • ('Diane', 23, 70, 92, 78.8, 1),

  • ('Calvin', NULL, 63, 76, 68.5, 7),

  • ('Rose', NULL, 69, 84, 76.7, 4),

  • ('Raymond', 18, 67, 92, 81.3, 1);

请注意，其中两行的rounds_played值为NULL。出于本教程的目的，假设这些高尔夫球手没有报告他们打了多少回合，因此这些值被记录为NULL。

您可能还会注意到，每个高尔夫球手的best价值都小于他们的worst. 这是因为，在常见的高尔夫规则中，高尔夫球手的得分取决于他们将球打入球场中每个洞所需的击球次数，获胜者是总击球次数最少的人。因此，与大多数其他运动不同，高尔夫球手的最佳成绩将低于其最差成绩。

有了这个，您就可以按照指南的其余部分并开始学习如何WHERE在 SQL 中使用子句。

用WHERE子句过滤数据

在 SQL 中，语句是发送到数据库系统的任何操作，这些操作将执行某种任务，例如创建表、插入或删除数据或更改列或表的结构。SQL 语句由各种子句组成，这些子句由某些关键字及其所需的信息组成。

正如介绍中提到的，WHERE子句允许您过滤掉某些数据行，以免受到 SQL 操作的影响。在查询中，WHERE子句在子句之后FROM，如下例所示：

  • SELECT columns_to_query

  • FROM table_to_query

  • WHERE search_condition;

继WHERE关键词是搜索条件。搜索条件是一组一个或多个谓词或表达式，这些表达式可以计算一个或多个值表达式并返回“真”、“假”或“未知”的结果。请注意，在搜索条件仅包含单个谓词的情况下，术语“搜索条件”和“谓词”是同义词。

WHERE子句搜索条件中的谓词可以采用多种形式，但它们通常遵循以下语法：

. . .
WHERE column_name OPERATOR value_expression
. . .

在 SQL 中，值表达式（有时也称为标量表达式）是任何将返回单个值的表达式。值表达式可以是字面值，如字符串或数值、数学表达式。但是，大多数情况下，WHERE子句搜索条件中的至少一个值表达式是列名。

在运行包含WHERE子句的SQL 查询时，数据库管理系统会将搜索条件应用于该FROM子句定义的逻辑表中的每一行。然后它将只返回搜索条件中的每个谓词评估为“真”的行。

为了说明这个想法，请运行以下查询。这将返回golfers表name列中的每个值：

  • SELECT name

  • FROM golfers

  • WHERE (2 + 2) = 4;

此查询包含一个 WHERE子句，但它没有指定列名，而是(2 + 2)用作第一个值表达式并测试它是否等于第二个值表达式4. 因为(2 + 2)是总是相等4，该搜索条件计算为“真”的每一行。因此，每一行都在结果集中返回：

Output
+---------+
| name    |
+---------+
| George  |
| Pat     |
| Grady   |
| Diane   |
| Calvin  |
| Rose    |
| Raymond |
+---------+
7 rows in set (0.01 sec)

这个WHERE子句不是很有用，因为它总是评估为“true”并且总是返回表中的每一行。如前所述，您通常会在WHERE子句搜索条件中使用至少一个列名作为值表达式。运行查询时，数据库系统将按顺序将搜索条件分别应用于每一行。通过提供列名作为搜索条件中的值表达式，您告诉 DBMS 使用该列中每一行的值作为该行搜索条件迭代的值表达式。

以下查询的WHERE子句对每一行应用比前一个示例更排他的搜索条件。它将从列值等于的任何行返回name和wins值：wins1

  • SELECT name, wins

  • FROM golfers

  • WHERE wins = 1;

只有两名高尔夫球手刚好赢得一轮比赛，因此查询仅返回这两行：

Output
+---------+------+
| name    | wins |
+---------+------+
| Diane   |    1 |
| Raymond |    1 |
+---------+------+
2 rows in set (0.01 sec)

前面的示例使用等号 ( =) 来测试两个值表达式是否等效，但您使用的运算符取决于您要用于过滤结果集的谓词类型。

SQL 标准定义了 18 种类型的谓词，尽管并非所有这些都包含在每个 SQL 实现中。以下是五种最常用的谓词类型，以及对每种类型及其使用的运算符的简要说明：

比较

比较谓词使用比较运算符将一个值（在查询中，通常是指定列中的值）与另一个值进行比较。六个比较运算符是：

  • =: 测试两个值是否相等
  • SELECT name

  • FROM golfers

  • WHERE name = 'George';

Output
+--------+
| name   |
+--------+
| George |
+--------+
1 row in set (0.00 sec)
  • <>: 测试两个值是否不相等
  • SELECT name, wins

  • FROM golfers

  • WHERE wins <> 1;

Output
+--------+------+
| name   | wins |
+--------+------+
| George |    3 |
| Pat    |    9 |
| Grady  |    0 |
| Calvin |    7 |
| Rose   |    4 |
+--------+------+
5 rows in set (0.00 sec)
  • <: 测试第一个值是否小于第二个
  • SELECT name, wins

  • FROM golfers

  • WHERE wins < 1;

Output
+-------+------+
| name  | wins |
+-------+------+
| Grady |    0 |
+-------+------+
1 row in set (0.00 sec)
  • >: 测试第一个值是否大于第二个
  • SELECT name, wins

  • FROM golfers

  • WHERE wins > 1;

Output
+--------+------+
| name   | wins |
+--------+------+
| George |    3 |
| Pat    |    9 |
| Calvin |    7 |
| Rose   |    4 |
+--------+------+
4 rows in set (0.00 sec)
  • <=: 测试第一个值是否小于或等于第二个
  • SELECT name, wins

  • FROM golfers

  • WHERE wins <= 1;

Output
+---------+------+
| name    | wins |
+---------+------+
| Grady   |    0 |
| Diane   |    1 |
| Raymond |    1 |
+---------+------+
3 rows in set (0.00 sec)
  • >=: 测试第一个值是否大于或等于第二个
  • SELECT name, wins

  • FROM golfers

  • WHERE wins >= 1;

Output
+---------+------+
| name    | wins |
+---------+------+
| George  |    3 |
| Pat     |    9 |
| Diane   |    1 |
| Calvin  |    7 |
| Rose    |    4 |
| Raymond |    1 |
+---------+------+
6 rows in set (0.00 sec)

空值

使用IS NULL运算符的谓词测试给定列中的值是否为 Null。如果是，则谓词评估为“true”并且该行包含在结果集中：

  • SELECT name, rounds_played

  • FROM golfers

  • WHERE rounds_played IS NULL;

Output
+--------+---------------+
| name   | rounds_played |
+--------+---------------+
| Calvin |          NULL |
| Rose   |          NULL |
+--------+---------------+
2 rows in set (0.00 sec)

范围

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

  • SELECT name, best

  • FROM golfers

  • WHERE best BETWEEN 67 AND 73;

Output
+---------+------+
| name    | best |
+---------+------+
| George  |   68 |
| Diane   |   70 |
| Rose    |   69 |
| Raymond |   67 |
+---------+------+
4 rows in set (0.00 sec)

会员资格

成员资格谓词使用IN运算符来测试一个值是否是给定集合的成员：

  • SELECT name, best

  • FROM golfers

  • WHERE best IN (65, 67, 69, 71);

Output
+---------+------+
| name    | best |
+---------+------+
| Pat     |   65 |
| Rose    |   69 |
| Raymond |   67 |
+---------+------+
3 rows in set (0.00 sec)

模式匹配

模式匹配谓词使用LIKE运算符来测试值是否与包含一个或多个通配符（也称为通配符）的字符串模式匹配。SQL 定义了两个通配符，%以及_：

  • _: 下划线代表单个未知字符
  • SELECT name, rounds_played

  • FROM golfers

  • WHERE rounds_played LIKE '2_';

Output
+--------+---------------+
| name   | rounds_played |
+--------+---------------+
| George |            22 |
| Pat    |            25 |
| Diane  |            23 |
+--------+---------------+
3 rows in set (0.00 sec)
  • %: 百分号代表零个或多个未知字符
  • SELECT name, rounds_played

  • FROM golfers

  • WHERE name LIKE 'G%';

Output
+--------+---------------+
| name   | rounds_played |
+--------+---------------+
| George |            22 |
| Grady  |            11 |
+--------+---------------+
2 rows in set (0.00 sec)

更详细地介绍每个谓词类型超出了本教程的范围。不过，如果您想了解更多关于它们的信息，我们鼓励您查看以下指南：

结合多个谓词AND和OR

有时您可能需要比WHERE具有单个搜索条件谓词的子句所能提供的更细粒度的过滤结果。另一方面，有时满足多个搜索条件之一的行在结果集中是可以接受的。在这种情况下，您可以编写WHERE包含多个分别带有ANDorOR运算符的谓词的子句。

要开始使用这些运营商，运行以下查询从返回值golfers表的name，best，worst，和average列。它的WHERE子句包括两个谓词，用 分隔AND：

  • SELECT name, best, worst, average

  • FROM golfers

  • WHERE best < 70 AND worst < 96;

第一个谓词测试每行的best值是否小于 70，而第二个谓词测试每行的值是否worst小于 96。如果任一测试对一行的评估结果为“false”，则该行将不会在结果集中返回：

Output
+---------+------+-------+---------+
| name    | best | worst | average |
+---------+------+-------+---------+
| Pat     |   65 |    74 |    68.7 |
| Calvin  |   63 |    76 |    68.5 |
| Rose    |   69 |    84 |    76.7 |
| Raymond |   67 |    92 |    81.3 |
+---------+------+-------+---------+
4 rows in set (0.00 sec)

接下来，运行以下查询。这与前面的示例相同，不同之处在于它使用OR运算符而不是分隔两个谓词AND：

  • SELECT name, best, worst, average

  • FROM golfers

  • WHERE best < 70 OR worst < 96;

因为对于要返回的行，只有一个谓词必须评估为“真”，所以此结果集比上一个示例多包含两行： 

Output
+---------+------+-------+---------+
| name    | best | worst | average |
+---------+------+-------+---------+
| George  |   68 |   103 |    84.6 |
| Pat     |   65 |    74 |    68.7 |
| Diane   |   70 |    92 |    78.8 |
| Calvin  |   63 |    76 |    68.5 |
| Rose    |   69 |    84 |    76.7 |
| Raymond |   67 |    92 |    81.3 |
+---------+------+-------+---------+
6 rows in set (0.00 sec)

您可以在单个WHERE子句中包含任意数量的谓词，只要将它们与正确的语法组合在一起即可。但是，随着您的搜索条件变得越来越复杂，预测它们将过滤哪些数据会变得很困难。

需要注意的是，数据库系统通常优先考虑AND操作员。这意味着由AND运算符分隔的任何谓词（或在两个以上谓词的情况下的运算符）都被视为单个独立的搜索条件，在WHERE子句中的任何其他谓词之前进行测试。

为了说明，运行以下查询，从返回值name，average，worst和rounds_played列符合定义搜索条件的列WHERE条款：

  • SELECT name, average, worst, rounds_played

  • FROM golfers 

  • WHERE average < 85 OR worst < 95 AND rounds_played BETWEEN 19 AND 23;

此查询首先测试由AND运算符分隔的谓词–worst < 95和rounds_played BETWEEN 19 AND 23– 对于当前迭代中的行都评估为“真”。如果是这样，那么该行将出现在结果集中。但是，如果任一评估为“false”，则查询将检查当前行的average值是否小于 85。如果是，则将返回该行：

Output
+---------+---------+-------+---------------+
| name    | average | worst | rounds_played |
+---------+---------+-------+---------------+
| George  |    84.6 |   103 |            22 |
| Pat     |    68.7 |    74 |            25 |
| Diane   |    78.8 |    92 |            23 |
| Calvin  |    68.5 |    76 |          NULL |
| Rose    |    76.7 |    84 |          NULL |
| Raymond |    81.3 |    92 |            18 |
+---------+---------+-------+---------------+
6 rows in set (0.00 sec)

您可以通过将一组两个或多个谓词括在括号中来确定它们的优先级。下面的示例与前一个示例相同，但它将average < 85andworst < 95谓词包装OR在括号中，由运算符分隔：

  • SELECT name, average, worst, rounds_played

  • FROM golfers

  • WHERE (average < 85 OR worst < 95) AND rounds_played BETWEEN 19 AND 23;

因为前两个谓词被括号包围，所以后续AND运算符将它们视为离散搜索条件，必须评估为“真”。如果这两个谓词 –average < 85和worst < 95– 评估为“假”，则整个搜索条件评估为“假”，查询立即从结果集中删除该行，然后继续评估下一个。

但是，如果前两个谓词中的任何一个评估为“true”，则查询将测试给定的高尔夫球手的rounds_played值是否介于 19 和 23 之间。如果是，该行将在结果集中返回：

Output
+--------+---------+-------+---------------+
| name   | average | worst | rounds_played |
+--------+---------+-------+---------------+
| George |    84.6 |   103 |            22 |
| Diane  |    78.8 |    92 |            23 |
+--------+---------+-------+---------------+
2 rows in set (0.00 sec)

正如此输出所示，通过对谓词集进行优先级排序并将它们括在括号中，否则相同的查询可能会返回显着不同的结果集。

尽管并非总是需要这样做，但建议您在单个搜索条件中组合两个以上的谓词时始终包含括号。这样做有助于使查询更具可读性和更易于理解。

排除结果 NOT

到目前为止，本指南的所有示例都集中在如何使用WHERE子句编写查询，这些子句仅包含在其结果集中满足指定搜索条件的行。但是，您可以通过NOT在WHERE子句中包含运算符来编写排除特定行的查询。

包含NOT运算符的范围、成员资格和模式匹配谓词子句通常遵循以下语法：

  • . . .

  • WHERE column_name NOT OPERATOR value_expression

  • . . .

为了进行说明，请运行以下查询。这将从golfers表的name列中返回值，但NOT其WHERE子句中的运算符将导致 DBMS 排除任何与通配符模式匹配的行：

  • SELECT name

  • FROM golfers

  • WHERE name NOT LIKE 'R%';

Output
+--------+
| name   |
+--------+
| George |
| Pat    |
| Grady  |
| Diane  |
| Calvin |
+--------+
5 rows in set (0.00 sec)

将NOT运算符添加到IS NULL谓词时，情况会有所不同。在这种情况下，您将放置NOT在IS和之间NULL，如下例所示。此查询返回值不为 Null的每个高尔夫球手的name和rounds_played值rounds_played：

  • SELECT name, rounds_played

  • FROM golfers

  • WHERE rounds_played IS NOT NULL;

Output
+---------+---------------+
| name    | rounds_played |
+---------+---------------+
| George  |            22 |
| Pat     |            25 |
| Grady   |            11 |
| Diane   |            23 |
| Raymond |            18 |
+---------+---------------+
5 rows in set (0.00 sec)

您也可以将NOT运算符紧跟在WHERE关键字之后。如果您是根据它们是否满足多个搜索条件，在这个例子中查询返回的球手不包括行。这是非常有用的name，average，best，和wins值：

  • SELECT name, average, best, wins

  • FROM golfers

  • WHERE NOT (average < 80 AND best < 70) OR wins = 9;

Output
+---------+---------+------+------+
| name    | average | best | wins |
+---------+---------+------+------+
| George  |    84.6 |   68 |    3 |
| Pat     |    68.7 |   65 |    9 |
| Grady   |    97.6 |   78 |    0 |
| Diane   |    78.8 |   70 |    1 |
| Raymond |    81.3 |   67 |    1 |
+---------+---------+------+------+
5 rows in set (0.00 sec)

请注意此结果集的第二行。Pat 的average分数小于 80，她的best分数小于 70。但是，她的行仍然包含在结果集中，因为NOT运算符只对括号中的搜索条件进行了否定。

回想一下，当您将多个谓词用括号分隔AND或OR括在括号中时，SQL 将优先考虑这些谓词并将它们视为单个孤立的搜索条件。因此，NOT运算符仅根据前两个谓词average < 80和排除行best < 70。但它包括基于第三个谓词的行wins = 9。

您可以重写查询以通过将所有三个谓词括在括号中来根据第三个谓词和前两个谓词排除行，如下所示：

  • SELECT name, average, best, wins

  • FROM golfers

  • WHERE NOT ((average < 80 AND best < 70) OR wins = 9);

Output
+---------+---------+------+------+
| name    | average | best | wins |
+---------+---------+------+------+
| George  |    84.6 |   68 |    3 |
| Grady   |    97.6 |   78 |    0 |
| Diane   |    78.8 |   70 |    1 |
| Raymond |    81.3 |   67 |    1 |
+---------+---------+------+------+
4 rows in set (0.00 sec)

根据其 SQL 实现，如果您NOT在比较运算符之前包含查询，则您的数据库系统可能会认为查询的语法无效。例如，尝试运行此查询：

  • SELECT name

  • FROM golfers

  • WHERE name NOT = 'Grady';

在 MySQL 及其衍生产品上，这将导致错误：

Output
ERROR 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运算符通常不与比较运算符 ( =, <>, <, <=, >, and >=) 一起使用，因为您可以通过将一个比较运算符替换为另一个将返回第一个行的行来实现与一个比较运算符相反的效果会排除。例如，您可以将等价运算符 ( =) 替换为不等价运算符 ( <>)。

结论

通过阅读本指南，您学习了如何编写WHERE子句，以便查询仅返回满足指定条件的行。您还学习了如何在单个查询中组合多个谓词和搜索条件，以及如何使用NOT关键字从结果集中排除信息。

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

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

觉得文章有用？

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