MySQL表的操作

目录

前言

创建表

创建表时指定不同的存储引擎会对表造成的影响

查看表的结构

显示创建语句

修改表

删除表 

---

前言

表操作至少会涉及如下两类SQL语句：

• DDL（Data Definition Language）数据定义语言：比如建表、删表、该表、新增列、删除列等。
• DML（Data Manipulation Language）数据操作语言：比如插入记录、删除记录、修改记录等。

注意：本篇文章讲的是表的DDL操作，也就是操作表结构的SQL语句。

创建表

创建表的SQL如下：

create [temporary] table [if not exists] 表名称(
	属性名称1 属性名称1的类型 [comment '注释信息'],
	属性名称2 属性名称2的类型 [comment '注释信息'],
	.....
)[charset=字符集名称] [collate=校验规则名称] [engine=存储引擎名称];

说明一下：

• [ ]中的内容代表的是可选项，即可以省略不写。
• 在创建表的SQL语句中加上temporary关键字时，MySQL服务端进程mysqld将创建出一个临时表，该表会在你与服务器的会话终止时自动消失。temporary表的名字可以与某个已有的永久表相同，当有temporary表存在时，对应的永久表会隐藏起来（即无法访问）。
• SQL中大写的表示关键字，[ ]中代表的是可选项。

• charset用于指定表所采用的字符集，如果没有指定则以表所在数据库DB的默认字符集为准（即以表所在的数据库DB中的配置文件db.opt中设置的字符集为准）。
• collate用于指定表所采用的校验规则，如果没有指定则以表所在的数据库DB的默认校验规则为准（即以表所在的数据库DB中的配置文件db.opt中设置的校验规则为准）。
• comment用于对指定列添加注释信息，在建表时增设的每个属性列肯定都有作用，比如增加属性列age int时，可以在其后面加上comment '该属性列表示年龄'，这样一来，往后用户忘了一个表中的某个属性列是用来干嘛的时候，就可以通过SQL语句【 show create table 表名称; 】来查看曾经创建该表时是如何编写的SQL语句，以及查看comment信息。
• engine用于指定表所采用的存储引擎。

如下图1所示，使用SQL语句show engines可以查看MySQL支持的所有存储引擎，然后如下图1的红框处所示，可以看到笔者当前的MySQL默认的存储引擎就是InnoDB，所以如果建表时在建表的SQL语句中没有指定使用哪种存储引擎，那么就会使用MySQL默认的存储引擎InnoDB。​​

说一下，为什么MySQL默认的存储引擎是InnoDB呢？如下图2的红框处所示，默认的存储引擎就是MySQL配置文件（即MySQL客户端进程mysql和MySQL服务端进程mysqld共同的配置文件/etc/my.cnf）中提前配置的存储引擎，所以因为提前配置好的存储引擎是InnoDB，所以MySQL默认的存储引擎就是InnoDB了。

• 图1如下。
• 图2如下。

创建表时指定不同的存储引擎会对表造成的影响

首先回顾一下，在<<MySQL数据库的基础概念>>一文中我们说过，本质上MySQL中的一个数据库DB就是Linux系统下/var/lib/mysql路径上的一个目录文件，MySQL中的表就是Linux系统下/var/lib/mysql路径上的一个非目录普通文件。

然后如下图左半部分所示，use进入数据库test1后，在该数据库中创建一个表table1，并在建表时指定采用MyISAM存储引擎，做完这些后，如下图右半部分所示，在MySQL用于存储所有数据的路径/var/lib/mysql上的test1目录（即test1数据库）中，就会增加3个名为table1的文件（说一下，db.opt是最初在创建数据库test1时就会自动生成的用于记录数据库test1的默认字符集和默认校验规则的配置文件）。换言之，在建表时指定采用MyISAM存储引擎时，在MySQL中创建1个表就对应在Linux系统中创建3个普通文件。

然后如下图左半部分所示，use进入数据库test1后，在该数据库中创建一个表table2，并在建表时指定采用InnoDB存储引擎，做完这些后，如下图右半部分所示，在MySQL用于存储所有数据的路径/var/lib/mysql上的test1目录（即test1数据库）中，就会增加2个名为table2的文件（说一下，db.opt是最初在创建数据库test1时就会自动生成的用于记录数据库test1的默认字符集和默认校验规则的配置文件）。换言之，在建表时指定采用InnoDB存储引擎时，在MySQL中创建1个表就对应在Linux系统中创建2个普通文件。

所以综上可以发现：

• 采用不同的存储引擎，创建表时所产生的文件数量以及文件类型不一样。
• 比如采用InnoDB存储引擎建表，会产生对应的xxx.frm（表结构）和xxx.ibd（表数据+表索引）文件。
• 再比如采用MyISAM存储引擎建表，会产生对应的xxx.frm（表结构）、xxx.MYD（表数据）和xxx.MYI（表索引）文件。

查看表的结构

如下图所示，使用SQL语句【desc 表的名称】即可查看表的结构。

说一下： 

• Field表示该属性字段的名字。
• Type表示该属性字段的类型。
• Null表示该属性字段是否允许为空，比如是否允许id属性上的值为NULL（注意，在MySQL中NULL才表示空，如上图的Key一列上是一片空白，这个不表示空，而表示' '）。
• Key在以后讲解索引的文章中再说明。
• Default表示该字段的默认值。
• Extra表示该字段的额外信息说明。

显示创建语句

如下图所示，使用SQL语句【 show create table 表的名称; 】即可查看对应表的创建语句。说一下，该条语句并不是用于创建新的表的，而是用于查看一个已经存在的表的创建语句、用于查看曾经在创建该表时写的SQL语句是怎样的。

可以看到，使用当前这条SQL语句还可以看到曾经在创建一个表时写的comment注释。

然后要知道的是，如下图所示，SQL语句【 show create table 表的名称 \G】也可以起到和上面一样的作用。

问题：为什么在已经有了SQL语句【 show create table 表的名称; 】的情况下，还要有SQL语句【 show create table 表的名称 \G】呢？

答案：因为前者在显示的时候是以一个表格显示的，如果shell界面不够大，则表格就会显示得很奇怪，这时就可以使用后者（注意使用后者时，在\G后面是不用加分号 ; 的），因为后者不会以表格的形式显示，所以即使shell界面不大，也能很完整简洁的显示一个已经存在的表的创建语句。

修改表

alter table 想要修改的表的名称 add 想要新增的属性列的名称 新增属性列的类型;

alter table 想要修改的表的名称 modify 想要修改的属性列的名称 修改后的属性列的类型;

alter table 想要修改的表的名称 drop 想要删除的属性列的名称;

alter table 想要修改的表的名称 change 想要修改的属性列的名称 属性列的新名称 属性列的新类型;

alter table 想要修改的表的名称 rename 表的新名称;

修改表的SQL语句如上所示，说明一下：

• alter table add用于给表中增加一个属性。
• alter table modify用于修改表中的某个属性的属性类型（无法修改属性的属性名）。
• alter table drop用于删除表中的一个属性。
• alter table change用于修改表中的某个属性的属性名和属性类型（因为可以修改属性的属性名，所以本条SQL语句相当于alter table modify的升级版）。注意只能同时修改属性的属性名和属性类型，不能只修改其中的一个。
• alter table rename用于修改表的名称。

alter table add的实操。（用于给表中增加一个属性）

注意修改表可能会影响到表中的原数据，我们在进行alter table add的实操时顺便证明一下这一点。

如何证明这一点呢？为了证明这一点，我们在修改表之前先在table1表中插入两条数据。如下：

如下图所示，然后通过alter table add在table1表中新增一个属性用于保存用户的照片存储路径，完成后，通过desc查看表table1的结构，可以发现的确新增成功了。

注意： SQL语句alter table add中的after表示将该属性列新增到哪一列之后，如果想要将新增的列放到第一列，可以将从after开始的后面的所有SQL语句换成not null first。

如下图所示，这时再插入一条用户记录后，再查看表中信息，可以看到在新增属性photo_path之前就已经插入到表中的两条记录对应的photo_path值为NULL，所以新增属性后可能还需要对原来插入的记录进行修改。这就证明了修改表可能会影响到表中的原数据，所以一个表的结构一但创建出来了，我们最好不要再修改表的结构。

alter table modify的实操。（用于修改表中的某个属性的属性类型）

如下图所示，该SQL语句即可将table1表中的password属性的类型由char(30)改成char(50)。

注意，如果需要在修改属性的属性类型后仍然保留comment字段（即如果想在未来调用show create table `table1`时能看见表table1的创建语句中还携带comment字段），就需要在使用SQL语句alter table modify修改属性的属性类型时如下图红框处所示重新指定comment字段。

alter table drop的实操。（用于删除表中的一个属性）

如下图红框处所示，该SQL语句即可将table1表中的photo_path属性删除。注意删除列后，该photo_path属性列对应的所有数据都没有了。

注意一直删除table1表中的属性时，如果删到只有最后一个属性时，此时是无法再通过alter table drop语句删除最后一个属性的，如果想要把最后一个属性删除，则需要调用删除整个表的drop table语句。

alter table change的实操。（用于修改表中的某个属性的属性名和属性类型。注意只能同时修改属性的属性名和属性类型，不能只修改其中的一个）

如下图所示，该SQL语句即可将table1表中的password属性的属性名从password改成passwd。

注意，如果需要在修改属性的属性名和属性类型后仍然保留comment字段（即如果想在未来调用show create table `table1`时能看见表table1的创建语句中还携带comment字段），就需要在使用SQL语句alter table change时如下图红框处所示重新指定comment字段。

alter table rename的实操。（用于修改表的名称）

如下图所示，该SQL语句即可将table1表的名字从table1改成table2。注意，如下图红框处所示，MySQL中的表的名字被改变后，在Linux系统下该表对应的几个普通文件的名字也会跟着被改变。

删除表 

删除表的SQL如下：

drop [temporary] table [if exists] 表1的名称,表2的名称.....;

说明一下：

• 在创建表的SQL语句中加上temporary关键字时，MySQL服务端进程mysqld将创建出一个临时表，该表会在你与服务器的会话终止时自动消失。temporary表的名字可以与某个已有的永久表相同，当有temporary表存在时，对应的永久表会隐藏起来（即无法访问）。为了避免重新连接后（temporary表已经不存在），在未做检测的情况下调用drop误删了对应的永久表，因此在使用drop删除临时表时需要带上temporary关键字。

将table1表删除。如下：