Memodifikasi Tabel dengan ALTER TABLE

Tabel yang telah dibuat dapat dimodifikasi menggunakan command ALTER TABLE. Bentuk command untuk ALTER TABLE adalah:
ALTER [IGNORE] TABLE table_name specification [,specification]
dengan specification sebagai berikut:

ADD [COLUMN] column name (column definitions) [FIRST or AFTER column_name]
ADD INDEX [index_name] (column_list)
ADD PRIMARY KEY (column_list)
ADD UNIQUE [index_name] (column_list)
ALTER [COLUMN] column_name {SET DEFAULT default_value or DROP DEFAULT}
CHANGE [COLUMN] old_col_name create_definition
DROP [COLUMN] col_name
DROP PRIMARY KEY
DROP INDEX index_name
MODIFY [COLUMN] create_definition
RENAME [AS] new_tbl_name

Menambah kolom pada tabel

mysql> CREATE TABLE TABLE5(
-> id int
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> desc TABLE5;
+-------+---------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> ALTER TABLE TABLE5
-> ADD name VARCHAR(5);
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc TABLE5;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | YES |   | NULL | |
| name  | varchar(5) | YES  |   | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE TABLE5
-> ADD first_name VARCHAR(30) AFTER id;
Query OK, 0 rows affected (0.84 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc TABLE5;
+------------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11)    | YES  | | NULL   |   |
| first_name | varchar(30) | YES  | | NULL   |   |
| name | varchar(5)  | YES  | | NULL   |   |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Memodifikasi nama dan definisi tabel

mysql> ALTER TABLE TABLE5
-> CHANGE name last_name VARCHAR(30);
Query OK, 0 rows affected (0.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc TABLE5;
+------------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11)    | YES  | | NULL   |   |
| first_name | varchar(30) | YES  | | NULL   |   |
| last_name  | varchar(30) | YES  | | NULL   |   |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Memodifikasi definisi tabel

mysql> ALTER TABLE TABLE5
-> MODIFY first_name VARCHAR(15),
-> MODIFY last_name VARCHAR(15);
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc TABLE5;
+------------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11)    | YES  | | NULL   |   |
| first_name | varchar(15) | YES  | | NULL   |   |
| last_name  | varchar(15) | YES  | | NULL   |   |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Menambahkan primary key

mysql> ALTER TABLE TABLE5 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc TABLE5;
+------------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11)    | NO   | PRI | 0   |   |
| first_name | varchar(15) | YES  | | NULL   |   |
| last_name  | varchar(15) | YES  | | NULL   |   |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Menghapus kolom

mysql> ALTER TABLE TABLE5 DROP first_name;
Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC TABLE5;
+------------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11)    | NO   | PRI | 0   |   |
| last_name  | varchar(15) | YES  | | NULL   |   |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Mengganti nama tabel

Mengganti nama tabel yang sudah dibuat dapat dilakukan dengan menggunakan perintah ALTER TABLE RENAME...
Berikut adalah contoh penggunaannya:
mysql> SHOW TABLES;
+-----------------+
| Tables_in_PRAK2 |
+-----------------+
| TABLE1   |
| TABLE2   |
| TABLE3   |
| TABLE4   |
| employee   |
| employee_copy   |
+-----------------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE employee_copy RENAME senior_employee;
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_PRAK2 |
+-----------------+
| TABLE1   |
| TABLE2   |
| TABLE3   |
| TABLE4   |
| employee   |
| senior_employee |
+-----------------+
6 rows in set (0.00 sec)

Menghapus semua data dalam tabel

Untuk menghapus semua data di dalam tabel digunakan command TRUNCATE sebagai berikut:
mysql> CREATE TABLE employee_copy AS
-> SELECT * FROM employee;
Query OK, 8 rows affected (0.16 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employee_copy;

+----+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date   | salary  | city    |description  |
+----+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason   | Martin   | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   |Programmer   |
| 2  | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
| 3  | James      | Smith   | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4  | Celia   | Rice   | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
| 5 | Robert   | Black   | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6  | Linda   | Green   | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester |
| 7  | David   | Larry   | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager |
| 8  | James   | Cat   | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |

8 rows in set (0.00 sec)
mysql> TRUNCATE TABLE employee_copy;
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM employee_copy;
Empty set (0.00 sec)

Menghapus Tabel

Untuk menghapus tabel dari database, digunakan command DROP TABLE.
mysql> DROP TABLE employee_copy;
Query OK, 0 rows affected (0.06 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_PRAK2 |
+-----------------+
| TABLE1   |
| TABLE2   |
| TABLE3   |
| TABLE4   |
| employee   |
| senior_employee |
+-----------------+
7 rows in set (0.00 sec)