MySQL Alter Command is used to change the structure of existing table . we can use Alter Command add or drop column, change column attribute, add primary key, rename table,etc.
ALTER TABLE tablename action1 [,action2 ,...]
To add a column in a table.
ALTER TABLE example ADD employee_id INT(11) NOT NULL AUTO_INCREMENT;
To delete a column in a table.
ALTER TABLE example DROP COLUMN employee_name
To change the data type of a column in a table.
ALTER TABLE example CHANGE COLUMN employee_name employee_name INT(11) NOT NULL
To drop a column from table.
ALTER TABLE example DROP COLUMN employee_name
To rename a column in a table.
ALTER TABLE example CHANGE COLUMN employee_name employeename varchar(20) NOT NULL;
To rename table.
ALTER TABLE example RENAME TO exampledesc;
To add a new TIMESTAMP column in table.
ALTER TABLE example ADD employee_attendance TIMESTAMP ;
To add an index on column emp_first_name and UNIQUE index on column emp_last_name.
ALTER TABLE example ADD INDEX (emp_first_name), ADD UNIQUE (emp_last_name);
To add PRIMARY KEY in a column .
ALTER TABLE example ADD PRIMARY KEY(employee_id)
ALTER COLUMN is used to set or remove the default value for a column.
ALTER TABLE example ALTER COLUMN employee_name SET DEFAULT 'Manish';
ALTER TABLE example ALTER COLUMN employee_name DROP DEFAULT;
CHANGE COLUMN
CHANGE COLUMN is used to rename a column, change its datatype, or move it within the schema.
ALTER TABLE example CHANGE COLUMN employee_name employee_name INT(11) NOT NULL;
MODIFY COLUMN
MODIFY COLUMN used to do everything CHANGE COLUMN can, but without renaming the column.
ALTER TABLE example MODIFY COLUMN employee_name employee_name INT(11) NOT NULL;
Here is a table reference for ALTER vs CHANGE vs MODIFY COLUMN.
| Method | DROP Default | Change Default | Rename Column | Change Datatype |
|---|---|---|---|---|
| ALTER | Yes | Yes | NO | No |
| CHANGE | NO | Yes | Yes | Yes |
| MODIFY | NO | Yes | NO | Yes |
