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://: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.