لطفا منتظر باشید ...
ALTER TABLE
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.
