数据库--多表关系

目录

• 多表关系
  • 一对多
  • 多对多
    • 总结
    • 查询方式
    • 子查询方式
  • 一对一
    • 补充：垂直分表，水平分表

多表关系

一对多

表1-->表2 一对多
表2-->表1 多对一

例如：一个部门有多个老师，而一个老师只有一个部门。此时部门表是主表，老师表是从表

create table dept(
	id int,
	name char(10),
	primary key(id)	# 主键
	);
create table teacher(
	id int,
	name char(10),
    dept_id int,
	primary key(id),	# 主键
	foreign key(dept_id) references dept(id)  # 外键
	);

多对多

表1-->表2 多对多

表2-->表1 多对多

例如：一个老师可以教多门课程，一门课程也可以有多个老师教，这么这就是多对多的关系，没有主从之分，所以外键也不便添加，因为外键的类型最好是int，这样便于修改和添加。

处理方式：

• 建立一个中间表3，专门用来储存表1和表2间的关系，至少具备两个字段，分别指向表1和表2的主键，这两个字段都是外键约束。这张表属于表1和表2的从表。

create table course(
	id int primary key auto_increment,
	name char(10)
	);
create table teacher(
	id int primary key auto_increment,
	name char(10)
	);
create table c_t_rel(
	cou_id int,
	tea_id int,
	foreign key(cou_id) references course(id),
	foreign key(tea_id) references teacher(id)
	);

• 这种方式确实做到了多对多，但是如何保证没有重复添加相同的关系呢？

方法一：给两个字段分别设置为联合唯一+非空

# 修改表
alter table c_t_rel modify cou_id int not null unique;
alter table c_t_rel modify tea_id int not null unique;
# 创建表
create table c_t_rel2(
	cou_id int not null,
	tea_id int not null,
	not null key(cou_id, tea_id),
    unique key(cou_id, tea_id)
	);

方法二：将中间的关系表3中的两个id，作为联合主键(多字段主键)

# 推荐
create table c_t_rel(
	cou_id int,
	tea_id int,
	primary key(cou_id, tea_id),
	foreign key(cou_id) references course(id),
	foreign key(tea_id) references teacher(id)
    );

总结

1. 创建两个主表，如课程表和老师表
2. 创建一个关系表，包含两个字段
3. 两个字段设置为联合主键，并且分别设置外键，对应主表的主键

查询方式

如果我们想要查找，python有哪些老师教了，步骤为

1. 通过课程名python在课程表中找到id
2. 通过id去关系表中找到老师的id
3. 通过老师的id拿到老师的名字

select id from course where name='python';
# 为什么这里有引号也能找
select tea_id from c_t_rel where cou_id=1;
select * from teacher where id=1 or id=2;

子查询方式

• 把语句A的结果作为另一条语句B的条件

# 如果语句A的结果有两个值，那么id就要用in，一个值就用=
							# 两个值                                    # 一个值
select * from teacher where id in (select tea_id from c_t_rel where cou_id=(select id from course where name='python'));

一对一

这种情况比较少见，因为一般的一对一，都不需要进行分表操作。

但是当一个表中数据量太大的时候，为了提高效率，将一些不是很常用的数据拆分到另一个表中。称之为垂直分表。

例如：人员表，和信息详情表

# 人员表
create table person(
	id int primary key auto_increment,
	name char(10)
	);

# 详情表
create table person_info(
	id int primary key,
	height float,
	weight float,
    foreign key(id) references person(id)
	);
# 在这个例子中，必须先插入主表也就是person，拿到一个id，再添加详情表的数据，有点类似于多对一关系

补充：垂直分表，水平分表

垂直分表：把一个表的部分字段分到另一个表中。

水平分表：两个表的字段完全一致，只是把数据拆分开来提高效率