MySQL修炼手册3：条件筛选：使用WHERE子句进行数据过滤

写在开头

MySQL数据库作为广泛应用的关系型数据库管理系统，在数据处理中扮演着至关重要的角色。熟练使用条件筛选是数据库查询的基石，而本文将深入探讨MySQL中的WHERE子句的使用，助力读者更有效地进行数据过滤与提取。

1 基础条件筛选

在MySQL查询中，基础条件筛选是最常见的操作之一，为了演示条件筛选，我们将创建一个简单的水果表（fruit_table）并执行一些基础条件筛选的查询。首先，我们创建水果表并插入一些示例数据：

-- 创建水果表
CREATE TABLE fruit_table (
    fruit_id INT PRIMARY KEY,
    fruit_name VARCHAR(50),
    quantity INT,
    price DECIMAL(8, 2)
);

-- 插入示例数据
INSERT INTO fruit_table (fruit_id, fruit_name, quantity, price) VALUES
(1, 'Apple', 100, 1.5),
(2, 'Banana', 50, 0.8),
(3, 'Orange', 75, 1.2),
(4, 'Grapes', 30, 2.0),
(5, 'Watermelon', 10, 5.0);

1.1 等值条件

等值条件用于精确匹配字段的值。例如，我们可以使用等值条件查找水果表中名称为 ‘Banana’ 的记录：

SELECT * FROM fruit_table WHERE fruit_name = 'Banana';

结果将是一行数据，即名称为 ‘Banana’ 的水果记录。

1.2 范围条件

范围条件用于指定字段的取值范围。例如，我们可以使用范围条件查找库存在 30 到 100 之间的水果：

SELECT * FROM fruit_table WHERE quantity BETWEEN 30 AND 100;

这将返回库存在指定范围内的水果记录。

1.3 空值判断

空值判断用于检查字段是否为空。例如，我们可以使用空值判断查找价格为空的水果记录：

SELECT * FROM fruit_table WHERE price IS NULL;

在这个示例中，由于我们的表中没有空值，结果集将为空。

下面我们将展示查询非空的数据集：

SELECT * FROM fruit_table WHERE price IS NOT NULL;

在这个示例中，由于我们的表中没有空值，结果集为所有数据。

2 复杂条件筛选

2.1 逻辑运算符的使用

2.1.1 AND 运算符

AND 运算符用于同时满足多个条件。例如，查找库存在 30 到 100 之间且价格小于 2 的水果：

SELECT * FROM fruit_table WHERE quantity BETWEEN 30 AND 100 AND price < 2;

2.1.2 OR 运算符

OR 运算符用于满足多个条件中的至少一个。例如，查找库存在 100 以上或价格小于 1 的水果：

SELECT * FROM fruit_table WHERE quantity > 100 OR price < 1;

2.1.3 NOT 运算符

NOT 运算符用于否定一个条件。例如，查找库存在 30 到 100 之间但价格不是 2 的水果：

SELECT * FROM fruit_table WHERE quantity BETWEEN 30 AND 100 AND NOT price = 2;

2.1.4 多重逻辑运算符的组合

逻辑运算符可以结合使用，形成更为复杂的查询条件。例如，查找库存在 50 到 100 之间且（价格小于 2 或水果名为 ‘Apple’）的水果：

SELECT * FROM fruit_table WHERE quantity BETWEEN 50 AND 100 AND (price < 2 OR fruit_name = 'Apple');

2.2 子查询在WHERE子句中的应用

当使用子查询在WHERE子句中进行条件筛选时，有许多不同的应用场景。

2.2.1 使用子查询进行IN条件筛选

假设我们想要找到销售量超过平均销售量的水果。我们可以使用子查询在WHERE子句中进行IN条件筛选：

SELECT * FROM fruit_table
WHERE fruit_id IN (SELECT fruit_id FROM sales WHERE quantity > (SELECT AVG(quantity) FROM sales));

这将返回销售量超过平均销售量的水果记录。

2.2.2 使用子查询进行EXISTS条件筛选

假设我们想要找到没有销售记录的水果。我们可以使用子查询在WHERE子句中进行EXISTS条件筛选：

SELECT * FROM fruit_table f
WHERE NOT EXISTS (SELECT 1 FROM sales s WHERE s.fruit_id = f.fruit_id);

这将返回没有销售记录的水果。

2.2.3 使用子查询进行比较条件筛选

假设我们想要找到库存量比某个水果的平均库存还多的水果。我们可以使用子查询在WHERE子句中进行比较条件筛选：

SELECT * FROM fruit_table f
WHERE quantity > (SELECT AVG(quantity) FROM fruit_table WHERE fruit_id = 1);

这将返回库存量比水果ID为1的平均库存还多的水果。

2.2.4 使用子查询进行相关子查询的比较

假设我们想要找到销售量比同类其他水果的平均销售量还多的水果。我们可以使用相关子查询在WHERE子句中进行比较：

SELECT * FROM fruit_table f
WHERE quantity > (SELECT AVG(quantity) FROM fruit_table WHERE fruit_name = f.fruit_name AND fruit_id <> f.fruit_id);

这将返回销售量比同类其他水果的平均销售量还多的水果。

2.3 EXISTS与NOT EXISTS的区别

EXISTS 和 NOT EXISTS 是用于子查询的条件谓词，它们的主要区别在于它们对子查询的结果集的处理方式。

2.3.1 EXISTS

• 定义： EXISTS 是一个条件谓词，用于检查子查询是否返回至少一行结果。

• 使用场景： 主要用于条件筛选，当主查询中的某一行满足子查询的条件时，EXISTS 返回真（True）；否则返回假（False）。

• 例子： 查找至少有一笔销售记录的水果。

  SELECT * FROM fruit_table f WHERE EXISTS (SELECT 1 FROM sales s WHERE s.fruit_id = f.fruit_id);
  

  如果子查询返回至少一行记录（销售记录），则主查询中的相应水果记录被包括在结果中。

2.3.2 NOT EXISTS

• 定义： NOT EXISTS 是 EXISTS 的相反条件谓词，用于检查子查询是否不返回任何结果。

• 使用场景： 主要用于条件筛选，当主查询中的某一行不满足子查询的条件时，NOT EXISTS 返回真（True）；否则返回假（False）。

• 例子： 查找没有销售记录的水果。

  SELECT * FROM fruit_table f WHERE NOT EXISTS (SELECT 1 FROM sales s WHERE s.fruit_id = f.fruit_id);
  

  如果子查询没有返回任何记录（没有销售记录），则主查询中的相应水果记录被包括在结果中。

2.3.3 区别总结

• EXISTS 返回真，如果子查询至少返回一行；而 NOT EXISTS 返回真，如果子查询没有返回任何行。
• 如果子查询返回任何行，EXISTS 条件为真，主查询中的行将被包括在结果中。相反，如果子查询返回任何行，NOT EXISTS 条件为假，主查询中的行将被排除在结果之外。

3 使用CASE表达式进行条件判断

3.1 简化复杂的条件判断

CASE 表达式允许我们在查询时实现类似编程语言中的条件判断，从而进行更为复杂的逻辑。例如，我们可以使用 CASE 表达式为水果添加一个新的列，用于表示库存状态（“In Stock” 或 “Out of Stock”）：

SELECT 
    fruit_name,
    quantity,
    price,
    CASE 
        WHEN quantity > 0 THEN 'In Stock'
        ELSE 'Out of Stock'
    END AS stock_status
FROM fruit_table;

在这个例子中，CASE 表达式根据库存数量判断水果的库存状态，将结果作为新的列 stock_status 返回。

3.2 CASE的使用场景

CASE 表达式不仅可以用于 SELECT 语句，还可以用于 WHERE 和 ORDER BY 等子句，具有广泛的应用场景。例如，我们可以使用 CASE 表达式在 WHERE 子句中筛选出价格高于平均价格的水果：

SELECT 
    fruit_name,
    quantity,
    price
FROM fruit_table
WHERE price > (
    SELECT AVG(price) FROM fruit_table
);

这个例子中，CASE 表达式用于在 WHERE 子句中进行条件判断，以筛选价格高于平均价格的水果。

写在最后

通过深入学习MySQL中条件筛选的使用，我们可以更加灵活地提取所需的数据，优化查询性能，提高数据库的操作效率。在实际应用中，结合基础和复杂条件筛选的技巧，以及合理使用CASE表达式，将使得我们能够更好地应对各种数据查询挑战。希望这篇MySQL修炼手册能够帮助读者更深入地理解条件筛选的精髓，提升数据库操作的水平。