MySQL ADD COLUMN

In this article, you will learn everything about how to add a column in a table using the MySQL ADD COLUMN statement. In the previous tutorial, we have seen all about how to drop columns from the table.

MySQL ADD COLUMN Introduction

MySQL provides a command MySQL ADD COLUMN to add one or more columns into the database table.

Syntax

The basic syntax of MySQL ADD COLUMN statement is.

ALTER TABLE table_name 
ADD COLUMN column_name column_definition [First | After existing_colimn];

Let’s breakdown the above syntax:-

1. First, you have to specify the table_name after the ALTER TABLE statement.
2. Second, you have to keep the new column name along with column definition after ADD COLUMN statement.
3. MySQL allow us to add new column as the first column of the table using First keyword or add a new column after a existing column by specifying AFTER existing_column name.

To add multiple columns in MySQL Table, you can use the following statements.

ALTER TABLE table_name 
ADD COLUMN column_name column_definition [First | After existing_colimn],
ADD COLUMN column_name column_definition [First | After existing_colimn],
ADD COLUMN column_name column_definition [First | After existing_colimn];

Note:- COLUMN keyword in ADD COLUMN the statement is optional so you can avoid that.

MySQL ADD COLUMN Examples

To add one column or multiple columns in MySQL, we will create a sample database table named students.

Example: Create MySQL Table

CREATE TABLE students 
(
	st_id INT PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	roll_no INT,
	UNIQUE(roll_no)
);

Click here to learn more about the MySQL Create Table statement.

To check Table students are created or not, use the following command.

DESC students

Output

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| st_id   | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(100) | NO   |     | NULL    |       |
| roll_no | int(11)      | YES  | UNI | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.11 sec)

Now, it’s time to come add a new column to the table.

How to add a column in MySQL ?

To add a new column to the table, use the following command.

Example:- Add new column in MySQL Table:

ALTER TABLE students ADD COLUMN address VARCHAR(100) NOT NULL;

As you can see in the above example, we have been successfully added a new column address to the students table.

You can use DESC students statements to verify the changes.

How to add mutiple columns in MySQL

To add a new column to the table, use the following command.

Example

ALTER TABLE students 
ADD COLUMN course VARCHAR(50) NOT NULL,
ADD COLUMN college VARCHAR(100) NOT NULL;

As you can see in the above example, we have been successfully added multiple columns ( course and college ) to the students table.

You can use DESC students statements to verify the changes.

Conclusion

So in this article, you have been seen all about how to add columns in MySQL using MySQL ADD COLUMN statement. This is a very important command of MySQL, especially when you want to add a new column in your existing MySQL database table.

I hope this tutorial will help you, If you like this article, please share, and keep visiting for further MySQL tutorials.

Thanks for reading ….

Reference:- Click Here