多表关系

概述

各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多（多对一）
  • 多对多
  • 一对一

一对多（多对一）

案例：部门 与 员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方面建议外键，指向一的一方的主键（可以结合上个笔记）

多对多

案例：学生 与 课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至上包含两个外键，分别关联两方主键

创建一张 student 表, 并插入内容

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建学生表 多对多案例
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, '亚尼', '2008950101'),
                        (NULL, '孙琴', '2000187802'),
                        (NULL, '无韦', '2009900103');

创建一张 course 表

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建课程表
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');

创建学生课程中间表对两张表进行 外键 关联

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE student_course(
    id INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    studentid INT NOT NULL COMMENT '学生ID', # studentid 是个字段
    coursed INT NOT NULL COMMENT '课程ID', # coursed 是个字段
    CONSTRAINT fk_coursed FOREIGN KEY (coursed) 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);

其中 coursed 对应 course (id)， studentid 对应 student (id)。

三张表结构中，定义了 id INT AUTO_INCREMENT，表示 id 字段是自增主键

这里的 NULL 是专门为自增主键设计的占位符，不是表示”空值”，而是告诉MySQL”请自动生成ID值”。

一对一

案例： 用户 与 用户详情的关系

关系：一对一关系，用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表当中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为 **唯一 ** 的（UNIQUE）

假设有一张超大信息表，我们将其拆分为两张表内容如下，然后进行 1 对 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 创建用户基本信息表
CREATE TABLE tb_user(
                        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                        name VARCHAR(19) COMMENT '姓名',
                        age INT COMMENT '年龄',
                        gender CHAR(1) COMMENT '1: 男，2: 女',
                        phone CHAR(11) COMMENT '手机号'
) COMMENT '用户基本信息表';

-- 创建用户教育信息表
CREATE TABLE tb_user_edu(
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  degree VARCHAR(29) COMMENT '学历',
  major VARCHAR(50) COMMENT '专业',
  primaryschool VARCHAR(50) COMMENT '小学',
   middleschool VARCHAR(50) COMMENT '中学',
   university VARCHAR(50) COMMENT '大学',
  userid INT UNIQUE COMMENT '用户ID', 
 # 设置userid外键，它所关联的就是 tb_user(id)，并且设置了约束 唯一 UNIQUE, 确保 1 对 1
                            CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES tb_user(id)
) COMMENT '用户教育信息表';

值得注意的是：上表设置 userid 外键，它所关联的就是 tb_user(id)，并且设置了约束 唯一 UNIQUE, 确保 1 对 1

向两张表注入相应的信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 向用户基本信息表(tb_user)插入数据
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');

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

最后完成了：一方加入外键，关联另外一方的主键，并且设置外键为 **唯一 ** 的（UNIQUE）

硬看不如实践