PostgreSQL SELECT 语句

作者likuolei

PostgreSQL SELECT 语句完全指南

涵盖 基础查询、高级过滤、JOIN、子查询、CTE、窗口函数、分页、JSON/数组操作、性能优化 等全部场景。

一、基本语法

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...]
[LIMIT ...] [OFFSET ...];

二、核心示例表

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT,
    age INTEGER,
    department TEXT,
    salary NUMERIC(10,2),
    is_active BOOLEAN DEFAULT true,
    tags TEXT[],
    profile JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- 插入测试数据
INSERT INTO users (username, email, age, department, salary, tags, profile)
VALUES 
('alice', 'a@ex.com', 25, 'IT', 70000, '{"dev","lead"}', '{"level": 5}'),
('bob', 'b@ex.com', 30, 'HR', 50000, '{"hr"}', '{"level": 3}'),
('carol', 'c@ex.com', 28, 'IT', 80000, '{"dev","ai"}', '{"level": 6}'),
('dave', 'd@ex.com', 35, 'IT', 90000, '{"dev","lead"}', '{"level": 7}');

三、基础 SELECT

场景SQL
查询所有列SELECT * FROM users;
指定列SELECT username, email FROM users;
别名SELECT username AS name, age + 1 AS next_year FROM users;
去重SELECT DISTINCT department FROM users;
常量列SELECT username, 'active' AS status FROM users;

四、WHERE 过滤条件

-- 基础比较
SELECT * FROM users WHERE age > 28;

-- 逻辑运算
SELECT * FROM users WHERE department = 'IT' AND salary > 60000;

-- IN / NOT IN
SELECT * FROM users WHERE username IN ('alice', 'bob');

-- LIKE / ILIKE（忽略大小写）
SELECT * FROM users WHERE email ILIKE '%@ex.com';

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NULL;

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 25 AND 30;

-- 数组包含
SELECT * FROM users WHERE tags @> ARRAY['dev'];

-- JSON 字段
SELECT * FROM users WHERE profile->>'level' = '5';
SELECT * FROM users WHERE profile @> '{"level": 6}';

五、排序与分页

-- 排序
SELECT * FROM users ORDER BY salary DESC, age ASC;

-- 分页（传统 OFFSET）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 分页（Keyset，推荐，性能高）
SELECT * FROM users 
WHERE id > 100 
ORDER BY id 
LIMIT 10;

六、聚合函数 + GROUP BY

-- 统计
SELECT 
    department,
    COUNT(*) AS cnt,
    AVG(salary) AS avg_salary,
    MAX(age) AS oldest
FROM users 
GROUP BY department;

-- HAVING 过滤分组
SELECT department, COUNT(*) 
FROM users 
GROUP BY department 
HAVING COUNT(*) > 1;

七、JOIN 关联查询

-- 准备关联表
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name TEXT,
    manager TEXT
);
INSERT INTO departments (name, manager) VALUES ('IT', 'alice'), ('HR', 'bob');

-- INNER JOIN
SELECT u.username, d.name AS dept 
FROM users u 
JOIN departments d ON u.department = d.name;

-- LEFT JOIN
SELECT u.username, d.manager 
FROM users u 
LEFT JOIN departments d ON u.department = d.name;

-- 多表 JOIN
SELECT u.username, d.name, o.total 
FROM users u 
JOIN departments d ON u.department = d.name
LEFT JOIN orders o ON u.id = o.user_id;

八、子查询（Subquery）

-- 标量子查询
SELECT username 
FROM users 
WHERE salary > (SELECT AVG(salary) FROM users);

-- 列子查询（IN）
SELECT * FROM users 
WHERE department IN (SELECT name FROM departments WHERE manager IS NOT NULL);

-- 行子查询
SELECT * FROM users 
WHERE (department, salary) IN (('IT', 80000), ('HR', 50000));

九、公共表表达式（CTE）—— 推荐！

WITH active_it AS (
    SELECT * FROM users 
    WHERE department = 'IT' AND is_active = true
)
SELECT username, salary FROM active_it WHERE salary > 70000;

递归 CTE（树形结构）

-- 假设有推荐关系
ALTER TABLE users ADD COLUMN referrer_id BIGINT;
UPDATE users SET referrer_id = 1 WHERE username = 'bob';  -- bob 推荐 carol

WITH RECURSIVE user_tree AS (
    SELECT id, username, referrer_id, 1 AS level
    FROM users WHERE username = 'carol'
  UNION ALL
    SELECT u.id, u.username, u.referrer_id, ut.level + 1
    FROM users u
    JOIN user_tree ut ON u.id = ut.referrer_id
)
SELECT * FROM user_tree;

十、窗口函数（Window Functions）—— 强大！

-- 排名
SELECT 
    username,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM users;

-- 累计
SELECT 
    username,
    created_at,
    salary,
    SUM(salary) OVER (ORDER BY created_at) AS running_total
FROM users;

-- 移动平均
SELECT 
    username,
    salary,
    AVG(salary)::DECIMAL(10,2) OVER (ORDER BY created_at ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg
FROM users;

十一、JSON 与数组查询

-- JSON 字段提取
SELECT username, profile->>'level' AS level FROM users;

-- JSON 嵌套
SELECT profile#>('{settings,theme}') FROM users;

-- 数组查询
SELECT * FROM users WHERE tags @> ARRAY['dev', 'lead'];
SELECT username, tags[1] AS first_tag FROM users;

十二、集合操作

-- UNION（去重）
SELECT username FROM users WHERE department = 'IT'
UNION
SELECT manager FROM departments;

-- UNION ALL（不去重）
-- INTERSECT（交集）
-- EXCEPT（差集）

十三、EXISTS 与 NOT EXISTS

-- 比 IN 更快（大数据量）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

十四、性能优化技巧

技巧说明
**避免 SELECT ***明确列名
索引 WHERE/JOIN 列CREATE INDEX ON users(department);
EXPLAIN ANALYZE查看执行计划
Keyset 分页WHERE id > ? LIMIT 10
CTE 物化（PG12+）WITH cte AS MATERIALIZED (...)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE department = 'IT' AND salary > 70000;

十五、视图（View）—— 复用查询

CREATE VIEW it_high_salary AS
SELECT username, salary FROM users 
WHERE department = 'IT' AND salary > 70000;

-- 查询视图
SELECT * FROM it_high_salary;

十六、pgAdmin 查询工具

  1. 打开 Query Tool
  2. 编写 SQL → F5 执行
  3. 结果导出 CSV/JSON
  4. EXPLAIN 可视化执行计划

十七、一键生成复杂查询

-- 统计各部门平均薪资、最高薪资、活跃用户数
WITH stats AS (
    SELECT 
        department,
        COUNT(*) FILTER (WHERE is_active) AS active_cnt,
        AVG(salary) AS avg_salary,
        MAX(salary) AS max_salary
    FROM users 
    GROUP BY department
)
SELECT 
    d.name AS dept,
    s.active_cnt,
    ROUND(s.avg_salary::numeric, 2) AS avg_salary,
    s.max_salary
FROM stats s
JOIN departments d ON s.department = d.name
ORDER BY s.avg_salary DESC;

十八、常见错误与解决

错误原因解决
column "x" does not exist列名拼错\d users 检查
syntax error at or near "WHERE"缺少 FROM补上
aggregate function not allowed in WHERE聚合在 WHERE改用 HAVING
division by zero除以 0用 NULLIF(col, 0)

十九、最佳实践 Checklist

项目建议
明确列名不要 SELECT *
WHERE 索引列常用过滤列建索引
ORDER BY + LIMIT分页必备
RETURNINGINSERT 后获取 ID
CTE 拆解复杂逻辑可读性高
EXPLAIN每条慢查询必查

二十、快速上手：5 分钟掌握 SELECT

-- 1. 查所有
SELECT * FROM users;

-- 2. 过滤 IT 部门高薪
SELECT username, salary FROM users 
WHERE department = 'IT' AND salary > 70000;

-- 3. 排序
SELECT * FROM users ORDER BY salary DESC LIMIT 3;

-- 4. 统计
SELECT department, AVG(salary) FROM users GROUP BY department;

-- 5. 窗口排名
SELECT username, salary, 
       RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM users;

现在就动手：

  1. 查询 tags 包含 dev 且 salary > 70000 的用户
  2. 统计每个 department 的平均年龄
  3. 用窗口函数给 IT 部门薪资排名
  4. 用 CTE 找出 profile.level > 5 的用户

需要我生成：

  • 100 条测试数据 + 复杂查询练习？回复 测试数据
  • 慢查询分析 + 优化案例？回复 慢查询优化
  • Python 执行 SELECT 示例？回复 Python SELECT
  • 实时仪表盘 SQL？回复 仪表盘 SQL

随时告诉我！

类似文章

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注