MySQL Drop Table Statement

In this tutorial, you will learn about the MySQL DROP Table statement to drop the table from the MySQL database. Here we will see how to drop single and multiple columns from the MySQL database.

MySQL Drop Table Statement Introduction

MySQL provides a MySQL DROP TABLE statement to drop single or multiple tables from a database.

Syntax

The basic syntax of MySQL DROP TABLE statement.

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

let’s break down the above syntax.

• The DROP TABLE statement removes the table along with their data permanently from the MySQL database.it also allows us to remove more than one table simultaneously.
• The TEMPORARY option allows us to remove only a temporary table.it ensures that you are deleting only temporary tables.
• IF EXISTS conditionally drop a table if a table exists. If you are trying to delete a non-existing table, MySQL raises a note which can be retrieved using the show warning statement.
• table_name represents the table name you want to delete, you can remove multiple tables. All the table names are separated by commas.

MySQL DROP TABLE Examples

MySQL DROP TABLE allows us to delete single and multiple columns. Here we will see all the examples one by one.

To delete only the table in MySQL, you have to assign one single table name with the MYSQL DROP TABLE statement.

First, we will create a table students table. This table is only for testing purposes.

Example: Create a Table in MySQL

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

Click here to learn the MySQL CREATE TABLE statement.

Example:- Drop a table in MySQL

Now, we will drop the students table using the DROP TABLE statement.

DROP TABLE students;

How to drop multiple tables in MySQL ?

To delete multiple columns, you need to assign all the table names separated by commas.

First of all, we will create three tables students1, students2, and students3 for testing purpose.

Create the first table:

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

Create the second table:

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

Create the third table:

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

You can use SHOW DATABASES to see all the created databases.

Example:- Drop multiple tables in MySQL

Now, we will drop all the tables which have been created above using the DROP TABLE statement.

DROP TABLE students1, students2, students3;

Again, You can use SHOW DATABASES to see the database.

Delete non-existing table using MYSQL DROP TABLE statement

When you are trying to delete a non-existing table using the MYSQL DROP TABLE statement, MySQL generates a note.

Let see what happens when we are trying to delete the non-existing table.

Example: Delete a non-existing table in MySQL

DROP TABLE students4;

MySQL issue the following note:

ERROR 1051 (42S02): Unknown table 'college.students4'

Conclusion

So, in this article, we have been seen all about how to delete single and multiple tables using MySQL DROP TABLE statement. This MySQL command is very useful when you want to delete one or more than one table from the MySQL database table.

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

Reference:- Click Here

Thanks for your valuable time …