MySQL - DROP Table

In this tutorial we will learn to list tables and drop i.e., delete tables in MySQL.

In the previous tutorial we learned how to create tables in MySQL. Feel free to check that out.

For this tutorial we will use the mysql_project database that we created in the database tutorial.

Open the terminal and login to MySQL server and type the following command to use the database.

mysql> USE mysql_project;
Database changed

Show all the tables

To show all the tables in a database we use the SHOW TABLES; command.

If we run the command in the terminal we will get the following output.

+-------------------------+
| Tables_in_mysql_project |
+-------------------------+
| comments                |
| employee                |
+-------------------------+
2 rows in set (0.00 sec)

Show the create statement of a table

To get the create statement of a table we use the SHOW CREATE TABLE table_name; command.

If we run the SHOW CREATE TABLE employee; command in the terminal we will get the following output.

mysql> SHOW CREATE TABLE employee;
+----------+-----------------------------------------------+
| Table    | Create Table                                  |
+----------+-----------------------------------------------+
| employee | CREATE TABLE `employee` (
  `employeeid` varchar(20) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `score` int(11) DEFAULT '0',
  `lastmodified` datetime DEFAULT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`employeeid`),
  UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8                       |
+----------+-----------------------------------------------+
1 row in set (0.00 sec)

We get the name of the table in the Table column and the create table statement in the Create Table column.

Rename a table

To rename a table we use the RENAME TABLE old_table_name TO new_table_name; command.

mysql> RENAME TABLE temp TO temporary;
Query OK, 0 rows affected (0.00 sec)

Drop a table

If we want to drop/delete a table from a database we use the DROP TABLE table_name; command.

mysql> DROP TABLE temp;
Query OK, 0 rows affected (0.01 sec)

We can even check if the table exists by adding IF EXISTS.

mysql> DROP TABLE IF EXISTS temp;
Query OK, 0 rows affected (0.01 sec)