MySQL Cheat Sheet

Numeric

String

Date & Time

DATETIME vs TIMESTAMP: TIMESTAMP auto-converts to/from UTC. Use TIMESTAMP for "when it happened", DATETIME for "scheduled time".

JSON & Spatial

CREATE TABLE

Constraints

ALTER TABLE

DROP, TRUNCATE, RENAME

INSERT

UPDATE

DELETE

SELECT Anatomy

💡 WHERE vs HAVING: WHERE filters before grouping (no aggregates). HAVING filters after (can use aggregates).

WHERE Operators

LIMIT & Pagination

⚠️ OFFSET gets slow at high pages. Use cursor pagination for large tables.

DISTINCT & ORDER BY

JOIN Types Visual

INNER JOIN

LEFT JOIN ⭐

SELF JOIN

CROSS JOIN & USING

Aggregate Functions

GROUP BY Examples

ROLLUP (Subtotals)

Subquery Types

Derived Table (inline view)

CTEs — WITH clause (MySQL 8+) 🔥

Syntax

Ranking Functions

Value Functions

PARTITION BY + Running Total ⭐

NTILE & PERCENT_RANK

Create & Manage

EXPLAIN — Query Analysis 🔥

Index Rules & Tips

Full-Text Search

ACID Properties

Transaction Syntax ⭐

Isolation Levels

Locking

String Functions

Date Functions 🔥

Numeric & Math

Conditional Functions

User Management

GRANT & REVOKE

Common Traps

NULL comparisons always fail. WHERE col = NULL returns 0 rows. Always use IS NULL / IS NOT NULL.

UPDATE/DELETE without WHERE wipes all rows. Always test with SELECT first. Use LIMIT as a safety belt.

LIKE '%value%' disables index usage. Leading wildcard = full table scan. Use FULLTEXT index instead.

Functions on indexed columns break indexes. WHERE YEAR(created_at) = 2024 → full scan. Use: WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

FLOAT/DOUBLE is imprecise. 0.1 + 0.2 ≠ 0.3 in floating point. Always use DECIMAL for money.

GROUP BY must include non-aggregated SELECT cols. MySQL's ONLY_FULL_GROUP_BY mode (default in 5.7+) enforces this. Don't rely on legacy permissive mode.

ON DELETE CASCADE can wipe huge data chains silently. Document all FK cascades. Test with small datasets first.

utf8 in MySQL ≠ real UTF-8. MySQL's "utf8" charset is 3-byte (breaks emoji). Always use utf8mb4 with utf8mb4_unicode_ci.

Interview Quick Fire