Alter 语句的使用

bj2008_0201

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.
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/73684/showart_1221298.html