MySQL DROP TABLE

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 or database_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.

mysql drop table

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.

mysql drop table

If we try to drop a table that does not exist then MySQL issue an error as below.

mysql drop table

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.

mysql drop table

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.