MySQL - ALTER 命令



MySQL ALTER 命令

MySQL 的ALTER命令用于修改现有表的结构。它允许您进行各种更改,例如添加、删除或修改表中的列。

此外,ALTER 命令还用于添加和删除与现有表关联的不同约束。

由于此命令修改了表的结构,因此它是 SQL 中数据定义语言的一部分。这也是 ALTER 命令与 UPDATE 命令的区别所在;ALTER 与表的结构交互以修改它,而 UPDATE 仅与表中存在的数据交互,而不会干扰其结构。

语法

以下是 MySQL 中 ALTER 命令的语法:

ALTER TABLE table_name [alter_option ...];

示例

让我们从创建一个名为CUSTOMERS的表开始。

CREATE TABLE CUSTOMERS ( ID INT, NAME VARCHAR(20) );

现在,执行以下查询以显示 CUSTOMERS 表中列的信息。

SHOW COLUMNS FROM CUSTOMERS;

输出

以下是 CUSTOMERS 表的列的详细信息:

字段 类型 空值 默认值 额外
ID int YES NULL
NAME varchar(20) YES NULL

删除列

要在现有表中删除列,我们使用带有DROP子句的 ALTER TABLE 命令。

示例

在下面的示例中,我们从上面创建的 CUSTOMERS 表中删除一个名为ID的现有列:

ALTER TABLE CUSTOMERS DROP ID;

输出

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

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

验证

要验证 ID 列是否已从 CUSTOMERS 表中删除,请执行以下查询:

SHOW COLUMNS FROM CUSTOMERS;

从下面的输出中可以看到,没有 ID 列存在。因此它被删除了。

字段 类型 空值 默认值 额外
NAME varchar(20) YES NULL

注意:如果列是表中剩下的唯一一列,则DROP子句将不起作用。

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

添加列

要向现有表中添加新列,我们使用 ALTER TABLE 命令中的ADD关键字。

示例

在以下查询中,我们向现有表 CUSTOMERS 中添加一个名为 ID 的列。

ALTER TABLE CUSTOMERS ADD ID INT;

输出

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

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

现在,CUSTOMERS 表将包含与您首次创建表时相同的两列。但新添加的 ID 列默认情况下将添加到表的末尾。在这种情况下,它将在 NAME 列之后添加。

验证

让我们使用以下查询进行验证:

SHOW COLUMNS FROM CUSTOMERS;

从下面的输出中可以看到,新添加的 ID 列已插入到表的末尾。

字段 类型 空值 默认值 额外
NAME varchar(20) YES NULL
ID int YES NULL

重新定位列

如果我们希望列放置在表中的特定位置,我们可以使用FIRST将其设为第一列,或使用AFTER col_name指示新列应放置在col_name之后。

示例

考虑之前修改过的 CUSTOMERS 表,其中 NAME 是第一列,ID 是最后一列。

在以下查询中,我们从表中删除 ID 列,然后将其添加回来,使用FIRST关键字将其定位为表中的第一列:

ALTER TABLE CUSTOMERS DROP ID; ALTER TABLE CUSTOMERS ADD ID INT FIRST;

输出

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

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

验证

现在,让我们验证 CUSTOMERS 表中列的位置:

SHOW COLUMNS FROM CUSTOMERS;

从下面的输出中可以看到,ID 列位于第一位。

字段 类型 空值 默认值 额外
ID int YES NULL
NAME varchar(20) YES NULL

示例

在这里,我们从表中删除 ID 列,然后将其添加回来,使用AFTER col_name关键字将其定位在 NAME 列之后。

ALTER TABLE CUSTOMERS DROP ID; ALTER TABLE CUSTOMERS ADD ID INT AFTER NAME;

输出

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

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

验证

现在,让我们验证 CUSTOMERS 表中列的位置:

SHOW COLUMNS FROM CUSTOMERS;

从下面的输出中可以看到,ID 列位于第一位。

字段 类型 空值 默认值 额外
NAME varchar(20) YES NULL
ID int YES NULL

注意:FIRST 和 AFTER 说明符仅适用于 ADD 子句。这意味着,如果要重新定位表中现有的列,则必须先DROP它,然后在新的位置ADD它。

更改列定义或名称

在 MySQL 中,要更改列的定义,我们使用 ALTER 命令结合MODIFYCHANGE子句。

示例

在下面的查询中,我们使用MODIFY子句将列NAME的定义从 varchar(20) 更改为 INT:

ALTER TABLE CUSTOMERS MODIFY NAME INT;

输出

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

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

验证

现在,让我们验证 CUSTOMERS 表中 NAME 列的定义:

SHOW COLUMNS FROM CUSTOMERS;

我们可以看到,NAME 列的定义已更改为 INT。

字段 类型 空值 默认值 额外
NAME int YES NULL
ID int YES NULL

示例

我们也可以使用 CHANGE 更改列定义,但语法与 MODIFY 略有不同。在 CHANGE 关键字之后,我们指定要更改的列的名称(两次),然后指定新的定义。

在这里,我们使用CHANGE子句将列ID的定义从 INT 更改为 varchar(20):

ALTER TABLE CUSTOMERS MODIFY ID VARCHAR(20);

输出

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

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

验证

现在,让我们验证 CUSTOMERS 表中 NAME 列的定义:

SHOW COLUMNS FROM CUSTOMERS;

我们可以看到,NAME 列的定义已更改为 INT。

字段 类型 空值 默认值 额外
NAME int YES NULL
ID varchar(20) YES NULL

更改列的默认值

在 MySQL 中,我们可以使用 **ALTER** 命令和 **DEFAULT** 约束来更改任何列的默认值。

示例

在以下示例中,我们正在 **更改** **NAME** 列的默认值。

ALTER TABLE CUSTOMERS ALTER NAME SET DEFAULT 1000;

输出

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

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

验证

现在,让我们验证 CUSTOMERS 表中 NAME 列的默认值 -

SHOW COLUMNS FROM CUSTOMERS;

我们可以看到,NAME 列的默认值已更改为 1000。

字段 类型 空值 默认值 额外
NAME int YES 1000
ID varchar(20) YES NULL

示例

我们可以使用 **ALTER** 命令和 **DROP** 子句从任何列中删除默认约束。

这里,我们正在删除 **NAME** 列的默认约束。

ALTER TABLE CUSTOMERS ALTER NAME DROP DEFAULT;

输出

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

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

验证

现在,让我们验证 CUSTOMERS 表中 NAME 列的默认值 -

SHOW COLUMNS FROM CUSTOMERS;

我们可以看到,NAME 列的默认值已更改为 NULL。

字段 类型 空值 默认值 额外
NAME int YES NULL
ID varchar(20) YES NULL

更改(重命名)表

要重命名表,请使用 **ALTER TABLE** 语句的 **RENAME** 选项。

示例

以下查询将名为 CUSTOMERS 的表重命名为 BUYERS。

ALTER TABLE CUSTOMERS RENAME TO BUYERS;

输出

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

Query OK, 0 rows affected (0.02 sec)

验证

现在,让我们验证 CUSTOMERS 表中 NAME 列的默认值 -

SHOW COLUMNS FROM BUYERS;

该表已重命名为 BUYERS,我们可以从其中的列中看到。

字段 类型 空值 默认值 额外
NAME int YES NULL
ID varchar(20) YES NULL

使用客户端程序更改表

除了使用 MySQL 查询更改 MySQL 数据库中现有表之外,我们还可以使用客户端程序执行 ALTER TABLE 操作。

语法

以下是使用各种编程语言从 MySQL 数据库中更改表的语法 -

要通过 PHP 程序从 MySQL 数据库中更改表,我们需要使用 **mysqli** 函数 **query()** 执行 **Alter** 语句,如下所示 -

$sql = "ALTER TABLE table_name"; $mysqli->query($sql);

要通过 Node.js 程序从 MySQL 数据库中更改表,我们需要使用 **mysql2** 库的 **query()** 函数执行 **Alter** 语句,如下所示 -

sql = "ALTER TABLE table_name"; con.query(sql);

要通过 Java 程序从 MySQL 数据库中更改表,我们需要使用 **JDBC** 函数 **executeUpdate()** 执行 **Alter** 语句,如下所示 -

String sql = "ALTER TABLE table_name"; statement.execute(sql);

要通过 Python 程序从 MySQL 数据库中更改表,我们需要使用 MySQL **Connector/Python** 的 **execute()** 函数执行 **Alter** 语句,如下所示 -

sql = "ALTER TABLE table_name"; cursorObj.execute(sql);

示例

以下是程序 -

$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $dbname = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s", $mysqli->connect_error); exit(); } // printf('Connected successfully.'); $sql = "ALTER TABLE testalter_tbl DROP i"; if ($mysqli->query($sql)) { printf("table altered successfully."); } if ($mysqli->errno) { printf("table could not alter: %s", $mysqli->error); } $mysqli->close();

输出

获得的输出如下 -

table altered successfully.
var mysql = require('mysql2'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err){ if (err) throw err; console.log("Connected!"); console.log("--------------------------"); sql = "USE TUTORIALS" con.query(sql); //Altering a table sql = "ALTER TABLE testalter_tbl DROP i"; con.query(sql, function(err){ if (err) throw err console.log("Altered table successfully..."); }); });

输出

产生的输出如下 -

Connected!
--------------------------
Altered table successfully...
import java.sql.*; public class AlterTable { public static void main(String[] args){ String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try{ Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Alter table statement...! String sql = "ALTER TABLE testalter_tbl DROP i"; statement.executeUpdate(sql); System.out.println("Table altered successfully...!"); connection.close(); } catch(Exception e){ System.out.println(e); } } }

输出

获得的输出如下所示 -

Connected successfully...!
Table altered successfully...!
import mysql.connector #establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) table_name = 'testalter_tbl' # ALTER TABLE statement alter_statement = 'testalter_tbl DROP i' #Creating a cursor object cursorObj = connection.cursor() cursorObj.execute(f"ALTER TABLE {table_name} {alter_statement}") print(f"Table '{table_name}' is altered successfully.") cursorObj.close() connection.close()

输出

以下是上述代码的输出 -

Table 'testalter_tbl' is altered successfully.
广告