MySQL CREATE DATABASE: Step-by-Step Guide with Syntax, Examples, and Best Practices

MySQL CREATE DATABASE, creating a database is one of the first tasks you’ll perform when working with MySQL. Whether you’re developing a web application, building a reporting system, or learning SQL, understanding how to create and manage databases is an essential skill.

In this comprehensive guide, you’ll learn how to create a new MySQL database using both the command-line interface (CLI) and MySQL Workbench. We’ll also cover syntax, practical examples, best practices, common mistakes, and frequently asked questions to help you work confidently with MySQL databases.

What Is a Database in MySQL?

A database is a structured collection of related data. It serves as a container that holds tables, views, indexes, stored procedures, triggers, and other database objects.

For example:

• An e-commerce website may have a database named online_store.
• A university may use a database called student_management.
• A hospital may maintain a database named hospital_records.

Each database organizes information independently, making applications easier to manage and scale.

Prerequisites

Before creating a database, ensure you have the following:

• MySQL Server installed
• Access to the MySQL command-line client
• A MySQL user with CREATE DATABASE privileges
• Basic understanding of SQL statements

MySQL CREATE DATABASE Syntax

The basic syntax is straightforward:

CREATE DATABASE database_name;

Example

CREATE DATABASE company_db;

After execution, MySQL creates a new database named company_db.

How to Create a Database Using MySQL Command Line

Step 1: Open the MySQL Command Line

Open your terminal or command prompt and connect to the MySQL server.

mysql -u root -p

Enter your password when prompted.

If authentication is successful, you’ll see the MySQL prompt.

mysql>

Step 2: Create a New Database

Use the CREATE DATABASE statement.

CREATE DATABASE company_db;

Output:

Query OK, 1 row affected

The database has now been created successfully.

Step 3: View Existing Databases

Verify that the database exists.

SHOW DATABASES;

Example output:

company_db
information_schema
mysql
performance_schema
sys

Your new database should appear in the list.

Step 4: Select the Database

Before creating tables, switch to the database.

USE company_db;

Output:

Database changed

Now every SQL command will execute within this database.

Step 5: Create Your First Table

Let’s create an employee job title table.

CREATE TABLE job_titles (
    job_id INT PRIMARY KEY,
    job_name VARCHAR(100) NOT NULL,
    median_salary DECIMAL(10,2),
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

Explanation

Note: The referenced departments table must already exist before creating the foreign key.

Step 6: Verify the Table

List all tables.

SHOW TABLES;

Example output:

job_titles

Display the table structure.

DESCRIBE job_titles;

Example output:

Field            Type
-----------------------------
job_id           int
job_name         varchar(100)
median_salary    decimal(10,2)
department_id    int

Creating a Database If It Doesn’t Already Exist

To avoid an error when a database already exists, use:

CREATE DATABASE IF NOT EXISTS company_db;

This statement safely creates the database only if it doesn’t already exist.

How to Create a Database in MySQL Workbench

If you prefer a graphical interface, MySQL Workbench makes the process simple.

Step 1

Open MySQL Workbench and connect to your MySQL server.

Step 2

From the top menu:

Database → Create Database

Step 3

Enter the database name.

Example:

company_db

Click Apply.

Step 4

Review the generated SQL statement.

CREATE DATABASE company_db;

Click Apply again.

Step 5

Refresh the Navigator panel.

Your newly created database will now appear under Schemas.

Step 6

To create a table:

• Expand the database
• Right-click Tables
• Select Create Table
• Define columns and constraints
• Click Apply
• Review the generated SQL
• Click Finish

Your table is now ready to use.

Practical Example

Suppose you’re building a Human Resources application.

Create the database:

CREATE DATABASE hr_database;

Select it:

USE hr_database;

Create an employee table:

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE
);

Insert sample data:

INSERT INTO employees
(first_name, last_name, email, salary, hire_date)
VALUES
('John','Smith','john@example.com',65000,'2025-01-10');

Retrieve data:

SELECT * FROM employees;

Common Errors and Solutions

Database Already Exists

Error:

ERROR 1007 (HY000)

Solution:

CREATE DATABASE IF NOT EXISTS company_db;

Access Denied

Error:

ERROR 1044

Cause:

The user doesn’t have permission.

Solution:

Grant CREATE DATABASE privileges or use an administrator account.

Unknown Database

Error:

Unknown database

Cause:

The database name is incorrect.

Solution:

Run:

SHOW DATABASES;

Verify the exact database name.

Best Practices

Use Meaningful Database Names

Good examples:

• ecommerce
• hr_database
• school_management

Avoid:

• db1
• test123
• abc

Use Lowercase Names

Example:

customer_db

Lowercase names improve portability across operating systems.

Avoid Spaces

Instead of:

Company Database

Use:

company_database

Create Separate Databases

Avoid storing unrelated applications in the same database.

For example:

• sales_db
• finance_db
• inventory_db

Back Up Databases Regularly

Always create backups before making structural changes.

Advantages of CREATE DATABASE

• Organizes application data efficiently
• Supports multiple applications on one server
• Simplifies security management
• Makes backups easier
• Improves maintainability
• Enables scalable database architecture

Frequently Asked Questions

Can I create multiple databases on one MySQL server?

Yes. A single MySQL server can host hundreds or even thousands of databases, depending on available system resources.

Can I rename a MySQL database?

MySQL does not provide a direct RENAME DATABASE command. The recommended approach is to create a new database, export the existing data, and import it into the new database.

How do I delete a database?

Use:

DROP DATABASE database_name;

Be cautious—this permanently removes the database and all its contents.

Is MySQL Workbench required?

No. Everything you can do in MySQL Workbench can also be accomplished through the command-line interface.

Conclusion

The MySQL CREATE DATABASE statement is the foundation of every MySQL project. Whether you’re managing a small personal project or a large enterprise application, understanding how to create, verify, and organize databases is an essential SQL skill.

By mastering both the command-line interface and MySQL Workbench, you can efficiently create databases, build tables, and develop scalable applications. Following best practices such as using descriptive names, checking for existing databases, and organizing related data into separate databases will help ensure your projects remain maintainable and efficient as they grow.