Alter 语句的使用

Table Manipulation

Lets say you made your table, and all the data has been added to it. Now you have come across a problem... your limit for characters in that particular column is too small for what you now need. You don't want to have to delete all of this data, yet, you have to change your table some how.

Fret no more everyone, you can manipulate your tables that have already been created.

The command for this task is known as ALTER TABLE. Just a note, it is possible to mix and match these commands, usually just separate them with a comma (,), or just place them all in the same line. Play around with them to get a feel for what I am talking about.

Renaming a Table

mysql> ALTER TABLE users RENAME public;

Changing a columns datatype

mysql> ALTER TABLE public MODIFY name CHAR(150);

Renaming a Table and Changing its datatype at once

mysql> ALTER table users CHANGE
-> email emailaddy CHAR (100);

Adding a Column

mysql> ALTER TABLE public ADD time TIMESTAMP;

Remove a Column

mysql> ALTER TABLE public DROP COLUMN time;

After you make these changes to the table, you may want to optimize the table afterwards (especially if you are using VARCHAR's, TEXT's or BLOB's, as this will optimize its memory allocation. You will also want to do it if you have deleted a large part of a table.

During a table optimization, the original table is available to clients, however, modifying and adding to the table is stalled until optimization is complete.

The syntax is:

OPTIMIZE TABLE table_name_goes_here

Deleting an entire table

To delete (or drop) an entire table, you would use the following syntax;

mysql> DROP TABLE public;

If you would like to drop more tables at once though, you would do this;

mysql> DROP TABLE public, tests;

Though this does not even remotely cover all of the available features found within MySQL, it does scratch the surface enough to catch your interest. Look for more tutorials coming soon on TheScripts.com covering MySQL's features.