SQL 語法 ALTER TABLE

Loading

建好的資料表可以透過指令 ALTER 做修改.

測試環境為 CentOS 8 x86_64 (虛擬機)

先建立一個資料庫 (testdb) , 與資料表 (employee) 格式為 Name CHAR(20), Dept CHAR(20), JobTitle CHAR(20) 各 20 個字元.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> CREATE TABLE employee (Name CHAR(20),Dept CHAR(20),JobTitle CHAR(20));
Query OK, 0 rows affected (0.006 sec)

MariaDB [testdb]> DESCRIBE employee; 
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| JobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.002 sec)

後續要改變資料表,可以使用 ALTER 指令,語法如下:

ALTER TABLE table_name ...;
  1. ADD – 增加欄位 (ADD COLUMN) 或是增加欄位的 Key (Primary , Unique , Index Key)
    新增單一欄位.

    ALTER TABLE table 
    ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];
    

    新增多個欄位.

    ALTER TABLE table
    ADD [COLUMN] column_name_1 column_1_definition [FIRST|AFTER existing_column],
    ADD [COLUMN] column_name_2 column_2_definition [FIRST|AFTER existing_column],
    ...;
    
  2. DROP – 刪除欄位 (DROP COLUMN) 或是刪除欄位的 Key (Primary , Unique , Index Key)
  3. MODIFY – 修改欄位的資料類別,或可否為 NULL.
  4. CHANGE – 功能同 MODIFY 還可以一併修改欄位名稱.

新增欄位

新增 salary 欄位並指定在 Dept 欄位後面,資料格式為 char(11).

MariaDB [testdb]> ALTER TABLE employee ADD COLUMN Salary CHAR(11) AFTER Dept;
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> DESCRIBE employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| Salary   | char(11) | YES  |     | NULL    |       |
| JobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.001 sec)

修改欄位

修改 Salary 欄位,資料格式為 INT(11).

MariaDB [testdb]> ALTER TABLE employee CHANGE COLUMN Salary Salary INT(11);
Query OK, 0 rows affected (0.012 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> DESCRIBE employee;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| Salary   | int(11)  | YES  |     | NULL    |       |
| JobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.001 sec)

Primary , Unique , Index Key

建立(轉)好的資料表在 phpMyAdmin 會顯示.

這個資料表中沒有可以辦識 (unique)的資料欄位 將無法執行修改、複製、刪除等相關的功能。

我們需要在 資料表 / 結構 的其中之一的資料欄位設定成為 unique .或是 Primary Key (主鍵) – 必須為 not null (不能為空) 並且要 unique (唯一) .

可以在 資料表/瀏覽/編輯 進行 資料 新增或是修改 .

下面是透過指令方式在資料表的資料欄位設定成為 Primary , Unique , Index Key ,但他們的差別是什麼呢!

  • Primary
    Primary Key (主鍵) 必須為 not null (不能為空) 並且要 Unique (唯一).資料表只有一個欄位可以為 Primary Key.
    剛剛在建立資料表的時候並沒有設定 Primary Key,可以透過 ALTER 來修改.

    MariaDB [testdb]> ALTER TABLE employee ADD PRIMARY KEY (Name);
    Query OK, 0 rows affected (0.013 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | NO   | PRI | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | Salary   | int(11)  | YES  |     | NULL    |       |
    | JobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    4 rows in set (0.002 sec)
    
    MariaDB [testdb]> SHOW INDEX FROM employee;
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | employee |          0 | PRIMARY  |            1 | Name        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    1 row in set (0.00 sec)
    

    來試試看新增一些資料.

    MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle) VALUES ('Ben','Testing','Engineer');
    Query OK, 1 row affected (0.00 sec)
    

    因為 Name 已經設定成為 Primary Key 所以重覆的名稱不能使用 (需 Unique 唯一),但是”代表的是空字串,並不是 NULL(沒有資料).

    MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle) VALUES ('Ben','Sales','Manager');
    ERROR 1062 (23000): Duplicate entry 'Ben' for key 'PRIMARY'
    MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle) VALUES ('','Sales','Manager');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle) VALUES (NULL,'Sales','Manager');
    ERROR 1048 (23000): Column 'Name' cannot be null
    

    要移除 Primary key ,一樣是透過 ALTER 指令.因為資料表中只能有一個欄位設定成為 Primary Key ,所以在 DROP 移除時就不需要再指定了.

    MariaDB [testdb]> ALTER TABLE employee DROP PRIMARY KEY;
    Query OK, 2 rows affected (0.015 sec)              
    Records: 2  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | NO   |     | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | Salary   | int(11)  | YES  |     | NULL    |       |
    | JobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    4 rows in set (0.001 sec)
    
    MariaDB [testdb]> SHOW INDEX FROM employee;
    Empty set (0.01 sec)
    

    但欄位還是不能 NULL ,一樣是透過 ALTER 指令修改.

    MariaDB [testdb]> ALTER TABLE employee MODIFY Name CHAR(20) NULL;
    Query OK, 0 rows affected (0.015 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | YES  |     | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | Salary   | int(11)  | YES  |     | NULL    |       |
    | JobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    4 rows in set (0.002 sec)
    
  • Primary KEY (Auto Increment)
    如果沒有辦法從其中一個欄位來設定成為 primary ,這時候可以考慮新增一個欄位為 Primary 並設定成 AUTO_INCREMENT,AUTO INCREMENT PRIMARY KEY 欄位型態必須為整數類 (INT, TINYINT , SMALLINT , BIGINT 等…).

    MariaDB [testdb]> CREATE TABLE employee1 (Name CHAR(20) , Dept CHAR(20) , JobTitle CHAR(20));
    Query OK, 0 rows affected (0.008 sec)
    
    MariaDB [testdb]> ALTER TABLE employee1 ADD ID smallint(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;
    Query OK, 0 rows affected (0.009 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | Name     | char(20)     | YES  |     | NULL    |                |
    | Dept     | char(20)     | YES  |     | NULL    |                |
    | JobTitle | char(20)     | YES  |     | NULL    |                |
    | ID       | smallint(11) | NO   | PRI | NULL    | auto_increment |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.002 sec)
    

    在 phpMyAdmin 一樣可以設定,只要在 Index 欄位選取 “PRIMARY”, 並勾選 A_I 欄即可

  • Unique
    Unique Key (唯一鍵) 不能重覆,但是可以為 null (空).

    MariaDB [testdb]> ALTER TABLE employee1 ADD UNIQUE (Name);
    Query OK, 0 rows affected (0.008 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | Name     | char(20)     | YES  | UNI | NULL    |                |
    | Dept     | char(20)     | YES  |     | NULL    |                |
    | JobTitle | char(20)     | YES  |     | NULL    |                |
    | ID       | smallint(11) | NO   | PRI | NULL    | auto_increment |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.001 sec)
    

    試試新增一些資料,當 Name 欄位資料重覆時無法輸入新資料.

    MariaDB [testdb]> INSERT INTO employee1 (Name , Dept , JobTitle) VALUES ('Tank','Sales','PM');
    Query OK, 1 row affected (0.002 sec)
    
    MariaDB [testdb]> INSERT INTO employee1 (Name , Dept , JobTitle) VALUES ('Tank','Engineer','Hardware');
    ERROR 1062 (23000): Duplicate entry 'Tank' for key 'Name'
    MariaDB [testdb]> INSERT INTO employee1 (Name , Dept , JobTitle) VALUES (NULL,'Engineer','Hardware');
    Query OK, 1 row affected (0.003 sec)
    

    移除 Unique

    MariaDB [testdb]> ALTER TABLE employee1 DROP INDEX Name;
    Query OK, 0 rows affected (0.006 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | Name     | char(20)     | YES  |     | NULL    |                |
    | Dept     | char(20)     | YES  |     | NULL    |                |
    | JobTitle | char(20)     | YES  |     | NULL    |                |
    | ID       | smallint(11) | NO   | PRI | NULL    | auto_increment |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.001 sec)
    
  • Index Key
    Index Key (索引鍵) ,常做存取的的欄位可以設定成為索引,這樣可以增加搜尋速度.

    MariaDB [testdb]> ALTER TABLE employee1 ADD INDEX (Name);
    Query OK, 0 rows affected (0.011 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | Name     | char(20)     | YES  | MUL | NULL    |                |
    | Dept     | char(20)     | YES  |     | NULL    |                |
    | JobTitle | char(20)     | YES  |     | NULL    |                |
    | ID       | smallint(11) | NO   | PRI | NULL    | auto_increment |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.002 sec)
    

    在 Key 可以看到 Index 為 MUL .

    移除 Index

    MariaDB [testdb]> ALTER TABLE employee1 DROP INDEX Name;
    Query OK, 0 rows affected (0.006 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee1;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | Name     | char(20)     | YES  |     | NULL    |                |
    | Dept     | char(20)     | YES  |     | NULL    |                |
    | JobTitle | char(20)     | YES  |     | NULL    |                |
    | ID       | smallint(11) | NO   | PRI | NULL    | auto_increment |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.002 sec)--------+-------+
    3 rows in set (0.00 sec)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料