How to Create a Database in MySQL: A Complete Step-by-Step Guide

Introduction

When we work with any software application — whether it’s an e-commerce website, a school management system, or a mobile app — the heart of the system is the database.

A database is a structured collection of data that can be easily stored, retrieved, and managed.
MySQL, one of the world’s most popular relational database management systems (RDBMS), allows us to create and manage databases efficiently.

In this guide, we’ll learn how to create a database in MySQL step by step with practical examples.

By the end of this lesson, you’ll not only know the commands but also the logic behind them.

What is MySQL Database?

  • MySQL Database is like a digital filing cabinet.

  • Inside it, we can store data in tables (rows and columns).

  • Each database may contain multiple tables, and each table holds specific information (e.g., students, products, employees).

 Example:

  • Database: SchoolDB

    • Table 1: Students

    • Table 2: Teachers

    • Table 3: Courses

Pre-Requisites

efore creating a database, make sure you have:

  1. MySQL installed (either locally or on a server).

  2. Access to MySQL Command-Line or a GUI tool like phpMyAdmin or MySQL Workbench.

  3. A user account with privileges to create a database. (Usually the root user has full access).

Step 1: Log into MySQL

Open your command-line terminal and type:

mysql -u root -p

  • -u root → tells MySQL you’re logging in as the root user.

  • -p → prompts you for the password.

After entering your password, you’ll enter the MySQL shell

Step 2: Create a Database

The syntax is simple:

 
CREATE DATABASE database_name;
CREATE DATABASE SchoolDB

This command creates a new database named SchoolDB.

✅ Best Practice:

  • Use meaningful names (ShopDB, LibraryDB, HRMS)

  • Avoid spaces and special characters

  • Stick to lowercase with underscores (student_db)

Step 3: Check if Database is Created

To verify, use:

 
SHOW DATABASES;

You’ll see a list of all databases. If SchoolDB appears, it means your database is successfully created. 🎉

Step 4: Select the Database

Before creating tables, you must select the database you want to work with:

 
USE SchoolDB;

Now, all your tables and operations will be stored inside SchoolDB.

Step 5: Create Tables inside the Database

A database is empty until we add tables.
Example: Create a Students table:

CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

  • student_id → A unique ID for each student

  • name → Stores student name (up to 100 characters)

  • age → Stores student’s age

  • grade → Stores grade/class

Step 6: Insert Data (Optional)

Let’s add some sample data:

INSERT INTO Students (name, age, grade)
VALUES ('Arjun Sharma', 15, '10th'),
       ('Priya Mehta', 14, '9th');

Step 7: View the Data

To check the stored records:

SELECT * FROM Students;

Output:


+------------+-------------+-----+-------+
| student_id | name        | age | grade |
+------------+-------------+-----+-------+
|          1 | Arjun Sharma|  15 | 10th  |
|          2 | Priya Mehta |  14 | 9th   |
+------------+-------------+-----+-------+

Bonus: Create Database Only If It Doesn’t Exist

Sometimes you may want to avoid errors if the database already exists:

 
CREATE DATABASE IF NOT EXISTS SchoolDB;

Common Errors & Fixes

  • Access Denied Error

    • Reason: Your MySQL user doesn’t have permission.

    • Fix: Log in as root or ask the admin to grant permissions.

     
    GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

  • Database Already Exists

    • Use: IF NOT EXISTS in your CREATE DATABASE command.

  • Forgot to Select Database

    • Always run: USE database_name; before creating tables.

Best Practices for Database Creation

✅ Use lowercase database names (avoid confusion on Linux vs Windows).

✅ Choose meaningful names related to the project.

✅ Regularly backup your database using mysqldump.

✅ Normalize your tables to avoid redundancy.

Quick Recap (Cheat Sheet)

-- Login
mysql -u root -p

-- Create Database
CREATE DATABASE SchoolDB;

-- View Databases
SHOW DATABASES;

-- Use Database
USE SchoolDB;

-- Create Table
CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

-- Insert Data
INSERT INTO Students (name, age, grade) VALUES ('Arjun Sharma', 15, '10th');

-- View Data
SELECT * FROM Students;