How to add a column in a table in MySQL?

MySQL Database Training for Beginners

39 Lectures 5.5 hours

Simon Sez IT

More Detail

Python programming with MySQL database: from Scratch

152 Lectures 16 hours

Metla Sudha Sekhar

More Detail

Learn MySQL from scratch for Data Science and Analytics

87 Lectures 5.5 hours

Metla Sudha Sekhar

More Detail

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

Updated on 30-Jul-2019 22:30:23

• Related Questions & Answers
• How to add a column to a MySQL table in Python?
• Can we add a column to a table from another table in MySQL?
• How to add subtotal to a table column displaying NULL in MySQL?
• How to add a column using MySQL SELECT in an already created table?
• Add a single year to all the date records in a MySQL table column
• How to delete a column from a table in MySQL?
• How can we add a time interval to date stored in a column of MySQL table?
• How to add a NOT NULL column in MySQL?
• How to substring value in a MySQL table column?
• How to add a “created at” column in a table to set the timestamp in MySQL?
• How to check if a column exist in a MySQL table?
• Add 30 days to a value in a MySQL table?
• How do I add a check constraint to a table in MySQL?
• Add leading zeros to a MySQL column?
• How to rename a column in an existing MySQL table?