mysql-多表之间的关系

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql-多表之间的关系

harry_h 2018-06-28 09:03:00 浏览696
展开阅读全文

在关系型数据库中,表与表之间的关系是通过主键与外键来体现的。而表与表之间的关系主要有三种:一对一关系,一对多(多对一)关系,多对多关系。

一对一关系

在表中体现一对一关系是比较特殊的,它需要让其中一张表中的主键,既是主键又是外键!!

生活中比较经典的一对一关系的体现就是夫妻间一夫一妻制,那要怎么在数据中体现这种夫妻关系呢?

1、准备两张表

//老公表
mysql> create table husband(
    -> hid int primary key,    //老公id,主键,表示在表中取唯一值
    -> hname varchar(20)   //老公的名字,可以为空
    -> );
img_5fa4f57b5754c69e783ef2cdf0d2abba.png
image.png
//老婆表
mysql> create table wife(
    -> wid int primary key,  //老婆id,主键,表示在表中取唯一值
    -> wname varchar(20)   //老婆的名字,可以为空
    -> );
img_9872fedf8dd1605e98386d7e20cb3498.png
image.png

2、让其中一张表的主键既是主键也是外键;在这里两张表的主键我们已经指定了,只需要把其中一张表的主键作为另一张表的外键就可以了。在这里我们选择老公表作为主表,老婆表作为从表,让老婆表关联老公表,也就是让老婆表的主键既是主键也是外键。

mysql> alter table wife
    -> add constraint FK_wife_husband
    -> foreign key(wid)
    -> references husband(hid);
img_81c65ac0d1ec61d26f586b29066baef4.png
image.png

其实也可以在创建老婆表的时候直接添加外键约束

mysql> create table wife(
    -> wid int primary key,  //老婆id,主键,表示在表中取唯一值
    -> wname varchar(20) ,  //老婆的名字,可以为空
    -> foreign key(wid) references husband(hid)
    -> );

3、这样一对一系就用关系表体现出来了,下面插入数据来测试一下

mysql> insert into husband(hid,hname) value(1,'张三');

mysql> insert into husband(hid,hname) value(2,'李四');
img_7471b46f4b3ebb14e988bd4a00a54190.png
image.png
mysql> insert into wife(wid,wname) value(1,'三姑');

mysql> insert into wife(wid,wname) value(2,'八姨');
img_b379916eca32ffb85ce13bdc33b6e5cb.png
image.png

从以上测试数据来看,这两个表是已经正常工作了,张三的老婆是三姑,李四的老婆是八姨,两张表主键的值是一一对应的,而且主表(被参照的表,在这里是老公表)的数据必须先于从表(参照表,在这里是老婆表)的数据而存在,而且从表里的主键值必须在主表主键值里面取,否则会出错。
下面来测试一下,在从表(老婆表)添加一条主键没有在主表(老公表)的记录:


img_cc28e7a92f37dcd727aa8aadd9826806.png
image.png

操作结果出错了:不能添加一条违背外键约束的记录。

再测试一下,一夫二妻制看一下能不能行

img_27c780e69de3a2823e947843ba706ae9.png
image.png

操作也出错了:违背了主键约束。
4、总结:

  • 一对一关系的体现是让其中一张表中的主键,既是主键又是外键!!
  • 主表和从表的主键值必须一一对应
  • 主表的数据必须先于从表的数据而存在,因为从表的主键值要参照主表的主键值

一对多(多对一)关系

一对多关系,也可以叫做多对一关系,这是从不同角度出发的而决定的。
一对多关系创建的原则:多引用一,多为从表,一为主表

在是常生活中最见的一对多关系,比如:员工和部的关系,在同一公司中,一个员工只能所属一个部门,一个部门可以有多名员工;再比如:学生和班级的关系,一个学生只能所属一个班级,而一个班级却可以有多名学生。除了这两个例子之外还有很多……
在这里拿的员工和部门来举例
1、老样子,还是要先准备所需要的表

//部门表
mysql> create table dept(
    -> dno int primary key,   //部门id,主键
    -> dname varchar(20)   //部门名称
    -> );
img_946e1ac3c51f7409aecfa15ae1faa490.png
image.png

img_74b74e16e2297bf8c31858d4910c0936.png
image.png
mysql> create table emp(
    -> eno int primary key,   //员工id,主键
    -> ename varchar(20),  //员工名字
    -> dno int, 
    -> foreign key(dno) references dept(dno) //直接在创建表的时候添加外键约束
    -> );
img_9d3b0407f81f8f3a3b7760bd9cc09b0c.png
image.png

img_0d27ba2edf2659b21c851ee9cd593b70.png
image.png

2、测试

mysql> insert into dept(dno,dname) value(1,'财务部');

mysql> insert into dept(dno,dname) value(2,'w销售部');
img_6951c2c7c373efcb22995d9e00ffef10.png
image.png
mysql> insert into emp(eno,ename,dno) value(1,'张三',1);

mysql> insert into emp(eno,ename,dno) value(2,'李四',1);

mysql> insert into emp(eno,ename,dno) value(3,'王五',1);

mysql> insert into emp(eno,ename,dno) value(4,'翠花',2);

mysql> insert into emp(eno,ename,dno) value(5,'三姑',2);

mysql> insert into emp(eno,ename) value(6,'八姨');
img_6f8ce790311122137a24ca6baece9c13.png
image.png

从测试数据中可以看到,在一对多关系中,外键是可以重复的,这就体现了一对多的关系,即一个部门可以有多名员工,但是每一位员工的对应的外键值都是唯一的。外键也可以是空,这代表员工目前没有所属部门。
3、总结

  • 在一对多关系中,从表的外键可以为空
  • 若从表的外键不为空,则其值必须在被参照的主表主键值上取

多对多关系

多对多关系的体现需要另外建立一张表来存放多对多关系,而不是在原来的表上引入外键。

生活中最常见多对多的例子就是学生与老师的关系,一个学生有多个老师,一个老师也会有很多的学生,这里要体现学生与老师的多对多关系,就要建立第三张多对多的关系表。

1、准备需要的表

//学生表
create table student(
     sno char(9) primary key,   //设置sno为主键
     sname char(20) unique,   //设置sname取唯一值
     ssex char(2),
     sage smallint,
     sdept char(20)
     );
img_4596a7df0400626e96dcbaad4f321305.png
image.png
//老师表
mysql> create table teacher(
    -> tid varchar(20) primary key,
    -> tname varchar(20)
    -> );
img_f34855e53aa8c8d62e3d8575f1c0ae8c.png
image.png
//多对多关系表
mysql> create table stu_tea(
    -> sno char(9),
    -> tid varchar(20),
    -> foreign key(sno) references student(sno),
    -> foreign key(tid) references teacher(tid)
    -> );
img_6f4d94d62ffbec96ae561a56668f4adb.png
image.png

2、插入测试数据

//为学生表插入数据
INSERT INTO `student` (`sno`, `sname`, `ssex`, `sage`, `sdept`, `s_entrance`) VALUES
    ('201215121', '李勇', '男', 20, 'CS', NULL),
    ('201215122', '刘晨', '女', 19, 'CS', NULL),
    ('201215123', '王敏', '女', 18, 'MA', NULL),
    ('201215125', '张立', '男', 19, 'IS', NULL);
img_8fdcd5b74a8e9327e06bc633b61dfa54.png
image.png
//为老师表插入数据
mysql> insert into teacher(tid,tname) value(101,'张三丰');

mysql> insert into teacher(tid,tname) value(102,'唐三藏');

mysql> insert into teacher(tid,tname) value(103,'李世民');
img_ec487a17dea3103a77c9663d01a42d32.png
image.png
//为学生老师关系表插入数据
mysql> insert into stu_tea(sno,tid) values(201215121,101),
    -> (201215121,102),
    -> (201215121,103),
    -> (201215122,101),
    -> (201215122,102),
    -> (201215122,103),
    -> (201215123,103),
    -> (201215123,102),
    -> (201215125,101),
    -> (201215125,103);
img_0f25ed64bbf4a7c3c04dae8601aaca63.png
image.png

img_508e4792f48eefcd296cf62f39e9d60f.png
image.png

从测试数据可以看出,多对多关系跟一对多关系大同小异,只不过多对多的关系体现需要第三张表,所以可以得出以下结论:

  • 多对多关系的体现需要第三张表,而且表里的字段都是主表上的主键
  • 在多对多关系中,第三张关系表的外键可以为空
  • 若关系表的外键不为空,则其值必须在被参照的主表主键值上取

网友评论

登录后评论
0/500
评论
harry_h
+ 关注