Fundamentals — Cheat Sheet

MySQL · 5 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org

Fundamentals

MySQL5 topicsQuick revision reference

MySQL Architecture

MySQL's architecture layers: connection handling (thread pool), parser/optimiser, storage engine API, and pluggable engines (InnoDB, MyISAM) — understanding each layer aids query tuning.

✓MySQL has three layers: connection, server (parser + optimiser), and pluggable storage engines.
✓InnoDB is the only production-grade engine: ACID, row-level locks, MVCC, foreign keys.
✓The buffer pool is InnoDB's in-memory page cache — size it to 70-80% of available RAM.
✓Redo log provides crash durability (write-ahead log); undo log enables MVCC + rollback.
✓The query optimiser selects indexes and join order — use EXPLAIN to inspect its decisions.
✓EXPLAIN ANALYZE (8.0.18+) executes the query and shows actual vs estimated row counts.

SQL + ASCII — MySQL architecture layers

# Architecture overview:
#
# ┌─────────────────────────────────────────────────┐
# │  Client (JDBC, mysql CLI, Workbench)             │
# └────────────────┬────────────────────────────────┘
#                  │ TCP / Unix socket
# ┌────────────────▼────────────────────────────────┐
# │  Connection Layer                                │
# │  • Thread pool / one thread per connection       │
# │  • Authentication (caching_sha2_password)        │
# │  • SSL/TLS termination                           │
# └────────────────┬────────────────────────────────┘
# ┌────────────────▼────────────────────────────────┐
# │  Server Layer                                    │
# │  • SQL Parser (validates syntax)                 │
# │  • Query Rewriter (transforms e.g. views)        │
# │  • Query Optimiser (chooses indexes, join order) │
# │  • Execution Engine (iterates rows)              │
# └────────────────┬────────────────────────────────┘
#                  │ Storage Engine API (handler interface)
# ┌────────────────▼────────────────────────────────┐
# │  Storage Engines                                 │
# │  InnoDB (default) │ MyISAM │ MEMORY │ CSV │ ...  │
# └─────────────────────────────────────────────────┘

SHOW ENGINES;   -- list available storage engines
SHOW ENGINE INNODB STATUSG  -- detailed InnoDB runtime info

MySQL Data Types

Choose data types precisely: INT vs BIGINT, VARCHAR vs TEXT, DATETIME vs TIMESTAMP (timezone-aware), DECIMAL for exact numerics, and ENUM for constrained string sets.

✓Use the smallest integer type that fits the range — TINYINT saves 7 bytes vs BIGINT per row.
✓Use DECIMAL for money/exact values — FLOAT/DOUBLE are approximate and will cause rounding errors.
✓VARCHAR is variable-length; CHAR is fixed-length and padded — use CHAR for codes and hashes.
✓TEXT/BLOB cannot have DEFAULT values and cannot be fully indexed (only prefix indexes).
✓TIMESTAMP (UTC, 4 bytes, 2038 limit) vs DATETIME (literal, 8 bytes, no timezone) — choose carefully.
✓utf8mb4 is the correct charset for full Unicode including emoji; plain utf8 in MySQL only has 3-byte chars.

SQL — numeric types and money storage

-- Integer type sizes
-- TINYINT:   -128 to 127          (unsigned: 0–255)        1 byte
-- SMALLINT:  -32,768 to 32,767    (unsigned: 0–65535)      2 bytes
-- MEDIUMINT: -8M to 8M                                     3 bytes
-- INT:       -2B to 2.1B          (unsigned: 0–4.3B)       4 bytes
-- BIGINT:    -9.2×10^18 to 9.2×10^18                       8 bytes

CREATE TABLE products (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  -- IDs can be large
    stock       MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,    -- max 16M units
    weight_g    SMALLINT UNSIGNED,                         -- max 65 kg (in grams)
    views       INT UNSIGNED NOT NULL DEFAULT 0,           -- up to 4B views
    -- Money: use DECIMAL(precision, scale) — NEVER FLOAT
    price       DECIMAL(10, 2) NOT NULL,   -- 99,999,999.99 max
    tax_rate    DECIMAL(5, 4) NOT NULL,    -- 0.1875 = 18.75%
    -- Floating point — only for scientific/imprecise values
    latitude    FLOAT,
    longitude   FLOAT,
    PRIMARY KEY (id)
);

-- ✗ Float precision problem with money:
-- SELECT 0.1 + 0.2;  → 0.30000000000000004 (approximate!)
-- ✓ Use DECIMAL for exact values

DDL — CREATE, ALTER, DROP

Data Definition Language defines schema; CREATE TABLE, ALTER TABLE (add/modify columns), DROP TABLE, and TRUNCATE are non-transactional DDL statements in MySQL.

✓DDL statements (CREATE, ALTER, DROP, TRUNCATE) are auto-committed and cannot be rolled back.
✓MySQL 8.0 INSTANT algorithm adds columns to InnoDB tables without a table copy.
✓ALGORITHM=INPLACE, LOCK=NONE requests an online DDL with an error if unsupported.
✓TRUNCATE is faster than DELETE * for clearing tables but resets AUTO_INCREMENT.
✓Foreign key constraints prevent DROP TABLE on referenced tables — use foreign_key_checks=0 during migrations.
✓Always use batch DELETE with LIMIT to avoid long-running locks on large tables.

SQL — CREATE TABLE with constraints and indexes

CREATE TABLE orders (
    id            BIGINT          NOT NULL AUTO_INCREMENT,
    customer_id   BIGINT          NOT NULL,
    status        ENUM('DRAFT','PLACED','SHIPPED','DELIVERED','CANCELLED')
                                  NOT NULL DEFAULT 'DRAFT',
    total         DECIMAL(10, 2)  NOT NULL,
    notes         TEXT,                          -- nullable TEXT column
    created_at    DATETIME(3)     NOT NULL       -- millisecond precision
                                  DEFAULT CURRENT_TIMESTAMP(3),
    updated_at    DATETIME(3)     NOT NULL
                                  DEFAULT CURRENT_TIMESTAMP(3)
                                  ON UPDATE CURRENT_TIMESTAMP(3),

    PRIMARY KEY (id),
    INDEX idx_customer_status (customer_id, status),   -- composite index
    INDEX idx_created_at (created_at),

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

DML — INSERT, UPDATE, DELETE

Data Manipulation Language modifies rows; INSERT ... ON DUPLICATE KEY UPDATE, UPDATE with JOIN, and DELETE with LIMIT are common MySQL-specific DML patterns.

✓Multi-row INSERT is significantly faster than repeated single-row INSERTs.
✓INSERT ... ON DUPLICATE KEY UPDATE provides atomic upsert on PK or unique key conflict.
✓UPDATE with JOIN lets you update one table using conditions from another.
✓Always include WHERE in UPDATE/DELETE — accidental full-table modifications are hard to undo.
✓Batch DELETE with LIMIT prevents long-running locks on large tables.
✓SELECT ... FOR UPDATE acquires a pessimistic row lock; use for read-modify-write patterns.

SQL — INSERT variants

-- Single row
INSERT INTO products (name, price, stock)
VALUES ('Widget', 9.99, 100);

-- Multi-row (single statement = faster than N individual INSERTs)
INSERT INTO products (name, price, stock) VALUES
    ('Gadget',  19.99, 50),
    ('Doohickey', 4.99, 200),
    ('Thingamajig', 29.99, 10);

-- Upsert — insert or update on PK/unique key conflict
INSERT INTO product_stock (product_id, quantity)
VALUES (42, 100)
ON DUPLICATE KEY UPDATE
    quantity = quantity + VALUES(quantity);
-- Atomic: if product_id 42 exists, increments quantity by 100

-- INSERT IGNORE — skip rows that would violate unique constraint
INSERT IGNORE INTO product_views (product_id, user_id)
VALUES (42, 99);   -- no error if (42,99) already exists

-- INSERT ... SELECT — copy data between tables
INSERT INTO orders_archive SELECT * FROM orders
WHERE created_at < NOW() - INTERVAL 1 YEAR;

SELECT Queries & Filtering

SELECT with WHERE, LIKE, BETWEEN, IN, IS NULL, and REGEXP filters rows; column aliases, DISTINCT, and case-insensitive string comparison (COLLATION) are foundational query skills.

✓Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
✓Always use IS NULL / IS NOT NULL — never = NULL (always returns NULL, not true/false).
✓LIKE uses % (any chars) and _ (one char); REGEXP supports full patterns.
✓DISTINCT deduplicates; ORDER BY accepts multiple columns; LIMIT restricts rows.
✓Column aliases are available in ORDER BY/HAVING but NOT in WHERE — use the expression.
✓Keyset (cursor) pagination is far more efficient than OFFSET for large pages.

SQL — WHERE clause filtering examples

-- Basic comparisons
SELECT id, name, price
FROM products
WHERE price BETWEEN 10.00 AND 50.00   -- inclusive
  AND category IN ('Books', 'Electronics', 'Toys')
  AND discontinued = FALSE;

-- NULL handling — always use IS NULL / IS NOT NULL, never = NULL
SELECT * FROM orders WHERE shipped_at IS NULL;        -- not yet shipped
SELECT * FROM orders WHERE shipped_at IS NOT NULL;    -- already shipped

-- LIKE — % matches zero or more chars; _ matches exactly one
SELECT * FROM customers WHERE email LIKE '%@gmail.com';   -- ends with
SELECT * FROM products WHERE sku LIKE 'PROD-___-2024';    -- 3-char middle

-- REGEXP — full regex support
SELECT * FROM products WHERE name REGEXP '^(iPhone|iPad|MacBook)';

-- Subquery in WHERE
SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE tier = 'GOLD'
);

-- EXPLAIN — check if index is used
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'PENDING';
-- key: NULL means no index → consider adding one

Learn this free with Aria, your AI tutor → AiCanCode.org/learn/mysql

Cheat Sheet

Fundamentals

MySQL

5 topics · swipe →

AiCanCode.org

Fundamentals

MySQL Architecture

✓MySQL has three layers: connection, server (parser + optimiser), and pluggable storage engines.
✓InnoDB is the only production-grade engine: ACID, row-level locks, MVCC, foreign keys.
✓The buffer pool is InnoDB's in-memory page cache — size it to 70-80% of available RAM.
✓Redo log provides crash durability (write-ahead log); undo log enables MVCC + rollback.
✓The query optimiser selects indexes and join order — use EXPLAIN to inspect its decisions.

# Architecture overview:
#
# ┌─────────────────────────────────────────────────┐
# │  Client (JDBC, mysql CLI, Workbench)             │
# └────────────────┬────────────────────────────────┘
#                  │ TCP / Unix socket
# ┌────────────────▼────────────────────────────────┐
# │  Connection Layer                                │
# │  • Thread pool / one thread per connection       │
…

AiCanCode.org

Fundamentals

MySQL Data Types

✓Use the smallest integer type that fits the range — TINYINT saves 7 bytes vs BIGINT per row.
✓Use DECIMAL for money/exact values — FLOAT/DOUBLE are approximate and will cause rounding errors.
✓VARCHAR is variable-length; CHAR is fixed-length and padded — use CHAR for codes and hashes.
✓TEXT/BLOB cannot have DEFAULT values and cannot be fully indexed (only prefix indexes).
✓TIMESTAMP (UTC, 4 bytes, 2038 limit) vs DATETIME (literal, 8 bytes, no timezone) — choose carefully.

-- Integer type sizes
-- TINYINT:   -128 to 127          (unsigned: 0–255)        1 byte
-- SMALLINT:  -32,768 to 32,767    (unsigned: 0–65535)      2 bytes
-- MEDIUMINT: -8M to 8M                                     3 bytes
-- INT:       -2B to 2.1B          (unsigned: 0–4.3B)       4 bytes
-- BIGINT:    -9.2×10^18 to 9.2×10^18                       8 bytes

CREATE TABLE products (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  -- IDs can be large
…

AiCanCode.org

Fundamentals

DDL — CREATE, ALTER, DROP

✓DDL statements (CREATE, ALTER, DROP, TRUNCATE) are auto-committed and cannot be rolled back.
✓MySQL 8.0 INSTANT algorithm adds columns to InnoDB tables without a table copy.
✓ALGORITHM=INPLACE, LOCK=NONE requests an online DDL with an error if unsupported.
✓TRUNCATE is faster than DELETE * for clearing tables but resets AUTO_INCREMENT.
✓Foreign key constraints prevent DROP TABLE on referenced tables — use foreign_key_checks=0 during migrations.

CREATE TABLE orders (
    id            BIGINT          NOT NULL AUTO_INCREMENT,
    customer_id   BIGINT          NOT NULL,
    status        ENUM('DRAFT','PLACED','SHIPPED','DELIVERED','CANCELLED')
                                  NOT NULL DEFAULT 'DRAFT',
    total         DECIMAL(10, 2)  NOT NULL,
    notes         TEXT,                          -- nullable TEXT column
    created_at    DATETIME(3)     NOT NULL       -- millisecond precision
                                  DEFAULT CURRENT_TIMESTAMP(3),
…

AiCanCode.org

Fundamentals

DML — INSERT, UPDATE, DELETE

✓Multi-row INSERT is significantly faster than repeated single-row INSERTs.
✓INSERT ... ON DUPLICATE KEY UPDATE provides atomic upsert on PK or unique key conflict.
✓UPDATE with JOIN lets you update one table using conditions from another.
✓Always include WHERE in UPDATE/DELETE — accidental full-table modifications are hard to undo.
✓Batch DELETE with LIMIT prevents long-running locks on large tables.

-- Single row
INSERT INTO products (name, price, stock)
VALUES ('Widget', 9.99, 100);

-- Multi-row (single statement = faster than N individual INSERTs)
INSERT INTO products (name, price, stock) VALUES
    ('Gadget',  19.99, 50),
    ('Doohickey', 4.99, 200),
    ('Thingamajig', 29.99, 10);
…

AiCanCode.org

Fundamentals

SELECT Queries & Filtering

✓Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
✓Always use IS NULL / IS NOT NULL — never = NULL (always returns NULL, not true/false).
✓LIKE uses % (any chars) and _ (one char); REGEXP supports full patterns.
✓DISTINCT deduplicates; ORDER BY accepts multiple columns; LIMIT restricts rows.
✓Column aliases are available in ORDER BY/HAVING but NOT in WHERE — use the expression.

-- Basic comparisons
SELECT id, name, price
FROM products
WHERE price BETWEEN 10.00 AND 50.00   -- inclusive
  AND category IN ('Books', 'Electronics', 'Toys')
  AND discontinued = FALSE;

-- NULL handling — always use IS NULL / IS NOT NULL, never = NULL
SELECT * FROM orders WHERE shipped_at IS NULL;        -- not yet shipped
…

AiCanCode.org

Master Fundamentals

Full lessons + an AI tutor, free.

AiCanCode.org

/learn/mysql