How to use alter table in sql with query

 

SUMMARY

In this tutorial we will learn about alter table in sql. 

WHAT IS  ALTER TABLE

Generally alter table  is used to add column ,rename table ,drop column and modify data type of a column.Moreover any modification will be done after creating a table using alter table command.

PREREQUISITE 

First we need to  login our mysql server using the below command.

$mysql - u root -p

Now you need to create mysql database and table .Then check all database list and select our prefered database. To show database list we will use the below command.

mysql>show databases;

 you select your database as per your requirement. In our case we select techrideradmin database . Then select the table list within the database. In our case we select department table. Now fetch all records form the table.

HOW TO USE ALTER TABLE  WITH EXAMPLE

We have some queries about alter table in sql . These are given below.

1) Now we want to add city column into dept table ,we will  use alter table statement .

mysql>alter table dept add city varchar (30);

Here it was an example of alter table add column .you can add multiple columns using alter table statement.

2) Now we want to drop city column uisng alter table statement .

mysql>alter table dept drop column city;

Here it was an example of drop column using alter table statement. 

3) Now we want to rename dept table to department using alter table query.

mysql>alter table dept rename to department;

Using the above statement of alter table we can rename table name if it is required. 

4) Now we can change datatype of SAL column from int to varchar using alter table query.

mysql>alter table dept modify column SAL varchar (10);

From the above statement of alter table we can modify datatype of particular column.

CONCLUSION 

In this above article  we have learned about alter table in sql. 

SHARE

Admin

• 
• 
• 
• 
•