SQL Tutorials

MySQL 8 from first connection to production — schema design and data types, SELECT and joins, aggregation, CTEs and window functions, indexes and query plans, transactions and deadlocks, backup, binlog and replication, every query run against a real pizza-ordering database.

• MySQL Interview – Advanced Queries
  The whiteboard questions that separate people who have written SQL from people who have read about it. Second-highest value, top N per group, finding and deleting duplicates, a running total, gaps in a sequence, a pivot with conditional aggregation, rows in one table with no match in another, and month-over-month growth — each one solved, then explained, then checked against a real database.
• MySQL Interview – Fundamentals
  The questions almost every SQL interview asks, answered the way you would say them out loud. The difference between the joins, WHERE versus HAVING, DELETE versus TRUNCATE versus DROP, what an index actually is and its cost, primary versus unique key, NULL behaviour, the ACID properties with an example each, normalization up to third normal form, and UNION versus UNION ALL.
• MySQL – Running Queries in Production Safely
  The habits that keep an ad-hoc query from becoming an incident. Reading before writing, wrapping a change in a transaction you can roll back, sql_safe_updates, always EXPLAIN before running something new against a big table, deleting and updating in batches, MAX_EXECUTION_TIME, finding and killing a runaway query with SHOW PROCESSLIST, the slow query log, and why a long ALTER TABLE needs a plan.
• MySQL – Replication
  Running a second copy of the database. Asynchronous replication and what it costs you, setting up a primary and a replica with GTIDs from scratch, reading SHOW REPLICA STATUS and the one field that tells you it is behind, replication lag and the read-after-write bug it causes in an application, semi-synchronous replication, and read/write splitting — plus when a replica is not a backup.
• MySQL – The Binary Log
  The log that makes replication and point-in-time recovery possible. What the binlog records and what it does not, ROW versus STATEMENT versus MIXED format and why ROW is the default now, reading one with mysqlbinlog, finding the statement that deleted the rows, replaying up to a position or a timestamp to recover, expire_logs_days and the disk it fills if you forget.
• MySQL – Backup and Restore with mysqldump
  Taking a backup you can actually restore from. mysqldump for one database, several, or all, --single-transaction and why omitting it locks your tables, schema-only and data-only dumps, --routines and --events which are NOT included by default, restoring, why a dump is a logical backup and what that costs at size, and the only test that matters — restoring it somewhere and looking.
• MySQL – Reset the Root Password
  Locked out of your own server. The `--skip-grant-tables` procedure step by step on MySQL 8, why FLUSH PRIVILEGES is required before ALTER USER will work in that mode, the `--init-file` alternative that avoids opening the server up at all, doing it in a Docker container where the answer is usually simpler, and the check to run afterwards to confirm you did not leave the server unauthenticated.
• MySQL – Users, Privileges and Roles
  Not connecting as root. CREATE USER and why `'app'@'%'` and `'app'@'localhost'` are two different accounts, GRANT at the database, table and column level, the principle of least privilege applied to an application account, REVOKE, roles in MySQL 8 and why they need activating, `mysql_native_password` versus `caching_sha2_password`, and reading SHOW GRANTS to audit what an account can do.
• MySQL – INFORMATION_SCHEMA
  Querying the database about itself. TABLES, COLUMNS, STATISTICS, KEY_COLUMN_USAGE and REFERENTIAL_CONSTRAINTS, finding every foreign key pointing at a table before you drop it, listing indexes that duplicate each other, estimating table and index size on disk, why TABLE_ROWS is an estimate and not a count, and how SHOW commands map onto the same data.
• MySQL – Scheduled Events
  Cron inside the database. The event_scheduler variable that is OFF by default so your first event never runs, CREATE EVENT with AT and with EVERY, STARTS and ENDS, ON COMPLETION PRESERVE, altering and disabling one, where errors go, what happens on a replica, and the question to settle first — whether this belongs in the database at all or in the scheduler you already operate.
• MySQL – Triggers
  Running a statement automatically when a row changes. BEFORE and AFTER, INSERT, UPDATE and DELETE, the NEW and OLD rows, writing an audit trail, keeping a derived total in step, the limitations that bite — a trigger cannot touch its own table, and it does not fire for TRUNCATE — and the real argument against them: logic that runs invisibly is logic nobody debugging your application will think to look for.
• MySQL – Stored Procedures and Functions
  Code that lives in the database. DELIMITER and why you need it, CREATE PROCEDURE, IN / OUT / INOUT parameters, variables, IF and CASE, WHILE and REPEAT loops, cursors and the handler that stops one looping forever, stored functions and how they differ from procedures, error handling with DECLARE ... HANDLER, and an honest look at when to put logic here rather than in the application.
• MySQL – Views
  Naming a query so the rest of the schema can use it. CREATE VIEW and CREATE OR REPLACE VIEW, what a view costs at query time, MERGE versus TEMPTABLE and why the second one loses your indexes, updatable views and the conditions they have to meet, WITH CHECK OPTION, using a view as a permission boundary, and why MySQL has no materialized views and what people do instead.
• MySQL – Deadlocks
  Two transactions each waiting for a lock the other holds. Reproducing one in two terminals so you can see it happen, reading SHOW ENGINE INNODB STATUS to find out which statements were involved, the difference between a deadlock and a lock wait timeout, gap locks and why REPEATABLE READ produces deadlocks READ COMMITTED does not, and the two fixes that actually work: consistent lock ordering, and retrying.
• MySQL – Transactions
  Making several statements succeed or fail together. START TRANSACTION, COMMIT and ROLLBACK, autocommit and why it is on by default, savepoints, the four isolation levels and what each one lets through, why MySQL's default is REPEATABLE READ when most databases use READ COMMITTED, SELECT ... FOR UPDATE, and the DDL statement that silently commits your transaction out from under you.
• MySQL – Storing Hierarchies with a Closure Table
  Trees in a relational database. The adjacency list everyone starts with and the query that makes it painful, MySQL 8's recursive CTE which fixes most of that, and the closure table — one row per ancestor-descendant pair — which trades write cost and storage for subtree reads that are a single indexed lookup. When each one is the right answer, with the numbers.
• MySQL – EXPLAIN and Reading a Query Plan
  Finding out what the optimizer decided instead of guessing. Reading EXPLAIN column by column, what the `type` values mean from `ALL` to `const` and which ones should worry you, `key` and `rows` and how rough the estimate is, EXPLAIN ANALYZE for actual timings rather than predictions, EXPLAIN FORMAT=JSON, and a worked example taking one slow query from a full scan to an index lookup.
• MySQL – Indexes
  The single biggest lever on query speed. What a B-tree index is and what it costs on every write, the clustered primary key and why a secondary index lookup is two lookups, composite indexes and the leftmost-prefix rule that decides whether yours gets used, covering indexes, why an index on a low-cardinality column is often ignored, and the four common ways to write a query that cannot use the index you just added.
• MySQL – Full-Text Search
  Searching text properly instead of with `LIKE '%...%'`. Creating a FULLTEXT index, MATCH ... AGAINST in natural language mode, boolean mode with `+`, `-` and `*`, relevance scores and ordering by them, the default minimum word length and stopword list that make short searches return nothing, and where full-text search stops being enough and Elasticsearch starts.
• MySQL – JSON Columns
  MySQL's native JSON type, which is not just a string. Storing and validating JSON, reading values with `->` and `->>`, JSON_EXTRACT, JSON_UNQUOTE, JSON_SET, JSON_ARRAY and JSON_OBJECT, JSON_TABLE for turning a document into rows, indexing a JSON path with a generated column — the only way to index one — and the question worth asking first: should this be a column instead?
• MySQL – Server and Session Functions
  The small functions that tell you where you are and what you are connected to. VERSION(), DATABASE(), USER() and CURRENT_USER() and why they differ, CONNECTION_ID(), @@variables for session and global settings, SHOW STATUS and SHOW VARIABLES, BENCHMARK() for a crude timing, and SLEEP() — useful for reproducing a lock wait on purpose.
• MySQL – DATE_FORMAT and Date Functions
  Formatting and calculating with dates. DATE_FORMAT and the specifier table you will keep coming back to, NOW() versus CURDATE() versus SYSDATE(), DATE_ADD and DATE_SUB, DATEDIFF and TIMESTAMPDIFF, EXTRACT, LAST_DAY, STR_TO_DATE for parsing, and why grouping by DATE_FORMAT(created_at, '%Y-%m') is convenient and quietly prevents the index on created_at from being used.
• MySQL – Built-in Functions
  The functions you reach for weekly. String work with CONCAT, CONCAT_WS, SUBSTRING, TRIM, REPLACE, UPPER and LOWER, LPAD — the one the schema uses to backfill UUIDs — numbers with ROUND, CEIL, FLOOR, ABS and MOD, why ROUND on a DECIMAL and on a FLOAT do not agree, GROUP_CONCAT for rolling a group into one string, and UUID() and RAND().
• MySQL – LAST_INSERT_ID
  Getting the AUTO_INCREMENT id of the row you just inserted, which is exactly what you need when saving an order and then its line items. Why LAST_INSERT_ID() is per-connection and therefore safe under concurrency, what it returns after a multi-row INSERT, why it does not see a trigger's own inserts, and how JDBC's `getGeneratedKeys` exposes the same thing to Java.
• MySQL – DELETE
  Removing rows, and the several ways to regret it. DELETE with WHERE, DELETE with a JOIN, deleting in batches so a big cleanup does not hold one enormous transaction, TRUNCATE versus DELETE and what each does to AUTO_INCREMENT, what ON DELETE CASCADE takes with it, and soft delete — the `deleted` flag the pizza schema uses so a historical order never loses the product it referenced.
• MySQL – UPDATE
  Changing rows that already exist. UPDATE with WHERE and the habit that saves you — run it as a SELECT first, UPDATE with a JOIN for a backfill, updating from a subquery, ORDER BY and LIMIT on an UPDATE to work through a large table in batches, and `sql_safe_updates`, the setting that refuses an UPDATE with no WHERE clause before it becomes an incident.
• MySQL – INSERT
  Putting rows in. Single-row and multi-row INSERT and why the multi-row form is dramatically faster, INSERT ... SELECT, leaving AUTO_INCREMENT and DEFAULT columns out, INSERT IGNORE and what it actually swallows, ON DUPLICATE KEY UPDATE for an upsert, REPLACE and why it is usually the wrong one, and LOAD DATA for a bulk load.
• MySQL – Window Functions
  Aggregating without collapsing the rows. OVER (PARTITION BY ... ORDER BY ...), ROW_NUMBER, RANK and DENSE_RANK and the difference ties make, LAG and LEAD for comparing a row to the one before it, running totals with a frame clause, NTILE, and the top-N-per-group problem — which is genuinely awkward without window functions and three lines with them.
• MySQL – Common Table Expressions (WITH)
  WITH, added in MySQL 8, and the reason a long query stops being unreadable. A CTE versus a derived table versus a view, chaining several CTEs so each step is named, referencing one twice, and RECURSIVE — walking a parent-child tree and generating a gap-free date series to report on days that had no orders at all.
• MySQL – Subqueries
  A query inside a query. Scalar subqueries in the select list, subqueries in WHERE with IN, EXISTS and the comparison operators, derived tables in FROM and why they need an alias, correlated subqueries and why they are the expensive kind, the NOT IN trap that returns nothing at all when the inner query yields a NULL, and when to reach for a join instead.
• MySQL – GROUP BY and HAVING
  Collapsing many rows into one per group. COUNT, SUM, AVG, MIN and MAX, grouping by several columns, HAVING versus WHERE and why they are not interchangeable, counting with COUNT(*) versus COUNT(column) when NULLs are involved, WITH ROLLUP for subtotals, and ONLY_FULL_GROUP_BY — the mode that is ON by default in MySQL 8 and rejects the sloppy GROUP BY that MySQL 5 quietly accepted.
• MySQL – UNION and UNION ALL
  Stacking result sets on top of each other instead of side by side. UNION versus UNION ALL and why the default deduplication is not free, the rules the branches have to satisfy, where ORDER BY and LIMIT go when there is more than one SELECT, MySQL 8's INTERSECT and EXCEPT, and when a UNION is the wrong tool and a conditional aggregate is the right one.
• MySQL – Self Join
  Joining a table to itself, which is not a special kind of join — it is an ordinary join where both aliases point at the same table. Why the aliases stop being optional, comparing rows within a table, finding pairs without duplicating them with `a.id < b.id`, and the classic employee-and-manager shape done on a table you can actually see.
• MySQL – CROSS JOIN
  Every row on the left paired with every row on the right. The Cartesian product, how CROSS JOIN differs from a comma join with no WHERE (it does not), the row count arithmetic that makes an accidental one so expensive, and the case where you actually want it: generating a complete grid — every product against every size — to find the combinations that are missing.
• MySQL – RIGHT JOIN
  RIGHT JOIN is LEFT JOIN with the tables the other way round, and that is very nearly the whole lesson. What it does, the identical query written both ways, why you will almost never see one in a real codebase, and the one honest argument for it — a long chain of joins where flipping the order would mean rewriting every line.
• MySQL – LEFT JOIN
  Keeping every row on the left whether or not the right side matches. Where the NULLs come from, the guest-order case the pizza schema is built around, finding rows with NO match using `WHERE right.id IS NULL`, and the single most common LEFT JOIN mistake: putting a condition on the right-hand table in WHERE instead of ON, which turns the whole thing back into an INNER JOIN.
• MySQL – INNER JOIN
  Reading columns from two tables in one result. The ON clause and how a join is evaluated, why INNER JOIN and JOIN are the same thing, joining more than two tables — order to item to topping is three deep in the pizza schema — table aliases, joining on something other than a foreign key, and why a missing ON clause silently gives you every row times every row.
• MySQL – IF, CASE and Conditional Expressions
  Branching inside a query. IF() for the two-way case, CASE WHEN for everything else, IFNULL() and NULLIF(), COALESCE() for the first non-NULL of several, and the pattern that makes CASE genuinely useful — conditional aggregation, counting completed and cancelled orders in a single pass instead of running two queries.
• MySQL – LIMIT and Pagination
  LIMIT, LIMIT with OFFSET, and why LIMIT without ORDER BY is a bug waiting to happen. Then the part most tutorials skip: OFFSET pagination gets slower the deeper you page, because the server still has to walk and discard every row it skips — and what keyset (seek) pagination does instead.
• MySQL – ORDER BY
  Sorting results. ASC and DESC, sorting by several columns, sorting by an expression or an alias, where NULLs land in MySQL and how to force them to the other end, sorting by a column you did not select, and why a query without ORDER BY has no guaranteed order at all — even when it looks sorted every time you run it.
• MySQL – LIKE and Pattern Matching
  Matching text patterns. The `%` and `_` wildcards, ESCAPE for matching a literal percent sign, why LIKE is case-insensitive here and what the column's collation has to do with it, NOT LIKE, REGEXP when LIKE is not enough, and the performance rule worth remembering: `LIKE 'Pep%'` can use an index and `LIKE '%roni'` cannot.
• MySQL – BETWEEN
  BETWEEN as shorthand for two comparisons, and the two things that catch people out: it is inclusive at both ends, and on a DATETIME column `BETWEEN '2024-01-01' AND '2024-01-31'` silently drops almost the whole of the 31st. NOT BETWEEN, BETWEEN on strings, and the half-open `>= ... < ...` form that is the right answer for dates.
• MySQL – NULL, IS NULL and ISNULL()
  NULL is not a value, it is the absence of one, and every surprising thing about it follows from that. IS NULL and IS NOT NULL, the ISNULL() function, IFNULL() and COALESCE(), why `= NULL` is always false, how NULL behaves in aggregates and in GROUP BY, and the nullable column the pizza schema uses on purpose — `user_id` is NULL exactly when the order was placed by a guest.
• MySQL – WHERE
  Filtering rows. Comparison operators, AND / OR / NOT and the precedence rule that makes parentheses worth typing, IN and NOT IN, filtering on a boolean column, and the trap that silently returns nothing: comparing to NULL with `=` instead of IS NULL. Also why wrapping a column in a function in the WHERE clause quietly disables the index on it.
• MySQL – SELECT
  The statement you will write more than all the others combined. Choosing columns instead of SELECT *, and why the star is a habit worth losing, column and table aliases, DISTINCT, expressions and arithmetic in the select list, the order MySQL actually evaluates a query in — which is not the order you type it — and why that explains half the errors beginners hit.
• MySQL – Normalization and Schema Design
  Why the pizza schema looks the way it does. First, second and third normal form explained on tables you can see rather than on students and courses, why price lives in `product_size` and not in `product`, the one place the schema deliberately denormalizes — `order_item` snapshots the product name and price so editing the menu cannot rewrite what someone already bought — and why `cart` does the opposite and stores no prices at all.
• MySQL – CREATE TABLE, Constraints and ALTER
  Writing the schema. CREATE TABLE column by column, PRIMARY KEY and why the pizza tables use a surrogate BIGINT, NOT NULL and DEFAULT, UNIQUE constraints and what they buy that application code cannot, FOREIGN KEY with ON DELETE CASCADE versus SET NULL — a choice the order tables make both ways on purpose — CHECK constraints, AUTO_INCREMENT, and ALTER TABLE on a table that already has rows in it.
• MySQL – Date and Time Types
  DATE, TIME, DATETIME, TIMESTAMP and YEAR, and the one difference that matters: TIMESTAMP converts to and from the session time zone and DATETIME does not. Why the pizza schema stores DATETIME(6), what the fractional-seconds argument buys you, the 2038 problem TIMESTAMP still has, DEFAULT CURRENT_TIMESTAMP and ON UPDATE, and how to store an instant so it survives a server moving between time zones.
• MySQL – Data Types
  Picking the right column type, and the two that cost real money to get wrong. INT versus BIGINT and what AUTO_INCREMENT actually runs out of, why every price column in the pizza schema is DECIMAL(10,2) and never FLOAT, VARCHAR versus CHAR and what the length really limits, TEXT and BLOB, ENUM and why the schema uses VARCHAR instead, BOOLEAN being TINYINT(1) in disguise, and utf8mb4 — the reason MySQL's own `utf8` cannot store an emoji.
• MySQL – Connections, URLs and Pooling
  How an application actually talks to MySQL. Reading a JDBC URL parameter by parameter, why `serverTimezone` and `allowPublicKeyRetrieval` exist and when you need them, connection pooling with HikariCP and how to size a pool, `wait_timeout` and the stale-connection errors it causes overnight, and `max_connections` — the limit you meet on a bad day rather than a good one.
• MySQL – Install and Connect
  Getting a server you can type into. One `docker compose up -d` for a throwaway MySQL 8.4, why the demo publishes 3308 instead of 3306, installing the client on macOS, Linux and Windows, connecting with `mysql` and with a GUI, the healthcheck that stops your app racing the database, and loading the pizza schema so the rest of the track has something to query.
• MySQL – Get Started
  Start here. What MySQL is and where it fits against Postgres and SQLite, why 8.4 is the version to learn and what LTS means for it, InnoDB and why the storage engine is a choice you no longer have to make, the pizza-ordering database every query in this track runs against, and the full lesson index in reading order.