Saturday, May 28, 2022

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;