MySQL CREATE DATABASE

A database is a container that holds tables and other database objects. Before creating any tables or storing data in MySQL, a database must first be created. The CREATE DATABASE statement is used for this purpose.

Syntax

CREATE DATABASE database_name;

database_name is the name given to the new database. It must be unique on the MySQL server — no two databases can share the same name.

Rules for Naming a Database

• Names can contain letters, numbers, and underscores.
• Names cannot have spaces. Use underscores instead (e.g., my_database).
• Names are case-insensitive on Windows but case-sensitive on Linux.
• Avoid using MySQL reserved words like SELECT, TABLE, or DATABASE as names.

Example: Creating a Database

The following statement creates a new database named school:

CREATE DATABASE school;

MySQL responds with:

Query OK, 1 row affected (0.01 sec)

This confirms the database was created successfully.

Viewing All Databases

To see a list of all databases on the MySQL server, use:

SHOW DATABASES;

Sample output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

The first four entries are system databases created by MySQL automatically. The school database is the one just created.

Switching to a Database

Creating a database does not automatically make it active. To start using it, the USE statement must be run:

USE school;

MySQL responds with:

Database changed

All subsequent commands will now apply to the school database until a different database is selected.

Creating a Database Only if It Does Not Exist

If a database with the same name already exists, running CREATE DATABASE again will produce an error. To avoid this, use the IF NOT EXISTS clause:

CREATE DATABASE IF NOT EXISTS school;

If the database already exists, MySQL skips the creation and does not raise an error. This is useful in scripts that run multiple times.

Specifying a Character Set and Collation

A character set determines which characters can be stored (for example, English only, or multilingual). A collation determines how those characters are sorted and compared.

CREATE DATABASE school
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

utf8mb4 supports all Unicode characters including emojis. utf8mb4_unicode_ci makes comparisons case-insensitive. This is the recommended setup for most modern applications.

Checking the Current Database

To find out which database is currently active, use:

SELECT DATABASE();

Output example:

+------------+
| DATABASE() |
+------------+
| school     |
+------------+

Complete Example Walkthrough

-- Step 1: Create the database
CREATE DATABASE IF NOT EXISTS school
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Step 2: Verify it was created
SHOW DATABASES;

-- Step 3: Switch to the new database
USE school;

-- Step 4: Confirm the active database
SELECT DATABASE();

Key Points

• Use CREATE DATABASE database_name; to create a new database.
• Use IF NOT EXISTS to avoid errors when the database already exists.
• Use USE database_name; to make a database active before working in it.
• Use SHOW DATABASES; to list all available databases.
• Setting CHARACTER SET utf8mb4 is recommended for multilingual or international applications.