Summary: in this tutorial, you will learn how to drop a table using the MySQL DROP TABLE
statement with syntax and examples.
MySQL allows us to drop a table from the database using the MySQL DROP TABLE
statement. This statement deletes the table structure along with the data completely from MySQL. So, we need to be very careful before executing this command as the wrong table name provided to the statement can lead to the loss of data.
MySQL DROP TABLE Syntax
The following are the syntax of dropping a table from MySQL.
mysql> DROP TABLE table_name; OR, mysql> DROP TABLE schema_name.table_name;
Here is the more generic syntax of the drop table command.
DROP [ TEMPORARY ] TABLE [ IF EXISTS ] table_name [ RESTRICT | CASCADE ];
In this syntax,
- table_name – the name of the table that you need to drop.
- schema_name.table_name – you can optionally specify the
schema_name
ordatabase_name
while dropping a table. It is a good practice to use the schema name along with the table name as this ensures that the table dropped from the desired schema only. - TEMPORARY – optional parameter. This ensures that only the temporary table will be deleted.
- IF EXISTS – If the table does not exist, MySQL issue an error. So, it is better to use this optional parameter along with the
DROP TABLE
statement. In this case, MySQL will not issue any error, simply it will not drop any table if the table does not exist. - RESTRICT and CASCADE – These optional parameters are reserved for the future release of MySQL.
MySQL DROP TABLE Example
Let us check the different options of the MySQL DROP TABLE
command using a few examples.
1) Dropping a Single Table
In this example, we will check an example of dropping a single table from the MySQL database. Suppose we have a table named Products
in the mysqltutorial
database as below.
To drop this Products
table, you use the following statement.
DROP TABLE Products;
The above statement removes the Products
table permanently from the database. Let’s verify using the SELECT
statement.
SELECT * FROM Products;
This will give the following output.
If we try to drop a table that does not exist then MySQL issue an error as below.
However, if we use the IF EXISTS
keywords before mentioning the table_name
in the DROP TABLE
statement, MySQL does not issue any error. You can see from the below output.
2) Dropping Multiple Tables
In this example, we will check how to drop multiple tables using a single DROP TABLE
statement. Suppose, we have two tables named courses and enrolls as follows:
Table: courses
mysql> SELECT * FROM courses; +-----+---------------+ | id | course_name | +-----+---------------+ | 101 | MySQL | | 102 | Oracle | | 103 | MS SQL Server | | 104 | Teradata | +-----+---------------+ 4 rows in set (0.00 sec)
Table: enrolls
mysql> SELECT * FROM enrolls; +----+-----------+ | id | course_id | +----+-----------+ | 1 | 101 | | 2 | 101 | | 3 | 102 | | 4 | 102 | +----+-----------+ 4 rows in set (0.00 sec)
To delete multiple tables in a single statement, you use the following statement.
DROP TABLE courses, enrolls;
3) Dropping Temporary Table
Let us now check the DROP TABLE
statement for the temporary table. Let’s create a temporary table first.
CREATE TEMPORARY TABLE Student_marks( roll_no INT PRIMARY KEY NOT NULL, name varchar(100) NOT NULL, total_marks DECIMAL(10,2) NOT NULL DEFAULT 0.00, total_subjects INT NOT NULL DEFAULT 0);
To drop a temporary table, you use the following syntax.
DROP TEMPORARY TABLE Student_marks;
The above statement deletes the Student_marks
table from MySQL.
Please note that the above statement will delete only the temporary table. If a permanent table exists with the same name as the temporary table, MySQL will not delete the permanent table.