8-[表操作]--foreign key、表与表的关系

1、 foreign key

　　（1）快速理解foreign key

　　员工信息表有三个字段：工号 姓名 部门

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

 　　　　

　　解决方法：

　　我们完全可以定义一个部门表

　　然后让员工信息表关联该表，如何关联，即foreign key

 　　

　　（2）建立表关系

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

# 先建立被关联的表，并且保证被关联的字段唯一
create table dep(
    id int primary key,
    name char(16),
    comment char(50)
);


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

# 再建立关联的表
create table emp(
    id int primary key,
    name char(10),
    sex enum('male','female','other'),
    dep_id int,
    foreign key(dep_id) references dep(id) on delete cascade  on update cascade
);

　　

       foreign key(dep_id) references dep(id) on delete cascade  on update cascade
	# 外键  dep_id,     引用      dep 表的id字段    删除，更新   级联

　　

　　

　　　　

　　　　

　　（2）插入数据

    
#先往被关联表插入记录 
#先往父表department中插入记录

insert into dep values
(1,"IT","技术能力有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");

#再往关联表插入记录
#再往子表employee中插入记录
insert into emp values
(1,'egon','male',1);

insert into emp values
(2,'alex','male',1),
(3,'wupeiqi','female',2),
(4,'yuanhao','male',3),
(5,'jinximn','male',2);

 

　　（4）删除更新

#删父表department，子表employee中对应的记录跟着删
delete from dep where id=3;

#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=22222 where id=2;

 

 

2、表关系

两张表之间的关系：
    # 1、多对一或一对多
        一个出版社可以出版多本书
        一夫多妻制：妻子表的丈夫id外键到丈夫表的id
        关联方式：foreign key
        
    # 2、多对多
        一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
        关联方式：foreign key+一张新的表
        
    # 3、一对一
        customer表       student表
        一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
        关联方式：foreign key+unique
    

　　（1）多对一：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)    # 关联父表的主键id
on delete cascade           # 同步更新，删除
on update cascade
);    

#插入data
# 先插入父表的
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;

# 在插入关联表的
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;

　　

 　　（2）多对多: 联合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');

#每个作者与自己的代表作如下
egon: 
    九阳神功
    九阴真经
    九阴白骨爪
    独孤九剑
    降龙十巴掌
    葵花宝典
alex: 
    九阳神功
    葵花宝典
yuanhao:
    独孤九剑
    降龙十巴掌
    葵花宝典
wpq:
    九阳神功

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

　　　　　

 　　（3）一对一：foreign key + unique

 

　　　　

#一定是student来foreign key表customer，这样就保证了：
#1 学生一定是一个客户，
#2 客户不一定是学生，但有可能成为一个学生

　　

# 客户表
create table customer(
id int primary key auto_increment,
name varchar(20) not null,
qq varchar(10) not null,
phone char(16) not null
);

# 学生表
create table student(
id int primary key auto_increment,
class_name varchar(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);

#增加客户
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;


#增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5)
;

 

　　　　　　　　

例一：一个用户只有一个博客

    用户表：
    id  name
   egon
   alex
   wupeiqi


    博客表   
           fk+unique
    id url name_id
 xxxx   1
 yyyy   3
 zzz    2



例二：一个管理员唯一对应一个用户
    用户表：
    id user  password
 egon    xxxx
 alex    yyyy

    管理员表：
       fk+unique
    id user_id password
  1      xxxxx
  2      yyyyy

其他例子