数据库MySQL表关系管理实例讲解
@概述
关系型数据库中的表与表不是彼此独立,而是相互关联的; 表与表之间的级联关系使得整个数据库成为一个有机关联的系统; 表关系可以分为一对一、一对多、多对多三种,它们的维护方式各不相同; 表关系的管理是关系型数据库的重要组成部分,要牢牢掌握;
@一对一
如果A表记录与B表记录有双向的一 一对应关系,我们就称它们之间有一对一的关系; 如校园数据库中的班级与班主任,班级有唯一的班主任,班主任执鞭唯一的班级,它们之间的关系就是一对一关系; 一对一关系的维护,由A、B中相对次要的一方来维护(这里假设是B),维护的方式是在B中插入一个指向A表主键的外键; 在本例中,班级表有一个masterid字段,指向老师表中的主键id; 在一对一关系中,本来外键放在任意一方都是可行的,之所以要选择相对次要的一方,是因为万一不得以必须删除一个表时,我们会选择删除相对次要的表,此时由他所维护的关联关系也被一并删除,不会形成脏数据;
案例:查询某班级的班主任,查询某班主任管理的班级
-- 将老师robin设置为班主任 update teacher set is_master=1 where name='robin'; -- 将班级“丐帮”的masterid设置为robin的id -- 此时一对一关系已经形成,通过查询班级表中的masterid可以知道该班的班主任 -- 通过在班级表中查询特定老师的masterid,可以找到他执掌的班级 update clazz set masterid=(select id from teacher where teacher.name='robin') where name='丐帮';
-- 查询“丐帮”的班主任 -- ②查询老师表中id为①结果的老师信息 select * from teacher where id = ( -- ①获取丐帮的班主任id select masterid from clazz where name='丐帮' );
-- 查询robin所管理的班级 -- ②查找班级表中masterid指向robin的记录 select * from clazz where masterid = ( -- ①获取robin的id select id from teacher where name="robin" );
@一对多
如果A表中的一条数据对应B表中的多条数据,而B表中的每条数据都对应一条唯一的A表数据,就称A表和B表是一对多的关系; 如校园数据库中的班级与学生,每个班级有多名学生,而每个学生只属于一个唯一特定的班级,所以班级表和学生表是一对多的关系; 一对多关系的维护,由多方进行维护,维护方式是多方在表中添加指向一方的外键; 本例中,学生表中有classid来指向学生所对应的班级;
案例:查询一个班级的所有学生
-- 修改学生表信息,为每个学生指定classid update student set classid=(select id from clazz where clazz.name = '丐帮') where student.name='野间忠一郎'; update student set classid=(select id from clazz where clazz.name = '小刀会') where student.name='二郎神'; update student set classid=(select id from clazz where clazz.name = '斧头帮') where student.name='张三丰'; update student set classid=(select id from clazz where clazz.name = '天地会') where student.name='郭小四'; update student set classid=(select id from clazz where clazz.name = '丐帮') where student.name='隔壁老王'; update student set classid=(select id from clazz where clazz.name = '小刀会') where student.name='练过的六爷'; update student set classid=(select id from clazz where clazz.name = '斧头帮') where student.name='洪七公'; update student set classid=(select id from clazz where clazz.name = '天地会') where student.name='香香八婆'; update student set classid=(select id from clazz where clazz.name = '丐帮') where student.name='马英九'; update student set classid=(select id from clazz where clazz.name = '小刀会') where student.name='十三姨'; update student set classid=(select id from clazz where clazz.name = '斧头帮') where student.name='山本五十六'; update student set classid=(select id from clazz where clazz.name = '天地会') where student.name='包租婆'; -- 通过查询学生表中所有classid指向“小刀会”的记录,来查询班级“小刀会”的所有学生 select * from student where classid = (select id from clazz where clazz.name='小刀会');
@多对多
如果A表中的一条记录对应B表中的多条记录,B表中的一条记录也对应A表中的多条记录,就称A表和B表是多对多的关系; 如校园数据库中的学生与课程,每个学生可以选修多门课程,每门课程也可以有多名学员,所以学生表与课程表是多对多的关系; 多对多关系的维护,要通过建立中间表来维护,一个学生每加选一门课程,就将学生的id和课程的id同时存入一条记录中进行记录,中间表的主键是【学生id和课程id的联合主键】,即同一个学生不能对同一门课程选修两次;
案例:查询某学生所选的全部课程,查询选修某课程的全部学生
首先创建【学生_课程中间表】
-- 创建【学生_课程】中间表 create table student_course( -- 学生id sid integer not null, --课程id cid integer not null, -- 主键是【学生id和课程id的联合主键】 primary key (sid,cid) ); -- 如果没有设置主键,可以通过修改表字段的方式来添加【联合主键】 -- alter table student_course add constraint s_c primary key (sid,cid);
插入一些选课信息
-- 野间忠一郎选修了Python insert into student_course(sid, cid) values ( (SELECT id from student where student.name='野间忠一郎'), (select id from course where course.name='Python') ); -- 野间忠一郎选修了Java insert into student_course(sid, cid) values ( (SELECT id from student where student.name='野间忠一郎'), (select id from course where course.name='Java') ); -- 野间忠一郎选修了HTML5 insert into student_course(sid, cid) values ( (SELECT id from student where student.name='野间忠一郎'), (select id from course where course.name='HTML5') ); -- 香香八婆选修了Python insert into student_course(sid, cid) values ( (SELECT id from student where student.name='香香八婆'), (select id from course where course.name='Python') ); -- 香香八婆选修了HTML5 insert into student_course(sid, cid) values ( (SELECT id from student where student.name='香香八婆'), (select id from course where course.name='HTML5') ); -- 二郎神选修了Python insert into student_course(sid, cid) values ( (SELECT id from student where student.name='二郎神'), (select id from course where course.name='Python') );
查询香香八婆的选课信息
-- ②查询这些课程id所对应的课程信息 select * from course where id in ( -- ①查询中间表中sid指向香香八婆的所有记录,获取对应的课程id select cid from student_course where sid=(select id from student where name='香香八婆') );
查询选修了Python的学员
-- ② 查找学生id所对应的学生信息 select * from student where id in ( -- ①查询中间表中所有课程id指向Python的记录,获得对应的学生id select sid from student_course where cid=(select id from course where name='Python') );