MySQL / 读取和查询数据

如何在 MySQL 中过滤查询结果

内容

导言
使用 WHERE 子句定义匹配条件
使用 GROUP BY 子句汇总多条记录
使用 HAVING 子句过滤记录组
使用 LIMIT 子句设置最大记录数
结论

分享到

导言

SELECT 命令是从 MySQL 数据库检索数据的主要方式。虽然基本命令允许您指定要显示的列、要从中提取数据的表以及要使用的输出格式，但 SELECT 的大部分强大功能都来自其过滤结果的能力。

过滤查询允许您仅返回您感兴趣的结果，方法是提供记录必须匹配的特定条件。SQL 中有许多不同的过滤查询的方法，在本指南中，我们将介绍一些最常用的过滤选项，这些选项可用于您的 MySQL 数据库：WHERE、GROUP BY、HAVING 和 LIMIT。

通过熟悉这些可选子句，您可以学习构建针对正确数据的查询，即使在包含大量记录的数据库中也是如此。

使用 `WHERE` 子句定义匹配条件

指定数据要求的最灵活和最常见的方法之一是使用 WHERE 子句。WHERE 子句提供了一种指定记录必须满足的要求才能匹配查询的方法。如果记录不满足 WHERE 子句指定的所有条件，则它不会包含在查询结果中。

WHERE 子句的工作原理是指定针对每个候选数据行检查的布尔表达式。如果表达式的结果为 false，则该行将从结果中删除，并且不会返回或继续进行下一个处理阶段。如果表达式的结果为 true，则它满足搜索条件，并将继续作为候选行进行任何进一步处理。

WHERE 子句的基本语法如下所示

SELECT * FROM <table> WHERE <condition>;

<condition> 可以是任何产生布尔值的内容。MySQL 没有专用的内置布尔类型，而是使用 TINYINT 类型来表示布尔值。MySQL 将 BOOLEAN 和 BOOL 识别为 TINYINT 类型的别名。

由于这种实现，非零值被认为是 true，而 0 被认为是 false。为了处理相反的情况，常量 TRUE 是 1 的别名，而 FALSE 同样是 0 的别名。

条件通常使用以下一个或多个运算符构成

=：等于
>：大于
<：小于
>=：大于或等于
<=：小于或等于
<> 或 !=：不等于
<=>：NULL 安全的等于（如果两个值均为 NULL 则返回 1，如果只有一个值为 NULL 则返回 0）
AND：逻辑“与”运算符 — 连接两个条件，如果两个条件均为 TRUE 则返回 TRUE
OR：逻辑“或”运算符 — 连接两个条件，如果至少一个条件为 TRUE 则返回 TRUE
IN：值包含在后面的列表、系列或范围中
BETWEEN：值包含在后面的最小值和最大值之间的范围内，包括端点
IS NULL：如果值为 NULL 则匹配
NOT：否定后面的布尔值
EXISTS：后面的查询包含结果
LIKE：根据模式匹配（使用通配符 % 匹配 0 个或多个字符，_ 匹配单个字符）
REGEXP 或 REGEXP_LIKE()：使用正则表达式根据模式匹配
STRCMP：使用字典排序比较字符串以确定哪个值在前。

虽然以上列表代表了一些最常见的测试结构，但还有许多其他可以产生布尔结果的运算符可以与 WHERE 子句结合使用。

PRISMA.IO 相关内容

Prisma Client 支持按多个条件过滤。查看我们的关于过滤的文档以了解更多信息。

Prisma 是一个用于 Typescript 和 Node.js 的开源数据库工具包，旨在使应用程序开发人员在处理数据库时更高效和更有信心。

`WHERE` 的使用示例

最常见和最直接的检查之一是使用 = 运算符进行相等性检查。在这里，我们检查 customer 表中的每一行是否具有等于 Smith 的 last_name 值

SELECT * FROM customer WHERE last_name = 'Smith';

我们可以向其中添加其他条件，以使用逻辑运算符创建复合表达式。此示例使用 AND 子句添加针对 first_name 列的额外测试。有效行必须满足给定的两个条件

SELECT * FROM customer WHERE first_name = 'John' AND last_name = 'Smith';

同样，我们可以检查是否满足一系列条件中的任何一个。在这里，我们检查 address 表中的行，以查看 zip_code 值是否等于 60626，或者 neighborhood 列是否等于字符串 "Roger's Park"

SELECT * FROM address WHERE zip_code = '60626' OR neighborhood = "Roger's Park";

IN 运算符可以像括号中包装的多个值之间的比较。如果与任何给定值匹配，则表达式为 TRUE

SELECT * FROM customer WHERE last_name IN ('Smith', 'Johnson', 'Fredrich');

在这里，我们使用 LIKE 检查字符串模式。% 用作通配符，匹配零个或多个字符，因此 "Pete"、"Peter" 以及任何其他以 "Pete" 开头的字符串都将匹配

SELECT * FROM customer WHERE last_name LIKE 'Pete%';

我们可以使用 REGEXP 函数进行类似的搜索，以使用正则表达式检查匹配项。在这种情况下，我们检查 last_name 的值是否以 "d" 开头并包含子字符串 "on"，这将匹配诸如 "Dickson"、"Donald" 和 "Devon" 之类的名称

SELECT * FROM customer WHERE last_name REGEXP '^D.*on.*';

我们可以使用 BETWEEN 和 AND 运算符定义包含范围，以检查街道号码是否在 4000 号地址段内

SELECT * FROM address WHERE street_number BETWEEN 4000 AND 4999;

在这里，我们可以显示任何社会安全号码不是 9 位数字长的 customer 条目。我们使用 LENGTH() 函数获取字段中的位数，并使用 <> 检查不相等性

SELECT * FROM customer WHERE LENGTH(SSN) <> 9;

使用 `GROUP BY` 子句汇总多条记录

GROUP BY 子句是另一种非常常见的过滤结果的方法，它用单行表示多个结果。GROUP BY 子句的基本语法如下所示

SELECT <columns> FROM <table> GROUP BY <columns_to_group>;

当语句中添加 GROUP BY 子句时，它告诉 MySQL 为给定列或列的每个唯一值显示单行。这有一些重要的含义。

由于 GROUP BY 子句是将多行表示为单行的一种方式，因此 MySQL 只有在它可以计算出它要显示的每一列的值时才能执行查询。这意味着语句的 SELECT 部分标识的每一列都必须是

包含在 GROUP BY 子句中，以保证每一行都有唯一值
被抽象化以汇总每个组内的所有行

实际上，这意味着 SELECT 列表中未包含在 GROUP BY 子句中的任何列都必须使用聚合函数来为每个组的列生成单个结果。

PRISMA.IO 相关内容

如果您使用 Prisma Client 连接到数据库，则可以使用聚合来计算和汇总值。

Prisma 是一个用于 Typescript 和 Node.js 的开源数据库工具包，旨在使应用程序开发人员在处理数据库时更高效和更有信心。

`GROUP BY` 的使用示例

对于本节中的示例，假设我们有一个名为 pet 的表，我们已定义并填充如下

CREATE TABLE pet (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50),
    name VARCHAR(50),
    color VARCHAR(50),
    age INT
);

INSERT INTO pet (type, name, color, age) VALUES
('dog', 'Spot', 'brown', 3),
('dog', 'Rover', 'black', 7),
('dog', 'Sally', 'brown', 1),
('cat', 'Sabrina', 'black', 8),
('cat', 'Felix', 'white', 4),
('cat', 'Simon', 'orange', 8),
('rabbit', 'Buttons', 'grey', 4),
('rabbit', 'Bunny', 'brown', 8),
('rabbit', 'Briony', 'brown', 6);

GROUP BY 最简单的用法是显示单个列的唯一值范围。为此，请在 SELECT 和 GROUP BY 中使用相同的列。在这里，我们看到表中使用的所有颜色

SELECT color FROM pet GROUP BY color;

+--------+
  color  |
+--------+
  brown  |
  black  |
  white  |
  orange |
  grey   |
+--------+
5 rows in set (0.00 sec)

当您在 SELECT 列列表中超出单个列时，您必须将列添加到 GROUP BY 子句中，或者使用聚合函数为要表示的行组生成单个值。

在这里，我们将 type 添加到 GROUP BY 子句，这意味着每一行将表示 type 和 color 值的唯一组合。我们还添加了 age 列，由 avg() 函数汇总，以查找每个组的平均年龄

SELECT type, color, avg(age) AS average_age FROM pet GROUP BY type, color;

+--------+--------+-------------+
  type   | color  | average_age |
+--------+--------+-------------+
  dog    | brown  |      2.0000 |
  dog    | black  |      7.0000 |
  cat    | black  |      8.0000 |
  cat    | white  |      4.0000 |
  cat    | orange |      8.0000 |
  rabbit | grey   |      4.0000 |
  rabbit | brown  |      7.0000 |
+--------+--------+-------------+
7 rows in set (0.00 sec)

聚合函数在使用 GROUP BY 子句中的单个列时也能很好地工作。在这里，我们找到每种动物类型的平均年龄

SELECT type, avg(age) AS average_age FROM pet GROUP BY type;

+--------+-------------+
  type   | average_age |
+--------+-------------+
  dog    |      3.6667 |
  cat    |      6.6667 |
  rabbit |      6.0000 |
+--------+-------------+
3 rows in set (0.00 sec)

如果我们想显示每种动物类型中最老的，我们可以改为在 age 列上使用 max() 函数。GROUP BY 子句将结果折叠成与之前相同的行，但新函数会更改另一列中的结果

SELECT type, max(age) AS oldest FROM pet GROUP BY type;

+--------+--------+
  type   | oldest |
+--------+--------+
  dog    |      7 |
  cat    |      8 |
  rabbit |      8 |
+--------+--------+
3 rows in set (0.00 sec)

使用 `HAVING` 子句过滤记录组

GROUP BY 子句是一种通过将多条记录折叠成单个代表行来汇总数据的方法。但是，如果您想根据其他因素缩小这些组的范围怎么办？

HAVING 子句是 GROUP BY 子句的修饰符，允许您指定每个组必须满足的条件才能包含在结果中。

一般语法如下所示

SELECT <columns> FROM <table> GROUP BY <columns_to_group> HAVING <condition>

该操作与 WHERE 子句非常相似，不同之处在于 WHERE 过滤单个记录，而 HAVING 过滤记录组。

`HAVING` 的使用示例

使用我们在上一节中介绍的同一张表，我们可以演示 HAVING 子句的工作原理。

在这里，我们按 type 列中的唯一值对 pet 表的行进行分组，并找到 age 的最小值。HAVING 子句然后过滤结果，以删除年龄不大于 1 的任何组

SELECT type, min(age) AS youngest FROM pet GROUP BY type HAVING min(age) > 1;

+--------+----------+
  type   | youngest |
+--------+----------+
  cat    |        4 |
  rabbit |        4 |
+--------+----------+
2 rows in set (0.00 sec)

在此示例中，我们按颜色对 pet 中的行进行分组。然后，我们过滤仅代表单行的组。结果向我们显示了每个出现多次的颜色

SELECT color FROM pet GROUP BY color HAVING count(color) > 1;

+-------+
  color |
+-------+
  brown |
  black |
+-------+
2 rows in set (0.00 sec)

我们可以执行类似的查询来获取只有一只动物拥有的 type 和 color 组合

SELECT type, color FROM pet GROUP BY type, color HAVING count(color) = 1;

+--------+--------+
  type   | color  |
+--------+--------+
  dog    | black  |
  cat    | black  |
  cat    | white  |
  cat    | orange |
  rabbit | grey   |
+--------+--------+
5 rows in set (0.00 sec)

使用 `LIMIT` 子句设置最大记录数

LIMIT 子句提供了一种不同的方法来削减查询返回的记录。与根据行本身内的条件消除数据行不同，LIMIT 子句设置查询返回的最大记录数。

LIMIT 的基本语法如下所示

SELECT * FROM <table> LIMIT <num_rows> [OFFSET <num_rows_to_skip>];

在这里，<num_rows> 指示要从执行的查询中显示的最大行数。这通常与 ORDER BY 子句结合使用，以获取在特定列中具有最极端值的行。例如，要获得考试中得分最高的五名，用户可以 ORDER BY score 列，然后将结果 LIMIT 为 5。

虽然 LIMIT 默认从结果的顶部开始计数，但可选的 OFFSET 关键字可用于偏移其使用的起始位置。实际上，这允许您通过分页显示由 LIMIT 定义的结果数，然后将 LIMIT 数添加到 OFFSET 以检索下一页，从而分页浏览结果。

PRISMA.IO 相关内容

如果您使用 Prisma Client 连接到数据库，则可以使用分页来迭代结果。

Prisma 是一个用于 Typescript 和 Node.js 的开源数据库工具包，旨在使应用程序开发人员在处理数据库时更高效和更有信心。

`LIMIT` 的使用示例

我们将在本节的示例中使用前面提到的 pet 表。

如上所述，LIMIT 通常与 ORDER BY 子句结合使用，以显式定义行排序，然后再对适当的数量进行切片。在这里，我们根据 pet 条目的 age 从老到年轻进行排序。然后我们使用 LIMIT 显示最老的 5 只动物

SELECT * FROM pet ORDER BY age DESC LIMIT 5;

+----+--------+---------+--------+------+
  id | type   | name    | color  | age  |
+----+--------+---------+--------+------+
   4 | cat    | Sabrina | black  |    8 |
   6 | cat    | Simon   | orange |    8 |
   8 | rabbit | Bunny   | brown  |    8 |
   2 | dog    | Rover   | black  |    7 |
   9 | rabbit | Briony  | brown  |    6 |
+----+--------+---------+--------+------+
5 rows in set (0.00 sec)

如果我们需要表中任何单只 dog 的记录，我们可以构造如下查询。请记住，虽然结果可能难以预测，但这并不是随机选择，不应如此使用

SELECT * FROM pet WHERE type = 'dog' LIMIT 1;

+----+------+------+-------+------+
  id | type | name | color | age  |
+----+------+------+-------+------+
   1 | dog  | Spot | brown |    3 |
+----+------+------+-------+------+
1 row in set (0.00 sec)

我们可以使用 OFFSET 子句来分页浏览结果。我们包含一个 ORDER BY 子句来为结果定义特定顺序。

对于第一个查询，我们限制结果而不指定 OFFSET 以获得最年轻的前 3 个条目

SELECT * FROM pet ORDER BY age LIMIT 3;

+----+--------+---------+-------+------+
  id | type   | name    | color | age  |
+----+--------+---------+-------+------+
   3 | dog    | Sally   | brown |    1 |
   1 | dog    | Spot    | brown |    3 |
   7 | rabbit | Buttons | grey  |    4 |
+----+--------+---------+-------+------+
3 rows in set (0.00 sec)

要获得接下来最年轻的 3 个，我们可以将 LIMIT 中定义的数字添加到 OFFSET，以跳过我们已经检索到的结果

SELECT * FROM pet ORDER BY age LIMIT 3 OFFSET 3;

+----+--------+---------+-------+------+
  id | type   | name    | color | age  |
+----+--------+---------+-------+------+
   7 | rabbit | Buttons | grey  |    4 |
   9 | rabbit | Briony  | brown |    6 |
   2 | dog    | Rover   | black |    7 |
+----+--------+---------+-------+------+
3 rows in set (0.00 sec)

如果我们再次将 LIMIT 添加到 OFFSET，我们将获得接下来的 3 个结果

SELECT * FROM pet ORDER BY age LIMIT 3 OFFSET 6;

+----+--------+---------+--------+------+
  id | type   | name    | color  | age  |
+----+--------+---------+--------+------+
   4 | cat    | Sabrina | black  |    8 |
   6 | cat    | Simon   | orange |    8 |
   8 | rabbit | Bunny   | brown  |    8 |
+----+--------+---------+--------+------+
3 rows in set (0.00 sec)

这使我们可以从查询中以可管理的分块检索数据行。

结论

大多数情况下，当从 MySQL 表中检索数据时，您可能会应用过滤条件来挑选出合适的记录。无论是明确的 WHERE 子句，它使用 = 相等运算符匹配特定的 id，还是 GROUP BY 子句，它帮助您在单个值中汇总多条记录，过滤数据都是使用记录的正常部分。

了解如何使用这些可选子句来评估潜在数据是否符合您的条件并相应地塑造结果，这使 MySQL 可以为您完成选择工作。使用这些构造，您可以从大型的、半组织化的数据集合中提取有用的信息。

关于作者

Justin Ellingwood

自 2013 年以来，Justin 一直在撰写关于数据库、Linux、基础设施和开发者工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作，这对所有相关方来说都是一种解脱。

如何在 MySQL 中使用 `SELECT` 执行基本查询

使用 join 在 MySQL 中合并来自不同表的数据

在 GitHub 上编辑此页

MySQL / 读取和查询数据

如何在 MySQL 中过滤查询结果

内容

导言

使用 WHERE 子句定义匹配条件

WHERE 的使用示例

使用 GROUP BY 子句汇总多条记录

GROUP BY 的使用示例

使用 HAVING 子句过滤记录组

HAVING 的使用示例

使用 LIMIT 子句设置最大记录数

LIMIT 的使用示例

结论

Justin Ellingwood

Prisma 数据指南

使用 `WHERE` 子句定义匹配条件

`WHERE` 的使用示例

使用 `GROUP BY` 子句汇总多条记录

`GROUP BY` 的使用示例

使用 `HAVING` 子句过滤记录组

`HAVING` 的使用示例

使用 `LIMIT` 子句设置最大记录数

`LIMIT` 的使用示例