MySQL - DROP TABLE Statement



MySQL DROP TABLE Statement

You can drop/delete an existing table using the DROP TABLE Statement. Once you delete a table all records, triggers, constraints and permission of the table are lost.

Syntax

Following is the syntax of the DROP TABLE statement −

DROP TABLE [IF EXISTS] table_name ...

Where, table_name is the name of the table you need to delete.

Example

Suppose we have created tables as shown below −

CREATE TABLE TestTable1(value VARCHAR(10));
CREATE TABLE TestTable2(value VARCHAR(10));
CREATE TABLE TestTable3(value VARCHAR(10));
CREATE TABLE TestTable4(value VARCHAR(10));

If you verify the list of tables you can observe the created ones in the list as follows −

show tables;

Output

Following is the output of the above query −

Tables_in_sample
employee
sample
test
testtable1
testtable2
testtable3
testtable4

You can delete three of the above created tables using the DROP TABLE statement as shown below −

DROP TABLE testtable1; 
DROP TABLE testtable2;
DROP TABLE testtable3;

Once a table is dropped, if you verify the list of the tables as shown below you can see its name is missing −

show tables;

Output

The above mysql query generates the following output −

Tables_in_sample
employee
sample
test
testtable4

The IF EXISTS clause

If you try to drop a table that doesn’t exist error will be generated as shown below −

DROP TABLE demo;
ERROR 1051 (42S02): Unknown table 'sample.demo'

If you use the IF EXISTS clause along with the DROP TABLE statement as shown below, the specified table will be dropped and if a table with the given name, doesn’t exist the query will be ignored.

DROP TABLE IF EXISTS demo;
Advertisements