表之间的关系
# 定义一张部门员工表
emp
id name gander dep_name dep_desc
1 ming male 教学部 教书
2 lilei male 教学部 教书
3 hammm female 销售部 销售工作
4 hong female 技术部 技术能力
'''
把所有数据存放到一张表中的弊端:
1.组织结构不清晰(重点是人还是部门?)
2.浪费银盘空间(重复的内容多)
3.扩展性极差(改动部门名要改动多行数据)
'''
# 上述代码的弊端类似把代码全部写到一个py文件中,所以应该>>>解耦合
# 1.将表中拆成两张表,一张员工表,一张部门表
# 2.分析两张表中的数据之间关系:(一对多,多对多,一对一)
# 1.站在员工表的角度:多个员工属于一个部门
# 2.站在部门表的角度:多个部门不能对应一个员工
# 3.然后将拆分后的两张表建立联系
拆分后的两张表:
emp
id name gander dep_id
1 ming male 1
2 lilei male 1
3 hammm female 2
4 hong female 3
dep
id dep_name dep_desc
1 教学部 教书
2 销售部 销售工作
3 技术部 技术能力
外键 foreign key
# 上述建立关系后的两张表中,员工表中的dep_id我可以随意改动,所以应该有一种限制,限制dep_id字段必须只能是部门表已有的id字段才合理
'''
foreign key
1.外键是绑定两张表关系的关键字,外键要建在主关联表中
2.先创建被关联表,才能创建主关联表
3.在插入记录时,必须先插被关联的记录,才能插主关联表
4.删除记录时,先删除主关联表中的记录,当主关联表中的记录与被关联表中的记录没有联系时,被关联表中的该条记录才能删除
'''
级联更新级联删除 on update cascade ,on delete cascade
# 受限于外键约束,导致操作数据变得非常复杂,能否有一种简单的方式,让我们不需要考虑在操作目标表的时候还需要考虑关联表的情况,比如直接删除被关联表中的部门,对应在主关联表中的该部门的员工都被删除.
'''
级联更新:on update cascade
更新部门后,对应部门里的员工表中的标识部门字段同步更新
级联删除:on delete cascade
删除部门后,对应该部门的员工表记录对应删除
''''
一对多
# 在一对多的表关联中,'多'为主关联表,'一'为被关联表
'''
站在两张表的角度:
1.站在员工表:一名员工是否可以属于多个部门 不可以
2.站在部门表:一个部门是否可以有多名员工 可以
双方只有一方是多对一的关系就是一对多
'''
# 代码实现创建关联表:
create table dep(
id int primary key auto_increment,
dep_name varchar(64),
dep_desc varchar(64)
);
create table emp(
id int primary key auto_increment,
name varchar(64),
gender enum('male','female','other') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
); # 插入记录
insert into dep(dep_name,dep_desc) values
('教学部','教书育人'),
('销售部','招学生'),
('技术部','技术攻关');
insert into emp(name,gender,dep_id) values
('小明','male',1),
('李雷','male',1),
('韩梅梅','female',2),
('小红','female',3);
多对多
# 在多对多关联表中,外键建在第三张表中
# 图书表与作者表之间的关系
'''
站在两张表的角度:
1.站在图书表:一本书是否可以有多个作者 可以
2.站在作者表:一个作者是否可以写多本书 可以
双方都能一条数据对应多条数据买这种关系就是多对多
'''
# 创建多对多关系表的方法
'''
1.设计第三张表,作为主关联表,此表中要有一个字段是图书id的外键,和一个字段是作者id的外键
2.先创建图书和作者这两种被关联表
3.再创建主关联表
'''
# 创建表
create table book(
id int primary key auto_increment,
name varchar(64),
price int
);
create table author(
id int primary key auto_increment,
name varchar(64),
gender enum('male','female','other')
);
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) references author(id)
on update cascade
on delete cascade
);
# 插入数据
insert into book(name,price) values
('斗破苍穹',''),
('python入门',''),
('MySQL基础',''),
('前端速成','');
insert into author(name,gender) values
('土豆','male'),
('韩梅梅','female'),
('李雷','male');
insert into book2author(book_id,author_id) values
(1,1),
(2,2),
(2,3),
(3,3),
(4,2);
一对一
客户表和学生表(报名之前数客户,报名之后是学生)
# 客户表记录唯一对应学生表中的一条记录,反之也一样
'''
站在两张表的角度
1.站在客户表:客户的一条记录对应学生的一条记录
2.站在学生表:学生的一条记录对应客户的一条记录
是一对一关联表
'''
# 创建一对一关联表 外键放在被频繁查询的表中并且外键为单列唯一
create table customer(
id int primary key auto_increment,
name varchar(64) not null,
qq int not null,
phone int not null
);
create into student(
id int primary key auto_increment,
name varchar(64) not null,
customer_id int unique, # 在一对一关联表中 此字段为唯一的
foreign key(customer_id) references customer(id)
on update cascade
on delete cascade
);
# 插入记录
insert into customer(name,qq,phone) values
('李雷','',''),
('韩梅梅','',''),
('小明','','');
insert into student(name,customer_id) values
('李雷',1),
('韩梅梅',2);
修改表
# 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 旧字段名 新字段名 数据类型[约束条件];
复制表
# 查询语句执行结果也是一张表,可以看成虚拟表
# 复制表结构+记录 (主键,外键,索引不会被复制)
create table 新表名 select * from 被复制的表名;
# 只复制结构
select * from 表名 where 1=2;
create table 新表名 select * from 被复制的表名 where 1=2;
create table 表名 like employees;