学习 MySQL 数据过滤与排序 | 动手实验

介绍

在本实验中，我们将探索在 MySQL 中过滤和排序数据的基本技巧。你将学习如何使用 WHERE 子句根据特定条件过滤数据，使用 ORDER BY 对结果进行排序，以及使用 LIMIT 子句限制输出。这些技能对于任何数据库用户来说都是基础，能够让你以所需的顺序检索到所需的数据。通过动手练习，你将获得这些关键数据库操作的实践经验，并理解如何在 MySQL 中有效地查询和组织数据。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sql(("SQL")) -.-> sql/BasicSQLCommandsGroup(["Basic SQL Commands"]) mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) sql(("SQL")) -.-> sql/DataManipulationandQueryingGroup(["Data Manipulation and Querying"]) sql(("SQL")) -.-> sql/AdvancedDataOperationsGroup(["Advanced Data Operations"]) sql/BasicSQLCommandsGroup -.-> sql/select("SELECT statements") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") sql/DataManipulationandQueryingGroup -.-> sql/where("WHERE clause") sql/DataManipulationandQueryingGroup -.-> sql/order_by("ORDER BY clause") sql/DataManipulationandQueryingGroup -.-> sql/like("LIKE operator") sql/AdvancedDataOperationsGroup -.-> sql/string_functions("String functions") sql/AdvancedDataOperationsGroup -.-> sql/numeric_functions("Numeric functions") subgraph Lab Skills sql/select -.-> lab-418305{{"MySQL 数据过滤与排序"}} mysql/select -.-> lab-418305{{"MySQL 数据过滤与排序"}} sql/where -.-> lab-418305{{"MySQL 数据过滤与排序"}} sql/order_by -.-> lab-418305{{"MySQL 数据过滤与排序"}} sql/like -.-> lab-418305{{"MySQL 数据过滤与排序"}} sql/string_functions -.-> lab-418305{{"MySQL 数据过滤与排序"}} sql/numeric_functions -.-> lab-418305{{"MySQL 数据过滤与排序"}} end

使用 WHERE 进行基本数据过滤

在这一步中，我们将学习如何使用 WHERE 子句根据特定条件过滤数据。WHERE 子句是 SQL 中最重要的功能之一，它允许你仅检索符合条件的数据。

首先，让我们连接到 MySQL 并选择我们的数据库：

sudo mysql -u root

连接成功后：

USE bookstore;

简单的比较操作

让我们从基本的比较运算符开始：

-- 价格超过 $40 的书籍
SELECT title, price
FROM books
WHERE price > 40;

你应该会看到类似以下的输出：

+-------------------------+-------+
| title                   | price |
+-------------------------+-------+
| The MySQL Guide         | 45.99 |
| Advanced Database Concepts | 54.99 |
| Database Administration | 49.99 |
| The Perfect Index       | 42.99 |
+-------------------------+-------+

常见的比较运算符包括：

=（等于）
<> 或 !=（不等于）
>（大于）
<（小于）
>=（大于或等于）
<=（小于或等于）

让我们尝试另一个例子，查找特定年份出版的书籍：

SELECT title, author, publication_year
FROM books
WHERE publication_year = 2023;

使用 AND 和 OR

我们可以使用 AND 和 OR 组合多个条件：

-- 2023 年出版的技术类书籍
SELECT title, genre, publication_year, price
FROM books
WHERE genre = 'Technical'
AND publication_year = 2023;

让我们尝试使用 OR 查找非常新或非常昂贵的书籍：

SELECT title, publication_year, price
FROM books
WHERE publication_year = 2023
OR price >= 50;

处理 NULL 和布尔值

查找缺货的书籍：

SELECT title, price, in_stock
FROM books
WHERE in_stock = FALSE;

使用 LIKE 进行模式匹配

在这一步中，我们将探索使用 LIKE 运算符进行模式匹配，它允许我们在文本数据中搜索特定模式。当你不知道要查找的确切文本时，这尤其有用。

LIKE 运算符使用两个特殊字符：

% 表示零个或多个字符
_ 表示恰好一个字符

让我们尝试一些例子：

-- 查找标题中包含 "SQL" 的书籍
SELECT title, author
FROM books
WHERE title LIKE '%SQL%';

此查询会找到标题中任意位置包含 "SQL" 的书籍。你应该会看到类似以下的输出：

+------------------+----------------+
| title            | author         |
+------------------+----------------+
| SQL for Beginners| Sarah Johnson  |
| SQL Mystery Tales| Jennifer White |
+------------------+----------------+

让我们尝试更多的模式匹配：

-- 查找以 "The" 开头的书籍
SELECT title, author
FROM books
WHERE title LIKE 'The%';

-- 查找标题中包含 "Database" 的书籍
SELECT title, author, price
FROM books
WHERE title LIKE '%Database%';

请注意，在 MySQL 中，LIKE 默认是大小写不敏感的。如果你需要区分大小写的匹配，可以使用 BINARY：

-- 区分大小写的搜索
SELECT title, author
FROM books
WHERE title LIKE BINARY 'The%';

使用 ORDER BY 对结果进行排序

在这一步中，我们将学习如何使用 ORDER BY 子句对查询结果进行排序。这允许我们以升序（ASC）或降序（DESC）的方式对数据进行有意义的排列。

单列排序

让我们从按价格进行基本排序开始：

-- 按价格排序（默认升序）
SELECT title, price
FROM books
ORDER BY price;

你会看到书籍按价格从低到高排列。要反转顺序：

-- 按价格降序排序
SELECT title, price
FROM books
ORDER BY price DESC;

输出将首先显示最昂贵的书籍：

+---------------------------+-------+
| title                     | price |
+---------------------------+-------+
| Advanced Database Concepts| 54.99 |
| Database Administration   | 49.99 |
| The MySQL Guide           | 45.99 |
| The Perfect Index         | 42.99 |
...
+---------------------------+-------+

多列排序

我们可以按多列排序，这在第一排序列有重复值时非常有用：

-- 按类型排序，然后在每个类型中按价格排序
SELECT title, genre, price
FROM books
ORDER BY genre, price DESC;

你可以混合使用升序和降序排序：

-- 按类型升序排序，按价格降序排序
SELECT title, genre, price
FROM books
ORDER BY genre ASC, price DESC;

使用 LIMIT 限制结果数量

在这一步中，我们将学习如何使用 LIMIT 子句来限制查询返回的行数。这在处理大型数据集或仅需查看少量示例时特别有用。

基本 LIMIT 用法

查看最贵的 3 本书：

SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3;

你应该只会看到三行数据：

+---------------------------+-------+
| title                     | price |
+---------------------------+-------+
| Advanced Database Concepts| 54.99 |
| Database Administration   | 49.99 |
| The MySQL Guide           | 45.99 |
+---------------------------+-------+

结合 OFFSET 使用 LIMIT

LIMIT 可以接受两个数字：偏移量（跳过多少行）和数量（返回多少行）：

-- 跳过前 3 本书，显示接下来的 3 本
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3, 3;

这将显示第 4、5 和 6 本最贵的书籍。

一个常见的用例是分页。例如，显示“第 2 页”，每页 5 项：

SELECT title, author, price
FROM books
ORDER BY title
LIMIT 5, 5;

这将跳过前 5 个结果（第 1 页）并显示接下来的 5 个结果（第 2 页）。

综合运用

让我们将所学内容结合起来，创建一个更复杂的查询：

SELECT title, author, price
FROM books
WHERE genre = 'Technical'
AND price > 30
ORDER BY price DESC
LIMIT 3;

此查询：

过滤价格超过 $30 的技术类书籍
按价格降序排序
仅显示前 3 个结果

总结

在本实验中，我们学习了在 MySQL 中过滤和排序数据的基本技巧：

使用 WHERE 子句结合比较和逻辑运算符来过滤数据
使用 LIKE 运算符进行文本搜索的模式匹配
使用 ORDER BY 对单列和多列进行排序
使用 LIMIT 和 OFFSET 限制结果集

这些技能构成了在 MySQL 中有效检索和组织数据的基础。