What is MySQL?

MySQL is a powerful, open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate data. It stores data in the form of tables consisting of rows and columns, where relationships between tables are defined using keys.

Originally developed by Michael "Monty" Widenius and David Axmark in 1995 under the Swedish company MySQL AB, MySQL is now owned by Oracle Corporation. It powers some of the largest websites and applications in the world, including Facebook, YouTube, Twitter, Netflix, Airbnb, GitHub, WordPress, and Wikipedia.

History & Evolution of MySQL

MySQL has had a fascinating journey from a small open-source project to becoming the backbone of the modern web.

Why is MySQL So Popular?

MySQL dominates the database market because of its perfect balance of simplicity, performance, reliability, and cost-effectiveness. Here are the core reasons developers love it:

MySQL Architecture: How It Works

MySQL follows a Client-Server architecture, where multiple clients can connect to a central MySQL server to perform database operations.

1. Client Layer

The topmost layer handles connection management, authentication, and security. Clients can be applications, command-line tools, or web interfaces like phpMyAdmin.

2. SQL Layer (Server Layer)

This is the brain of MySQL. It includes the SQL parser, optimizer, query cache, and execution engine. It analyzes, optimizes, and executes SQL queries.

3. Storage Engine Layer

MySQL supports pluggable storage engines — the actual layer that reads and writes data on disk. Common engines include:

• InnoDB — Default engine, supports ACID, foreign keys, transactions
• MyISAM — Older engine, fast for read-heavy workloads
• MEMORY — Stores data in RAM for ultra-fast access
• ARCHIVE — Optimized for storing large volumes of historical data
• CSV — Stores data as CSV files

4. File System Layer

The lowest layer where actual data files, log files, and configuration files are stored on disk.

Core Database Concepts in MySQL

🗄️ Database

A collection of related tables. For example, a SchoolDB might contain tables like Students, Teachers, and Courses.

📋 Table

A structured set of data organized in rows and columns. Each table represents a specific entity (e.g., Students).

📝 Row (Record / Tuple)

A single entry in a table representing one instance of the entity. For example, one student's complete information.

🏷️ Column (Field / Attribute)

A vertical entity that holds a specific type of data (e.g., Name, Age, Email).

🔑 Primary Key

A unique identifier for each row in a table. It cannot be NULL and must be unique.

🔗 Foreign Key

A field in one table that refers to the Primary Key of another table, establishing a relationship between them.

What a MySQL Table Looks Like

Here's a simple example of a Students table in MySQL:

SQL Command Categories in MySQL

MySQL uses SQL commands, grouped into 5 major categories:

Basic MySQL Commands (Hands-On)

1. Create a Database

CREATE DATABASE SchoolDB;

USE SchoolDB;

SHOW DATABASES;

2. Create a Table

CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    city VARCHAR(50),
    course VARCHAR(100),
    admission_date DATE DEFAULT (CURRENT_DATE)
);

3. Insert Data

-- Insert a single record
INSERT INTO Students (name, age, city, course)
VALUES ('Rumman Ansari', 25, 'Kolkata', 'Computer Science');

-- Insert multiple records at once
INSERT INTO Students (name, age, city, course) VALUES
('Aditi Sharma', 22, 'Delhi', 'Data Science'),
('Rohan Mehta', 24, 'Mumbai', 'AI & ML'),
('Priya Singh', 23, 'Bangalore', 'Cybersecurity');

4. Read / Retrieve Data

-- Fetch all records
SELECT * FROM Students;

-- Fetch specific columns with condition
SELECT name, city FROM Students WHERE age > 22;

-- Sort by name
SELECT * FROM Students ORDER BY name ASC;

-- Limit results
SELECT * FROM Students LIMIT 3;

5. Update Data

-- Update a single record
UPDATE Students 
SET age = 26 
WHERE name = 'Rumman Ansari';

-- Update multiple columns
UPDATE Students 
SET city = 'Hyderabad', course = 'Cloud Computing' 
WHERE student_id = 102;

6. Delete Data

-- Delete a specific record
DELETE FROM Students WHERE student_id = 101;

-- Delete all records (keeps table structure)
DELETE FROM Students;

-- Drop the entire table
DROP TABLE Students;

7. Alter Table Structure

-- Add a new column
ALTER TABLE Students ADD email VARCHAR(100);

-- Modify a column's data type
ALTER TABLE Students MODIFY age TINYINT;

-- Rename a column
ALTER TABLE Students CHANGE city location VARCHAR(100);

-- Drop a column
ALTER TABLE Students DROP COLUMN email;

8. Filtering with WHERE Clause

-- Using AND / OR
SELECT * FROM Students WHERE age > 22 AND city = 'Kolkata';

-- Using BETWEEN
SELECT * FROM Students WHERE age BETWEEN 22 AND 25;

-- Using IN
SELECT * FROM Students WHERE city IN ('Delhi', 'Mumbai', 'Kolkata');

-- Using LIKE for pattern matching
SELECT * FROM Students WHERE name LIKE 'R%';

9. Aggregate Functions

-- Count total students
SELECT COUNT(*) AS total_students FROM Students;

-- Average age
SELECT AVG(age) AS average_age FROM Students;

-- Maximum and minimum age
SELECT MAX(age) AS oldest, MIN(age) AS youngest FROM Students;

-- Group by city
SELECT city, COUNT(*) AS total 
FROM Students 
GROUP BY city;

10. Joining Tables

-- INNER JOIN example
SELECT s.name, c.course_name, c.duration
FROM Students s
INNER JOIN Courses c ON s.course_id = c.course_id;

-- LEFT JOIN example
SELECT s.name, c.course_name
FROM Students s
LEFT JOIN Courses c ON s.course_id = c.course_id;

Common Data Types in MySQL

Example: Using Different Data Types

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2),
    is_active BOOLEAN DEFAULT TRUE,
    joining_date DATE,
    profile JSON,
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

MySQL Editions

Oracle offers multiple versions of MySQL based on user needs:

Advantages vs Limitations of MySQL

When Should You Use MySQL?

Real-World Applications of MySQL

MySQL vs Other Databases

Popular MySQL Tools

MySQL Best Practices

Example: Using Transactions Safely

START TRANSACTION;

UPDATE Accounts SET balance = balance - 5000 WHERE acc_id = 1;
UPDATE Accounts SET balance = balance + 5000 WHERE acc_id = 2;

-- If everything is OK
COMMIT;

-- If something went wrong
-- ROLLBACK;