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;

Leave a Reply to This Post

Your email address will not be published. Required fields are marked *

*

You may also like

Latest From 360 KLP Blog

Posted In: Tour and Travel

No matter, where you go, but reaching your destination on time is important. If not you reach your destination on time, you will have to face the issues according to the purpose you go for that destination. For example, if you are going to ai...

Posted In: Clothing

It is true that, most of us do not find time to wash our clothes as we are so busy in office and home. As far the cafes, hotels, salons and other offices are concerned, they need to definitely hire the laundry service to wash the clothes, as ...