表之间的关系(重点)

表之间的关系(重点)

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

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;

外键的使用

什么时候使用外键?

表之间存在关联关系

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

多对一(一对多)

处理方式

老师和部门的关系

老师的角度看(多)

	一个老师应该对应有一个部门

	一个老师可以对应对多个部门?  不行 一个老师只能属于一个部门 (要看具体业务要求)!

	多个老师可以对应一个部门

	多对一

部门的角度看 (一)

	一个部门可以对应多个老师

	一个部门可以对应一个老师

	多个部门可以对应一个老师? 不行

	一对多

如何处理一对多(多对一)?

	在老师表中存储 部门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 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 在添加详情表的数据  

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

巴特西

mysql-表关系介绍(应用较多)

表之间的关系(重点)

foreign key (外键)

级联操作 (cascade)

两种级联操作

外键的使用

多对一(一对多)

处理方式

多对多

处理方式:

一对一关系

处理方式

最新文章

热门文章