MySQL修炼手册2：MySQL基础查询语句解析

写在开头

MySQL中的SELECT语句是数据库查询的核心，通过合理运用其各种特性，我们能够灵活地获取所需的数据。在本篇文章中，我们将逐步介绍SELECT语句的基础用法以及条件筛选、排序与限制等高级用法，帮助读者深入掌握MySQL的查询操作。

首先，我们创建一个名为 fruits 的测试表：

-- 创建测试表
CREATE TABLE fruits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    color VARCHAR(20),
    taste VARCHAR(20),
    price DECIMAL(8, 2)
);

-- 插入测试数据
INSERT INTO fruits (name, color, taste, price) VALUES
('Apple', 'Red', 'Sweet', 2.5),
('Banana', 'Yellow', 'Sweet', 1.8),
('Orange', 'Orange', 'Citrus', 2.2),
('Grapes', 'Purple', 'Sweet', 3.5),
('Kiwi', 'Brown', 'Tart', 2.8);

1. SELECT语句基础

1.1 查询所有列与指定列

查询所有列

SELECT * FROM fruits;

这将检索出 fruits 表中所有列的数据。

查询指定列

SELECT name, color FROM fruits;

上述语句将只返回 name 和 color 两列的数据。

1.2 使用别名

为列使用别名

SELECT name AS fruit_name, quantity AS amount FROM fruits;

通过别名，您可以将列重新命名为新的标识符，例如将 name 列命名为 fruit_name，quantity 列命名为 amount。

1.3 DISTINCT关键字的应用

消除重复行

SELECT DISTINCT color FROM fruits;

此语句将返回 color 列中唯一的值，消除了重复的行。

1.4 使用计算字段

使用计算字段

SELECT name, quantity * 2 AS doubled_quantity FROM fruits;

在 SELECT 语句中，您可以对数据进行计算并返回计算结果，例如将 quantity 列的值乘以 2 并将其作为 doubled_quantity 返回。

2 条件筛选：使用WHERE子句进行数据过滤

2.1 WHERE子句的基本使用

-- WHERE子句的基本使用
SELECT name, taste FROM fruits WHERE color = 'Red';

-- 多个条件的WHERE子句
SELECT name, color, taste FROM fruits WHERE color = 'Red' AND price > 2.0;

2.2 比较运算符与逻辑运算符

-- 比较运算符与逻辑运算符
SELECT name, color FROM fruits WHERE taste = 'Sweet' AND color <> 'Red';

-- 使用OR条件
SELECT name, color FROM fruits WHERE taste = 'Sweet' OR price < 2.0;

2.3 BETWEEN、IN、LIKE等条件筛选方法

-- BETWEEN条件筛选
SELECT name, color FROM fruits WHERE id BETWEEN 2 AND 4;

-- IN条件筛选
SELECT name, color FROM fruits WHERE taste IN ('Sweet', 'Tart');

-- LIKE条件筛选
SELECT name, color FROM fruits WHERE name LIKE 'A%';

-- 使用通配符%
SELECT name, color FROM fruits WHERE name LIKE '%a%';

-- 使用通配符_
SELECT name, color FROM fruits WHERE name LIKE 'Ora_ _ e';

3. 排序与限制：ORDER BY与LIMIT的妙用

3.1 ORDER BY子句的使用

3.1.1 单一排序

ORDER BY 子句允许您按照一个或多个列对查询结果进行排序。默认情况下，排序是按升序进行的（从最小到最大）。语法如下：

SELECT * FROM fruits ORDER BY quantity;

上述查询将按照 quantity 列的升序（从最小到最大）对结果进行排序。如果您想按照降序排序（从最大到最小），可以使用 DESC 关键字：

SELECT * FROM fruits ORDER BY quantity DESC;

3.1.2 复合排序

除了单一列的排序外，您也可以通过指定多个列进行复合排序，例如：

SELECT * FROM fruits ORDER BY color, quantity DESC;

上述查询首先按照 color 列进行升序排序，然后对于具有相同 color 值的行，按照 quantity 列进行降序排序。

3.1.3 null值排序

升序排序中的NULL值

默认情况下，当您使用 ORDER BY 进行升序排序时，NULL 值会排列在最前面。例如，假设 fruits 表中的 quantity 列包含 NULL 值：

SELECT * FROM fruits ORDER BY quantity;

这个查询将按照 quantity 列进行升序排序，将 NULL 值排列在结果的最前面。

降序排序中的NULL值

在降序排序时，NULL 值会被排列在最后面。例如：

SELECT * FROM fruits ORDER BY quantity DESC;

上述查询将按照 quantity 列进行降序排序，NULL 值会排列在结果的最后面。

控制NULL值排序位置

如果您想要更灵活地控制 NULL 值的排序位置，可以使用 IS NULL 和 IS NOT NULL 条件，将 NULL 值分开处理。例如，如果您希望将 NULL 值放在排序结果的最后，可以使用以下方法：

SELECT * FROM fruits ORDER BY quantity IS NULL, quantity DESC;

上述查询首先按照是否为 NULL 对 quantity 列进行排序，将 NULL 值放在最后；然后对非 NULL 值进行降序排序。

若想将 NULL 值放在排序结果的最前面，可以使用以下方式：

SELECT * FROM fruits ORDER BY quantity IS NOT NULL, quantity;

这个查询首先按照是否为 NULL 对 quantity 列进行排序，将 NULL 值放在最前面；然后对非 NULL 值进行升序排序。

3.2 LIMIT子句的应用

LIMIT 子句用于限制结果集的行数，可以在查询中控制返回的记录数量。它的语法如下：

SELECT * FROM fruits LIMIT 5;

上述查询将返回前五行的结果。这在您希望获取一小部分数据或者进行简单的数据预览时非常有用。

同时，您还可以结合 LIMIT 和 OFFSET（或者简写为 LIMIT x, y）来实现分页的效果，其中 x 代表起始位置，y 代表返回的行数：

SELECT * FROM fruits LIMIT 3 OFFSET 2;

上述查询将返回从第三行开始的三行数据，实现了分页查询中的偏移效果。

3.3 结合使用ORDER BY和LIMIT进行分页

结合使用 ORDER BY 和 LIMIT 可以非常有效地实现分页查询。例如，如果您希望获取第二页的数据，每页显示 5 条记录，可以这样做：

SELECT * FROM fruits ORDER BY id LIMIT 5 OFFSET 5;

以上查询将按照 id 列排序，并返回第二页的数据（第 6 到第 10 条记录）。

3.4 使用FETCH FIRST实现更灵活的限制

MySQL 8.0 版本引入了 FETCH FIRST 语句，它可以实现更灵活的限制，并且与 OFFSET 不同，它更为直观。

示例：

SELECT * FROM fruits ORDER BY id FETCH FIRST 5 ROWS ONLY;

以上查询将按照 id 列排序，并返回前 5 条记录。这个语法使用 FETCH FIRST 指定要返回的行数，相对于 LIMIT 和 OFFSET，这种方式更加直观。

3.5 结合使用ORDER BY和FETCH FIRST实现分页效果

结合 ORDER BY 和 FETCH FIRST 可以轻松实现分页效果：

SELECT * FROM fruits ORDER BY id OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY;

这个查询首先按照 id 列排序，然后跳过前 5 行并返回接下来的 5 条记录，实现了分页查询中的偏移和限制。

写在最后

通过以上一系列操作，我们详细解析了MySQL基础查询语句的使用。在实际操作中，读者能够更深入地理解每个语句的