欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库MySQL表关系管理实例讲解

程序员文章站 2022-07-07 22:34:48
@概述 关系型数据库中的表与表不是彼此独立,而是相互关联的; 表与表之间的级联关系使得整个数据库成为一个有机关联的系统; 表关系可以分为一对一、一对多、多对多三种,它们的维护方式...

@概述

关系型数据库中的表与表不是彼此独立,而是相互关联的; 表与表之间的级联关系使得整个数据库成为一个有机关联的系统; 表关系可以分为一对一、一对多、多对多三种,它们的维护方式各不相同; 表关系的管理是关系型数据库的重要组成部分,要牢牢掌握;

@一对一

如果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')
);