MySQL Create Database

Learn how to create databases in MySQL

🗄️ What is CREATE DATABASE?

The CREATE DATABASE statement creates a new database in MySQL. A database is a container that stores tables, views, and other database objects for organizing your data efficiently. 


-- Create a simple database
CREATE DATABASE mystore;

Output:

Query OK, 1 row affected (0.02 sec)

Key Concepts

📦

Database

Container for organizing tables 

CREATE DATABASE shop;
✅

IF NOT EXISTS

Prevents errors if database exists 

CREATE DATABASE IF NOT EXISTS shop;
🔤

Character Set

Define text encoding for database 

CREATE DATABASE shop 
CHARACTER SET utf8mb4;
🌍

Collation

Set sorting and comparison rules 

CREATE DATABASE shop 
COLLATE utf8mb4_unicode_ci;

🔹 Basic CREATE DATABASE Syntax

The simplest way to create a database is using the CREATE DATABASE statement followed by the database name. This creates an empty database ready to store tables and data. 

-- Basic syntax
CREATE DATABASE database_name;

-- Example: Create a database for a school
CREATE DATABASE school;

-- Example: Create a database for a blog
CREATE DATABASE blog_site;

Output:

Database 'school' created successfully

Database 'blog_site' created successfully

🔹 CREATE DATABASE IF NOT EXISTS

Using IF NOT EXISTS prevents errors when trying to create a database that already exists. This is useful in scripts that run multiple times or in production environments where you want to avoid failures. 

-- Safe database creation
CREATE DATABASE IF NOT EXISTS company;

-- This won't cause an error even if 'company' exists
CREATE DATABASE IF NOT EXISTS company;

-- Example with a longer name
CREATE DATABASE IF NOT EXISTS employee_management_system;

Output:

Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Note: 1 warning (database already exists)

🔹 CREATE DATABASE with Character Set

Specifying a character set determines how text data is stored and displayed in your database. UTF8MB4 is recommended for modern applications as it supports all Unicode characters including emojis and special symbols from various languages. 

-- Create database with UTF8MB4 character set
CREATE DATABASE online_store
CHARACTER SET utf8mb4;

-- Create database with specific collation
CREATE DATABASE customer_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Short form using CHARSET
CREATE DATABASE products
CHARSET utf8mb4;

Output:

Database created with UTF8MB4 encoding

✓ Supports emojis and international characters

🔹 Viewing Created Databases

After creating databases, you can view all databases on your MySQL server using the SHOW DATABASES command. This displays a list of all databases you have permission to see, including system databases and your custom databases. 

-- Show all databases
SHOW DATABASES;

-- Show databases matching a pattern
SHOW DATABASES LIKE 'shop%';

-- Use a specific database
USE mystore;

Output:

Database
mystore
school
blog_site

🔹 Complete Example

Here's a complete example showing how to create a database with all recommended settings, verify its creation, and start using it. This demonstrates best practices for database creation in real-world applications. 

-- Step 1: Create database with full configuration
CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Step 2: Verify database was created
SHOW DATABASES LIKE 'ecommerce';

-- Step 3: Select the database for use
USE ecommerce;

-- Step 4: Check current database
SELECT DATABASE();

Output:

✓ Database 'ecommerce' created successfully

✓ Database selected

Current database: ecommerce

💡 Best Practices

  • Always use IF NOT EXISTS in production scripts
  • Use utf8mb4 character set for international support
  • Choose meaningful database names (lowercase with underscores)
  • Avoid spaces and special characters in database names
  • Document your database purpose and structure

🧠 Test Your Knowledge

Which statement creates a database safely without errors if it exists?