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
database_namewhile 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 TABLEstatement. 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 * 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:
mysql> SELECT * FROM courses; +-----+---------------+ | id | course_name | +-----+---------------+ | 101 | MySQL | | 102 | Oracle | | 103 | MS SQL Server | | 104 | Teradata | +-----+---------------+ 4 rows in set (0.00 sec)
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.