正文
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 |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
*/
结语
感谢您的阅读!期待您的一键三连!欢迎指正!