Adding column:
ALTER TABLE vendor ADD COLUMN phone CHAR(20);
ALTER TABLE vendor ADD phone CHAR(20) NOT NULL;
When you add a new column to a table, MySQL places it after all existing columns. This is the default placement unless you specify otherwise. To indicate that MySQL should place the new column in a specific position within the table, append either the keyword FIRST or the keyword-identifier combination AFTER column_name to the column definition:
ALTER TABLE HeadOfState ADD InaugurationDate DATE NOT NULL FIRST;
ALTER TABLE HeadOfState ADD InaugurationDate DATE NOT NULL AFTER FirstName;
Remove / Drop column:
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP column_name;
Modify the column definition:
ALTER TABLE HeadOfState MODIFY ID BIGINT UNSIGNED NOT NULL;
The new column definition must include all attributes that you wish to retain, because attributes are not carried over from existing definition.
The CHANGE clause enables you to modify both the column's definition and its name. To use this clause, specify the CHANGE keyword, followed by the existing column name, then its new name, and its new definition. This means, you must specify the existing column name twice if you want to change only the column definition. For example, to change the LastName column from CHAR(30) to CHAR(40) without renaming the column:
ALTER TABLE HeadOfState CHANGE LastName LastName CHAR(40) NOT NULL;
Rename a table:
ALTER TABLE t1 RENAME TO t2;
RENAME TABLE t1 TO t2
RENAME TABLE has advantage over ALTER TABLE in that it can perform multiple table renames in a single operation. One use for this feature is to swap the names of two tables:
RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;
For TEMPORARY tables, RENAME TABLE does not work. You must use ALTER TABLE instead.
Specifying Multiple Table Alterations:
You can specify multiple alterations for a table with a single ALTER TABLE statement. Just separate the actions by commas:
ALTER TABLE HeadOfState RENAME TO CountryLeader, MODIFY ID BIGINT UNSIGNED NOT NULL, ADD Salutation CHAR(30) NULL AFTER FirstName;
Adding Indexes:
To add an index to a table with ALTER TABLE, use the ADD keyword followed by the index-type keywords and a list of columns to be indexed in parentheses:
ALTER TABLE HeadOfState ADD PRIMARY KEY (ID);
ALTER TABLE HeadOfState ADD INDEX (LastName, FirstName);
MySQL allows multiple actions to be performed with a single ALTER TABLE statement. One common use for multiple actions is to add several indexes to a table at the same time, which is more efficient than adding each one separately. Thus, the preceding two ALTER TABLE statements can be combined as follows:
ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName, FirstName);
Specifying an Indexing Algorithm:
ALTER TABLE lookup ADD INDEX USING BTREE (id);
Dropping Indexes:
ALTER TABLE HeadOfState DROP PRIMARY KEY;
ALTER TABLE HeadOfState DROP INDEX index_name;
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_order FOREIGN KEY(orderID) REFERENCES order(id);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_product FOREIGN KEY(productID) REFERENCES product(id);
ALTER TABLE order ADD CONSTRAINT fk_order_customer FOREIGN KEY(customerID) REFERENCES customer(id);
ALTER TABLE product ADD CONSTRAINT fk_product_vendor FOREIGN KEY(vendorID) REFERENCES vendor(id);
ALTER TABLE t ENGINE = MyISAM // change t to use MyISAM storag engine
