PostgreSQL 运算符

概述

运算符是用于执行操作的符号或关键字。PostgreSQL 支持多种类型的运算符，包括算术、比较、逻辑、字符串和其他特殊运算符。

算术运算符

基本算术运算

sql

-- 加法
SELECT 10 + 5 AS sum;  -- 15

-- 减法
SELECT 10 - 5 AS difference;  -- 5

-- 乘法
SELECT 10 * 5 AS product;  -- 50

-- 除法
SELECT 10 / 5 AS quotient;  -- 2
SELECT 10.0 / 3.0 AS decimal_division;  -- 3.333...

-- 取模（余数）
SELECT 10 % 3 AS remainder;  -- 1

-- 幂运算
SELECT 2 ^ 3 AS power;  -- 8

-- 平方根
SELECT |/ 25 AS square_root;  -- 5

-- 立方根
SELECT ||/ 27 AS cube_root;  -- 3

-- 阶乘
SELECT 5! AS factorial;  -- 120

实际应用示例

sql

-- 计算订单总额
SELECT 
    order_id,
    price * quantity AS subtotal,
    (price * quantity) * 0.08 AS tax,
    (price * quantity) * 1.08 AS total
FROM order_items;

-- 计算折扣价格
SELECT 
    product_name,
    price AS original_price,
    price * 0.8 AS discounted_price,
    price - (price * 0.8) AS savings
FROM products;

-- 计算百分比
SELECT 
    category,
    COUNT(*) AS count,
    COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products) AS percentage
FROM products
GROUP BY category;

比较运算符

基本比较

sql

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

-- 不等于
SELECT * FROM products WHERE price <> 50;
SELECT * FROM products WHERE price != 50;  -- 同上

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

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

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

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

比较运算符示例

sql

-- 查找特定价格范围的产品
SELECT * FROM products 
WHERE price >= 50 AND price <= 100;

-- 查找不在特定状态的订单
SELECT * FROM orders 
WHERE status <> 'cancelled';

-- 查找高评分产品
SELECT * FROM products 
WHERE rating >= 4.5;

逻辑运算符

AND 运算符

sql

-- 所有条件必须为真
SELECT * FROM users 
WHERE status = 'active' 
    AND country = 'USA' 
    AND age >= 18;

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

OR 运算符

sql

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

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

NOT 运算符

sql

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

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

-- NOT 与其他运算符
SELECT * FROM products 
WHERE NOT (price > 1000 OR discontinued = true);

组合逻辑运算符

sql

-- 使用括号控制优先级
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Computers') 
    AND price < 500 
    AND stock > 0;

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

字符串运算符

连接运算符

sql

-- 使用 || 连接字符串
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- 连接多个字符串
SELECT 'Order #' || order_id || ' - ' || status AS order_info FROM orders;

-- 使用 CONCAT 函数
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT CONCAT('Total: $', price) AS price_label FROM products;

模式匹配运算符

LIKE 运算符

sql

-- % 匹配任意字符
SELECT * FROM users WHERE name LIKE 'J%';  -- 以 J 开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- 以 @gmail.com 结尾
SELECT * FROM users WHERE name LIKE '%john%';  -- 包含 john

-- _ 匹配单个字符
SELECT * FROM users WHERE name LIKE '_ohn';  -- 匹配 John, Kohn 等

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

ILIKE 运算符（不区分大小写）

sql

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

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

SIMILAR TO 运算符

sql

-- SQL 标准的正则表达式
SELECT * FROM users WHERE name SIMILAR TO '(John|Jane)%';
SELECT * FROM products WHERE sku SIMILAR TO '[A-Z]{3}[0-9]{4}';

正则表达式运算符

sql

-- ~ 区分大小写的正则匹配
SELECT * FROM users WHERE name ~ '^J';
SELECT * FROM users WHERE email ~ '[0-9]+';

-- ~* 不区分大小写的正则匹配
SELECT * FROM users WHERE name ~* '^john';

-- !~ 不匹配（区分大小写）
SELECT * FROM users WHERE email !~ '@spam\.com$';

-- !~* 不匹配（不区分大小写）
SELECT * FROM users WHERE name !~* '^test';

范围运算符

BETWEEN 运算符

sql

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

-- 等同于
SELECT * FROM products WHERE price >= 10 AND price <= 100;

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

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

IN 运算符

sql

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

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

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

-- NOT IN
SELECT * FROM users WHERE country NOT IN ('Spam', 'Test');

NULL 运算符

IS NULL 和 IS NOT NULL

sql

-- 检查 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;

IS DISTINCT FROM

sql

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

-- 等同于
SELECT * FROM users WHERE email IS NOT NULL;

-- 比较两个可能为 NULL 的值
SELECT * FROM products 
WHERE old_price IS DISTINCT FROM new_price;

数组运算符

sql

-- 数组包含
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'sale'];

-- 数组被包含
SELECT * FROM products WHERE ARRAY['electronics'] <@ tags;

-- 数组重叠
SELECT * FROM products WHERE tags && ARRAY['new', 'featured'];

-- 数组连接
SELECT ARRAY[1,2,3] || ARRAY[4,5,6];  -- {1,2,3,4,5,6}

-- 数组元素访问
SELECT tags[1] FROM products;  -- 第一个元素

JSON 运算符

sql

-- 获取 JSON 字段
SELECT data->>'name' AS name FROM users;
SELECT data->'address'->>'city' AS city FROM users;

-- JSON 路径
SELECT data#>'{address,city}' AS city FROM users;

-- JSON 包含
SELECT * FROM users WHERE data @> '{"status": "active"}';

-- JSON 键存在
SELECT * FROM users WHERE data ? 'email';

位运算符

sql

-- 按位与
SELECT 5 & 3;  -- 1

-- 按位或
SELECT 5 | 3;  -- 7

-- 按位异或
SELECT 5 # 3;  -- 6

-- 按位非
SELECT ~5;  -- -6

-- 左移
SELECT 5 << 2;  -- 20

-- 右移
SELECT 20 >> 2;  -- 5

类型转换运算符

sql

-- :: 运算符
SELECT '100'::INTEGER;
SELECT '2024-01-15'::DATE;
SELECT NOW()::DATE;
SELECT 10::NUMERIC(10, 2);

-- CAST 函数
SELECT CAST('100' AS INTEGER);
SELECT CAST('2024-01-15' AS DATE);
SELECT CAST(price AS TEXT) FROM products;

子查询运算符

EXISTS 运算符

sql

-- 检查子查询是否返回行
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
);

ANY/SOME 运算符

sql

-- 大于任意值
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Books');

-- 等于任意值
SELECT * FROM users
WHERE country = ANY (ARRAY['USA', 'UK', 'Canada']);

ALL 运算符

sql

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

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

运算符优先级

从高到低：

::（类型转换）
[]（数组元素）
.（表/列名分隔符）
-（一元减号）
^（幂运算）
*, /, %（乘、除、取模）
+, -（加、减）
||（字符串连接）
BETWEEN, IN, LIKE, ILIKE, SIMILAR TO
<, >, =, <=, >=, <>（比较）
IS NULL, IS NOT NULL, IS DISTINCT FROM
NOT（逻辑非）
AND（逻辑与）
OR（逻辑或）

使用括号控制优先级

sql

-- 不使用括号
SELECT * FROM products WHERE price > 50 OR category = 'Books' AND stock > 0;
-- 等同于: WHERE price > 50 OR (category = 'Books' AND stock > 0)

-- 使用括号明确意图
SELECT * FROM products WHERE (price > 50 OR category = 'Books') AND stock > 0;

实际应用示例

示例 1：电商产品筛选

sql

SELECT 
    product_name,
    price,
    CASE 
        WHEN price < 50 THEN '低价'
        WHEN price BETWEEN 50 AND 200 THEN '中价'
        ELSE '高价'
    END AS price_range,
    stock > 0 AS in_stock
FROM products
WHERE (category IN ('Electronics', 'Computers') OR on_sale = true)
    AND price <= 1000
    AND rating >= 4.0
    AND NOT discontinued = true;

示例 2：用户数据分析

sql

SELECT 
    first_name || ' ' || last_name AS full_name,
    email,
    EXTRACT(YEAR FROM AGE(birth_date)) AS age,
    CASE 
        WHEN EXTRACT(YEAR FROM AGE(birth_date)) < 18 THEN '未成年'
        WHEN EXTRACT(YEAR FROM AGE(birth_date)) BETWEEN 18 AND 65 THEN '成年'
        ELSE '老年'
    END AS age_group
FROM users
WHERE status = 'active'
    AND email LIKE '%@company.com'
    AND created_at >= CURRENT_DATE - INTERVAL '1 year';

示例 3：订单统计

sql

SELECT 
    order_id,
    customer_id,
    total_amount,
    total_amount * 0.08 AS tax,
    total_amount * 1.08 AS total_with_tax,
    CASE 
        WHEN total_amount > 1000 THEN 'VIP'
        WHEN total_amount > 500 THEN 'Premium'
        ELSE 'Standard'
    END AS order_tier
FROM orders
WHERE status IN ('completed', 'shipped')
    AND order_date BETWEEN '2024-01-01' AND '2024-12-31'
    AND NOT (payment_method = 'cash' AND total_amount > 5000);

性能优化提示

sql

-- 1. 在常用运算符列上创建索引
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_users_status ON users(status);

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

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

-- 3. 使用适当的运算符
-- 使用 IN 而不是多个 OR
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

-- 4. 使用 EXPLAIN 分析查询
EXPLAIN ANALYZE
SELECT * FROM products WHERE price > 100 AND category = 'Electronics';

最佳实践

使用括号提高可读性：即使不需要，也使用括号使逻辑更清晰
选择正确的运算符：使用最适合任务的运算符
注意 NULL 处理：使用 IS NULL 而不是 = NULL
考虑性能：在常用列上创建索引
类型匹配：确保比较的数据类型匹配
使用标准 SQL：优先使用标准 SQL 运算符以提高可移植性

小结

PostgreSQL 运算符是构建查询的基础：

算术运算符：+, -, *, /, %, ^
比较运算符：=, <>, <, >, <=, >=
逻辑运算符：AND, OR, NOT
字符串运算符：||, LIKE, ILIKE, ~
范围运算符：BETWEEN, IN
NULL 运算符：IS NULL, IS NOT NULL
特殊运算符：EXISTS, ANY, ALL

理解和正确使用运算符对于编写高效的 SQL 查询至关重要。

PostgreSQL 表达式

表达式概述

表达式是 PostgreSQL 中值和运算符的组合，用于计算结果。表达式可以在 SELECT、WHERE、HAVING 和其他子句中使用。

条件表达式

CASE 表达式

简单 CASE 表达式

sql

-- 基本语法
SELECT 
    name,
    status,
    CASE status
        WHEN 'active' THEN '活跃'
        WHEN 'pending' THEN '待审核'
        WHEN 'suspended' THEN '已暂停'
        ELSE '未知'
    END AS status_cn
FROM users;

-- 实际应用
SELECT 
    product_name,
    category,
    CASE category
        WHEN 'Electronics' THEN '电子产品'
        WHEN 'Books' THEN '图书'
        WHEN 'Clothing' THEN '服装'
        ELSE '其他'
    END AS category_cn
FROM products;

搜索 CASE 表达式

sql

-- 基本语法
SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age >= 18 AND age < 65 THEN '成年'
        ELSE '老年'
    END AS age_group
FROM users;

-- 价格分级
SELECT 
    product_name,
    price,
    CASE 
        WHEN price < 50 THEN '低价'
        WHEN price >= 50 AND price < 200 THEN '中价'
        WHEN price >= 200 AND price < 1000 THEN '高价'
        ELSE '奢侈品'
    END AS price_tier
FROM products;

-- 订单优先级
SELECT 
    order_id,
    total_amount,
    customer_type,
    CASE 
        WHEN customer_type = 'VIP' THEN 1
        WHEN total_amount > 1000 THEN 2
        WHEN total_amount > 500 THEN 3
        ELSE 4
    END AS priority
FROM orders;

嵌套 CASE 表达式

sql

SELECT 
    product_name,
    category,
    price,
    stock,
    CASE 
        WHEN stock = 0 THEN '缺货'
        WHEN stock < 10 THEN 
            CASE 
                WHEN price > 500 THEN '低库存-高价值'
                ELSE '低库存-普通'
            END
        ELSE '库存充足'
    END AS stock_status
FROM products;

COALESCE 表达式

sql

-- 返回第一个非 NULL 值
SELECT COALESCE(phone, 'No phone') AS contact_phone FROM users;

-- 多个备选值
SELECT COALESCE(mobile, home_phone, work_phone, 'No contact') AS phone FROM users;

-- 实际应用
SELECT 
    name,
    COALESCE(email, 'no-email@example.com') AS email,
    COALESCE(phone, 'N/A') AS phone,
    COALESCE(address, 'No address provided') AS address
FROM users;

-- 计算中使用
SELECT 
    product_name,
    price,
    COALESCE(discount_price, price) AS final_price,
    price - COALESCE(discount_price, price) AS savings
FROM products;

NULLIF 表达式

sql

-- 如果两个值相等则返回 NULL
SELECT NULLIF(price, 0) FROM products;
SELECT NULLIF(email, '') FROM users;

-- 避免除以零
SELECT 
    total_sales,
    total_orders,
    total_sales / NULLIF(total_orders, 0) AS average_order_value
FROM sales_summary;

-- 实际应用
SELECT 
    product_name,
    old_price,
    new_price,
    NULLIF(old_price, new_price) AS price_changed
FROM products;

GREATEST 和 LEAST 表达式

sql

-- GREATEST 返回最大值
SELECT GREATEST(10, 20, 15) AS max_value;  -- 20
SELECT GREATEST(price, cost, 0) AS highest FROM products;

-- LEAST 返回最小值
SELECT LEAST(10, 20, 15) AS min_value;  -- 10
SELECT LEAST(stock, reorder_level) AS min_stock FROM products;

-- 实际应用
SELECT 
    product_name,
    price,
    competitor_price,
    LEAST(price, competitor_price) AS best_price,
    GREATEST(price, competitor_price) - LEAST(price, competitor_price) AS price_difference
FROM products;

窗口表达式

sql

-- ROW_NUMBER
SELECT 
    name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- RANK 和 DENSE_RANK
SELECT 
    name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

-- 聚合窗口函数
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS running_total,
    AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

表达式最佳实践

使用括号提高可读性：复杂表达式使用括号明确优先级
避免过度嵌套：深度嵌套的表达式难以维护
使用有意义的别名：为复杂表达式提供清晰的别名
注意 NULL 处理：使用 COALESCE 或 NULLIF 处理 NULL 值
考虑性能：复杂表达式可能影响查询性能
类型匹配：确保表达式中的数据类型兼容

表达式小结

PostgreSQL 表达式提供了强大的数据处理能力：

CASE 表达式：条件逻辑
COALESCE：NULL 值处理
NULLIF：条件 NULL 返回
GREATEST/LEAST：多值比较
窗口表达式：高级分析

掌握表达式的使用可以编写更灵活、更强大的 SQL 查询。

PostgreSQL 运算符 ​

概述 ​

算术运算符 ​

基本算术运算 ​

实际应用示例 ​

比较运算符 ​

基本比较 ​

比较运算符示例 ​

逻辑运算符 ​

AND 运算符 ​

OR 运算符 ​

NOT 运算符 ​

组合逻辑运算符 ​

字符串运算符 ​

连接运算符 ​

模式匹配运算符 ​

LIKE 运算符 ​

ILIKE 运算符（不区分大小写） ​

SIMILAR TO 运算符 ​

正则表达式运算符 ​

范围运算符 ​

BETWEEN 运算符 ​

IN 运算符 ​

NULL 运算符 ​

IS NULL 和 IS NOT NULL ​

IS DISTINCT FROM ​

数组运算符 ​

JSON 运算符 ​

位运算符 ​

类型转换运算符 ​

子查询运算符 ​

EXISTS 运算符 ​

ANY/SOME 运算符 ​

ALL 运算符 ​

运算符优先级 ​

使用括号控制优先级 ​

实际应用示例 ​

示例 1：电商产品筛选 ​

示例 2：用户数据分析 ​

示例 3：订单统计 ​

性能优化提示 ​

最佳实践 ​

小结 ​

PostgreSQL 表达式 ​

表达式概述 ​

条件表达式 ​

CASE 表达式 ​

简单 CASE 表达式 ​

搜索 CASE 表达式 ​

嵌套 CASE 表达式 ​

COALESCE 表达式 ​

NULLIF 表达式 ​

GREATEST 和 LEAST 表达式 ​

窗口表达式 ​

表达式最佳实践 ​

表达式小结 ​

PostgreSQL 运算符

概述

算术运算符

基本算术运算

实际应用示例

比较运算符

基本比较

比较运算符示例

逻辑运算符

AND 运算符

OR 运算符

NOT 运算符

组合逻辑运算符

字符串运算符

连接运算符

模式匹配运算符

LIKE 运算符

ILIKE 运算符（不区分大小写）

SIMILAR TO 运算符

正则表达式运算符

范围运算符

BETWEEN 运算符

IN 运算符

NULL 运算符

IS NULL 和 IS NOT NULL

IS DISTINCT FROM

数组运算符

JSON 运算符

位运算符

类型转换运算符

子查询运算符

EXISTS 运算符

ANY/SOME 运算符

ALL 运算符

运算符优先级

使用括号控制优先级

实际应用示例

示例 1：电商产品筛选

示例 2：用户数据分析

示例 3：订单统计

性能优化提示

最佳实践

小结

PostgreSQL 表达式

表达式概述

条件表达式

CASE 表达式

简单 CASE 表达式

搜索 CASE 表达式

嵌套 CASE 表达式

COALESCE 表达式

NULLIF 表达式

GREATEST 和 LEAST 表达式

窗口表达式

表达式最佳实践

表达式小结