Cheat SheetsMySQLAdvanced Queries

Advanced Queries — Cheat Sheet

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

Cheat Sheet · AiCanCode.org
Advanced Queries
MySQL7 topicsQuick revision reference
1

Window Functions

ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, SUM OVER PARTITION — window functions compute a result across a sliding frame without collapsing rows like GROUP BY.

  • ✓Window functions compute per-row results over a window without collapsing rows (unlike GROUP BY)
  • ✓PARTITION BY divides rows into groups; ORDER BY orders rows within each partition
  • ✓ROW_NUMBER: unique; RANK: gaps on ties; DENSE_RANK: no gaps on ties
  • ✓LAG/LEAD access previous/next rows by offset — perfect for delta/trend calculations
  • ✓ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives a cumulative running total
  • ✓Window functions are evaluated after WHERE/GROUP BY but before ORDER BY/LIMIT
MySQL 8 — ROW_NUMBER, RANK, DENSE_RANK
-- Top 3 orders per customer by amount
SELECT *
FROM (
    SELECT
        customer_id,
        order_id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id
                           ORDER BY amount DESC)   AS rn
    FROM orders
) ranked
WHERE rn <= 3;

-- RANK vs DENSE_RANK vs ROW_NUMBER on tied scores
SELECT
    name,
    score,
    RANK()        OVER (ORDER BY score DESC) AS rank_w_gaps,
    DENSE_RANK()  OVER (ORDER BY score DESC) AS rank_no_gaps,
    ROW_NUMBER()  OVER (ORDER BY score DESC) AS unique_row_num
FROM leaderboard;
-- score=100: rank=1, dense=1, row=1
-- score=95:  rank=2, dense=2, row=2
-- score=95:  rank=2, dense=2, row=3  ← same score, same rank
-- score=90:  rank=4, dense=3, row=4  ← RANK skips 3
2

Common Table Expressions (CTE)

WITH clause defines reusable named result sets; recursive CTEs traverse hierarchical data (org charts, bill-of-materials) using a base case and recursive member.

  • ✓CTEs require MySQL 8.0+ — they do not exist in MySQL 5.x.
  • ✓Regular CTEs improve readability and allow reuse within the same query; they do not automatically materialise.
  • ✓Recursive CTEs use UNION ALL (not UNION) between anchor and recursive member; UNION would eliminate duplicates unnecessarily.
  • ✓Recursion terminates when the recursive member returns zero rows — ensure your join condition narrows with each iteration.
  • ✓cte_max_recursion_depth (default 1000) prevents infinite loops from data cycles; always validate input trees.
  • ✓For large CTEs referenced multiple times, MySQL materialises them into a temp table — verify with EXPLAIN FORMAT=TREE.
SQL — chained CTEs for monthly analytics
-- Monthly revenue with CTE (vs nested subquery)
WITH monthly_orders AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS month,
        SUM(total_amount)                AS revenue,
        COUNT(*)                         AS order_count
    FROM orders
    WHERE status = 'COMPLETED'
    GROUP BY month
),
monthly_avg AS (
    SELECT AVG(revenue) AS avg_revenue FROM monthly_orders
)
SELECT
    mo.month,
    mo.revenue,
    mo.order_count,
    ROUND(mo.revenue / ma.avg_revenue * 100, 1) AS pct_of_avg
FROM monthly_orders mo, monthly_avg ma
ORDER BY mo.month;

-- Multiple CTEs: chain them with commas
WITH
  active_users AS (SELECT id FROM users WHERE status = 'active'),
  recent_orders AS (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders
    WHERE user_id IN (SELECT id FROM active_users)
      AND created_at > NOW() - INTERVAL 30 DAY
    GROUP BY user_id
  )
SELECT u.email, ro.cnt
FROM users u JOIN recent_orders ro ON u.id = ro.user_id
ORDER BY ro.cnt DESC;
3

Stored Procedures

Stored procedures encapsulate multi-statement logic in the DB; use IN/OUT/INOUT parameters, cursors, loops, and conditional logic to build server-side reusable routines.

  • ✓Use DELIMITER $$ to avoid ; conflicts when defining multi-statement procedures in MySQL CLI.
  • ✓DECLARE CONTINUE HANDLER FOR NOT FOUND is required to detect cursor exhaustion — without it the loop runs indefinitely.
  • ✓Stored procedures do not reduce query parsing overhead in MySQL (unlike some other databases) — benefit is fewer round-trips.
  • ✓SIGNAL SQLSTATE '45000' raises a user-defined exception; RESIGNAL re-throws the caught exception to the caller.
  • ✓Test stored procedures carefully: they run in the DB context, are harder to unit-test than application code.
  • ✓Prefer application-layer logic with Flyway-managed SP scripts in production for testability and deployment control.
SQL — stored procedure with IN/OUT parameters
DELIMITER $$

CREATE PROCEDURE calculate_customer_ltv(
    IN  p_customer_id BIGINT,
    OUT p_ltv         DECIMAL(12, 2),
    OUT p_order_count INT
)
BEGIN
    -- Local variable declarations must come before logic
    DECLARE v_total_revenue DECIMAL(12, 2) DEFAULT 0.00;
    DECLARE v_count INT DEFAULT 0;

    -- Query into local variables
    SELECT SUM(total), COUNT(*)
    INTO   v_total_revenue, v_count
    FROM   orders
    WHERE  customer_id = p_customer_id
      AND  status NOT IN ('CANCELLED', 'REFUNDED');

    -- Handle NULL (no orders)
    SET p_ltv         = COALESCE(v_total_revenue, 0.00);
    SET p_order_count = COALESCE(v_count, 0);
END$$

DELIMITER ;

-- Call the procedure
CALL calculate_customer_ltv(42, @ltv, @orders);
SELECT @ltv AS lifetime_value, @orders AS total_orders;
4

Triggers & User-Defined Functions

BEFORE/AFTER INSERT/UPDATE/DELETE triggers enforce cross-table logic; deterministic scalar UDFs encapsulate reusable computation safe for use in queries.

  • ✓BEFORE triggers can modify NEW row values (validation, defaults); AFTER triggers cannot modify the row but can audit
  • ✓Trigger errors roll back the parent DML transaction — never let a trigger silently fail in a AFTER trigger
  • ✓DETERMINISTIC UDFs tell the optimiser inputs always produce the same output — required for use in generated column indexes
  • ✓Triggers are invisible to application code — document them in schema migrations and test them explicitly
  • ✓In microservices, prefer Hibernate @EntityListeners or application-layer logic over triggers for testability
  • ✓SHOW TRIGGERS and information_schema.TRIGGERS are the primary tools for discovering existing trigger definitions
SQL — BEFORE INSERT validation trigger and AFTER triggers for audit log
-- BEFORE INSERT trigger: set created_at and validate
DELIMITER $$
CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
    IF NEW.total <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Order total must be positive';
    END IF;
END$$
DELIMITER ;

-- AFTER INSERT trigger: write audit log
DELIMITER $$
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit_log (order_id, action, new_status, changed_at)
    VALUES (NEW.id, 'CREATED', NEW.status, NOW());
END$$
DELIMITER ;

-- AFTER UPDATE trigger: audit status changes
DELIMITER $$
CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status THEN
        INSERT INTO order_audit_log (order_id, action, old_status, new_status, changed_at)
        VALUES (NEW.id, 'STATUS_CHANGE', OLD.status, NEW.status, NOW());
    END IF;
END$$
DELIMITER ;
5

Views

Views are named stored SELECT queries; updatable views allow DML on simple single-table views; use views to simplify complex joins and enforce column-level security.

  • ✓Views are virtual tables backed by a stored SELECT — not materialised; every query re-executes the underlying SELECT
  • ✓Grant permissions on the view, not the base table, to enforce column- and row-level access control for specific roles
  • ✓Updatable views require single base table and no aggregates, DISTINCT, GROUP BY, UNION, or select-list subqueries
  • ✓WITH CHECK OPTION blocks inserts/updates that would make the row invisible through the view's WHERE filter
  • ✓ALGORITHM=MERGE inlines the view and allows index use on the outer query; ALGORITHM=TEMPTABLE blocks predicate pushdown
  • ✓Views with GROUP BY, aggregates, or DISTINCT always use TEMPTABLE — check EXPLAIN for DERIVED to identify this
SQL — creating views and enforcing column-level security
-- View exposing only non-sensitive order columns (column-level security)
CREATE OR REPLACE VIEW v_orders_public AS
SELECT order_id, customer_id, order_date, status, total_amount
FROM orders
WHERE deleted_at IS NULL;

-- Grant SELECT on view only — reporting_user cannot access base table
GRANT SELECT ON shop.v_orders_public TO 'reporting_user'@'%';

-- Stacked view — joins over another view
CREATE OR REPLACE VIEW v_customer_orders AS
SELECT c.name, c.email, o.order_id, o.total_amount
FROM customers c
JOIN v_orders_public o ON c.id = o.customer_id;

-- Query view like a table
SELECT * FROM v_customer_orders WHERE total_amount > 1000;

-- Inspect view definition
SHOW CREATE VIEW v_orders_public;
6

String Functions

CONCAT, SUBSTRING, LENGTH, REPLACE, UPPER/LOWER, TRIM, REGEXP_REPLACE, and FORMAT are core string functions; MySQL is case-insensitive for string comparison by default (COLLATION-dependent).

  • ✓CONCAT_WS is safer than CONCAT when some arguments may be NULL (NULLs are skipped).
  • ✓Use CHAR_LENGTH for character count in multibyte encodings; LENGTH returns byte count.
  • ✓REGEXP_REPLACE and REGEXP_SUBSTR require MySQL 8.0+.
  • ✓Most utf8mb4_unicode_ci comparisons are case-insensitive; use BINARY for case-sensitive search.
  • ✓FORMAT(n, d) produces locale-formatted numbers; LPAD/RPAD produce fixed-width strings.
  • ✓String function calls in WHERE clauses prevent index use — avoid on indexed columns.
SQL — CONCAT, SUBSTRING, LENGTH, TRIM
-- CONCAT and CONCAT_WS
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT CONCAT_WS(', ', city, state, country) AS address FROM locations;
-- CONCAT_WS skips NULL values; CONCAT returns NULL if any arg is NULL

-- SUBSTRING
SELECT SUBSTRING('Hello World', 1, 5);   -- 'Hello'  (1-indexed)
SELECT LEFT('Hello World', 5);            -- 'Hello'
SELECT RIGHT('Hello World', 5);           -- 'World'
SELECT MID('Hello World', 7, 5);          -- 'World'  (alias for SUBSTRING)

-- Length: bytes vs characters
SELECT LENGTH('こんにちは');       -- 15 (5 chars × 3 bytes each in utf8)
SELECT CHAR_LENGTH('こんにちは');  -- 5  (5 characters)
-- Always use CHAR_LENGTH for user-visible string length checks

-- Case and trim
SELECT UPPER('hello WORLD');   -- 'HELLO WORLD'
SELECT LOWER('HELLO world');   -- 'hello world'
SELECT TRIM('  hello  ');      -- 'hello'
SELECT LTRIM('  hello');       -- 'hello'
SELECT RTRIM('hello  ');       -- 'hello'
7

Date & Time Functions

NOW(), CURDATE(), DATE_ADD(), DATEDIFF(), DATE_FORMAT(), UNIX_TIMESTAMP(), and STR_TO_DATE() are essential for date arithmetic, formatting, and timezone-aware applications.

  • ✓NOW() is evaluated once per statement; SYSDATE() is evaluated at call time — matters inside loops
  • ✓Never apply functions to indexed date columns in WHERE — use range comparisons instead
  • ✓DATEDIFF() returns integer days; TIMESTAMPDIFF(UNIT, start, end) supports any unit
  • ✓DATE_FORMAT uses % specifiers: %Y (4-digit year), %m (month), %d (day), %H:%i:%s (time)
  • ✓TIMESTAMP is auto-converted from/to session timezone; DATETIME stores verbatim
  • ✓Store all timestamps in UTC in the DB; apply timezone conversion in the application layer
SQL — date arithmetic and index-friendly comparisons
-- Current date/time
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();

-- Date arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)  AS next_week,
       DATE_SUB(NOW(), INTERVAL 1 MONTH) AS last_month;

-- Difference
SELECT DATEDIFF('2025-12-31', '2025-01-01')        AS days_diff,   -- 364
       TIMESTAMPDIFF(MONTH, '2024-01-01', NOW())    AS months_since;

-- Index-friendly range query (avoids function on column)
SELECT * FROM orders
WHERE created_at >= CURDATE()
  AND created_at <  CURDATE() + INTERVAL 1 DAY;
Learn this free with Aria, your AI tutor → AiCanCode.org/learn/mysql