MariaDB-Drop Database

MariaDB-Drop Database

In MariaDB to perform the operations like creating and deleting of databases we need privileges usually privileges are provided to root users or admins.

As we can create the database, we can also delete them. To do so, we use DROP DATABASE command in MariaDB. This command allows us to delete all the tables from the particular database and then drops the database. 

To perform this operation, we need DROP privilege on the database.

When we drop a database, the user privileges applied on the database are not innately deleted.

Emphasize that deleted databases are unrecoverable.

Syntax:

DROP DATABASE  database_name;

OR

 DROP SCHEMA database_name;
 DROP DATABASE command is equivalent to DROP SCHEME. 

For Example:

 MariaDB [(none)]> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | db1                |
 | db2                |
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 9 rows in set (0.022 sec)
 MariaDB [(none)]> DROP DATABASE db1;
 Query OK, 0 rows affected (0.726 sec)
 MariaDB [(none)]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | db2                |
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 8 rows in set (0.001 sec) 

In the above example, using the SHOW DATABASES command, we can view the existing databases which shows default databases as well as user created databases. So here we are going to delete the database named db1 using DROP DATABASE command. Then we can see that database db1 is deleted as we cannot see it in the list.

Example 2:

We are dropping a database using SCHEMA keyword. As mentioned earlier we can also drop database using DROP SCHEMA command.

 MariaDB [(none)]> DROP SCHEMA db2;
 Query OK, 0 rows affected (0.002 sec)
 MariaDB [(none)]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 7 rows in set (0.001 sec) 

IF EXISTS

We can get an error if we attempt to drop a database that is not present in the database to stop this; we can use IF EXISTS. When we are using IF EXISTS, the note is produced for non-existing.

Syntax:

DROP DATABASE IF EXISTS database_name;

For Example:

Initially we will create a database named “Sales” by using CREATE DATABASE command in MariaDB.

Using SHOW DATABASES we are viewing the databases list which includes our newly created database-sales.

 MariaDB [(none)]> CREATE DATABASE SALES;
 Query OK, 1 row affected (0.002 sec)
 MariaDB [(none)]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | db2                |
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | sales              |
 | student            |
 | test               |
 | university         |
 +--------------------+
 9 rows in set (0.029 sec) 

Next We will drop that database using DROP DATABASE command in MariaDB that deletes the database mentioned in query.

Further we try to delete the database sales again which returns an error which says can’t drop the database, database doesn’t exist

 MariaDB [(none)]> DROP DATABASE SALES;
 Query OK, 0 rows affected (0.808 sec)
 MariaDB [(none)]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | db2                |
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 8 rows in set (0.001 sec)
 MariaDB [(none)]> DROP DATABASE SALES;
 ERROR 1008 (HY000): Can't drop database 'sales'; database doesn't exist 

To avoid this error, we use IF EXISTS. It does not return an error when we try to delete the already dropped database. MariaDB generates a note.

We can use ‘/W’ to enable the viewing of waning

Note that if warnings have enabled before using the same command /W then using it again would disable warning’s view. To enable it again we again need to execute the command ‘\w’ which shows the notes and warning returned by MariaDB

 MariaDB [(none)]> \W
 Show warnings enabled.
 MariaDB [(none)]> DROP DATABASE IF EXISTS SALES;
 Query OK, 0 rows affected, 1 warning (0.001 sec)
 Note (Code 1008): Can't drop database 'sales'; database doesn't exist
 MariaDB [(none)]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | db2                |
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 8 rows in set (0.001 sec)
 As we can see the database is deleted