mysql处理表多对多、一对一关系

一、多对多

1、如何确定多对多关系

​ 例如 老师表 和 学生表

​ 套路一样 先站在老师表 在站在学生表

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

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

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

2、处理方式

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

create table t_s_r(
    id int primary key auto_increment,
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
    unique key(t_id,s_id)
    );
    
 上表中id是可选的,问题是如何保证没有重复的关系 ?

 方式1:
 给两个字段设置为联合唯一  +  非空
 # 假设表已经存在了
 alter table t_s_r add unique key(t_id,s_id);
 # 创建表时指定 多字段联合唯一 
 create table t_s_r2(t_id int,s_id int,unique key(t_id,s_id));
 方式2:
 # 推荐 将中间的关系表 两个id 作为联合主键 同时具备了 唯一且非空约束 
 create table t_s_r3(t_id int,s_id int,primary key(t_id,s_id));
 
 
 
处理多对多关系
1.创建两个主表  如学员 和 老师 
2.创建关系表 包含两个字段 分别设置外键 指向对应的表 
3.将两个字段 作为联合主键  

 

3、案例:

create table student(id int primary key auto_increment,name char(10));
create table teacher(id int primary key auto_increment,name char(10)); 
create table t_s_r(
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
    primary key(t_id,s_id)
    );
 # 先插入 学生或是老师都可以 但是关系表一定是最后添加的 
 insert into teacher values(null,"bgon"),(null,"nike");
 
  insert into student values(null,"老王"),(null,"老李");
  
  # 老王被bgon教过 
  insert into t_s_r values(1,1);
   # nike教过老李 
  insert into t_s_r values(2,2);
   # nike教过老王 
  insert into t_s_r values(2,1);



 已知老师名称为bgon 请找出他教过那些学生			
1.通过名字获取 bgon的id 			
2.拿着id取关系表中拿到一堆学生的id			
3.通过学生的id取出学生的信息			
 select id from teacher where name = "bgon";
 select s_id from t_s_r where t_id = 1;
 select  * from student where id = 1;
 
 # 子查询方式   把一条语句的结果作为另一条语句的条件!
 select  * from student where id = (select s_id from t_s_r where t_id = (select id from teacher where name = "bgon"));
  
  
  
 已知学生名为老李 请查询出 哪些老师教过他  				
1.通过名字获取老李的id
select id from student where name = "老李";
2.拿着id去关系表 找出老师的id				
select t_id from t_s_r where s_id = 2;
3.通过老师的id取出老师的信息 			
select name from teacher where id = x;


# 子查询方式:
select name from teacher where id = (
    select t_id from t_s_r where s_id = (
        select id from student where name = "老李"
    )
);

二、一对一关系

​ 如一个客户对应一个学生:

​ 站在两边看都是一对一的关系,

1、处理方式,

​ 确定先后顺序

​ 将先存在的数据作为主表

​ 后存在的作为从表

​ 使两个表的id保持一一对应

​ 方法1: 从表的id 即是主键又是外键

​ 方法2:从表的id设置为外键 并保证唯一

2、案例:

# 人员表
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 在添加详情表的数据  

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