建好的資料表可以透過指令 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 ...;
- 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], ...;
- DROP – 刪除欄位 (DROP COLUMN) 或是刪除欄位的 Key (Primary , Unique , Index Key)
- MODIFY – 修改欄位的資料類別,或可否為 NULL.
- 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)
沒有解決問題,試試搜尋本站其他內容