数据库-外键
一、foreign key外键
员工信息表有三个字段：工号 姓名 部门
公司有3个部门，但是有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中插入数据，再往子表employee中插入数据
删除父表department中的数据，子表employee中的对应的记录也会删除
更新父表department，子表employee中对应的记录跟着修改
二、如何找出两张表之间关系
分析步骤:
1.先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
2.再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id
3.总结：
多对一：
如果只有步骤1成立，则是左表多对一右表；如果只有步骤2成立，则是右表多对一左表
多对多：
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
一对一：
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
三、建立表之间的关系
1.一对多
两张表：出版社和书，一个出版社可以出版多本书，一本书只有一个出版社
# 多对一 # 出版社表 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 );
2.多对多
两张表：作者和书，一个作者可以有多本书，一本书也可以有多个作者
# 多对多 # 作者表 create table author( id int primary key auto_increment, name varchar(20) ); # 书表 create table book( 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) );
3.一对一
两张表：学生表和客户表，一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
#一定是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 );
四、修改表
语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
五、复制表
复制表结构＋记录 （key不会复制: 主键、外键和索引） mysql> create table new_service select * from service; 只复制表结构 mysql> create table new1_service select * from service where 1=2;
