The ALTER TABLE command allows you to alter tables, fields and indexes of a
table.
Renaming a Table
The RENAME modifier allows you to rename a table. The following example renames
the table search to site.
mysql> ALTER TABLE search RENAME site;
Adding Columns to a Table
The ADD COLUMN modifier allows you to add a column to a table. The following
example adds a field called modified to the search table.
mysql> ALTER TABLE search ADD COLUMN modified TIMESTAMP;
The FIRST and AFTER modifiers may be used to position the field within the table. The following places the new field as the first field in the table.
mysql> ALTER TABLE search ADD COLUMN modified TIMESTAMP FIRST;
The next example could be used to add the new field immediately after the field Keywords.
mysql> ALTER TABLE search ADD COLUMN modified TIMESTAMP AFTER Keywords;
Dropping a Column from a Table
The DROP COLUMN modifier may be used to drop a column from the table. The
following example drops the field modified.
mysql> ALTER TABLE search DROP COLUMN modified;
Dropping a Indexes from a Table
The DROP INDEX modifier may be used to drop an index from a table. The following
example drops the index keyIndex from the search table.
mysql> ALTER TABLE search DROP INDEX keyIndex;
Modifying columns
When you modify a column, you must specify the attributes for the column again. The following example renames the Description field to PageDescription in the search table.
mysql> ALTER TABLE search CHANGE Description PageDescription VARCHAR(255)
NOT NULL;
If you only want to change the attributes, use the same column name as in the following example:
mysql> ALTER TABLE search CHANGE Description Description VARCHAR(200)
NOT NULL;
Deleting a Table
Tables are deleted from the database using the DROP statement. The following
example drops the table search from the database.
mysql> DROP TABLE search;
Comments