Summary: in this tutorial, you will learn how to remove databases using SQL DROP DATABASE statement.
SQL provides DROP DATABASE
statement to allow you to remove existing databases. The SQL DROP DATABASE
statement deletes all tables, views, triggers, and other related objects inside a database, and also removes the database permanently.
The following illustrates the syntax of the statement:
DROP DATABASE database_name
Code language: SQL (Structured Query Language) (sql)
Some database management systems, such as Microsoft SQL Server or MySQL, allow you to drop multiple databases using a single DROP DATABASE
statement. To remove multiple databases, you specify a list of comma-separated database name after the keyword DROP DATABASE
as follows:
DROP DATABASE dbname1,dbname2,...
Code language: SQL (Structured Query Language) (sql)
SQL DROP DATABASE example
Let’s practice with an example to get familiar with the DROP DATABASE
statement.
First, create a new database for the practicing purpose. If you don’t know how to create a new database, check it out the creating a database tutorial.
CREATE DATABASE mydb;
Code language: SQL (Structured Query Language) (sql)
Second, connect to this database to confirm if it is created successfully.
USE mydb;
Code language: SQL (Structured Query Language) (sql)
Third, use the DROP DATABASE
to remove the mydb
database.
DROP DATABASE mydb
Code language: SQL (Structured Query Language) (sql)
Notes on SQL DROP DATABASE statement
There are some important points you should keep them in mind before using the DROP DATABASE
statement.
- You should execute the
DROP DATABASE
statement with extra cautious because when the database is removed, it cannot be undone. - To execute the
DROP DATABASE
you must have remove database privilege. Typically the database owner can remove the database. - Before removing the database, make sure that you communicate with stakeholders and shutdown connections that are connecting to the database.
For more information on removing databases in specific database management systems:
- MySQL – delete database in MySQL
- Microsoft SQL Server – remove database in Microsoft SQL Server
- Oracle – remove Oracle database
- PostgreSQL – delete PostgreSQL database
In this tutorial, we have shown you how to use the SQL DROP DATABASE statement to remove a database in a database server.