MySQL?alter命令修改表語(yǔ)法實(shí)例詳解

更新時(shí)間：2024年01月12日 08:22:11 作者：Bridge?Fish

這篇文章主要給大家介紹了關(guān)于MySQL?alter命令修改表語(yǔ)法實(shí)例詳解的相關(guān)資料,在MySQL中ALTER指令的作用是修改已存在的數(shù)據(jù)庫(kù)表的結(jié)構(gòu),文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下

前言

在使用表的過(guò)程中，如果您需要對(duì)表進(jìn)行修改，您可以使用 ALTER TABLE 語(yǔ)句。通過(guò) ALTER TABLE 語(yǔ)句，您可以重命名表、重命名列、添加列、刪除列、修改列的屬性等。

ALTER TABLE 語(yǔ)法

ALTER TABLE table_name
  [alter_action options], ...

其中 alter_action 是一個(gè)修改動(dòng)作，包括

ADD 關(guān)鍵字可用來(lái)添加列、索引、約束等，包括：

ADD [COLUMN]: 添加列
ADD INDEX: 添加索引
ADD PRIMARY KEY: 添加主鍵
ADD FOREIGN KEY: 添加外鍵
ADD UNIQUE INDEX: 添加唯一索引
ADD CHECK: 添加檢查約束

DROP 關(guān)鍵字可用來(lái)刪除列、索引、約束等，包括：

DROP [COLUMN] col_name: 刪除列
ADD INDEX index_name: 刪除索引
DROP PRIMARY KEY: 刪除主鍵
DROP FOREIGN KEY fk_symbol: 刪除外鍵
DROP CHECK symbol: 刪除檢查約束

MODIFY 關(guān)鍵字用來(lái)修改列的定義。與 CHANGE 關(guān)鍵字不同，它不能重命名列。例如: MODIFY [COLUMN] col_name column_definition。

CHANGE 關(guān)鍵字用來(lái)修改列的定義。與 MODIFY 關(guān)鍵字不同，它可以重命名列。例如: CHANGE [COLUMN] old_col_name new_col_name column_definition。

RENAME 關(guān)鍵字可以重命名列、索引和表。包括：

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

ALTER TABLE 實(shí)例

從 ALTER TABLE 語(yǔ)法看出， ALTER TABLE 用法很多。為了演示 ALTER TABLE 用法，我們?cè)?nbsp;testdb 數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)表 user。

請(qǐng)執(zhí)行以下語(yǔ)句：

CREATE TABLE user (id INT);

通過(guò)以下語(yǔ)句查看 user 表的定義。

DESC user;

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

下面讓我們看一些實(shí)際的例子。

添加一列

以下語(yǔ)句使用 ADD 關(guān)鍵字在 user 表中添加了 name 列。

ALTER TABLE user
ADD name VARCHAR(20);

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

注意，當(dāng)向包含數(shù)據(jù)的表中添加列時(shí)，新添加的列不滿足表的約束定義，則會(huì)返回錯(cuò)誤。

添加多列

以下語(yǔ)句使用 ADD 關(guān)鍵字在 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

重命名列

以下語(yǔ)句使用 RENAME COLUMN 關(guān)鍵字，將 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)

修改列定義

以下語(yǔ)句使用 MODIFY 關(guān)鍵字，將 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)

修改列名和定義

以下語(yǔ)句使用 CHANGE 關(guān)鍵字，將 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)

添加主鍵

以下語(yǔ)句使用 ADD 關(guān)鍵字，將 user 表的 id 列設(shè)置為主鍵。

ALTER TABLE user
ADD PRIMARY KEY(id);

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

注意，由于主鍵要求列的值是唯一的并且不能為 NULL，如果該列中有重復(fù)的值或者 NULL 值，則會(huì)返回錯(cuò)誤。同樣，如果你添加唯一索引，也可能會(huì)出現(xiàn)相同的錯(cuò)誤。

以下是修改后的表定義：

+-------+-------------+------+-----+---------+-------+
| 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)

刪除列

下面語(yǔ)句使用 DROP 關(guān)鍵字刪除了 email 列。

ALTER TABLE user
DROP COLUMN email;

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

注意：刪除列是很危險(xiǎn)的操作，因?yàn)樗鼤?huì)永久刪除列中的數(shù)據(jù)。這是不可撤銷(xiāo)的操作，請(qǐng)謹(jǐn)慎。

以下是刪除后的表定義：

+-------+-------------+------+-----+---------+-------+
| 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)

重命名表

下面語(yǔ)句使用 RENAME 關(guān)鍵字將 user 表重命名為 users。

ALTER TABLE user
RENAME users;

我們還可以是用 RENAME TABLE 語(yǔ)句重命名表，它的用法如下：

RENAME TABLE table_name TO new_table_name;

下面的語(yǔ)句同樣實(shí)現(xiàn)將 user 表重命名為 users：

RENAME TABLE user TO users;

修改表的存儲(chǔ)引擎

ALTER TABLE 命令也可以用于修改一張表的存儲(chǔ)引擎?？梢允褂靡韵抡Z(yǔ)法：

ALTER TABLE table_name ENGINE=engine_name;

其中，table_name是要修改的表的名稱(chēng)，engine_name是新的存儲(chǔ)引擎。

示例：

-- 將customer表的存儲(chǔ)引擎改為innodb
ALTER TABLE customer ENGINE=InnoDB;

結(jié)論

在本文中，我們介紹了在 MySQL 服務(wù)器上如何使用 ALTER TABLE 語(yǔ)句修改表，包括：添加列、刪除列、修改列、重命名列、重命名表和設(shè)置主鍵等。本文要點(diǎn)包括：

ALTER TABLE 關(guān)鍵字后面跟要修改的表名。
ADD 關(guān)鍵字可用來(lái)添加列、索引、約束等。
DROP 關(guān)鍵字可用來(lái)刪除列、索引、約束等。
RENAME 關(guān)鍵字可以重命名列、索引和表。
MODIFY 關(guān)鍵字用來(lái)修改列的定義。
CHANGE 關(guān)鍵字用來(lái)修改列的定義和列名。
RENAME TABLE ... TO ... 用來(lái)重命名表。

-- alter table 修改表結(jié)構(gòu)（表名，列名，列的數(shù)據(jù)類(lèi)型，相關(guān)約束）
use d3;
show tables;
-- 查看表結(jié)構(gòu)
desc tuser;
describe tuser;
show columns from tuser;
describe table tuser;
-- 修改表名
alter table tuser rename userinfo;
rename table userinfo to tuser;
-- 移動(dòng)表表tt2從db2庫(kù)移動(dòng)當(dāng)前數(shù)據(jù)庫(kù)為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);
-- 修改多個(gè)表的名稱(chēng)
rename table
t1 to tt1,
t2 to tt2,
t3 to tt3;
-- 查看表結(jié)構(gòu)
desc tt1;
-- 查看建立數(shù)據(jù)庫(kù)的語(yǔ)句
show create database mysql;
-- 查看建立表的語(yǔ)句
show create table tt1;
rename table tt1 to student;
-- 增加字段類(lèi)型
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;
-- 修改列的類(lèi)型 及 位置
alter table student modify address varchar(255) after age;
-- 修改列名
alter table student rename column address to saddr;
-- 修改列名 類(lèi)型 及 位置
alter table student change saddr address varchar(100) after age;
-- 復(fù)制表結(jié)構(gòu)或建立一個(gè)空表
create table stu like student;
-- 根據(jù)查詢的內(nèi)容建立一個(gè)表，此表沒(méi)有相關(guān)的約束，不推薦使用
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;
-- 查看表結(jié)構(gòu)
describe st;
desc st;
-- 查看建立表語(yǔ)句
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('計(jì)算機(jī)英語(yǔ)','高數(shù)','離散數(shù)學(xué)','線性代數(shù)') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3
-- 增加一列（字段）
-- 刪除一列（字段）
-- 修改列名
-- 修改列的數(shù)據(jù)類(lèi)型 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;
-- 修改列的數(shù)據(jù)類(lèi)型及精度
alter table st modify name varchar(100) after address;
alter table st modify name varchar(50) after id;
-- 復(fù)制表的結(jié)構(gòu)
-- create table t6(t int);
create table st2 like st;
show create table st2;
select * from st2;
-- 插入數(shù)據(jù)
insert into st2 select * from st;
show tables;
-- 根據(jù)查詢的結(jié)構(gòu)，建立一個(gè)沒(méi)有相關(guān)約束的表,可以直接插入數(shù)據(jù)
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;