ALTER QUERY MYSQL

Alter query is user to change the structure of a database table if it has been created and though it has some data .The alter table gives us the facility to delete,add,rename columns and create/destroy indexes as well as change the data type of already existing table.

Adding Column with ALTER TABLE STATEMENT

The syntax to alter table is :ALTER TABLE tablename ADD COLUMN colname coltype;

The result of the query add column to the end of the table means as a last column its default case. If you want to add the column as first column use “FIRST ” while using add new column to the begining of the table. If you wish to add the new column to next to particular column use “AFTER colname”. See the example below.

ALTER TABLE employee ADD COLUMN Address varchar (200);(in default case)
ALTER TABLE employee ADD COLUMN Adress varchar(200) FIRST;(add as first column)
ALTER TABLE employee ADD COLUMN Adress varchar(200) AFTER age;(add after particular column)

ADD UNIQUE USE:-

If you want a column to be added only if there is no other column already exist in the table with same name use “ADD UNIQUE” in place of ADD COLUMN so that no dublicate column name is added.

Deleting Column using ALTER TABLE STATEMENT

The syntex of deleting existing column is :ALTER TABLE table name DROP COLUMN colname.

If there is data in that particular Drop column that will also be deleted as well.

See the example:-
ALTER TABLE employee DROP COLUMN Address.

Renaming the table with ALTER TABLE STATEMENT

To rename the whole table we use ALTER TABLE . The syntex is :
ALTER TABLE employee RENAME to newtable name;

CHANGE TABLE NAME wth ALTER TABLE STATEMENT

IF you want to change the name of a column the syntex is : ALTER TABLE tablename CHANGE oldcolname newcolname coltype;

ALTER TABLE employee CHANGE Adress permanentadress text;

Changing/modifying the data type of cloumn data with ALTER TABLE STATEMENT

To change /modify the type of a column,the syntax is: ALTER TABLE tablename MODIFY colname coltype;