查询条件WHERE

嗨，长安又来了！今天我们深入学习WHERE子句，它是查询的核心！

掌握WHERE，你就能精准地找到任何想要的数据！🎯

🎯 WHERE是什么？

想象你在图书馆找书：

  • 📚 "我要找编程类的书" → WHERE category = '编程'
  • 📚 "我要找2020年后出版的书" → WHERE publish_year > 2020
  • 📚 "我要找价格在50-100元的书" → WHERE price BETWEEN 50 AND 100

WHERE就是筛选条件！

📝 准备测试数据

让我们创建一个图书表来练习：

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    price DECIMAL(10, 2),
    publish_year INT,
    pages INT,
    category VARCHAR(50),
    rating DECIMAL(3, 1),
    in_stock BOOLEAN DEFAULT TRUE,
    description TEXT
);

-- 插入测试数据
INSERT INTO books (title, author, price, publish_year, pages, category, rating, in_stock) 
VALUES 
    ('PostgreSQL权威指南', '长安', 89.00, 2023, 500, '数据库', 4.8, TRUE),
    ('Python核心编程', '张三', 79.00, 2022, 450, '编程', 4.5, TRUE),
    ('算法导论', '李四', 128.00, 2020, 800, '算法', 4.9, FALSE),
    ('深入理解计算机系统', '王五', 139.00, 2021, 900, '计算机', 4.7, TRUE),
    ('代码大全', '赵六', 108.00, 2019, 700, '编程', 4.6, TRUE),
    ('设计模式', '孙七', 58.00, 2018, 350, '设计', 4.4, TRUE),
    ('Clean Code', 'Robert', 68.00, 2022, 400, '编程', 4.7, TRUE),
    ('人月神话', 'Brooks', 45.00, 2015, 200, '管理', 4.3, FALSE);

🔍 基础比较运算符

相等和不等

-- 等于
SELECT * FROM books WHERE category = '编程';

-- 不等于（两种写法）
SELECT * FROM books WHERE category != '编程';
SELECT * FROM books WHERE category <> '编程';

-- 查找特定作者
SELECT * FROM books WHERE author = '长安';

-- 查找特定年份
SELECT * FROM books WHERE publish_year = 2022;

大于、小于

-- 大于
SELECT * FROM books WHERE price > 100;

-- 小于
SELECT * FROM books WHERE pages < 500;

-- 大于等于
SELECT * FROM books WHERE rating >= 4.5;

-- 小于等于
SELECT * FROM books WHERE publish_year <= 2020;

组合比较

-- 价格在80-120之间
SELECT * FROM books 
WHERE price >= 80 AND price <= 120;

-- 使用BETWEEN（更简洁）
SELECT * FROM books 
WHERE price BETWEEN 80 AND 120;

-- 页数不在300-500之间
SELECT * FROM books 
WHERE pages NOT BETWEEN 300 AND 500;

🎨 逻辑运算符

AND（与）

所有条件都必须满足：

-- 编程类且评分大于4.5
SELECT * FROM books 
WHERE category = '编程' AND rating > 4.5;

-- 2020年后出版且有库存
SELECT * FROM books 
WHERE publish_year > 2020 AND in_stock = TRUE;

-- 多个AND条件
SELECT * FROM books 
WHERE category = '编程' 
  AND price < 100 
  AND rating >= 4.5 
  AND in_stock = TRUE;

OR（或）

任意一个条件满足即可：

-- 编程类或算法类
SELECT * FROM books 
WHERE category = '编程' OR category = '算法';

-- 评分很高或价格很便宜
SELECT * FROM books 
WHERE rating >= 4.8 OR price < 60;

-- 库存不足或价格太高
SELECT * FROM books 
WHERE in_stock = FALSE OR price > 120;

NOT（非）

取反：

-- 不是编程类的书
SELECT * FROM books 
WHERE NOT category = '编程';

-- 等价写法
SELECT * FROM books 
WHERE category != '编程';

-- 没有库存的书
SELECT * FROM books 
WHERE NOT in_stock;

-- 等价写法
SELECT * FROM books 
WHERE in_stock = FALSE;

组合使用（注意优先级）

-- ❌ 可能不是你想要的结果
SELECT * FROM books 
WHERE category = '编程' OR category = '算法' AND price < 100;
-- AND优先级高于OR，相当于：
-- category = '编程' OR (category = '算法' AND price < 100)

-- ✅ 使用括号明确优先级
SELECT * FROM books 
WHERE (category = '编程' OR category = '算法') AND price < 100;

长安提醒：复杂条件一定要用括号！不要依赖优先级记忆！

📋 IN 和 NOT IN

IN（在列表中）

-- 查找特定几个分类
SELECT * FROM books 
WHERE category IN ('编程', '算法', '数据库');

-- 等价于
SELECT * FROM books 
WHERE category = '编程' 
   OR category = '算法' 
   OR category = '数据库';

-- 查找特定年份
SELECT * FROM books 
WHERE publish_year IN (2020, 2021, 2022, 2023);

-- 结合子查询（后面会详细讲）
SELECT * FROM books 
WHERE author IN (
    SELECT author FROM books 
    WHERE rating > 4.7
);

NOT IN（不在列表中）

-- 排除某些分类
SELECT * FROM books 
WHERE category NOT IN ('管理', '设计');

-- 排除某些作者
SELECT * FROM books 
WHERE author NOT IN ('张三', '李四');

🔤 字符串匹配

LIKE（模糊查询）

通配符含义示例
%任意多个字符'张%' 匹配"张三"、"张三丰"
_单个字符'张_' 只匹配"张三"、"张四"
-- 标题包含"Python"
SELECT * FROM books 
WHERE title LIKE '%Python%';

-- 标题以"深入"开头
SELECT * FROM books 
WHERE title LIKE '深入%';

-- 标题以"编程"结尾
SELECT * FROM books 
WHERE title LIKE '%编程';

-- 作者姓张
SELECT * FROM books 
WHERE author LIKE '张%';

-- 标题是"XXX大全"（三个字+大全）
SELECT * FROM books 
WHERE title LIKE '___大全';

ILIKE（不区分大小写）

-- 不区分大小写的匹配
SELECT * FROM books 
WHERE title ILIKE '%code%';
-- 能匹配"Code"、"code"、"CODE"

NOT LIKE

-- 标题不包含"入门"
SELECT * FROM books 
WHERE title NOT LIKE '%入门%';

正则表达式（高级）

-- 使用正则表达式
SELECT * FROM books 
WHERE title ~ 'Python|Java|C\+\+';  -- 包含Python或Java或C++

-- 不区分大小写的正则
SELECT * FROM books 
WHERE title ~* 'python|java';

-- 标题以数字开头
SELECT * FROM books 
WHERE title ~ '^[0-9]';

🔍 NULL值处理

NULL表示"未知"或"没有值"，它很特殊！

IS NULL 和 IS NOT NULL

-- 查找没有描述的书
SELECT * FROM books 
WHERE description IS NULL;

-- 查找有描述的书
SELECT * FROM books 
WHERE description IS NOT NULL;

⚠️ 长安的警告：

-- ❌ 错误！NULL不能用=比较
SELECT * FROM books WHERE description = NULL;  -- 永远返回空！

-- ✅ 正确
SELECT * FROM books WHERE description IS NULL;

COALESCE（处理NULL）

-- 如果description为NULL，显示"暂无描述"
SELECT 
    title,
    COALESCE(description, '暂无描述') AS desc
FROM books;

-- 用第一个非NULL值
SELECT 
    title,
    COALESCE(author, '佚名') AS author_name
FROM books;

NULLIF（生成NULL）

-- 如果评分是0，返回NULL
SELECT 
    title,
    NULLIF(rating, 0) AS rating
FROM books;

🎯 EXISTS（存在性查询）

检查子查询是否返回结果：

-- 查找有评分大于4.5的书的作者写的所有书
SELECT * FROM books b1
WHERE EXISTS (
    SELECT 1 FROM books b2 
    WHERE b2.author = b1.author 
      AND b2.rating > 4.5
);

-- NOT EXISTS（不存在）
SELECT * FROM books b1
WHERE NOT EXISTS (
    SELECT 1 FROM books b2 
    WHERE b2.author = b1.author 
      AND b2.rating > 4.8
);

🎲 其他实用条件

ANY / ALL

-- 价格大于任意一个编程类书籍的价格
SELECT * FROM books 
WHERE price > ANY (
    SELECT price FROM books WHERE category = '编程'
);

-- 价格大于所有编程类书籍的价格
SELECT * FROM books 
WHERE price > ALL (
    SELECT price FROM books WHERE category = '编程'
);

CASE WHEN（条件表达式）

-- 根据价格分类
SELECT 
    title,
    price,
    CASE 
        WHEN price < 60 THEN '便宜'
        WHEN price BETWEEN 60 AND 100 THEN '中等'
        ELSE '昂贵'
    END AS price_level
FROM books;

-- 根据评分分级
SELECT 
    title,
    rating,
    CASE 
        WHEN rating >= 4.7 THEN '神作'
        WHEN rating >= 4.5 THEN '优秀'
        WHEN rating >= 4.0 THEN '良好'
        ELSE '一般'
    END AS grade
FROM books;

📊 实战案例

案例1：图书搜索系统

-- 综合搜索：编程类、价格适中、有库存、评分高
SELECT 
    title,
    author,
    price,
    rating
FROM books 
WHERE category = '编程'
  AND price BETWEEN 50 AND 100
  AND in_stock = TRUE
  AND rating >= 4.5
ORDER BY rating DESC;

-- 模糊搜索
SELECT * FROM books 
WHERE title ILIKE '%python%'
   OR description ILIKE '%python%'
   OR author ILIKE '%python%';

-- 高级筛选
SELECT * FROM books 
WHERE (category IN ('编程', '算法', '数据库'))
  AND (publish_year >= 2020)
  AND (rating >= 4.5 OR price < 80)
  AND in_stock = TRUE
ORDER BY rating DESC, price ASC
LIMIT 10;

案例2：数据分析

-- 找出"性价比"高的书（评分高且价格低）
SELECT 
    title,
    price,
    rating,
    ROUND(rating / price * 100, 2) AS value_score
FROM books 
WHERE rating IS NOT NULL 
  AND price > 0
ORDER BY value_score DESC
LIMIT 5;

-- 找出库存不足的热门书
SELECT * FROM books 
WHERE in_stock = FALSE 
  AND rating >= 4.5
ORDER BY rating DESC;

-- 按年代分类统计
SELECT 
    CASE 
        WHEN publish_year >= 2022 THEN '最新'
        WHEN publish_year >= 2020 THEN '近期'
        WHEN publish_year >= 2015 THEN '较旧'
        ELSE '老书'
    END AS age_group,
    COUNT(*) AS book_count,
    AVG(rating) AS avg_rating
FROM books 
GROUP BY age_group
ORDER BY book_count DESC;

案例3：推荐系统

-- 如果你喜欢这本书，你可能也喜欢...
SELECT b2.*
FROM books b1
JOIN books b2 ON b1.category = b2.category 
  AND b1.id != b2.id
WHERE b1.title = 'PostgreSQL权威指南'
  AND b2.rating >= 4.5
  AND b2.in_stock = TRUE
ORDER BY b2.rating DESC
LIMIT 5;

💡 WHERE使用技巧

1. 索引友好的写法

-- ✅ 好：直接比较列
SELECT * FROM books WHERE publish_year = 2022;

-- ❌ 不好：对列进行计算（破坏索引）
SELECT * FROM books WHERE publish_year + 1 = 2023;

-- ✅ 好：移到右边计算
SELECT * FROM books WHERE publish_year = 2023 - 1;

2. 避免隐式类型转换

-- 假设id是INTEGER类型

-- ❌ 不好：字符串会导致类型转换
SELECT * FROM books WHERE id = '1';

-- ✅ 好：直接用数字
SELECT * FROM books WHERE id = 1;

3. 短路优化

-- 把最可能过滤掉大量数据的条件放前面
-- ✅ 好（假设in_stock能过滤掉90%）
SELECT * FROM books 
WHERE in_stock = TRUE 
  AND category = '编程' 
  AND rating > 4.5;

4. 使用EXISTS代替IN

-- 如果子查询结果很大，EXISTS更快
-- ✅ 推荐
SELECT * FROM books b
WHERE EXISTS (
    SELECT 1 FROM large_table 
    WHERE some_id = b.id
);

-- 可能较慢
SELECT * FROM books 
WHERE id IN (
    SELECT some_id FROM large_table
);

🎮 练习题

任务1：基础查询

使用books表，完成以下查询：

  1. 查找价格在60-100元的书
  2. 查找2020年及之后出版的书
  3. 查找标题包含"编程"或"算法"的书
  4. 查找有库存且评分大于4.5的书
  5. 查找作者姓"张"或姓"李"的书
点击查看答案
-- 1
SELECT * FROM books WHERE price BETWEEN 60 AND 100;

-- 2
SELECT * FROM books WHERE publish_year >= 2020;

-- 3
SELECT * FROM books WHERE title LIKE '%编程%' OR title LIKE '%算法%';

-- 4
SELECT * FROM books WHERE in_stock = TRUE AND rating > 4.5;

-- 5
SELECT * FROM books WHERE author LIKE '张%' OR author LIKE '李%';

任务2：综合查询

  1. 查找编程类、有库存、价格低于100、2020年后出版的书
  2. 查找评分最高的3本书
  3. 统计每个分类的平均价格，只显示平均价格大于80的
  4. 找出同时满足"评分>=4.5"和"页数>=500"的书
点击查看答案
-- 1
SELECT * FROM books 
WHERE category = '编程'
  AND in_stock = TRUE
  AND price < 100
  AND publish_year > 2020;

-- 2
SELECT * FROM books 
ORDER BY rating DESC 
LIMIT 3;

-- 3
SELECT category, AVG(price) as avg_price 
FROM books 
GROUP BY category 
HAVING AVG(price) > 80;

-- 4
SELECT * FROM books 
WHERE rating >= 4.5 AND pages >= 500;

📚 下一步

太棒了！你已经掌握了WHERE条件查询的各种技巧！现在你可以：

  • ✅ 使用各种比较运算符
  • ✅ 组合AND、OR、NOT逻辑
  • ✅ 使用IN、LIKE、BETWEEN
  • ✅ 处理NULL值
  • ✅ 编写复杂的查询条件

接下来，让我们进入进阶部分，学习多表联查JOIN！

长安的经验谈：

WHERE是SQL查询的灵魂！记住几个要点：

  1. 括号很重要：复杂条件一定要加括号
  2. NULL要特殊对待：用IS NULL，不用=NULL
  3. LIKE要谨慎：%开头的模糊查询很慢
  4. 索引很关键：写WHERE时要考虑索引

多写多练，WHERE的各种组合很快就能熟练掌握！💪

遇到问题就来编程指南找我！

最近更新: