PostgreSQL 练习题(12 题)

-- 假设有一张员工表:
-- employees(id, name, dept, salary)
-- 1) 查询全部列
SELECT * FROM employees;

-- 2) 只查姓名和工资(只取需要的列,不滥用 *)
SELECT name, salary FROM employees;

-- 3) 去重:列出所有部门
SELECT DISTINCT dept FROM employees;
-- 输出:
--  dept
-- ------
--  技术部
--  市场部
--  人事部

-- 查工资大于 8000 的技术部员工
SELECT name, salary
FROM employees
WHERE dept = '技术部' AND salary > 8000;

-- IN + BETWEEN:市场部或人事部,工资在 5000~9000 之间
SELECT name, dept, salary
FROM employees
WHERE dept IN ('市场部', '人事部')
  AND salary BETWEEN 5000 AND 9000;

-- 模糊匹配:姓名以"小"开头
SELECT name FROM employees WHERE name LIKE '小%';
-- % 匹配任意长度任意字符;_ 匹配单个字符;ILIKE 不区分大小写

-- 工资 Top 5
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- 分页:每页 5 条,取第 2 页(跳过前 5 条)
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

-- NULL 处理:工资未定(NULL)的排到最后
SELECT name, salary FROM employees
ORDER BY salary DESC NULLS LAST;
-- OFFSET 必须配合 ORDER BY,否则顺序无意义

-- 单条插入
INSERT INTO employees (name, dept, salary)
VALUES ('小明', '技术部', 9000);

-- 批量插入(一条语句插多行,比循环多次插入快得多)
INSERT INTO employees (name, dept, salary)
VALUES ('小红', '市场部', 8500),
       ('小刚', '技术部', 9500),
       ('小丽', '人事部', 7000);

-- INSERT ... SELECT:从另一张表复制
INSERT INTO employees_backup (name, dept, salary)
SELECT name, dept, salary FROM employees WHERE dept = '技术部';

-- 给技术部全员加薪 10%
UPDATE employees
SET salary = salary * 1.1
WHERE dept = '技术部';

-- 多列同时更新
UPDATE employees
SET dept = '研发部', salary = salary + 500
WHERE name = '小明';

-- ⚠️ 务必带 WHERE!漏了 WHERE 会更新全表
-- UPDATE employees SET salary = 0;  -- 灾难!所有人工资清零
-- 安全习惯:先用 SELECT 验证 WHERE 命中的行,再改成 UPDATE
SELECT name, dept, salary FROM employees WHERE dept = '技术部';

-- 删除工资低于 5000 的员工
DELETE FROM employees WHERE salary < 5000;

-- ⚠️ 同样必须带 WHERE!DELETE FROM employees; 会清空全表
-- 想清空全表又想保留结构:TRUNCATE 更快(直接释放数据页,不写 WAL)
TRUNCATE employees;

-- 事务里删除,出错可回滚——这是 UPDATE/DELETE 的安全网
BEGIN;
DELETE FROM employees WHERE dept = '人事部';
-- 看一眼还剩几条,不对就 ROLLBACK,对了就 COMMIT
SELECT count(*) FROM employees;
ROLLBACK;   -- 撤销删除,数据回来了

-- 按部门统计人数、平均工资、最高工资
-- 只保留人数 >= 3 的部门,按平均工资降序
SELECT
    dept,
    COUNT(*)           AS headcount,
    ROUND(AVG(salary), 2) AS avg_salary,
    MAX(salary)        AS max_salary
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 3
ORDER BY avg_salary DESC;

-- HAVING vs WHERE:
--   WHERE 在分组前过滤行(不能用聚合);
--   HAVING 在分组后过滤组(可以用聚合)。
-- 执行顺序:FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

-- 两张表:
-- customers(id, name, city)
-- orders(id, customer_id, amount, created_at)
-- 查每个订单的客户名、金额(内连接,只取能配上客户的订单)
SELECT c.name, c.city, o.amount, o.created_at
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.amount > 100
ORDER BY o.amount DESC;

-- LEFT JOIN:列出所有客户,即使没下过订单(没单的 amount 为 NULL)
SELECT c.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total DESC;
-- COALESCE(x, 0):x 为 NULL 时取 0,常用于 LEFT JOIN 后的聚合兜底

-- users(id, name, profile JSONB)
-- profile 形如 {"role":"admin","age":30,"tags":["python","sql"]}

-- 1. ->> 取 JSON 字段为文本,能直接和字符串比较
SELECT name FROM users WHERE profile ->> 'role' = 'admin';

-- 2. @> 包含查询:tags 数组里含 'python'(命中 GIN 索引,极快)
SELECT name FROM users WHERE profile @> '{"tags":["python"]}';

-- 3. ? 检查 key 是否存在
SELECT name FROM users WHERE profile ? 'age';

-- 4. 修改 JSONB(生成新值,JSONB 不可变)
UPDATE users SET profile = jsonb_set(profile, '{age}', '31')
WHERE name = '小明';

-- 5. 加索引:GIN 索引让 @> / ? 查询飞起来(核心优化手段)
CREATE INDEX idx_users_profile ON users USING GIN (profile);
-- JSONB 兼具文档库的灵活和关系库的事务,这是 Postgres 比 MySQL 强的地方

-- 按部门分组,工资降序排名(每位员工都能看到自己名次)
SELECT
    name,
    dept,
    salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank,
    salary - AVG(salary) OVER (PARTITION BY dept) AS diff_from_avg
FROM employees;

-- 三种排名函数的区别:
--   ROW_NUMBER():1,2,3,4(永不重复,即使并列)
--   RANK():       1,2,2,4(并列后跳号)
--   DENSE_RANK(): 1,2,2,3(并列不跳号)
--
-- OVER (...) 定义"窗口":PARTITION BY 分组,ORDER BY 排序
-- 窗口函数不合并行(不像 GROUP BY),原行数不变,只是多一列排名
-- 这是分析报表的核心工具:Top N per group、累计求和、移动平均都能做

-- 找每个部门工资最高的员工
-- 用 CTE + 窗口函数,逻辑分两步,可读性远高于嵌套子查询
WITH ranked AS (
    SELECT
        name, dept, salary,
        RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS r
    FROM employees
)
SELECT dept, name, salary
FROM ranked
WHERE r = 1;

-- 多个 CTE 用逗号分隔,后一个能引用前一个(像管道)
WITH dept_avg AS (
    SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept
),
above_avg AS (
    SELECT e.name, e.dept, e.salary
    FROM employees e
    JOIN dept_avg d ON e.dept = d.dept
    WHERE e.salary > d.avg_sal
)
SELECT dept, count(*) AS high_performers FROM above_avg
GROUP BY dept ORDER BY high_performers DESC;
-- CTE 让复杂查询像读"过程式脚本",推荐用它替代深层嵌套子查询

-- 1. UPSERT:存在则更新,不存在则插入(用唯一约束判定冲突)
--   要求 name 列有 UNIQUE 约束
INSERT INTO employees (name, dept, salary)
VALUES ('小明', '技术部', 10000)
ON CONFLICT (name) DO UPDATE
SET salary = EXCLUDED.salary;     -- EXCLUDED 指代"本打算插入的新值"

-- 2. 事务:转账,两条 UPDATE 要么都成功要么都失败
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 检查无误后提交;任一步出错用 ROLLBACK 全部撤销
COMMIT;
-- 或:ROLLBACK;

-- 3. MVCC(多版本并发控制):每个事务看到一个一致的数据快照
--    读不阻塞写、写不阻塞读——这是 Postgres 高并发的根基
--    隔离级别默认 READ COMMITTED,可设 SERIALIZABLE 最严格
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  -- 在此隔离级别下,并发执行的"等价串行化"事务不会互相干扰
  SELECT count(*) FROM employees;
COMMIT;