MySQL - ALTER TABLE

There are numerous MySQL syntax on ALTER TABLE and this is powerful one:
1. Add a column (first, before, after)

mysql> ALTER TABLE data ADD city CHAR(50) NOT NULL DEFAULT '' AFTER name;

2. Remove a column

mysql> ALTER TABLE data DROP city

3. Redefine and rename a column

mysql> ALTER TABLE data CHANGE city town char(50) not null default '';

4. Make a column to a new type
mysql> ALTER TABLE data MODIFY city INT NOT NULL DEFAULT 0;

5. Create an index

msyql> ALTER TABLE data ADD INDEX index_id (id);

6. Create a unique

mysql> ALTER TABLE data ADD UNIQUE name_people(name);

7. Define a default value
mysql> ALTER TABLE data ALTER name SET DEFAULT 'no name';

8. Drop primary key
mysql> ALTER TABLE data DROP PRIMARY KEY;

9. Drop index

mysql> ALTER TABLE data DROP INDEX index_id;

10. Remove default value
mysql> ALTER TABLE data ALTER city DROP DEFAULT

11. Change table type
mysql> ALTER TABLE data TYPE=MERGE

MySQL - ALTER TABLE