外键、复制表、修改表
前言
提出问题
所有的信息都记录在同一张表中所带来的问题： 1. 表的结构不清晰 2. 浪费硬盘空间 3. 表的扩展性差（这一点是无法忽略的）
解决方案
解耦，拆分表
拆分表 就要搞明白表与表之间的关系
如何确认表与表之间的关系：
“换位思考”，必须两方都考虑周全之后才能得出结论
举例：员工表和部门表之间的关系：
先站在员工表看是否可以：
多个员工对应一个部门
翻译过来：一个部门是否能有多个员工
可以（所以暂时确认了员工反向多对一部门）
再站在部门表看是否可以：
多个部门对应一个员工
翻译过来：一个员工是否可以属于多个部门
不可以
结论：员工表和部门表之间 仅仅是单向的多对一
那么他们的关系就是“一对多”
注意：关系表中没有多对一之说，只有一对多（无论是多对一还是一对多，都叫一对多）
表关系之 一对多
1. 在创建表示，先创建被关联的表dep，才能创建关联表emp
create table dep ( id int primary key auto_increment, dep_name char(10), dep_comment char(60) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male', 'female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) );
2. 在插入记录是，必须先插入被关联表dep，才能插入关联表emp
insert into dep(dep_name, dep_commont) values ('教学部'， '辅导学生')， ('外交部', '形象公关')； insert into emp(name, gender, dep_id) values ('alex', 'male', 1), ('egon', 'male', 2), ('xxx', 'male', 1);
3. 删除
问题：无法随意删除
1. 当想删除“被关联表dep”的id 或者emp的dep_id时，都报错 2. 想整个删除“被关联表dep”的某个字段 如教学部时，也报错
因为这些都是被关联的东西，无法直接删除
临时处理方案：
方式一：先删除教学部对应的所有员工（相当于解除了关联关系），就可以删除教学部了
方式二：新的方法，如下：
问题描述： 受限于外键的约束，导致操作数据变得非常复杂， 能否有一个简单的方式，让我们不需要考虑在操作目标表的时候还需要考虑关联表的情况 比如删除部门的时候，那么这个部门对应的员工就应该自动跟着立即清空（这也是符合常理的） 解决方案： 在创建表的时候，加上两个东西：on udate cascade 和 on delete cascade # 删除部门后，对应的部门里面的员工表数据对应删除 # 更新部门后，对应员工表中的标示部门的字段同步更新
create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade on delete cascade ); insert into dep(dep_name,dep_comment) values ('sb教学部','sb辅导学生学习，教授python课程'), ('外交部','老男孩上海校区驻张江形象大使'), ('nb技术部','nb技术能力有限部门'); insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3);
表关系之 多对多
# 图书表与作者表之间的关系 """ 仍然站在两张表的角度： 1.站在图书表：一本书可不可以有多个作者，可以！那就是书多对一作者 2.站在作者表：一个作者可不可以写多本书，可以！那就是作者多对一书 双方都能一条数据对应对方多条记录，这种关系就是多对多！ """ # 先来想如何创建表？ 图书表需要有一个外键关联作者， 作者也需要有一个外键字段关联图书。 问题来了，先创建谁都不合适！如何解决？
# 建立第三张表，该表中有一个字段是左表的id，有一个字段是右表的id
create table author( id int primary key auto_increment, name char(16) ); create table book( id int primary key auto_increment, bname char(16), price int ); insert into author(name) values ('egon'), ('alex'), ('wxx'); insert into book(bname,price) values ('python从入门到入土',200), ('葵花宝典切割到精通',800), ('九阴真经',500), ('九阳神功',100); create table author2book( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, foreign key(book_id) references book(id) on update cascade on delete cascade ); insert into author2book(author_id,book_id) values (1,3), (1,4), (2,2), (2,4), (3,1), (3,2), (3,3), (3,4);
表关系之 一对一
客户表和学生表 (老男孩的客户与学生之间,报名之前都是客户，只有报了名的才能是学生) # 左表的一条记录唯一对应右表的一条记录，反之也一样
create table customer( id int primary key auto_increment, name char(20) not null, qq char(10) not null, phone char(16) not null ); create table student( id int primary key auto_increment, class_name char(20) not null, customer_id int unique, # 该字段一定要是唯一的 foreign key(customer_id) references customer(id) # 外键的字段一定要保证unique on delete cascade on update cascade ); # 三种外键关系都是用foreign key，区别在于如何使用以及其他条件限制即可做出三种关系
表关系之 小结
通常将关系字段 称之为 外键字段
一对多的外键字段 建在多的一方
多对多 建在第三张表了
一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率较高的一方
修改表
# mysql对大小写不敏感！！！ 语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！ ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
复制表
# 查询语句执行的结果也是一张表，可以看成虚拟表 # 复制表结构＋记录 （key不会复制: 主键、外键和索引） create table new_service select * from service; # 只复制表结构 select * from service where 1=2; //条件为假，查不到任何记录 create table new1_service select * from service where 1=2; create table t4 like employees;
练习
# 班级表 cid caption # 学生表 sid sname gender class_id # 老师表 tid tname # 课程表 cid cname teacher_id # 成绩表 sid student_id course_id number
表之间主外键关系可以参考：
# 班级表
create table class(cid int primary key auto_increment, caption char(16));
# 老师表
create table teacher(tid int primary key auto_increment, tname char(16));
# 学生表
create table student(sid int primary key auto_increment, aname char(16), gender enum('female','male') default 'male', class_id int,foreign key(class_id) references class(cid) on update cascade on delete cascade);
# 课程表
create table course(cid int primary key auto_increment, cname char(16), teacher_id int, foreign key(teacher_id) references teacher(tid) on update cascade on delete cascade);
# 成绩表
create table score(sid int primary key auto_increment, student_id int, foreign key(student_id) references student(sid) on update cascade on delete cascade, course_id int, foreign key(course_id) references course(cid) on update cascade on delete cascade, number int);
# 班级 insert into class(caption) values('三年级二班'),('一年级三班'),('三年级一班'); # 老师 insert into teacher(tname) values('波多'),('苍空'),('饭岛'); # 学生表 insert into student(sname,gender,class_id) values('钢蛋','female',1),('铁锤','female',1),('山炮','male',2); # 课程表 insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2); # 成绩表 insert into score values(1,1,1,60),(2,1,2,59),(3,2,2,100);
