[MySQL Tutorial 7] Altering Table

In the previous MySQL tutorial, you learnt how to insert values inside the table. In this tutorial, you will come to know how to make changes in the existing table.

To make changes in the table, alter command is used:

Alter table table_name your_query;

Suppose you want to add another column to an existing table, use alter command.

Alter table employee add column column_name data type;

For example,

Alter table employee add name varchar(10);

Making changes in the table using PHP:

<?php
$link=mysql_connect('localhost','root','your_password');
mysql_select_db('mysql');
echo "connected to mysql";
$r=mysql_query(‘Alter table employee add name varchar(10)’);
If($r)
{
echo "column added";
}
Else
{
Echo “not added”;
}
?>

The output is as below:

To add multiple columns, the syntax is:

Alter table table_name add column column1 data type, add column2 data type;

Example,

Alter table employee add column name varchar(10), add column salary int(5);

Do this using PHP as follows:

<?php
$link=mysql_connect('localhost','root','your password');
mysql_select_db('mysql');
echo "connected to mysql"."<br>";
if($q=mysql_query('alter table employee add column name varchar(10), add column salary int(5)'))
{
	echo "columns added";
}
else
{
	echo "not added";
}
?>

The output is:

In the similar way, you can delete a column from the table with “drop” command as follows:

Alter table table_name drop column_name;

Use the select command to see whether the column exists now or not.

For example,

Alter table employee drop name;

Using PHP:

<?php
$link=mysql_connect('localhost','root','your_password');
mysql_select_db('mysql');
echo "connected to mysql"."<br>";
if($q=mysql_query('alter table employee drop name'))
{
	echo "column dropped";
}
else
{
	echo "not dropped";
}
?>

The output is as follows:

You can also delete more than one columns on separating them by comma as follows:

Alter table table_name drop column1, drop column2;

Example:

Alter table employee drop name, drop salary;

[Note: All the columns of a table cannot be deleted at once using “drop command“, you can drop the table instead]

If you want to change only the name of the column and not the values inside it, it can be done as follows:

Alter table table_name change old_name new_name data type;

Example,

Alter table employee change salary compensation int(10);

The output is:

Using PHP:

<?php
$link=mysql_connect('localhost','root','your_password');
mysql_select_db('mysql');
echo "connected to mysql<br>";
$r=mysql_query('alter table employee change compensation salary int(10)');
if($r)
{
echo "column name changed";
}
else
{
echo "not changed";
}
?>

The output is:

Now that you know how to add and delete a column in the existing table, continue with the next MySQL tutorial on how to make changes in the values inside the existing table. Also, if you have any queries, please  leave a comment below.