文档章节

MySQL外键及修改表和复制表

o
 osc_wws45aot
发布于 2019/08/20 14:57
字数 1967
阅读 13
收藏 0

外键

前戏之一对多关系

# 定义一张部门员工表
id        name        gender        dep_name        dep_desc
1            jason            male            教学部                教书育人
2            egon            male            外交部                漂泊游荡
3            tank            male            教学部                教书育人
4            kevin            male            教学部                教书育人
5            owen            female        技术部                技术能力有限部门
"""
把所有数据都存放于一张表的弊端
1.组织结构不清晰
2.浪费硬盘空间
3.扩展性极差
"""
# 上述的弊端产生原因类似于把代码全部写在一个py文件中,你应该怎么做?>>>解耦合!将上述一张表拆成员工和部门两张表!
# 类似的表关系学生与班级,也是如此,一张学生表和一张班级表

# 分析表数据之间的关系:多个用户对应一个部门,一个部门对应多个用户。禁止一个用户对应多个部门这种情况是另外一张表关系


# 如何查找表与表之间的关系
"""
老师与课程表
1.站在老师表的角度:一名老师能否教授多门课程(限制死,不能,一名老师只能教python,不能同时教python和linux)
2.站在课程表的角度:一门课程能否可以被多个老师教,完全可以!
那就是课程表多对一老师表,如何表示这种关系?在课程表中创建一个字段(tea_id)指向老师表的id字段

学生与班级表
1.站在学生表的角度:???
2.站在班级表的角度:???
那就是学生表多对一班级表,如何表示这种关系?在学生表中创建一个字段(class_id)指向班级表的id字段
"""

# 再回过头来看员工与部门表,我员工表里面的dep_id我可以随意更改,但是应该有一个强制限制,限制dep_id字段必须只是部门表已有的id字段才合理

一对多(Foreign Key)

# foreign key会带来什么样的效果?
# 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_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),
    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.站在作者表:一个作者可不可以写多本书,可以!那就是作者多对一书
双方都能一条数据对应对方多条记录,这种关系就是多对多!
"""
# 先来想如何创建表?图书表需要有一个外键关联作者,作者也需要有一个外键字段关联图书。问题来了,先创建谁都不合适!如何解决?
# 建立第三张表,该表中有一个字段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(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;

 

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。

暂无文章

OSChina 周五乱弹 —— 你大妈还是你大妈

Osc乱弹歌单(2020)请戳(这里) 【今日歌曲】 @watergood:是时候分享一波我的这张纯音乐歌单了,过去的五年多时间里,我陆陆续续地把听到的好听的纯音乐添加了进去,目前一共65首,相信总...

小小编辑
今天
19
0
在Objective-C中生成随机数 - Generating random numbers in Objective-C

问题: I'm a Java head mainly, and I want a way to generate a pseudo-random number between 0 and 74. In Java I would use the method: 我主要是Java头,我想要一种生成0到74之间的伪随......

技术盛宴
今天
13
0
ftp-ftps-sftp的关系

Ftp FTP 是File Transfer Protocol(文件传输协议)的英文简称,而中文简称为“文传协议”。用于Internet上的控制文件的双向传输。同时,它也是一个应用程序(Application)。基于不同的操作...

独钓渔
今天
12
0
使Vim将所有空格显示为字符 - Make Vim show ALL white spaces as a character

问题: I can't find a way to make Vim show all white spaces as a character. 我找不到让Vim将所有空白显示为字符的方法。 All I found was about tabs, trailing spaces etc. 我发现的只......

富含淀粉
今天
23
0
RN 接入高德地图遇到的一些问题

react-native-amap-geolocation、react-native-amap3d 1、iOS Geolocation.getCurrentPosition 获取坐标后,没有返回 address 信息? 逆地理编码 Android 默认返回逆地理编码,而 iOS 需要手...

Jack088
今天
14
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部