{"id":7356,"date":"2018-09-02T05:55:59","date_gmt":"2018-09-02T12:55:59","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7356"},"modified":"2023-10-23T01:45:09","modified_gmt":"2023-10-23T08:45:09","slug":"mysql-drop-database","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-drop-database\/","title":{"rendered":"MySQL DROP DATABASE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL DROP DATABASE<\/code> statement to delete an existing database in the server.<\/p>\n\n\n\n

Introduction to the MySQL DROP DATABASE statement<\/h2>\n\n\n\n

The DROP DATABASE<\/code> statement drops all tables in the database and deletes the database permanently. Therefore, you need to be very careful when using this statement.<\/p>\n\n\n\n

The following shows the syntax of the DROP DATABASE<\/code> statement:<\/p>\n\n\n

DROP<\/span> DATABASE<\/span> [IF<\/span> EXISTS<\/span>] database_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this statement, you specify the name of the database which you want to delete after the DROP DATABASE<\/code> keywords.<\/p>\n\n\n\n
If you drop a database that does not exist, MySQL will issue an error.<\/p>\n\n\n\n
To prevent an error from occurring if you delete a non-existing database, you can use the IF EXISTS<\/code> option. In this case, MySQL will terminate the statement without issuing any error.<\/p>\n\n\n\n
The DROP DATABASE<\/code> statement returns the number of tables it deleted.<\/p>\n\n\n\n
In MySQL, the schema is the synonym for the database. Therefore, you can use them interchangeably:<\/p>\n\n\n
DROP<\/span> SCHEMA<\/span> [IF<\/span> EXISTS<\/span>] database_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the next section, we will use the testdb<\/code> and testdb2<\/code> created in the CREATE DATABASE<\/a><\/code> tutorial. If you do not have these databases available, you can follow the previous tutorial<\/a> to create them.<\/p>\n\n\n\n
MySQL DROP DATABASE using mysql program example<\/h2>\n\n\n\n
First, log in to the MySQL Server using the root<\/code> user:<\/p>\n\n\n
mysql -u root -p<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
Type the password for the root<\/code> user and press Enter.<\/p>\n\n\n
Enter password: ********<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
SHOW<\/span> DATABASES<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------+\n| Database           |\n+--------------------+\n| classicmodels      |\n| information_schema |\n| mysql              |\n| performance_schema |\n| sys                |\n| testdb             |\n| testdb2            |\n+--------------------+\n7 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, drop the testdb<\/code> database by issuing the DROP DATABASE<\/code> statement:<\/p>\n\n\n
DROP<\/span> DATABASE<\/span> testdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Query OK, 0 rows affected (0.03 sec)<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL returned zero affected rows indicating that the testdb<\/code> database has no tables.<\/p>\n\n\n\n
DROP DATABASE using MySQL Workbench<\/h2>\n\n\n\n
First, launch the MySQL workbench and log in to the MySQL Server.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Second, right-click the database that you want to remove, for example, testdb2 and choose the Drop Schema...<\/code> option.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Third, MySQL Workbench displays a dialog to confirm the deletion. <\/p>\n\n\n\n
If you choose Review SQL<\/strong>, you’ll see the SQL statement that will be executed. If you choose Drop Now<\/strong>, it’ll delete the database immediately.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To be safe, let’s choose Review SQL:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Fourth, once you are sure that the SQL statement is going to drop the right database, you can click the Execute<\/strong> button to execute the statement.<\/p>\n\n\n\n
MySQL returns the following output indicating that the database is dropped successfully. Because the testdb2<\/code> is an empty database, the number of affected rows is zero.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If you view the schemas<\/strong> pane, you will see that the testdb2<\/strong> is not on the list anymore.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL DROP DATABASE<\/code> statement to delete a database.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, display all the databases using the SHOW DATABASES<\/a><\/code> statement:<\/p>\n\n\n