MySQL Tutorial

Altering Tables

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;

You might also like...

Comments

About the author

Gez Lemon United Kingdom

I'm available for contract work. Please visit Juicify for details.

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Programs must be written for people to read, and only incidentally for machines to execute.”