MySQL ALTER TABLE

MySQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns and to add or drop various constraints on an existing table.

To add a column to a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;

The SQL statement below adds an “Email” column to the “Customers” table:

ALTER TABLE Customers
ADD Email varchar(255);

To delete a column from a table, use the following syntax (note that some database systems may not support column deletion)

ALTER TABLE table_name
DROP COLUMN column_name;

The SQL statement below deletes the “Email” column from the “Customers” table:

ALTER TABLE Customers
DROP COLUMN Email;

To modify the data type of a column in a table, use the following syntax:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Consider the “Persons” table:

Now, we want to add a column named “DateOfBirth” to the “Persons” table.

We use the following SQL statement:

ALTER TABLE Persons
ADD DateOfBirth date;

Note that the new column, “DateOfBirth”, is of type date and will store dates. Data types define what kind of data a column can hold.

The structure of the “Persons” table will now appear as follows:

Now, we aim to modify the data type of the column named “DateOfBirth” in the “Persons” table.

We employ the following SQL statement:

ALTER TABLE Persons
MODIFY COLUMN DateOfBirth year;

Note that the “DateOfBirth” column is now of type year and will store a year in either a two- or four-digit format

Next, we intend to delete the column named “DateOfBirth” from the “Persons” table.

We utilize the following SQL statement

ALTER TABLE Persons
DROP COLUMN DateOfBirth;

The structure of the “Persons” table will now appear as follows: