背景概述：
在真实的项目开发中,一个项目中的数据,一般都会保存在同一个数据库中,但是不同的数据需要保存在不同的数据表中。这时不能把所有的数据都保存在同一张表中。
个人理解：也就是说一个完整项目中的数据只会(过于绝对，但我暂时这样理解，便于本人记忆)存放在一个数据库中，而一个完整项目中的数据的会有多种，每种都会单独存放在一个表中（每种,概念极为不清晰，那些数据可以归类为一种,这个问题可以总结为如何对一个项目中的数据进行分类，因为表就是存放数据的,所以也可以将是如何对表进行设计，后面会有详细介绍）。
那么在设计保存数据的数据表时,我们就要根据具体的数据进行分析,然后把同一类数据保存在同一张表中,不同的数据进行分表处理。
数据之间必然会有一定的联系,我们把不同数据保存在不同的数据表中之后,同时还要在数据表中维护这些数据之间的关系。这时就会导致表和表之间必然会有一定的联系。这时要求设计表的人员,就需要考虑不同表之间的的具体关系。
个人理解:数据之间会有联系,那么存放这些数据的表之间一定也会有联系，那么我们就需要在设计表的时候考虑不同表之间的关系
在数据表中,表总共存在三种关系,这三种关系如下描述:真实的数据表之间的关系:多对多关系、一对多(多对一)、一对一(极少)。（一对一关系就是我们之前学习的Map集合的key_value关系）。
多对多关系:
eg：程序员(programmer)和项目(project)的关系、老师(teacher)和学生(student)、学生和课程(course)、顾客(customer)和商品(goods)的关系。
详解:
程序员和项目:一个程序员可以参与多个项目的开发,一个项目可以有多个程序员来开发,同理一个学生可以学多个课程,一个课程可以被多个学生学。一个顾客可以买多个商品，一个商品也能被多个顾客所购买等等。向这种关系就称为多对多关系。
当我们把数据之间的关系分析清楚之后,一般我们需要通过E（Entity）-R(relation)图来展示。这种图就叫实体关系图。一个java对象，对应数据库中的一张表，而java中类的属性,可以对应表中的字段。在E-R图中:
一张表,可以称为一个实体,使用矩形表示,每个实体的属性(字段，表的列),使用椭圆表示。表和表之间的关系用菱形表示:
eg:实体(程序员):编号、姓名、薪资。
实体(项目): 编号、名称。
程序员和项目存在的关系:一个程序员可以开发多个项目,一个项目可以被多个程序员开发。这就是典型的多对多关系。
说明:如果两张表是多对多关系，那么需要创建第三张表,并在第三张表中增加两列,引入其他两张表主键作为自己的外键
个人理解:说明这段话极为重要，牢记于心。第三张表中增加两列的意思是,这个第三张表不是只有两列,可以有很多列,但必须有两列,且这两列必须是外键,而且引用的是其他两张表的主键(一列引一个)
创建表的sql语句：-- 创建程序员表 create table coder( id int primary key auto_increment, name varchar(50), salary double);-- 创建项目表 create table project( id int primary key auto_increment, name varchar(50) );-- 创建中间关系表 create table code_project( coder_id int; project_id int );
下面的上述表的测试数据
点击查看代码
-- 添加测试数据
insert into coder values(1,'小明',12000);
insert into coder values(2,'小红',15000);
insert into coder values（3，'小芳',18000);
insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');
insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);
小结:多对多关系,需要创建第三张表来维护两张主表的关系。在中间表中起码要有两列来自主表的主键。
外键约束(十分重要)
创建第三张关系表即是中间表,这个中间表是用来维护程序员表和项目表之间的关系的。使用中间表的目的是维护两表多对多的关系:
1.中间表插入的数据必须在对多的主表中存在
2.如果主表的记录在中间表维护了关系,就不能随意删除。如果可以删除,中间表就找不到对应的数据,这样中间表就失去了意义。
上述前两点是中间表存在的意义,可是我们这里所创建的中间表并没有起到上述的作用而是存在缺点的:
缺点1:我们可以向中间表code_project插入不存在在的项目编号和程序员编号
缺点2:如果中间表存在程序员编号,我们是可以删除程序员表对应的记录的
也就是说我们可以删除主表的记录,但中间表的对应主表主键的记录并不会改变,所以这行中间表的记录就没有意义了
说明:
创建第三张表的语句如下:create table coder_project( coder_id int,-- 这个外键来自于coder表中的主键 project_id int -- 这个外键来自于project表中的主键 );
当我们在创建第三张关系表时,表中的每一列,都是在使用其他两张表中的列,这时我们需要对第三张表中的列进行相应的约束。当前第三张表中的列由于都是引用其他表中的列,我们把第三张表中这些列称为引用其他表的外键约束
给表中的某一列添加外键约束:简化语法: foreign key(当前表的列名) references 被引用表名(被引用表的列名);
eg：foreign key(coder_id) references coder(id);
注意:一般在开发中,被引用表的列名都是被引用表的主键。
eg：constraint [外键约束名] foreign key(当前表中的列名)
references 被引用表名(被引用表的列名)
eg:constraint coder_project_id foreign key(coder_id) references coder(id);
关键字解释:
constraint：添加约束,可以不写
foreign key（当前表中的列名）：将某个字段作为外键
references 被引用表名(被引用表的列名):外键引用主表的主键
给第三张表添加外键约束有两种方式:
第一种方式:给已存在的表添加外键约束:-- 来自程序员表 alter table coder_project add constraint c_id_fk foreign key(coder_id) references coder(id);-- 来自项目表 alter table coder_project add constraint p_id_fk foreign key(project_id) references project(id);
第二种方式:创建表时就添加外键约束:create table coder_project( coder_id int, project_id int, constraint c_id_fk foreign key(coder_id) references coder(id), constraint p_id_fk foreign key(project_id) references project(id) );
了解完如何给第三张表添加约束以后,我们就开始给上述创建好的第三张表添加外键约束。在添加外键约束之前,由于刚才已经修改了表中的数据,所以我们先清空三张表,然后添加外键约束。
操作步骤:
1.清空上述三张表:truncate coder; truncate project; truncate coder_project;
2.增加外键约束:alter table coder_project add constraint c_id_fk foreign key(coder_id) references coder(id);alter table coder_project add constaint p_id_fk foreign key(project_id) references project(id);
3,添加完外键约束以后,就会在可视化工具中的架构设计器上查看表之间的关系,先选中表,然后点击右下角，最后就可以看见相应的标识
4.向三张表中分别插入数据:
点击查看代码
-- 插入数据
insert into coder values(null,'张三',12000);
insert into coder values(null.'李四',15000);
insert into coder values(null,'王五',18000);
insert into project values(null,'QQ项目');
insert into project values(null,'微信项目');
insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,1);
5.测试外键约束是否起作用:
当我向中间表中插入不存在的项目编号和程序员编号，和如果当中间表中存在程序员编号,在删除程序员表对应的记录时,执行对应的sql操作时,会发现sql执行报错,而且数据表中的数据都没有发生改变,说明外键约束起到了作用.
小结:
1.为何引入外键约束?：让表的数据具有有效性,正确性。提高查询效率
2.添加外键约束语法:constraint 外键约束名 foreign key(当前表的字段名) references 主表（主键);
3.有了外键约束,那么要求添加数据需要先添加主表,然后添加从表。
4.有了外键约束,那么要求删除数据需要先删除从表,然后再删除主表。
外键的级联(十分重要)
在修改和删除主表的主键时,同时更新或删除从表的外键值,称为级联操作
on update cascade –级联更新,主键发生更新时,外键也会更新
on delete cascade –级联删除,主键发生删除时,外键也会删除
具体操作:
1.删除三张表coder、coder_project、project表
2.重新创建三张表,添加级联更新和级联删除-- 创建程序员表 create table coder( id int primary key auto_increment, name varchar(50), salary double );-- 创建项目表 create table project( id int primary key auto_increment, name varchar(50) );create table coder_project( coder_id int, project_id int, -- 添加外键约束,并且添加级联更新和级联删除 constraint c_id_fk foreign key(coder_id) references code(id) on update cascade on delete cascade, constraint p_id_fk foreign key(project_id) references project(id) on update cascade on delete cascade );
3.再次添加数据到三张表:
点击查看代码
-- 添加测试数据
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);
insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');
insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);
小结:
级联更新:on update cascade 主键修改后,外键也会跟着修改
级联删除:on delete cascade 主键删除后,外键对应的数据也会删除
一对多(掌握)
一对多的关系表:其中也有两个实体,但是其中A实体中的数据可以对应另外B实体中的多个数据,反过来B实体中的多个数据只能对应A实体中的一个数据
eg:作者和小说的关系、班级和学生、部门和员工、客户和订单等等
具体分析:
eg：作者和小说:一个作者可以写多部小说，但每部小说，只能对应具体的一个作者
注意(如何设计):如果是一对多的关系,那么设计表的时候需要在多的一方增加一列,引入一的一方的主键作为自己的外键
一对一
一对一关系表在实际开发中使用的并不多,其中也是两个实体,其中A实体中的数据只能对应B实体中的一个数据,同时B实体中的数据也只能对应A实体中的一个数据。例如:人和身份证对应关系、老公和老婆的对应关系。
注意:而一对一在建表的时候,可以在任意一方的表中添加另外一方的主键作为外键即可。
小结:
1.多对多：创建第三张表来维护两张主表的关系,在第三张表中起码要有两列来自于两张主表的主键
2.一对多:在多方增加一列,来自于一方的主键作为外键
3.一对一:在任意一方将另一方的主键作为外键
表设计案例(练习)
目标:设计学生成绩管理系统数据表(按照给定需求设计即可)
1.每个教师可以教多门课程
2.每个课程由一个老师负责
3.每门课程可以由多个学生选修
4.每个学生可以选修多门课程
5.学生选修课程要有成绩
讲解:
具体操作:
（1）分析:当我们拿到一个需求之后,首先应该分析这个需求中到底有多少名词，或者是当前这个需求中可以抽象出具体几个E-R图中的实体对象
分析需求中存在的实体:实体使用矩形表示。这个案例中的实体就是:学生、课程、老师。当分析清楚具体的实体之后,
接着要分析实体具备那些属性？属性使用椭圆形表示，学生:学号、姓名等。课程:课程编号、课程名称等。老师:工号、姓名等.
最后就要考虑实体和实体之间的关系问题:
老师和课程之间：一对多关系。一个老师可以教授多门课程，一个课程只能由一个老师负责。
学生和课程之间：多对多关系。每个学生可以选修多门课程，每门课程可以被多个学生来选修
(2)关于设计学生成绩管理系统的数据表的E-R图如下所示:
(https://www.icode9.com/i/l/?n=22&i=blog/2972588/202209/2972588-20220908141103367-196058841.png)
3）创建表的sql语句：画完E-R图之后,接下来我们就根据E-R图来创建具体的数据库表。
学生选课关系系统表的创建:
思考:先创建哪张表?
不能先创建课程表，因为课程表需要有教师的工号,也不能创建中间表,因为中间表需要课程表和学生表的id。
所以我们可以创建表的顺序如下:
–1.教师表
–2.课程表
–3.学生表
–4学生课程表(中间关系表要有成绩属性)
创建表的语句如下:
点击查看代码
--1.教师表
create table teacher(
id int primary key auto_increment,
name varchar(50)
);
--2.课程表
create table course(
id int primary key auto_increment,
name varchar(50),
teacher_id int,
foreign key(teacher_id) references teachers(id)
);
--3.学生表
create table student(
id int primary key auto_increment,
name varchar(50),
);
-- 4.学生课程表（中间关系表要有成绩属性）
create table studentcourse(
student_id int,
course_id int,
score double,
foreign key(student_id) references student(id),
foreign key(course_id) references course(id)
);
多表练习
一对多关系练习
以下案例是我们javaWeb课程最后的小项目，我们拿出其中一部分需求，
根据需求来设计数据库表之间的关系一个旅游路线分类中有多条旅游线路，一条旅游线路属于某一个分类,旅游线路表是多表，可以在多表上添加一个外键来执行分类表中的主键
具体操作:
点击查看代码
-- 创建旅游线路分类表
create table tab_category(
cid int primary key auto_increment,-- 旅游线路分类主键
cname varchar(100) not null unique -- 旅游线路分类名称
);
-- 添加旅游线路分类数据
insert into tab_category(cname) values('周边游'),('出境游'),('国内游'),('港澳游');
--创建旅游线路表
create table tab_route(
rid int primary key auto_increment,--旅游线路主键
rname varchar(100) not null unique,--旅游线路名称
price double not null,--价格
cid int not null,--所属分类
constraint ro_cid_ref_cate_id foreign key(cid) references
tab_categroy(cid)
);
多对多关系练习
一个用户可以收藏多个线路,一个线路可以被多个用户收藏,所以用户和线路之间是多对多的关系,对于多对多的关系我们需要增加一张中间表来维护他们之间的关系
具体操作:
点击查看代码
-- 创建用户表
create table tab_user(
uid int primary key auto_increment,--用户id
username varchar(100) not null unique--用户名
)；
--添加用户数据
insert into table_user values (NULL,'老王'),(NULL,'小王')；
--创建收藏表
create table tab_favorite(
fid int primary key auto_increment,--收藏主键
rid int not null,--旅游线路id
date date no null,--收藏时间
uid int not null --用户id
);
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/288305.html