mysql-表关系介绍(应用较多)
表之间的关系(重点)
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 在添加详情表的数据
#将一条完整数拆分到不同表中,可以提高查询的效率,上述方式称之为垂直分表!
