MySQL MariaDB

Delete/Drop a Table in MySQL

MySQL is a relational database management system that provides quick and reliable solutions. It is well-known for its ability to execute quickly and for its unique and straightforward user experience. Performing CRUD operations are the core operations and basic concepts when working with databases. In this article, you will learn how to delete a table in a database.

Before learning more about the deletion of tables using MySQL, be sure that you have the latest version of MySQL installed on your computer. Also, ensure that you have a database and a table in it that you want to delete. In this article, we assume that you understand the basic concepts of MySQL statements and that you have a database and table in MySQL that you would like to delete.

You can figure out the version of MySQL running on your system by running the ‘mysql -V’ command:

mysql -V

You can now move forward knowing that you have the latest version installed.

To figure out whether MySQL is working properly, run the following command:

sudo systemctl status mysql

If the service is not running, then you can activate the service using the command below:

sudo systemctl start mysql

After starting it, connect to the MySQL server as a root user with superuser privileges using sudo. Otherwise, you can enter a custom username instead of the root username.

The following steps show the table deletion process for MySQL servers in the command-line terminal.

sudo mysql -u root -p

After entering the MySQL shell, list the databases and choose the database from which you want to delete a table.

Select the correct database by running the USE statement with the database name.

USE database_name;

After choosing the database from the list, choose the table, as well. To see a list of the tables in the database, run the SHOW TABLES command:

Now, choose the table that you would like to delete. To delete the table, run the “DROP TABLE” command and provide a table name, for example:

DROP TABLE table_name;

If you are unable to delete or drop a table, make sure that you have the correct privileges for that table. If you do not have the privileges issue but are still getting an error when attempting to delete a table, then you may be trying to delete a nonexistent table, or there could be a spelling mistake. To avoid this error, MySQL provides the “IF EXISTS” clause. If you use this clause, MySQL will not throw any errors if no table exists of the given name in the query in the database. The “IF EXISTS” clause has a specific syntax that needs to be followed, shown below:

DROP DATABASE IF EXISTS database_name;

Conclusion

This article includes two different methods of deleting an existing table in a MySQL database, both with and without using the “IF EXISTS” clause. The article also described the difference between these two methods for your convenience.

About the author

Shehroz Azam

Shehroz Azam

A Javascript Developer & Linux enthusiast with 4 years of industrial experience and proven know-how to combine creative and usability viewpoints resulting in world-class web applications. I have experience working with Vue, React & Node.js & currently working on article writing and video creation.