MySQL Cheat Sheet and What to Learn Next

mysql -u user -p -h host -P 3306 dbname

SHOW DATABASES;                              -- list databases
USE shop;                                    -- choose a database
SHOW TABLES;                                 -- list tables
DESCRIBE orders;                             -- columns of a table (also: SHOW COLUMNS FROM orders)
SHOW CREATE TABLE orders\G                   -- the exact CREATE statement
SELECT DATABASE(), CURRENT_USER(), VERSION();

CREATE DATABASE shop CHARACTER SET utf8mb4;
DROP DATABASE IF EXISTS shop;

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_customers_email (email)
);

ALTER TABLE customers ADD COLUMN phone VARCHAR(20) NULL AFTER email;
ALTER TABLE customers MODIFY phone VARCHAR(30) NOT NULL DEFAULT '';
ALTER TABLE customers DROP COLUMN phone;
ALTER TABLE customers RENAME COLUMN name TO full_name;
RENAME TABLE customers TO clients;
TRUNCATE TABLE clients;
DROP TABLE IF EXISTS clients;

INSERT INTO customers (name, email) VALUES ('Maya', 'maya@example.com'), ('Ben', 'ben@example.com');
INSERT INTO archive SELECT * FROM customers WHERE created_at < '2025-01-01';
INSERT INTO customers (name, email) VALUES ('Maya', 'maya@example.com') ON DUPLICATE KEY UPDATE name = VALUES(name);
UPDATE customers SET name = 'Maya Shah' WHERE id = 1;
DELETE FROM customers WHERE id = 1;
SELECT ROW_COUNT(), LAST_INSERT_ID();

SELECT   DISTINCT c.city, COUNT(*) AS customers, AVG(o.total) AS avg_total
FROM     customers c
JOIN     orders o ON o.customer_id = c.id
WHERE    o.status = 'paid' AND o.ordered_on >= '2026-01-01'
GROUP BY c.city
HAVING   COUNT(*) > 5
ORDER BY customers DESC, c.city
LIMIT    10 OFFSET 20;

FROM a JOIN b ON b.a_id = a.id                  -- INNER: matching rows only
FROM a LEFT JOIN b ON b.a_id = a.id             -- all of a, b where it matches
FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.id IS NULL     -- rows of a with no match
FROM a JOIN a AS parent ON parent.id = a.parent_id          -- self join
FROM a CROSS JOIN b                             -- every combination

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

WITH monthly AS (SELECT DATE_FORMAT(d, '%Y-%m') AS m, SUM(x) AS s FROM t GROUP BY m)
SELECT * FROM monthly;

WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 10) SELECT i FROM n;

SELECT a FROM t1 UNION SELECT a FROM t2;          -- distinct   (UNION ALL keeps duplicates)
SELECT a FROM t1 INTERSECT SELECT a FROM t2;      -- MySQL 8.0.31 and later
SELECT a FROM t1 EXCEPT SELECT a FROM t2;

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
RANK() OVER w          DENSE_RANK() OVER w         NTILE(4) OVER w
LAG(x) OVER w          LEAD(x) OVER w              FIRST_VALUE(x) OVER w
SUM(x) OVER (ORDER BY d)                                        -- running total
AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)   -- moving average
... WINDOW w AS (PARTITION BY dept ORDER BY salary DESC)

PRIMARY KEY (id)
UNIQUE KEY uq_email (email)
FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
CHECK (price >= 0)
NOT NULL    DEFAULT 0

CREATE INDEX idx_orders_customer_date ON orders (customer_id, ordered_on);
CREATE FULLTEXT INDEX ft_body ON posts (title, body);
DROP INDEX idx_orders_customer_date ON orders;
EXPLAIN SELECT ...;           EXPLAIN ANALYZE SELECT ...;
ANALYZE TABLE orders;         OPTIMIZE TABLE orders;

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;            -- or ROLLBACK;
SAVEPOINT sp1;     ROLLBACK TO sp1;

SELECT * FROM t WHERE id = 1 FOR UPDATE;                 -- lock the row
SELECT * FROM jobs WHERE status = 'new' LIMIT 1 FOR UPDATE SKIP LOCKED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;          -- before START TRANSACTION
SHOW ENGINE INNODB STATUS\G                               -- last deadlock, lock waits

CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.0.%';
SHOW GRANTS FOR 'shop_app'@'10.0.0.%';
REVOKE DELETE ON shop.* FROM 'shop_app'@'10.0.0.%';
ALTER USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'new-password' RETAIN CURRENT PASSWORD;
DROP USER 'shop_app'@'10.0.0.%';
CREATE ROLE 'reporting';  GRANT SELECT ON shop.* TO 'reporting';  GRANT 'reporting' TO 'asha'@'%';

mysqldump -u backup -p --single-transaction --routines --triggers --events --databases shop > shop.sql
mysql -u root -p < shop.sql
mysqlbinlog --start-position=158 --stop-position=1145 binlog.000002 | mysql -u root -p
mysqlcheck -u root -p --check shop

SHOW VARIABLES LIKE 'max_connections';        SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW FULL PROCESSLIST;                        KILL QUERY 123;
SELECT @@sql_mode, @@transaction_isolation, @@time_zone;
SHOW WARNINGS;                                SHOW ERRORS;