MySQL Complete Roadmap
1 - Fundamentals
1. What is Data?
Raw facts, values, or information that can be stored, processed,
analyzed.
2. What is a Database?
Organized collection of data stored in tables.
3. DBMS (Database Management System)
Software used to store, retrieve, and manage data.
4. RDBMS (Relational DBMS)
Stores data in tables with rows and columns.
Supports relationships using Primary Key + Foreign Key.
Examples → MySQL, PostgreSQL, Oracle, SQL Server.
5. Types of Databases
a) Relational Databases (SQL)
Structured
Uses tables
Follows ACID properties
Examples: MySQL, PostgreSQL, Oracle
b) Non-Relational (NoSQL)
store data in flexible, non-tabular formats like documents or key-
value pairs, rather than the rigid tables of a relational database
2 - SQL Basics
6. What is SQL?
Structured Query Language → to store, retrieve, update, and
delete data.
7. MySQL
Most popular open-source SQL database used in web apps.
3 - SQL Commands
8. SQL Command Types
Type Purpose Examples
Create
DDL CREATE, ALTER, DROP, TRUNCATE
structure
DML Modify data INSERT, UPDATE, DELETE
DQL Query data SELECT
DCL Permissions GRANT, REVOKE
TCL Transactions COMMIT, ROLLBACK, SAVEPOINT
4 - Constraints
9. SQL Constraints
Constraint Meaning
PRIMARY KEY Unique + Not Null
FOREIGN KEY Links two tables
UNIQUE No duplicate values
NOT NULL Must have a value
Value must follow a
CHECK
condition
DEFAULT Auto value
Auto-increment
AUTO_INCREMENT
integer
10. Cascading
ON DELETE CASCADE
ON UPDATE CASCADE
Used for maintaining referential integrity.
5 - Operators
11. SQL Operators
Comparison: =, <>, >, <, >=, <=
Logical: AND, OR, NOT
Range: BETWEEN
Pattern: LIKE, NOT LIKE
List: IN, NOT IN
Null: IS NULL, IS NOT NULL
6 -Filtering & Sorting
12. WHERE Clause
Filters rows before grouping.
13. ORDER BY
Sort ascending/descending.
14. LIMIT
Restrict number of rows.
15. OFFSET
Used for pagination.
7- GROUPING
16. GROUP BY
Groups rows to apply aggregate functions.
17. HAVING Clause
Filters after grouping (unlike WHERE).
18. Aggregate Functions
COUNT()
SUM()
AVG()
MIN()
MAX()
19. GROUPING WITH ROLLUP (Subtotal + Grand Total)
MySQL supports:
ROLLUP
Subtotal
Grand total
8 - SQL Order of Evaluation
Important for interviews
Actual order SQL executes:
1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY
8. LIMIT / OFFSET
9 - JOINS
20. Types of Joins
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN (not in MySQL → simulate using UNION)
CROSS JOIN
SELF JOIN
10 - Subqueries
21. Types of Subqueries
Single-row
Multi-row
Correlated subquery
In SELECT, WHERE, FROM
11 - Views
22. Views
Virtual table
Simplifies complex queries
Reusable
Secure
Materialized View
MySQL does NOT support directly
But can simulate using tables + triggers
12 - Indexes
23. Index Types
B-Tree index (default)
Composite index
Unique index
Full-text index
Hash index (in-memory)
Why Indexes?
Speed up SELECT
Slow down INSERT/UPDATE/DELETE
13 - Transactions
24. ACID Properties
Atomicity
Consistency
Isolation
Durability
25. Transaction Control
START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT
14 - Query Optimization
26. Optimization Techniques
Use indexes properly
Use WHERE filters before joining
Avoid SELECT *
Avoid subqueries → use joins when possible
Proper composite index (left-most rule)
Use LIMIT & keyset pagination instead of OFFSET
Denormalize heavy read models
Partition large tables
15- MySQL Functions
27. String Functions
CONCAT
SUBSTR
LOWER/UPPER
TRIM
LENGTH
28. Date Functions
NOW()
CURDATE()
DATEDIFF()
DATE_ADD()
MONTH(), YEAR()
29. Math Functions
ROUND()
CEIL()
FLOOR()
16 - MySQL Advanced
30. Stored Procedures
Procedure with parameters
IN, OUT, INOUT parameters
31. Triggers
Before Insert
After Insert
Before Update
After Delete
32. Events
Scheduled tasks inside MySQL
33. Database Locking
Row-level lock
Table-level lock
Shared & Exclusive locks
34. Partitioning
Range
List
Hash
Key
17 - Security & Permissions
35. Users & Roles
CREATE USER
GRANT
REVOKE
36. SQL Injection Prevention
Use prepared statements