mysql 从一个表insert到另一张表_产品操作MySQL第3篇 – 修改一张表

本资料为产品岗位作为日常工作参考，语言口语化

At 2019/4/22 By David.Yang

修改一张数据表

随着业务发展，会出现数据表无法支撑当前业务数据的情况，

这时候我们需要对数据表进行变动，

数据表发生变动都通过SQL ALTER TABLE 语句来实现。

新增字段

-- 新增字段

-- ALTER TABLE `表名` ADD `列名称` 字段类型(长度) [完整性约束条件] COMMENT '注释说明' AFTER `位置字段`;

-- [完整性约束条件]指前文讲的限定字段的条件，比如：

-- 为空设定：NOT NULL DEFAULT '0'

示例：

先看看test表现在有哪些字段

SQL

mysql> desc test;

补充两个字段

SQL

mysql> ALTER TABLE `test` ADD `name` varchar(20) COMMENT '名称';

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

SQL

mysql> desc test;

看到name字段已经成功添加到test表当中了

我们在添加一个字段，并且指定字段在name字段后面，

而且指定不能为空，默认值为0

SQL

mysql> ALTER TABLE `test` ADD `gender` int(2) NOT NULL DEFAULT 0 COMMENT '性别 0未知 1女 2男' AFTER `name`;

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

看看现在有哪些字段了

SQL

mysql> desc test;

修改字段属性

-- 修改字段属性

-- ALTER TABLE `表名` CHANGE `列名称``新列名称`（不需要重命名就用和原来列名） 字段类型(长度) [完整性约束条件]

-- 注意，修改时如果不带完整性约束条件，原有的约束条件将丢失，如果想保留修改时就得带上完整性约束条件

示例：

我们先添加一个字段，在修改他的属性

SQL

mysql> ALTER TABLE `test` ADD `brith` int(10) COMMENT '生日';

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

看到brith字段已经加进去了

修改字段名称

示例：

-- 修改字段名称

-- ALTER TABLE `表名` CHANGE `列名称` `新列名称`（不需要重命名就用和原来列名） 字段类型(长度) [完整性约束条件]

-- 注意，修改时如果不带完整性约束条件，原有的约束条件将丢失，如果想保留修改时就得带上完整性约束条件

SQL

mysql> ALTER TABLE `test` CHANGE `brith` `brithday` int(10) COMMENT '生日';

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

可以看到brith已经改名为brithday

修改字段属性

-- 修改字段属性

-- ALTER TABLE `表名` MODIFY `列名称` 字段类型(长度) [完整性约束条件]

-- 注意，修改时如果不带完整性约束条件，原有的约束条件将丢失，如果想保留修改时就得带上完整性约束条件

示例：

-- 修改brithday类型由数值改成字符串

SQL

mysql> ALTER TABLE `test` MODIFY `brithday` VARCHAR(20) COMMENT '生日';

Query OK, 1 row affected (0.08 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> desc test;

可以看到brithday已经由数值变成了一种文本格式

示例：

-- 将brithday移到name后面

SQL

mysql> ALTER TABLE `test` MODIFY `brithday` VARCHAR(20) COMMENT '生日' AFTER `name`;

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC test;

可以看到成功讲brithday移动到了name后面

删除一个字段列

删除一个不再需要的字段使用DROP 语句，

在删除数据列之前应该确定业务当中已经不再使用这个字段，

当前使用到的地方都已经做了迁移，

否则…

-- 删除列

-- ALTER TABLE `表名字` DROP `字段名称`

示例：

此处先创建一个字段，

后然再将其删除

SQL

mysql> ALTER TABLE `test` ADD `deleted` varchar(20) COMMENT '删除演示字段';

Query OK, 0 rows affected (0.10 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

将其删除

SQL

mysql> ALTER TABLE `test` DROP `deleted`;

Query OK, 0 rows affected (0.10 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

对字段值管理默认值

添加默认值

• 新建字段时指定默认值

我们可以在一个字段创建时就可以进行默认值赋值

-- 新增字段

-- 前面讲 ALTER TABLE `表名` ADD `字段名` 这个语法时讲到约束条件里面是可以将默认值之类进行规定

演示：

SQL

mysql> ALTER TABLE `test` ADD `age` int(4) DEFAULT 18;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

设定age默认值为18，之后所有数据在不赋予age时数据库默认会填入18

注：

新字段创建后，存量数据也会被赋予默认值

• 修改已存在字段的默认值

-- 已存在字段设置默认值

-- ALTER TABLE `表名` ALTER `字段名` SET DEFAULT '默认值';

演示：

SQL

mysql> ALTER TABLE `test` ALTER `name` SET DEFAULT '匿名用户';

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

注：

针对已存在的字段进行默认值赋予，

存量数据不受影响，不会被赋值，

新数据将会赋予默认值。

删除默认值

使一个字段的默认值失效，直接删除即可。

-- 删除默认值

-- ALTER TABLE `表明` ALTER `字段名` DROP DEFAULT;

演示：

SQL

mysql> ALTER TABLE `test` ALTER `name` DROP DEFAULT;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

我们可以发现name的默认值已经不存在

数据表重命名

我先复制一张表出来，然后再演示删除一张表。

演示：

SQL

mysql> CREATE TABLE `test1` SELECT * FROM `test`;

Query OK, 1 row affected (0.05 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> show tables;

再对表进行重命名

-- ALTER TABLE `表名字` RENAME `表新名字`

演示：

SQL

mysql> ALTER TABLE `test1` RENAME `test2`;

Query OK, 0 rows affected (0.02 sec)

mysql> show tables;

可以发现表已经由test1改名为test2