MySQL alter命令修改表详解

目次
ALTER TABLE 语法
ALTER TABLE 实例
添加一列
添加多列
重命名列
修改列定义
修改列名和定义
添加主键
删除列
重命名表
修改表的存储引擎
结论

---

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

1. ALTER TABLE table_name
2.   [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。
请实行以下语句：

1. CREATE TABLE user (id INT);

复制代码

通过以下语句检察 user 表的定义。

1. DESC user;

复制代码

1. +-------+------+------+-----+---------+-------+
2. | Field | Type | Null | Key | Default | Extra |
3. +-------+------+------+-----+---------+-------+
4. | id    | int  | YES  |     | NULL    |       |
5. +-------+------+------+-----+---------+-------+
6. 1 row in set (0.00 sec)

复制代码

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

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

1. ALTER TABLE user
2. ADD name VARCHAR(20);

复制代码

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

复制代码

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

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

1. ALTER TABLE user
2. ADD age INT,
3. ADD email VARCHAR(50);

复制代码

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

复制代码

重命名列

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

1. ALTER TABLE user
2. RENAME COLUMN name TO username;

复制代码

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

复制代码

以下是修改后的表定义：

1. +----------+-------------+------+-----+---------+-------+
2. | Field    | Type        | Null | Key | Default | Extra |
3. +----------+-------------+------+-----+---------+-------+
4. | id       | int         | YES  |     | NULL    |       |
5. | username | varchar(20) | YES  |     | NULL    |       |
6. | age      | int         | YES  |     | NULL    |       |
7. | email    | varchar(50) | YES  |     | NULL    |       |
8. +----------+-------------+------+-----+---------+-------+
9. 4 rows in set (0.00 sec)

复制代码

修改列定义

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

1. ALTER TABLE user
2. MODIFY username VARCHAR(45);

复制代码

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

复制代码

以下是修改后的表定义：

1. +----------+-------------+------+-----+---------+-------+
2. | Field    | Type        | Null | Key | Default | Extra |
3. +----------+-------------+------+-----+---------+-------+
4. | id       | int         | YES  |     | NULL    |       |
5. | username | varchar(45) | YES  |     | NULL    |       |
6. | age      | int         | YES  |     | NULL    |       |
7. | email    | varchar(50) | YES  |     | NULL    |       |
8. +----------+-------------+------+-----+---------+-------+
9. 4 rows in set (0.00 sec)

复制代码

修改列名和定义

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

1. ALTER TABLE user
2. CHANGE username name VARCHAR(30);

复制代码

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

复制代码

以下是修改后的表定义：

1. +-------+-------------+------+-----+---------+-------+
2. | Field | Type        | Null | Key | Default | Extra |
3. +-------+-------------+------+-----+---------+-------+
4. | id    | int         | YES  |     | NULL    |       |
5. | name  | varchar(30) | YES  |     | NULL    |       |
6. | age   | int         | YES  |     | NULL    |       |
7. | email | varchar(50) | YES  |     | NULL    |       |
8. +-------+-------------+------+-----+---------+-------+
9. 4 rows in set (0.00 sec)

复制代码

添加主键

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

1. ALTER TABLE user
2. ADD PRIMARY KEY(id);

复制代码

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

复制代码

留意，由于主键要求列的值是唯一的而且不能为 NULL，如果该列中有重复的值或者 NULL 值，则会返回错误。同样，如果你添加唯一索引，也可能会出现类似的错误。
以下是修改后的表定义：

1. +-------+-------------+------+-----+---------+-------+
2. | Field | Type        | Null | Key | Default | Extra |
3. +-------+-------------+------+-----+---------+-------+
4. | id    | int         | NO   | PRI | NULL    |       |
5. | name  | varchar(30) | YES  |     | NULL    |       |
6. | age   | int         | YES  |     | NULL    |       |
7. | email | varchar(50) | YES  |     | NULL    |       |
8. +-------+-------------+------+-----+---------+-------+
9. 4 rows in set (0.00 sec)

复制代码

删除列

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

1. ALTER TABLE user
2. DROP COLUMN email;

复制代码

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

复制代码

留意：删除列是很危险的操作，因为它会永久删除列中的数据。这是不可撤销的操作，请谨慎。
以下是删除后的表定义：

1. +-------+-------------+------+-----+---------+-------+
2. | Field | Type        | Null | Key | Default | Extra |
3. +-------+-------------+------+-----+---------+-------+
4. | id    | int         | NO   | PRI | NULL    |       |
5. | name  | varchar(30) | YES  |     | NULL    |       |
6. | age   | int         | YES  |     | NULL    |       |
7. +-------+-------------+------+-----+---------+-------+
8. 3 rows in set (0.00 sec)

复制代码

重命名表

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

1. ALTER TABLE user
2. RENAME users;

复制代码

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

1. RENAME TABLE table_name TO new_table_name;

复制代码

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

1. RENAME TABLE user TO users;

复制代码

修改表的存储引擎

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

1. ALTER TABLE table_name ENGINE=engine_name;

复制代码

此中，table_name是要修改的表的名称，engine_name是新的存储引擎。
示例：

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

复制代码

结论

在本文中，我们先容了在 MySQL 服务器上怎样利用 ALTER TABLE 语句修改表，包括：添加列、删除列、修改列、重命名列、重命名表和设置主键等。本文要点包括：

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

1. -- alter table 修改表结构（表名，列名，列的数据类型，相关约束）
2. use d3;
3. show tables;
4. -- 查看表结构
5. desc tuser;
6. describe tuser;
7. show columns from tuser;
8. describe table tuser;
9. -- 修改表名
10. alter table tuser rename userinfo;
11. rename table userinfo to tuser;
12. -- 移动表表tt2从db2库移动当前数据库为tt3表
13. rename table db2.tt2 to tt3;
14. /*
15. RENAME TABLE
16. tbl_name TO new_tbl_name
17. [, tbl_name2 TO new_tbl_*/
18. drop table if exists
19. t,t1,t3,s,stu,user,tuser,tteacher,dept,employees,student;
20. create table t1(t int);
21. create table t2(t int);
22. create table t3(t int);
23. -- 修改多个表的名称
24. rename table
25. t1 to tt1,
26. t2 to tt2,
27. t3 to tt3;
28. -- 查看表结构
29. desc tt1;
30. -- 查看建立数据库的语句
31. show create database mysql;
32. -- 查看建立表的语句
33. show create table tt1;
34. rename table tt1 to student;
35. -- 增加字段类型
36. alter table student add column id int unsigned auto_increment primary key;
37. alter table student add sname varchar(15) not null;
38. alter table student add age tinyint unsigned default 18;
39. alter table student add gender enum('男','女') default '男' after sname;
40. alter table student add address varchar(255) first;
41. -- 修改列的类型 及 位置
42. alter table student modify address varchar(255) after age;
43. -- 修改列名
44. alter table student rename column address to saddr;
45. -- 修改列名 类型 及 位置
46. alter table student change saddr address varchar(100) after age;
47. -- 复制表结构或建立一个空表
48. create table stu like student;
49. -- 根据查询的内容建立一个表，此表没有相关的约束，不推荐使用
50. create table t1 as select 20,'jack';
51. -- 删除字段
52. alter table student drop column t;
53. -- 修改表名 student cf_student cf_user cf_admin cf_car
54. rename table w_student to cf_student;
55. alter table cf_student rename st;
56. -- 查看表结构
57. describe st;
58. desc st;
59. -- 查看建立表语句
60. show create table st;
61. CREATE TABLE `st` (
62. `id` int unsigned NOT NULL AUTO_INCREMENT,
63. `name` varchar(30) NOT NULL,
64. `gender` enum('男','女') DEFAULT NULL,
65. `course` set('计算机英语','高数','离散数学','线性代数') DEFAULT NULL,
66. PRIMARY KEY (`id`)
67. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3
68. -- 增加一列（字段）
69. -- 删除一列（字段）
70. -- 修改列名
71. -- 修改列的数据类型 int bigint varchar int varchar(30) varchar(5)
72. select * from st;
73. -- 增加一列
74. alter table st add address varchar(255) not null default '郑州市';
75. alter table st add age tinyint unsigned not null default 18 first;
76. alter table st add age tinyint unsigned default 0 after name;
77. -- 删除一列
78. alter table st drop address;
79. alter table st drop column age;
80. -- 修改列名 name StudentName sname
81. alter table st rename column name to sname;
82. alter table st change sname name varchar(30) first;
83. alter table st change name name varchar(30) after id;
84. -- 修改列的数据类型及精度
85. alter table st modify name varchar(100) after address;
86. alter table st modify name varchar(50) after id;
87. -- 复制表的结构
88. -- create table t6(t int);
89. create table st2 like st;
90. show create table st2;
91. select * from st2;
92. -- 插入数据
93. insert into st2 select * from st;
94. show tables;
95. -- 根据查询的结构，建立一个没有相关约束的表,可以直接插入数据
96. create table st3 as select id,name from st;
97. create table st4 as select id,name,address from st where name like '李%';
98. select * from st3;
99. show create table st3;
100. drop table st3;
101. select * from st4;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。