目录



  • 表之间的关系(重点)
  • foreign key (外键)
  • 级联操作 (cascade)
  • 两种级联操作
  • 外键的使用
  • 多对一(一对多)
  • 多对多
  • 一对一关系



表之间的关系(重点)

foreign key (外键)

外键约束,用于指向另一个表的主键字段

创建表时,需要先创建主表,在创建从表

# 创建主表
create table dept(id int primary key auto_increment,
                 mananger char(10),
                  content char(100)
                 );

# 创建表的时候添加外键
create table student3(id int primary key auto_increment,
                     name char(10),
                      gender char(10),
                      dept_id int,
                      # 绑定外键,绑定主表的id
                      foreign key (dept_id) references dept(id)  
                     );

foreign key (dept_id) references dept(id)
# dept_id  表示当前的外键字段
# dept 表示要关联哪个表
# dept(id) id 表示关联的dept表的id 字段

# 删除从表时,要先删除从表,否则会报错
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
# 创建主表
create table dept(id int primary key auto_increment,
                 mananger char(10),
                  content char(100)
                 );

# 创建表的时候添加外键
create table student3(id int primary key auto_increment,
                     name char(10),
                      gender char(10),
                      dept_id int,
                      # 绑定外键,绑定主表的id
                      foreign key (dept_id) references dept(id)  
                     );

foreign key (dept_id) references dept(id)
# dept_id  表示当前的外键字段
# dept 表示要关联哪个表
# dept(id) id 表示关联的dept表的id 字段

# 删除从表时,要先删除从表,否则会报错
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

foreign key 带来的约束作用

  • 在从表中插入一条记录,关联了一个主表中不存在的id,会报错;必须保证从表中外键的值是在主表中存在的
  • 插入数据的顺序
    先插入主表记录,在插入从表记录
  • 从表更新外键时,也必须保证外键的值再主表是存在的
  • 删除主表记录前,要保证从表中没有外键关联到要删除的id
    必须先删除从表,再删除主表
  • 更新主表记录的主键时,要保证从表中没有外键关联到要删除的id
  • 必须先创建主表

foreign key 就是用来保证两种表之间的关联是正确的

级联操作 (cascade)

级联操作指的就是,当你操作主表是,自动的操作从表

两种级联操作

  • 级联的删除
    当删除主表时自动删除从表中相关数据
  • 级联更新
    当主表的主键更新时,会自动更新关联的从表数据.
# 创建从表,绑定级联关系
create table student(id int primary key auto_increment,
                     name char(10),tea_id int, 
                     foreign key(tea_id) references teacher(id) 
                     on update cascade 
                     on delete cascade  
                    );
# on update cascade 绑定级联更新
# on deletc cascade 绑定级联删除
# 两个可以单独使用,也可以一起使用,用空格隔开即可
Query OK, 0 rows affected (0.65 sec)

# 添加信息
insert into student values (null,"jack",1),(null,"rose",1),(null,"rayn",2);
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0
# 删除老师表中第一个信息
delete from teacher where id = 1;
Query OK, 1 row affected (0.08 sec)

mysql> select * from teacher;
+----+------+
| id | name |
+----+------+
|  2 | nick |
+----+------+
1 row in set (0.00 sec)
# 学生表中,绑定的对应id的信息也会自动删除
mysql> select * from student;
+----+------+--------+
| id | name | tea_id |
+----+------+--------+
|  3 | rayn |      2 |
+----+------+--------+
1 row in set (0.00 sec)


# 表建好后需要在添加外键或者级联操作,可以使用
# alter table 表名 add constraint 外键名称 foreign key (外键字段) references 关系表名(关系表内字段)
alter table student add constraint class_id foreign key(class_id) references class(id) on update cascade on delete cascade;
# 创建从表,绑定级联关系
create table student(id int primary key auto_increment,
                     name char(10),tea_id int, 
                     foreign key(tea_id) references teacher(id) 
                     on update cascade 
                     on delete cascade  
                    );
# on update cascade 绑定级联更新
# on deletc cascade 绑定级联删除
# 两个可以单独使用,也可以一起使用,用空格隔开即可
Query OK, 0 rows affected (0.65 sec)

# 添加信息
insert into student values (null,"jack",1),(null,"rose",1),(null,"rayn",2);
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0
# 删除老师表中第一个信息
delete from teacher where id = 1;
Query OK, 1 row affected (0.08 sec)

mysql> select * from teacher;
+----+------+
| id | name |
+----+------+
|  2 | nick |
+----+------+
1 row in set (0.00 sec)
# 学生表中,绑定的对应id的信息也会自动删除
mysql> select * from student;
+----+------+--------+
| id | name | tea_id |
+----+------+--------+
|  3 | rayn |      2 |
+----+------+--------+
1 row in set (0.00 sec)


# 表建好后需要在添加外键或者级联操作,可以使用
# alter table 表名 add constraint 外键名称 foreign key (外键字段) references 关系表名(关系表内字段)
alter table student add constraint class_id foreign key(class_id) references class(id) on update cascade on delete cascade;

外键的使用

什么时候使用外键?

表之间存在关联关系

首先就要明确表之间的关系

多对一(一对多)

处理方式

老师和部门的关系 
老师的角度看(多)
    一个老师应该对应有一个部门 
    一个老师可以对应对多个部门?  不行 一个老师只能属于一个部门 (要看具体业务要求)!
    多个老师可以对应一个部门 
    多对一
部门的角度看 (一)
    一个部门可以对应多个老师
    一个部门可以对应一个老师
    多个部门可以对应一个老师? 不行 
    一对多 
如何处理一对多(多对一)?
    在老师表中存储 部门id
    即多的一方存储 一的一方的id
老师和部门的关系 
老师的角度看(多)
    一个老师应该对应有一个部门 
    一个老师可以对应对多个部门?  不行 一个老师只能属于一个部门 (要看具体业务要求)!
    多个老师可以对应一个部门 
    多对一
部门的角度看 (一)
    一个部门可以对应多个老师
    一个部门可以对应一个老师
    多个部门可以对应一个老师? 不行 
    一对多 
如何处理一对多(多对一)?
    在老师表中存储 部门id
    即多的一方存储 一的一方的id

在多的一方,即teacher表中保存相应部门(一的一方)的编号

#部门:
    create table dept(
        id int primary key auto_increment,
        name char(20),
        job char(50),
        manager char(10)
    );
    #老师表:
    create table teacher(
        id int primary key auto_increment,
        name char(20),
        gender char(1),
        dept_id int,
        foreign key(t_id) references teacher(id),
    );
#部门:
    create table dept(
        id int primary key auto_increment,
        name char(20),
        job char(50),
        manager char(10)
    );
    #老师表:
    create table teacher(
        id int primary key auto_increment,
        name char(20),
        gender char(1),
        dept_id int,
        foreign key(t_id) references teacher(id),
    );

多对多

如何确定多对多关系

例如: 老师表 和学生表

老师角度:一个老师可以对应多个学生

学生角度:一个学生也可以对应多个老师

如果双方都是一对多的关系,那么两者是多对多关系

处理方式:

  • 创建两个主表 如 学员 和老师
  • 创建关系表 包含两个字段,分别设置外键, 指向对应的表
  • 将两个字段,作为联合主键

建立一个中间表,用于存储关系,至少具备两个字段,分别指向老师和学生的主键,两个字段都是外键,如下:

一定要先建立两个主表,才能建立关系表

#先创建老师表和学生表,再创建关系表
create table teacher(id int primary key auto_increment, name char(10));
create table student(id int primary key auto_increment, name char(10));
create table tea_stu_a(
        tea_id int,
        stu_id int,
        foreign key (tea_id) references teacher(id),
        foreign key (stu_id) references student(id),
        primary key (tea_id,stu_id)
);

+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| tea_id | int(11) | NO   | PRI | 0       |       |
| stu_id | int(11) | NO   | PRI | 0       |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# 插入老师信息
insert into teacher values (null,"jerry"),(null,"nick");
# 插入学生信息
insert into student values (null,"jack"),(null,"rose");

# 添加关系表信息
insert into tea_stu_a values (1,1);
insert into tea_stu_a values (1,2);
insert into tea_stu_a values (2,1);
insert into tea_stu_a values (2,2);

+--------+--------+
| tea_id | stu_id |
+--------+--------+
|      1 |      1 |
|      2 |      1 |
|      1 |      2 |
|      2 |      2 |
+--------+--------+
# 如何通过关系表查找信息,比如要找出Jerry老师教过的学生
# 1.通过名字获取Jerry老师的id
# 2.拿着id去关系表中拿到学生的id
# 3.通过学生的id取出学生的信息

select * from student where id = any(
        select stu_id from tea_stu_a where tea_id =any(
        select id from teacher where name = "jerry")
);
+----+------+
| id | name |
+----+------+
|  1 | jack |
|  2 | rose |
+----+------+

# 在id=后面加any,否则会报错
ERROR 1242 (21000): Subquery returns more than 1 row
#先创建老师表和学生表,再创建关系表
create table teacher(id int primary key auto_increment, name char(10));
create table student(id int primary key auto_increment, name char(10));
create table tea_stu_a(
        tea_id int,
        stu_id int,
        foreign key (tea_id) references teacher(id),
        foreign key (stu_id) references student(id),
        primary key (tea_id,stu_id)
);

+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| tea_id | int(11) | NO   | PRI | 0       |       |
| stu_id | int(11) | NO   | PRI | 0       |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# 插入老师信息
insert into teacher values (null,"jerry"),(null,"nick");
# 插入学生信息
insert into student values (null,"jack"),(null,"rose");

# 添加关系表信息
insert into tea_stu_a values (1,1);
insert into tea_stu_a values (1,2);
insert into tea_stu_a values (2,1);
insert into tea_stu_a values (2,2);

+--------+--------+
| tea_id | stu_id |
+--------+--------+
|      1 |      1 |
|      2 |      1 |
|      1 |      2 |
|      2 |      2 |
+--------+--------+
# 如何通过关系表查找信息,比如要找出Jerry老师教过的学生
# 1.通过名字获取Jerry老师的id
# 2.拿着id去关系表中拿到学生的id
# 3.通过学生的id取出学生的信息

select * from student where id = any(
        select stu_id from tea_stu_a where tea_id =any(
        select id from teacher where name = "jerry")
);
+----+------+
| id | name |
+----+------+
|  1 | jack |
|  2 | rose |
+----+------+

# 在id=后面加any,否则会报错
ERROR 1242 (21000): Subquery returns more than 1 row

一对一关系

站在两个表的角度都是一对一的关系

处理方式

  • 确定先后顺序,
  • 将先存在的数据作为主表
  • 后存在的作为从表
  • 使两个表id保持一一对应
  • 方法1:从表的id即是主键又是外键
  • 方法2:从表的id设置为外键,并保证唯一
# 人员表
create table person(
    id int primary key auto_increment,
    name char(10),
    age int
);
# 详情表 
create table person_info(
    id int primary key,
    height float,
    weight float,
    foreign key(id) references person(id)
);
#再这样的关系中 必须先插入主表即person 拿到一个id 在添加详情表的数据  

#将一条完整数拆分到不同表中,可以提高查询的效率,上述方式称之为垂直分表!
# 人员表
create table person(
    id int primary key auto_increment,
    name char(10),
    age int
);
# 详情表 
create table person_info(
    id int primary key,
    height float,
    weight float,
    foreign key(id) references person(id)
);
#再这样的关系中 必须先插入主表即person 拿到一个id 在添加详情表的数据  

#将一条完整数拆分到不同表中,可以提高查询的效率,上述方式称之为垂直分表!