MySQL表与表之间的关系详解

时间：2022-05-11 15:26:17

外键

说到表与表之间的关系就不得不说到一个关键词：外键

MySQ中的外键是什么，和表与表之间有什么关联？

外键(foreign  key)又叫外连接， 在数据库中发挥着重要的作用  尤其是对于表和表之间的关系尤为重要

通过示例说明：

员工信息表有三个字段：工号  姓名  部门      如何把他们相互联系起来呢？？

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法：

　　我们完全可以定义一个部门表，然后让员工信息表关联该表，如何关联，即foreign key

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一

create table department(

id int primary key,

name varchar() not null

)engine=innodb;

#dpt_id外键，关联父表（department主键id），同步更新，同步删除

create table employee(

id int primary key,

name varchar() not null,

dpt_id int,

constraint fk_name foreign key(dpt_id)

references department(id)

on delete cascade

on update cascade

)engine=innodb;

#先往父表department中插入记录

insert into department values

(,'欧德博爱技术有限事业部'),

(,'艾利克斯人力资源部'),

(,'销售部');

#再往子表employee中插入记录

insert into employee values

(,'egon',),

(,'alex1',),

(,'alex2',),

(,'alex3',),

(,'李坦克',),

(,'刘飞机',),

(,'张火箭',),

(,'林子弹',),

(,'加特林',)

;

#删父表department，子表employee中对应的记录跟着删

mysql> delete from department where id=;

mysql> select * from employee;

+----+-------+--------+

| id | name  | dpt_id |

+----+-------+--------+

|   | egon  |       |

|   | alex1 |       |

|   | alex2 |       |

|   | alex3 |       |

+----+-------+--------+

#更新父表department，子表employee中对应的记录跟着改

mysql> update department set id= where id=;

mysql> select * from employee;

+----+-------+--------+

| id | name  | dpt_id |

+----+-------+--------+

|   | egon  |       |

|   | alex2 |   |

|   | alex3 |   |

|   | alex1 |   |

+----+-------+--------+

示例详解

通过上面的示例  我们可以发现：其实表和表之间是存在一定的关系的  那么  我们怎么找出表和表之间的关系呢？？

分析步骤：

#、先站在左表的角度去找

是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#、再站在右表的角度去找

是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#、总结：

#多对一：

如果只有步骤1成立，则是左表多对一右表

如果只有步骤2成立，则是右表多对一左表

#多对多

如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:

如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

找出表和表之间的关系

通过以上的方法可以找到表和表之间的 关系，既然找到了这种关系或者叫关联  我们就可以用表把他们之间的关联表现出来(即表与表之间的关系)：

表和表之间的关系

一对多或者叫多对一

三张表：出版社，作者信息，书   实现三者的联系

一对多（或多对一）：一个出版社可以出版多本书   关联方式：foreign key

=====================多对一=====================

create table press(

id int primary key auto_increment,

name varchar()

);

create table book(

id int primary key auto_increment,

name varchar(),

press_id int not null,

foreign key(press_id) references press(id)

on delete cascade

on update cascade

);

insert into press(name) values

('北京工业地雷出版社'),

('人民音乐不好听出版社'),

('知识产权没有用出版社')

;

insert into book(name,press_id) values

('九阳神功',),

('九阴真经',),

('九阴白骨爪',),

('独孤九剑',),

('降龙十巴掌',),

('葵花宝典',)

示例素材及详解

多对多

三张表：出版社，作者信息，书   实现相互关联

多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多

　　

关联方式：foreign key+一张新的表

=====================多对多=====================

create table author(

id int primary key auto_increment,

name varchar()

);

#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了

create table author2book(

id int not null unique auto_increment,

author_id int not null,

book_id int not null,

constraint fk_author foreign key(author_id) references author(id)

on delete cascade

on update cascade,

constraint fk_book foreign key(book_id) references book(id)

on delete cascade

on update cascade,

primary key(author_id,book_id)

);

#插入四个作者，id依次排开

insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

#每个作者与自己的代表作如下

 egon:

       九阳神功

       九阴真经

       九阴白骨爪

       独孤九剑

       降龙十巴掌

       葵花宝典

 alex:

       九阳神功

       葵花宝典

 yuanhao:

       独孤九剑

       降龙十巴掌

       葵花宝典

 wpq:

       九阳神功

insert into author2book(author_id,book_id) values

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,)

;

示例

一对一

#一对一

两张表：学生表和客户表  实现相互关联

一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系

关联方式：foreign key+unique

#一定是student来foreign key表customer，这样就保证了：

# 学生一定是一个客户，

# 客户不一定是学生，但有可能成为一个学生

create table customer(

id int primary key auto_increment,

name varchar() not null

);

create table student(

id int primary key auto_increment,

name varchar() not null,

class_name varchar() not null default 'python自动化',

level int default ,

customer_id int unique, #该字段一定要是唯一的

foreign key(customer_id) references customer(id) #外键的字段一定要保证unique

on delete cascade

on update cascade

);

#增加客户

insert into customer(name) values

('李飞机'),

('王大炮'),

('守榴弹'),

('吴坦克'),

('赢火箭'),

('战地雷')

;

#增加学生

insert into student(name,customer_id) values

('李飞机',),

('王大炮',)

;

示例素材及详解

相关练习题：

　　账号信息表，用户组，主机表，主机组

#用户表

create table user(

id int not null unique auto_increment,

username varchar() not null,

password varchar() not null,

primary key(username,password)

);

insert into user(username,password) values

('root',''),

('egon',''),

('alex','alex3714')

;

#用户组表

create table usergroup(

id int primary key auto_increment,

groupname varchar() not null unique

);

insert into usergroup(groupname) values

('IT'),

('Sale'),

('Finance'),

('boss')

;

#主机表

create table host(

id int primary key auto_increment,

ip char() not null unique default '127.0.0.1'

);

insert into host(ip) values

('172.16.45.2'),

('172.16.31.10'),

('172.16.45.3'),

('172.16.31.11'),

('172.10.45.3'),

('172.10.45.4'),

('172.10.45.5'),

('192.168.1.20'),

('192.168.1.21'),

('192.168.1.22'),

('192.168.2.23'),

('192.168.2.223'),

('192.168.2.24'),

('192.168.3.22'),

('192.168.3.23'),

('192.168.3.24')

;

#业务线表

create table business(

id int primary key auto_increment,

business varchar() not null unique

);

insert into business(business) values

('轻松贷'),

('随便花'),

('大富翁'),

('穷一生')

;

#建关系：user与usergroup

create table user2usergroup(

id int not null unique auto_increment,

user_id int not null,

group_id int not null,

primary key(user_id,group_id),

foreign key(user_id) references user(id),

foreign key(group_id) references usergroup(id)

);

insert into user2usergroup(user_id,group_id) values

(,),

(,),

(,),

(,),

(,),

(,),

(,)

;

#建关系：host与business

create table host2business(

id int not null unique auto_increment,

host_id int not null,

business_id int not null,

primary key(host_id,business_id),

foreign key(host_id) references host(id),

foreign key(business_id) references business(id)

);

insert into host2business(host_id,business_id) values

(,),

(,),

(,),

(,),

(,),

(,)

;

#建关系：user与host

create table user2host(

id int not null unique auto_increment,

user_id int not null,

host_id int not null,

primary key(user_id,host_id),

foreign key(user_id) references user(id),

foreign key(host_id) references host(id)

);

insert into user2host(user_id,host_id) values

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,),

(,)

;

相关文章