Skip to content
Tutorials Center
My learning

Topics

Lesson 75 of 75 ยท Practice and Review

MySQL Cheat Sheet

A one-page MySQL cheat sheet: connecting, DDL, data types, queries, JSON, indexes, transactions, admin commands and how to read EXPLAIN output.

This page is the whole track on one screen: the commands you type every week, grouped so you can find them quickly. It does not teach anything from scratch. Each section links to the lesson that explains the details, so use the links when a line does not make sense yet.

Most of the code below is read-only, because it needs a MySQL server (the mysql client, SHOW statements, MySQL-specific syntax). Two live examples near the end run portable SQL in SQLite so you can try the query and JSON ideas, and they say where MySQL's spelling differs.

What is on this page

Connect Client, databases, tables.
Define DDL, types, constraints.
Query SELECT, joins, JSON, windows.
Operate Indexes, transactions, EXPLAIN.
1 / 2

Connect and explore

Terminal
mysql -u app -p -h 127.0.0.1 -P 3306 shop     # prompt for the password
mysql -u app -p shop -e "SELECT NOW()"        # run one statement and exit
mysql -u app -p shop < dump.sql               # run a script
SQL
SHOW DATABASES;
USE shop;
SHOW TABLES;
DESCRIBE orders;                 -- columns (also: SHOW COLUMNS FROM orders)
SHOW CREATE TABLE orders\G       -- exact DDL, vertical output
SHOW INDEX FROM orders;
SELECT VERSION(), DATABASE(), CURRENT_USER();
SHOW VARIABLES LIKE 'sql_mode';
SHOW PROCESSLIST;                -- running queries (KILL <id> to stop one)

More in the client lesson and installing and connecting.

Create and change structure

SQL
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED    NOT NULL,
  status      VARCHAR(20)     NOT NULL DEFAULT 'new',
  total       DECIMAL(10,2)   NOT NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_customer_created (customer_id, created_at),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT chk_total CHECK (total >= 0)
) ENGINE=InnoDB;

ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders MODIFY status VARCHAR(30) NOT NULL DEFAULT 'new';
ALTER TABLE orders RENAME COLUMN note TO remark;
ALTER TABLE orders DROP COLUMN remark;
ALTER TABLE orders ADD INDEX idx_status (status);
DROP TABLE IF EXISTS tmp_orders;
TRUNCATE TABLE logs;              -- fast, resets AUTO_INCREMENT, implicit commit

See CREATE TABLE in depth, keys and constraints and ALTER TABLE and online DDL.

Data types at a glance

Need Use Notes
Small whole numbers TINYINT (1 byte), SMALLINT (2), MEDIUMINT (3), INT (4), BIGINT (8) UNSIGNED doubles the positive range
Money, exact values DECIMAL(p,s) Never FLOAT or DOUBLE for money
Approximate numbers FLOAT, DOUBLE Rounding error is normal
Short text VARCHAR(n) n is characters; max row size is 65,535 bytes
Fixed code CHAR(n) Padded; good for fixed-length codes
Long text TEXT, MEDIUMTEXT, LONGTEXT Cannot have a full-length index or a plain default
Binary BINARY(n), VARBINARY(n), BLOB UUID as BINARY(16)
Date and time DATE, DATETIME, TIMESTAMP, TIME TIMESTAMP ends in 2038 and converts time zones
Few fixed choices ENUM(...), or a lookup table Lookup tables change more easily
Flexible documents JSON Validated, binary storage
Yes or no BOOLEAN (alias of TINYINT(1)) TRUE is 1, FALSE is 0

Details: numeric types, string types, date and time and choosing types.

Query patterns

SQL
SELECT c.name, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01' AND o.created_at < '2027-01-01'
GROUP BY c.id, c.name
HAVING SUM(o.total) > 1000
ORDER BY revenue DESC
LIMIT 10;
Task Syntax
Null-safe equality a <=> b
First non-null COALESCE(a, b, 'n/a'), IFNULL(a, 0)
Concatenate CONCAT(a, b), CONCAT_WS('-', a, b) (|| is OR by default)
Join strings per group GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')
Any value from a group ANY_VALUE(col)
Date arithmetic DATE_ADD(d, INTERVAL 7 DAY), TIMESTAMPDIFF(HOUR, a, b)
Format a date DATE_FORMAT(d, '%Y-%m-%d')
Upsert INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col)
Ignore duplicates INSERT IGNORE ... (hides other errors too)
Combine results UNION ALL, UNION, INTERSECT, EXCEPT (8.0.31 and later)
Per-row subquery JOIN LATERAL (SELECT ...) x ON TRUE (8.0.14 and later)
Paging LIMIT 20 OFFSET 40, or keyset: WHERE id > ? ORDER BY id LIMIT 20
Row locks SELECT ... FOR UPDATE, FOR SHARE, SKIP LOCKED, NOWAIT

Try the core pieces live below. This example uses portable SQL; the GROUP_CONCAT in SQLite has a simpler form (it does not accept ORDER BY or SEPARATOR inside), but the rest is identical in MySQL.

SQL Join, group and filter on groups

Press Run (or Ctrl + Enter) to see the result here.

The result lists Mara (3 orders, 500) and Noor (1 order, 45.5); Ivo is filtered out by HAVING. See GROUP BY, joins and upserts.

CTEs, windows and JSON

SQL
-- CTE and window function (MySQL 8.0+)
WITH monthly AS (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(total) AS revenue
  FROM orders GROUP BY ym
)
SELECT ym, revenue,
       LAG(revenue) OVER (ORDER BY ym) AS previous,
       SUM(revenue) OVER (ORDER BY ym)  AS running_total
FROM monthly;

-- JSON
SELECT data->'$.name'  AS quoted,       -- "Mara" (JSON value)
       data->>'$.name' AS plain         -- Mara   (text)
FROM events;
UPDATE events SET data = JSON_SET(data, '$.seen', TRUE) WHERE id = 1;
SELECT * FROM events WHERE JSON_CONTAINS(data->'$.tags', '"sql"');
SELECT * FROM events WHERE 'sql' MEMBER OF (data->'$.tags');

SQLite offers the same ideas through slightly different names. This live demo runs a window function and JSON extraction (json_extract, and ->> which SQLite supports from version 3.38):

SQL Window function and JSON extraction

Press Run (or Ctrl + Enter) to see the result here.

The first query shows each month's previous revenue and a running total (100, 250, 370). The second returns Mara and json. More in window functions, CTEs and the JSON type.

Indexes

SQL
CREATE INDEX idx_name ON customers (last_name, first_name);
CREATE UNIQUE INDEX uq_email ON customers (email);
CREATE FULLTEXT INDEX ft_body ON articles (title, body);
CREATE INDEX idx_prefix ON urls (url(100));               -- prefix index
CREATE INDEX idx_created_desc ON events (created_at DESC);
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;      -- test before dropping
DROP INDEX idx_status ON orders;
ANALYZE TABLE orders;                                     -- refresh statistics

Rules to remember: equality columns first, then a range or sort column; the leftmost prefix decides what an index can serve; no function on the indexed column; types must match; avoid redundant indexes. See how indexes work and composite indexes.

Transactions and locking

SQL
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
-- ROLLBACK TO SAVEPOINT after_debit;
COMMIT;                                   -- or ROLLBACK

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;           -- default: REPEATABLE-READ
SHOW ENGINE INNODB STATUS\G               -- includes the latest deadlock

DDL such as CREATE, ALTER and DROP commits implicitly. Retry a transaction after error 1213 (deadlock) and consider it after 1205 (lock wait timeout). See transactions, isolation and locking.

Administration

SQL
CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'a-long-random-secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'10.0.%';
SHOW GRANTS FOR 'app'@'10.0.%';
REVOKE DELETE ON shop.* FROM 'app'@'10.0.%';
ALTER USER 'app'@'10.0.%' PASSWORD EXPIRE INTERVAL 180 DAY;

SET PERSIST max_connections = 500;        -- survives a restart
SHOW GLOBAL STATUS LIKE 'Threads_connected';
Terminal
mysqldump --single-transaction --routines --triggers shop > shop.sql
mysql shop < shop.sql

More in users and privileges, backup and restore and server tuning. Replication commands in 8.4 are CHANGE REPLICATION SOURCE TO ..., START REPLICA and SHOW REPLICA STATUS (see the replication lesson).

Reading EXPLAIN

SQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at;
EXPLAIN FORMAT=TREE SELECT ...;           -- tree view
EXPLAIN ANALYZE SELECT ...;               -- runs it, adds real timings (8.0.18+)
Column What to look for
type Best to worst: system, const, eq_ref, ref, range, index, ALL. Be suspicious of ALL on big tables
possible_keys / key Indexes considered and the one chosen; NULL means no index
key_len How many bytes of a composite index are used; shorter than expected means fewer columns used
rows Estimated rows to examine; compare it with the rows you return
filtered Estimated percent of examined rows that survive the other conditions
Extra Using index (covering, good), Using index condition, Using where, Using filesort (extra sort), Using temporary (temp table)

A healthy plan on a big table is ref or range on a selective key, a small rows figure and no filesort. See EXPLAIN, query optimization and common pitfalls.

Keep this page open as a map, but copy statements into a real MySQL client from your own project's schema. Cheat sheets go stale; SHOW CREATE TABLE, EXPLAIN and the version you actually run are the sources of truth.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement shows the exact DDL for an existing table?

    SHOW CREATE TABLE prints the CREATE TABLE statement including indexes and options. DESCRIBE lists only the columns.
  2. 2. In EXPLAIN, which type is the best access method among these?

    eq_ref (one row per join lookup through a unique index) beats range, which beats index (full index scan) and ALL (full table scan).
  3. 3. Which data type should hold prices, such as 19.99?

    DECIMAL stores exact values. FLOAT and DOUBLE are approximate and can drift by tiny amounts.
  4. 4. What does TRUNCATE TABLE do that DELETE FROM table without a WHERE does not?

    TRUNCATE is DDL. It is fast, resets the counter and causes an implicit commit, so it cannot be rolled back.

Summary

  • Explore with SHOW DATABASES, SHOW TABLES, DESCRIBE and SHOW CREATE TABLE; script with mysql -e and mysqldump --single-transaction.
  • Use exact types (DECIMAL for money, utf8mb4 for text), name constraints and index the columns your queries filter and sort on.
  • Learn the MySQL spellings: <=>, CONCAT_WS, GROUP_CONCAT, ON DUPLICATE KEY UPDATE, ->> for JSON text.
  • Keep transactions short, retry after deadlocks, and let the database do read-modify-write arithmetic.
  • Read EXPLAIN by type, key, rows and Extra, and use EXPLAIN ANALYZE to compare estimates with reality.

That is the end of the MySQL track. Go back to common pitfalls or interview questions for a review, or start a project.