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 KEYREFERENCES 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.查询测试

selectu.id,u.username,ui.id_number
fromusers as u
left join user_identity as ui onu.id = ui.user_id

1.2.一对多关系

表A的一行能对应表B的多行。一个用户可以有多个手机号。

1.2.1.建表

CREATE TABLE user_phones (id            SERIAL PRIMARY KEY,user_id       INTEGER NOT NULLREFERENCES users (id) ON DELETE CASCADE,phone_number  VARCHAR(20) NOT NULL UNIQUE,   -- 手机号全局唯一，防止重复归属phone_type    VARCHAR(20) DEFAULT 'mobile',  -- 类型：mobile / home / work / otheris_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.查询测试

selectu.id,u.username,up.phone_number,up.phone_type
fromusers as u
left join user_phones as up onu.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 NULLREFERENCES users (id) ON DELETE CASCADE,course_id     INTEGER NOT NULLREFERENCES 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.查询测试

-- 查看每个用户都学习了那些课
selectu.username,c.course_name
fromusers u
left join user_courses uc onu.id = uc.user_id
left join courses c onuc.course_id = c.id;

本文来自博客园，作者：TheLifelongLearner，转载请注明原文链接：https://www.cnblogs.com/The-Lifelong-Learner/p/21602902