ALTER Commands

While every SQL term can be used in many ways to accomplish different goals, ALTER may be the most complex. In general, ALTER is used to change the structure of a table, by:

• Changing its name

• Adding or deleting indexes

• Modifying its columns

Table B.2 lists some of the common ALTER clauses, all of which would begin with the phrase ALTER TABLE tablename. For example:

ALTER TABLE my_table ADD COLUMN my_column INT UNSIGNED
ALTER TABLE my_table DROP INDEX my_index

Table B.2. The ALTER SQL command can be used to modify tables in numerous ways.

ALTER TABLE Clauses
Clause Usage	Meaning
ALTER TABLE tblname ADD COLUMN colname coltype	Adds a new column to the end of the table.
ALTER TABLE tblname CHANGE COLUMN colname newcolname newcoltype	Allows you to change the data type and properties.
ALTER TABLE tblname DROP COLUMN colname	Removes a column from a table, including all of its data.
ALTER TABLE tblname ADD INDEX indexname (columns)	Adds a new index on the listed column(s).
ALTER TABLE tblname DROP INDEX indexname	Removes an existing index.
ALTER TABLE tblname RENAME AS newtblname	Changes the name of a table.