MySQL表的相关操作

淩亂°似流年 2021-12-04 05:25 323阅读 0赞

> 一 、 为什么要对表进行操作。

一、将所有的数据都放在一张表内产生的弊端;

1. 表的组织结构不清晰

2. 浪费存储空间

3. 可扩展性极差（修改某一个部门的信息的时候）

类似于你把所有的代码都写到一个py文件中，你是怎么优化的？

>>>解耦拆分

如何查找表与表之间的关系

以员工和部门表为例。查找表关系需要做到换位思考（站在两边去找表关系）

先站在员工表：

找员工表的多条数据能否对应部门表的一条数据

翻译：

多个员工能否属于一个部门

可以。之后不能直接下结论，还需要站在部门表的角度再确认关系

再站在部门表：

找部门表的多余数据能否对应员工表的一条数据

翻译：

多个部门能否有同一个员工

不可以

只有站在两边表的角度都分析过了，才能下结论

员工表单向多对一部门表

![1629202-20190514154625741-534260677.png][]

> 二 、 外键（foreign key）

1. 必需要先建被关联表

create table dep(

id int primary key auto\_increment,

dep\_name char(16),

dep\_desc char(64)

);

create table emp(

id int primary key auto\_increment,

name char(16),

gender enum('male','female','others') not null default 'male',    \#default 后面的默认值空格直接书写即可

dep\_id int,

foreign key(dep\_id) referreces dep(id)

);

2. 插入数据

新增数据的时候，要先增被关联表中的数据

insert into dep(dep\_name,dep\_desc) values

(‘外交官’，‘颜值扛把子’），

（‘教学部’，‘教书育人’），

（‘技术部’，‘技术能力有限部门’）；

insert into emp(name,gender,dep\_id) values  
　　('jason','male',1),  
　　('egon','male',2),  
　　('kevin','male',2),  
　　('tank','male',2),  
　　('jerry','female',3);

3. 修改emp表中的dep\_id字段  
update emp set dep\_id=100 where id=1;  
update dep set id=100 where id=1;  
delete from dep where id=2;

delete from emp where id>1 and id<5;  
delete from dep where id=2;

\# 给外键字段新增功能 同步更新同步删除(级联删除级联更新)

create table dep(  
　　id int primary key auto\_increment,  
　　dep\_name char(16),  
　　dep\_desc char(64)  
);

create table emp(  
　　id int primary key auto\_increment,  
　　name char(16),  
　　gender enum('male','female','others') not null default 'male', \# default后面的默认值空格直接书写即可  
　　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(name,gender,dep\_id) values  
　　('jason','male',1),  
　　('egon','male',2),  
　　('kevin','male',2),  
　　('tank','male',2),  
　　('jerry','female',3);

update dep set id=100 where id=2;  
delete from dep where id=100;

多对多

图书与作者

![1629202-20190514160503716-1923268952.png][]

create table book(  
　　id int primary key auto\_increment,  
　　title char(16),  
　　price int  
);  
create table author(  
　　id int primary key auto\_increment,  
　　name char(16),  
　　gender char(16)  
);  
create table book2author(  
　　id int primary key auto\_increment,  
　　book\_id int,  
　　author\_id int,  
　　foreign key(book\_id) references book(id)  
　　on update cascade \# 同步更新  
　　on delete cascade, \# 同步删除  
　　foreign key(author\_id) references author(id)  
　　on update cascade \# 同步更新  
　　on delete cascade \# 同步删除  
);

insert into book(title,price) values  
　　('金 瓶 梅','69.96'),  
　　('围 城','99.99'),  
　　('python全栈开发','21000');

insert into author(name,gender) values  
　　('jason','male'),  
　　('egon','female'),  
　　('kevin','male');

insert into book2author(book\_id,author\_id) values  
(1,1),  
(1,2),  
(1,3),  
(2,1),  
(2,3),  
(3,1),  
(3,2);

一对一  
user  
name password

userdetail  
gender phone addr hobby ...

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. 增加字段  
　　 表名  
　　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 旧字段名 新字段名 新数据类型 \[完整性约束条件…\];

转载于:https://www.cnblogs.com/SlookUp/p/10862835.html

[1629202-20190514154625741-534260677.png]: /images/20211204/447c711e46a54a4da7af2e1304b378d0.png
[1629202-20190514160503716-1923268952.png]: /images/20211204/9c919afe5c7144178c8660a627c33adc.png