修改数据表

土狗不土、

已于 2023-02-21 08:33:15 修改

阅读量1.1k

点赞数 1

文章标签： 1024程序员节

于 2022-10-24 09:30:30 首次发布

本文链接：https://blog.csdn.net/king220022/article/details/127485662

版权

MySQL中支持创建数据表后对数据表的表结构进行修改。本节简单介绍在创建数据表后如何对数据表进行相应的修改。

8.3.1　修改数据表名称

1．语法格式

在MySQL中修改数据表名称，语法格式如下：

ALTER TABLE 原表名 RENAME [TO] 新表名

其中，TO关键字可以省略。

2．简单示例

首先，在MySQL命令行查看goods数据库下存在的所有数据表。

mysql> USE goods;
  Database changed
  mysql> SHOW TABLES;
  +------------------+
  | Tables_in_goods  |
  +------------------+
  | t_goods          |
  | t_goods_backup   |
  | t_goods_category |
  +------------------+
  3 rows in set (0.01 sec)

可以看到，goods数据库下存在3张数据表，分别是商品信息表t_goods、商品信息备份表t_goods_backup和商品类别信息表t_goods_category。

接下来，将商品信息备份表t_goods_backup的名称修改为t_goods_tmp。

mysql> ALTER TABLE t_goods_backup RENAME TO t_goods_tmp;
  Query OK, 0 rows affected (0.12 sec)

SQL语句执行成功，此时再次查看goods数据库中存在的数据表。

mysql> SHOW TABLES;
  +------------------+
  | Tables_in_goods  |
  +------------------+
  | t_goods          |
  | t_goods_category |
  | t_goods_tmp      |
  +------------------+
  3 rows in set (0.00 sec)

可以看到，goods数据库中的t_goods_backup数据表的名称已经被修改为t_goods_tmp，并且数据表结构与t_goods数据表结构相同。

8.3.2　添加字段

1．语法格式

为数据表添加字段的语法格式如下：

ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值]

2．简单示例

为数据表t_goods_tmp添加一个名称为t_create_time的字段，数据类型为DATETIME，默认值为NULL。

mysql> ALTER TABLE t_goods_tmp ADD COLUMN t_create_time DATETIME DEFAULT NULL;
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功，再次查看表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

在数据表t_goods_tmp中多了一个字段t_create_time，说明为数据表t_goods_tmp添加字段成功。

8.3.3　添加字段时指定位置

MySQL中不仅支持为数据表添加字段，而且在添加字段时还能指定当前要添加的字段在数据表中的位置。添加字段时指定要添加字段的位置包括：在表的第一列添加字段和在指定字段的后面添加字段。

1．在表的第一列添加字段

语法格式如下：

ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值] FIRST

其中，FIRST关键字指定当前要添加的字段位于当前表的第一个字段的位置。

简单示例如下：

为数据表t_goods_tmp添加修改时间字段t_update_time，数据类型为DATETIME，默认值为系统当前时间，并将字段t_update_time放在表t_goods_tmp中第一个字段的位置。

mysql> ALTER TABLE t_goods_tmp ADD COLUMN t_update_time DATETIME DEFAULT NOW() FIRST;
  Query OK, 0 rows affected (0.31 sec)
  Records: 0  Duplicates: 0  Warnings: 0

使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `t_update_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

可以看到，在数据表t_goods_tmp中字段t_update_time添加成功，并位于数据表的第一个字段的位置。

2．在指定字段的后面添加字段

语法格式如下：

ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值] AFTER 原有字段名

其中，AFTER关键字指定在数据表的原有字段名之后添加新字段。

简单示例如下：

为数据表t_goods_tmp添加区域字段t_area，数据类型为VARCHAR(100)，默认值为空字符串，并将t_area字段放在库存字段t_stock的后面。

mysql> ALTER TABLE t_goods_tmp ADD COLUMN t_area VARCHAR(100) NOT NULL DEFAULT '' AFTER t_stock;
  Query OK, 0 rows affected (0.03 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功。接下来，使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `t_update_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

在数据表t_goods_tmp中的库存字段t_stock后面多了一个商品区域t_area字段，说明表字段添加成功。

8.3.4　修改字段名称

MySQL支持在创建数据表之后修改字段的名称。这就使开发人员或者数据库维护人员能够根据实际需要将数据库的字段名称修改为更有意义的名称。

1．语法格式

修改数据表字段名称的语法格式如下：

ALTER TABLE 表名 CHANGE 原有字段名 新字段名 新数据类型

在修改数据表字段名称时可以不修改数据类型，此时可以将新字段的数据类型设置成与原有字段的数据类型一样即可，但是新字段名称的数据类型不能为空。

2．简单示例

将数据表t_goods_tmp中的t_update_time字段的名称修改为t_last_modified，数据类型保持不变。

mysql> ALTER TABLE t_goods_tmp CHANGE t_update_time t_last_modified DATETIME;
  Query OK, 0 rows affected (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功。接下来，使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `t_last_modified` datetime DEFAULT NULL,
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

在数据表t_goods_tmp中，t_update_time字段已经被修改为t_last_modified字段。

8.3.5　修改字段的数据类型

MySQL支持将当前字段的数据类型修改成另外一种数据类型，修改数据类型也可以使用ALTER TABLE语句。

1．语法格式

修改数据类型的语法格式如下：

ALTER TABLE 表名 MODIFY 字段名 新数据类型 [DEFAULT 默认值]

使用MODIFY关键字指定字段的新数据类型即可。

2．简单示例

将数据表t_goods_tmp中的商品价格字段t_price的数据类型修改为BIGINT类型，存储价格信息时以分为单位进行存储，默认值为0。

mysql> ALTER TABLE t_goods_tmp MODIFY t_price BIGINT DEFAULT 0;
  Query OK, 0 rows affected (0.03 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功。接下来使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `t_last_modified` datetime DEFAULT NULL,
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

数据表t_goods_tmp中的商品价格字段t_price的数据类型由原来的DECIMAL(10,2)类型变成了BIGINT类型，说明修改字段的数据类型成功。

8.3.6　修改字段的位置

MySQL不仅支持在添加字段时指定要添加的字段在数据表中的位置，还支持修改数据表中已经存在的字段在数据表中的位置，并且MySQL支持将字段的位置修改为数据库的第一个字段，以及将当前字段的位置修改到某个字段的后面。

1．将字段的位置修改为数据库的第一个字段

语法格式如下：

ALTER TABLE 表名 MIDIFY 字段名 数据类型 FIRST

使用FIRST关键字标识将当前字段修改为数据表的第一个字段。

简单示例：

将数据表t_goods_tmp中的id字段的位置修改为表中的第一个字段位置。

mysql> ALTER TABLE t_goods_tmp MODIFY id int(11) NOT NULL FIRST;
  Query OK, 0 rows affected, 1 warning (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 1

SQL语句执行成功，使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_last_modified` datetime DEFAULT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

可以看到，此时id字段被修改为t_goods_tmp表中的第一个字段。

2．将当前字段的位置修改到某个字段的后面

语法格式如下：

ALTER TABLE 表名 MODIFY 字段1名称  字段1的数据类型 AFTER 字段2名称

将字段1移动到字段2的后面。

简单示例：

将数据表t_goods_tmp中的t_last_modified字段修改到t_create_time字段的后面。

mysql> ALTER TABLE t_goods_tmp MODIFY t_last_modified datetime DEFAULT NULL AFTER t_create_time;
  Query OK, 0 rows affected (0.13 sec)
  Records: 0  Duplicates: 0  Warnings: 0

接下来查看表t_goods_tmp的表结构。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    `t_last_modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

此时t_last_modified字段已经被修改到t_create_time字段的后面了。

8.3.7　删除字段

MySQL支持删除数据表中某个字段的操作，删除字段同样使用ALTER TABLE语句。

1．语法格式

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 字段名

2．简单示例

删除数据表t_goods_tmp中的t_area字段。

mysql> ALTER TABLE t_goods_tmp DROP t_area;
  Query OK, 0 rows affected (0.03 sec)
  Records: 0  Duplicates: 0  Warnings: 0

可以看到SQL语句执行成功。接下来查看数据表t_goods_tmp的表结构。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    `t_last_modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

数据表t_goods_tmp中的t_area字段已经被删除。

8.3.8　修改已有表的存储引擎

MySQL不仅支持在创建数据表的时候为数据表指定存储引擎，还支持修改已有表的存储引擎。

1．语法格式

指定数据表的存储引擎，语法如下：

ALTER TABLE 表名 ENGINE=存储引擎名称

“ENGINE”关键字指定数据表的存储引擎。

2．简单示例

由前面的章节可以知道，数据表t_goods_tmp使用的存储引擎为InnoDB，将其修改为MyISAM存储引擎。

mysql> ALTER TABLE t_goods_tmp ENGINE=MyISAM;
  Query OK, 0 rows affected (0.08 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功。接下来查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    `t_last_modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

此时，数据表t_goods_tmp的存储引擎已经被修改为MyISAM了。

最后，将数据表t_goods_tmp的存储引擎再次修改为InnoDB。

mysql> ALTER TABLE t_goods_tmp ENGINE=InnoDB;
  Query OK, 0 rows affected (0.02 sec)
  Records: 0  Duplicates: 0  Warnings: 0

8.3.9　取消数据表的外键约束

1．语法格式

取消数据表的外键约束的语法格式如下：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名

使用DROP FOREIGN KEY关键字删除表中的外键。

2．简单示例

在8.1.3节中我们为商品信息表t_goods指定了一个名称为foreign_category的外键。接下来删除外键约束。

mysql> ALTER TABLE t_goods DROP FOREIGN KEY foreign_category;
  Query OK, 0 rows affected (0.13 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功，再次查看数据表t_goods的表结构信息。

mysql> SHOW CREATE TABLE t_goods \G
  *************************** 1. row ***************************
         Table: t_goods
  Create Table: CREATE TABLE `t_goods` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `foreign_category` (`t_category_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

数据表t_goods的外键约束删除成功。

土狗不土、

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
修改数据表

为数据表t_goods_tmp添加修改时间字段t_update_time，数据类型为DATETIME，默认值为系统当前时间，并将字段t_update_time放在表t_goods_tmp中第一个字段的位置。为数据表t_goods_tmp添加区域字段t_area，数据类型为VARCHAR(100)，默认值为空字符串，并将t_area字段放在库存字段t_stock的后面。在数据表t_goods_tmp中多了一个字段t_create_time，说明为数据表t_goods_tmp添加字段成功。SQL语句执行成功。
复制链接

扫一扫