wordpress建站小百科,网络推广加盟,wordpress 文章加密,网站做app开发工具关系多对多的关系#xff0c;如何通过mysql来表示站在老师的角度一个老师可以教多个学生#xff0c;一个老师也可以教一个学生。站在学生的角度一个学生可以被一个老师教一个学生也可以被多个老师教结论#xff1a;如果站在两边看都是一对多的情况#xff0c;那么这个关系就…关系多对多的关系如何通过mysql来表示站在老师的角度一个老师可以教多个学生一个老师也可以教一个学生。站在学生的角度一个学生可以被一个老师教一个学生也可以被多个老师教结论如果站在两边看都是一对多的情况那么这个关系就是多对多的。问题如果表示a老师教过x学生和y学生x学生和y学生同时也被b老师教解决方法多对多关系无论是把外键放在哪一张表都不合适因为可能有多个值解决方案建立一个中间的关系表create table student(id int primary key auto_increment,name char(10)) charset utf8;create table teacher(id int primary key auto_increment,name char(10)) charset utf8;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)) charset utf8 ;insert into teacher values(null,bgon),(null,nike);insert into student values(null,老王),(null,老李);# 老王被bgon教过insert into t_s_r values(null,1,1);# nike教过老李insert into t_s_r values(null,2,2);# nike教过老王insert into t_s_r values(null,2,1);# 现在已知老师名称为bgon,请找出他教过的那些学生mysql select id from teacher where namebgon;----| id |----| 1 |----1 row in set (0.00 sec)mysql select s_id from t_s_r where t_id1;------| s_id |------| 1 |------1 row in set (0.00 sec)mysql select name from student where id1;--------| name |--------| 老王 |--------# 子查询mysql select name from student where id(select s_id from t_s_r where t_id(select id from teacher where namebgon));--------| name |--------| 老王 |--------1 row in set (0.00 sec)# 已知学生名为老李请查询出哪些老师教过他.mysql select id from student where name 老李;----| id |----| 2 |----1 row in set (0.00 sec)mysql select t_id from t_s_r where s_id2;------| t_id |------| 2 |------1 row in set (0.00 sec)mysql select name from teacher where id2;------| name |------| nike |------1 row in set (0.00 sec)# 子查询mysql select name from teacher where id(select t_id from t_s_r where s_id(select id from student where name 老李));------| name |------| nike |------1 row in set (0.00 sec)总结如何确认多对多的关系站在两个表的角度去想处理方式通过在两个表中间建立一个外键表该外键表分别都关联两表的字段。联合唯一约束对于上面的t_s_r表进行改进# 原表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),) charset utf8 ;# 改进方法一(表已创建的情况下)unique keyalter table t_s_r add unique key(t_id,s_id);# 改进方法二(表没创建的情况下):unique keycreate 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)) charset utf8 ;# 联合主键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),primary key(t_id,s_id)) charset utf8 ;对于上面的t_s_r表进行改进------最终版# 创建学生表create table student(id int primary key auto_increment,name char(10)) charset utf8;# 创建教师表create table teacher(id int primary key auto_increment,name char(10)) charset utf8;# 创建学生和老师关系表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)) charset utf8 ;insert into teacher values(null,bgon),(null,nike);insert into student values(null,老王),(null,老李);# 老王被bgon教过insert into t_s_r values(null,1,1);# nike教过老李insert into t_s_r values(null,2,2);# nike教过老王insert into t_s_r values(null,2,1);# 问题现在已知老师名称为bgon,请找出他教过的那些学生mysql select id from teacher where namebgon;----| id |----| 1 |----1 row in set (0.00 sec)mysql select s_id from t_s_r where t_id1;------| s_id |------| 1 |------1 row in set (0.00 sec)mysql select name from student where id1;--------| name |--------| 老王 |--------# 整合查询语句子查询mysql select name from student where id(select s_id from t_s_r where t_id(select id from teacher where namebgon));--------| name |--------| 老王 |--------1 row in set (0.00 sec)# 问题已知学生名为老李请查询出哪些老师教过他.mysql select id from student where name 老李;----| id |----| 2 |----1 row in set (0.00 sec)mysql select t_id from t_s_r where s_id2;------| t_id |------| 2 |------1 row in set (0.00 sec)mysql select name from teacher where id2;------| name |------| nike |------1 row in set (0.00 sec)# 整合查询语句子查询mysql select name from teacher where id(select t_id from t_s_r where s_id(select id from student where name 老李));------| name |------| nike |------1 row in set (0.00 sec)一对一关系例如每一个人都有一个身份证。一个身份证只对应一个人分表垂直分表例如人物的详细信息就可以垂直分表# 全表create table person(id int primary key auto_increment,name char(10),age int,height float,weigth float)# 垂直分表personcreate table person(id int primary key auto_increment,name char(10),age int) charset utf8;# 垂直分表person_infocreate table person_info(id int primary key,height float,weigth float,foreign key(id) references person(id)) charset utf8;水平分表数据量很大使用一个表查询效率低使用两个表来存取这些数据处理一对一关系处理方式先确定先后顺序将先存在的数据作为主表后存在的作为从表使两个表的id保持一一对应方法1从表的id即是主键又是外键方法2从表的id设置为外键并保证唯一人物关系表从客户演变为学生# 创建客户表create table kehu_t(id int primary key auto_increment,name char(10),phone char(11))# 创建学生表create table student_t(id int primary key auto_increment,card_id char(18))为什么要分表数据分担在多个表提高了查询的效率
