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 中，要更改列的定义，我们使用 ALTER 命令结合MODIFY或CHANGE子句。

示例

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

示例

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

更改（重命名）表

要重命名表，请使用 **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"
});
  //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://127.0.0.1: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.