一 foreign key
一 快速理解foreign key
员工信息表有三个字段：工号 姓名 部门
公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费
解决方法：
我们完全可以定义一个部门表
然后让员工信息表关联该表，如何关联，即foreign key
#需求：创建员工表，需要员工信息与所属部门及职责 create table emp( id int primary key auto_increment, name char(8), gender enum('male','female'), dep_name char(8), dep_info char(20) ); insert into emp(name,gender,dep_name,dep_info) values ('张三','male','教育部','辅导学生，教授课程'), ('李四','male','技术部','开发软件，技术有限部门'), ('王二','male','教育部','辅导学生，教授课程'), ('麻子','male','教育部','辅导学生，教授课程'), ('小月','female','人事部','招收学生，安排入学'); #############创建结果############# +----+--------+--------+-----------+-----------------------------------+ | id | name | gender | dep_name | dep_info | +----+--------+--------+-----------+-----------------------------------+ | 1 | 张三 | male | 教育部 | 辅导学生，教授课程 | | 2 | 李四 | male | 技术部 | 开发软件，技术有限部门 | | 3 | 王二 | male | 教育部 | 辅导学生，教授课程 | | 4 | 麻子 | male | 教育部 | 辅导学生，教授课程 | | 5 | 小月 | female | 人事部 | 招收学生，安排入学 | +----+--------+--------+-----------+-----------------------------------+ 问题分析：每添加一个员工就需要在重新写一遍所属部门和职责，这样重复的代码太多，并且表的信息不够清晰 解决思路：将部门单独做成一张表，员工信息在创建时只需要添加一个部门id字段即可对应部门以及详细信息，那么两表之间的逻辑关联由谁来联系？答案就是外键（总不能让人拿着两张表去比对吧，这样也太'人工智能'了~~~）
create table dep( id int primary key auto_increment, dep_name char(8), dep_info char(20) ); insert dep(dep_name,dep_info) values ('销售部','联系客户，促谈生意'), ('教育部','辅导学生，教授课程'), ('技术部','写bug，调bug，写bug'); create table emp( id int primary key auto_increment, name char(8), gender enum('male','female'), dep_id int, foreign key(dep_id) references dep(id) ); insert emp(name,gender,dep_id) values ('对象1','male',2), ('对象2','male',2), ('对象3','female',1), ('对象4','male',3), ('对象5','male',2), ('对象6','female',1); 外键语法就是在关联表中使用foreign key(关联字段) references 被关联表名(被关联字段) ############上述操作结果############ +----+---------+--------+--------+ | id | name | gender | dep_id | +----+---------+--------+--------+ | 1 | 对象1 | male | 2 | | 2 | 对象2 | male | 2 | | 3 | 对象3 | female | 1 | | 4 | 对象4 | male | 3 | | 5 | 对象5 | male | 2 | | 6 | 对象6 | female | 1 | +----+---------+--------+--------+ +----+---------+--------+--------+ | id | name | gender | dep_id | +----+---------+--------+--------+ | 1 | 对象1 | male | 2 | | 2 | 对象2 | male | 2 | | 3 | 对象3 | female | 1 | | 4 | 对象4 | male | 3 | | 5 | 对象5 | male | 2 | | 6 | 对象6 | female | 1 |
# foreign key会带来什么样的效果？ #1、约束1：在创建表时，先建被关联的表dep，才能建关联表emp #2、约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp #3、设置外键增加语句on update cascade on delete cascade与外键设置为一句话 实现同步删除同步更新
二 如何找出两张表之间的关系
分析步骤： #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), ('九阴真经',2), ('九阴白骨爪',2), ('独孤九剑',3), ('降龙十巴掌',2), ('葵花宝典',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'); #每个作者与自己的代表作如下 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) ;
#一对一
两张表：学生表和客户表
一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
关联方式：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
三 练习
#用户表 create table user( id int not null unique auto_increment, 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) ;
练习2：
