MySQL WHERE 条件筛选

比较/逻辑/IN/BETWEEN/LIKE/IS NULL 全面掌握 · 难度：入门 · +20XP

WHERE — 数据筛选的核心

WHERE 子句过滤查询结果，只返回满足条件的行。没有 WHERE 的 SELECT 会返回全表数据，加了 WHERE 精准定位。

比较运算符

SELECT * FROM products WHERE price = 99;       -- 等于
SELECT * FROM products WHERE price > 50;        -- 大于
SELECT * FROM products WHERE price >= 100;      -- 大于等于
SELECT * FROM products WHERE price < 30;        -- 小于
SELECT * FROM products WHERE name != "test";    -- 不等于（也可用 <>）

逻辑运算符

-- AND：所有条件都满足
SELECT * FROM students WHERE age > 18 AND grade = "A";
-- OR：任一条件满足
SELECT * FROM products WHERE category = "电子" OR category = "图书";
-- NOT：取反
SELECT * FROM users WHERE NOT is_deleted;
-- 组合（用括号控制优先级）
SELECT * FROM orders WHERE (status = "pending" OR status = "processing") AND total > 100;

IN / BETWEEN / LIKE / IS NULL

-- IN：匹配列表中的任意值
SELECT * FROM students WHERE grade IN ("A", "B", "C");
-- BETWEEN：范围查询（包含边界）
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
-- LIKE：模糊匹配（% 匹配任意字符，_ 匹配单个字符）
SELECT * FROM users WHERE name LIKE "张%";       -- 张开头
SELECT * FROM users WHERE email LIKE "%@gmail%";  -- 含 @gmail
-- IS NULL：判空（不能用 = NULL！）
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

常见陷阱

• NULL 不能用 = 或 != 比较，必须用 IS NULL / IS NOT NULL
• LIKE "%keyword%" 全表扫描大数据时很慢，考虑用全文索引
• BETWEEN 是包含边界的（闭区间）