多表关系

设计表结构时，会根据业务需求及业务模块之间的关系分析表与表之间的联系，基本分为三种：一对多（多对一）、多对多、一对一。

关系	案例	实现方式
一对多（多对一）	部门 ↔ 员工	在多的一方建立外键，指向一的一方的主键
多对多	学生 ↔ 课程	建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一	用户 ↔ 用户详情	在任意一方加入外键关联另一方主键，并设置外键为唯一（UNIQUE）

一、一对多（多对一）

案例：部门与员工
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方（员工表）建立外键，指向一的一方（部门表）的主键

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept (id);

二、多对多

案例：学生与课程
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

-- 学生表
CREATE TABLE student(
    id    INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name  VARCHAR(10) COMMENT '姓名',
    no    VARCHAR(10) COMMENT '学号'
) COMMENT '学生表';

INSERT INTO student VALUES
(NULL, '黛绮丝', '2000100101'), (NULL, '谢逊', '2000100102'),
(NULL, '殷天正', '2000100103'), (NULL, '韦一笑', '2000100104');

-- 课程表
CREATE TABLE course(
    id    INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name  VARCHAR(10) COMMENT '课程名称'
) COMMENT '课程表';

INSERT INTO course VALUES (NULL, 'Java'), (NULL, 'PHP'), (NULL, 'MySQL'), (NULL, 'Hadoop');

-- 中间表：两个外键分别指向两张表的主键
CREATE TABLE student_course(
    id         INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    studentid  INT NOT NULL COMMENT '学生ID',
    courseid   INT NOT NULL COMMENT '课程ID',
    CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course (id),
    CONSTRAINT fk_studentid FOREIGN KEY (studentid) REFERENCES student (id)
) COMMENT '学生课程中间表';

INSERT INTO student_course VALUES (NULL, 1, 1), (NULL, 1, 2), (NULL, 1, 3), (NULL, 2, 2), (NULL, 2, 3), (NULL, 3, 4);

三、一对一

案例：用户与用户详情
关系：一对一，多用于单表拆分——把基础字段放一张表，其他详情字段放另一张表，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一（UNIQUE）

-- 用户基本信息表
CREATE TABLE tb_user(
    id      INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name    VARCHAR(10) COMMENT '姓名',
    age     INT COMMENT '年龄',
    gender  CHAR(1) COMMENT '1: 男, 2: 女',
    phone   CHAR(11) COMMENT '手机号'
) COMMENT '用户基本信息表';

-- 用户教育信息表：userid 唯一且作为外键
CREATE TABLE tb_user_edu(
    id             INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    degree         VARCHAR(20) COMMENT '学历',
    major          VARCHAR(50) COMMENT '专业',
    primaryschool  VARCHAR(50) COMMENT '小学',
    middleschool   VARCHAR(50) COMMENT '中学',
    university     VARCHAR(50) COMMENT '大学',
    userid         INT UNIQUE COMMENT '用户ID',
    CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES tb_user (id)
) COMMENT '用户教育信息表';

INSERT INTO tb_user(id, name, age, gender, phone) VALUES
(NULL, '黄渤', 45, '1', '18800001111'), (NULL, '冰冰', 35, '2', '18800002222'),
(NULL, '码云', 55, '1', '18800008888'), (NULL, '李彦宏', 50, '1', '18800009999');

INSERT INTO tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) VALUES
(NULL, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
(NULL, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
(NULL, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
(NULL, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);

多表关系

一、一对多（多对一）

二、多对多

三、一对一

相关