admin
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;
