Drop A DataBase
PHP uses mysqli query() or mysql_query() function to drop a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)
| Sr.No. | Parameter & Description |
|---|---|
| 1 | $sql Required – SQL query to drop a MySQL database. |
| 2 | $resultmode Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
Try the following example to drop a database −
Copy and paste the following example as mysql_example.php −
<html> <head><title>Dropping MySQL Database</title></head> <body> <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = '[email protected]'; $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 TUTORIALS dropped successfully.<br />"); } if ($mysqli->errno) { printf("Could not drop database: %s<br />", $mysqli->error); } $mysqli->close(); ?> </body> </html>
Output
Access the mysql_example.php deployed on apache web server and verify the output.
Connected successfully. Database TUTORIALS dropped successfully.
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax
DROP DATABASE databasename;Note: Be careful before dropping a database. Deleting a database will result in loss of complete information stored in the database!
DROP DATABASE Example
The following SQL statement drops the existing database “testDB”:
Example
DROP DATABASE testDB;Tip: Make sure you have admin privilege before dropping any database. Once a database is dropped, you can check it in the list of databases with the following SQL command: SHOW DATABASES;
Introduction to the MySQL DROP DATABASE statement
The DROP DATABASE statement drops all tables in the database and deletes the database permanently. Therefore, you need to be very careful when using this statement.
The following shows the syntax of the DROP DATABASE statement:
DROP DATABASE [IF EXISTS] database_name;Code language: SQL (Structured Query Language) (sql)
In this statement, you specify the name of the database which you want to delete after the DROP DATABASE keywords.
If you drop a database that does not exist, MySQL will issue an error.
To prevent an error from occurring if you delete a non-existing database, you can use the IF EXISTS option. In this case, MySQL will terminate the statement without issuing any error.
The DROP DATABASE statement returns the number of tables it deleted.
In MySQL, the schema is the synonym for the database. Therefore, you can use them interchangeably:
DROP SCHEMA [IF EXISTS] database_name;Code language: SQL (Structured Query Language) (sql)
In the next section, we will use the testdb and testdb2 created in the CREATE DATABASE tutorial. If you do not have these databases available, you can follow the previous tutorial to create them.
MySQL DROP DATABASE using mysql program example
First, log in to the MySQL Server using the root user:
mysql -u root -pCode language: Shell Session (shell)
Type the password for the root user and press Enter.
Enter password: ********Code language: Shell Session (shell)
Second, display all the databases using the SHOW DATABASES statement:
SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)
Output:
+--------------------+
| Database |
+--------------------+
| classicmodels |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb2 |
+--------------------+
7 rows in set (0.00 sec)Code language: Shell Session (shell)
Third, drop the testdb database by issuing the DROP DATABASE statement:
DROP DATABASE testdb;Code language: SQL (Structured Query Language) (sql)
Output:
Query OK, 0 rows affected (0.03 sec)
