4.5 CREATE DATABASE and DROP DATABASE

To create a new database, use the CREATE DATABASE statement. The following statement creates a database named mydb:

CREATE DATABASE mydb;

After a database has been created, you can create new tables in it using the CREATE TABLE statement, which is described in section 4.6, "CREATE TABLE."

If you try to create a database that already exists, an error occurs. If you simply want to ensure that the database exists, add an IF NOT EXISTS clause to the statement:

CREATE DATABASE IF NOT EXISTS mydb;

With the additional clause, the statement creates the database only if it does not already exist. Otherwise, the statement does nothing and no error occurs. This can be useful in applications that need to ensure that a given database is available, without disrupting any existing database with the same name.

Creating a database has no effect on the database that's currently selected as the default database. To make the new database the default database, issue a USE statement:

USE mydb;

To see a list of available databases, use the SHOW DATABASES statement. This statement will not show the names of databases to which you have no access. To see a list of tables in a database, use SHOW TABLES FROM db_name (or just SHOW TABLES if db_name is the name of the current database). The SHOW command is described in more detail in section 4.11, "Using SHOW and DESCRIBE to Review Table Structures."

When you no longer need a database, you can remove it with DROP DATABASE:

DROP DATABASE mydb;

It's unnecessary to remove the tables in a database before dropping it. DROP DATABASE does not require the database to be empty, so it does not fail if the database contains tables. DROP DATABASE removes the tables in the process of removing the database.

DROP DATABASE is a dangerous statement and you should use it with care. There is no statement to "undo" DROP DATABASE. If you drop a database by mistake, your only option is to recover it from your backups.