DDL - MySQL (Modify Structure)

Add a new data field (ALTER TABLE ... ADD)

Syntax

ALTER TABLE <table_name> ADD <new_field> <field_data_type>;

Usage

• From the worker table. add a new field - NICKNAME with VARCHAR(10).

mysql> ALTER TABLE worker ADD NICKNAME VARCHAR(10);

Output:

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Validation:

mysql> desc worker;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| ID          | int              | YES  |     | NULL    |       |
| WORKER_ID   | varchar(10)      | YES  |     | NULL    |       |
| WORKER_NAME | varchar(50)      | YES  |     | NULL    |       |
| GENDER      | char(1)          | YES  |     | NULL    |       |
| AGE         | tinyint unsigned | YES  |     | NULL    |       |
| JOIN_DATE   | date             | YES  |     | NULL    |       |
| NICKNAME    | varchar(10)      | YES  |     | NULL    |       |
+-------------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Modify the data field (ALTER TABLE ... MODIFY)

Synatx

ALTER TABLE <table_name> MODIFY <field_name> <NEW_DATA_TYPE>;

Usage

• Change the data type of NICKNAME field to VARCHAR(20).

mysql> ALTER TABLE worker MODIFY NICKNAME VARCHAR(20);

Output:

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Validation:

mysql> desc worker;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| ID          | int              | YES  |     | NULL    |       |
| WORKER_ID   | varchar(10)      | YES  |     | NULL    |       |
| WORKER_NAME | varchar(50)      | YES  |     | NULL    |       |
| GENDER      | char(1)          | YES  |     | NULL    |       |
| AGE         | tinyint unsigned | YES  |     | NULL    |       |
| JOIN_DATE   | date             | YES  |     | NULL    |       |
| NICKNAME    | varchar(20)      | YES  |     | NULL    |       |
+-------------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Change the data field (ALTER TABLE ... CHANGE)

Synatx

ALTER TABLE <table_name> CHANGE <OLD_FIELD_NAME> <NEW_FIELD_NAME> <data_type>;

Usage

• Change NICKNAME to USERNAME.

mysql> ALTER TABLE worker CHANGE NICKNAME USERNAME VARCHAR(20);

Output:

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Validation:

mysql> desc worker;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| ID          | int              | YES  |     | NULL    |       |
| WORKER_ID   | varchar(10)      | YES  |     | NULL    |       |
| WORKER_NAME | varchar(50)      | YES  |     | NULL    |       |
| GENDER      | char(1)          | YES  |     | NULL    |       |
| AGE         | tinyint unsigned | YES  |     | NULL    |       |
| JOIN_DATE   | date             | YES  |     | NULL    |       |
| USERNAME    | varchar(20)      | YES  |     | NULL    |       |
+-------------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Drop the data field (ALTER TABLE ... DROP)

Syntax

ALTER TABLE <table_name> DROP <field_name>;

Usage

• Drop the USERNAME data field from worker.

mysql> ALTER TABLE worker DROP USERNAME;

Output:

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Validation:

mysql> desc worker;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| ID          | int              | YES  |     | NULL    |       |
| WORKER_ID   | varchar(10)      | YES  |     | NULL    |       |
| WORKER_NAME | varchar(50)      | YES  |     | NULL    |       |
| GENDER      | char(1)          | YES  |     | NULL    |       |
| AGE         | tinyint unsigned | YES  |     | NULL    |       |
| JOIN_DATE   | date             | YES  |     | NULL    |       |
+-------------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Rename the table name (ALTER TABLE ... RENAME TO ...)

Syntax

ALTER TABLE <table_name> RENAME TO <new_table_name>;

Usage

• Rename the table worker to workers.

mysql> ALTER TABLE worker RENAME TO workers;

Output:

Query OK, 0 rows affected (0.01 sec)

Validation:

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student        |
| workers        |
+----------------+
2 rows in set (0.00 sec)

Drop the table

1. TRUNCATE TABLE ...

• Truncate table will drop the table and re-create it

Syntax

TRUNCATE TABLE <table_name>;

Usage

• Truncate table workers

mysql> TRUNCATE TABLE workers;

Output:

Query OK, 0 rows affected (0.02 sec)

Validation:

mysql> desc workers;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| ID          | int              | YES  |     | NULL    |       |
| WORKER_ID   | varchar(10)      | YES  |     | NULL    |       |
| WORKER_NAME | varchar(50)      | YES  |     | NULL    |       |
| GENDER      | char(1)          | YES  |     | NULL    |       |
| AGE         | tinyint unsigned | YES  |     | NULL    |       |
| JOIN_DATE   | date             | YES  |     | NULL    |       |
+-------------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

2. DROP TABLE IF EXISTS ...

Syntax

DROP TABLE IF EXISTS <table_name>;

Usage

• Drop table workers

mysql> DROP TABLE IF EXISTS workers;

Output:

Query OK, 0 rows affected (0.04 sec)

Validation:

mysql> desc workers;
ERROR 1146 (42S02): Table 'test.workers' doesn't exist

INFO

• TRUNCATE will just remove all the data of a table.
• DROP will remove entire tables and include its data.