PostgreSQL 常用 SELECT 数据查询命令

SELECT 是 PostgreSQL（以及所有 SQL 数据库）中最核心、最常用的数据查询命令。作为 Java 后端开发程序员，掌握 SELECT 的各种用法，不仅能写出高效的 SQL，还能减少应用层的数据处理逻辑，提升系统性能。

下面我将从基础到高级，为你系统讲解 SELECT 命令的语法结构，并提供具有实际开发参考意义的示例，每个示例都附有中文注释说明，适合你在项目中直接参考使用。

---

🧩 一、SELECT 命令基本语法结构

SELECT [DISTINCT] 列名 | *
FROM 表名
[WHERE 条件]
[GROUP BY 分组字段]
[HAVING 分组后筛选条件]
[ORDER BY 排序字段 [ASC|DESC]]
[LIMIT 数量] [OFFSET 偏移量];

⚠️ 方括号 [] 表示可选部分

---

📚 二、实际开发示例（从简单到复杂）

✅ 示例 1：基础查询 —— 查询所有用户信息

-- 查询 users 表中所有字段的所有数据
-- 适用于：调试、导出全量数据
SELECT * FROM users;

💡 实际开发中不建议在接口中使用 SELECT *，应明确指定字段以提高性能和可维护性。

---

✅ 示例 2：指定字段查询 —— 只查用户名和邮箱

-- 只查询用户名和邮箱，避免传输不必要的字段（如密码哈希）
-- 适用于：用户列表接口
SELECT username, email, created_at 
FROM users 
ORDER BY created_at DESC;  -- 按创建时间倒序排列

✅ 推荐做法：接口返回 DTO 字段应与查询字段一致。

---

✅ 示例 3：条件查询 —— 查找特定用户

-- 根据用户名精确查找用户（登录验证）
-- 适用于：用户登录、权限校验
SELECT id, username, email, profile 
FROM users 
WHERE username = 'zhangsan';

-- 或者根据邮箱查找（注册去重）
SELECT id FROM users WHERE email = 'zhangsan@example.com';

🔍 提示：确保 username 和 email 字段有唯一索引。

---

✅ 示例 4：模糊查询 —— 支持中文搜索

-- 模糊搜索用户名或昵称（支持中文）
-- 适用于：后台管理用户搜索功能
SELECT id, username, profile->>'nick' AS nickname, email 
FROM users 
WHERE username ILIKE '%san%'           -- 不区分大小写匹配用户名
   OR profile->>'nick' ILIKE '%张%';   -- 搜索 JSON 字段中的昵称

📌 技巧：

• ILIKE：不区分大小写
• ->>：从 JSONB 字段提取文本值
• 配合 GIN 索引可加速 JSONB 模糊查询

---

✅ 示例 5：分页查询 —— 实现列表分页

-- 分页查询用户列表（每页 10 条，第 2 页）
-- 适用于：REST API 分页接口
SELECT id, username, email, created_at 
FROM users 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 10;  -- 第2页（跳过前10条，取10条）

-- 更现代的写法（PostgreSQL 8.4+ 支持）
-- LIMIT 10 OFFSET 10 等价于
-- OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

✅ 推荐：在 Spring Data JPA 中使用 Pageable，底层就是生成这种 SQL。

---

✅ 示例 6：去重查询 —— 统计不同状态的订单

-- 查询订单表中所有不同的状态（去重）
-- 适用于：前端下拉框选项加载
SELECT DISTINCT status 
FROM orders 
WHERE status IS NOT NULL;

输出示例：

   status   
------------
 pending
 paid
 shipped
 cancelled

---

✅ 示例 7：聚合查询 —— 统计订单总数和总金额

-- 按用户统计订单数量和总金额
-- 适用于：用户中心“我的订单”统计
SELECT 
    user_id,
    COUNT(*) AS order_count,           -- 订单总数
    SUM(amount) AS total_amount,       -- 总金额
    AVG(amount) AS avg_amount,         -- 平均金额
    MAX(created_at) AS last_order_time -- 最近下单时间
FROM orders 
WHERE status = 'paid'                  -- 只统计已支付订单
GROUP BY user_id 
HAVING COUNT(*) >= 1                   -- 至少有1笔订单
ORDER BY total_amount DESC 
LIMIT 5;                               -- 取消费最高的前5个用户

📊 这种查询可替代 Java 中的 Map<UserId, List<Order>> + Stream 处理。

---

✅ 示例 8：多表关联查询 —— 查询用户及其订单

-- 查询用户信息及其最近一笔订单
-- 适用于：用户详情页
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    o.id AS order_id,
    o.order_no,
    o.amount,
    o.status,
    o.created_at AS order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id 
    AND o.created_at = (
        -- 子查询：获取该用户的最新订单时间
        SELECT MAX(created_at) 
        FROM orders 
        WHERE user_id = u.id
    )
ORDER BY u.created_at DESC;

🔍 使用 LEFT JOIN 确保没有订单的用户也能显示。

---

✅ 示例 9：使用 CTE（公共表表达式）—— 复杂逻辑分步处理

-- 使用 CTE 查询“每个用户最近3笔订单”
-- 适用于：用户订单历史页面
WITH user_recent_orders AS (
    SELECT 
        o.user_id,
        u.username,
        o.order_no,
        o.amount,
        o.status,
        o.created_at,
        -- 为每个用户的订单按时间倒序编号
        ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC) AS rn
    FROM orders o
    JOIN users u ON o.user_id = u.id
    WHERE o.created_at >= NOW() - INTERVAL '90 days'  -- 近90天订单
)
-- 外层查询：只取每用户前3条
SELECT 
    user_id, username, order_no, amount, status, created_at
FROM user_recent_orders 
WHERE rn <= 3
ORDER BY username, created_at DESC;

✅ CTE 让复杂 SQL 更清晰，类似 Java 中的“中间变量”。

---

✅ 示例 10：窗口函数 —— 计算排名和累计值

-- 计算每个订单在其用户内的排名，以及累计消费
-- 适用于：用户消费分析报表
SELECT 
    user_id,
    order_no,
    amount,
    created_at,
    -- 当前用户订单排名
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_rank,
    -- 累计消费金额（从第一笔到当前笔）
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS cumulative_amount
FROM orders 
WHERE status = 'paid'
ORDER BY user_id, created_at;

📈 窗口函数是 PostgreSQL 的强大特性，可替代 Java 中的 for 循环累计逻辑。

---

✅ 示例 11：JSONB 查询 —— 查询订单中的商品信息

-- 假设 orders.items 是 JSONB 字段，存储商品列表
-- 查询包含“Java书”的订单
SELECT 
    id, order_no, amount, items 
FROM orders 
WHERE items @> '[{"name": "Java书"}]';  -- JSONB 包含指定结构

-- 提取第一个商品名称
SELECT 
    order_no,
    items->0->>'name' AS first_item_name  -- 取第1个商品的 name 字段
FROM orders 
WHERE items ? 'name';  -- ? 检查 key 是否存在（适用于数组）

💡 JSONB + GIN 索引 = 半结构化数据的高效查询。

---

✅ 示例 12：条件表达式 —— 返回状态中文描述

-- 将订单状态英文转换为中文，用于前端展示
-- 适用于：API 返回 DTO 映射
SELECT 
    order_no,
    amount,
    status,
    CASE 
        WHEN status = 'pending' THEN '待支付'
        WHEN status = 'paid' THEN '已支付'
        WHEN status = 'shipped' THEN '已发货'
        WHEN status = 'cancelled' THEN '已取消'
        ELSE '未知状态'
    END AS status_text,
    created_at
FROM orders 
ORDER BY created_at DESC;

✅ 避免在 Java 中用 if-else 转换状态，直接在 SQL 中处理更高效。

---

🎯 三、开发建议总结

场景	建议
接口查询	避免 SELECT *，明确字段
分页	使用 LIMIT + OFFSET 或 cursor-based pagination
模糊搜索	用 ILIKE + GIN 索引加速
统计分析	用 GROUP BY + HAVING 替代 Java Stream
多表查询	用 JOIN，避免 N+1 查询
复杂逻辑	用 CTE 或 窗口函数 提升可读性
JSON 字段	用 ->>, @>, ? 操作 JSONB
性能优化	配合 EXPLAIN ANALYZE 查看执行计划

---

📌 四、常见性能陷阱

错误写法	正确做法
SELECT * FROM users WHERE LOWER(username) = 'xxx'	建 LOWER(username) 函数索引 或 用 ILIKE
OFFSET 10000 LIMIT 10（深度分页慢）	改用 WHERE id > last_id LIMIT 10（游标分页）
WHERE profile->>'age' = '28'	为 profile 建 GIN 索引：CREATE INDEX idx_users_profile ON users USING GIN (profile);

---

✅ 总结

SELECT 不只是“查数据”，它是你构建高效后端系统的核心工具。掌握它，你就能：

• 减少 Java 层的数据处理
• 提升接口响应速度
• 写出更简洁、可维护的代码
• 应对复杂的业务查询需求

---

如果你需要，我可以为你生成：

• 一份“SELECT 常用模式速查表”
• 一个 Spring Boot 分页查询 + PostgreSQL 的完整示例
• CTE 和窗口函数的对比使用指南