🔍 MySQL WHERE 子句

WHERE 子句概述

WHERE 子句用于过滤记录，只返回满足指定条件的记录。它是 SQL 查询中最重要的部分之一，可以与 SELECT、UPDATE、DELETE 等语句配合使用。

基本比较操作符

等于和不等于

-- 等于
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE city = 'Beijing';
SELECT * FROM users WHERE status = 'active';

-- 不等于
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age <> 25;  -- 标准 SQL 语法
SELECT * FROM users WHERE city != 'Beijing';

-- 字符串比较（区分大小写）
SELECT * FROM users WHERE username = 'John';  -- 不会匹配 'john'
SELECT * FROM users WHERE LOWER(username) = 'john';  -- 不区分大小写

大小比较

-- 大于和小于
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age < 65;
SELECT * FROM users WHERE salary >= 50000;
SELECT * FROM users WHERE created_at <= '2024-01-01';

-- 日期比较
SELECT * FROM orders WHERE order_date > '2024-01-01';
SELECT * FROM orders WHERE created_at >= '2024-01-01 00:00:00';
SELECT * FROM orders WHERE created_at < NOW();

-- 数值范围查询
SELECT * FROM products WHERE price > 100 AND price < 500;

范围和列表操作符

BETWEEN 范围查询

-- 数值范围
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- 日期范围
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM logs 
WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';

-- NOT BETWEEN
SELECT * FROM users WHERE age NOT BETWEEN 18 AND 65;

-- 字符串范围（按字典序）
SELECT * FROM users WHERE username BETWEEN 'A' AND 'M';

IN 列表查询

-- 基本 IN 查询
SELECT * FROM users WHERE city IN ('Beijing', 'Shanghai', 'Guangzhou');
SELECT * FROM products WHERE category_id IN (1, 2, 3, 5);
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');

-- NOT IN
SELECT * FROM users WHERE city NOT IN ('Beijing', 'Shanghai');
SELECT * FROM products WHERE id NOT IN (1, 2, 3);

-- IN 子查询
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE total_amount > 1000
);

-- 多列 IN（MySQL 5.7+）
SELECT * FROM users 
WHERE (city, country) IN (('Beijing', 'China'), ('Tokyo', 'Japan'));

模式匹配

LIKE 模糊查询

-- 基本 LIKE 查询
SELECT * FROM users WHERE username LIKE 'john%';     -- 以 'john' 开头
SELECT * FROM users WHERE username LIKE '%smith';    -- 以 'smith' 结尾
SELECT * FROM users WHERE username LIKE '%admin%';   -- 包含 'admin'
SELECT * FROM users WHERE username LIKE 'user_';     -- 'user' + 单个字符

-- 通配符说明
-- % : 匹配零个或多个字符
-- _ : 匹配单个字符

-- 复杂模式
SELECT * FROM products WHERE product_name LIKE 'iPhone%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE phone LIKE '138________';  -- 138开头的11位手机号

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@temp%';

-- 转义特殊字符
SELECT * FROM products WHERE description LIKE '%50\% off%';  -- 查找包含 "50% off" 的记录
SELECT * FROM files WHERE filename LIKE '%\_backup%';       -- 查找包含 "_backup" 的文件名

正则表达式 REGEXP

-- 基本正则表达式
SELECT * FROM users WHERE username REGEXP '^[a-zA-Z]+$';     -- 只包含字母
SELECT * FROM users WHERE email REGEXP '^[^@]+@[^@]+\.[^@]+$'; -- 基本邮箱格式
SELECT * FROM products WHERE product_code REGEXP '^[A-Z]{2}[0-9]{4}$'; -- 2个字母+4个数字

-- 常用正则模式
SELECT * FROM users WHERE phone REGEXP '^1[3-9][0-9]{9}$';   -- 中国手机号
SELECT * FROM users WHERE username REGEXP '^[a-z0-9_]{3,20}$'; -- 用户名格式
SELECT * FROM products WHERE price REGEXP '^[0-9]+\.[0-9]{2}$'; -- 价格格式

-- NOT REGEXP
SELECT * FROM users WHERE username NOT REGEXP '[0-9]';       -- 不包含数字的用户名

-- 大小写不敏感匹配
SELECT * FROM users WHERE username REGEXP BINARY '^Admin';   -- 区分大小写
SELECT * FROM users WHERE username REGEXP '^admin';          -- 不区分大小写（默认）

NULL 值处理

IS NULL 和 IS NOT NULL

-- 查找 NULL 值
SELECT * FROM users WHERE last_login IS NULL;
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM orders WHERE delivery_date IS NULL;

-- 查找非 NULL 值
SELECT * FROM users WHERE last_login IS NOT NULL;
SELECT * FROM products WHERE description IS NOT NULL;

-- 注意：不能使用 = NULL 或 != NULL
-- 错误写法
SELECT * FROM users WHERE last_login = NULL;     -- 永远返回空结果
SELECT * FROM users WHERE last_login != NULL;    -- 永远返回空结果

-- 正确写法
SELECT * FROM users WHERE last_login IS NULL;
SELECT * FROM users WHERE last_login IS NOT NULL;

NULL 值的特殊处理

-- 使用 COALESCE 处理 NULL
SELECT 
    username,
    COALESCE(last_login, '从未登录') AS last_login_display
FROM users;

-- 使用 IFNULL 处理 NULL
SELECT 
    product_name,
    IFNULL(description, '暂无描述') AS description_display
FROM products;

-- 使用 CASE 处理 NULL
SELECT 
    username,
    CASE 
        WHEN last_login IS NULL THEN '从未登录'
        ELSE DATE_FORMAT(last_login, '%Y-%m-%d')
    END AS login_status
FROM users;

-- NULL 值在比较中的行为
SELECT * FROM users WHERE age > 25 OR age IS NULL;  -- 包含 NULL 值
SELECT * FROM users WHERE NOT (age <= 25);          -- 不包含 NULL 值

逻辑操作符

AND 逻辑与

-- 基本 AND 操作
SELECT * FROM users 
WHERE age >= 18 AND age <= 65;

SELECT * FROM users 
WHERE city = 'Beijing' AND status = 'active';

SELECT * FROM products 
WHERE price > 100 AND category_id = 1 AND stock_quantity > 0;

-- 多条件组合
SELECT * FROM orders 
WHERE status = 'completed' 
  AND total_amount > 500 
  AND created_at >= '2024-01-01'
  AND user_id IS NOT NULL;

-- 复杂条件
SELECT * FROM users 
WHERE (age BETWEEN 25 AND 35) 
  AND (city IN ('Beijing', 'Shanghai')) 
  AND (salary > 80000);

OR 逻辑或

-- 基本 OR 操作
SELECT * FROM users 
WHERE city = 'Beijing' OR city = 'Shanghai';

SELECT * FROM products 
WHERE category_id = 1 OR category_id = 2;

SELECT * FROM orders 
WHERE status = 'pending' OR status = 'processing';

-- OR 与 AND 的组合（注意优先级）
SELECT * FROM users 
WHERE (city = 'Beijing' OR city = 'Shanghai') 
  AND age > 25;

-- 复杂 OR 条件
SELECT * FROM users 
WHERE (age < 18 OR age > 65) 
   OR (status = 'inactive') 
   OR (last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR));

NOT 逻辑非

-- 基本 NOT 操作
SELECT * FROM users WHERE NOT (age < 18);
SELECT * FROM users WHERE NOT (city = 'Beijing');

-- NOT 与其他操作符结合
SELECT * FROM users WHERE NOT (age BETWEEN 18 AND 65);
SELECT * FROM users WHERE NOT (city IN ('Beijing', 'Shanghai'));
SELECT * FROM users WHERE NOT (username LIKE 'admin%');

-- 复杂 NOT 条件
SELECT * FROM users 
WHERE NOT (
    (city = 'Beijing' AND age < 30) 
    OR 
    (city = 'Shanghai' AND salary < 50000)
);

-- De Morgan's Law 应用
-- NOT (A AND B) = (NOT A) OR (NOT B)
-- NOT (A OR B) = (NOT A) AND (NOT B)
SELECT * FROM users 
WHERE NOT (age < 18 AND status = 'inactive');
-- 等价于
SELECT * FROM users 
WHERE age >= 18 OR status != 'inactive';

操作符优先级

📋 操作符优先级（从高到低）括号 ()比较操作符：=, !=, <>, <, <=, >, >=IS NULL, IS NOT NULLBETWEEN, IN, LIKE, REGEXPNOTANDOR

-- 优先级示例
-- 没有括号的情况
SELECT * FROM users 
WHERE city = 'Beijing' OR city = 'Shanghai' AND age > 25;
-- 等价于
SELECT * FROM users 
WHERE city = 'Beijing' OR (city = 'Shanghai' AND age > 25);

-- 使用括号明确优先级
SELECT * FROM users 
WHERE (city = 'Beijing' OR city = 'Shanghai') AND age > 25;

-- 复杂条件的括号使用
SELECT * FROM orders 
WHERE (status = 'completed' OR status = 'shipped') 
  AND (total_amount > 1000 OR (total_amount > 500 AND user_id IN (1,2,3)))
  AND created_at >= '2024-01-01';

子查询在 WHERE 中的应用

标量子查询

-- 与平均值比较
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- 与最大值比较
SELECT * FROM users 
WHERE created_at = (SELECT MAX(created_at) FROM users);

-- 相关子查询
SELECT * FROM products p1
WHERE price > (
    SELECT AVG(price) 
    FROM products p2 
    WHERE p2.category_id = p1.category_id
);

EXISTS 子查询

-- 存在性检查
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- 不存在检查
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi 
    WHERE oi.product_id = p.id
);

-- 复杂 EXISTS 查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
      AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      AND o.total_amount > 500
);

性能优化技巧

🚀 WHERE 子句性能优化使用索引：确保 WHERE 条件中的列有适当的索引避免函数：不要在 WHERE 条件的列上使用函数选择性高的条件优先：将过滤效果好的条件放在前面避免 OR：考虑使用 UNION 替代复杂的 OR 条件合理使用 LIMIT：限制返回的行数

-- 性能优化示例

-- 不好的查询（在列上使用函数）
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 优化后的查询（使用范围条件）
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 不好的查询（复杂的 OR 条件）
SELECT * FROM users 
WHERE city = 'Beijing' OR city = 'Shanghai' OR city = 'Guangzhou';

-- 优化后的查询（使用 IN）
SELECT * FROM users 
WHERE city IN ('Beijing', 'Shanghai', 'Guangzhou');

-- 使用复合索引的查询
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'completed' AND created_at >= '2024-01-01';
-- 需要在 (user_id, status, created_at) 上创建复合索引

实际应用示例

电商业务查询

-- 查找活跃用户
SELECT * FROM users 
WHERE status = 'active' 
  AND last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND email IS NOT NULL
  AND email NOT LIKE '%@temp%';

-- 查找热销产品
SELECT p.* FROM products p
WHERE p.status = 'active'
  AND p.stock_quantity > 0
  AND EXISTS (
      SELECT 1 FROM order_items oi
      JOIN orders o ON oi.order_id = o.id
      WHERE oi.product_id = p.id
        AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND o.status = 'completed'
      GROUP BY oi.product_id
      HAVING SUM(oi.quantity) > 100
  );

-- 查找异常订单
SELECT * FROM orders 
WHERE (
    (total_amount > 10000 AND user_id NOT IN (SELECT id FROM vip_users))
    OR 
    (total_amount < 1 AND status = 'completed')
    OR 
    (created_at != updated_at AND status = 'pending' AND 
     created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR))
);

数据清理查询

-- 查找重复数据
SELECT email, COUNT(*) as count
FROM users 
WHERE email IS NOT NULL
GROUP BY email 
HAVING COUNT(*) > 1;

-- 查找无效数据
SELECT * FROM users 
WHERE email NOT REGEXP '^[^@]+@[^@]+\.[^@]+$'
   OR phone NOT REGEXP '^1[3-9][0-9]{9}$'
   OR age < 0 OR age > 150
   OR username IS NULL OR username = '';

-- 查找孤立数据
SELECT * FROM order_items oi
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.id = oi.order_id
)
OR NOT EXISTS (
    SELECT 1 FROM products p WHERE p.id = oi.product_id
);

📋 WHERE 子句最佳实践索引优化：为常用的 WHERE 条件列创建索引条件顺序：将选择性高的条件放在前面避免函数：不要在 WHERE 条件的列上使用函数NULL 处理：正确处理 NULL 值的比较类型匹配：确保比较的数据类型一致使用括号：复杂条件使用括号明确优先级测试验证：复杂条件要充分测试各种情况