MySQL – Drop Database
Table of content
- MySQL DROP Database Statement
- Dropping a Database using mysqladmin
- Dropping Database Using a Client Program
”;
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 −
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 −
$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
”;