MariaDB Drop Table

Summary: in this tutorial, you will learn how to use the MariaDB drop table statement to remove a table from a database.

Introduction to MariaDB drop table statement

The drop table statement removes a table from a database. The following shows the syntax of the drop table statement:

drop table [if exists] table_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the table that you want to remove after the drop table keywords.

The if exists option conditionally drops the table only if it exists.

To execute the drop table statement successfully, you need to have the drop privilege for the table that you want to drop.

To drop multiple tables at once, you use the following syntax:

drop table [if exists]
    table1,
    table2,
    ...;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify a comma-list of tables that you want to remove after the drop table keywords.

It is important to note that when you drop a table, MariaDB will retain all user privileges on the removed table. In case you create a new table with the same name as the table that you dropped, all the privileges on the dropped table will apply to the new table. It could be a potential security risk that you should be aware of.

MariaDB drop table statement example

Let’s take some examples of using the drop table statement.

A) Using MariaDB drop table to drop one table

First, create a new table called cities for the testing purpose:

create table cities(
    id int primary key,
    city_name varchar(100) not null,
    area decimal(15,2)
);
Code language: SQL (Structured Query Language) (sql)

Second, drop the cities table using the drop table statement:

drop table cities;
Code language: SQL (Structured Query Language) (sql)

B) Using MariaDB drop table to drop multiple tables

First, create two tables called mountains and oceans:

create table mountains(
    id int primary key,
    mountain_name varchar(100) not null,
    height decimal(15,2)
);

create table oceans(
    id int primary key,
    ocean_name varchar(100) not null,
    square decimal(15,2)
)
Code language: SQL (Structured Query Language) (sql)

Second, use the drop table statement to remove both mountains and oceans at once:

drop table mountains, oceans;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you learned how to use the MariaDB drop table statement to remove one or more tables from a database.