MySQL Cheat Sheet: Every Command You Need (2026)

Basic CRUD

These four operations form the backbone of every MySQL application. If you know nothing else, know these.

SELECT - Read data

-- Select specific columns
SELECT first_name, last_name, email
FROM users;

-- Select all columns
SELECT * FROM products;

-- Select with alias
SELECT first_name AS name, email AS contact
FROM users;

-- Select distinct values
SELECT DISTINCT category FROM products;

INSERT INTO - Add new rows

-- Insert a single row
INSERT INTO users (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane@example.com');

-- Insert multiple rows
INSERT INTO users (first_name, last_name, email)
VALUES
    ('Alice', 'Johnson', 'alice@example.com'),
    ('Bob', 'Williams', 'bob@example.com');

-- INSERT IGNORE: skip rows that would cause duplicate key errors
INSERT IGNORE INTO users (id, email)
VALUES (1, 'existing@example.com');

-- REPLACE INTO: delete + insert if duplicate key exists
REPLACE INTO settings (user_id, theme, language)
VALUES (42, 'dark', 'en');

UPDATE - Modify existing rows

-- Update specific rows
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 42;

-- Update multiple columns
UPDATE products
SET price = price * 1.10, updated_at = NOW()
WHERE category = 'electronics';

DELETE - Remove rows

-- Delete specific rows
DELETE FROM orders
WHERE status = 'cancelled' AND created_at < '2025-01-01';

-- Delete all rows (use with caution)
DELETE FROM temp_logs;

-- Delete with LIMIT (MySQL-specific)
DELETE FROM logs
WHERE level = 'debug'
ORDER BY created_at
LIMIT 1000;

Filtering & Sorting

Control which rows are returned and in what order.

WHERE, AND, OR

SELECT * FROM orders
WHERE status = 'shipped'
  AND total_amount > 100
  AND (region = 'US' OR region = 'EU');

IN and BETWEEN

-- IN: match any value in a list
SELECT * FROM products
WHERE category IN ('electronics', 'books', 'clothing');

-- BETWEEN: inclusive range
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

LIKE - Pattern matching

-- % matches any sequence of characters
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- _ matches exactly one character
SELECT * FROM products WHERE sku LIKE 'SKU-____';

-- Note: LIKE is case-insensitive by default in MySQL
-- (depends on collation, e.g., utf8mb4_general_ci)
SELECT * FROM users WHERE name LIKE 'john%';  -- matches John, JOHN, john

IS NULL

-- Find rows with NULL values
SELECT * FROM users WHERE phone IS NULL;

-- Find rows with non-NULL values
SELECT * FROM users WHERE phone IS NOT NULL;

ORDER BY and LIMIT

-- Sort ascending (default) and descending
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- LIMIT with offset (MySQL shorthand)
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 40, 20;  -- skip 40, return 20 (page 3)

-- Standard LIMIT OFFSET syntax also works
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

Aggregation

Summarize data across multiple rows into a single result.

COUNT, SUM, AVG, MIN, MAX

SELECT
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,
    MIN(total_amount) AS smallest_order,
    MAX(total_amount) AS largest_order
FROM orders
WHERE order_date >= '2026-01-01';

GROUP BY

-- Revenue by category
SELECT
    category,
    COUNT(*) AS num_products,
    SUM(price) AS total_value
FROM products
GROUP BY category
ORDER BY total_value DESC;

HAVING - Filter groups

-- Only show categories with more than 10 products
SELECT category, COUNT(*) AS num_products
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY num_products DESC;

Remember: WHERE filters rows before grouping. HAVING filters groups after aggregation. For more details, see our complete GROUP BY guide.

GROUP_CONCAT - MySQL-specific aggregation

-- Concatenate values from multiple rows into one string
SELECT
    department,
    GROUP_CONCAT(employee_name ORDER BY employee_name SEPARATOR ', ') AS team_members
FROM employees
GROUP BY department;

-- With DISTINCT to remove duplicates
SELECT
    order_id,
    GROUP_CONCAT(DISTINCT product_name SEPARATOR ' | ') AS products
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY order_id;

JOINs

Combine rows from two or more tables based on a related column.

INNER JOIN - Only matching rows from both tables

SELECT o.id, o.order_date, c.name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN - All rows from left table, matching from right

-- All customers, even those with no orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

RIGHT JOIN - All rows from right table, matching from left

-- All products, even those never ordered
SELECT p.product_name, SUM(oi.quantity) AS total_sold
FROM order_items oi
RIGHT JOIN products p ON oi.product_id = p.id
GROUP BY p.product_name;

CROSS JOIN - Every combination of rows

-- Generate all size/color combinations
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;

FULL OUTER JOIN - MySQL workaround

MySQL does not support FULL OUTER JOIN natively. Use a UNION of LEFT and RIGHT JOINs instead:

-- Simulate FULL OUTER JOIN in MySQL
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id

UNION

SELECT c.name, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

Quick reference: INNER = intersection, LEFT = all left + matching right (NULLs for no match), RIGHT = mirror of LEFT, CROSS = cartesian product.

Subqueries & CTEs

Break complex queries into manageable parts.

Scalar subquery

-- Use a single-value subquery in SELECT
SELECT
    product_name,
    price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

Subquery with IN

-- Customers who placed orders over $500
SELECT name, email
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount > 500
);

EXISTS

-- Products that have been ordered at least once
SELECT p.product_name
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.id
);

WITH (CTE) - MySQL 8.0+

-- Monthly revenue with running total
WITH monthly_revenue AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_revenue
ORDER BY month;

Recursive CTE - MySQL 8.0+

-- Generate a sequence of dates
WITH RECURSIVE date_series AS (
    SELECT '2026-01-01' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < '2026-01-31'
)
SELECT dt FROM date_series;

-- Traverse a category hierarchy
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

Window Functions

Perform calculations across a set of rows related to the current row, without collapsing them. Requires MySQL 8.0+.

ROW_NUMBER, RANK, DENSE_RANK

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS `rank`,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

ROW_NUMBER gives unique sequential numbers. RANK leaves gaps after ties (1,2,2,4). DENSE_RANK has no gaps (1,2,2,3).

NTILE - Divide into buckets

-- Split employees into 4 salary quartiles
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

LAG and LEAD

-- Compare each month's revenue to the previous month
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;

PARTITION BY

-- Rank employees within each department
SELECT
    name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dept_rank
FROM employees;

MySQL-Specific Features

Commands and features unique to MySQL that you will not find in standard SQL or other databases.

AUTO_INCREMENT

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Check the last auto-generated ID
SELECT LAST_INSERT_ID();

ON DUPLICATE KEY UPDATE (Upsert)

-- Insert or update if the key already exists
INSERT INTO page_views (page_url, view_count, last_viewed)
VALUES ('/home', 1, NOW())
ON DUPLICATE KEY UPDATE
    view_count = view_count + 1,
    last_viewed = NOW();

IFNULL and COALESCE

-- IFNULL: return second arg if first is NULL (MySQL-specific)
SELECT name, IFNULL(phone, 'N/A') AS phone
FROM contacts;

-- COALESCE: return first non-NULL value (standard SQL)
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM users;

ENUM and SET types

-- ENUM: column can hold exactly one of the listed values
CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
    priority ENUM('low', 'medium', 'high', 'critical') NOT NULL
);

-- SET: column can hold zero or more of the listed values
CREATE TABLE user_preferences (
    user_id INT PRIMARY KEY,
    notifications SET('email', 'sms', 'push', 'in_app') DEFAULT 'email'
);

UNSIGNED integers and backtick quoting

-- UNSIGNED: only positive numbers (doubles the max value)
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stock SMALLINT UNSIGNED DEFAULT 0
);

-- Backticks: quote reserved words or special characters
SELECT `order`, `group`, `key`
FROM `table` WHERE `select` = 1;

FOUND_ROWS()

-- Get total matching rows even with LIMIT
SELECT SQL_CALC_FOUND_ROWS * FROM products
WHERE category = 'electronics'
LIMIT 10;

SELECT FOUND_ROWS() AS total_matching;
-- Returns the total count without the LIMIT

String Functions

Manipulate and transform text data in MySQL.

-- CONCAT: combine strings
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- CONCAT_WS: concat with separator (skips NULLs)
SELECT CONCAT_WS(', ', city, state, country) AS location
FROM addresses;

-- SUBSTRING: extract part of a string
SELECT SUBSTRING(phone, 1, 3) AS area_code
FROM contacts;

-- TRIM: remove whitespace (or specific characters)
SELECT TRIM(name) FROM products;
SELECT TRIM(BOTH '.' FROM filename) FROM uploads;

-- UPPER / LOWER
SELECT UPPER(email) FROM users;
SELECT LOWER(city) FROM addresses;

-- LENGTH vs CHAR_LENGTH
SELECT
    LENGTH('Hello')       AS byte_length,    -- 5
    CHAR_LENGTH('Hello')  AS char_length;    -- 5
-- LENGTH counts bytes; CHAR_LENGTH counts characters (matters for UTF-8)

-- REPLACE: substitute text
SELECT REPLACE(url, 'http://', 'https://') FROM links;

-- GROUP_CONCAT: aggregate strings across rows
SELECT department,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY department;

-- REGEXP: regular expression matching
SELECT * FROM users
WHERE email REGEXP '^[a-z]+@gmail\\.com$';

Date & Time

MySQL has rich built-in date and time functions.

-- Current date and time
SELECT NOW();          -- 2026-03-12 14:30:00
SELECT CURDATE();      -- 2026-03-12
SELECT CURTIME();      -- 14:30:00

-- DATE_ADD and DATE_SUB
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month;
SELECT DATE_ADD('2026-01-01', INTERVAL 3 MONTH) AS q2_start;

-- DATEDIFF: days between two dates
SELECT DATEDIFF(NOW(), created_at) AS days_since_signup
FROM users;

-- DATE_FORMAT: custom date output
SELECT DATE_FORMAT(order_date, '%M %d, %Y') AS formatted_date
FROM orders;
-- Output: March 12, 2026

SELECT DATE_FORMAT(created_at, '%Y-%m') AS month
FROM orders;
-- Output: 2026-03

-- STR_TO_DATE: parse string into date
SELECT STR_TO_DATE('12-03-2026', '%d-%m-%Y') AS parsed_date;

-- Extract parts of a date
SELECT
    YEAR(order_date)  AS yr,
    MONTH(order_date) AS mo,
    DAY(order_date)   AS dy
FROM orders;

-- Filter by relative dates
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- TIMESTAMPDIFF for precise intervals
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_ago
FROM events;

Table Operations

Define and modify your database structure.

CREATE TABLE

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- Modify a column type
ALTER TABLE products MODIFY COLUMN price DECIMAL(10,2) NOT NULL;

-- Rename a column (MySQL 8.0+)
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- Add a foreign key
ALTER TABLE orders ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Drop a column
ALTER TABLE users DROP COLUMN phone_number;

DROP TABLE and TRUNCATE

-- Drop if exists (safe)
DROP TABLE IF EXISTS temp_imports;

-- TRUNCATE: remove all rows instantly (resets AUTO_INCREMENT)
TRUNCATE TABLE logs;

CREATE INDEX

-- Create an index for faster lookups
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Drop an index
DROP INDEX idx_users_email ON users;

Inspection commands

-- List all tables in the current database
SHOW TABLES;

-- See column definitions
DESCRIBE users;
-- or
SHOW COLUMNS FROM users;

-- See the full CREATE TABLE statement
SHOW CREATE TABLE users;

Pro Tips

Write faster, more efficient MySQL queries.

EXPLAIN and EXPLAIN ANALYZE

-- See the query execution plan
EXPLAIN
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- EXPLAIN ANALYZE: actually runs the query and shows real timing (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY order_date DESC
LIMIT 10;

Look for type: ALL (full table scan) in EXPLAIN output. If you see one on a large table, add an index on the filtered or joined column.

Slow query log

-- Enable the slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- log queries taking > 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Check current settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Index optimization guidelines

• Index your WHERE and JOIN columns. If you frequently filter by status or join on customer_id, those columns need indexes.
• Use composite indexes wisely. A composite index on (customer_id, order_date) helps queries filtering by both columns, or just customer_id alone (leftmost prefix rule).
• Avoid functions on indexed columns in WHERE. WHERE YEAR(created_at) = 2026 cannot use an index. Use WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' instead.
• Use covering indexes. If a query only reads indexed columns, MySQL can answer from the index alone without touching the table data.
• Do not over-index. Every index slows down INSERT, UPDATE, and DELETE. Only index columns you actually query on.

InnoDB vs MyISAM

• InnoDB (default since MySQL 5.5): transactions, row-level locking, foreign keys, crash recovery. Use this for almost everything.
• MyISAM: table-level locking, no transactions, no foreign keys. Faster for read-only bulk data in some edge cases, but rarely the right choice today.

For the general SQL version of this reference, see our SQL Cheat Sheet. New to SQL entirely? Start with What is SQL?

Try these queries instantly - AI2SQL generates MySQL from plain English so you can skip the syntax and focus on the data you need.

Frequently Asked Questions

What is the difference between MySQL and standard SQL?

MySQL follows the SQL standard for core commands (SELECT, INSERT, UPDATE, DELETE, JOINs) but adds MySQL-specific features like AUTO_INCREMENT, INSERT IGNORE, REPLACE INTO, ON DUPLICATE KEY UPDATE, ENUM/SET column types, backtick quoting for identifiers, and functions like GROUP_CONCAT, IFNULL, and LAST_INSERT_ID. MySQL also lacks native FULL OUTER JOIN support.

Does MySQL support window functions and CTEs?

Yes, starting from MySQL 8.0. Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) and Common Table Expressions (WITH ... AS) including recursive CTEs are fully supported in MySQL 8.0 and later. If you are on MySQL 5.7 or earlier, you need workarounds using variables and subqueries.

How do I check my MySQL version?

Run SELECT VERSION(); in any MySQL client. You can also run mysql --version from the command line. Knowing your version is important because features like window functions and CTEs require MySQL 8.0+.

What is the difference between InnoDB and MyISAM?

InnoDB is the default storage engine since MySQL 5.5. It supports transactions (ACID compliance), row-level locking, foreign keys, and crash recovery. MyISAM is older, uses table-level locking, does not support transactions or foreign keys, but can be faster for read-heavy workloads with no concurrent writes. Use InnoDB for nearly all modern applications.

Can AI generate MySQL queries for me?

Yes. Tools like AI2SQL let you describe what you need in plain English and generate the correct MySQL syntax automatically. This is especially useful for complex queries involving JOINs, subqueries, or MySQL-specific features like ON DUPLICATE KEY UPDATE. Understanding the fundamentals from a cheat sheet helps you verify and refine AI-generated queries.