MySQL WHERE 条件过滤

WHERE 子句用于指定查询条件，只返回满足条件的记录。

基本语法

SELECT 列名 FROM 表名 WHERE 条件;

比较运算符

-- 创建示例表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 2),
    stock INT
);

INSERT INTO products VALUES
(1, '手机', 2999, 100),
(2, '电脑', 5999, 50),
(3, '耳机', 299, 200),
(4, '平板', 1999, 0);

-- 等于
SELECT * FROM products WHERE price = 2999;

-- 不等于
SELECT * FROM products WHERE price != 2999;
SELECT * FROM products WHERE price <> 2999;

-- 大于、小于
SELECT * FROM products WHERE price > 2000;
SELECT * FROM products WHERE stock < 50;

-- 范围比较
SELECT * FROM products WHERE price >= 1000 AND price <= 5000;

逻辑运算符

-- AND 与
SELECT * FROM products WHERE price > 1000 AND stock > 0;

-- OR 或
SELECT * FROM products WHERE price < 500 OR stock = 0;

-- NOT 非
SELECT * FROM products WHERE NOT stock = 0;

特殊运算符

-- BETWEEN...AND 范围
SELECT * FROM products WHERE price BETWEEN 500 AND 3000;

-- IN 列表
SELECT * FROM products WHERE id IN (1, 3, 5);

-- LIKE 模糊匹配
SELECT * FROM products WHERE name LIKE '手%';  -- 以"手"开头
SELECT * FROM products WHERE name LIKE '%机';  -- 以"机"结尾
SELECT * FROM products WHERE name LIKE '%耳%'; -- 包含"耳"

-- IS NULL 判断空值
SELECT * FROM products WHERE stock IS NULL;
SELECT * FROM products WHERE stock IS NOT NULL;

运算符优先级

-- 使用括号明确优先级
SELECT * FROM products
WHERE (price < 500 OR stock > 100) AND name LIKE '%机';

WHERE 在 GROUP BY 之前执行，用于过滤原始数据。

要点总结

• WHERE 用于过滤行数据
• 支持比较、逻辑、范围、模糊匹配等运算符
• AND 优先级高于 OR，建议用括号明确逻辑
• NULL 值判断用 IS NULL 或 IS NOT NULL