MySQL DROP COLUMN

In this tutorial, you will learn everything about MySQL DROP COLUMN statement to drop column in MySQL. Sometimes you need to drop one or more columns from the Mysql database.

MySQL DROP COLUMN Intoduction

Sometimes you want to remove one or more columns from the table, In that case, you can use ALTER TABLE DROP COLUMN statement.

Syntax

The basic syntax of DROP COLUMN is:-

ALTER TABLE table_name
DROP COLUMN column_name;

let’s break down the above syntax.

First, specify the name of the table that contains the columns that you want to remove with the ALTER TABLE command. Use column_name with the DROP COLUMN statement to delete the column.

Note:- The COLUMN keyword in the DROP COLUMN statement is optional, so you can above it and you can use DROP column_name instead of DROP COLUMN column_name.

How to drop multiple columns in MySQL

To drop the multiple columns in the MySQL database table, you can use the following command.

Syntax:

ALTER TABLE table_name
DROP column_name,
DROP column_name,
DROP column_name;

MySQL DROP COLUMN Example

To drop columns in MySQL, first, we will create a sample table so that you can understand it very well.

First, Create a table name for students using the MySQL CREATE TABLE statement.

Example: Create MySQL Table

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

Click here to learn more about the MySQL CREATE TABLE statement.

To check whether 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    |       |
| course  | varchar(40)  | NO   |     | NULL    |       |
| address | varchar(100) | NO   |     | NULL    |       |
| roll_no | int(11)      | YES  | UNI | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.11 sec)

How to drop a column in MySQL

To drop a single column in the MySQL database table, use the following command. Here we will delete the roll_no column from the students table.

Example: Drop a column in MySQL

ALTER TABLE students DROP roll_no;

You can use DESCRIBE students the command to verify the changes.

How to drop multiple columns in MySQL

To drop multiple columns from the MySQL database table, you have to use the following command. In this example, we are deleting course and address columns from students tables.

Example: Drop multiple columns in MySQL

ALTER TABLE students 
DROP course,
DROP roll_no;

You can use DESCRIBE students command to verify the changes.

Output

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

Conclusion

So, In this MySQL DROP COLUMN tutorial, we have seen all of how to drop single and multiple columns from the MySQL database table.

I hope you don’t have any confusion regarding the MySQL DROP COLUMN statement. If you like this article, please share and keep visiting for further MySQL Tutorials.

Thanks for reading ….

Reference:- Click Here