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.
Headings of Contents
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 which contains the columns which you want to remove with ALTER TABLE command. Use column_name with DROP COLUMN statement to delete the column.
Note:- 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 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
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 been seen all 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