【MySQL】表的操作【有关表结构的操作】【创建、查看、删除、修改表结构】

表的操作

1.创建表

创建表的语句语法：

CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
) charset 字符集 collate 校验规则 engine 存储引擎;

field 表示列名
datatype 表示列的类型
character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准
engine 存储引擎，如果没有指定存储引擎，就会以所在数据库的默认存储引擎为准

实例：

创建两个表，一个user1，一个user2，分别使用不同的存储引擎去创建

create table if not exists user1(
    id int,
    name varchar(20) comment '用户名',
    password char(32) comment '用户的密码',
    birthday date comment '用户的生日'
    )charset=utf8 collate=utf8_general_ci engine=InnoDB;

两个表的区别只有存储引擎不同

create table if not exists user2(
    id int,
    name varchar(20) comment '用户名',
    password char(32) comment '用户的密码',
    birthday date comment '用户的生日'
    )charset=utf8 collate=utf8_general_ci engine=MyIsam;

我们发现，不同的存储引擎，在数据库中创建的文件是不同的

user1 表存储引擎是 InooDB ，在数据目中有两个不同的文件，分别是：

user1.frm：表结构
user1.ibd：表数据和表索引

user2 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：

user2.frm：表结构
user2.MYD：表数据
user2.MYI：表索引

具体有关存储引擎的不同带来的区别，要以后在学。这里知道会创建不同的文件就行。

2.查看表结构

查看表结构可以输入：

desc 表名;

查询上面我们创建的user1和user2两个表

但是这个查看有一些信息我们还不能知道，比如说他的字符集和校验规则。

我们输入：

show create table user1;

或者

show create table user1 \G

\G可以去掉格式化，让显示出来的信息更方便我们去阅读

这里的语句，就是mysqld服务器经过词法语法分析之后的标准语句，和我们写的是有点不一样的，只要能查到就说明mysql已经将我写的指令记录下来了。mysql会将我所有的操作都记录下来。

3修改表

在实际中，经常修改某个表的结构，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引擎等等。我们还有需求，添加字段，删除字段等等。这时我们就需要修改表。

3.1修改表名

alter table 旧表名 rename to 新表名;

这个to也可以省略

alter table 旧表名 rename 新表名

实例：

修改完之后呢，我们插入两个数据。

insert into user values(1, '张三', '123456', '2021-10-1');
insert into user values(2, '李四', '12286', '2024-10-1');

也可以一次性全部插入

insert into users values(1, '张三', '123456', '2021-10-1'),(2, '李四', '12286', '2024-10-1');

之前我们插入数据要输入对应的字段名字：

比如：

insert into user(id, name, password, birthday) values(1, '张三', '123456', '2021-10-1');
insert into user(id, name, password, birthday) values(2, '李四', '12286', '2024-10-1');

如果不输入字段名字，就说明默认要对全部字段进行插入

插入完表格内多了两个数据.

如果这个时候我们有一个需求，要在这个表中多存储一个张三或者李四的照片的存储路径，那就要多一列，也就是在表中多添加一个字段

3.2添加字段/列

在users表添加一个字段，用于保存图片路径

alter table user add image_path varchar(128) comment '用户的头像路径' after birthday;

对这段指令的一些解析如下图所示：

查看表的数据，可以证实我们的字段被添加进去了,并且添加字段对原数据不会发生影响

详细的也可以输入

show create table user;

或着

desc user;

来查看。

3.3修改字段/列

对user的name字段进行长度的扩容

输入下面这个指令将name字段的长度扩容到60

alter table user modify name varchar(60);

也不会对数据造成影响

看着非常完美，实际上，这个name字段的注释已经消失了。因为modify，更像是覆盖，而不是修改，他是创建了一个新的name字段去对原字段进行覆盖

输入

show create table user \G;

查看

因此在对字段进行修改的时候，要加上注释

alter table user modify name varchar(60) comment '用户名';

3.4删除字段/列

删除password列

注意：删除字段一定要小心，删除字段及其对应的列数据都没了

输入指令将password字段给删除掉

alter table user drop password;

3.5对单一字段/列重命名

比如将name列重命名为 xingming

alter table user change 旧字段名name 新字段名xingming varchar(60); --新字段需要完整定义

4.删除表

还是要注意，尽管我们学习了对表的修改和对表的删除，这些都是对表的结构进行修改（属于DDL类的sql语句），但是我们还是尽量不要去对表的结构进行改动，因为数据库属于比较底层的东西，底层改了上层就要跟着动，而且对数据也有丢失的风险。

删除表的操作很简单。

drop table 表名

实例：
删除名为user2的表

如果只想删除数据，保留表结构，就输入：

delete from 表名

类的sql语句），但是我们还是尽量不要去对表的结构进行改动，因为数据库属于比较底层的东西，底层改了上层就要跟着动，而且对数据也有丢失的风险。