pgsql多表关系

在真实的业务中，业务逻辑往往需要多张表紧密配合，表与表是有关系的一对一关系表 A 的一行只能对应表 B 的一行。通常用于将“核心高频数据”与“扩展低频数据”分离，提高查询效率示例：用户 (

在真实的业务中，业务逻辑往往需要多张表紧密配合，表与表是有关系的

一对一关系

表 A 的一行只能对应表 B 的一行。通常用于将“核心高频数据”与“扩展低频数据”分离，提高查询效率

示例：用户 (Users) 与用户详细信息 (User_Profiles)

在用户系统中，用户的账号密码是核心，而个人介绍、头像 URL、收货地址属于扩展信息。

-- 主表：核心账户
CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL UNIQUE
);

-- 扩展表：详细资料
CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY, -- 既是主键，也是外键
    avatar_url TEXT,
    bio TEXT,
    CONSTRAINT fk_user 
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

实现要点：在子表中，将外键字段设为PRIMARY KEY或UNIQUE，确保一个用户 ID 只能在 Profile 表里出现一次。

ON DELETE CASCADE(定义“连坐”逻辑) ——最核心部分

这是定义当**主表（users）**的数据被删除时，**从表（user_profiles）**该怎么办。

CASCADE** (级联)**：就像多米诺骨牌。如果 ID 为 1 的用户被删除了，数据库会自动、静默地把user_profiles里所有user_id = 1 的行也删掉。
为什么用它：在“一对一”或“一对多”关系中，如果主体（用户）都不存在了，相关的详细资料或头像信息也就没有存在的意义了。使用级联可以防止数据库堆积垃圾数据。

除了CASCADE，你有时也会看到这些写法：

选项	效果	适用场景
`RESTRICT` (默认)	如果用户还有资料，就不准删这个用户。	保护重要数据，防止误删。
`SET NULL`	用户删了，资料表里的`user_id`变成`NULL`。	用户注销了，但你想保留他的评论或痕迹。
`NO ACTION`	与 RESTRICT 类似，但在事务结束时检查。	复杂的事务处理。

插入数据

-- 第一步：插入核心用户
-- 我们利用 RETURNING 直接获取系统生成的自增 ID
INSERT INTO users (username) 
VALUES ('fengfeng') 
RETURNING id; 

-- 假设上面返回的 ID 是 1
-- 第二步：插入对应的详细资料
INSERT INTO user_profiles (user_id, avatar_url, bio) 
VALUES (1, 'https://example.com/avatar.png', '技术教育者，B站UP主');

-- 尝试再次为 ID 为 1 的用户插入资料（会报错！）
-- 报错信息：duplicate key value violates unique constraint "user_profiles_pkey"
-- 这正是我们设计成 PRIMARY KEY 的目的，保证了一对一的严格性。

查询数据正向联查和反向

select username, avatar_url from users join user_profiles up on users.id = up.user_id;

select avatar_url, username from user_profiles left join users u on u.id = user_profiles.user_id;

删除数据

设置CASCADE之后，删除用户之后，关联的用户详情也会删除

外键：一般业务里面都会去掉外键，使用逻辑外键

一对多关系

概念： 表 A 的一行可以对应表 B 的多行，但表 B 的一行只能属于表 A 的一个实体。

示例：女神与舔狗

一位“女神”可以拥有成千上万名“舔狗”，但一名“舔狗”在特定的一段时间内（业务逻辑上）往往只能死心塌地给一位“女神”打钱。

-- 1. 女神表 (一)
CREATE TABLE goddesses (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    star_sign TEXT -- 星座
);

-- 2. 舔狗表 (多)
CREATE TABLE simps (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    target_id BIGINT NOT NULL, -- 目标女神的ID (外键)
    nickname TEXT NOT NULL,    -- 舔狗昵称
    contributed_amount DECIMAL(10, 2) DEFAULT 0, -- 累计贡献(打钱)金额
    
    -- 外键约束：如果女神号注销了，舔狗记录也级联消失
    CONSTRAINT fk_goddess 
        FOREIGN KEY (target_id) REFERENCES goddesses(id) ON DELETE CASCADE
);

实现要点：在“多”的一方（simps ）添加一个字段存储“一”的一方（goddesses）的主键。

插入数据

-- 插入两位女神
INSERT INTO goddesses (name, star_sign) 
VALUES ('林青霞', '天蝎座'), ('王祖贤', '水瓶座') 
RETURNING id, name;
-- 假设 林青霞 ID=1, 王祖贤 ID=2

-- 为 林青霞 (ID: 1) 插入忠实粉丝
INSERT INTO simps (target_id, nickname, contributed_amount) VALUES 
(1, '阿强', 520.00),
(1, '小明', 1314.00),
(1, '旺财', 9.90);

-- 为 王祖贤 (ID: 2) 插入忠实粉丝
INSERT INTO simps (target_id, nickname, contributed_amount) VALUES 
(2, '大壮', 8888.88),
(2, '铁柱', 0.01);

查询

查看每位舔狗正在追谁

SELECT 
    s.nickname AS 舔狗, 
    g.name AS 女神, 
    s.contributed_amount AS 贡献值
FROM simps s
JOIN goddesses g ON s.target_id = g.id
ORDER BY s.contributed_amount DESC;

查看哪位女神收到的“贡献”总额最高

SELECT 
    g.name AS 女神, 
    COUNT(s.id) AS 舔狗总数, 
    SUM(s.contributed_amount) AS 收到总金额
FROM goddesses g
LEFT JOIN simps s ON g.id = s.target_id
GROUP BY g.name
ORDER BY 收到总金额 DESC;

找出贡献金额低于 10 元的舔狗

SELECT nickname, contributed_amount 
FROM simps 
WHERE contributed_amount < 10.00;

多对多关系

概念： 表 A 的一行对应表 B 的多行，反之亦然。

示例：学生 (Students) 与社团 (Clubs)。

一个学生可以参加多个社团（比如既参加羽毛球社，又参加代码狂魔社）。
一个社团也可以拥有很多名学生。
这种关系无法在任何一张表里通过加一个字段来解决，必须有一个“中间人”来牵线搭桥。

-- 1. 学生表 (核心实体 A)
CREATE TABLE students (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

-- 2. 社团表 (核心实体 B)
CREATE TABLE clubs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    club_name TEXT NOT NULL UNIQUE,
    description TEXT
);

-- 3. 成员关系表 (中间桥梁)
-- 这张表记录了“谁”参加了“哪个”社团
CREATE TABLE memberships (
    student_id BIGINT REFERENCES students(id) ON DELETE CASCADE,
    club_id BIGINT REFERENCES clubs(id) ON DELETE CASCADE,
    joined_at TIMESTAMPTZ DEFAULT NOW(), -- 甚至可以记录入社时间
    PRIMARY KEY (student_id, club_id)    -- 联合主键：保证一个学生不会在同一个社团里加两次
);

插入数据

-- 插入学生
INSERT INTO students (name) VALUES ('枫枫'), ('小红'), ('老王') RETURNING id;
-- 假设 ID 分别是 1, 2, 3

-- 插入社团
INSERT INTO clubs (club_name, description) VALUES 
('Go语言社', '高性能后端开发探讨'), 
('篮球社', '只因你太美'), 
('钓鱼社', '永不空军') 
RETURNING id;
-- 假设 ID 分别是 1, 2, 3

-- 建立关系 (多对多)
INSERT INTO memberships (student_id, club_id) VALUES 
(1, 1), -- 枫枫 参加了 Go语言社
(1, 2), -- 枫枫 参加了 篮球社
(2, 2), -- 小红 参加了 篮球社
(3, 1), -- 老王 参加了 Go语言社
(3, 3); -- 老王 参加了 钓鱼社

查询

查成员：想看“Go语言社”里都有哪些大神？

由于信息隔了两层，我们需要两次JOIN跳过去。

SELECT 
    c.club_name AS 社团名, 
    s.name AS 成员名,
    m.joined_at AS 入社时间
FROM clubs c
JOIN memberships m ON c.id = m.club_id
JOIN students s ON m.student_id = s.id
WHERE c.club_name = 'Go语言社';

查轨迹：想看“枫枫”同学一共参加了多少个社团？

SELECT 
    s.name AS 学生名, 
    COUNT(m.club_id) AS 参加社团数,
    string_agg(c.club_name, ', ') AS 社团清单 -- 把社团名拼成字符串
FROM students s
LEFT JOIN memberships m ON s.id = m.student_id
LEFT JOIN clubs c ON m.club_id = c.id
WHERE s.name = '枫枫'
GROUP BY s.name;

查热度：哪个社团人最多？

SELECT 
    c.club_name, 
    COUNT(m.student_id) AS 总人数
FROM clubs c
LEFT JOIN memberships m ON c.id = m.club_id
GROUP BY c.club_name
ORDER BY 总人数 DESC;