赞
踩
目录
在使用表的过程中,如果您需要对表进行修改,您可以使用 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
用法,我们在 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;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。