Mysql 简明教程
MySQL - ALTER Command
MySQL ALTER Command
MySQL ALTER 命令用于修改现有表的结构。它允许进行各种更改，例如添加、删除或修改表内的列。
The MySQL ALTER command is used to modify the structure of an existing table. It allows you to make various changes, such as adding, deleting, or modify columns within the table.
此外，ALTER 命令还用于添加和撤销与现有表关联的不同约束。
Additionally, the ALTER command is also used to add and drop different constraints associated with an existing table.
Syntax
以下是 MySQL 中 ALTER 命令的语法 −
Following is the syntax of ALTER command in MySQL −
ALTER TABLE table_name [alter_option ...];Example
让我们从创建一个名为 CUSTOMERS 的表开始。
Let us begin with the creation of a table named CUSTOMERS.
CREATE TABLE CUSTOMERS (
ID INT,
NAME VARCHAR(20)
);现在，执行以下查询以显示 CUSTOMERS 表中列的信息。
Now, execute the following query to display information about the columns in CUSTOMERS table.
SHOW COLUMNS FROM CUSTOMERS;Dropping a Column
要删除现有表中的列，我们使用带 DROP 子句的 ALTER TABLE 命令。
To drop a column in an existing table, we use the ALTER TABLE command with DROP clause.
Example
在以下示例中，我们删除了上文中创建的 CUSTOMERS 表中的一个已有列 ID −
In the following example, we are dropping an existing column named ID from the above-created CUSTOMERS table −
ALTER TABLE CUSTOMERS DROP ID;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0Verification
要确认 ID 列是否已从 CUSTOMERS 表中删除，执行以下查询 −
To verify whether the ID column has been dropped from the CUSTOMERS table, execute the following query −
SHOW COLUMNS FROM CUSTOMERS;如我们在下面的输出中看到的，不存在 ID 列。所以它被删除了。
As we can see in the output below, there is no ID column present. Hence it is dropped.
Note: A DROP 子句在列是表中仅剩下的列时不起作用。
Note: A DROP clause will not work if the column is the only one left in the table.
Adding a Column
要在一个现有表中添加一个新列，我们在 ALTER TABLE 命令中使用 ADD 关键字。
To add a new column into an existing table, we use ADD keyword with the ALTER TABLE command.
Example
在以下查询中，我们在一个已有表 CUSTOMERS 中添加了一个名为 ID 的列。
In the following query, we are adding a column named ID into an existing table CUSTOMERS.
ALTER TABLE CUSTOMERS ADD ID INT;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0现在， CUSTOMERS 表将包含与你最初创建该表时相同的两列。但新添加的 ID 列将默认添加到表的末尾。在此示例中，它将添加到 NAME 列之后。
Now, the CUSTOMERS table will contain the same two columns that it had when you first created the table. But the newly added ID column will be added at the end of the table by default. In this case, it will add after the NAME column.
Repositioning a Column
如果我们希望列置于表中的特定位置，可以使用 FIRST 将其置为第一列，或使用 AFTER col_name 指示新列应置于 col_name 之后。
If we want a column to be placed at a specific position within the table, we can use FIRST to make it the first column or AFTER col_name to indicate that the new column should be positioned after the col_name.
Example
考虑之前修改的 CUSTOMERS 表，其中 NAME 为第一列，ID 为最后一列。
Consider the previously modified CUSTOMERS table, where the NAME is the first column and ID is the last column.
在以下查询中，我们从表中移除 ID 列，然后将其添加回来，使用 FIRST 关键字将其置于表中的第一列 −
In the following query, we are removing the ID column from the table and then adding it back, positioning it as the first column in the table using FIRST keyword −
ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT FIRST;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0Verification
现在，让我们验证 CUSTOMERS 表中列的位置 −
Now, let us verify the positions of the column in the CUSTOMERS table −
SHOW COLUMNS FROM CUSTOMERS;正如我们在下面的输出中看到的那样，ID 列被置于第一位。
As we can see in the output below, the ID column is positioned first.
Example
在此，我们从表中移除 ID 列，然后将其添加回来，使用 AFTER col_name 关键字将其置于 NAME 列之后。
Here, we are removing the ID column from the table and then adding it back, positioning it after the NAME column using the AFTER col_name keyword.
ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT AFTER NAME;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0Verification
现在，让我们验证 CUSTOMERS 表中列的位置 −
Now, let us verify the positions of the column in the CUSTOMERS table −
SHOW COLUMNS FROM CUSTOMERS;正如我们在下面的输出中看到的那样，ID 列被置于第一位。
As we can see in the output below, the ID column is positioned first.
Note: FIRST 和 AFTER 规范符仅适用于 ADD 子句。这意味着，如果您想重新定位表中的现有列，您首先必须 DROP 它，然后 ADD 它到新位置。
Note: The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.
Altering a Column Definition or a Name
在 MySQL 中，要更改列的定义，我们需要在 ALTER 命令中使用 MODIFY 或 CHANGE 子句。
In MySQL, to change a column’s definition, we use MODIFY or CHANGE clause in conjunction with the ALTER command.
Example
在下面的查询中，我们使用 MODIFY 子句将列 NAME 的定义从 varchar(20) 更改为 INT −
In the query below, we are changing the definition of column NAME from varchar(20) to INT using the MODIFY clause −
ALTER TABLE CUSTOMERS MODIFY NAME INT;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0Verification
现在，让我们验证 CUSTOMERS 表中 NAME 列的定义 −
Now, let us verify the definition of the NAME column in the CUSTOMERS table −
SHOW COLUMNS FROM CUSTOMERS;正如我们观察到的，NAME 列的定义已更改为 INT。
As we can observe, the definition for NAME column has been changed to INT.
Example
我们还可以使用 CHANGE 更改列定义，但语法与 MODIFY 略有不同。在 CHANGE 关键字之后，我们指定要更改的列的名称（两次），然后指定新定义。
We can also change the column definition using CHANGE, but the syntax is a bit different from MODIFY. After the CHANGE keyword, we specify the name of the column (twice) that we want to change, then specify the new definition.
在此，我们使用 CHANGE 子句将列 ID 的定义从 INT 更改为 varchar(20): −
Here, we are changing the definition of column ID from INT to varchar(20) using the CHANGE clause −
ALTER TABLE CUSTOMERS MODIFY ID VARCHAR(20);Altering a Column’s Default Value
在 MySQL 中，我们可以使用 DEFAULT 约束与 ALTER 命令一起更改任何列的默认值。
In MySQL, we can change a default value for any column by using the DEFAULT constraint with ALTER command.
Example
在以下示例中，我们将 changing 列 NAME 的默认值。
In the following example, we are changing the default value of NAME column.
ALTER TABLE CUSTOMERS ALTER NAME SET DEFAULT 1000;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0Verification
现在，让我们验证 CUSTOMERS 表中 NAME 列的默认值 −
Now, let us verify the default value of the NAME column in the CUSTOMERS table −
SHOW COLUMNS FROM CUSTOMERS;正如我们观察到的，NAME 列的默认值已更改为 1000。
As we can observe, the default value for NAME column has been changed to 1000.
Example
我们可以用 DROP 从任何列中删除默认约束和 ALTER 命令一起使用。
We can remove the default constraint from any column by using DROP clause along with the ALTER command.
在此，我们删除了 NAME 列的默认约束。
Here, we are removing the default constraint of NAME column.
ALTER TABLE CUSTOMERS ALTER NAME DROP DEFAULT;Altering (Renaming) a Table
要重命名表，请使用 RENAME 的 ALTER TABLE 语句选项。
To rename a table, use the RENAME option of the ALTER TABLE statement.
Example
以下查询将 CUSTOMERS 表重命名为 BUYERS。
The following query renames the table named CUSTOMERS to BUYERS.
ALTER TABLE CUSTOMERS RENAME TO BUYERS;Altering Table Using a Client Program
除了使用 MySQL 查询更改 MySQL 数据库中的现有表外，还可以使用客户端程序执行 ALTER TABLE 操作。
Besides altering an existing table in a MySQL database with a MySQL query, we can also use a client program to perform the ALTER TABLE operation.