MySQL 表之间的关系
# 定义一张部门员工表 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 ('斗破苍穹','90'), ('python入门','178'), ('MySQL基础','169'), ('前端速成','99'); 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 ('李雷','123456789','15555555555'), ('韩梅梅','987654321','16666666666'), ('小明','111222333','11111111111'); 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;
