Creating and deleting databases

When writing SQL queries, we actively use tables. The tables themselves are stored within specific databases, which will be discussed in this article.

Database creation

Database creation has the following syntax:

MySQL 8.4

CREATE DATABASE database_name;

database_name: The name of the new database.

Database names usually use letters, numbers, and the characters "_" and "$". The maximum name length is 64 characters.

You can control the creation of the database using the SHOW DATABASES operator.

MySQL 8.4
SHOW DATABASES;

Database
user_table_1
user_table_2
information_schema
mysql
performance_schema
sys

If a database name is written without double quotes, it must start with a letter or the "_" character. After that, you can use letters, numbers, as well as the "_" and "$" characters. The maximum name length is 63 characters.

You can control the creation of the database using an SQL query:

MySQL 8.4
SELECT datname FROM pg_database WHERE datistemplate = false;

datname
user_database_1
user_database_2
postgres

Deleting database

Deleting a database is done using the DROP DATABASE operator:

MySQL 8.4

DROP DATABASE database_name;

database_name: The database to delete together with all its tables.

IF EXISTS and IF NOT EXISTS

When creating or deleting a database, an error may occur. For example, the database may already exist or, on the contrary, may not exist yet. In such cases, the IF EXISTS and IF NOT EXISTS constructions are used.

That is, if we want to create a database only on the condition that it does not exist yet, then we use following syntax:

MySQL 8.4

CREATE DATABASE IF NOT EXISTS database_name;

IF NOT EXISTS: Create the database only if there is no database with this name yet. Otherwise the query does nothing and doesn't fail.
Keyword
database_name: The name of the new database.

If we want to delete the database only if it exists, then we use following syntax:

MySQL 8.4

DROP DATABASE IF EXISTS database_name;

IF EXISTS: Delete the database only if it exists. Otherwise the query does nothing and doesn't fail.
Keyword
database_name: The database to delete.

IF EXISTS

When deleting a database, an error may occur if such a database does not exist. In this case, you can use the IF EXISTS construction.

When deleting a database, you can use the IF EXISTS construction:

MySQL 8.4

DROP DATABASE IF EXISTS database_name;

IF EXISTS: Delete the database only if it exists. Otherwise the query does nothing and doesn't fail.
Keyword
database_name: The database to delete.