Modify a table

The ALTER TABLE statement is used to change the existing table structure. It can be used to add or remove columns, change the column type, rename a table, etc.

Rename a table

The ALTER TABLE statement can be used to rename a table. The syntax:

ALTER TABLE old_name RENAME TO new_name

Here is how we can rename our table from testtable to testtb:

mysql> ALTER TABLE testtable RENAME TO testtb;
 Query OK, 0 rows affected (0.01 sec)

Change the column data type

We can change the data type of a column using the ALTER TABLE statement. Here is the syntax:

ALTER TABLE table_name MODIFY column_name new_type

For example, to change the data type of a column called year from CHAR to INT, we can use the following command:

mysql> ALTER TABLE testtb MODIFY year INT;
 Query OK, 2 rows affected (0.02 sec)
 Records: 2 Duplicates: 0 Warnings: 0

Add a new column

We can use the ALTER TABLE statement to add a new column to our table. Here is the syntax:

ALTER TABLE table_name ADD COLUMN column_name TYPE

Here is an example. To add a new column called postcode of the INT type to our table testtb, we can use the following command:

mysql> ALTER TABLE testtb ADD COLUMN postcode INT;
 Query OK, 2 rows affected (0.01 sec)
 Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM testtb;
 +------+-----------+------+----------+
 | name | surname | year | postcode |
 +------+-----------+------+----------+
 | Amy | Goodridge | 1991 | NULL |
 | Mark | Smith | 1955 | NULL |
 +------+-----------+------+----------+
 2 rows in set (0.00 sec)

Remove a column

You can use the following ALTER TABLE statement to drop a column from a table:

ALTER TABLE table_name DROP COLUMN column_name

To remove the column postcode we’ve created in the previous step, we would use the following command:

mysql> ALTER TABLE testtb DROP COLUMN postcode;
 Query OK, 2 rows affected (0.01 sec)
 Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM testtb;
 +------+-----------+------+
 | name | surname | year |
 +------+-----------+------+
 | Amy | Goodridge | 1991 |
 | Mark | Smith | 1955 |
 +------+-----------+------+
 2 rows in set (0.00 sec)