MySQL alter命令修改表详解

在使用表的过程中，如果您需要对表进行修改，您可以使用

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;

标签： mysql 数据库

本文转载自: https://blog.csdn.net/qq_57570052/article/details/132147393
版权归原作者 Bridge Fish 所有，如有侵权，请联系我们删除。

MySQL alter命令修改表详解

ALTER TABLE 语法

ALTER TABLE 实例

添加一列

添加多列

重命名列

修改列定义

修改列名和定义

添加主键

删除列

重命名表

修改表的存储引擎

结论

发表评论

“MySQL alter命令修改表详解”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航