How to Create a Table in MySQL: Step-by-Step Guide

Introduction

A table in MySQL is like a spreadsheet with rows and columns.

Rows = records (individual entries)
Columns = fields (attributes of the data)

Example: In a Student Database, we may have a table named Students with columns like student_id, name, age, grade.

In this lesson, we’ll learn how to create a table in MySQL step by step.

Step 1: Select a Database

Before creating a table, you must choose the database where the table will be stored.

This ensures your new table belongs to the SchoolDB database.

Step 2: Syntax of CREATE TABLE

The general structure is:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

table_name → Name of your table (e.g., Students, Employees).
column1, column2 → Field names.
datatype → Type of data (e.g., INT, VARCHAR, DATE).
constraints → Rules (e.g., NOT NULL, PRIMARY KEY, UNIQUE).

Step 3: Example – Creating a Students Table

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

Explanation:

student_id INT AUTO_INCREMENT PRIMARY KEY → Each student gets a unique ID automatically.
name VARCHAR(100) NOT NULL → Stores name (up to 100 characters) and cannot be empty.
age INT → Stores age as a number.
grade VARCHAR(10) → Stores grade/class (e.g., “10th”).
admission_date DATE → Stores admission date.

Step 4: Verify the Table

After creating a table, check if it exists:

+----------------+
| Tables_in_SchoolDB |
+----------------+
| Students       |
+----------------+

Step 5: Describe the Table Structure

To check columns and their datatypes:

Output:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| student_id     | int          | NO   | PRI | NULL    | auto_increment |
| name           | varchar(100) | NO   |     | NULL    |                |
| age            | int          | YES  |     | NULL    |                |
| grade          | varchar(10)  | YES  |     | NULL    |                |
| admission_date | date         | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

Step 6: Insert and View Data (Optional)

Add sample records:

Check records:

+------------+-------------+-----+-------+----------------+
| student_id | name        | age | grade | admission_date |
+------------+-------------+-----+-------+----------------+
|          1 | Arjun Sharma|  15 | 10th  | 2025-06-01     |
|          2 | Priya Mehta |  14 | 9th   | 2025-07-10     |
+------------+-------------+-----+-------+----------------+

Step 7: Drop (Delete) a Table (Optional)

If you want to remove the table completely:

⚠️ Warning: This deletes the table and all its data permanently.

Best Practices for Creating Tables

✅ Use singular names for tables (e.g., Student not Students) in enterprise projects.

✅ Always define a PRIMARY KEY for unique identification.

✅ Use appropriate data types (e.g., DATE for dates, not VARCHAR).

✅ Add constraints (NOT NULL, UNIQUE, DEFAULT) for data safety.

✅ Use AUTO_INCREMENT for IDs to avoid duplication.

Quick Recap (Cheat Sheet)

-- Select Database
USE SchoolDB;

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

-- Show Tables
SHOW TABLES;

-- Describe Table
DESCRIBE Students;

-- Insert Data
INSERT INTO Students (name, age, grade, admission_date)
VALUES ('Rahul Kumar', 16, '11th', '2025-05-20');

-- View Data
SELECT * FROM Students;

-- Delete Table
DROP TABLE Students;