CREATE and DROP DATABASE in MySQL | Operations on a Database in MySQL

CREATE DATABASE

The CREATE DATABASE statement is used in MySQL to create a new database on a MySQL server. The syntax for this is shown below:

Syntax:

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]

In the above syntax:

• database_name is specified after the CREATE DATABASE statement, and it must be unique.
• IF NOT EXISTS is used to create the database only if it already doesn't exist on the server.
• After that, the character set and collation are specified for the new database. This is optional, if not defined MySQL will set them to default for the new database.

For example:

CREATE DATABASE testdatabase;

Selecting database using the USE statement

The USE statement is used in MySQL to select an existing database on a MySQL server. The syntax for this is shown below:

Syntax:

USE database_name;

In the above syntax, the name of the database that we want to set is used with the USE statement to set it to the current database.

For example:

USE testdatabase;

The above query is used to set the current database to testdatabase.

It will show the message “database changed” which means the current database has been successfully set to testdatabase.

It can be verified by using the SELECT database() statement.

SELECT database();

The above query will return something like shown below:

DROP DATABASE

DROP DATABASE statement is used in MySQL to delete all the tables of a database and delete the database permanently from the server.

The syntax for this is shown below:

DROP DATABASE [IF EXISTS] database_name;

In the above syntax:

• database_name is the name of the database that we want to delete from the server.
• If the specified database doesn't exist, it will show an error.
• The IF EXISTS condition is used to prevent that error, as it checks first that if the database specified to drop exists on the server or not.

All the existing databases can be shown using the SHOW DATABASES statement.

For example:

SHOW DATABASES 

It will return all the existing databases as shown below:

Now, the DROP DATABASE statement can be used to delete an existing database.

DROP DATABASE testdatabase;

The above query will delete all the tables and the testdatabase from the MySQL server.