MySQL数据表操作

前言：

在创建数据表的时候，有时会发现字段遗漏、字段多余、字段名称或者数据类型、属性不对等种种问题，这时我们迫切需要对数据表进行相应的操作。

添加单列：

语法结构：

ALTER TABLE tbl_name ADD [ COLUMN ] col_name column_definition [ FIRST | AFTER col_name ]

注：在添加单列时，需要对此列定义类型，同时可以在结尾处设置列的位置：第一位或者在指定列的后面，如果省略了指定列的位置，则系统默认添加在所有列的最下面。

案例：

首先来查看表 tb_user1 的结构：

由图可以看出，该表只有 id ，username，pid 这三个字段。

对此表添加字段：

添加一个字段 age 并赋予无符号非空整型且默认值为 10 的属性，在这并没有对该字段指定位置。

重新查看下表 tb_user1 的结构：

由图可以看出，表 tb_user1 新增了 age 字段，而且位于所有字段的底部。

我们再添加一个字段，并对添加的字段设置指定位置：

添加一个 password 字段，并指定位置在字段 username 之后，重新查看表结构：

可以发现，password 字段确实在 username 之后。同理，如果选择 FIRST 则添加的字段位于该数据表的第一位。

添加多列：

语法结构：

ALTER TABLE tbl_name ADD [ COLUMN ] ( col_name col_definition , ...)

注：添加多列与添加单列的区别在于，添加多列不能指定列位置，只能在原来的数据表基础上继续添加。

案例：

由图可以看出，同时添加多列时，位置处于数据表的底部。

删除列：

语法结构：

ALTER TABLE tbl_name DROP [ COLUMN ] col_name

案例：

删除字段 address 并查看该表的结构，可以看出字段 address 已经不存在于该表中。

如果要删除多个字段，可以通过以下操作来删除：

可以看出该表同时删除了 password 和 truename 字段。

添加主键约束：

语法结构：

ALTER TABLE tbl_name ADD [ CONSTRAINT [ symbol ] ] PRIMARY KEY

[ index_type ] ( index_col_name, ...)

案例：

我们先来创建一张表 tb_user2，并查看该表的创建命令：

可以发现，此表并没有任何的主键约束。

现在我们来对此表添加一个字段 id ：

查看该表的结构：

现在给字段 id 设置为主键：

其中，对于 CONSTRAINT 关键字，可以省略不写，如果需要，可以对主键取名称，如图中的PK_user2_id ，而所修饰的主键字段为 id 。查看下表 tb_user2 的结构：

由图可以看出，字段 id 已经是主键。当然除了可以添加主键约束，还可以添加唯一约束。

添加唯一约束：

语法结构：

ALTER TABLE tbl_name ADD [ CONSTRAINT [ symbol ] ] UNIQUE

[ index | key ] [ index_name ] [ index_type ] ( index_col_name, ...)

注：添加唯一约束和提交主键约束的不同在于，唯一约束可以添加多个，而主键约束只能添加一个。

案例：

对表 tb_user2 中的字段 username 添加唯一约束，查看表结构：

由图可以看出，字段 username 已经是唯一约束。

添加外键约束：

语法结构：

ALTER TABLE tbl_name ADD [ CONSTRAINT [ symbol ] ] FOREIGN KEY

[ index_name ] ( index_col_name, ...) reference_definition

案例：

当我们想对表 tb_user2 中的字段 pid 去参照 tb_province 表中的字段 id，此时必须给 pid 字段添加外键约束：

再查看表的创建命令：

可以发现，该表已经存在外键约束。

添加/删除默认约束：

语法结构：

ALTER TABLE tbl_name ALTER [ COLUMN ] col_name

{ SET DEFAULT literal | DROP DEFAULT}

案例：

创建一个字段 age ，并查看表结构：

给字段 age 设置默认值 20，并查看表结构：

删除默认值，并查看表结构：

删除主键约束：

语法结构：

ALTER TABLE tb_user DROP PRIMARY KEY

案例：

首先查看表 tb_user2 的表结构：

此时，字段 id 为主键约束，我们通过以下操作来删除主键约束，并查看表结构：

可以看出，字段 id 已经不再是主键约束。

删除唯一约束：

语法结构：

ALTER TABLE tbl_name DROP { INDEX | KEY } index_name

注：因为一张表可以存在多个唯一约束，所以在删除的时候需要指定字段名。

案例：

先查看表 tb_user2 中约束的名字：

可以发现，在字段 username 上，创建了一个名为 username 的约束，这时，我们通过以下操作来删除 username 的唯一约束，并查看表结构：

再查看下表的约束条件：

可以发现，字段 username 已经不再是唯一约束，只剩下一个约束。

删除外键约束：

语法结构：

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

注：删除外键约束前，需要知道约束的名字

案例：

先查看下外键约束的名称：

可以发现外键约束的名称为系统所生成的 tb_user2_ibfk_1，所以可以通过以下操作来删除外键约束：

再查看下该表的创建命令：

可以发现该表已经不存在外键约束，但是 pid 还是具有索引，如果要删除索引可以通过以下操作来删除：

再次查看表创建命令：

可以发现，字段 pid 已经不再具有索引。

修改列定义：

语法结构：

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition 

[FIRST | AFTER col_name]

案例：

先来查看一下表 tb_user2 的结构：

可以发现，现在字段 id 并没有处在第一位。字段的位置其实并没有任何影响，只不过说习惯将字段 id 放在第一位。

调整一下字段 id 的位置：

重新查看表结构：

字段 id 已经位于第一位。

修改字段 id 的数据类型：

再查看一下表结构：

可以发现，字段 id 的数据类型由原来的 smallint 变成 tinyint 。

注：如果将大类型修改为小类型，会造成数据丢失，慎改。

修改列名称：

语法结构：

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name 

new_col_name column_definition [FIRST | AFTER col_name]

注：CHANGE 关键字不仅可以修改列定义，还需修改列名称。

案例：

查看下表 tb_user2 结构：

现在我们将字段 pid 的名称修改为 p_id ，并修改为 tinyint 数据类型：

再查看数据表结构：

可以看出，原先的字段 pid 变成了 p_id，数据类型也变成了 tinyint 。

删除数据表：

语法结构：

DROP TABLE tbl_name

修改数据表的名称：

语法结构：

⑴ ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name

⑵ RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2] ...

案例：

查看下当前数据库下的所有数据表：

现在我们将表 tb_users 修改为 tb_user3：

再查看下当前数据库的所有表：

可以看出，原先的表 tb_users 已经修改为 tb_user3 。

同样可以使用方法二进行修改，而且可以对多张数据表进行更名，这里就不进行操作了。

注：某些修改的数据列或者数据表曾经被引用的情况下，一旦进行修改，将会导致视图或者存储过程无法正常工作，所以我们尽量不要随意的更改数据列的名字和数据表的名字。

总结：

修改数据表操作可以分为：

⑴ 针对字段的操作：添加或删除字段、修改列定义，修改列名称等

⑵ 针对约束的操作：添加或删除各种约束

⑶ 针对数据表的操作：数据表的更名（两种方式）

以上为本人的一些学习笔记，如有出错欢迎指正，陆续更新！！！