MySQL - Drop Database

Table of content

MySQL DROP Database Statement
Dropping a Database using mysqladmin
Dropping Database Using a Client Program

Quiz

MySQL DROP Database Statement

The DROP DATABASE statement in MySQL is used to delete a database along with all the data such as tables, views, indexes, stored procedures, and constraints.

While deleting an existing database −

It is important to make sure that we have to perform the backup of the database that we are going to delete because once the "DROP DATABASE" statement is executed, all the data and database objects in the database will be permanently deleted and cannot be recovered.
It is also important to ensure that no other user or application is currently connected to the database that we want to delete. If we try to delete the database while others users are connected to it, then it can cause data corruption or other issues.

In addition to these we need to make sure we have the necessary privileges before deleting any database using the DROP DATABASE statement.

Syntax

Following is the syntax to delete a database in MySQL −

DROP DATABASE DatabaseName;

Here, the "DatabaseName" is the name of the database that we want to delete.

Example

First of all, let us create a database named TUTORIALS into database system using the following query −

CREATE DATABASE TUTORIALS;

Once the database is created, execute the following query to verify whether it is created or not −

SHOW DATABASES;

As we can see the list of databases below, the TUTORIALS database has been created successfully −

Database
information_schema
mysql
performance_schema
tutorials

Now, let us delete the existing database <TUTORIALS> using the following DROP DATABASE statement −

DROP DATABASE TUTORIALS;

Output

On executing the given query, the output is displayed as follows −

Query OK, 0 rows affected (0.01 sec)

Verification

Once we have deleted the TUTORIALS database, we can verify whether it is deleted or not using the following query −

SHOW DATABASES;

As we can in the output, the database has been deleted successfully.

Database
information_schema
mysql
performance_schema

Dropping a Database using mysqladmin

You would need special privileges to create or to delete a MySQL database. So, assuming you have access to the root user, you can create any database using the mysql mysqladmin binary.

Note: Be careful while deleting any database because you will lose your all the data available in your database.

Example

Here is an example to delete a database(TUTORIALS) created in the previous chapter −

[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******

This will give you a warning and it will ask you to confirm (Y/N) that you really want to delete this database or not. If you enter 'y', the database will be deleted, else no −

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'TUTORIALS' database [y/N] y

Output

The TUTORIALS database has been deleted successfully.

Database "TUTORIALS" dropped

Dropping Database Using a Client Program

Besides using MySQL queries to perform the DROP DATABASE operation, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

PHP NodeJS Java Python

To drop a database through a PHP program, we need to execute the 'DROP DATABASE' statement using the mysqli function query() as follows −

$sql = "DROP DATABASE DatabaseName;";
$mysqli->query($sql);

To drop a database through a Node.js program, we need to execute the 'DROP DATABASE' statement using the query() function of the mysql2 library as follows −

sql = "DROP DATABASE DatabaseName;";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

To drop a database through a Java program, we need to execute the 'DROP DATABASE' statement using the JDBC function executeUpdate() as follows −

String sql = "DROP DATABASE DatabaseName;";
st.execute(sql);

To drop a database through a Python program, we need to execute the 'DROP DATABASE' statement using the execute() function of the MySQL Connector/Python as follows −

sql = "DROP DATABASE DatabaseName;"
cursorObj.execute(sql)

Example

Following are the programs −

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s<br />", $mysqli->connect_error);
   exit();
}
printf('Connected successfully.<br />');

if ($mysqli->query("Drop DATABASE TUTORIALS")) {
   printf("Database dropped successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not drop database: %s<br />", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Connected successfully.
Database dropped successfully.

var mysql = require('mysql2');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});
  //Connecting to MySQL
  con.connect(function (err){
   if (err) throw err;
   console.log("Connected!");
   console.log("--------------------------");
   //Creating a Database
   sql = "DROP DATABASE TUTORIALS"
   con.query(sql, function(err){
    if (err) throw err
    console.log("Database Dropped successfully...!")
   });
});

Output

The output produced is as follows −

Connected!
--------------------------
Database Dropped successfully...!

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DropDatabase {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/tutorials";
		String user = "root";
		String password = "password";
		ResultSet st;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st1 = con.createStatement();
            //System.out.println("Connected successfully...!");
            String sql = "DROP DATABASE TUTORIALS";
            st1.execute(sql);
            System.out.println("Database dropped successfully...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Output

The output obtained is as shown below −

Database dropped successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
host ="localhost",
user ="root",
password ="password"
)
# creating cursor object
cursorObj = connection.cursor()
# dropping the database 
cursorObj.execute("DROP DATABASE MySqlPython")
print("Database dropped Successfully")
# disconnecting from server
connection.close()

Output

Following is the output of the above code −

Database dropped Successfully

Print Page

Save or share this article