MySQL alter命令修改表详解

淩亂°似流年 2024-03-23 19:22 95阅读 0赞

**目录**

ALTER TABLE 语法

ALTER TABLE 实例

添加一列

添加多列

重命名列

修改列定义

修改列名和定义

添加主键

删除列

重命名表

修改表的存储引擎

结论

--------------------

在使用表的过程中，如果您需要对表进行修改，您可以使用 `ALTER TABLE` 语句。通过 `ALTER TABLE` 语句，您可以重命名表、重命名列、添加列、删除列、修改列的属性等。

### ALTER TABLE 语法 ###

ALTER TABLE table_name
      [alter_action options], ...

其中`alter_action`是一个修改动作，包括

`ADD`关键字可用来添加列、索引、约束等，包括：

*  `ADD [COLUMN]`: 添加列
 *  `ADD INDEX`: 添加索引
 *  `ADD PRIMARY KEY`: 添加主键
 *  `ADD FOREIGN KEY`: 添加外键
 *  `ADD UNIQUE INDEX`: 添加唯一索引
 *  `ADD CHECK`: 添加检查约束

`DROP` 关键字可用来删除列、索引、约束等，包括：

*  `DROP [COLUMN] col_name`: 删除列
 *  `ADD INDEX index_name`: 删除索引
 *  `DROP PRIMARY KEY`: 删除主键
 *  `DROP FOREIGN KEY fk_symbol`: 删除外键
 *  `DROP CHECK symbol`: 删除检查约束

> `MODIFY`关键字用来修改列的定义。与`CHANGE`关键字不同，它不能重命名列。例如: `MODIFY [COLUMN] col_name column_definition`。
> 
> `CHANGE`关键字用来修改列的定义。与`MODIFY`关键字不同，它可以重命名列。例如: `CHANGE [COLUMN] old_col_name new_col_name column_definition`。

`RENAME`关键字可以重命名列、索引和表。包括：

*  `RENAME COLUMN old_col_name TO new_col_name`: 重命名列。
 *  `RENAME INDEX old_index_name TO new_index_name`: 重命名索引。
 *  `RENAME new_tbl_name`: 重命名表。

### ALTER TABLE 实例 ###

从 `ALTER TABLE` 语法看出，`ALTER TABLE` 用法很多。为了演示`ALTER TABLE`用法，我们在`testdb` 数据库中创建一个表`user`。

请执行以下语句：

CREATE TABLE user (id INT);

通过以下语句查看 `user` 表的定义。

DESC user;

+-------+------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+------+------+-----+---------+-------+
    | id    | int  | YES  |     | NULL    |       |
    +-------+------+------+-----+---------+-------+
    1 row in set (0.00 sec)

下面让我们看一些实际的例子。

#### 添加一列 ####

以下语句使用`ADD`关键字在`user` 表中添加了 `name` 列。

ALTER TABLE user
    ADD name VARCHAR(20);

Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0

注意，当向包含数据的表中添加列时，新添加的列不满足表的约束定义，则会返回错误。

#### 添加多列 ####

以下语句使用 `ADD`关键字在 `user`表中添加了`age` 和`email` 列。

ALTER TABLE user
    ADD age INT,
    ADD email VARCHAR(50);

Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0

#### 重命名列 ####

以下语句使用 `RENAME COLUMN` 关键字，将 `user` 表的 `name` 列重命名为 `username`。

ALTER TABLE user
    RENAME COLUMN name TO username;

Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0

以下是修改后的表定义：

+----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id       | int         | YES  |     | NULL    |       |
    | username | varchar(20) | YES  |     | NULL    |       |
    | age      | int         | YES  |     | NULL    |       |
    | email    | varchar(50) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

#### 修改列定义 ####

以下语句使用 `MODIFY` 关键字，将 `user` 表的 `username` 列由 `varchar(20)` 修改为 `VARCHAR(45)`。

ALTER TABLE user
    MODIFY username VARCHAR(45);

Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0

以下是修改后的表定义：

+----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id       | int         | YES  |     | NULL    |       |
    | username | varchar(45) | YES  |     | NULL    |       |
    | age      | int         | YES  |     | NULL    |       |
    | email    | varchar(50) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

#### 修改列名和定义 ####

以下语句使用 `CHANGE`关键字，将`user` 表的 `username` 列修改为`name VARCHAR(30)`。

ALTER TABLE user
    CHANGE username name VARCHAR(30);

Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0

以下是修改后的表定义：

+-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int         | YES  |     | NULL    |       |
    | name  | varchar(30) | YES  |     | NULL    |       |
    | age   | int         | YES  |     | NULL    |       |
    | email | varchar(50) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

#### 添加主键 ####

以下语句使用 `ADD`关键字，将 `user`表的`id` 列设置为主键。

ALTER TABLE user
    ADD PRIMARY KEY(id);

Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0

注意，由于主键要求列的值是唯一的并且不能为`NULL`，如果该列中有重复的值或者 `NULL`值，则会返回错误。同样，如果你添加唯一索引，也可能会出现相同的错误。

以下是修改后的表定义：

+-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int         | NO   | PRI | NULL    |       |
    | name  | varchar(30) | YES  |     | NULL    |       |
    | age   | int         | YES  |     | NULL    |       |
    | email | varchar(50) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

#### 删除列 ####

下面语句使用 `DROP`关键字删除了 `email` 列。

ALTER TABLE user
    DROP COLUMN email;

Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0

注意：删除列是很危险的操作，因为它会永久删除列中的数据。这是不可撤销的操作，请谨慎。

以下是删除后的表定义：

+-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int         | NO   | PRI | NULL    |       |
    | name  | varchar(30) | YES  |     | NULL    |       |
    | age   | int         | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

#### 重命名表 ####

下面语句使用 `RENAME`关键字将`user` 表重命名为`users`。

ALTER TABLE user
    RENAME users;

我们还可以是用`RENAME TABLE` 语句重命名表，它的用法如下：

RENAME TABLE table_name TO new_table_name;

下面的语句同样实现将`user` 表重命名为`users`：

RENAME TABLE user TO users;

#### 修改表的存储引擎 ####

ALTER TABLE 命令也可以用于修改一张表的存储引擎。可以使用以下语法：

ALTER TABLE table_name ENGINE=engine_name;

其中，table\_name是要修改的表的名称，engine\_name是新的存储引擎。

示例：

-- 将customer表的存储引擎改为innodb
    ALTER TABLE customer ENGINE=InnoDB;

### 结论 ###

在本文中，我们介绍了在 MySQL 服务器上如何使用`ALTER TABLE`语句修改表，包括：添加列、删除列、修改列、重命名列、重命名表和设置主键等。本文要点包括：

*  `ALTER TABLE`关键字后面跟要修改的表名。
 *  `ADD` 关键字可用来添加列、索引、约束等。
 *  `DROP` 关键字可用来删除列、索引、约束等。
 *  `RENAME` 关键字可以重命名列、索引和表。
 *  `MODIFY`关键字用来修改列的定义。
 *  `CHANGE` 关键字用来修改列的定义和列名。
 *  `RENAME TABLE ... TO ...` 用来重命名表。

-- alter table 修改表结构（表名，列名，列的数据类型，相关约束）
    use d3;
    show tables;
    -- 查看表结构
    desc tuser;
    describe tuser;
    show columns from tuser;
    describe table tuser;
    -- 修改表名
    alter table tuser rename userinfo;
    rename table userinfo to tuser;
    -- 移动表表tt2从db2库移动当前数据库为tt3表
    rename table db2.tt2 to tt3;
    /*
    RENAME TABLE
    tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_*/
    drop table if exists
    t,t1,t3,s,stu,user,tuser,tteacher,dept,employees,student;
    create table t1(t int);
    create table t2(t int);
    create table t3(t int);
    -- 修改多个表的名称
    rename table
    t1 to tt1,
    t2 to tt2,
    t3 to tt3;
    -- 查看表结构
    desc tt1;
    -- 查看建立数据库的语句
    show create database mysql;
    -- 查看建立表的语句
    show create table tt1;
    rename table tt1 to student;
    -- 增加字段类型
    alter table student add column id int unsigned auto_increment primary key;
    alter table student add sname varchar(15) not null;
    alter table student add age tinyint unsigned default 18;
    alter table student add gender enum('男','女') default '男' after sname;
    alter table student add address varchar(255) first;
    -- 修改列的类型 及 位置
    alter table student modify address varchar(255) after age;
    -- 修改列名
    alter table student rename column address to saddr;
    -- 修改列名 类型 及 位置
    alter table student change saddr address varchar(100) after age;
    -- 复制表结构或建立一个空表
    create table stu like student;
    -- 根据查询的内容建立一个表，此表没有相关的约束，不推荐使用
    create table t1 as select 20,'jack';
    -- 删除字段
    alter table student drop column t;
    -- 修改表名 student cf_student cf_user cf_admin cf_car
    rename table w_student to cf_student;
    alter table cf_student rename st;
    -- 查看表结构
    describe st;
    desc st;
    -- 查看建立表语句
    show create table st;
    CREATE TABLE `st` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(30) NOT NULL,
    `gender` enum('男','女') DEFAULT NULL,
    `course` set('计算机英语','高数','离散数学','线性代数') DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3
    -- 增加一列（字段）
    -- 删除一列（字段）
    -- 修改列名
    -- 修改列的数据类型 int bigint varchar int varchar(30) varchar(5)
    select * from st;
    -- 增加一列
    alter table st add address varchar(255) not null default '郑州市';
    alter table st add age tinyint unsigned not null default 18 first;
    alter table st add age tinyint unsigned default 0 after name;
    -- 删除一列
    alter table st drop address;
    alter table st drop column age;
    -- 修改列名 name StudentName sname
    alter table st rename column name to sname;
    alter table st change sname name varchar(30) first;
    alter table st change name name varchar(30) after id;
    -- 修改列的数据类型及精度
    alter table st modify name varchar(100) after address;
    alter table st modify name varchar(50) after id;
    -- 复制表的结构
    -- create table t6(t int);
    create table st2 like st;
    show create table st2;
    select * from st2;
    -- 插入数据
    insert into st2 select * from st;
    show tables;
    -- 根据查询的结构，建立一个没有相关约束的表,可以直接插入数据
    create table st3 as select id,name from st;
    create table st4 as select id,name,address from st where name like '李%';
    select * from st3;
    show create table st3;
    drop table st3;
    select * from st4;