数据库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')
);