ALTER TABLE for add/drop/modify column/add after existing column in MySQL

Following are the example and use of ALTER commands:

Table used for example:

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

ADD new Column in the table

Syntax:
ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type> NOT NULL; 

Example:
mysql> ALTER TABLE test ADD COLUMN create_date date NOT NULL;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

DROP the column from the table

Syntax:
ALTER TABLE <table_name> DROP COLUMN <column_name>;

Example:
mysql> ALTER TABLE test DROP COLUMN create_date;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

Modify the type of column

Syntax:
ALTER TABLE <table_name> MODIFY <column_name> <data_type> NOT NULL;

Example:
mysql> ALTER TABLE test MODIFY create_date DATETIME NOT NULL;
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0

Change type and name of column

Syntax:
ALTER TABLE <table_name> CHANGE <old_col_name> <new_col_name> <data_type> NOT NULL;

Example:
mysql> ALTER TABLE test CHANGE create_date update_date DATETIME NOT NULL;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Add New column after existing column

Syntax:
ALTER TABLE <table_name> ADD COLUMN <new_col_name> varchar(20) NOT NULL AFTER <old_col_name>; 

Example:
mysql> ALTER TABLE test ADD COLUMN address varchar(20) NOT NULL AFTER id;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verify the table

mysql> desc test;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int         | YES  |     | NULL    |       |
| address     | varchar(20) | NO   |     | NULL    |       |
| name        | varchar(10) | YES  |     | NULL    |       |
| update_date | datetime    | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)