MySQL Cheat Sheet

Updated 2026-04-27

Next Topic: Neo4j and Cypher Query Language Cheat Sheet

🧠Study flashcards on this topic170 cards · spaced repetition→

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and querying data. As one of the world's most popular database systems, MySQL powers everything from small web applications to large-scale enterprise systems, offering ACID-compliant transactions, replication, and high availability features. MySQL 8.4 is the current Long-Term Support (LTS) release, bringing improvements to JSON handling, window functions, LATERAL derived tables, and role-based access control while removing legacy master/slave replication terminology. A critical distinction to understand: while MySQL supports multiple storage engines, InnoDB (the default since MySQL 5.5) provides row-level locking and foreign key constraints, whereas the legacy MyISAM uses table-level locking—choosing the right engine and understanding isolation levels can make the difference between a responsive system and one plagued by deadlocks.

What This Cheat Sheet Covers

This topic spans 38 focused tables and 306 indexed concepts, 170 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Data TypesTable 2: DDL StatementsTable 3: DML StatementsTable 4: Query ClausesTable 5: JOIN TypesTable 6: Aggregate FunctionsTable 7: String FunctionsTable 8: Regular Expression FunctionsTable 9: Date and Time FunctionsTable 10: Numeric FunctionsTable 11: Type ConversionTable 12: Conditional ExpressionsTable 13: ConstraintsTable 14: Generated ColumnsTable 15: Index TypesTable 16: Full-Text SearchTable 17: Window FunctionsTable 18: SubqueriesTable 19: Common Table Expressions (CTE)Table 20: Set OperationsTable 21: Transaction ControlTable 22: Isolation LevelsTable 23: User and Privilege ManagementTable 24: RolesTable 25: Stored Procedures and FunctionsTable 26: Prepared StatementsTable 27: TriggersTable 28: Event SchedulerTable 29: JSON FunctionsTable 30: Performance and OptimizationTable 31: Administrative CommandsTable 32: Storage EnginesTable 33: Character Sets and CollationsTable 34: PartitioningTable 35: ReplicationTable 36: Backup and RecoveryTable 37: Locking MechanismsTable 38: Operators

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Data Types

Picking the right column type is the first design decision in any schema, and it has lasting consequences for storage, speed, and correctness. The recurring tension here is exactness versus efficiency — use DECIMAL for money where rounding errors are unacceptable, INT/BIGINT sized to your real range, and VARCHAR over CHAR unless the length is genuinely fixed. Note also the timezone trap: DATETIME stores a wall-clock value while TIMESTAMP converts to and from UTC.

Type	Example	Description
INT	`id INT PRIMARY KEY`	• Integer range -2,147,483,648 to 2,147,483,647 (signed) • 4 bytes.
VARCHAR	`name VARCHAR(100)`	• Variable-length string • only uses space needed plus 1–2 bytes for length • max 65,535 chars.
TEXT	`description TEXT`	• Variable-length string for large text blocks • max 65,535 chars • cannot have a default value or be a primary key.
DECIMAL	`price DECIMAL(10,2)`	• Fixed-point number with exact precision • first param = total digits, second = decimal places • ideal for currency.
DATE	`birth_date DATE`	• Stores date only in `YYYY-MM-DD` format • range `1000-01-01` to `9999-12-31`.
DATETIME	`created_at DATETIME`	• Stores date and time in `YYYY-MM-DD HH:MM:SS` • range up to `9999-12-31 23:59:59` • not timezone-aware.
TIMESTAMP	`updated_at TIMESTAMP` `DEFAULT CURRENT_TIMESTAMP`	• Stores datetime with automatic timezone conversion • range `1970-01-01` to `2038-01-19 UTC` • auto-updates on modification.
BOOLEAN	`is_active BOOLEAN`	• Synonym for TINYINT(1) • stores 0 (false) or 1 (true) • MySQL converts TRUE/FALSE to 1/0.
BIGINT	`user_id BIGINT`	• Large integer • 8 bytes • range ±9.2×10¹⁸ • used for large auto-increment IDs and big counts.
JSON	`metadata JSON`	• Native JSON type • validates JSON syntax • supports path expressions and indexing via generated columns.
ENUM	`status ENUM('pending',` `'active', 'closed')`	• String with predefined allowed values • stored internally as integers • max 65,535 distinct values.
CHAR	`country_code CHAR(2)`	• Fixed-length string • always uses full allocated length (padded with spaces) • faster than VARCHAR for fixed-size data.
FLOAT	`score FLOAT`	• Approximate floating-point • 4 bytes • precision can be lost in calculations • faster than DECIMAL.
DOUBLE	`latitude DOUBLE`	• Double-precision floating-point • 8 bytes • higher precision than FLOAT but still approximate.
TINYINT	`age TINYINT UNSIGNED`	• Smallest integer • 1 byte • range -128 to 127 (signed) or 0 to 255 unsigned • common for flags and small counts.
SMALLINT	`quantity SMALLINT`	• 2-byte integer • range -32,768 to 32,767 (signed) • good for moderate-range integers saving space vs INT.
MEDIUMINT	`page_views MEDIUMINT`	• 3-byte integer • range ±8.4 million • between SMALLINT and INT in size and range.
BLOB	`image BLOB`	• Binary large object • stores binary data • four sizes: TINYBLOB (255B), BLOB (64KB), MEDIUMBLOB (16MB), LONGBLOB (4GB).
MEDIUMTEXT / LONGTEXT	`body MEDIUMTEXT`	• MEDIUMTEXT: up to 16MB of text • LONGTEXT: up to 4GB of text; both cannot have defaults.
TIME	`duration TIME`	• Stores time in `HH:MM:SS` • range `-838:59:59` to `838:59:59` • represents time intervals as well as time of day.
SET	`perms SET('read','write','exec')`	• String object that can hold zero or more values from a predefined list • stored as a bitmask • max 64 members.
YEAR	`grad_year YEAR`	• Stores a 4-digit year (1901–2155) • 1 byte • displays as `YYYY`.

Table 2: DDL Statements

Data Definition Language is how you shape the database itself — creating, altering, and dropping databases, tables, indexes, and views. These commands change structure rather than data, and several of them are destructive and irreversible (DROP, TRUNCATE), so the IF EXISTS guard is worth the habit. In InnoDB many ALTER TABLE operations now run as online DDL, so you can reshape a live table without a long lock.

Statement	Example	Description
CREATE TABLE	`CREATE TABLE products (` `id INT PRIMARY KEY,` `name VARCHAR(100)` `);`	• Creates a new table with specified columns and constraints • requires at least one column definition.

The rest of this section is part of the full cheat sheet

Table 3: DML Statements

Data Manipulation Language is the day-to-day work of moving rows in and out — the four verbs SELECT, INSERT, UPDATE, and DELETE. The cardinal safety rule lives here: always pair UPDATE and DELETE with a WHERE, or you change every row in the table. The upsert idioms — INSERT ... ON DUPLICATE KEY UPDATE and REPLACE — handle the common "insert or update" case, with the former being the more efficient choice since it avoids a delete.

Statement	Example	Description
SELECT	`SELECT name, email` `FROM users` `WHERE id > 10;`	• Retrieves data from one or more tables • supports WHERE, ORDER BY, LIMIT, GROUP BY, and JOIN clauses.

The rest of this section is part of the full cheat sheet

Table 4: Query Clauses

The clauses that filter, group, sort, and trim a SELECT result. Order of evaluation matters more than people expect: WHERE filters individual rows before grouping, while HAVING filters the groups afterward — which is why only HAVING can reference aggregate functions. LIMIT with OFFSET is the backbone of pagination, and DISTINCT removes duplicates at a performance cost on large sets.

Clause	Example	Description
WHERE	`SELECT * FROM users` `WHERE age > 18;`	• Filters rows before grouping • supports comparison, logical, and pattern-matching operators.

The rest of this section is part of the full cheat sheet

Table 1: Data Types

Type	Example	Description
INT	`id INT PRIMARY KEY`	• Integer range -2,147,483,648 to 2,147,483,647 (signed) • 4 bytes.
VARCHAR	`name VARCHAR(100)`	• Variable-length string • only uses space needed plus 1–2 bytes for length • max 65,535 chars.
TEXT	`description TEXT`	• Variable-length string for large text blocks • max 65,535 chars • cannot have a default value or be a primary key.
DECIMAL	`price DECIMAL(10,2)`	• Fixed-point number with exact precision • first param = total digits, second = decimal places • ideal for currency.
DATE	`birth_date DATE`	• Stores date only in `YYYY-MM-DD` format • range `1000-01-01` to `9999-12-31`.
DATETIME	`created_at DATETIME`	• Stores date and time in `YYYY-MM-DD HH:MM:SS` • range up to `9999-12-31 23:59:59` • not timezone-aware.
TIMESTAMP	`updated_at TIMESTAMP` `DEFAULT CURRENT_TIMESTAMP`	• Stores datetime with automatic timezone conversion • range `1970-01-01` to `2038-01-19 UTC` • auto-updates on modification.
BOOLEAN	`is_active BOOLEAN`	• Synonym for TINYINT(1) • stores 0 (false) or 1 (true) • MySQL converts TRUE/FALSE to 1/0.
BIGINT	`user_id BIGINT`	• Large integer • 8 bytes • range ±9.2×10¹⁸ • used for large auto-increment IDs and big counts.
JSON	`metadata JSON`	• Native JSON type • validates JSON syntax • supports path expressions and indexing via generated columns.
ENUM	`status ENUM('pending',` `'active', 'closed')`	• String with predefined allowed values • stored internally as integers • max 65,535 distinct values.
CHAR	`country_code CHAR(2)`	• Fixed-length string • always uses full allocated length (padded with spaces) • faster than VARCHAR for fixed-size data.
FLOAT	`score FLOAT`	• Approximate floating-point • 4 bytes • precision can be lost in calculations • faster than DECIMAL.
DOUBLE	`latitude DOUBLE`	• Double-precision floating-point • 8 bytes • higher precision than FLOAT but still approximate.
TINYINT	`age TINYINT UNSIGNED`	• Smallest integer • 1 byte • range -128 to 127 (signed) or 0 to 255 unsigned • common for flags and small counts.
SMALLINT	`quantity SMALLINT`	• 2-byte integer • range -32,768 to 32,767 (signed) • good for moderate-range integers saving space vs INT.
MEDIUMINT	`page_views MEDIUMINT`	• 3-byte integer • range ±8.4 million • between SMALLINT and INT in size and range.
BLOB	`image BLOB`	• Binary large object • stores binary data • four sizes: TINYBLOB (255B), BLOB (64KB), MEDIUMBLOB (16MB), LONGBLOB (4GB).
MEDIUMTEXT / LONGTEXT	`body MEDIUMTEXT`	• MEDIUMTEXT: up to 16MB of text • LONGTEXT: up to 4GB of text; both cannot have defaults.
TIME	`duration TIME`	• Stores time in `HH:MM:SS` • range `-838:59:59` to `838:59:59` • represents time intervals as well as time of day.
SET	`perms SET('read','write','exec')`	• String object that can hold zero or more values from a predefined list • stored as a bitmask • max 64 members.
YEAR	`grad_year YEAR`	• Stores a 4-digit year (1901–2155) • 1 byte • displays as `YYYY`.

Table 2: DDL Statements

Statement	Example	Description
CREATE TABLE	`CREATE TABLE products (` `id INT PRIMARY KEY,` `name VARCHAR(100)` `);`	• Creates a new table with specified columns and constraints • requires at least one column definition.

Table 3: DML Statements

Statement	Example	Description
SELECT	`SELECT name, email` `FROM users` `WHERE id > 10;`	• Retrieves data from one or more tables • supports WHERE, ORDER BY, LIMIT, GROUP BY, and JOIN clauses.

Table 4: Query Clauses

Clause	Example	Description
WHERE	`SELECT * FROM users` `WHERE age > 18;`	• Filters rows before grouping • supports comparison, logical, and pattern-matching operators.