Batman's complete guide to DDL, DML, aggregate functions, GROUP BY, ORDER BY, JOINs and everything in between.
CREATE, ALTER, DROP — define your schema
INSERT, UPDATE, DELETE — manipulate data
SELECT, WHERE, ORDER BY, LIMIT
COUNT, SUM, AVG, GROUP BY, HAVING
JOINs, Subqueries, Constraints
All commands at a glance
CREATE DATABASE college; USE college; -- Check all databases SHOW DATABASES; -- Delete a database (careful!) DROP DATABASE college;
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 );
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
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
-- 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: 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;
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 );
CASCADE — delete child rows when parent is deletedSET NULL — set FK to NULL when parent is deletedRESTRICT — prevent deletion if child rows exist
-- 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 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;
UPDATE students SET cgpa = 0; — this wipes CGPA for every student! Always double-check your WHERE clause.-- 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 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;
-- 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;
-- 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;
| name | cgpa |
|---|---|
| Diana Prince | 9.5 |
| Bruce Wayne | 9.2 |
| Clark Kent | 8.8 |
| Barry Allen | 7.9 |
| Arthur Curry | 6.5 |
-- 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
-- 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';
| total | avg_cgpa | best | worst |
|---|---|---|---|
| 3 | 9.17 | 9.5 | 8.8 |
-- 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;
| branch | student_count | avg_cgpa |
|---|---|---|
| CSE | 3 | 9.17 |
| EEE | 1 | 8.80 |
| ECE | 1 | 7.90 |
| MECH | 1 | 6.50 |
-- 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 filters rows BEFORE groupingHAVING filters groups AFTER GROUP BYSELECT 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
-- 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;
-- 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' );
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;
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 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';
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;
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 * 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;
= < > <= >= !=
AND OR NOT
BETWEEN v1 AND v2
IN (v1, v2, v3)
LIKE 'pattern%'
IS NULL / IS NOT NULL
COUNT(*)
COUNT(col) -- ignores NULL
SUM(col)
AVG(col)
MAX(col)
MIN(col)
GROUP BY col1, col2
HAVING COUNT(*) > 1
HAVING AVG(col) > val
WHERE → rows filter (before)
HAVING → group filter (after)
ORDER BY col ASC
ORDER BY col DESC
ORDER BY c1 ASC, c2 DESC
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
UPPER(str) LOWER(str)
LENGTH(str)
SUBSTRING(str, start, len)
CONCAT(s1, s2)
TRIM(str)
REPLACE(str, from, to)
NOW() CURDATE()
YEAR(d) MONTH(d) DAY(d)
DATEDIFF(d1, d2)
DATE_ADD(d, INTERVAL n DAY)
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT
WHERE col = (SELECT MAX(col) FROM t)
WHERE col IN (SELECT id FROM t2)
WHERE col > (SELECT AVG(col) FROM t)