MySQL – Get Started

This is a 52-lesson MySQL track. It starts at "what is a database" and ends at replication and running queries against production without causing an incident. Every query in it was executed against a real database before it was published, and every result you see printed was captured from that run rather than typed by hand.

What MySQL is

MySQL is a relational database server. Your data lives in tables — rows and columns, with a declared type for each column — and you talk to it in SQL. It runs as a separate process that your application connects to over a socket, which is why "install MySQL" and "install a MySQL client" are two different things.

Relational means the tables reference each other. A pizza order does not contain a copy of the customer; it holds the customer's id, and the database enforces that the id points at a row that exists. That constraint is the thing you are buying — it is what keeps data consistent when several things are writing at once, and it is what a pile of JSON files cannot give you.

Where it sits

MySQL versus Postgres is not a question with a general answer, and the differences that used to matter have narrowed a lot: MySQL 8 added CTEs, window functions, and a JSON type worth using. Pick MySQL when you or your team already know it, when your host makes it the easy option, or when you are working on something that already uses it. Learn the SQL and most of it transfers either way.

Which version, and what LTS means

This track is written against MySQL 8.4, which is an LTS release: it gets bug and security fixes for years without behaviour changing underneath you. The alternative is the "Innovation" track, which ships features faster and expects you to keep upgrading. For anything you have to operate rather than experiment with, take the LTS.

MySQL 5.7 reached end of life in October 2023. If you are on it, the upgrade to 8 is worth planning now — and note that 8 turns ONLY_FULL_GROUP_BY on by default, which rejects sloppy GROUP BY queries that 5.7 quietly accepted. That single change breaks more upgrades than anything else. See GROUP BY.

InnoDB, and a choice you no longer have to make

MySQL separates SQL from storage, so it has pluggable storage engines. Older tutorials spend time choosing between InnoDB and MyISAM. Do not: InnoDB is the default and the right answer. It gives you transactions, row-level locking, crash recovery and foreign keys. MyISAM has none of those and locks the whole table on write.

You will still meet MyISAM in an old schema. Converting is one statement per table (ALTER TABLE t ENGINE = InnoDB), and it is almost always worth doing.

The database everything here runs against

Every example comes from a real application: a pizza-ordering API — menu, cart, checkout, payment, admin reports — with its schema managed by migrations. It is a good teaching schema because it was built to work rather than to demonstrate: orders reference an account or a guest email, line items snapshot the price they were bought at, and the tables are called app_user and customer_order because user and order are reserved words.

It is small — 14 products, 18 orders — so you can check any answer by eye. The lessons on indexes, query plans and production behaviour use a second, larger copy of the same schema (400,000 orders), because at 18 rows the optimizer reads everything and there is nothing to learn.

The versions this track is written against, read off the running server rather than chosen:

How to read this track

In order, if you are starting out — each section assumes the one before it. If you are already writing SQL, the lessons stand alone well enough to arrive at from a search; the ones most likely to teach an experienced developer something are indexes, EXPLAIN, transactions and deadlocks.

Next: install MySQL and connect to it.

The lessons

Getting started

• MySQL – Get Started — you are here
• MySQL – Install and Connect
• MySQL – Connections, URLs and Pooling

Designing a schema

• MySQL – Data Types
• MySQL – Date and Time Types
• MySQL – CREATE TABLE, Constraints and ALTER
• MySQL – Normalization and Schema Design

Reading data

• MySQL – SELECT
• MySQL – WHERE
• MySQL – NULL, IS NULL and ISNULL()
• MySQL – BETWEEN
• MySQL – LIKE and Pattern Matching
• MySQL – ORDER BY
• MySQL – LIMIT and Pagination
• MySQL – IF, CASE and Conditional Expressions

Joins

• MySQL – INNER JOIN
• MySQL – LEFT JOIN
• MySQL – RIGHT JOIN
• MySQL – CROSS JOIN
• MySQL – Self Join
• MySQL – UNION and UNION ALL

Aggregation and advanced queries

• MySQL – GROUP BY and HAVING
• MySQL – Subqueries
• MySQL – Common Table Expressions (WITH)
• MySQL – Window Functions

Writing data

• MySQL – INSERT
• MySQL – UPDATE
• MySQL – DELETE
• MySQL – LAST_INSERT_ID

Functions and searching

• MySQL – Built-in Functions
• MySQL – DATE_FORMAT and Date Functions
• MySQL – Server and Session Functions
• MySQL – JSON Columns
• MySQL – Full-Text Search

Performance

• MySQL – Indexes
• MySQL – EXPLAIN and Reading a Query Plan
• MySQL – Storing Hierarchies with a Closure Table

Transactions and concurrency

• MySQL – Transactions
• MySQL – Deadlocks

Server-side objects

• MySQL – Views
• MySQL – Stored Procedures and Functions
• MySQL – Triggers
• MySQL – Scheduled Events

Running MySQL in production

• MySQL – INFORMATION_SCHEMA
• MySQL – Users, Privileges and Roles
• MySQL – Reset the Root Password
• MySQL – Backup and Restore with mysqldump
• MySQL – The Binary Log
• MySQL – Replication
• MySQL – Running Queries in Production Safely

Interview preparation

• MySQL Interview – Fundamentals
• MySQL Interview – Advanced Queries