MYSQL--表与表之间的关系、修改表的相关操作
如果所有信息都在一张表中：
1、表的结构不清晰
2、浪费硬盘空间
3、表的扩展性变得极差（致命的缺点）
确立表与表之间的关系。一定要换位思考（必须在两者考虑清楚之后才能得出结论）
以员工和部门为例：
员工：一个员工只能对应一个部门
部门：一个部门同时包含了很多员工
结论：员工表和部门表之间仅仅是单向的多对一，所以他们的关系就是"多对一"
在表关系中没有多对一一说，是说一对多，无论是一对多还是多对一，都叫"一对多"
如何让两张表在代码层面上产生真正的关联。这时候就必须使用外键：
外键：让表和表在硬性层面上产生关系
foreign key：外键约束
1、在创建表的时候。必须先创建被关联的表
2、在插入数据的时候。必须先插入被关联表的数据
建表：
create table dep( id int primary key auto_increment, dep_name varchar(32), dep_desc varchar(128) ); create table emp( id int primary key auto_increment, emp_name varchar(64), emp_gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) );
insert into dep(dep_name,dep_desc) values('外交部','搞外交'), ('教学部','教书育人'), ('技术部','技术能力有限部门') ; insert into emp(emp_name,dep_id) values('jason',1), ('egon',2), ('tank',2), ('kevin',3)
;
修改数据：
update dep set id=200 where id = 1; update emp set dep_id = 300 where id = 1; delete from dep where id = 3;
外键虽然能够帮你强制建立表关系。但是也会给表之间增加数据相关的约束
delete from emp where id = 4; delete from dep where id = 3;
2、级联更新级联删除
create table dep( id int primary key auto_increment, dep_name varchar(32), dep_desc varchar(128) ); create table emp( id int primary key auto_increment, emp_name varchar(64), emp_gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade on delete cascade ); insert into dep(dep_name,dep_desc) values('外交部','搞外交'), ('教学部','教书育人'), ('技术部','技术能力有限部门') ; insert into emp(emp_name,dep_id) values('jason',1), ('egon',2), ('tank',2), ('kevin',3); update dep set id=200 where id = 3; delete from dep where id = 2;
多对多：
实例：图书与作者之间的关系
图书：一本书能够拥有多个作者
作者：一个作者能够写多本书
如果双方都可以的话， 那么这种关系就是多对多
强调：foreign key 只是用来帮你建立表关系的。不是某个关系特有的方法
1、先建立两种普通的表。不需要设置外键：
create table book( id int primary key auto_increment, title varchar(32), price int ); create table author( id int primary key auto_increment, name varchar(32), age int );
2、手动建立第三张表，用来专门记录两种表之间的关系
create table book2author( id int primary key auto_increment, book_id int, foreign key(book_id) references book(id) on update cascade on delete cascade, author_id int, foreign key(author_id) references author(id) on update cascade on delete cascade );
3、插入数据
insert into book(title,price) values('金瓶',199),('聊斋',299),('jason教你删别人的库,让别人跑去吧',1); insert into author(name,age) values('jason',18),('tank',38); insert into book2author(book_id,author_id) values(4,3); # 报错 insert into book2author(book_id,author_id) values(1,1),(1,2),(2,1),(3,1),(3,2);
一对一的建表关系：
1、一对一主要用在当你的表特别庞大，这时候你就可以考虑将这个表拆分开来
可以联想到客户和学生
create table authordetail1( id int primary key auto_increment, phone int, addr char(255) ); create table author1( id int primary key auto_increment, name char(4), age int, authordetail_id int unique, foreign key(authordetail_id) references authordetail1(id) on update cascade on delete cascade );
总结：通常将关系字段称之为外键字段
一对多的外键字段 建在多的一方
多对多 建在第三张表
一对一 外键字段可以在任意一方（但是推荐你建在查询频率比较高的一方）
1、修改表名称 ALTER TABLE 表名 RENAME 新表名; 2、增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; # 直接移到最前面 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 寻找插哪个字段的后面 3、删除字段 ALTER TABLE 表名 DROP 字段名; 4、修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！ ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; 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;
