🎯 本教程涵盖 SQL 所有核心语法，所有示例均可直接在 PostgreSQL 中运行。
建议你按顺序逐节学习，每个代码块都复制到 psql 或 DBeaver 中执行。
目录
- 环境准备 & 基础概念
- DDL — 数据定义语言
- 数据类型完整参考
- DML — 数据操作语言
- DQL — 数据查询语言（SELECT）
- WHERE 子句 & 条件过滤
- JOIN — 表连接
- 聚合函数 & GROUP BY
- 子查询 (Subquery)
- CTE — 公共表表达式
- 窗口函数 (Window Functions)
- 集合操作
- 视图 (View)
- 索引 (Index)
- 约束 (Constraints)
- 事务控制 (TCL)
- JSON / JSONB 操作
- 数组 (Array) 操作
- 字符串函数
- 日期时间函数
- 数学/数值函数
- 条件表达式
- 类型转换 (CAST)
- PL/pgSQL 函数 & 存储过程
- 触发器 (Trigger)
- DCL — 权限控制
- 序列 (Sequence)
- EXPLAIN & 性能优化
- 实用技巧 & 高级特性
- 综合练习项目
1. 环境准备 & 基础概念
创建练习数据库
-- 创建一个专门的练习数据库
CREATE DATABASE sql_tutorial;
-- 连接到该数据库 (在 psql 中)
\c sql_tutorial
SQL 语句基本规则
- SQL 语句以分号
; 结尾 - 关键字不区分大小写（建议大写）
- 标识符（表名、列名）建议小写+下划线命名
- 注释：
-- 单行注释，/* */ 多行注释
-- 这是单行注释
SELECT 1 + 1 AS result; -- 行内注释
/*
这是多行注释
可以跨行
*/
SELECT 'Hello, PostgreSQL!' AS greeting;
2. DDL — 数据定义语言
2.1 CREATE TABLE
-- 创建部门表
CREATE TABLE departments (
dept_id SERIAL PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL UNIQUE,
location VARCHAR(200),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建员工表（含外键）
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary NUMERIC(10, 2) CHECK (salary > 0),
hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
dept_id INT REFERENCES departments(dept_id),
is_active BOOLEAN DEFAULT TRUE
);
-- 创建项目表
CREATE TABLE projects (
project_id SERIAL PRIMARY KEY,
project_name VARCHAR(200) NOT NULL,
budget NUMERIC(12, 2),
start_date DATE,
end_date DATE,
status VARCHAR(20) DEFAULT 'active'
CHECK (status IN ('active', 'completed', 'cancelled'))
);
-- 创建员工-项目关联表 (多对多)
CREATE TABLE employee_projects (
emp_id INT REFERENCES employees(emp_id) ON DELETE CASCADE,
project_id INT REFERENCES projects(project_id) ON DELETE CASCADE,
role VARCHAR(50),
joined_at DATE DEFAULT CURRENT_DATE,
PRIMARY KEY (emp_id, project_id)
);
-- 创建订单表（用于后续练习）
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100),
product VARCHAR(100),
quantity INT CHECK (quantity > 0),
unit_price NUMERIC(10, 2),
order_date DATE DEFAULT CURRENT_DATE,
region VARCHAR(50)
);
2.2 ALTER TABLE
-- 添加列
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
-- 删除列
ALTER TABLE employees DROP COLUMN phone;
-- 重命名列
ALTER TABLE employees RENAME COLUMN email TO email_address;
-- 改回来
ALTER TABLE employees RENAME COLUMN email_address TO email;
-- 修改列类型
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12, 2);
-- 添加约束
ALTER TABLE employees ADD CONSTRAINT chk_hire_date
CHECK (hire_date >= '2000-01-01');
-- 删除约束
ALTER TABLE employees DROP CONSTRAINT chk_hire_date;
-- 设置/删除默认值
ALTER TABLE employees ALTER COLUMN is_active SET DEFAULT TRUE;
ALTER TABLE employees ALTER COLUMN is_active DROP DEFAULT;
-- 设置 NOT NULL
ALTER TABLE employees ALTER COLUMN last_name SET NOT NULL;
-- 重命名表
ALTER TABLE orders RENAME TO customer_orders;
ALTER TABLE customer_orders RENAME TO orders;
2.3 DROP & TRUNCATE
-- DROP TABLE — 完全删除表（结构+数据）
-- DROP TABLE IF EXISTS temp_table;
-- TRUNCATE — 清空数据，保留结构（比 DELETE 快）
-- TRUNCATE TABLE orders RESTART IDENTITY; -- 重置序列
-- TRUNCATE TABLE orders CASCADE; -- 级联清空关联表
2.4 CREATE TABLE AS / SELECT INTO
-- 从查询结果创建新表
CREATE TABLE employees_backup AS
SELECT * FROM employees;
-- SELECT INTO (效果相同)
-- SELECT * INTO employees_copy FROM employees;
-- 清理
DROP TABLE IF EXISTS employees_backup;
2.5 临时表
-- 临时表：仅在当前会话有效
CREATE TEMP TABLE temp_results (
id SERIAL,
value TEXT
);
INSERT INTO temp_results (value) VALUES ('test1'), ('test2');
SELECT * FROM temp_results;
-- 会话结束后自动销毁
DROP TABLE IF EXISTS temp_results;
3. 数据类型完整参考
数值类型
| 类型 |
大小 |
范围 |
用途 |
SMALLINT |
2 bytes |
-32768 ~ 32767 |
小范围整数 |
INTEGER / INT |
4 bytes |
-2147483648 ~ 2147483647 |
常用整数 |
BIGINT |
8 bytes |
很大 |
大整数 |
NUMERIC(p,s) / DECIMAL |
可变 |
任意精度 |
精确计算（金融） |
REAL |
4 bytes |
6位精度 |
浮点数 |
DOUBLE PRECISION |
8 bytes |
15位精度 |
高精度浮点 |
SERIAL |
4 bytes |
自增整数 |
主键常用 |
BIGSERIAL |
8 bytes |
自增大整数 |
大表主键 |
字符类型
| 类型 |
说明 |
CHAR(n) |
固定长度，不足补空格 |
VARCHAR(n) |
可变长度，最大 n |
TEXT |
无限制长度文本 |
日期时间类型
| 类型 |
说明 |
示例 |
DATE |
日期 |
'2026-02-15' |
TIME |
时间 |
'14:30:00' |
TIMESTAMP |
日期+时间 |
'2026-02-15 14:30:00' |
TIMESTAMPTZ |
带时区的时间戳 |
'2026-02-15 14:30:00+01' |
INTERVAL |
时间间隔 |
'2 hours 30 minutes' |
其他重要类型
| 类型 |
说明 |
BOOLEAN |
TRUE / FALSE / NULL |
UUID |
通用唯一标识符 |
JSON / JSONB |
JSON 数据（JSONB 更高效） |
ARRAY |
数组类型 |
BYTEA |
二进制数据 |
INET / CIDR |
IP 地址 |
ENUM |
枚举类型 |
TSVECTOR / TSQUERY |
全文检索 |
-- 数据类型测试
SELECT
1::SMALLINT AS small_int,
42::INTEGER AS int_val,
3.14::NUMERIC(5,2) AS decimal_val,
'hello'::VARCHAR(10) AS varchar_val,
TRUE::BOOLEAN AS bool_val,
'2026-02-15'::DATE AS date_val,
'14:30:00'::TIME AS time_val,
NOW()::TIMESTAMPTZ AS timestamp_val,
'2 hours'::INTERVAL AS interval_val,
gen_random_uuid() AS uuid_val;
创建 ENUM 类型
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE person_mood (
id SERIAL PRIMARY KEY,
name TEXT,
current_mood mood
);
INSERT INTO person_mood (name, current_mood) VALUES ('Alice', 'happy');
SELECT * FROM person_mood;
-- 清理
DROP TABLE person_mood;
DROP TYPE mood;
4. DML — 数据操作语言
4.1 INSERT
-- 插入部门数据
INSERT INTO departments (dept_name, location) VALUES
('Engineering', 'Building A'),
('Marketing', 'Building B'),
('Finance', 'Building C'),
('HR', 'Building D'),
('Research', 'Building A');
-- 插入员工数据
INSERT INTO employees (first_name, last_name, email, salary, hire_date, dept_id) VALUES
('Alice', 'Smith', 'alice@example.com', 95000.00, '2020-03-15', 1),
('Bob', 'Johnson', 'bob@example.com', 88000.00, '2019-07-20', 1),
('Charlie', 'Williams', 'charlie@example.com', 72000.00, '2021-01-10', 2),
('Diana', 'Brown', 'diana@example.com', 105000.00,'2018-11-05', 3),
('Eve', 'Davis', 'eve@example.com', 67000.00, '2022-06-01', 2),
('Frank', 'Miller', 'frank@example.com', 92000.00, '2020-09-12', 1),
('Grace', 'Wilson', 'grace@example.com', 78000.00, '2021-04-20', 4),
('Henry', 'Moore', 'henry@example.com', 115000.00,'2017-02-28', 3),
('Ivy', 'Taylor', 'ivy@example.com', 83000.00, '2023-01-15', 5),
('Jack', 'Anderson', 'jack@example.com', 70000.00, '2024-03-01', NULL);
-- 插入项目数据
INSERT INTO projects (project_name, budget, start_date, end_date, status) VALUES
('Project Alpha', 500000, '2024-01-01', '2025-06-30', 'completed'),
('Project Beta', 300000, '2024-06-01', '2025-12-31', 'active'),
('Project Gamma', 750000, '2025-01-01', '2026-12-31', 'active'),
('Project Delta', 200000, '2023-01-01', '2023-12-31', 'cancelled');
-- 插入员工-项目关联
INSERT INTO employee_projects (emp_id, project_id, role) VALUES
(1, 1, 'Lead'),
(1, 3, 'Advisor'),
(2, 1, 'Developer'),
(2, 2, 'Developer'),
(3, 2, 'Marketing Lead'),
(4, 3, 'Finance Manager'),
(5, 2, 'Marketing'),
(6, 1, 'Developer'),
(6, 3, 'Lead'),
(7, 3, 'HR Support');
-- 插入订单数据
INSERT INTO orders (customer_name, product, quantity, unit_price, order_date, region) VALUES
('Customer A', 'Widget', 10, 25.50, '2025-01-15', 'North'),
('Customer B', 'Gadget', 5, 45.00, '2025-01-20', 'South'),
('Customer A', 'Widget', 20, 25.50, '2025-02-10', 'North'),
('Customer C', 'Gizmo', 3, 99.99, '2025-02-15', 'East'),
('Customer B', 'Widget', 15, 25.50, '2025-03-01', 'South'),
('Customer D', 'Gadget', 8, 45.00, '2025-03-10', 'West'),
('Customer A', 'Gizmo', 2, 99.99, '2025-04-01', 'North'),
('Customer C', 'Widget', 25, 25.50, '2025-04-15', 'East'),
('Customer D', 'Gizmo', 1, 99.99, '2025-05-01', 'West'),
('Customer B', 'Gadget', 12, 45.00, '2025-05-20', 'South'),
('Customer A', 'Widget', 8, 25.50, '2025-06-01', 'North'),
('Customer E', 'Gadget', 6, 45.00, '2025-06-15', 'North');
4.2 INSERT ... ON CONFLICT (UPSERT)
-- UPSERT: 如果冲突则更新
INSERT INTO departments (dept_name, location)
VALUES ('Engineering', 'Building Z')
ON CONFLICT (dept_name)
DO UPDATE SET location = EXCLUDED.location;
-- 如果冲突则什么都不做
INSERT INTO departments (dept_name, location)
VALUES ('Engineering', 'Building X')
ON CONFLICT (dept_name)
DO NOTHING;
SELECT * FROM departments WHERE dept_name = 'Engineering';
4.3 INSERT ... RETURNING
-- 插入并返回生成的 ID
INSERT INTO departments (dept_name, location)
VALUES ('Legal', 'Building E')
RETURNING dept_id, dept_name;
4.4 UPDATE
-- 单条更新
UPDATE employees SET salary = 100000 WHERE emp_id = 1;
-- 条件更新
UPDATE employees SET salary = salary * 1.10
WHERE dept_id = 1 AND salary < 95000;
-- 使用子查询更新
UPDATE employees
SET dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Research')
WHERE emp_id = 10;
-- UPDATE ... RETURNING
UPDATE employees SET salary = salary + 5000
WHERE emp_id = 2
RETURNING emp_id, first_name, salary;
4.5 DELETE
-- 删除特定行
DELETE FROM orders WHERE order_id = 12;
-- 重新插入
INSERT INTO orders (customer_name, product, quantity, unit_price, order_date, region)
VALUES ('Customer E', 'Gadget', 6, 45.00, '2025-06-15', 'North');
-- DELETE ... RETURNING
DELETE FROM orders WHERE customer_name = 'Customer E'
RETURNING *;
-- 重新插入
INSERT INTO orders (customer_name, product, quantity, unit_price, order_date, region)
VALUES ('Customer E', 'Gadget', 6, 45.00, '2025-06-15', 'North');
5. DQL — 数据查询语言
5.1 SELECT 基础
-- 查询所有列
SELECT * FROM employees;
-- 查询指定列
SELECT first_name, last_name, salary FROM employees;
-- 使用别名
SELECT
first_name AS "名",
last_name AS "姓",
salary AS "年薪",
salary / 12 AS "月薪"
FROM employees;
-- DISTINCT — 去重
SELECT DISTINCT dept_id FROM employees;
SELECT DISTINCT region FROM orders;
-- 常量和表达式
SELECT
'Hello' AS greeting,
2 + 3 AS sum,
CURRENT_DATE AS today,
NOW() AS current_time;
5.2 ORDER BY — 排序
-- 升序（默认）
SELECT first_name, salary FROM employees ORDER BY salary;
-- 降序
SELECT first_name, salary FROM employees ORDER BY salary DESC;
-- 多列排序
SELECT first_name, dept_id, salary FROM employees
ORDER BY dept_id ASC, salary DESC;
-- 按表达式排序
SELECT first_name, last_name FROM employees
ORDER BY LENGTH(first_name || last_name) DESC;
-- 按列序号排序
SELECT first_name, salary FROM employees ORDER BY 2 DESC;
-- NULLS FIRST / NULLS LAST
SELECT first_name, dept_id FROM employees
ORDER BY dept_id NULLS LAST;
5.3 LIMIT & OFFSET — 分页
-- 取前 5 条
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
-- 跳过前 3 条，取 3 条（分页第2页）
SELECT * FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 3;
-- FETCH (SQL 标准写法)
SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;
-- 带 OFFSET 的 FETCH
SELECT * FROM employees ORDER BY salary DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
6. WHERE 子句 & 条件过滤
6.1 比较运算符
-- 等于
SELECT * FROM employees WHERE dept_id = 1;
-- 不等于
SELECT * FROM employees WHERE dept_id != 1;
SELECT * FROM employees WHERE dept_id <> 1;
-- 大于、小于、大于等于、小于等于
SELECT * FROM employees WHERE salary > 90000;
SELECT * FROM employees WHERE salary >= 90000;
SELECT * FROM employees WHERE salary < 80000;
SELECT * FROM employees WHERE hire_date <= '2020-12-31';
6.2 逻辑运算符
-- AND
SELECT * FROM employees
WHERE dept_id = 1 AND salary > 90000;
-- OR
SELECT * FROM employees
WHERE dept_id = 1 OR dept_id = 2;
-- NOT
SELECT * FROM employees
WHERE NOT is_active;
-- 组合
SELECT * FROM employees
WHERE (dept_id = 1 OR dept_id = 2) AND salary > 80000;
6.3 BETWEEN
SELECT * FROM employees
WHERE salary BETWEEN 70000 AND 90000;
SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31';
-- NOT BETWEEN
SELECT * FROM employees
WHERE salary NOT BETWEEN 70000 AND 90000;
6.4 IN / NOT IN
SELECT * FROM employees
WHERE dept_id IN (1, 2, 3);
SELECT * FROM employees
WHERE dept_id NOT IN (1, 2);
-- IN 与子查询
SELECT * FROM employees
WHERE dept_id IN (
SELECT dept_id FROM departments WHERE location = 'Building A'
);
6.5 LIKE / ILIKE — 模式匹配
-- % 匹配任意多个字符
SELECT * FROM employees WHERE first_name LIKE 'A%';
SELECT * FROM employees WHERE email LIKE '%@example.com';
-- _ 匹配单个字符
SELECT * FROM employees WHERE first_name LIKE '_o_';
-- ILIKE — 不区分大小写（PostgreSQL 特有）
SELECT * FROM employees WHERE first_name ILIKE 'alice';
-- NOT LIKE
SELECT * FROM employees WHERE first_name NOT LIKE 'A%';
6.6 正则表达式匹配
-- ~ 大小写敏感正则匹配
SELECT * FROM employees WHERE first_name ~ '^[A-D]';
-- ~* 不区分大小写
SELECT * FROM employees WHERE first_name ~* '^a';
-- !~ 不匹配
SELECT * FROM employees WHERE first_name !~ '^[A-D]';
-- SIMILAR TO (SQL 标准正则)
SELECT * FROM employees WHERE first_name SIMILAR TO '(A|B|C)%';
6.7 IS NULL / IS NOT NULL
SELECT * FROM employees WHERE dept_id IS NULL;
SELECT * FROM employees WHERE dept_id IS NOT NULL;
6.8 EXISTS
-- 查找有员工的部门
SELECT * FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
-- 查找没有员工的部门
SELECT * FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
6.9 ANY / ALL
-- ANY: 匹配任意一个
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 2);
-- ALL: 匹配全部
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2);
7. JOIN — 表连接
7.1 INNER JOIN
-- 只返回两表匹配的行
SELECT
e.first_name,
e.last_name,
e.salary,
d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
7.2 LEFT JOIN (LEFT OUTER JOIN)
-- 返回左表所有行，右表无匹配则为 NULL
SELECT
e.first_name,
e.last_name,
d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
7.3 RIGHT JOIN (RIGHT OUTER JOIN)
-- 返回右表所有行
SELECT
e.first_name,
d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
7.4 FULL OUTER JOIN
-- 返回两表所有行
SELECT
e.first_name,
d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;
7.5 CROSS JOIN
-- 笛卡尔积
SELECT
e.first_name,
p.project_name
FROM employees e
CROSS JOIN projects p
LIMIT 20; -- 限制输出
7.6 SELF JOIN
-- 自连接：找到同部门的员工对
SELECT
e1.first_name AS employee1,
e2.first_name AS employee2,
e1.dept_id
FROM employees e1
INNER JOIN employees e2
ON e1.dept_id = e2.dept_id
AND e1.emp_id < e2.emp_id
WHERE e1.dept_id IS NOT NULL;
7.7 NATURAL JOIN
-- 自动匹配同名列（生产中不推荐，可读性差）
SELECT * FROM employees NATURAL JOIN departments;
7.8 多表连接
-- 三表连接：员工 + 部门 + 项目
SELECT
e.first_name || ' ' || e.last_name AS employee_name,
d.dept_name,
p.project_name,
ep.role
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN employee_projects ep ON e.emp_id = ep.emp_id
JOIN projects p ON ep.project_id = p.project_id
ORDER BY d.dept_name, e.last_name;
7.9 LATERAL JOIN
-- LATERAL 允许子查询引用外部查询的列
SELECT
d.dept_name,
top_emp.first_name,
top_emp.salary
FROM departments d
LEFT JOIN LATERAL (
SELECT first_name, salary
FROM employees e
WHERE e.dept_id = d.dept_id
ORDER BY salary DESC
LIMIT 1
) top_emp ON TRUE;
8. 聚合函数 & GROUP BY
8.1 常用聚合函数
SELECT
COUNT(*) AS total_employees,
COUNT(dept_id) AS with_department,
COUNT(DISTINCT dept_id) AS unique_departments,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
ROUND(STDDEV(salary), 2) AS salary_stddev
FROM employees;
8.2 GROUP BY
-- 按部门统计
SELECT
d.dept_name,
COUNT(e.emp_id) AS emp_count,
ROUND(AVG(e.salary), 2) AS avg_salary,
SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC;
-- 按多列分组
SELECT
region,
product,
SUM(quantity) AS total_qty,
SUM(quantity * unit_price) AS total_revenue
FROM orders
GROUP BY region, product
ORDER BY region, total_revenue DESC;
8.3 HAVING — 过滤分组
-- HAVING 在 GROUP BY 之后过滤
SELECT
d.dept_name,
COUNT(e.emp_id) AS emp_count,
ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) > 1
AND AVG(e.salary) > 75000
ORDER BY avg_salary DESC;
8.4 GROUPING SETS / ROLLUP / CUBE
-- GROUPING SETS: 多种分组维度
SELECT
region,
product,
SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY GROUPING SETS (
(region, product),
(region),
(product),
() -- 总计
)
ORDER BY region NULLS LAST, product NULLS LAST;
-- ROLLUP: 层级汇总
SELECT
region,
product,
SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY ROLLUP (region, product)
ORDER BY region NULLS LAST, product NULLS LAST;
-- CUBE: 所有组合
SELECT
region,
product,
SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY CUBE (region, product)
ORDER BY region NULLS LAST, product NULLS LAST;
8.5 聚合函数 + FILTER
-- PostgreSQL 特有的 FILTER 子句
SELECT
COUNT(*) AS total,
COUNT(*) FILTER (WHERE salary > 90000) AS high_salary_count,
AVG(salary) FILTER (WHERE dept_id = 1) AS eng_avg_salary,
SUM(salary) FILTER (WHERE hire_date >= '2021-01-01') AS new_hire_total
FROM employees;
9. 子查询 (Subquery)
9.1 标量子查询
-- 在 SELECT 中使用子查询
SELECT
first_name,
salary,
(SELECT AVG(salary) FROM employees) AS company_avg,
salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;
9.2 WHERE 中的子查询
-- 找出薪资高于平均的员工
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 找出所在部门平均薪资最高的员工
SELECT first_name, salary, dept_id
FROM employees
WHERE dept_id = (
SELECT dept_id FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
9.3 FROM 中的子查询（派生表）
-- 先统计每个部门，再筛选
SELECT * FROM (
SELECT
dept_id,
COUNT(*) AS cnt,
ROUND(AVG(salary), 2) AS avg_sal
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
) dept_stats
WHERE cnt > 1;
-- 找出每个部门中薪资最高的员工
SELECT e.first_name, e.salary, e.dept_id
FROM employees e
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e.dept_id
);
10. CTE — 公共表表达式
10.1 基本 CTE
-- WITH 定义临时结果集
WITH dept_stats AS (
SELECT
dept_id,
COUNT(*) AS emp_count,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
)
SELECT
d.dept_name,
ds.emp_count,
ds.avg_salary
FROM dept_stats ds
JOIN departments d ON ds.dept_id = d.dept_id
ORDER BY ds.avg_salary DESC;
10.2 多 CTE 链式调用
WITH
revenue_by_region AS (
SELECT
region,
SUM(quantity * unit_price) AS total_revenue
FROM orders
GROUP BY region
),
avg_revenue AS (
SELECT AVG(total_revenue) AS avg_rev FROM revenue_by_region
)
SELECT
r.region,
r.total_revenue,
a.avg_rev,
CASE WHEN r.total_revenue > a.avg_rev THEN 'Above Average'
ELSE 'Below Average' END AS performance
FROM revenue_by_region r
CROSS JOIN avg_revenue a
ORDER BY r.total_revenue DESC;
10.3 递归 CTE
-- 创建组织架构表
CREATE TABLE org_chart (
emp_id INT PRIMARY KEY,
name TEXT,
manager_id INT REFERENCES org_chart(emp_id)
);
INSERT INTO org_chart VALUES
(1, 'CEO', NULL),
(2, 'VP Engineering', 1),
(3, 'VP Marketing', 1),
(4, 'Tech Lead', 2),
(5, 'Senior Dev', 4),
(6, 'Junior Dev', 4),
(7, 'Marketing Manager', 3);
-- 递归遍历组织树
WITH RECURSIVE org_tree AS (
-- 基础条件: CEO (没有上级)
SELECT emp_id, name, manager_id, 1 AS level,
name::TEXT AS path
FROM org_chart
WHERE manager_id IS NULL
UNION ALL
-- 递归条件
SELECT oc.emp_id, oc.name, oc.manager_id, ot.level + 1,
ot.path || ' → ' || oc.name
FROM org_chart oc
INNER JOIN org_tree ot ON oc.manager_id = ot.emp_id
)
SELECT
REPEAT(' ', level - 1) || name AS org_hierarchy,
level,
path
FROM org_tree
ORDER BY path;
-- 递归生成数列
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;
-- 清理
DROP TABLE org_chart;
10.4 CTE 物化控制 (PostgreSQL 12+)
-- MATERIALIZED: 强制物化（计算一次并缓存）
WITH expensive AS MATERIALIZED (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
)
SELECT * FROM expensive WHERE avg_sal > 80000;
-- NOT MATERIALIZED: 内联到主查询（可能优化更好）
WITH simple AS NOT MATERIALIZED (
SELECT * FROM employees WHERE is_active = TRUE
)
SELECT * FROM simple WHERE salary > 90000;
11. 窗口函数 (Window Functions)
11.1 ROW_NUMBER / RANK / DENSE_RANK
SELECT
first_name,
dept_id,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
-- 分区排名：每个部门内排名
SELECT
first_name,
dept_id,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees
WHERE dept_id IS NOT NULL;
11.2 聚合窗口函数
SELECT
first_name,
dept_id,
salary,
SUM(salary) OVER (PARTITION BY dept_id) AS dept_total,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
COUNT(*) OVER (PARTITION BY dept_id) AS dept_count,
salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_dept_avg
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id, salary DESC;
11.3 累计计算 (Running Total)
SELECT
order_id,
customer_name,
order_date,
quantity * unit_price AS revenue,
SUM(quantity * unit_price)
OVER (ORDER BY order_date) AS running_total,
AVG(quantity * unit_price)
OVER (ORDER BY order_date) AS running_avg
FROM orders
ORDER BY order_date;
11.4 LAG / LEAD
-- LAG: 前一行 LEAD: 后一行
SELECT
order_id,
order_date,
quantity * unit_price AS revenue,
LAG(quantity * unit_price, 1) OVER (ORDER BY order_date) AS prev_revenue,
LEAD(quantity * unit_price, 1) OVER (ORDER BY order_date) AS next_revenue,
quantity * unit_price -
LAG(quantity * unit_price, 1) OVER (ORDER BY order_date) AS revenue_change
FROM orders
ORDER BY order_date;
11.5 FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT
first_name,
dept_id,
salary,
FIRST_VALUE(first_name) OVER (
PARTITION BY dept_id ORDER BY salary DESC
) AS highest_earner,
LAST_VALUE(first_name) OVER (
PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest_earner
FROM employees
WHERE dept_id IS NOT NULL;
11.6 NTILE
-- 将员工分成4组（四分位数）
SELECT
first_name,
salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;
11.7 PERCENT_RANK / CUME_DIST
SELECT
first_name,
salary,
ROUND(PERCENT_RANK() OVER (ORDER BY salary)::NUMERIC, 4) AS pct_rank,
ROUND(CUME_DIST() OVER (ORDER BY salary)::NUMERIC, 4) AS cume_dist
FROM employees;
11.8 Frame 子句详解
SELECT
order_id,
order_date,
quantity * unit_price AS revenue,
-- 移动平均 (当前行 + 前2行)
AVG(quantity * unit_price) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3,
-- 前后各1行的平均
AVG(quantity * unit_price) OVER (
ORDER BY order_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS centered_avg,
-- 从开头到当前行的总和
SUM(quantity * unit_price) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_sum
FROM orders
ORDER BY order_date;
11.9 命名窗口 (WINDOW 子句)
-- 避免重复定义窗口
SELECT
first_name,
dept_id,
salary,
ROW_NUMBER() OVER w AS rn,
RANK() OVER w AS rnk,
SUM(salary) OVER w AS running_sum
FROM employees
WHERE dept_id IS NOT NULL
WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC);
12. 集合操作
-- UNION: 合并去重
SELECT first_name FROM employees WHERE dept_id = 1
UNION
SELECT first_name FROM employees WHERE salary > 90000;
-- UNION ALL: 合并不去重
SELECT first_name FROM employees WHERE dept_id = 1
UNION ALL
SELECT first_name FROM employees WHERE salary > 90000;
-- INTERSECT: 交集
SELECT first_name FROM employees WHERE dept_id = 1
INTERSECT
SELECT first_name FROM employees WHERE salary > 90000;
-- EXCEPT: 差集
SELECT first_name FROM employees WHERE dept_id = 1
EXCEPT
SELECT first_name FROM employees WHERE salary > 90000;
13. 视图 (View)
13.1 普通视图
-- 创建视图
CREATE VIEW v_employee_details AS
SELECT
e.emp_id,
e.first_name || ' ' || e.last_name AS full_name,
e.salary,
d.dept_name,
e.hire_date
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- 使用视图
SELECT * FROM v_employee_details WHERE dept_name = 'Engineering';
-- 修改视图
CREATE OR REPLACE VIEW v_employee_details AS
SELECT
e.emp_id,
e.first_name || ' ' || e.last_name AS full_name,
e.salary,
e.salary / 12 AS monthly_salary,
d.dept_name,
e.hire_date,
e.is_active
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
SELECT * FROM v_employee_details;
13.2 物化视图 (Materialized View)
-- 物化视图: 存储查询结果，需手动刷新
CREATE MATERIALIZED VIEW mv_dept_summary AS
SELECT
d.dept_name,
COUNT(e.emp_id) AS emp_count,
ROUND(AVG(e.salary), 2) AS avg_salary,
SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;
-- 查询物化视图（速度很快）
SELECT * FROM mv_dept_summary;
-- 刷新物化视图
REFRESH MATERIALIZED VIEW mv_dept_summary;
-- 并发刷新（需要唯一索引）
CREATE UNIQUE INDEX idx_mv_dept ON mv_dept_summary (dept_name);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_summary;
13.3 删除视图
DROP VIEW IF EXISTS v_employee_details;
DROP MATERIALIZED VIEW IF EXISTS mv_dept_summary;
-- 重新创建（后续练习需要）
CREATE VIEW v_employee_details AS
SELECT
e.emp_id,
e.first_name || ' ' || e.last_name AS full_name,
e.salary,
d.dept_name,
e.hire_date
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
14. 索引 (Index)
14.1 B-tree 索引（默认）
-- 单列索引
CREATE INDEX idx_emp_salary ON employees (salary);
-- 多列索引
CREATE INDEX idx_emp_dept_salary ON employees (dept_id, salary);
-- 唯一索引
CREATE UNIQUE INDEX idx_emp_email ON employees (email);
-- 降序索引
CREATE INDEX idx_emp_salary_desc ON employees (salary DESC);
14.2 其他索引类型
-- 部分索引 (Partial Index)
CREATE INDEX idx_active_employees ON employees (salary)
WHERE is_active = TRUE;
-- 表达式索引 (Expression Index)
CREATE INDEX idx_emp_lower_name ON employees (LOWER(first_name));
-- GIN 索引 (适用于数组、JSONB、全文检索)
-- CREATE INDEX idx_json_data ON some_table USING GIN (json_column);
-- GiST 索引 (适用于几何数据、范围类型)
-- CREATE INDEX idx_geo ON geo_table USING GIST (location);
-- BRIN 索引 (适用于有序大表，如时间序列)
CREATE INDEX idx_orders_date_brin ON orders USING BRIN (order_date);
14.3 管理索引
-- 查看表的索引
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'employees';
-- 删除索引
DROP INDEX IF EXISTS idx_emp_salary;
DROP INDEX IF EXISTS idx_emp_dept_salary;
DROP INDEX IF EXISTS idx_emp_salary_desc;
DROP INDEX IF EXISTS idx_active_employees;
DROP INDEX IF EXISTS idx_emp_lower_name;
DROP INDEX IF EXISTS idx_orders_date_brin;
-- 重建索引
-- REINDEX INDEX idx_emp_email;
-- REINDEX TABLE employees;
15. 约束 (Constraints)
-- 约束示例表
CREATE TABLE constraint_demo (
id SERIAL PRIMARY KEY, -- 主键
username VARCHAR(50) NOT NULL UNIQUE, -- 非空+唯一
age INT CHECK (age >= 0 AND age <= 150), -- 检查约束
email VARCHAR(100),
score NUMERIC DEFAULT 0, -- 默认值
dept_id INT REFERENCES departments(dept_id) -- 外键
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT uq_email UNIQUE (email) -- 命名唯一约束
);
-- 排除约束 (EXCLUDE) — 用于范围不重叠
-- 需要 btree_gist 扩展
-- CREATE EXTENSION IF NOT EXISTS btree_gist;
-- CREATE TABLE room_booking (
-- room_id INT,
-- booking_period TSRANGE,
-- EXCLUDE USING GIST (room_id WITH =, booking_period WITH &&)
-- );
-- 清理
DROP TABLE constraint_demo;
外键引用动作
| ON DELETE / ON UPDATE |
说明 |
CASCADE |
级联删除/更新 |
SET NULL |
设为 NULL |
SET DEFAULT |
设为默认值 |
RESTRICT |
阻止操作 |
NO ACTION |
延迟检查（默认） |
16. 事务控制 (TCL)
-- 基本事务
BEGIN;
UPDATE employees SET salary = salary + 1000 WHERE emp_id = 1;
UPDATE employees SET salary = salary - 1000 WHERE emp_id = 2;
COMMIT;
-- 回滚事务
BEGIN;
DELETE FROM employees WHERE emp_id = 1;
-- 发现错误，回滚！
ROLLBACK;
-- SAVEPOINT
BEGIN;
UPDATE employees SET salary = 100000 WHERE emp_id = 1;
SAVEPOINT sp1;
UPDATE employees SET salary = 0 WHERE emp_id = 2; -- 糟糕！
ROLLBACK TO SAVEPOINT sp1; -- 只回滚到 sp1
UPDATE employees SET salary = 90000 WHERE emp_id = 2; -- 正确更新
COMMIT;
-- 事务隔离级别
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 默认
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
17. JSON / JSONB 操作
17.1 创建和插入
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
attrs JSONB
);
INSERT INTO products (name, attrs) VALUES
('Laptop', '{"brand": "Dell", "ram": 16, "storage": "512GB", "tags": ["work", "portable"]}'),
('Phone', '{"brand": "Apple", "ram": 8, "storage": "256GB", "tags": ["mobile", "5G"]}'),
('Tablet', '{"brand": "Samsung", "ram": 6, "storage": "128GB", "tags": ["portable", "reading"]}'),
('Desktop','{"brand": "HP", "ram": 32, "storage": "1TB", "tags": ["work", "gaming"]}');
17.2 JSON 操作符
-- -> 获取 JSON 对象（返回 JSON）
SELECT name, attrs -> 'brand' AS brand_json FROM products;
-- ->> 获取 JSON 值（返回 TEXT）
SELECT name, attrs ->> 'brand' AS brand_text FROM products;
-- #> 路径获取 (JSON)
SELECT name, attrs #> '{tags, 0}' AS first_tag FROM products;
-- #>> 路径获取 (TEXT)
SELECT name, attrs #>> '{tags, 0}' AS first_tag FROM products;
-- @> 包含检查
SELECT * FROM products WHERE attrs @> '{"brand": "Dell"}';
-- <@ 被包含检查
SELECT * FROM products WHERE '{"brand": "Apple"}' <@ attrs;
-- ? 键存在检查
SELECT * FROM products WHERE attrs ? 'ram';
-- ?| 任意一个键存在
SELECT * FROM products WHERE attrs ?| ARRAY['brand', 'color'];
-- ?& 所有键都存在
SELECT * FROM products WHERE attrs ?& ARRAY['brand', 'ram'];
-- || 合并 JSON
SELECT attrs || '{"color": "black"}' FROM products WHERE id = 1;
-- - 删除键
SELECT attrs - 'tags' FROM products WHERE id = 1;
-- #- 删除路径
SELECT attrs #- '{tags, 0}' FROM products WHERE id = 1;
17.3 JSONB 函数
-- jsonb_each: 展开键值对
SELECT p.name, kv.key, kv.value
FROM products p, jsonb_each(p.attrs) kv;
-- jsonb_each_text: 展开为文本
SELECT p.name, kv.key, kv.value
FROM products p, jsonb_each_text(p.attrs) kv;
-- jsonb_array_elements: 展开数组
SELECT p.name, elem
FROM products p, jsonb_array_elements(p.attrs -> 'tags') elem;
-- jsonb_object_keys: 获取所有键
SELECT DISTINCT jsonb_object_keys(attrs) FROM products;
-- jsonb_typeof: 获取类型
SELECT attrs ->> 'brand', jsonb_typeof(attrs -> 'brand'),
attrs ->> 'ram', jsonb_typeof(attrs -> 'ram')
FROM products LIMIT 1;
-- jsonb_set: 修改 JSON 值
UPDATE products
SET attrs = jsonb_set(attrs, '{ram}', '64')
WHERE name = 'Desktop';
-- jsonb_insert: 在路径插入值
SELECT jsonb_insert(attrs, '{tags, 0}', '"new_tag"')
FROM products WHERE id = 1;
-- jsonb_strip_nulls: 移除 null 值
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": 3}'::JSONB);
-- jsonb_pretty: 格式化输出
SELECT jsonb_pretty(attrs) FROM products WHERE id = 1;
-- 为 JSONB 创建 GIN 索引
CREATE INDEX idx_products_attrs ON products USING GIN (attrs);
-- 清理
DROP INDEX idx_products_attrs;
18. 数组 (Array) 操作
-- 数组列
CREATE TABLE array_demo (
id SERIAL PRIMARY KEY,
name TEXT,
scores INT[],
tags TEXT[]
);
INSERT INTO array_demo (name, scores, tags) VALUES
('Alice', ARRAY[85, 90, 92], ARRAY['math', 'science']),
('Bob', ARRAY[78, 82, 88], ARRAY['science', 'art']),
('Charlie', '{95, 88, 91}', '{math, art, music}');
-- 访问数组元素（索引从 1 开始！）
SELECT name, scores[1] AS first_score FROM array_demo;
-- 数组切片
SELECT name, scores[1:2] AS first_two FROM array_demo;
-- 数组长度
SELECT name, array_length(scores, 1) AS num_scores FROM array_demo;
-- 包含检查
SELECT * FROM array_demo WHERE 'math' = ANY(tags);
SELECT * FROM array_demo WHERE tags @> ARRAY['science'];
-- 重叠检查
SELECT * FROM array_demo WHERE tags && ARRAY['art', 'music'];
-- 数组追加
SELECT name, array_append(scores, 100) AS updated FROM array_demo;
-- 数组拼接
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
-- 数组移除
SELECT array_remove(ARRAY[1,2,3,2], 2);
-- unnest: 数组展开为行
SELECT name, unnest(tags) AS tag FROM array_demo;
-- array_agg: 行聚合为数组
SELECT dept_id, array_agg(first_name ORDER BY first_name) AS employees
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id;
-- 清理
DROP TABLE array_demo;
19. 字符串函数
SELECT
-- 拼接
'Hello' || ' ' || 'World' AS concat1,
CONCAT('Hello', ' ', 'World') AS concat2,
CONCAT_WS(', ', 'Alice', 'Bob', 'Charlie') AS concat_ws,
-- 大小写
UPPER('hello') AS upper_case,
LOWER('HELLO') AS lower_case,
INITCAP('hello world') AS title_case,
-- 长度
LENGTH('Hello') AS str_length,
CHAR_LENGTH('Hello') AS char_len,
OCTET_LENGTH('Hello') AS byte_len;
SELECT
-- 截取和位置
SUBSTRING('Hello World' FROM 1 FOR 5) AS substr1,
SUBSTR('Hello World', 7) AS substr2,
LEFT('Hello World', 5) AS left_str,
RIGHT('Hello World', 5) AS right_str,
POSITION('World' IN 'Hello World') AS pos,
STRPOS('Hello World', 'World') AS strpos;
SELECT
-- 修剪和填充
TRIM(' hello ') AS trimmed,
LTRIM(' hello') AS left_trimmed,
RTRIM('hello ') AS right_trimmed,
TRIM(BOTH 'x' FROM 'xxxhelloxxx') AS custom_trim,
LPAD('42', 5, '0') AS left_padded,
RPAD('hi', 10, '.') AS right_padded;
SELECT
-- 替换和反转
REPLACE('Hello World', 'World', 'PostgreSQL') AS replaced,
TRANSLATE('abc', 'abc', 'xyz') AS translated,
REVERSE('Hello') AS reversed,
REPEAT('ha', 3) AS repeated;
SELECT
-- 正则
REGEXP_REPLACE('abc 123 def 456', '[0-9]+', 'NUM', 'g') AS regex_replaced,
REGEXP_MATCHES('abc123def456', '[0-9]+', 'g') AS regex_matches;
-- SPLIT_PART: 按分隔符取部分
SELECT
SPLIT_PART('2026-02-15', '-', 1) AS year,
SPLIT_PART('2026-02-15', '-', 2) AS month,
SPLIT_PART('2026-02-15', '-', 3) AS day;
-- STRING_AGG: 字符串聚合
SELECT
dept_id,
STRING_AGG(first_name, ', ' ORDER BY first_name) AS employee_list
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id;
20. 日期时间函数
-- 获取当前时间
SELECT
CURRENT_DATE AS today,
CURRENT_TIME AS now_time,
CURRENT_TIMESTAMP AS now_ts,
NOW() AS now_func,
LOCALTIME AS local_time,
LOCALTIMESTAMP AS local_ts;
-- 日期提取
SELECT
EXTRACT(YEAR FROM TIMESTAMP '2026-02-15 14:30:00') AS year,
EXTRACT(MONTH FROM TIMESTAMP '2026-02-15 14:30:00') AS month,
EXTRACT(DAY FROM TIMESTAMP '2026-02-15 14:30:00') AS day,
EXTRACT(HOUR FROM TIMESTAMP '2026-02-15 14:30:00') AS hour,
EXTRACT(DOW FROM TIMESTAMP '2026-02-15 14:30:00') AS day_of_week,
EXTRACT(DOY FROM TIMESTAMP '2026-02-15 14:30:00') AS day_of_year,
EXTRACT(WEEK FROM TIMESTAMP '2026-02-15 14:30:00') AS week_num,
EXTRACT(QUARTER FROM TIMESTAMP '2026-02-15 14:30:00') AS quarter;
-- DATE_PART (类似 EXTRACT)
SELECT DATE_PART('year', CURRENT_DATE) AS year;
-- DATE_TRUNC: 截断到指定精度
SELECT
DATE_TRUNC('year', TIMESTAMP '2026-02-15 14:30:45') AS trunc_year,
DATE_TRUNC('month', TIMESTAMP '2026-02-15 14:30:45') AS trunc_month,
DATE_TRUNC('day', TIMESTAMP '2026-02-15 14:30:45') AS trunc_day,
DATE_TRUNC('hour', TIMESTAMP '2026-02-15 14:30:45') AS trunc_hour;
-- 日期算术
SELECT
CURRENT_DATE + 30 AS plus_30_days,
CURRENT_DATE - INTERVAL '3 months' AS minus_3_months,
CURRENT_TIMESTAMP + INTERVAL '2 hours 30 minutes' AS plus_time,
'2026-12-31'::DATE - CURRENT_DATE AS days_until_yearend;
-- AGE: 计算两个日期之间的差
SELECT
AGE(TIMESTAMP '2026-02-15', TIMESTAMP '1990-06-20') AS age_result,
AGE(CURRENT_DATE, '2020-01-01'::DATE) AS since_2020;
-- 日期格式化
SELECT
TO_CHAR(NOW(), 'YYYY-MM-DD') AS iso_date,
TO_CHAR(NOW(), 'DD/MM/YYYY') AS eu_date,
TO_CHAR(NOW(), 'Day, DD Month YYYY') AS full_date,
TO_CHAR(NOW(), 'HH24:MI:SS') AS time_24h,
TO_CHAR(NOW(), 'HH12:MI:SS AM') AS time_12h;
-- 字符串转日期
SELECT
TO_DATE('15-02-2026', 'DD-MM-YYYY') AS parsed_date,
TO_TIMESTAMP('2026/02/15 14:30', 'YYYY/MM/DD HH24:MI') AS parsed_ts;
-- GENERATE_SERIES: 生成日期序列
SELECT d::DATE AS date
FROM GENERATE_SERIES('2026-01-01'::DATE, '2026-01-10'::DATE, '1 day'::INTERVAL) d;
-- 实用: 按月统计订单
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS order_count,
SUM(quantity * unit_price) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
21. 数学/数值函数
SELECT
ABS(-42) AS absolute,
CEIL(4.2) AS ceiling,
FLOOR(4.8) AS floor_val,
ROUND(3.14159, 2) AS rounded,
TRUNC(3.14159, 2) AS truncated,
MOD(17, 5) AS modulo,
POWER(2, 10) AS power_val,
SQRT(144) AS square_root,
CBRT(27) AS cube_root,
LOG(100) AS log10,
LN(2.71828) AS natural_log,
EXP(1) AS e_value,
PI() AS pi_value,
SIGN(-42) AS sign_val,
DIV(17, 5) AS int_division,
GREATEST(1, 5, 3, 9, 2) AS greatest_val,
LEAST(1, 5, 3, 9, 2) AS least_val;
-- RANDOM: 随机数
SELECT RANDOM(); -- 0 到 1 之间
SELECT FLOOR(RANDOM() * 100 + 1)::INT; -- 1 到 100 之间
-- GENERATE_SERIES: 数值序列
SELECT * FROM GENERATE_SERIES(1, 10);
SELECT * FROM GENERATE_SERIES(0, 1, 0.1);
22. 条件表达式
22.1 CASE WHEN
-- 简单 CASE
SELECT
first_name,
salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 80000 THEN 'Mid-Level'
WHEN salary >= 60000 THEN 'Junior'
ELSE 'Entry'
END AS salary_level
FROM employees;
-- CASE 在聚合中使用
SELECT
dept_id,
COUNT(*) AS total,
COUNT(CASE WHEN salary >= 90000 THEN 1 END) AS high_salary,
COUNT(CASE WHEN salary < 90000 THEN 1 END) AS normal_salary
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id;
22.2 COALESCE
-- 返回第一个非 NULL 值
SELECT
first_name,
COALESCE(dept_id::TEXT, 'No Department') AS department
FROM employees;
22.3 NULLIF
-- 两个值相等时返回 NULL（常用于避免除零错误）
SELECT NULLIF(10, 10); -- 返回 NULL
SELECT NULLIF(10, 20); -- 返回 10
-- 避免除零
SELECT 100.0 / NULLIF(0, 0); -- 返回 NULL 而不是报错
22.4 GREATEST / LEAST
SELECT GREATEST(10, 20, 5, 30) AS max_val;
SELECT LEAST(10, 20, 5, 30) AS min_val;
23. 类型转换 (CAST)
-- CAST 语法
SELECT CAST('42' AS INTEGER);
SELECT CAST('2026-02-15' AS DATE);
SELECT CAST(3.14 AS TEXT);
-- PostgreSQL 简写 ::
SELECT '42'::INTEGER;
SELECT '2026-02-15'::DATE;
SELECT 3.14::TEXT;
SELECT 42::NUMERIC(10,2);
SELECT TRUE::INTEGER; -- 1
SELECT 'true'::BOOLEAN; -- true
-- 实用转换
SELECT
'100'::INT + 50 AS calc,
TO_NUMBER('$1,234.56', '$9,999.99') AS parsed_number;
24. PL/pgSQL 函数 & 存储过程
24.1 基本函数
-- 创建函数
CREATE OR REPLACE FUNCTION get_salary_level(emp_salary NUMERIC)
RETURNS TEXT AS $$
BEGIN
IF emp_salary >= 100000 THEN
RETURN 'Senior';
ELSIF emp_salary >= 80000 THEN
RETURN 'Mid-Level';
ELSIF emp_salary >= 60000 THEN
RETURN 'Junior';
ELSE
RETURN 'Entry';
END IF;
END;
$$ LANGUAGE plpgsql;
-- 使用函数
SELECT first_name, salary, get_salary_level(salary) AS level
FROM employees;
24.2 返回表的函数
CREATE OR REPLACE FUNCTION get_dept_employees(p_dept_id INT)
RETURNS TABLE(name TEXT, salary NUMERIC) AS $$
BEGIN
RETURN QUERY
SELECT first_name || ' ' || last_name, e.salary
FROM employees e
WHERE e.dept_id = p_dept_id;
END;
$$ LANGUAGE plpgsql;
-- 使用
SELECT * FROM get_dept_employees(1);
24.3 带 OUT 参数的函数
CREATE OR REPLACE FUNCTION get_salary_stats(
p_dept_id INT,
OUT min_sal NUMERIC,
OUT max_sal NUMERIC,
OUT avg_sal NUMERIC
) AS $$
BEGIN
SELECT MIN(salary), MAX(salary), ROUND(AVG(salary), 2)
INTO min_sal, max_sal, avg_sal
FROM employees
WHERE dept_id = p_dept_id;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_salary_stats(1);
24.4 存储过程 (PostgreSQL 11+)
-- 存储过程（可以包含事务控制）
CREATE OR REPLACE PROCEDURE transfer_employee(
p_emp_id INT,
p_new_dept_id INT
) AS $$
BEGIN
UPDATE employees
SET dept_id = p_new_dept_id
WHERE emp_id = p_emp_id;
-- 记录日志
RAISE NOTICE 'Employee % transferred to dept %', p_emp_id, p_new_dept_id;
END;
$$ LANGUAGE plpgsql;
-- 调用存储过程
CALL transfer_employee(10, 1);
-- 验证
SELECT first_name, dept_id FROM employees WHERE emp_id = 10;
24.5 循环和异常处理
CREATE OR REPLACE FUNCTION demo_loop_and_exception()
RETURNS TEXT AS $$
DECLARE
rec RECORD;
result TEXT := '';
total NUMERIC := 0;
BEGIN
-- FOR 循环
FOR rec IN SELECT first_name, salary FROM employees LIMIT 5 LOOP
result := result || rec.first_name || ': ' || rec.salary || E'\n';
total := total + rec.salary;
END LOOP;
result := result || 'Total: ' || total;
-- 异常处理
BEGIN
-- 尝试除零
PERFORM 1 / 0;
EXCEPTION
WHEN division_by_zero THEN
result := result || E'\n(Caught division by zero!)';
END;
RETURN result;
END;
$$ LANGUAGE plpgsql;
SELECT demo_loop_and_exception();
24.6 WHILE 循环 & 简单循环
CREATE OR REPLACE FUNCTION fibonacci(n INT)
RETURNS INT[] AS $$
DECLARE
fib INT[] := ARRAY[0, 1];
i INT := 2;
BEGIN
WHILE i < n LOOP
fib := array_append(fib, fib[i-1] + fib[i]);
i := i + 1;
END LOOP;
RETURN fib;
END;
$$ LANGUAGE plpgsql;
SELECT fibonacci(10);
24.7 清理函数
-- 查看所有自定义函数
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public';
-- 删除函数
DROP FUNCTION IF EXISTS get_salary_level(NUMERIC);
DROP FUNCTION IF EXISTS get_dept_employees(INT);
DROP FUNCTION IF EXISTS get_salary_stats(INT);
DROP PROCEDURE IF EXISTS transfer_employee(INT, INT);
DROP FUNCTION IF EXISTS demo_loop_and_exception();
DROP FUNCTION IF EXISTS fibonacci(INT);
25. 触发器 (Trigger)
25.1 审计日志触发器
-- 创建审计日志表
CREATE TABLE audit_log (
log_id SERIAL PRIMARY KEY,
table_name TEXT,
operation TEXT,
old_data JSONB,
new_data JSONB,
changed_at TIMESTAMP DEFAULT NOW(),
changed_by TEXT DEFAULT CURRENT_USER
);
-- 创建触发器函数
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation, new_data)
VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW));
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, operation, old_data)
VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD));
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
-- 在 employees 表上创建触发器
CREATE TRIGGER trg_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
-- 测试触发器
UPDATE employees SET salary = 96000 WHERE emp_id = 1;
-- 查看审计日志
SELECT * FROM audit_log;
25.2 数据验证触发器
CREATE OR REPLACE FUNCTION validate_salary()
RETURNS TRIGGER AS $$
BEGIN
-- 涨幅不能超过 50%
IF TG_OP = 'UPDATE' AND NEW.salary > OLD.salary * 1.5 THEN
RAISE EXCEPTION 'Salary increase cannot exceed 50%%. Old: %, New: %',
OLD.salary, NEW.salary;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_validate_salary
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION validate_salary();
-- 测试（这会报错）
-- UPDATE employees SET salary = 999999 WHERE emp_id = 1;
-- 清理触发器
DROP TRIGGER IF EXISTS trg_employees_audit ON employees;
DROP TRIGGER IF EXISTS trg_validate_salary ON employees;
DROP FUNCTION IF EXISTS audit_trigger_func();
DROP FUNCTION IF EXISTS validate_salary();
DROP TABLE IF EXISTS audit_log;
25.3 自动更新时间戳
CREATE OR REPLACE FUNCTION update_modified_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.modified_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 示例：给表添加 modified_at 列和触发器
ALTER TABLE employees ADD COLUMN modified_at TIMESTAMP;
CREATE TRIGGER trg_emp_modified
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION update_modified_at();
-- 测试
UPDATE employees SET salary = 96000 WHERE emp_id = 1;
SELECT emp_id, first_name, salary, modified_at FROM employees WHERE emp_id = 1;
-- 清理
DROP TRIGGER trg_emp_modified ON employees;
ALTER TABLE employees DROP COLUMN modified_at;
DROP FUNCTION update_modified_at();
26. DCL — 权限控制
-- 注意：以下命令需要超级用户权限
-- 创建用户
-- CREATE USER analyst WITH PASSWORD 'password123';
-- 授予权限
-- GRANT CONNECT ON DATABASE sql_tutorial TO analyst;
-- GRANT USAGE ON SCHEMA public TO analyst;
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
-- GRANT SELECT, INSERT ON employees TO analyst;
-- 撤销权限
-- REVOKE INSERT ON employees FROM analyst;
-- 创建角色
-- CREATE ROLE data_team;
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_team;
-- GRANT data_team TO analyst;
-- 查看权限
SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
ORDER BY table_name, grantee;
-- 删除用户
-- DROP USER analyst;
27. 序列 (Sequence)
-- 创建序列
CREATE SEQUENCE order_seq
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 999999
NO CYCLE;
-- 使用序列
SELECT nextval('order_seq'); -- 1000
SELECT nextval('order_seq'); -- 1001
SELECT currval('order_seq'); -- 当前值
-- 重置序列
ALTER SEQUENCE order_seq RESTART WITH 1000;
-- 在表中使用序列
-- CREATE TABLE custom_orders (
-- id INT DEFAULT nextval('order_seq') PRIMARY KEY,
-- ...
-- );
-- 查看所有序列
SELECT sequencename FROM pg_sequences WHERE schemaname = 'public';
-- 删除序列
DROP SEQUENCE order_seq;
28. EXPLAIN & 性能优化
28.1 EXPLAIN
-- 查看执行计划
EXPLAIN SELECT * FROM employees WHERE salary > 90000;
-- 查看详细执行计划（实际执行）
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 90000;
-- 查看更详细信息
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT e.first_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 80000;
-- JSON 格式输出（便于程序处理）
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM employees WHERE salary > 90000;
28.2 索引优化示例
-- 无索引
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_name = 'Customer A';
-- 添加索引
CREATE INDEX idx_orders_customer ON orders (customer_name);
-- 有索引
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_name = 'Customer A';
-- 清理
DROP INDEX idx_orders_customer;
28.3 常用性能命令
-- 更新统计信息
ANALYZE employees;
ANALYZE orders;
-- 清理死行
VACUUM employees;
-- 完整清理（锁表，回收空间）
-- VACUUM FULL employees;
-- 查看表大小
SELECT
relname AS table_name,
pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
pg_size_pretty(pg_relation_size(oid)) AS data_size,
pg_size_pretty(pg_indexes_size(oid)) AS index_size
FROM pg_class
WHERE relname IN ('employees', 'orders', 'departments', 'projects')
ORDER BY pg_total_relation_size(oid) DESC;
29. 实用技巧 & 高级特性
29.1 COPY — 导入导出
-- 导出到 CSV
-- COPY employees TO '/tmp/employees.csv' WITH CSV HEADER;
-- 导入 CSV
-- COPY employees FROM '/tmp/employees.csv' WITH CSV HEADER;
-- 用 \copy (psql 客户端命令，不需要超级用户)
-- \copy employees TO 'employees.csv' WITH CSV HEADER
-- \copy employees FROM 'employees.csv' WITH CSV HEADER
29.2 生成测试数据
-- 使用 generate_series 生成大量测试数据
CREATE TABLE test_data AS
SELECT
gs AS id,
'user_' || gs AS username,
(RANDOM() * 100000 + 30000)::NUMERIC(10,2) AS salary,
CURRENT_DATE - (RANDOM() * 1000)::INT AS hire_date,
(RANDOM() * 4 + 1)::INT AS dept_id
FROM generate_series(1, 10000) gs;
-- 验证
SELECT COUNT(*) FROM test_data;
SELECT * FROM test_data LIMIT 5;
-- 清理
DROP TABLE test_data;
29.3 条件聚合 (Pivot)
-- 交叉表 / 数据透视
SELECT
region,
SUM(CASE WHEN product = 'Widget' THEN quantity * unit_price ELSE 0 END) AS widget_revenue,
SUM(CASE WHEN product = 'Gadget' THEN quantity * unit_price ELSE 0 END) AS gadget_revenue,
SUM(CASE WHEN product = 'Gizmo' THEN quantity * unit_price ELSE 0 END) AS gizmo_revenue,
SUM(quantity * unit_price) AS total_revenue
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;
29.4 RETURNING 子句
-- INSERT + RETURNING
INSERT INTO departments (dept_name, location)
VALUES ('Operations', 'Building F')
RETURNING *;
-- UPDATE + RETURNING
UPDATE departments SET location = 'Building G'
WHERE dept_name = 'Operations'
RETURNING dept_id, dept_name, location;
-- DELETE + RETURNING
DELETE FROM departments WHERE dept_name = 'Operations'
RETURNING *;
29.5 DO 匿名代码块
-- 不创建函数直接执行 PL/pgSQL
DO $$
DECLARE
emp_count INT;
avg_sal NUMERIC;
BEGIN
SELECT COUNT(*), ROUND(AVG(salary), 2)
INTO emp_count, avg_sal
FROM employees;
RAISE NOTICE 'Total employees: %, Average salary: %', emp_count, avg_sal;
END;
$$;
29.6 全文检索
-- 基本全文检索
SELECT first_name, last_name
FROM employees
WHERE to_tsvector('english', first_name || ' ' || last_name)
@@ to_tsquery('english', 'alice | bob');
-- 带排名
SELECT
first_name,
ts_rank(
to_tsvector('english', first_name || ' ' || last_name),
to_tsquery('english', 'alice | bob')
) AS rank
FROM employees
WHERE to_tsvector('english', first_name || ' ' || last_name)
@@ to_tsquery('english', 'alice | bob')
ORDER BY rank DESC;
29.7 系统信息查询
-- PostgreSQL 版本
SELECT version();
-- 当前数据库
SELECT current_database();
-- 当前用户
SELECT current_user, session_user;
-- 数据库大小
SELECT pg_size_pretty(pg_database_size(current_database()));
-- 所有表信息
SELECT
table_name,
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;
-- 活动连接
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE datname = current_database();
29.8 常用 psql 元命令
\l -- 列出所有数据库
\dt -- 列出当前数据库所有表
\d table -- 描述表结构
\di -- 列出索引
\dv -- 列出视图
\df -- 列出函数
\dn -- 列出 schema
\du -- 列出用户/角色
\timing -- 开启/关闭查询计时
\x -- 切换扩展显示模式
\e -- 用编辑器编辑查询
\i file.sql -- 执行 SQL 文件
\q -- 退出
30. 综合练习项目
以下是一组综合练习题，运用你学到的所有知识：
练习 1: 数据分析查询
-- 找出每个部门薪资最高的员工
WITH ranked AS (
SELECT
e.*,
d.dept_name,
ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
)
SELECT first_name, last_name, dept_name, salary
FROM ranked
WHERE rn = 1;
练习 2: 月度趋势分析
-- 逐月收入变化率
WITH monthly AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month)) /
NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
) AS growth_pct
FROM monthly
ORDER BY month;
练习 3: 客户 RFM 分析
-- Recency, Frequency, Monetary
WITH rfm AS (
SELECT
customer_name,
CURRENT_DATE - MAX(order_date) AS recency_days,
COUNT(*) AS frequency,
SUM(quantity * unit_price) AS monetary
FROM orders
GROUP BY customer_name
)
SELECT
customer_name,
recency_days,
frequency,
ROUND(monetary, 2) AS monetary,
NTILE(3) OVER (ORDER BY recency_days) AS r_score,
NTILE(3) OVER (ORDER BY frequency DESC) AS f_score,
NTILE(3) OVER (ORDER BY monetary DESC) AS m_score
FROM rfm
ORDER BY monetary DESC;
练习 4: 复杂报表
-- 部门 + 项目交叉报表
SELECT
d.dept_name,
COUNT(DISTINCT e.emp_id) AS total_employees,
COUNT(DISTINCT ep.project_id) AS active_projects,
ROUND(AVG(e.salary), 2) AS avg_salary,
STRING_AGG(DISTINCT p.project_name, ', ') AS projects
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
LEFT JOIN employee_projects ep ON e.emp_id = ep.emp_id
LEFT JOIN projects p ON ep.project_id = p.project_id
GROUP BY d.dept_name
ORDER BY total_employees DESC;
练习 5: 清理所有练习对象
-- ⚠️ 只在你完成所有练习后运行
-- DROP VIEW IF EXISTS v_employee_details;
-- DROP TABLE IF EXISTS employee_projects;
-- DROP TABLE IF EXISTS orders;
-- DROP TABLE IF EXISTS employees;
-- DROP TABLE IF EXISTS projects;
-- DROP TABLE IF EXISTS products;
-- DROP TABLE IF EXISTS departments;
📌 SQL 语句执行顺序
理解 SQL 的逻辑执行顺序有助于写出正确的查询：
1. FROM / JOIN ← 确定数据源
2. WHERE ← 行级过滤
3. GROUP BY ← 分组
4. HAVING ← 组级过滤
5. SELECT ← 选择列 & 计算
6. DISTINCT ← 去重
7. ORDER BY ← 排序
8. LIMIT / OFFSET ← 分页
窗口函数在 SELECT 之后、ORDER BY 之前执行。
🎯 学习路线建议
- Week 1-2: 章节 1-6 (基础查询、过滤)
- Week 3: 章节 7-8 (JOIN、聚合)
- Week 4: 章节 9-11 (子查询、CTE、窗口函数) ← 重点！面试高频
- Week 5: 章节 12-16 (视图、索引、约束、事务)
- Week 6: 章节 17-23 (数据类型、函数)
- Week 7: 章节 24-25 (PL/pgSQL、触发器)
- Week 8: 章节 26-30 (优化、高级特性、综合项目)
💡 Tipp für die Praxis: 每学完一节，立即在你的 PostgreSQL 中运行所有代码，然后尝试修改参数、组合不同的语法来加深理解。
