【MySQL】条件查询与过滤_mysql 条件查询-优快云博客

本文链接：https://blog.youkuaiyun.com/Go_ahead1025/article/details/147935938

在这里插入图片描述

个人主页：Guiat
归属专栏：MySQL

在这里插入图片描述

文章目录

1. 条件查询基础
- 1.1 条件查询的重要性
- 1.2 MySQL条件查询结构
2. WHERE 子句基础
3. 比较运算符
4. 逻辑运算符
5. NULL 值处理
6. 高级过滤技术
7. 过滤分组结果 (HAVING)
8. 复合条件查询优化

正文

1. 条件查询基础

条件查询是数据库操作中最常用的功能之一，它允许我们从大量数据中精确筛选出满足特定条件的记录。MySQL提供了丰富的条件查询和过滤机制，帮助用户高效地获取所需数据。

1.1 条件查询的重要性

减少数据传输量，提高应用性能
精确获取业务所需的数据
减轻应用程序的数据处理负担
提高数据分析的精确度和效率
实现更复杂的业务逻辑

1.2 MySQL条件查询结构

2. WHERE 子句基础

2.1 WHERE子句语法

WHERE子句是SQL语句中用于指定查询条件的关键部分，它决定了哪些行会被包含在最终结果中：

-- 基本WHERE语法
SELECT column1, column2, ...
FROM table_name
WHERE condition;

2.2 单一条件示例

-- 精确匹配
SELECT * FROM products WHERE product_id = 101;

-- 不等于条件
SELECT * FROM products WHERE category_id <> 5;

-- 大于和小于
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE stock < 10;

-- 大于等于和小于等于
SELECT * FROM employees WHERE hire_date <= '2022-01-01';
SELECT * FROM products WHERE price >= 50;

2.3 条件测试顺序

当多个条件组合时，MySQL按照以下优先级处理条件：

3. 比较运算符

3.1 基本比较运算符

MySQL支持多种比较运算符用于构建查询条件：

运算符	描述	示例
=	等于	`price = 19.99`
<> 或 !=	不等于	`status <> 'cancelled'`
<	小于	`quantity < 5`
<=	小于等于	`date <= '2023-01-31'`
>	大于	`price > 100`
>=	大于等于	`age >= 18`

-- 示例查询
SELECT product_name, price, stock
FROM products
WHERE price = 19.99;

SELECT order_id, order_date, status
FROM orders
WHERE status <> 'cancelled';

SELECT product_name, price, stock
FROM products
WHERE stock < 10;

3.2 BETWEEN 运算符

BETWEEN运算符用于查找在指定范围内的值（包含边界值）：

-- 语法
SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;

-- 等价表达式
WHERE column >= value1 AND column <= value2;

示例：

-- 价格范围查询
SELECT product_name, price
FROM products
WHERE price BETWEEN 10 AND 50;

-- 日期范围查询
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

-- 字符范围查询
SELECT first_name, last_name
FROM customers
WHERE last_name BETWEEN 'A' AND 'M';

-- 使用NOT BETWEEN
SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 10 AND 50;

3.3 IN 运算符

IN运算符用于检查值是否匹配列表中的任何值：

-- 语法
SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

-- 等价表达式
WHERE column = value1 OR column = value2 OR ...

示例：

-- 多个ID查询
SELECT product_name, price
FROM products
WHERE category_id IN (1, 3, 5);

-- 多状态查询
SELECT order_id, status
FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

-- 使用NOT IN
SELECT customer_id, first_name, last_name
FROM customers
WHERE country NOT IN ('USA', 'Canada', 'Mexico');

-- 子查询与IN结合
SELECT product_name, price
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE parent_category_id = 10
);

3.4 LIKE 运算符与模式匹配

LIKE运算符用于基于模式进行字符串匹配，通常与通配符一起使用：

-- 语法
SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

常用通配符：

%: 匹配任意数量的字符（包括零个字符）
_: 匹配单个字符

示例：

-- 开头匹配
SELECT product_name, price
FROM products
WHERE product_name LIKE 'Apple%';  -- 以"Apple"开头的产品

-- 结尾匹配
SELECT customer_id, email
FROM customers
WHERE email LIKE '%@gmail.com';  -- Gmail邮箱用户

-- 包含匹配
SELECT product_name, description
FROM products
WHERE description LIKE '%wireless%';  -- 描述中包含"wireless"的产品

-- 单字符匹配
SELECT product_id, product_code
FROM products
WHERE product_code LIKE 'A_123';  -- 产品代码格式为A后接任意单个字符，然后是123

-- 组合使用
SELECT product_name, price
FROM products
WHERE product_name LIKE 'i%one%';  -- 以"i"开头且包含"one"的产品

-- 使用NOT LIKE
SELECT product_name, description
FROM products
WHERE product_name NOT LIKE '%refurbished%';  -- 名称中不含"refurbished"的产品

3.5 正则表达式匹配 (REGEXP)

MySQL支持使用REGEXP运算符进行更强大的模式匹配：

-- 语法
SELECT column1, column2, ...
FROM table_name
WHERE column REGEXP pattern;

示例：

-- 基本模式匹配
SELECT product_name, price
FROM products
WHERE product_name REGEXP '^iPhone';  -- 以"iPhone"开头的产品

-- 多模式匹配
SELECT customer_id, first_name, last_name
FROM customers
WHERE last_name REGEXP 'son$|^Mac';  -- 以"son"结尾或以"Mac"开头的姓氏

-- 字符类
SELECT product_name, price
FROM products
WHERE product_name REGEXP '[0-9]GB';  -- 包含数字后跟"GB"的产品名

-- 重复模式
SELECT customer_id, phone
FROM customers
WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';  -- 美国电话号码格式验证

-- 使用NOT REGEXP
SELECT product_name, description
FROM products
WHERE description NOT REGEXP 'refurbished|used|damaged';  -- 描述中不包含这些关键词的产品

4. 逻辑运算符

4.1 基本逻辑运算符

MySQL支持以下逻辑运算符用于组合多个条件：

4.2 AND 运算符

AND运算符用于组合多个条件，所有条件必须为真才返回结果：

-- 语法
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND ...;

示例：

-- 价格和库存条件组合
SELECT product_name, price, stock
FROM products
WHERE price > 100 AND stock > 0;

-- 多条件筛选
SELECT order_id, total_amount, status
FROM orders
WHERE order_date > '2023-01-01' AND total_amount > 500 AND status = 'completed';

-- 范围条件
SELECT product_name, price
FROM products
WHERE price > 50 AND price < 200;  -- 等价于BETWEEN 50 AND 200，但不含边界值

4.3 OR 运算符

OR运算符用于组合多个条件，只要任一条件为真即返回结果：

-- 语法
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR ...;

示例：

-- 多类别查询
SELECT product_name, category_id, price
FROM products
WHERE category_id = 1 OR category_id = 3;  -- 等价于IN (1, 3)

-- 价格条件
SELECT product_name, price
FROM products
WHERE price < 10 OR price > 100;

-- 状态条件
SELECT order_id, status
FROM orders
WHERE status = 'cancelled' OR status = 'refunded';

4.4 NOT 运算符

NOT运算符用于否定条件：

-- 语法
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

示例：

-- 排除条件
SELECT product_name, category_id
FROM products
WHERE NOT category_id = 5;  -- 等价于category_id <> 5

-- 与IN组合
SELECT customer_id, country
FROM customers
WHERE NOT country IN ('USA', 'Canada');  -- 等价于NOT IN

-- 与LIKE组合
SELECT product_name, description
FROM products
WHERE product_name NOT LIKE '%refurbished%';

-- 与BETWEEN组合
SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 10 AND 50;

4.5 XOR 运算符

XOR是异或运算符，当且仅当两个条件中有一个为真（但不是两个都为真）时返回结果：

-- 语法
SELECT column1, column2, ...
FROM table_name
WHERE condition1 XOR condition2;

示例：

-- 互斥条件
SELECT product_id, price, is_featured
FROM products
WHERE price > 100 XOR is_featured = 1;  -- 价格高于100或者是特色产品，但不能同时满足两个条件

-- 等价表达式
SELECT product_id, price, is_featured
FROM products
WHERE (price > 100 OR is_featured = 1) AND NOT (price > 100 AND is_featured = 1);

4.6 逻辑运算符组合与优先级

当多个逻辑运算符组合使用时，注意运算优先级：

graph TD
    A[运算优先级] --> B[1. 括号 ()]
    A --> C[2. NOT]
    A --> D[3. AND]
    A --> E[4. OR/XOR]
    
    style A fill:#66CDAA
    style B fill:#87CEFA
    style C fill:#87CEFA
    style D fill:#87CEFA
    style E fill:#87CEFA

示例：

-- 不使用括号（AND优先级高于OR）
SELECT product_name, price, category_id, stock
FROM products
WHERE category_id = 1 OR category_id = 2 AND stock > 0;
-- 等价于：WHERE category_id = 1 OR (category_id = 2 AND stock > 0)

-- 使用括号改变优先级
SELECT product_name, price, category_id, stock
FROM products
WHERE (category_id = 1 OR category_id = 2) AND stock > 0;

-- 复杂条件组合
SELECT order_id, customer_id, total_amount, status
FROM orders
WHERE (status = 'pending' OR status = 'processing')
  AND (total_amount > 1000 OR customer_id IN (1, 2, 3))
  AND order_date > '2023-01-01';

5. NULL 值处理

5.1 NULL的概念

NULL表示"未知"或"不适用"的值，而不是零或空字符串。NULL值的处理有特殊规则：

NULL与任何值（包括另一个NULL）比较总是返回NULL，不是true或false
NULL需要使用特殊运算符IS NULL和IS NOT NULL来检查
包含NULL的算术运算通常返回NULL
聚合函数（如SUM、AVG等）通常忽略NULL值

5.2 IS NULL 和 IS NOT NULL

这两个运算符用于检查列是否为NULL：

-- 语法
SELECT column1, column2, ...
FROM table_name
WHERE column IS NULL;

SELECT column1, column2, ...
FROM table_name
WHERE column IS NOT NULL;

示例：

-- 查找缺少电话号码的客户
SELECT customer_id, first_name, last_name
FROM customers
WHERE phone IS NULL;

-- 查找有电话号码的客户
SELECT customer_id, first_name, last_name
FROM customers
WHERE phone IS NOT NULL;

-- 查找未分配类别的产品
SELECT product_id, product_name
FROM products
WHERE category_id IS NULL;

-- 查找有发货日期的订单
SELECT order_id, order_date, ship_date
FROM orders
WHERE ship_date IS NOT NULL;

5.3 NULL值与逻辑运算

NULL与逻辑运算符结合使用需要特别注意：

-- NULL与AND
SELECT * FROM table_name WHERE NULL AND true;  -- 返回空结果集
SELECT * FROM table_name WHERE NULL AND false;  -- 返回空结果集

-- NULL与OR
SELECT * FROM table_name WHERE NULL OR true;  -- 返回满足true条件的行
SELECT * FROM table_name WHERE NULL OR false;  -- 返回空结果集

-- 在实际查询中的应用
SELECT product_id, product_name, discount
FROM products
WHERE (discount > 0 OR discount IS NULL);  -- 包含有折扣的产品和折扣未知的产品

5.4 IFNULL 和 COALESCE 函数

这些函数用于处理NULL值，提供替代值：

-- IFNULL函数（两个参数）
SELECT product_name, IFNULL(description, 'No description available') AS description
FROM products;

-- COALESCE函数（多个参数，返回第一个非NULL值）
SELECT customer_id, 
       COALESCE(mobile_phone, home_phone, work_phone, email, 'No contact info') AS contact_method
FROM customers;

-- 在计算中使用
SELECT product_id, price, discount,
       price * IFNULL(discount, 0) AS discount_amount,
       price - price * IFNULL(discount, 0) AS final_price
FROM products;

5.5 NULLIF 函数

NULLIF函数将两个表达式进行比较，如果相等则返回NULL，否则返回第一个表达式：

-- 语法
NULLIF(expr1, expr2)

-- 示例
SELECT customer_id, 
       first_purchase_amount,
       second_purchase_amount,
       NULLIF(second_purchase_amount, first_purchase_amount) AS different_amount
FROM customer_purchases;
-- 如果第二次购买金额与第一次相同，则返回NULL，否则返回第二次购买金额

6. 高级过滤技术

6.1 子查询过滤

子查询是嵌套在另一个查询内的SELECT语句，可以在WHERE子句中使用子查询进行过滤：

-- 基本子查询
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);  -- 价格高于平均价格的产品

-- IN子查询
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2023-01-01'
);  -- 2023年以来有下单的客户

-- EXISTS子查询
SELECT customer_id, first_name, last_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > 1000
);  -- 有大额订单的客户

-- ALL和ANY运算符
SELECT product_name, price
FROM products
WHERE price > ALL (
    SELECT price
    FROM products
    WHERE category_id = 3
);  -- 价格高于类别3中所有产品的产品

SELECT product_name, price
FROM products
WHERE price > ANY (
    SELECT price
    FROM products
    WHERE category_id = 3
);  -- 价格高于类别3中至少一个产品的产品

6.2 相关子查询

相关子查询是引用外部查询列的子查询：

-- 查找每个类别中价格最高的产品
SELECT p1.category_id, p1.product_name, p1.price
FROM products p1
WHERE p1.price = (
    SELECT MAX(p2.price)
    FROM products p2
    WHERE p2.category_id = p1.category_id
);

-- 查找高于其类别平均价格的产品
SELECT p1.product_id, p1.product_name, p1.category_id, p1.price
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category_id = p1.category_id
);

6.3 使用函数进行过滤

可以在WHERE子句中使用函数对列值进行处理后过滤：

-- 字符串函数过滤
SELECT product_id, product_name
FROM products
WHERE UPPER(product_name) LIKE '%IPHONE%';  -- 不区分大小写的搜索

-- 日期函数过滤
SELECT order_id, order_date
FROM orders
WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 6;  -- 2023年6月的订单

-- 数值函数过滤
SELECT product_id, price
FROM products
WHERE ROUND(price) = 99;  -- 价格四舍五入后等于99的产品

-- 条件函数过滤
SELECT order_id, order_date, delivery_date
FROM orders
WHERE DATEDIFF(delivery_date, order_date) > 7;  -- 配送时间超过7天的订单

6.4 CASE表达式过滤

CASE表达式可以在WHERE子句中进行条件判断：

-- 使用CASE进行多条件过滤
SELECT product_id, product_name, price, stock
FROM products
WHERE 
    CASE 
        WHEN price < 50 THEN stock > 100  -- 低价产品要求库存大于100
        WHEN price >= 50 AND price < 200 THEN stock > 50  -- 中价产品要求库存大于50
        ELSE stock > 20  -- 高价产品要求库存大于20
    END;

-- 使用CASE简化复杂逻辑
SELECT customer_id, first_name, last_name, country, total_purchases
FROM customers
WHERE 
    CASE country
        WHEN 'USA' THEN total_purchases > 5000
        WHEN 'Canada' THEN total_purchases > 3000
        WHEN 'Mexico' THEN total_purchases > 2000
        ELSE total_purchases > 1000
    END;

7. 过滤分组结果 (HAVING)

7.1 GROUP BY 与 HAVING

HAVING子句用于过滤GROUP BY分组后的结果，类似于WHERE对表中的行进行过滤：

-- 语法
SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition_on_aggregate;

7.2 WHERE 与 HAVING 的区别

7.3 HAVING 子句示例

-- 查找订单总额超过10000的客户
SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 10000;

-- 查找平均价格超过100且有5个以上产品的类别
SELECT category_id, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
GROUP BY category_id
HAVING COUNT(*) > 5 AND AVG(price) > 100;

-- 结合WHERE和HAVING
SELECT category_id, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
WHERE is_active = 1  -- 先过滤出激活的产品
GROUP BY category_id
HAVING COUNT(*) > 5;  -- 再过滤出产品数大于5的类别

-- 复杂分组过滤
SELECT 
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS monthly_revenue
FROM orders
WHERE status = 'completed'
GROUP BY YEAR(order_date), MONTH(order_date)
HAVING COUNT(*) > 100 AND SUM(total_amount) > 50000
ORDER BY order_year, order_month;

8. 复合条件查询优化

8.1 查询优化基本原则

8.2 索引与条件查询

索引是提高条件查询性能的关键：

-- 创建索引
CREATE INDEX idx_product_price ON products(price);
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_customer_country ON customers(country, city);

-- 有效使用索引的查询
-- 精确匹配索引列
SELECT * FROM customers WHERE customer_id = 123;

-- 范围查询索引列
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

-- 前缀匹配索引列（可以使用索引）
SELECT * FROM products WHERE product_name LIKE 'Apple%';

-- 无法有效使用索引的查询
-- 在索引列上使用函数
SELECT * FROM orders WHERE YEAR(order_date) = 2023;  -- 不能使用索引

-- 索引列在表达式中
SELECT * FROM products WHERE price + 10 > 100;  -- 不能使用索引

-- 通配符开头的LIKE
SELECT * FROM products WHERE product_name LIKE '%phone%';  -- 不能使用索引

8.3 复合条件优化

优化复杂WHERE子句可以显著提高查询性能：

-- 将OR改为IN（可能提高性能）
-- 低效：
SELECT * FROM products WHERE category_id = 1 OR category_id = 2 OR category_id = 3;
-- 优化：
SELECT * FROM products WHERE category_id IN (1, 2, 3);

-- 避免否定条件（NOT、<>、!=）
-- 低效：
SELECT * FROM customers WHERE country <> 'USA';
-- 如果需要处理大部分数据，考虑重写查询

-- 分解复杂查询
-- 低效：
SELECT * 
FROM products
WHERE (category_id = 1 AND price > 100) OR (category_id = 2 AND price > 50);
-- 优化（使用UNION）：
SELECT * FROM products WHERE category_id = 1 AND price > 100
UNION
SELECT * FROM products WHERE category_id = 2 AND price > 50;

-- 使用EXISTS代替IN子查询（对于大结果集）
-- 低效：
SELECT * 
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 1000);
-- 优化：
SELECT * 
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id AND o.total_amount > 1000
);

8.4 查询执行计划分析

使用EXPLAIN命令分析和优化条件查询：

-- 分析查询执行计划
EXPLAIN SELECT * FROM products WHERE price > 100 AND category_id = 5;

-- 执行计划输出示例
/*
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
*/

-- 添加索引并再次分析
CREATE INDEX idx_cat_price ON products(category_id, price);
EXPLAIN SELECT * FROM products WHERE price > 100 AND category_id = 5;

-- 优化后的执行计划示例
/*
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | range | idx_cat_price | idx_cat_price| 9       | NULL |  100 |    33.33 | Using where |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
*/