MySQL - ALTER 命令

• 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 表各列的详细信息 -

删除列

要删除现有表中的列，我们使用带有 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 列。因此它已被删除。

注意:如果表中只剩下该列，则 DROP 子句将不起作用。

添加列

要向现有表中添加新列，我们在 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 列已插入到表的末尾。

重新定位列

如果我们想将某一列放置在表格中的特定位置，可以使用 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 列，然后将其添加回来，并使用 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 列位于第一位。

注意: FIRST 和 AFTER 说明符仅适用于 ADD 子句。这意味着，如果要重新定位表中的现有列，必须先将其 DROP 删除，然后再将其 ADD 添加至新位置。

更改列定义或名称

在 MySQL 中，要更改列的定义，我们将 MODIFY 或 CHANGE 子句与 ALTER 命令结合使用。

示例

在下面的查询中，我们使用 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。

示例

我们也可以使用 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。

更改列的默认值

在 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。

示例

我们可以使用 DROP 子句和 ALTER 命令来删除任意列的默认约束。

此处，我们删除 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。

更改(重命名)表

要重命名表，请使用 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。

使用客户端程序修改表

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

语法

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

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

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

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

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"
});
  //连接到 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
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'testalter_tbl'
# ALTER TABLE 语句
alter_statement = 'testalter_tbl  DROP i'  
#创建游标对象
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.