Chapter 4: Creating and Managing Tables
This is where we start building the real structure inside our database. Tables are the heart of any database — everything else (data, queries, relationships) lives inside tables.
Today we’ll learn how to:
- Create tables
- Choose correct data types
- Add constraints (rules) so data stays clean
- Modify tables later
- Delete tables
- See what tables exist
So open your MySQL command line or Workbench, make sure you’re inside your database, and let’s start building our coaching_class database together!
First, let’s switch to our database (if you haven’t already):
SQL
|
0 1 2 3 4 5 6 |
USE my_coaching; -- or USE coaching_class; whatever name you chose |
1. CREATE TABLE – The Most Important Command!
Basic Syntax:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE table_name ( column1 datatype [constraints], column2 datatype [constraints], ... ); |
Let’s create our first real table: students
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE students ( student_id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(100) NOT NULL, phone VARCHAR(15) UNIQUE, email VARCHAR(100) UNIQUE, date_of_birth DATE, gender CHAR(1), -- 'M' or 'F' or 'O' is_active BOOLEAN DEFAULT TRUE, join_date DATE DEFAULT CURRENT_DATE, notes TEXT ); |
Run this command — you should see: Query OK, 0 rows affected
Explanation of each part (very detailed):
| Column Name | Data Type | What it means | Constraints used |
|---|---|---|---|
| student_id | INT | Unique number for each student (like roll number) | AUTO_INCREMENT + PRIMARY KEY |
| full_name | VARCHAR(100) | Student’s full name (up to 100 characters) | NOT NULL (cannot be empty) |
| phone | VARCHAR(15) | Phone number (we use VARCHAR because it can have +91, -, spaces) | UNIQUE (no two students same phone) |
| VARCHAR(100) | Email address | UNIQUE | |
| date_of_birth | DATE | Birth date (format: YYYY-MM-DD) | None (can be empty) |
| gender | CHAR(1) | Single character: ‘M’, ‘F’, ‘O’ (other) | None |
| is_active | BOOLEAN | TRUE = active student, FALSE = left the class | DEFAULT TRUE |
| join_date | DATE | When student joined the coaching | DEFAULT CURRENT_DATE |
| notes | TEXT | Any extra information (long text, can be very long) | None |
Common Data Types in MySQL (you should know these by heart):
| Data Type | Use Case | Example Values | Max Size/Range |
|---|---|---|---|
| INT | Whole numbers (IDs, quantities) | 1, 42, 100000 | -2 billion to +2 billion |
| VARCHAR(n) | Variable-length text (most common for names) | “Priya Sharma”, “rahul123” | Up to n characters (max 65,535) |
| CHAR(n) | Fixed-length text (like gender ‘M’/’F’) | ‘M’, ‘F’ | Exactly n characters |
| TEXT | Long text (notes, bio, messages) | Long paragraph | Up to 65,535 characters |
| DATE | Only date (no time) | 2005-08-15 | 1000-01-01 to 9999-12-31 |
| DATETIME | Date + Time | 2025-01-15 14:30:00 | Same as DATE + time |
| BOOLEAN | True/False | TRUE / FALSE | Stored as 1/0 |
| DECIMAL(10,2) | Money, precise numbers | 99999999.99 | 10 digits total, 2 after decimal |
2. Constraints – Rules to Keep Data Clean
| Constraint | Meaning | Example in our table |
|---|---|---|
| PRIMARY KEY | Unique + Not Null. One per table. Automatically creates index. | student_id |
| NOT NULL | Value cannot be empty (NULL) | full_name |
| UNIQUE | No two rows can have same value | phone, email |
| AUTO_INCREMENT | MySQL automatically gives next number (1,2,3…) | student_id |
| DEFAULT | If no value given, use this default | join_date DEFAULT CURRENT_DATE |
3. Let’s Add Some Data to See It Work!
SQL
|
0 1 2 3 4 5 6 7 8 9 10 |
INSERT INTO students (full_name, phone, email, date_of_birth, gender, join_date) VALUES ('Priya Sharma', '9876543210', 'priya.sharma@gmail.com', '2008-05-12', 'F', '2025-01-05'), ('Rahul Patel', '9123456789', 'rahul.p@gmail.com', '2007-11-20', 'M', '2025-01-10'), ('Sneha Joshi', '9988776655', 'sneha.joshi@yahoo.com', '2009-03-03', 'F', DEFAULT); |
4. SHOW TABLES – See All Tables in Current Database
SQL
|
0 1 2 3 4 5 6 |
SHOW TABLES; |
You should see:
text
|
0 1 2 3 4 5 6 7 8 9 10 |
+---------------------+ | Tables_in_my_coaching | +---------------------+ | students | +---------------------+ |
5. DESCRIBE table – See Structure of a Table
SQL
|
0 1 2 3 4 5 6 7 8 |
DESCRIBE students; -- or DESC students; |
You will see something like this:
text
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
+---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | student_id | int | NO | PRI | NULL | auto_increment | | full_name | varchar(100) | NO | | NULL | | | phone | varchar(15) | YES | UNI | NULL | | | email | varchar(100) | YES | UNI | NULL | | | date_of_birth | date | YES | | NULL | | | gender | char(1) | YES | | NULL | | | is_active | tinyint(1) | YES | | 1 | | | join_date | date | YES | | curdate()| | | notes | text | YES | | NULL | | +---------------+--------------+------+-----+---------+----------------+ |
6. ALTER TABLE – Modify Existing Table (Very Important!)
Add a new column:
SQL
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ADD COLUMN address VARCHAR(200) AFTER email; |
Drop (remove) a column:
SQL
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students DROP COLUMN notes; |
Modify (change) a column’s data type or constraints:
SQL
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students MODIFY COLUMN phone VARCHAR(20) NOT NULL; |
Add a new constraint:
SQL
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ADD CONSTRAINT unique_email UNIQUE (email); |
Rename a column:
SQL
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students CHANGE COLUMN full_name student_name VARCHAR(120) NOT NULL; |
7. DROP TABLE – Delete a Table (Careful!)
SQL
|
0 1 2 3 4 5 6 |
DROP TABLE IF EXISTS old_students; |
This deletes the table and all data inside it forever — no recycle bin!
That’s it for Chapter 4! 🎉
Summary – What we learned today:
- CREATE TABLE → Build structure with columns & data types
- Common data types: INT, VARCHAR, DATE, TEXT, BOOLEAN
- Constraints: PRIMARY KEY, NOT NULL, UNIQUE, AUTO_INCREMENT, DEFAULT
- SHOW TABLES → List all tables
- DESCRIBE / DESC → See table structure
- ALTER TABLE → Change table later (add/drop/modify columns)
- DROP TABLE → Delete table
Homework for today (do it now – super important!)
- Make sure you’re in your database (USE my_coaching;)
- Create the students table exactly as shown above
- Run these commands and paste the output here:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SHOW TABLES; DESC students; -- Then insert the 3 sample rows I showed INSERT INTO students ... (the 3 rows) -- Finally see the data SELECT * FROM students; |
