Mysql 简明教程
MySQL - Add/Delete Columns
表中的一列是一系列垂直单元格，用于存储诸如文本、数字、图像等等的各种类型的数据。每一列都可以包含一个或多个行，其中每行可以存储一个单个值。
A column in a table is a series of vertical cells that are used to store different types of data such as text, numbers, images, etc. Every column can contain one or more rows, where each row can store a single value.
Adding Columns to a MySQL table
在 MySQL 中，可以使用 ALTER TABLE ADD 语句在一个表中添加一个或多个列。当我们需要添加新数据时，向表中添加列可能非常有用。
In MySQL, we can add one or multiple columns in a table using the ALTER TABLE ADD statement. Adding columns to a table can be useful when we need to add new data.
Syntax
以下是 MySQL 表中添加列的语法 −
Following is the syntax to add a column in a MySQL table −
ALTER TABLE table_name
ADD [COLUMN] column_1_definition [FIRST|AFTER existing_column],
ADD [COLUMN] column_2_definition [FIRST|AFTER existing_column],
...;其中，
Where,
-
The FIRST keyword is used to add a specific column at the beginning of the table.
-
The AFTER keyword is used to add a column after a particular existing column in the table.
Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表：
First of all, let us create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL
);执行以下查询以下在前面创建的表中检索列列表 −
Execute the following query to retrieve the columns list in above created table −
DESCRIBE CUSTOMERS;以下是目前 CUSTOMERS 表中存在的列 −
Following are the columns that are present in the CUSTOMERS table at the moment −
现在，我们正在使用以下查询将名为 AGE 的列添加到 CUSTOMERS 表 −
Now, we are adding a column named AGE to the CUSTOMERS table using the below query −
ALTER TABLE CUSTOMERS
ADD COLUMN AGE INT NOT NULL;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0Verification
在将 AGE 列添加到 CUSTOMERS 表之后，我们可以检查以确认 AGE 列是否已添加，使用以下查询 −
After adding the AGE column to the CUSTOMERS table, we can check to confirm if the AGE column has been added or not, using the following query −
DESCRIBE CUSTOMERS;正如我们可以在CUSTOMERS表的列列表中看到，AGE列已成功添加。
As we can see in the colums list of CUSTOMERS table, the column AGE is added successfully.
Example
在以下查询中，我们使用 FIRST 关键词在先前创建的CUSTOMERS表的开头添加S_NO列-
In the following query, we are using the FIRST keyword to add the S_NO column at the beginning of the previosly created CUSTOMERS table −
ALTER TABLE CUSTOMERS
ADD COLUMN S_NO INT NOT NULL FIRST;Output
执行给定的查询后，输出如下：
On executing the given query, the output is displayed as follows −
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0Verification
现在，让我们通过执行以下查询来验证是否首先添加了S_NO列-
Now, let us verify whether the S_NO column is added first or not by executing the below query −
DESCRIBE CUSTOMERS;正如我们在输出表中看到的，S_NO列已成功添加到表的开头。
As we can see in the output table, the S_NO column is added successfully at the beginning of the table.
Example
目前，CUSTOMERS表中有4列。现在，我们使用 AFTER 关键词在名为 ID 的列之后添加一个新列 GENDER -
At the moment, the CUSTOMERS table has 4 columns in it. Now, we are using the AFTER keyword to add a new column GENDER after the column named ID −
ALTER TABLE CUSTOMERS
ADD COLUMN GENDER VARCHAR(10) AFTER ID;Adding Multiple Columns
我们可以使用ALTER TABLE…ADD命令将多列添加到指定表中。要做到这一点，我们只需要指定我们要添加的新列，并用逗号分隔它们。
We can add multiple columns into a specified table using the ALTER TABLE…ADD command. To do this, we just need to specify the new columns that we want to add, separating them with commas.
Example
在下面的查询中，我们使用单个ALTER语句将多列( ADDRESS 和 CONTACT )添加到CUSTOMERS表中-
In the below query, we are adding multiple columns (ADDRESS and CONTACT) to the CUSTOMERS table with a single ALTER statement −
ALTER TABLE CUSTOMERS
ADD COLUMN ADDRESS CHAR (25),
ADD COLUMN CONTACT INT;Output
以上程序的输出如下所示：
The output for the program above is produced as given below −
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0我们可以使用以下查询验证是否添加了MARKS和GRADES列-
We can verify whether the columns MARKS and GRADES are added or not using the following query −
DESCRIBE CUSTOMERS;以下输出显示MARKS和GRADES列已添加到CUSTOMERS表中-
The following output show that the MARKS and GRADES columns are added into CUSTOMERS table −
Deleting Columns from a MySQL table
在MySQL中，我们可以使用 ALTER TABLE DROP COLUMN 语句从表中删除单列或多列。我们通常在不再需要特定数据时删除列。
In MySQL, we can delete single or multiple columns from a table using the ALTER TABLE DROP COLUMN statement. We generally delete the columns when there is specific data that is no longer needed.
Syntax
以下是MySQL中ATLER TABLE DROP COLUMN的语法-
Following is the syntax of ATLER TABLE DROP COLUMN in MySQL −
ALTER TABLE table_name
DROP COLUMN column_name;Example
目前，我们在CUSTOMERS表中有7列。现在，我们正在从CUSTOMERS表中删除现有的S_NO列-
At the moment, we have 7 columns in the CUSTOMERS table. Now, we are deleting the existing column S_NO from the CUSTOMERS table −
ALTER TABLE CUSTOMERS
DROP COLUMN S_NO;Output
执行上面程序时，将获得如下所示的输出：
When we execute the program above, the output is obtained as follows −
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0Verification
我们可以使用以下查询验证名为S_NO的列是否已删除-
We can verify whether the column named S_NO is deleted or not using the following query −
DESCRIBE CUSTOMERS;正如我们看到的，CUSTOMERS表的新更新列列表，S_NO列已被删除。
As we can see the newly updated columns list of CUSTOMERS table, the S_NO column has deleted.
Example
在这里，我们尝试使用单个ALTER语句删除多列( GENDER 、 ADDRESS 和 CONTACT )-
Here, we are trying to delete multiple columns (GENDER, ADDRESS, and CONTACT) using a single ALTER statement −
ALTER TABLE CUSTOMERS
DROP COLUMN AGE,
DROP COLUMN GENDER;Output
执行给定的程序后，输出如下所示：
On executing the given program, the output is displayed as follows −
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0Verification
我们可以使用以下查询验证是否删除了GENDER、ADDRESS和CONTACT列-
Using the following query, we can verify whether the GENDER, ADDRESS and CONTACT columns are deleted or not −
DESCRIBE CUSTOMERS;以下是删除上述列后CUSTOMERS中的列列表-
Following is the list of columns in CUSTOMERS after deleting the above mentioned columns −
Adding/Deleting column in a table Using a Client Program
除了使用 MySQL 查询在 MySQL 数据库中添加/删除表中的列外，还可以使用客户端程序执行 ALTER TABLE ADD/DROP 操作。
Besides adding/deleting a column in a table in MySQL database with a MySQL query, we can also use a client program to perform the ALTER TABLE ADD/DROP operation.