SQL 語法 ALTER TABLE

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

測試環境為 Ubuntu 16.04 x86_64 虛擬機.

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

root@ubuntu:~# mysql -u root -p
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> use testdb;
Database changed
 
MariaDB [testdb]> create table employee (Name char(20),Dept char(20),jobTitle char(20));
Query OK, 0 rows affected (0.10 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.03 sec)
 
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)

ALTER 的用法

> ALTER TABLE table_name ...;
  1. ADD – 增加欄位 (ADD COLUMN) 或是增加欄位的 Key (Primary , Unique , Index Key)
  2. DROP – 刪除欄位 (DROP COLUMN) 或是刪除欄位的 Key (Primary , Unique , Index Key)
  3. CHANGE – 修改欄位名稱.
  4. MODIFY – 修改欄位的資料類別,或可否為 NULL.

新增欄位

新增 salary 欄位,資料格式為 char(11).

MariaDB [testdb]> ALTER TABLE employee ADD COLUMN salary char(11);
Query OK, 0 rows affected (0.04 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    |       |
| JobTitle | char(20) | YES  |     | NULL    |       |
| salary   | char(11) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改欄位

修改 salary 欄位,資料格式為 int(11).

MariaDB [testdb]> ALTER TABLE employee CHANGE COLUMN salary salary int(11);
Query OK, 0 rows affected (0.03 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    |       |
| JobTitle | char(20) | YES  |     | NULL    |       |
| salary   | int(11)  | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.00 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, 4 rows affected (0.03 sec)               
    Records: 4  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> describe employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | NO   | PRI |         |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.01 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 VALUES ('Ben','Testing','Engineer');
    Query OK, 1 row affected (0.00 sec)
    

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

    MariaDB [testdb]> INSERT INTO employee VALUES ('Ben','Sales','Manager');
    ERROR 1062 (23000): Duplicate entry 'Ben' for key 'PRIMARY'
    MariaDB [testdb]> INSERT INTO employee VALUES ('','Sales','Manager');
    Query OK, 1 row affected (0.00 sec)
    MariaDB [testdb]> INSERT INTO employee 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, 3 rows affected (0.06 sec)               
    Records: 3  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> describe employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | NO   |     |         |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.07 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.05 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    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
  • Primary KEY (Auto Increment)
    如果沒有辦法從其中一個欄位來設定成為 primary ,這時候可以考慮新增一個欄位為 primary 並設定成 auto_increment,,AUTO INCREMENT PRIMARY KEY 欄位型態必須為整數類 (int, tinyiny, smallint, bigint 等…).

    MariaDB [testdb]> create table employee (Name char(20),Dept char(20),jobTitle char(20));
    MariaDB [testdb]> ALTER TABLE employee ADD id smallint(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;
    Query OK, 0 rows affected (0.02 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    |                |
    | jobTitle | char(20)     | YES  |     | NULL    |                |
    | id       | smallint(11) | NO   | PRI | NULL    | auto_increment |
    +----------+--------------+------+-----+---------+----------------+
    4 rows in set (0.00 sec)
    

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

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

    MariaDB [testdb]> alter table employee add unique (Name);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> describe employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | name     | char(20) | YES  | UNI | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    

    試試新增一些資料.

    MariaDB [testdb]> INSERT INTO employee VALUES ('Tank','Sales','PM');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO employee VALUES ('Tank','Engineer','Hardware');
    ERROR 1062 (23000): Duplicate entry 'Tank' for key 'name'
    MariaDB [testdb]> INSERT INTO employee VALUES (NULL,'Engineer','Hardware');
    Query OK, 1 row affected (0.00 sec)
    

    移除 unique

    MariaDB [testdb]> alter table employee drop index Name;
    Query OK, 0 rows affected (0.02 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    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
  • Index Key
    Index Key (索引鍵) ,常做存取的的欄位可以設定成為索引,這樣可以增加搜尋速度.

    MariaDB [testdb]> alter table employee add index (Name);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> describe employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | name     | char(20) | YES  | MUL | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    

    移除 index

    MariaDB [testdb]> alter table employee drop index Name;
    Query OK, 0 rows affected (0.02 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    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    

    可以看到 UNI (Unique) 跟 MUL (Index) 的差別是,Unique 不能重覆, Index 可以重覆.

沒有解決問題,試試搜尋本站其他內容

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

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