Altering MYSQL Tables

The ability to change the structure of a database table after it has been created, and even after it contains data, will come in handy sooner or later. The ALTER TABLE statement gives you the ability to add, delete and rename columns, as well as create and destroy indexes and change the type of an existing column.

ALTER TABLE - Add Column

The syntax to add a new column to an existing table is: ALTER TABLE tablename ADD COLUMN colname coltype;
By default, additional columns will be added to the end of the specified table. You can use "FIRST" to add a new column to the beginning of the table instead, or "AFTER colname" to specify which existing column the new column should be inserted next to (directly after).
ALTER TABLE pets ADD COLUMN photo VARCHAR(50);
ALTER TABLE pets ADD COLUMN photo VARCHAR(50) FIRST;
ALTER TABLE pets ADD COLUMN photo VARCHAR(50) AFTER pedigree;
If you are concerned that a column name might already exist, you can used "ADD UNIQUE" in place of "ADD COLUMN" so that the column will only be added if the column name has not already been used.

ALTER TABLE - Delete Column

The syntax to delete an existing column is: ALTER TABLE tablename DROP COLUMN colname;
Any data contained in a dropped column will be deleted as well.
ALTER TABLE pets DROP COLUMN photo;

ALTER TABLE - Rename Table

To rename the entire table, the syntax is: ALTER TABLE tablename RENAME TO tablename;
ALTER TABLE pets RENAME TO animals;

ALTER TABLE - Change Table

To change the name of a column, the syntax is: ALTER TABLE tablename CHANGE oldcolname newcolname coltype;
ALTER TABLE pets CHANGE pedigree bloodline TEXT;

ALTER TABLE - Modify Table

To change/modify the type of a column, the syntax is: ALTER TABLE tablename MODIFY colname coltype;
As an alternative, CHANGE can be used to modify the column type. Both old and new column names must be specified when using CHANGE, but they can be the same so that the column name does not actually change.
ALTER TABLE pets MODIFY breed VARCHAR(50);
ALTER TABLE pets CHANGE breed breed VARCHAR(50);