MySQL alter命令修改表详解

2024-03-15 18:40:02 分类：后端 热度：344 评论： 0

MySQL alter命令修改表详解

目录

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;

转载请说明出处内容投诉
CSS教程_站长资源网 » MySQL alter命令修改表详解
PPT模板

PPT模板

分享到：

相关推荐

发表评论

欢迎 访客 发表评论

一个令你着迷的主题！

查看演示 官网购买