Master
MySQL

Batman's complete guide to DDL, DML, aggregate functions, GROUP BY, ORDER BY, JOINs and everything in between.

🏗

DDL

CREATE, ALTER, DROP — define your schema

✏️

DML

INSERT, UPDATE, DELETE — manipulate data

🔍

Queries

SELECT, WHERE, ORDER BY, LIMIT

📊

Aggregates

COUNT, SUM, AVG, GROUP BY, HAVING

⚡

Advanced

JOINs, Subqueries, Constraints

📋

Cheatsheet

All commands at a glance

🏗 DDL — Data Definition Language
DDL is used to define and modify the structure of your database — tables, columns, constraints. These changes are auto-committed (can't be rolled back).
🗄
CREATE DATABASE & USE
DDL ▶
Before creating tables, you need a database. CREATE DATABASE creates it, USE selects it as the active one.
SQL
CREATE DATABASE college;
USE college;

-- Check all databases
SHOW DATABASES;

-- Delete a database (careful!)
DROP DATABASE college;
📋
CREATE TABLE
DDL ▶
Defines a new table with columns and their data types. Every column must have a type, and you can add constraints.
SQL
CREATE TABLE students (
    student_id   INT           AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    email        VARCHAR(150) UNIQUE NOT NULL,
    age          INT           CHECK(age >= 18),
    branch       VARCHAR(50)  DEFAULT 'CSE',
    cgpa         DECIMAL(4,2),
    joined_on    DATE
);
Data Types Quick Ref:
INT — whole numbers  |  DECIMAL(p,s) — exact decimals  |  VARCHAR(n) — variable text  |  CHAR(n) — fixed text  |  DATE — YYYY-MM-DD  |  TEXT — long text  |  BOOLEAN — 0/1
Constraints at a glance:
PRIMARY KEY — unique + not null  |  NOT NULL — can't be empty  |  UNIQUE — no duplicates  |  DEFAULT val — fallback value  |  CHECK(cond) — validation  |  FOREIGN KEY — links to another table
🔧
ALTER TABLE
DDL ▶
ALTER TABLE lets you modify an existing table — add/remove columns, change types, rename things.
SQL
-- Add a new column
ALTER TABLE students
  ADD COLUMN phone VARCHAR(15);

-- Remove a column
ALTER TABLE students
  DROP COLUMN phone;

-- Change column type / name
ALTER TABLE students
  MODIFY COLUMN name VARCHAR(200);

ALTER TABLE students
  RENAME COLUMN joined_on TO enrollment_date;

-- Add a constraint later
ALTER TABLE students
  ADD CONSTRAINT chk_age CHECK(age >= 17);
🗑
DROP, TRUNCATE, RENAME
DDL ▶
Three ways to "delete" — they're very different!
SQL
-- DROP: deletes the ENTIRE table + structure
DROP TABLE students;

-- TRUNCATE: deletes all ROWS but keeps the table
-- Faster than DELETE, resets AUTO_INCREMENT
TRUNCATE TABLE students;

-- RENAME: rename a table
RENAME TABLE students TO learners;
DROP vs TRUNCATE vs DELETE:
DROP = table gone forever  |  TRUNCATE = empty table, no rollback  |  DELETE = remove rows with WHERE, can rollback
🔗
FOREIGN KEY & References
DDL ▶
A FOREIGN KEY links a column in one table to the PRIMARY KEY of another — enforcing referential integrity.
SQL
CREATE TABLE courses (
    course_id   INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    enroll_id   INT PRIMARY KEY AUTO_INCREMENT,
    student_id  INT,
    course_id   INT,
    grade       CHAR(2),

    FOREIGN KEY (student_id) REFERENCES students(student_id)
        ON DELETE CASCADE,
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)
        ON DELETE SET NULL
);
ON DELETE options:
CASCADE — delete child rows when parent is deleted
SET NULL — set FK to NULL when parent is deleted
RESTRICT — prevent deletion if child rows exist
✏️ DML — Data Manipulation Language
DML is used to manipulate the actual data in your tables — inserting, updating, deleting rows. These can be rolled back in a transaction.
➕
INSERT — Adding Rows
DML ▶
INSERT INTO adds new rows. You can insert one or multiple rows at once.
SQL
-- Insert a single row
INSERT INTO students (name, email, age, branch, cgpa)
VALUES ('Bruce Wayne', 'batman@gotham.com', 20, 'CSE', 9.2);

-- Insert multiple rows at once (efficient!)
INSERT INTO students (name, email, age, branch, cgpa)
VALUES
  ('Clark Kent',     'superman@krypton.com', 22, 'EEE', 8.8),
  ('Diana Prince',   'wonder@themysc.com',   21, 'CSE', 9.5),
  ('Barry Allen',    'flash@ccpd.com',       19, 'ECE', 7.9),
  ('Arthur Curry',   'aqua@atlantis.com',    23, 'MECH', 6.5);

-- If ALL columns are filled in order, skip column list
INSERT INTO students
VALUES (NULL, 'Hal Jordan', 'green@corps.com', 24, 'CSE', 8.1, '2024-08-01');
✏️
UPDATE — Modifying Rows
DML ▶
UPDATE modifies existing data. Always use WHERE — without it, every row gets updated!
SQL
-- Update one column for a specific row
UPDATE students
SET cgpa = 9.8
WHERE name = 'Bruce Wayne';

-- Update multiple columns at once
UPDATE students
SET branch = 'IT', cgpa = 8.0
WHERE student_id = 3;

-- Update with condition on other column
UPDATE students
SET cgpa = cgpa + 0.1
WHERE branch = 'CSE' AND cgpa < 9.0;
⚠️ Danger Zone: UPDATE students SET cgpa = 0; — this wipes CGPA for every student! Always double-check your WHERE clause.
🗑
DELETE — Removing Rows
DML ▶
DELETE removes specific rows. Again, WHERE is your best friend here.
SQL
-- Delete a specific row
DELETE FROM students
WHERE student_id = 5;

-- Delete rows matching a condition
DELETE FROM students
WHERE cgpa < 5.0;

-- Delete ALL rows (but keep table structure)
DELETE FROM students;
-- Same effect as TRUNCATE, but slower + can rollback
🔍 SELECT Queries
The most powerful part of SQL — SELECT retrieves data with filtering, sorting, and limiting capabilities.
📄
SELECT Basics
Query ▶
SQL
-- Select all columns
SELECT * FROM students;

-- Select specific columns
SELECT name, branch, cgpa
FROM students;

-- Column aliases (rename in output)
SELECT
    name   AS student_name,
    cgpa   AS gpa,
    branch AS department
FROM students;

-- Remove duplicates
SELECT DISTINCT branch FROM students;
🔎
WHERE Clause — Filtering
Query ▶
WHERE filters rows before they're returned. You can combine conditions with AND, OR, NOT.
SQL
-- Basic comparison
SELECT * FROM students
WHERE branch = 'CSE';

-- Multiple conditions
SELECT * FROM students
WHERE branch = 'CSE' AND cgpa > 8.0;

-- Range check
SELECT * FROM students
WHERE cgpa BETWEEN 7.0 AND 9.0;

-- Match a list of values
SELECT * FROM students
WHERE branch IN ('CSE', 'IT', 'ECE');

-- Pattern matching (% = any chars, _ = 1 char)
SELECT * FROM students
WHERE name LIKE 'B%';       -- starts with B

SELECT * FROM students
WHERE email LIKE '%@gmail.com'; -- gmail users

-- NULL check
SELECT * FROM students
WHERE cgpa IS NULL;

SELECT * FROM students
WHERE cgpa IS NOT NULL;
🔢
ORDER BY — Sorting Results
Query ▶
ORDER BY sorts the result set. Default is ASC (ascending). You can sort by multiple columns.
SQL
-- Ascending (default) — lowest CGPA first
SELECT name, cgpa
FROM students
ORDER BY cgpa ASC;

-- Descending — topper first (rank list!)
SELECT name, cgpa
FROM students
ORDER BY cgpa DESC;

-- Sort by multiple columns
-- First sort by branch A-Z, then by CGPA high-low
SELECT name, branch, cgpa
FROM students
ORDER BY branch ASC, cgpa DESC;
Output (ORDER BY cgpa DESC)
namecgpa
Diana Prince9.5
Bruce Wayne9.2
Clark Kent8.8
Barry Allen7.9
Arthur Curry6.5
✂️
LIMIT & OFFSET
Query ▶
LIMIT restricts how many rows are returned. OFFSET skips rows — together they enable pagination.
SQL
-- Top 3 students by CGPA
SELECT name, cgpa
FROM students
ORDER BY cgpa DESC
LIMIT 3;

-- Page 2: skip first 3, show next 3
SELECT name, cgpa
FROM students
ORDER BY cgpa DESC
LIMIT 3 OFFSET 3;

-- Shorthand: LIMIT offset, count
LIMIT 3, 3;  -- skip 3, take 3
📊 Aggregate Functions
Aggregate functions compute a single result from multiple rows — counts, sums, averages. They shine with GROUP BY and HAVING.
🔢
Core Aggregate Functions
Aggregate ▶
SQL
-- COUNT: how many rows
SELECT COUNT(*) AS total_students
FROM students;

-- COUNT(column) ignores NULLs
SELECT COUNT(cgpa) AS students_with_cgpa
FROM students;

-- SUM: total of a numeric column
SELECT SUM(cgpa) AS total_cgpa
FROM students;

-- AVG: average value
SELECT AVG(cgpa) AS avg_cgpa
FROM students;

-- MAX and MIN
SELECT
    MAX(cgpa) AS highest_cgpa,
    MIN(cgpa) AS lowest_cgpa
FROM students;

-- All together
SELECT
    COUNT(*) AS total,
    AVG(cgpa) AS avg_cgpa,
    MAX(cgpa) AS best,
    MIN(cgpa) AS worst
FROM students
WHERE branch = 'CSE';
Output
totalavg_cgpabestworst
39.179.58.8
📦
GROUP BY — Group and Aggregate
Aggregate ▶
GROUP BY splits rows into groups, then applies aggregate functions per group. Any column in SELECT that's NOT an aggregate must be in GROUP BY.
SQL
-- Count students per branch
SELECT
    branch,
    COUNT(*) AS student_count,
    AVG(cgpa) AS avg_cgpa
FROM students
GROUP BY branch;

-- Max CGPA per branch, sorted
SELECT
    branch,
    MAX(cgpa) AS topper_cgpa
FROM students
GROUP BY branch
ORDER BY topper_cgpa DESC;
Output (students per branch)
branchstudent_countavg_cgpa
CSE39.17
EEE18.80
ECE17.90
MECH16.50
Rule: Every column in SELECT must either be in GROUP BY, or be wrapped in an aggregate function (COUNT, SUM, AVG, MAX, MIN).
🔍
HAVING — Filter After Grouping
Aggregate ▶
HAVING is like WHERE, but for groups. Use it to filter after GROUP BY. You can use aggregate functions inside HAVING.
SQL
-- Branches with more than 1 student
SELECT
    branch,
    COUNT(*) AS student_count
FROM students
GROUP BY branch
HAVING COUNT(*) > 1;

-- Branches where avg CGPA > 8.0
SELECT
    branch,
    AVG(cgpa) AS avg_cgpa
FROM students
GROUP BY branch
HAVING AVG(cgpa) > 8.0
ORDER BY avg_cgpa DESC;
WHERE vs HAVING:
WHERE filters rows BEFORE grouping
HAVING filters groups AFTER GROUP BY
You can use both in the same query!
SQL — WHERE + GROUP BY + HAVING together
SELECT
    branch,
    AVG(cgpa) AS avg_cgpa
FROM students
WHERE age >= 20           -- filter rows first
GROUP BY branch           -- then group
HAVING AVG(cgpa) > 7.5  -- then filter groups
ORDER BY avg_cgpa DESC;  -- then sort
SQL Execution Order (important for exams!):
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
⚡ Advanced SQL
JOINs, Subqueries, String functions, and more — the tools that make SQL truly powerful.
🔗
JOINs — Combining Tables
Advanced ▶
JOINs combine rows from two tables based on a related column (usually a FK → PK pair).
SQL
-- INNER JOIN: only matching rows from both tables
SELECT
    s.name,
    c.course_name,
    e.grade
FROM students s
INNER JOIN enrollments e
    ON s.student_id = e.student_id
INNER JOIN courses c
    ON e.course_id = c.course_id;

-- LEFT JOIN: ALL left table rows + matching right
-- Students who may NOT be enrolled anywhere
SELECT
    s.name,
    c.course_name
FROM students s
LEFT JOIN enrollments e
    ON s.student_id = e.student_id
LEFT JOIN courses c
    ON e.course_id = c.course_id;
-- Non-enrolled students get NULL for course_name

-- RIGHT JOIN: ALL right table rows + matching left
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN enrollments e
    ON s.student_id = e.student_id;
JOIN Types:
INNER JOIN — intersection (both must match)
LEFT JOIN — all from left, NULL if no match on right
RIGHT JOIN — all from right, NULL if no match on left
FULL OUTER JOIN — MySQL doesn't support this directly; use UNION of LEFT + RIGHT
🪆
Subqueries
Advanced ▶
A subquery is a query inside another query. The inner query runs first, and its result is used by the outer query.
SQL
-- Students with above-average CGPA
SELECT name, cgpa
FROM students
WHERE cgpa > (
    SELECT AVG(cgpa) FROM students
);

-- Student with highest CGPA (subquery in WHERE)
SELECT name, cgpa
FROM students
WHERE cgpa = (
    SELECT MAX(cgpa) FROM students
);

-- IN with subquery
SELECT name
FROM students
WHERE student_id IN (
    SELECT student_id
    FROM enrollments
    WHERE grade = 'A'
);
🔤
String & Date Functions
Advanced ▶
SQL — String Functions
SELECT
    UPPER(name)            AS upper_name,
    LOWER(name)            AS lower_name,
    LENGTH(name)           AS name_length,
    SUBSTRING(name, 1, 5) AS first_5_chars,
    CONCAT(name, ' — ', branch) AS full_info,
    TRIM(name)             AS trimmed,
    REPLACE(email, '@', '[at]') AS safe_email
FROM students;
SQL — Date Functions
SELECT
    NOW()                       AS current_datetime,
    CURDATE()                   AS today,
    YEAR(enrollment_date)       AS year_joined,
    MONTH(enrollment_date)      AS month_joined,
    DATEDIFF(CURDATE(), enrollment_date) AS days_enrolled
FROM students;
🔀
UNION — Combine Query Results
Advanced ▶
UNION stacks results of two SELECT statements vertically. Both queries must have the same number of columns with compatible types.
SQL
-- UNION removes duplicates
SELECT name, branch FROM students WHERE cgpa > 9
UNION
SELECT name, branch FROM students WHERE branch = 'CSE';

-- UNION ALL keeps duplicates (faster)
SELECT name, branch FROM students WHERE cgpa > 9
UNION ALL
SELECT name, branch FROM students WHERE branch = 'CSE';
📋 Cheatsheet
All commands at a glance — your quick-reference for exams and labs.

🏗 DDL

CREATE DATABASE db;
USE db;
CREATE TABLE t (...);
ALTER TABLE t ADD col;
ALTER TABLE t DROP col;
ALTER TABLE t MODIFY col;
DROP TABLE t;
TRUNCATE TABLE t;
RENAME TABLE t TO t2;

✏️ DML

INSERT INTO t (c1,c2) VALUES (v1,v2);
INSERT INTO t VALUES (...),...;
UPDATE t SET c=v WHERE cond;
DELETE FROM t WHERE cond;
DELETE FROM t; -- all rows

🔍 SELECT

SELECT * FROM t;
SELECT c1, c2 FROM t;
SELECT DISTINCT c FROM t;
SELECT c AS alias FROM t;
SELECT * FROM t WHERE cond;
SELECT * FROM t LIMIT n;
SELECT * FROM t LIMIT n OFFSET m;

🔎 WHERE Operators

= < > <= >= !=
AND OR NOT
BETWEEN v1 AND v2
IN (v1, v2, v3)
LIKE 'pattern%'
IS NULL / IS NOT NULL

📊 Aggregates

COUNT(*)
COUNT(col) -- ignores NULL
SUM(col)
AVG(col)
MAX(col)
MIN(col)

📦 GROUP BY / HAVING

GROUP BY col1, col2
HAVING COUNT(*) > 1
HAVING AVG(col) > val

WHERE → rows filter (before)
HAVING → group filter (after)

🔢 ORDER BY

ORDER BY col ASC
ORDER BY col DESC
ORDER BY c1 ASC, c2 DESC

🔗 JOINs

INNER JOIN t2 ON t1.id = t2.id
LEFT JOIN t2 ON ...
RIGHT JOIN t2 ON ...

-- self join
FROM t a, t b WHERE a.id = b.ref

🔤 String Functions

UPPER(str) LOWER(str)
LENGTH(str)
SUBSTRING(str, start, len)
CONCAT(s1, s2)
TRIM(str)
REPLACE(str, from, to)

📅 Date Functions

NOW() CURDATE()
YEAR(d) MONTH(d) DAY(d)
DATEDIFF(d1, d2)
DATE_ADD(d, INTERVAL n DAY)

⚡ Execution Order

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

🪆 Subquery

WHERE col = (SELECT MAX(col) FROM t)
WHERE col IN (SELECT id FROM t2)
WHERE col > (SELECT AVG(col) FROM t)