mysql-多表之间的关系

在关系型数据库中，表与表之间的关系是通过主键与外键来体现的。而表与表之间的关系主要有三种：一对一关系，一对多（多对一）关系，多对多关系。

一对一关系

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

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

1、准备两张表

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

image.png

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

image.png

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

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

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,'李四');

image.png

mysql> insert into wife(wid,wname) value(1,'三姑');

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

image.png

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

image.png

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

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

image.png

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

一对多（多对一）关系

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

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

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

image.png

image.png

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

image.png

image.png

2、测试

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

mysql> insert into dept(dno,dname) value(2,'w销售部');

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,'八姨');

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)
     );

image.png

//老师表
mysql> create table teacher(
    -> tid varchar(20) primary key,
    -> tname varchar(20)
    -> );

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)
    -> );

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);

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,'李世民');

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);

image.png

image.png

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

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