PostgreSQL WHERE子句

概述

WHERE子句根据指定的条件过滤记录。它只提取满足指定条件的记录。WHERE子句与SELECT、UPDATE和DELETE语句一起使用。

基本语法

SELECT column1, column2, ...
FROM table_name
WHERE condition;

比较运算符

相等和不等

-- 相等
SELECT * FROM users WHERE status = 'active';
SELECT * FROM products WHERE price = 99.99;

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

比较运算符

-- 大于
SELECT * FROM products WHERE price > 50;

-- 大于或等于
SELECT * FROM products WHERE price >= 50;

-- 小于
SELECT * FROM products WHERE price < 100;

-- 小于或等于
SELECT * FROM products WHERE price <= 100;

BETWEEN运算符

-- 数值范围
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

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

-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 100;

IN运算符

-- 值列表
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

-- 数值列表
SELECT * FROM products WHERE category_id IN (1, 2, 3);

-- 子查询
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = true);

-- NOT IN
SELECT * FROM users WHERE country NOT IN ('USA', 'UK');

LIKE运算符

模式匹配

-- 以'J'开头
SELECT * FROM users WHERE name LIKE 'J%';

-- 以'.com'结尾
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 包含'john'
SELECT * FROM users WHERE name LIKE '%john%';

-- 单字符通配符
SELECT * FROM users WHERE name LIKE '_ohn';  -- 匹配'John'、'Kohn'等

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@spam.com';

ILIKE（不区分大小写）

-- 不区分大小写的搜索
SELECT * FROM users WHERE name ILIKE 'john%';  -- 匹配'John'、'JOHN'、'john'

-- 不区分大小写的包含
SELECT * FROM products WHERE name ILIKE '%laptop%';

逻辑运算符

AND运算符

AND 运算符用于组合两个或多个条件，必须所有条件都为真才返回结果。

-- 两个条件（全部必须为真）
SELECT * FROM users WHERE status = 'active' AND country = 'USA';

-- 三个条件
SELECT * FROM products 
WHERE category = 'Electronics' 
    AND price > 50 
    AND stock > 0;

-- 多个AND条件
SELECT * FROM employees 
WHERE department = 'Sales' 
    AND salary > 50000 
    AND hire_date >= '2020-01-01'
    AND status = 'active';

AND运算符示例

-- 查找特定条件的订单
SELECT * FROM orders 
WHERE status = 'completed' 
    AND order_date >= '2024-01-01' 
    AND total_amount > 1000;

-- 产品筛选
SELECT * FROM products 
WHERE price > 100 
    AND category = 'Electronics' 
    AND stock > 0
    AND rating >= 4.0;

-- 用户筛选
SELECT * FROM users 
WHERE status = 'active' 
    AND email_verified = true 
    AND created_at >= CURRENT_DATE - INTERVAL '30 days';

OR运算符

OR 运算符用于组合两个或多个条件，只要有一个条件为真就返回结果。

-- 至少一个条件必须为真
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Books';

-- 多个OR条件
SELECT * FROM users 
WHERE country = 'USA' 
    OR country = 'UK' 
    OR country = 'Canada';

-- 使用IN代替多个OR（更简洁）
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

OR运算符示例

-- 查找多种状态的订单
SELECT * FROM orders 
WHERE status = 'pending' 
    OR status = 'processing' 
    OR status = 'shipped';

-- 更好的方式：使用IN
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'shipped');

-- 多条件OR
SELECT * FROM products 
WHERE category = 'Electronics' 
    OR price < 20 
    OR on_sale = true;

NOT运算符

NOT 运算符用于否定条件，返回不满足条件的记录。

-- 否定条件
SELECT * FROM users WHERE NOT status = 'banned';

-- 等同于
SELECT * FROM users WHERE status <> 'banned';

-- NOT与IN
SELECT * FROM users WHERE status NOT IN ('banned', 'suspended');

-- NOT与LIKE
SELECT * FROM users WHERE email NOT LIKE '%@spam.com';

-- NOT与BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 100;

-- NOT与EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

组合AND、OR、NOT

运算符优先级

PostgreSQL 中的逻辑运算符优先级：

1. NOT（最高优先级）
2. AND
3. OR（最低优先级）

-- 不使用括号（AND优先于OR）
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing' AND salary > 50000;
-- 等同于：
-- WHERE department = 'Sales' OR (department = 'Marketing' AND salary > 50000)

-- 使用括号明确意图
SELECT * FROM employees 
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 50000;

复杂条件组合

-- 使用括号确定优先级
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Books') 
    AND price > 50
    AND stock > 0;

-- 多层嵌套条件
SELECT * FROM users 
WHERE (country = 'USA' OR country = 'UK')
    AND status = 'active'
    AND NOT email LIKE '%@spam.com'
    AND (age >= 18 OR parental_consent = true);

-- 复杂的业务逻辑
SELECT * FROM orders 
WHERE (status = 'pending' OR status = 'processing')
    AND (priority = 'high' OR total_amount > 1000)
    AND NOT (payment_method = 'cash' AND shipping_country <> 'USA');

实际应用场景

-- 场景1：用户筛选
SELECT * FROM users 
WHERE (account_type = 'premium' OR account_type = 'enterprise')
    AND status = 'active'
    AND created_at < CURRENT_DATE - INTERVAL '1 year'
    AND (email_verified = true OR phone_verified = true);

-- 场景2：订单查询
SELECT * FROM orders 
WHERE (status = 'pending' OR status = 'processing')
    AND priority = 'high'
    AND created_at >= CURRENT_DATE - INTERVAL '7 days'
    AND NOT (shipping_method = 'standard' AND total_amount < 50);

-- 场景3：产品搜索
SELECT * FROM products 
WHERE ((category = 'Electronics' AND price < 500) 
    OR (category = 'Books' AND price < 50))
    AND stock > 0
    AND rating >= 4.0
    AND NOT discontinued = true;

AND/OR性能优化

-- 1. 将选择性高的条件放在前面
-- 好：先过滤掉大部分数据
SELECT * FROM orders 
WHERE status = 'completed'  -- 假设只有10%的订单完成
    AND customer_id = 12345;

-- 2. 使用索引
CREATE INDEX idx_orders_status_customer ON orders(status, customer_id);

-- 3. 避免复杂的OR条件
-- 慢：多个OR条件
SELECT * FROM products 
WHERE category = 'A' OR category = 'B' OR category = 'C';

-- 快：使用IN
SELECT * FROM products 
WHERE category IN ('A', 'B', 'C');

-- 4. 使用UNION代替复杂OR（某些情况下）
-- 如果OR条件完全不同，考虑使用UNION
SELECT * FROM products WHERE category = 'Electronics' AND price > 1000
UNION
SELECT * FROM products WHERE on_sale = true AND rating >= 4.5;

IS NULL和IS NOT NULL

-- 检查NULL值
SELECT * FROM users WHERE phone IS NULL;

-- 检查非NULL值
SELECT * FROM users WHERE phone IS NOT NULL;

-- 多个NULL检查
SELECT * FROM users 
WHERE phone IS NULL 
    AND address IS NULL;

EXISTS运算符

-- 检查子查询是否返回任何行
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 复杂的EXISTS
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.product_id = p.id
        AND oi.quantity > 10
);

ANY和ALL运算符

ANY运算符

-- 大于子查询中的任何值
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Books');

-- 等于任何值（类似于IN）
SELECT * FROM users
WHERE country = ANY (ARRAY['USA', 'UK', 'Canada']);

ALL运算符

-- 大于子查询中的所有值
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

-- 不等于所有值
SELECT * FROM users
WHERE country <> ALL (ARRAY['Spam', 'Test']);

WHERE与表达式

-- 算术表达式
SELECT * FROM products WHERE price * 1.1 > 100;

-- 字符串函数
SELECT * FROM users WHERE LENGTH(name) > 10;
SELECT * FROM users WHERE UPPER(email) LIKE '%@GMAIL.COM';

-- 日期函数
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;
SELECT * FROM users WHERE AGE(birth_date) > INTERVAL '18 years';

WHERE与CASE

-- 条件过滤
SELECT * FROM products
WHERE CASE 
    WHEN category = 'Electronics' THEN price > 100
    WHEN category = 'Books' THEN price > 20
    ELSE price > 50
END;

常见模式

多条件

-- 带多个过滤器的用户搜索
SELECT * FROM users
WHERE status = 'active'
    AND country IN ('USA', 'UK', 'Canada')
    AND created_at >= '2024-01-01'
    AND email LIKE '%@company.com'
    AND age BETWEEN 18 AND 65;

日期过滤

-- 今天的记录
SELECT * FROM orders WHERE DATE(created_at) = CURRENT_DATE;

-- 最近7天
SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

-- 本月
SELECT * FROM orders 
WHERE EXTRACT(MONTH FROM created_at) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM created_at) = EXTRACT(YEAR FROM CURRENT_DATE);

-- 今年
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = EXTRACT(YEAR FROM CURRENT_DATE);

文本搜索

-- 不区分大小写的搜索
SELECT * FROM products WHERE LOWER(name) LIKE LOWER('%laptop%');

-- 多词搜索
SELECT * FROM products 
WHERE name ILIKE '%gaming%' 
    AND name ILIKE '%laptop%';

-- 在多列中搜索
SELECT * FROM users 
WHERE name LIKE '%John%' 
    OR email LIKE '%John%';

NULL安全比较

-- 处理NULL值
SELECT * FROM users 
WHERE COALESCE(phone, '') <> '';

-- IS DISTINCT FROM（NULL安全比较）
SELECT * FROM users WHERE email IS DISTINCT FROM NULL;

性能提示

-- 在WHERE列上使用索引
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_country ON users(country);

-- 避免在索引列上使用函数（会阻止索引使用）
-- 不好
SELECT * FROM users WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';

-- 好
SELECT * FROM users WHERE email = 'john@example.com';

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE status = 'active';

最佳实践

1. 使用适当的运算符

   -- 使用IN而不是多个OR
   SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');  -- 好
   SELECT * FROM users WHERE country = 'USA' OR country = 'UK' OR country = 'Canada';  -- 避免

2. 使用BETWEEN表示范围

   -- 使用BETWEEN
   SELECT * FROM products WHERE price BETWEEN 10 AND 100;  -- 好
   SELECT * FROM products WHERE price >= 10 AND price <= 100;  -- 也可以

3. 正确处理NULL

   -- 使用IS NULL
   SELECT * FROM users WHERE phone IS NULL;  -- 正确
   SELECT * FROM users WHERE phone = NULL;   -- 错误！总是返回false

4. 使用括号提高清晰度

   -- 清晰的优先级
   SELECT * FROM products 
   WHERE (category = 'Electronics' OR category = 'Books') 
       AND price > 50;

小结

WHERE子句对于过滤数据至关重要：

• 比较运算符：=、<>、<、>、<=、>=
• 范围：BETWEEN
• 列表：IN
• 模式：LIKE、ILIKE
• 逻辑：AND、OR、NOT
• NULL：IS NULL、IS NOT NULL
• 子查询：EXISTS、ANY、ALL