PostgreSql学习第三篇
一、多表关系
1.1一对一关系
表A的一行只能对应表B的一行。例如：用户表与用户身份证信息表，用户表中的一条数据只能对应用户身份信息表中的一条数据。
1.1.1.建表
-- 用户表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 用户身份证信息表（主键即外键）
CREATE TABLE user_identity (
user_id INTEGER PRIMARY KEY
REFERENCES users (id) ON DELETE CASCADE,
id_number VARCHAR(18) NOT NULL,
real_name VARCHAR(50) NOT NULL,
issue_date DATE,
expiry_date DATE
);
user_identity.user_id既是主键又是外键，天然保证每个用户最多只有一条身份记录。- 无法插入
user_id不存在的记录。 ON DELETE CASCADE保证用户删除时其身份信息自动删除。
1.1.2.添加测试数据
BEGIN;
-- 插入 10 条用户记录
INSERT INTO users (username, email) VALUES
('zhangwei', 'zhangwei@example.com'),
('lina', 'lina@example.com'),
('wangfang', 'wangfang@example.com'),
('liuyang', 'liuyang@example.com'),
('chenming', 'chenming@example.com'),
('zhaoling', 'zhaoling@example.com'),
('huangjie', 'huangjie@example.com'),
('zhouqiang', 'zhouqiang@example.com'),
('wuxiu', 'wuxiu@example.com'),
('sunpeng', 'sunpeng@example.com');
-- 插入对应的 10 条身份证信息（user_id 与 users.id 一一对应）
INSERT INTO user_identity (user_id, id_number, real_name, issue_date, expiry_date) VALUES
(1, '110101199001011234', '张伟', '2015-01-01', '2035-01-01'),
(2, '110101199205152345', '李娜', '2016-03-15', '2036-03-15'),
(3, '110101198807213456', '王芳', '2014-07-21', '2034-07-21'),
(4, '110101199112034567', '刘洋', '2015-12-03', '2035-12-03'),
(5, '110101199406185678', '陈明', '2017-06-18', '2037-06-18'),
(6, '110101198903297890', '赵玲', '2013-09-29', '2033-09-29'),
(7, '110101199510118901', '黄杰', '2018-10-11', '2038-10-11'),
(8, '110101198602229012', '周强', '2012-02-22', '2032-02-22'),
(9, '110101199708039123', '吴秀', '2019-08-03', '2039-08-03'),
(10, '110101200011119234', '孙鹏', '2020-11-11', '2040-11-11');
COMMIT;
1.1.3.查询测试
select
u.id,
u.username,
ui.id_number
from
users as u
left join user_identity as ui on
u.id = ui.user_id
1.2.一对多关系
表A的一行能对应表B的多行。一个用户可以有多个手机号。
1.2.1.建表
CREATE TABLE user_phones (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL
REFERENCES users (id) ON DELETE CASCADE,
phone_number VARCHAR(20) NOT NULL UNIQUE, -- 手机号全局唯一，防止重复归属
phone_type VARCHAR(20) DEFAULT 'mobile', -- 类型：mobile / home / work / other
is_verified BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 为外键创建索引，提高关联查询性能（可选）
CREATE INDEX idx_user_phones_user_id ON user_phones (user_id);
1.2.2.添加测试数据
BEGIN;
INSERT INTO user_phones (user_id, phone_number, phone_type, is_verified) VALUES
(1, '13800001111', 'mobile', true),
(1, '13800002222', 'home', false),
(2, '13900003333', 'mobile', true),
(3, '13700004444', 'mobile', true),
(3, '13600005555', 'work', true),
(4, '13500006666', 'mobile', false),
(5, '13400007777', 'mobile', true),
(5, '13300008888', 'home', true),
(6, '13200009999', 'mobile', true),
(7, '13100001111', 'mobile', false),
(7, '13000002222', 'work', true),
(8, '18900003333', 'mobile', true),
(9, '18800004444', 'mobile', true),
(9, '18700005555', 'other', false),
(10, '18600006666', 'mobile', true);
COMMIT;
1.2.3.查询测试
select
u.id,
u.username,
up.phone_number,
up.phone_type
from
users as u
left join user_phones as up on
u.id = up.user_id;
1.3.多对多关系
表A的一行能对应表B的多行，B表中的一行能对应A表中的多行。一个用户可以有多个课程，一个课程有被多个用户学。
1.3.1.建表语句
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
description TEXT,
teacher VARCHAR(50),
credit NUMERIC(3,1) DEFAULT 0.0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user_courses (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL
REFERENCES users (id) ON DELETE CASCADE,
course_id INTEGER NOT NULL
REFERENCES courses (id) ON DELETE CASCADE,
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
progress INTEGER DEFAULT 0 CHECK (progress BETWEEN 0 AND 100),
score NUMERIC(5,2), -- 最终成绩，可空
UNIQUE (user_id, course_id) -- 防止重复选课
);
-- 为外键创建索引，提高关联查询性能
CREATE INDEX idx_user_courses_user_id ON user_courses (user_id);
CREATE INDEX idx_user_courses_course_id ON user_courses (course_id);
1.3.2.添加测试数据
BEGIN;
-- 插入课程数据
INSERT INTO courses (course_name, description, teacher, credit) VALUES
('数据库原理', '关系数据库设计与SQL', '张教授', 3.0),
('数据结构', '线性表、树、图等', '李教授', 3.5),
('操作系统', '进程管理、内存管理', '王教授', 3.0),
('计算机网络', 'TCP/IP、路由协议', '赵教授', 2.5),
('软件工程', '敏捷开发、UML建模', '孙教授', 2.0),
('Python编程', 'Python语法与项目实战', '周老师', 2.5);
-- 插入用户-课程关联数据（每个用户选2~4门课）
INSERT INTO user_courses (user_id, course_id, progress, score) VALUES
-- 用户1 (张伟) 选了3门
(1, 1, 90, 85.5),
(1, 2, 75, NULL),
(1, 3, 60, NULL),
-- 用户2 (李娜) 选了2门
(2, 1, 100, 92.0),
(2, 4, 80, NULL),
-- 用户3 (王芳) 选了4门
(3, 2, 45, NULL),
(3, 3, 30, NULL),
(3, 5, 70, NULL),
(3, 6, 50, NULL),
-- 用户4 (刘洋) 选了3门
(4, 1, 85, 78.0),
(4, 4, 90, 88.5),
(4, 6, 65, NULL),
-- 用户5 (陈明) 选了2门
(5, 2, 100, 95.0),
(5, 3, 80, NULL),
-- 用户6 (赵玲) 选了3门
(6, 1, 70, NULL),
(6, 5, 60, NULL),
(6, 6, 40, NULL),
-- 用户7 (黄杰) 选了2门
(7, 3, 100, 87.0),
(7, 4, 95, 90.0),
-- 用户8 (周强) 选了3门
(8, 2, 55, NULL),
(8, 4, 70, NULL),
(8, 5, 80, NULL),
-- 用户9 (吴秀) 选了2门
(9, 1, 100, 97.5),
(9, 6, 85, NULL),
-- 用户10 (孙鹏) 选了3门
(10, 2, 40, NULL),
(10, 3, 20, NULL),
(10, 5, 50, NULL);
COMMIT;
1.3.3.查询测试
-- 查看每个用户都学习了那些课
select
u.username,
c.course_name
from
users u
left join user_courses uc on
u.id = uc.user_id
left join courses c on
uc.course_id = c.id;
二、索引
1.建表
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
event_type TEXT NOT NULL,
created_at TIMESTAMP NOT NULL
);
2.插入模拟数据
-- 插入100万条数据
INSERT INTO events (user_id, event_type, created_at)
SELECT
(random() * 100000)::INTEGER,
CASE (random() * 4)::INTEGER
WHEN 0 THEN 'login'
WHEN 1 THEN 'logout'
WHEN 2 THEN 'purchase'
ELSE 'view'
END,
NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 1000000);
3.未加索引验证
explain analyze select * from events where user_id = 68902;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------+
Gather (cost=1000.00..13562.43 rows=11 width=26) (actual time=0.455..64.369 rows=10 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Parallel Seq Scan on events (cost=0.00..12561.33 rows=5 width=26) (actual time=11.687..50.041 rows=3 loops=3)|
Filter: (user_id = 68902) |
Rows Removed by Filter: 333330 |
Planning Time: 0.105 ms |
Execution Time: 64.405 ms |
4.添加索引
CREATE INDEX idx_events_user_id ON events(user_id);
5.加索引后验证
explain analyze select * from events where user_id = 68902;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------+
Bitmap Heap Scan on events (cost=4.51..47.37 rows=11 width=26) (actual time=0.097..0.120 rows=10 loops=1) |
Recheck Cond: (user_id = 68902) |
Heap Blocks: exact=10 |
-> Bitmap Index Scan on idx_events_user_id (cost=0.00..4.51 rows=11 width=0) (actual time=0.086..0.086 rows=10 loops=1)|
Index Cond: (user_id = 68902) |
Planning Time: 0.454 ms |
Execution Time: 0.187 ms |
6.其它类型索引
-- 显式执行或默认不写都是 B-Tree
CREATE INDEX idx_events_user_id ON events using btree (user_id);
-- 仅适用于 = 查询
CREATE INDEX idx_events_user_id ON events using hash (user_id);
-- 联合索引 遵循最左匹配原则
CREATE INDEX idx_events_user_type ON events using (user_id, event_type);
-- 假设有一个 arrays 字段（类型是 TEXT []）,使用gin索引
CREATE INDEX idx_events_arrays ON events using gin (arrays);
-- 假设有一个 json_data 字段（类型是 JSONB）,使用gin索引
CREATE INDEX idx_events_json_data ON events using gin (json_data jsonb_path_ops);
-- 假设有一个 unique_data 唯一
CREATE UNIQUE INDEX idx_events_unique_data ON events(unique_data);
7.索引查询快的原因
默认索引是 B-tree：所有叶子节点在同一深度，查询/插入/删除时间复杂度均为 O(log n)。一个3层的B-tree索引平均可索引约1.08亿行数据。索引能极大加速查询，但会拖慢数据插入、更新和删除的速度，因为索引本身也需要同步更新。
8.索引失效
- 模糊查询：like 查询时，左边字符不能%
- 函数操作：对等号左边的的索引列，进行函数操作
- 类型不匹配：隐式转换，索引列是数字，查询的是字符串
- 最左匹配：联合索引必须满足从最左侧字段开始匹配
三、事务
1.建表
-- 建表
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
balance DECIMAL(10, 2) NOT NULL
);
-- 插入测试数据
INSERT INTO accounts (name, balance) VALUES
('Alice', 1000.00),
('Bob', 500.00);
2.交易
-- 开启事务
BEGIN;
-- 事务内的查询（看到的是操作前的数据）
SELECT * FROM accounts WHERE name = 'Alice'; -- 余额 1000
-- 扣钱
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
-- 加钱
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
-- 事务内再次查询（看到的是修改后的暂存数据，但此时其他会话看不到）
SELECT * FROM accounts WHERE name = 'Alice'; -- 余额 900
-- 提交事务，数据永久生效
COMMIT;
四、锁
PostgreSQL的锁主要分为表级锁和行级锁。绝大多数情况下，数据库会自动为我们选择最合适的锁。
1. 表级锁 (Table-Level Locks)
PostgreSQL有八种表级锁。下面列出了其中几种最常见、最重要的：
| 锁模式 (Lock Mode) | 自动获取的场景 | 简要说明 |
|---|---|---|
| ACCESS SHARE | SELECT 命令 |
最轻量的锁，仅与ACCESS EXCLUSIVE冲突。普通的读操作只会加这个锁。 |
| ROW SHARE | SELECT ... FOR UPDATE/SHARE |
表示事务意图在表上获取行级锁，与EXCLUSIVE和ACCESS EXCLUSIVE冲突。 |
| ROW EXCLUSIVE | UPDATE, DELETE, INSERT, MERGE |
大多数修改数据的命令都会获取此锁，它会与SHARE、SHARE ROW EXCLUSIVE等锁冲突。 |
| SHARE UPDATE EXCLUSIVE | VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY |
保护表免受并发模式更改和VACUUM的影响。 |
| ACCESS EXCLUSIVE | DROP TABLE, TRUNCATE, REINDEX, LOCK TABLE的默认模式 |
限制最严格的锁，与所有其他锁冲突。它会阻塞包括普通SELECT在内的所有操作。 |
2. 行级锁 (Row-Level Locks)
行级锁与表级锁是独立的。有四种行级锁模式，其中最常见的是：
FOR UPDATE：最严格的模式，会阻塞其他事务对同一行的UPDATE、DELETE、SELECT FOR UPDATE等操作。FOR SHARE：共享锁，允许其他事务读取（SELECT），但阻止它们执行UPDATE、DELETE或SELECT FOR UPDATE。
3. 额外说明：“自动提交”模式
需要补充一点的是，PostgreSQL 默认开启了“自动提交”（autocommit）。在没有显式执行 BEGIN 开始一个事务块的情况下，每一条SQL语句都会被当作一个独立的事务，执行成功后立即提交。
4.查看有哪些锁
select
*
from
pg_locks l
join pg_stat_activity a on
l.pid = a.pid;
五、CTE与视图
1.CTE
CTE（Common Table Expression，公共表表达式） 是 SQL 中一种通过 WITH 关键字定义的临时命名查询结果集。你可以把它理解为一个在单次查询执行期间存在的“临时视图”或“查询变量”，能让复杂的 SQL 像写程序一样层层拆解，极大提升可读性。只能用一次。
with course_6_user_id as (
select user_id,course_id from user_courses uc where course_id = 6
)
select user_id from course_6_user_id;
2.视图
视图是一张虚拟表，他不存储具体数据而只存储sql。
create view v_user_course as
select
u.id as user_id,
u.username,
c.id as course_id,
c.course_name
from
user_courses uc
left join users as u on
uc.user_id = u.id
left join courses c on
uc.course_id = c.id;
select * from v_user_course where user_id = 1;
本文来自博客园，作者：TheLifelongLearner，转载请注明原文链接：https://www.cnblogs.com/The-Lifelong-Learner/p/21602902
浙公网安备 33010602011771号