How to add a column in a table in MySQL?

To add a column in a table in MySQL, we can use ALTER command with add column command. First, let us create a table with columns Id and Name. After that, we will add column name Age and Address with the help of ALTER command.

The following is the query to create a table.

mysql> create table AddingColumnNameDemo
   -> (
   -> Id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.65 sec)

We can check information about the table with the help of DESC command.

The query is as follows −

mysql> desc AddingColumnNameDemo;

Here is the output.

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Id    | int(11)      | YES  |     | NULL    |       |
| Name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

As you can see above, there are only two columns.

The following is the syntax to add column Age and Address with the help of ALTER command.

alter table yourTableName
add column yourColumnName1 dataType,
add column yourColumnName2 dataType,
.
.
add column yourColumnNameN dataType,

Apply the above syntax in the following query to add more than one columns into the table.

mysql> alter table AddingColumnNameDemo
   -> add column Age int,
   -> add column Address varchar(300);
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

We have added two more column names (Age, Address) successfully. Let us now check with the help of DESC command. The following is the query.

mysql> DESC AddingColumnNameDemo;

Here is the output.

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| Id      | int(11)      | YES  |     | NULL    |       |
| Name    | varchar(100) | YES  |     | NULL    |       |
| Age     | int(11)      | YES  |     | NULL    |       |
| Address | varchar(300) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Chandu yadav

Published on 30-Nov-2018 15:45:55