MySQL Drop Database

When using a regular user to log in to the MySQL server, you may need specific permissions to create or delete MySQL databases, so here we use the root user to log in, and the root user has the highest privileges.

When deleting a database, you must be very careful, because after executing the delete command, all data will disappear.

Drop command to delete database

The drop command format:

DROP DATABASE <database_name>;        -- 直接删除数据库，不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;

Parameter description:

  • IF EXISTSIt is an optional clause, indicating that the delete operation is executed only if the database exists, to avoid errors caused by the database not existing.
  • database_nameThis is the name of the database you want to delete.

For example, to delete a database named EXAMPLE:

-- 直接删除数据库，不检查是否存在
mysql> DROP DATABASE EXAMPLE;

-- 删除数据库，如果存在的话
DROP DATABASE IF EXISTS EXAMPLE;

Note:Before performing the database deletion operation, please make sure you really want to delete the database and all its data, because this operation is irreversible. To avoid accidental deletion, it is usually recommended to back up the database before executing the deletion.

Use mysqladmin to delete database

You can also use the MySQLmysqladmincommand in the terminal to execute the delete command.

The following is the command to delete a database using mysqladmin:

mysqladmin -u your_username -p drop your_database

your_usernameIt is the MySQL username,your_databaseIt is the name of the database to be deleted.

After executing this command, the system will prompt you to enter a password. After entering the password, press the Enter key to delete the database.

The following example deletes the database EXAMPLE (this database was created in the previous chapter):

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

After executing the above database deletion command, a prompt box will appear to confirm whether you really want to delete the database:

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 'EXAMPLE' database [y/N] y
Database "EXAMPLE" dropped

Use PHP script to delete database

PHP uses the mysqli_query function to create or delete MySQL databases.

This function has two parameters, and returns TRUE when execution succeeds, otherwise it returns FALSE.

Syntax

mysqli_query(connection,query,resultmode);
Parameters Description
connection Required. Specifies the MySQL connection to use.
query Required. Specifies the query string.
resultmode

Optional. A constant. It can be any of the following values:

  • MYSQLI_USE_RESULT (use this if you need to retrieve a large amount of data)
  • MYSQLI_STORE_RESULT (default)

Examples

The following example demonstrates using the PHP mysqli_query function to delete a database:

Drop Database

<?php $dbhost = 'localhost'; //MySQL server host address $dbuser = 'root'; //MySQL username $dbpass = '123456'; //MySQL user password $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Connection failed:' . mysqli_error($conn)); } echo 'Connection successful<br />'; $sql = 'DROP DATABASE EXAMPLE'; $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('Failed to delete database:' . mysqli_error($conn)); } echo "Database EXAMPLE deleted successfully\n"; mysqli_close($conn); ?>

After successful execution, the output result is:

Note:When using a PHP script to delete a database, there will be no confirmation message about whether to delete it; the specified database will be deleted directly, so you must be especially careful when deleting a database.

Other extensions