MySQL, Second Edition

While every SQL term can be used in many ways to accomplish different goals, ALTER may be the most complex. In general, ALTER is used to change the structure of a table, by:

  • Changing its name

  • Adding or deleting indexes

  • Modifying its columns

Table B.2 lists some of the common ALTER clauses, all of which would begin with the phrase ALTER TABLE tablename. For example:

ALTER TABLE my_table ADD COLUMN my_column INT UNSIGNED ALTER TABLE my_table DROP INDEX my_index

Table B.2. The ALTER SQL command can be used to modify tables in numerous ways.

ALTER TABLE Clauses

Clause Usage

Meaning

ALTER TABLE tblname ADD COLUMN colname coltype

Adds a new column to the end of the table.

ALTER TABLE tblname CHANGE COLUMN colname newcolname newcoltype

Allows you to change the data type and properties.

ALTER TABLE tblname DROP COLUMN colname

Removes a column from a table, including all of its data.

ALTER TABLE tblname ADD INDEX indexname (columns)

Adds a new index on the listed column(s).

ALTER TABLE tblname DROP INDEX indexname

Removes an existing index.

ALTER TABLE tblname RENAME AS newtblname

Changes the name of a table.

Related

Категории