Sql 简明教程

SQL - ALTER TABLE

SQL − ALTER TABLE Statement

SQL ALTER TABLE 命令是数据定义语言 (DDL) 的一部分，它修改表的结构。ALTER TABLE 命令可以添加或删除列、创建或销毁索引、更改现有列的类型，或重命名列或表本身。

The SQL ALTER TABLE command is a part of Data Definition Language (DDL) and modifies the structure of a table. The ALTER TABLE command can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.

ALTER TABLE 命令还可以更改表的特性，例如用于该表的存储引擎。我们将在示例中使用以下表

The ALTER TABLE command can also change characteristics of a table such as the storage engine used for the table. We will make use of the following table in our examples

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

4500.00

7

Muffy

24

Indore

10000.00

Syntax

以下是 ALTER TABLE 命令的基本语法 −

Following is the basic syntax of an ALTER TABLE command −

ALTER TABLE table_name [alter_option ...];

其中 alter_option 取决于要在表上执行的操作类型。本文将逐一讨论这些重要的操作。

Where, the alter_option depends on the type of operation to be performed on a table. This article will discuss such important operations one by one.

ALTER TABLE − ADD Column

如果您需要为一个表增加一列，您应该使用 ADD COLUMN 选项以及如下所示的 ALTER TABLE 语句：

If you need to add a new column to a table, you should use the ADD COLUMN option along with ALTER TABLE statement as shown below −

ALTER TABLE table_name ADD column_name datatype;

Example

以下是为现有表 ADD New Column 的示例：

Following is the example to ADD a New Column to an existing table −

ALTER TABLE CUSTOMERS ADD SEX char(1);

Output

执行上述查询将产生以下输出：

Executing the query above will produce the following output −

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

Verification

为验证 CUSTOMERS 表是否通过添加新列 SEX 进行了更改，使用 SELECT 语句来检索该表的记录：

To verify whether the CUSTOMERS table is altered by adding a new column SEX, use the SELECT statement to retrieve the records of the table −

SELECT * FROM CUSTOMERS;

现在，CUSTOMERS 表将按照如下方式显示：

Now, the CUSTOMERS table will be displayed as follows −

ID

NAME

AGE

ADDRESS

SALARY

SEX

1

Ramesh

32

Ahmedabad

2000.00

NULL

2

Khilan

25

Delhi

1500.00

NULL

3

Kaushik

23

Kota

2000.00

NULL

4

Chaitali

25

Mumbai

6500.00

NULL

5

Hardik

27

Bhopal

8500.00

NULL

6

Komal

22

Hyderabad

4500.00

NULL

7

Muffy

24

Indore

10000.00

NULL

ALTER TABLE − DROP COLUMN

如果您需要从表中删除现有列，您应该使用 DROP COLUMN 选项以及如下所示的 ALTER TABLE 语句：

If you need to drop an existing column from a table, you should use the DROP COLUMN option along with ALTER TABLE statement as shown below.

ALTER TABLE table_name DROP COLUMN column_name;

Example

以下是从现有表中 DROP sex 列的示例：

Following is the example to DROP sex column from the existing table.

ALTER TABLE CUSTOMERS DROP COLUMN SEX;

Output

执行上述查询将产生以下输出：

Executing the query above will produce the following output −

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

Verification

为验证 CUSTOMERS 表是否通过删除现有列 SEX 进行了更改，使用 SELECT 语句来检索该表的记录：

To verify whether the CUSTOMERS table is altered by dropping an existing column SEX, use the SELECT statement to retrieve the records of the table −

SELECT * FROM CUSTOMERS;

现在，CUSTOMERS 表已被更改，以下是 SELECT 语句的输出结果：

Now, the CUSTOMERS table is changed and following would be the output from the SELECT statement.

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

4500.00

7

Muffy

24

Indore

10000.00

ALTER TABLE − ADD INDEX

您可以使用 ADD INDEX 语句以及 ALTER 语句，为表中的现有列添加索引：

You can add index to an existing column of a table using the ADD INDEX statement along with the ALTER statement −

ALTER TABLE table_name
ADD INDEX index_name [index_type]

Example

以下是针对 CUSTOMERS 表的列 NAME 添加索引的查询：

Following query adds an index on the column NAME of CUSTOMERS table −

ALTER TABLE CUSTOMERS ADD INDEX name_index (NAME);

Output

输出将显示为：

The output will be displayed as −

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

ALTER TABLE − DROP INDEX

您可以使用 DROP INDEX 语句以及 ALTER 语句，从表中删除现有索引：

You can drop an existing index from a table using the DROP INDEX statement along with the ALTER statement −

ALTER TABLE table_name DROP INDEX index_name;

Example

以下是针对 CUSTOMERS 表的列 NAME 添加索引的查询：

Following query adds an index on the column NAME of CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP INDEX name_index;

Output

输出将显示为：

The output will be displayed as −

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

ALTER TABLE − ADD PRIMARY KEY

以下是为现有数据库表添加主键的语法：

Following is the syntax to add a primary key in an existing table of a database −

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
PRIMARY KEY (column1, column2...);

Example

在为现有表添加主键之前，首先让我们创建一个名为 EMPLOYEES 的新表，如下所示：

Before we add a primary key to an existing table, first let’s create a new table called EMPLOYEES as follows:

CREATE TABLE EMPLOYEES(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2)
);

以下是针对 EMPLOYEES 表的列 ID 添加主键约束的查询：

Following query adds primary key constraint on the column ID of EMPLOYEES table −

ALTER TABLE EMPLOYEES
ADD CONSTRAINT MyPrimaryKey
PRIMARY KEY(ID);

这将产生以下输出 −

This will produce the following output −

Query OK, 0 rows affected, 1 warning (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 1

Verification

为验证上述查询，如果您使用 DESC EMPLOYEES 命令来描述该表：

To verify the above query if you describe the table using the DESC EMPLOYEES command −

DESC EMPLOYEES;

这将显示创建的表的结构：列名称、它们各自的数据类型、约束（如果有）等等。

This will display the structure of the table created: column names, their respective data types, constraints (if any) etc.

Field

Type

Null

Key

Default

Extra

ID

int(11)

NO

PRI

NULL

NAME

varchar(20)

NO

NULL

AGE

int(11)

NO

NULL

ADDRESS

char(25)

YES

NULL

SALARY

decimal(18,2)

YES

NULL

ALTER TABLE − DROP PRIMARY KEY

以下是为现有数据库表删除主键的语法：

Following is the syntax to delete a primary key from an existing table of a database −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

以下查询从 EMPLOYEES 表的列 ID 中删除主键约束：

Following query deletes primary key constraint from the column ID of EMPLOYEES table −

ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

这将产生以下输出 −

This will produce the following output −

Query OK, 0 rows affected, 1 warning (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 1

ALTER TABLE − ADD CONSTRAINT

以下是在已有表中向列添加唯一约束的语法：

Following is the syntax to add a unique constraint to a column of an existing table −

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE(column1, column2...);

Example

以下查询向 CUSTOMERS 表添加 UNIQUE 约束：

Following query adds UNIQUE constraint to the table CUSTOMERS −

ALTER TABLE EMPLOYEES ADD CONSTRAINT CONST UNIQUE(NAME);

这将产生以下输出 −

This will produce the following output −

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

ALTER TABLE − DROP CONSTRAINT

以下是从已有表中删除唯一约束的语法：

Following is the syntax to drop a unique constraint from an existing table −

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example

以下查询向 CUSTOMERS 表添加 UNIQUE 约束：

Following query adds UNIQUE constraint to the table CUSTOMERS −

ALTER TABLE EMPLOYEES DROP CONSTRAINT CONST;

这将产生以下输出 −

This will produce the following output −

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

ALTER TABLE − RENAME COLUMN

以下是重命名已有表列名的语法：

Following is the syntax to rename a column name of an existing table −

ALTER TABLE table_name
RENAME COLUMN old_column_name to new_column_name;

Example

以下查询重新命名表 CUSTOMERS 中的 NAME 列：

Following query renames NAME column in table CUSTOMERS −

ALTER TABLE CUSTOMERS RENAME COLUMN name to full_name;

这将产生以下输出 −

This will produce the following output −

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

ALTER TABLE − MODIFY DATATYPE

以下是更改 MySQL、MS Server 和 Oracle 中任意列数据类型的语法：

Following is the syntax to change the data type of any column in MySQL, MS Server and Oracle.

SQL Server/MS Access Syntax

ALTER TABLE table_name ALTER COLUMN column_name datatype;

MySQL Syntax

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Oracle Syntax

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Example

以下查询修改 MySQL CUSTOMERS 表中 SALARY 列的数据类型：

Following query modifies datatype of SALARY column in MySQL CUSTOMERS table −

ALTER TABLE CUSTOMERS MODIFY COLUMN ID DECIMAL(18, 4);

这将产生以下输出 −

This will produce the following output −

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