ALTER [IGNORE] TABLE tbl_name action [, action ...]
In this code, each action refers to an action as defined below.
ALTER TABLE queries may be used to change the definition of a table, without losing any of the information in the table (except in obvious cases, such as the deletion of a column). Here are the main actions that are possible:
ADD [COLUMN] create_definition [FIRST | AFTER column_name]
This action adds a new column to the table. The syntax for create_definition is as described for "CREATE TABLE". By default, the column will be added to the end of the table, but by specifying FIRST or AFTER column_name, you can place the column wherever you like. The optional word COLUMN does not actually do anything—leave it off unless you particularly like to see it there.
ADD INDEX [index_name] (index_col_name, ...)
This action creates a new index to speed up searches based on the column(s) specified. It’s usually a good idea to assign a name to your indices by specifying the index_name, otherwise, a default name based on the first column in the index will be used. When creating an index based on CHAR and/or VARCHAR columns, you can specify a number of characters to index as part of index_col_name (e.g. myColumn(5) will index the first 5 characters of myColumn). When indexing BLOB and TEXT columns, this number must be specified. For detailed information on indexes, see the MySQL Reference Manual, or Mike Sullivan's excellent article Optimizing your MySQL Application on SitePoint.
ADD PRIMARY KEY (index_col_name, ...)
This action creates an index for the specified row(s) with the name “PRIMARY”, identifying it as the primary key for the table. All values (or combinations of values) must be unique, as described for the ADD UNIQUE action below. This will cause an error if a primary key already exists for this table. index_col_name is defined as it is for the ADD INDEX action above.
ADD UNIQUE [index_name] (index_col_name, ...)
This action creates an index on the specified columns, but with a twist: all values in the designated column, or all combinations of values, if more than one column is included in the index, must be unique. The parameters index_name and index_col_name are defined as they are for the ADD INDEX action above.
ALTER [COLUMN] col_name {SET DEFAULT value | DROP DEFAULT}
This action assigns a new default value to a column (SET DEFAULT), or removes the existing default value (DROP DEFAULT). Again, the word COLUMN is completely optional, and has no effect one way or the other.
CHANGE [COLUMN] col_name create_definition
This action replaces an existing column (col_name) with a new column, as defined by create_definition (the syntax of which is as specified for "CREATE TABLE"). The data in the existing column is converted, if necessary, and placed in the new column. Note that create_definition includes a new column name, so this action may be used to rename a column, if you wish. If you want to leave the name of the column unchanged, however, don’t forget to include it twice (once for col_name and once for create_definition), or use the MODIFY action below.
MODIFY [COLUMN] create_definition
Nearly identical to the CHANGE action above, this action lets you specify a new declaration for a column in the table, but assumes you will not be changing its name. Thus, you simply have to re-declare the column with the same name in the create_definition parameter (as defined for "CREATE TABLE"). As before, COLUMN is completely optional and does nothing. Although convenient, this action is not standard SQL syntax, and was added for compatibility with an identical extension in Oracle database servers.
DROP [COLUMN] col_name
Fairly self-explanatory, this action completely removes a column from the table. The data in that column is irretrievable after this query completes, so be sure of the column name. COLUMN, as usual, can be left off—it doesn’t do anything but make the query sound better when read aloud.
DROP PRIMARY KEY
DROP INDEX index_name
The above two actions are quite self-explanatory: they remove from the table the primary key, and a specific index, respectively.
RENAME [TO | AS] new_tbl_name
This action renames the table. The words TO and AS are completely optional, and don’t do anything. Use ‘em if you like ‘em.
ORDER BY col_name
Lets you sort the entries in a table by a particular column. However, this is not a persistent state; as soon as new entries are added to the table, or existing entries modified, the ordering can no longer be guaranteed. The only practical use of this action would be to increase performance of a table that you regularly sorted in a certain way in your application’s SELECT queries. Under some circumstances, arranging the rows in (almost) the right order to begin with will make sorting quicker.
table_options
Using the same syntax as in the CREATE TABLE query, this action allows you to set and change advanced table options. These options are fully documented in the MySQL Reference Manual.