【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 ，在数据目中有两个不同的文件，分别是：

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

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

1. user2.frm：表结构
2. user2.MYD：表数据
3. 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语句），但是我们还是尽量不要去对表的结构进行改动，因为数据库属于比较底层的东西，底层改了上层就要跟着动，而且对数据也有丢失的风险。

删除表的操作很简单。

drop table 表名