PostgreSQL AND和OR运算符

概述

AND和OR运算符在WHERE子句中组合多个条件。AND要求所有条件都为真，OR要求至少一个条件为真。

AND运算符

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

-- 多个AND条件
SELECT * FROM products 
WHERE category = 'Electronics' 
    AND price > 100 
    AND stock > 0
    AND is_active = TRUE;

OR运算符

-- 至少一个条件必须为真
SELECT * FROM users WHERE country = 'USA' OR country = 'UK';

-- 多个OR条件
SELECT * FROM products 
WHERE category = 'Electronics' 
    OR category = 'Books' 
    OR category = 'Clothing';

组合AND和OR

优先级

AND的优先级高于OR，因此先计算AND条件：

-- 不使用括号
-- (category = 'Electronics' OR category = 'Books') AND price > 50
SELECT * FROM products 
WHERE category = 'Electronics' OR category = 'Books' AND price > 50;

-- 使用括号提高清晰度
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Books') 
    AND price > 50;

复杂条件

-- 复杂组合
SELECT * FROM users 
WHERE 
    (status = 'active' OR status = 'pending')
    AND (country = 'USA' OR country = 'Canada')
    AND created_at > '2024-01-01';

-- 三路组合
SELECT * FROM orders 
WHERE 
    (status = 'completed' OR status = 'shipped')
    AND total > 100
    AND created_at > '2024-01-01';

NOT与AND/OR

-- NOT与AND组合
SELECT * FROM users 
WHERE NOT (status = 'active' AND country = 'USA');

-- NOT与OR组合
SELECT * FROM products 
WHERE NOT (category = 'Electronics' OR category = 'Books');

-- 德摩根定律
-- NOT (A AND B) = NOT A OR NOT B
-- NOT (A OR B) = NOT A AND NOT B

实用示例

用户过滤

SELECT * FROM users 
WHERE 
    (is_verified = TRUE OR is_admin = TRUE)
    AND status = 'active'
    AND last_login > '2024-01-01';

产品搜索

SELECT * FROM products 
WHERE 
    (name ILIKE '%laptop%' OR name ILIKE '%computer%' OR name ILIKE '%pc%')
    AND (category = 'Electronics' OR category = 'Computers')
    AND (price BETWEEN 500 AND 2000 OR price IS NULL)
    AND stock > 0;

订单过滤

SELECT * FROM orders 
WHERE 
    (status = 'completed' OR status = 'delivered')
    AND (total > 50 OR discount > 0)
    AND created_at BETWEEN '2024-01-01' AND '2024-12-31';

小结

AND和OR运算符：

• AND：所有条件都必须为真
• OR：至少一个条件为真
• 优先级：AND先于OR
• 使用括号提高代码清晰度