一、多表关系
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