MySQL / 读取和查询数据

如何在 MySQL 中过滤查询结果

内容

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

分享到

简介

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

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

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

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

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

WHERE 子句通过指定布尔表达式来工作，这些表达式会针对每个候选数据行进行检查。如果表达式的结果为假，则该行将从结果中移除，并且不会返回或继续到下一个处理阶段。如果表达式的结果为真，则表示它满足搜索条件，并将作为候选行继续进行后续处理。

WHERE 子句的基本语法如下：

SELECT * FROM <table> WHERE <condition>;

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

由于这种实现，非零值被视为真，而 0 被视为假。为了处理相反的情况，常量 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 子句结合使用。

`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 子句中的任何列都必须使用聚合函数为每个组的列生成单个结果。

`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 子句结合使用，以获取某个列中具有最极端值的行。例如，要获取考试的五个最佳分数，用户可以按 score 列 ORDER BY，然后将结果 LIMIT 到 5。

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

`LIMIT` 的使用示例

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

如上所述，LIMIT 通常与 ORDER BY 子句结合使用，以在切片适当数量之前明确定义行的排序。在这里，我们根据 age 对 pet 条目进行排序，从最老到最年轻。然后我们使用 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 表中检索数据时，您很可能会应用过滤条件来挑选出适当的记录。无论是使用 = 相等运算符匹配特定 id 的明确 WHERE 子句，还是帮助您将多条记录汇总为单个值的 GROUP BY 子句，过滤数据都是处理记录的正常部分。

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

关于作者

Justin Ellingwood

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

上一个

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

下一个

如何在 MySQL 中使用联接组合来自不同表的数据

在 GitHub 上编辑此页面

MySQL / 读取和查询数据

如何在 MySQL 中过滤查询结果

内容

简介

使用 WHERE 子句定义匹配条件

WHERE 的使用示例

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

GROUP BY 的使用示例

使用 HAVING 子句过滤记录组

HAVING 的使用示例

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

LIMIT 的使用示例

总结