MySQL - Drop Database

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 −

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.

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 −

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

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

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

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

Example

Following are the programs −

$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();     

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...!")
    });
});

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();
		}
	}
}

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()

Database dropped Successfully