pg怎么写？pg 查询语法详解：从零构建 PostgreSQL 查询能力体系

WHERE 条件详解

WHERE 子句是查询性能的首要战场。合理使用条件可显著减少扫描行数。

常用操作符

• 比较：=, ≠ (!= 或 <>), >, <, >=, <=
• 范围：BETWEEN x AND y（含边界）
• 集合：IN (a, b, c)，等价于 = a OR = b OR = c
• 模糊匹配：LIKE（% 任意字符，_ 单字符）；推荐使用 ILIKE 忽略大小写
• 空值：IS NULL / IS NOT NULL
• 正则：~（区分大小写）、~（不区分）

实战示例：订单状态查询

-- 查找近7天内“已支付”或“已发货”的订单
SELECT order_id, customer_id, status, created_at
FROM orders
WHERE created_at >= current_date - 7
  AND status IN ('PAID', 'SHIPPED')
  AND customer_id IS NOT NULL;

排序与分页

-- 按金额降序，同金额按时间升序
SELECT order_id, customer_id, amount, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY amount DESC, created_at ASC
LIMIT 20 -- 分页第1页

⚠️ 分页陷阱：OFFSET 过大导致性能下降

当 OFFSET = 100000 时，数据库仍需扫描前10万行再丢弃——效率极低。推荐使用“基于游标”的分页：

-- 第一页最后一条记录的 created_at = '2024-03-01 14:22:33'
SELECT order_id, amount, created_at
FROM orders
WHERE created_at < '2024-03-01 14:22:33'  -- 用最后一条记录的时间
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

JOIN 性能优化关键点

• 驱动表选择： 小表驱动大表（INNER JOIN 时优化器自动处理；LEFT JOIN 时左表必为驱动表）
• 索引设计： 关联字段必须建索引（如 orders(customer_id)）
• 避免 SELECT ：只取必要字段，减少数据传输量
• 拆分复杂 JOIN： 当 JOIN 表数 > 4 时，建议用 CTE 分步处理

反例：性能灾难式 JOIN

SELECT COUNT(1)
FROM orders o, customers c, products p, order_items oi, warehouses w
WHERE o.customer_id = c.id
  AND o.id = oi.order_id
  AND oi.product_id = p.id
  AND p.warehouse_id = w.id
  AND c.region = 'East';

CTE：结构化查询的基石

CTE（Common Table Expression）用 WITH 定义临时结果集，使复杂查询清晰易读，且支持递归查询。

基本用法

WITH paid_orders AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  WHERE status = 'PAID'
  GROUP BY customer_id
)
SELECT c.name, p.total_spent
FROM customers c
JOIN paid_orders p ON c.id = p.customer_id
WHERE p.total_spent > 1000;

递归 CTE：组织架构查询

WITH RECURSIVE org_tree AS (
  -- 基础：CEO
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE name = 'CEO'
  UNION ALL
  -- 递归：子员工
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT REPEAT('  ', level) || name AS org_chart
FROM org_tree;

JSONB 查询：非结构化数据的结构化处理

PostgreSQL 的 JSONB 类型支持高效嵌套查询，是 pg怎么写 的差异化优势。

常用操作符

实战：订单元数据查询

-- 查找包含标签 "urgent" 且金额 > 500 的订单
SELECT order_id, data ->> 'customer' AS customer_name
FROM orders
WHERE data @> '{"amount":500}'  -- amount 字段 ≥ 500
  AND data -> 'tags' ? 'urgent';

Q3：为什么我的查询没走索引？

A： 常见原因：

• WHERE 条件中对字段使用函数（如 DATE(col)）
• 数据类型不匹配（如 VARCHAR = INT）
• 数据量太小（优化器认为全表扫描更快）
• 索引未覆盖 WHERE + ORDER BY + SELECT 字段

Q4：如何避免锁表？

使用 CONCURRENTLY 创建索引（不阻塞写入）：
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

Q5：MySQL 语法迁移到 PostgreSQL 需要注意什么？

A： 关键差异：