MySql的外键以及表操作

外键

"""
把所有数据都存放与一张表的弊端:
1.组织结构不清晰
2.浪费硬盘空间
3.扩展性极差
"""
# 上述弊端产生原因类似于把所有代码写在同一个python文件中，我们常用的优化方法就是解耦合

# 如何查找表与表之间的关系
"""
以员工和部门表为例。查找表关系需要做到换位思考(站在两边去找表关系)
先站在员工表:
    找员工表的多条数据能否对应部门表的一条数据
    翻译:
        多个员工能否属于一个部门
        可以！之后不能直接下结论，还需要站在部门表的角度再确认关系

再站在部门表:
    找部门表的多条数据能否对应员工表的一条数据
    翻译:
        多个部门能否有同一个员工
        不可以！
只有站在两边表的角度都分析过了，才能够下结论
员工表单向多对一部门表
"""
# 员工表中的dep_id 可以随意更改，但是应该限制dep_id字段必须只是部门表已有的id字段才合理

一对多(Foreign Key)

# foreign key 会带来什么样的效果
# 1.在创建表时，先建立被关联的表dep，才能见关联表emp 否则会报错
create table dep(
	id int primary key auto_increment,
	dep_name char(16),
	dep_comment char(60)
);

create table emp(
	id int primary key auto_increment,
	name char(16),
	gender enum('male','female','other') not null default 'male',
	dep_id int,
	foreign key(dep_id) references dep(id)
);

# 2.再插入记录时，必须先插被关联的表dep，才能插关联表emp
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);

# 如果想修改emp中的dep_id或dep里的id时都无法成功
# 如果想删除dep表的教学部时，也无法删除
# 方式1:先删除教学部对应的所有的员工，再删除教学部
# 方式2:受限于外键约束，导致操作数据变得非常复杂，能否有一张简单的方式，让我不需要考虑在操作目标表的时候还需要考虑关联表的情况，比如我删除部门，那么这个部门对应的员工就应该跟着立即清空
# 先把之前创建的表删除，先删员工表，再删不么表
# 3.更新与删除都需要考虑到关联与被关联的关系>>>同步更新与同步删除
create table dep(
	id int primary key auto_increment,
	dep_name char(10),
	def_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 int 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.站在作者表：一个作者可不可以写多本书，可以，那就是作者多对一书
双方都能一条数据对应对方多条记录，这种关系就是多对多
"""

# 先考虑如何创建表？图书表需要有一个外键关联作者，作者表也需要一个外键关联图书，那么先创建谁都不合适，如何解决？
# 解决方法:建立第三张表，该表中有一个字段fk作者的id，还有一个字段fk书的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(name,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,
	custome_id int unique, # 该字段一定要是唯一的
    foreign key(customer_id) references customer(id) # 外键的字段一定要保证唯一
    on update cascade
    on delete 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;