MySQL ALTER TABLE Statement

Add new column in table

ALTER TABLE employee ADD email_id varchar(40) NOT NULL  AFTER last_name;

Add multiple columns in table

ALTER TABLE employee  
	ADD mobile varchar(40) NOT NULL  AFTER email_id, 
	 ADD address varchar(35) NULL  AFTER mobile;

Add multiple columns in table without using After : This will add columns at the end of table.

mysql> ALTER TABLE employee ADD mobile varchar(40) NOT NULL,  ADD address varchar(35) NULL;

Modify column in table : We can modify datatype and definition of the column (NULL or NOT NULL, etc). like in this example we are modify address varchar(30) to address varchar(200)

mysql> ALTER TABLE employee MODIFY address varchar(200) NULL;

Modify Multiple columns in table

ALTER TABLE employee
  MODIFY last_name varchar(55) NULL,
  MODIFY first_name varchar(30) NOT NULL;

Rename column name in table

mysql> ALTER TABLE employee CHANGE COLUMN email_id emailId varchar(20) NOT NULL;

 MYSQL DROP COLUMN

mysql> ALTER TABLE employee DROP COLUMN occupation;

Rename table name

mysql> ALTER TABLE employee  RENAME TO employees_tbl;