【MySQL基础篇】三、表结构的操作

利刃大大

发布于 2025-05-21 11:31:42

5820

Ⅰ. 创建表

1、语法

create table [if not exists] table_name (
    field1 datatype,
    field2 datatype,
    field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

说明：
- field：表示列名
- datatype：表示列的类型
- character set 字符集：如果没有指定字符集，则以所在数据库的字符集为准
- collate 校验规则：如果没有指定校验规则，则以所在数据库的校验规则为准
- engine 存储引擎：指定该表的存储引擎，不指定的话默认使用 /etc/my.cnf 配置文件中设置的存储引擎。

2、创建表样例

下面我们创建两个不同存储引擎的表，来看看区别：

不同的存储引擎，创建表的文件不一样：
- user1 表存储引擎是 MyISAM ，在数据目录中有三个不同的文件，分别是：
  - users1.frm：表结构
  - users1.MYD：表数据，MY 表示 MyISAM，D 表示 Data
  - users1.MYI：表索引，MY 表示 MyISAM，I 表示 Index
- user2 表存储引擎是 Innodb，在数据目录中有两个不同的文件，分别是：
  - users1.frm：表结构
  - user2.ibd：表的数据和索引

3、创建和其它表一样结构的表

create table 表名1 like 表名2;

就是创建一个表1，它的结构是和表2一样的！（但是是没有数据的，只是表结构一样）

Ⅱ. 查看表结构

1、查看数据库中的表

show tables;

2、查看指定表的属性

desc tb_name;

3、获取表的创建语句

show create table tb_name \G;

一般我们会在最后加一个 \G，让打印出来的格式容易看懂一些！

Ⅲ. 删除表

drop [temporary] table [if exists] tb_name [, tb_name, ...]

切记，谨慎删除！

Ⅳ. 修改表结构

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

注意，这并不是与表内容打交道，而是与表结构打交道！

并且我们在开发的时候，最好是情况是一开始就定好表的结构，不要在后面频繁的去改动表的结构，因为一旦改动，上层的代码中也是需要修改的，这很可能导致 bug 的产生！

1、向表中插入新的字段

alter table tablename add column_name datatype;

在上面的语法中，您需要将 tablename 替换为要插入字段的表的实际名称，column_name 替换为要插入的字段的名称，data_type 替换为字段的数据类型。

举个例子，我们先向 user1 表中插入两条数据：

接着，我们此时要向 user1 表中新增一个字段也就是一个列，叫做 path，用来存放图片的路径（可能是 linux 目录下的某个文件路径，这个不重要），此时我们就要用上面的语句进行操作：

可以看到，我们插入了新字段之后，每行中这个新字段的内容都默认是空的，并且对原来表中的内容是没有影响的！

2、删除表中的字段

alter table tablename drop column_name;

在上面的语句中，将 tablename 替换为要删除字段的表的名称，将 column_name 替换为要删除的字段的名称。

注意，删除字段将永久删除该字段及其相关数据，因此在执行此操作之前，请确保已经备份了相关数据，并且明确了删除字段的后果。

比如我们这里将上面插入的新字段 path 删除掉：

3、修改表名

alter table 旧表名 rename to 新表名;

其中语句中的 to 是可以省略的！

下面我们先来试试看改改字段的名称，将 user1 改成 user，如下所示：

4、修改字段属性

这里说的属性可能是字段名字、字段大小、字段类型等等，但是有一个共同特点，在修改这个字段的某个属性的时候，必须也要重新携带上其它属性的值。比如说我们要修改字段的大小，那么此时如果还设置了注释的话，如果不携带上之前的注释，那么修改完之后只会被覆盖为空！

说的可能比较绕，可以结合下面的例子！

以下是一些常见的字段属性修改操作的示例：

修改字段名称：

alter table 表名 rename column 旧字段名 to 新字段名;

修改字段数据类型：

alter table 表名 modify column 字段名 新数据类型;

修改字段大小：

alter table 表名 modify column 字段名 新数据类型(新大小);

修改和删除字段默认值：

alter table 表名 alter column 字段名 set default 新默认值;

alter table 表名 alter column 字段名 drop default;

添加和删除字段约束：

alter table 表名 add constraint 约束名 字段名 约束条件;

alter table 表名 drop constraint 约束名;

请注意，这些示例中的 “表名” 是要修改的表的名称，“字段名” 是要修改的字段的名称，“新字段名” 是要修改为的新字段名称，“新数据类型” 是要修改为的新数据类型，“新长度” 是要修改为的新字段长度，“新默认值” 是要设置为的新默认值，“约束名” 是要添加或删除的约束的名称，“约束条件” 是要添加的约束条件。

在执行任何修改操作之前，请确保备份了数据，并谨慎操作以避免数据丢失或不一致。

下面举个例子，将 user 表中的 name 字段大小从 20 改为改为 60，看看前后效果：