MySQL 之外键

键

说到表和表之间的关系，我们不得不说一个关键词：外键

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

外键(foreign  key)又称外连接， 在数据库中发挥着重要作用  特别是表与表的关系尤为重要

示例说明：

员工信息表有三个字段：工号 姓名 部门 怎样才能把它们联系起来？

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

解决方法：

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

#表型必须是innodb存储引擎，相关字段，即references另一个指定表的字段必须保证唯一 create table department( id int primary key, name varchar(20) not null )engine=innodb;  #dpt_id外键，关联父表（department主键id），同步更新，同步删除 create table employee( id int primary key, name varchar(20) 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 (1,欧德博爱技术有限公司， (2，艾利克斯人力资源部)， (3,'销售部');   #再往子表employee中插入记录 insert into employee values (1,'egon',1), (2,'alex1',2), (3,'alex2',2), (4,'alex3',2), (5，李坦克，3)， (6，刘飞机，3)， (7，张火箭，3)， (8，林子弹，3)， (9,'加特林',3) ;   #删父表department，子表employee删除相应的记录 mysql> delete from department where id=3; mysql> select * from employee;  ---- ------- --------  | id | name  | dpt_id |  ---- ------- --------  |  1 | egon  |      1 | |  2 | alex1 |      2 | |  3 | alex2 |      2 | |  4 | alex3 |      2 |  ---- ------- --------    #更新父表department，子表employee相应的记录随之改变 mysql> update department set id=22222 where id=2; mysql> select * from employee;  ---- ------- --------  | id | name  | dpt_id |  ---- ------- --------  |  1 | egon  |      1 | |  3 | alex2 |  22222 | |  4 | alex3 |  22222 | |  5 | alex1 |  22222 |  ---- ------- -------- 

以上示例 我们可以发现表和表之间有一定的关系那么 如何找出表与表的关系？

分析步骤： #1.先从左表的角度找 左表的多个记录是否可以对应于右表的一个记录，如果是，则证明左表的一个字段foreign key 右表一个字段(通常是id）  #2、再站在右表的角度去找 如果是的话，右表的多个记录是否可以对应左表的一个记录，证明右表的一个字段foreign key 左表一个字段(通常是id）  #3、总结： #多对一： 如果只有步骤1立，则左表多对一右表 如果只有步骤2，则右表多对一左表  #多对多 若步骤1和2同时成立，则证明这两张表是双向多对一，即多对多，两者之间的关系需要定义一个关系表来存储  #一对一: 若1和2都不成立，则左表的记录唯一对应右表的记录，反之亦然。这种情况很简单，就是在左表foreign key在右表的基础上，设置左表的外键字段unique即可

表以通过上述方法找到表与表之间的关系 既然找到了这种关系或者关系， 我们可以用表来表达它们之间的关系(即表与表之间的关系):

表与表的关系

一对多或多对一

三张表：出版社，作者信息，书   实现三者的联系  一对多(或多对一):一个出版社可以出版多本书   关联方式：foreign key  =====================多对一===================== create table press( id int primary key auto_increment, name varchar(20) );  create table book( id int primary key auto_increment, name varchar(20), 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 (九阳神功，1)， (九阴真经，二)， (九阴白骨爪，二)， (独孤九剑，3)， (二)， 3)

多对多

三张表：出版社、作者信息、书籍   实现相互关联  多对多：一个作者可以写多本书，一本书也可以有多个作者，双向一对多，即多对多    关联方式：foreign key 一张新的表 =====================多对多===================== create table author( id int primary key auto_increment, name varchar(20) );   #本表存储作者表与书表之间的关系，即查询两者之间的关系，查看本表。 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');  #每个作者及其代表作如下 1 egon:        1 九阳神功       2 九阴真经       3 九阴白骨爪       4 独孤九剑       5 降龙十巴掌       6 葵花宝典   2 alex:        1 九阳神功       6 葵花宝典  3 yuanhao:       4 独孤九剑       5 降龙十巴掌       6 葵花宝典  4 wpq:       1 九阳神功   insert into author2book(author_id,book_id) values (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (2,1), (2,6), (3,4), (3,5), (3,6), (4,1) ;

一对一

#一对一 两张表：学生表和客户表  实现相互关联  一对一：学生是客户，客户可能成为学校，即一对一关系  关联方式：foreign key unique #一定是student来foreign key表customer，这样保证: #1 学生必须是客户， #2 客户不一定是学生，但可能是学生   create table customer( id int primary key auto_increment, name varchar(20) not null );   create table student( id int primary key auto_increment, name varchar(20) not null, class_name varchar(20) not null default 'python自动化', level int default 1, 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 (李飞机，1)， (王大炮，2) ;

相关练习：

账户信息表、用户组、主机表、主机组

#用户表 create table user( id int not null unique auto_ncrement,
username varchar(20) not null,
password varchar(50) not null,
primary key(username,password)
);

insert into user(username,password) values
('root','123'),
('egon','456'),
('alex','alex3714')
;


#用户组表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20) not null unique
);

insert into usergroup(groupname) values
('IT'),
('Sale'),
('Finance'),
('boss')
;


#主机表
create table host(
id int primary key auto_increment,
ip char(15) 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(20) 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
(1,1),
(1,2),
(1,3),
(1,4),
(2,3),
(2,4),
(3,4)
;



#建关系：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
(1,1),
(1,2),
(1,3),
(2,2),
(2,3),
(3,4)
;

#建关系：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
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,7),
(1,8),
(1,9),
(1,10),
(1,11),
(1,12),
(1,13),
(1,14),
(1,15),
(1,16),
(2,2),
(2,3),
(2,4),
(2,5),
(3,10),
(3,11),
(3,12)
;