The Ultimate MySQL Cheatsheet

-- ── CREATE TABLE ──
CREATE TABLE users (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username    VARCHAR(50)  NOT NULL UNIQUE,
  email       VARCHAR(255) NOT NULL,
  password    VARCHAR(255) NOT NULL,
  full_name   VARCHAR(100),
  age         TINYINT UNSIGNED DEFAULT NULL,
  balance     DECIMAL(10,2) DEFAULT 0.00,
  is_active   BOOLEAN DEFAULT TRUE,
  role        ENUM('user','admin','moderator') DEFAULT 'user',
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_role_active (role, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── INSERT ──
INSERT INTO users (username, email, password, full_name)
VALUES ('alice', 'alice@example.com', SHA2('secret', 256), 'Alice Smith');

INSERT INTO users (username, email, password) VALUES
  ('bob',   'bob@example.com',   SHA2('pass1', 256)),
  ('carol', 'carol@example.com', SHA2('pass2', 256)),
  ('dave',  'dave@example.com',  SHA2('pass3', 256));

-- ── SELECT ──
SELECT * FROM users WHERE is_active = TRUE AND role = 'admin';
SELECT username, full_name FROM users WHERE age BETWEEN 25 AND 35;
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE email IN ('a@x.com', 'b@x.com');
SELECT * FROM users WHERE role IS NOT NULL;

-- ── UPDATE ──
UPDATE users SET balance = balance + 100.00 WHERE id = 1;
UPDATE users SET is_active = FALSE WHERE last_login < '2024-01-01';

-- ── DELETE ──
DELETE FROM users WHERE is_active = FALSE AND created_at < '2023-01-01';

-- ── ALTER TABLE ──
ALTER TABLE users ADD COLUMN avatar VARCHAR(500) AFTER full_name;
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;
ALTER TABLE users DROP COLUMN avatar;
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 150);

-- ── INNER JOIN ──
SELECT u.username, o.total, o.status
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- ── LEFT JOIN (all users, even without orders) ──
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- ── RIGHT JOIN ──
SELECT c.name, COUNT(u.id) AS user_count
FROM cities c
RIGHT JOIN users u ON c.id = u.city_id
GROUP BY c.id, c.name;

-- ── Self Join (manager hierarchy) ──
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- ── Multiple Joins ──
SELECT u.username, o.total, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= '2024-01-01';

-- ── CROSS JOIN (Cartesian product) ──
SELECT c.name, p.name
FROM colors c
CROSS JOIN products p;

-- ── Subquery (Scalar) ──
SELECT username, balance
FROM users
WHERE balance > (SELECT AVG(balance) FROM users);

-- ── Subquery (IN) ──
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE total > 1000);

-- ── Subquery (EXISTS) ──
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'pending'
);

-- ── Derived Table (subquery in FROM) ──
SELECT region, total_orders, total_revenue
FROM (
  SELECT
    CASE
      WHEN total < 100 THEN 'small'
      WHEN total < 500 THEN 'medium'
      ELSE 'large'
    END AS region,
    COUNT(*) AS total_orders,
    SUM(total) AS total_revenue
  FROM orders
  GROUP BY region
) AS summary
ORDER BY total_revenue DESC;

-- ── Create Indexes ──
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- ── Composite Index (order matters!) ──
-- Good for: WHERE user_id = ? AND status = ? ORDER BY created_at
CREATE INDEX idx_orders_composite ON orders(user_id, status, created_at);

-- ── Fulltext Index ──
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

-- ── Prefix Index (for long strings) ──
CREATE INDEX idx_url_prefix ON pages(url(100));

-- ── Partial / Functional Index (MySQL 8.0+) ──
CREATE INDEX idx_active_users ON users((is_active AND role = 'admin'));

-- ── Explain Query Plan ──
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;

-- ── Covering Index (all fields in index, no table lookup) ──
CREATE INDEX idx_covering ON orders(user_id, status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 1;
-- "Using index" in EXPLAIN output means covering index is used

-- ── Managing Indexes ──
SHOW INDEX FROM users;
ALTER TABLE users DROP INDEX idx_users_email;
ANALYZE TABLE users;  -- update index statistics

-- ── View Index Usage ──
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'myapp';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'myapp';

-- ── Query Optimization Tips ──

-- 1. Avoid SELECT * — specify only needed columns
SELECT id, username, email FROM users WHERE role = 'admin';

-- 2. Use LIMIT for pagination
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 20 OFFSET 40;
-- Better: keyset pagination (avoids OFFSET scan)
SELECT * FROM orders
WHERE user_id = 1 AND created_at < '2024-06-01 00:00:00'
ORDER BY created_at DESC LIMIT 20;

-- 3. Use UNION ALL instead of UNION (no dedup overhead)
SELECT user_id FROM orders WHERE status = 'completed'
UNION ALL
SELECT user_id FROM returns;

-- 4. Batch inserts (much faster than single inserts)
INSERT INTO logs (user_id, action, created_at) VALUES
  (1, 'login', NOW()), (2, 'login', NOW()), (3, 'signup', NOW());

-- 5. Avoid functions on indexed columns (breaks index usage)
-- BAD:  WHERE YEAR(created_at) = 2024
-- GOOD: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- 6. Use COUNT(*) for InnoDB (no full table scan needed)
SELECT COUNT(*) FROM users WHERE is_active = TRUE;

-- 7. Optimize LIKE queries
-- BAD:  WHERE name LIKE '%alice%'  (full scan)
-- GOOD: WHERE name LIKE 'alice%'    (uses index)

-- ── Window Functions (MySQL 8.0+) ──

-- ROW_NUMBER: sequential number
SELECT
  username, total,
  ROW_NUMBER() OVER (ORDER BY total DESC) AS rank
FROM orders;

-- RANK / DENSE_RANK
SELECT
  username, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
-- RANK: 1,2,2,4 (gaps for ties)
-- DENSE_RANK: 1,2,2,3 (no gaps)

-- Running Total
SELECT
  order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

-- Moving Average
SELECT
  order_date, revenue,
  AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

-- LAG / LEAD
SELECT
  order_date, revenue,
  LAG(revenue, 1) OVER (ORDER BY order_date) AS prev_day,
  LEAD(revenue, 1) OVER (ORDER BY order_date) AS next_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY order_date) AS day_over_day
FROM daily_sales;

-- FIRST_VALUE / LAST_VALUE
SELECT
  product, month, sales,
  FIRST_VALUE(sales) OVER (PARTITION BY product ORDER BY month) AS first_month,
  LAST_VALUE(sales) OVER (
    PARTITION BY product ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_month
FROM product_sales;

-- NTILE (percentile buckets)
SELECT
  customer_id, total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_totals;

-- ── Common Table Expressions (CTEs) ──

-- Basic CTE
WITH active_users AS (
  SELECT id, username, email FROM users WHERE is_active = TRUE
)
SELECT * FROM active_users WHERE username LIKE 'a%';

-- Multiple CTEs
WITH
  active_users AS (
    SELECT id, username FROM users WHERE is_active = TRUE
  ),
  user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders WHERE status = 'completed'
    GROUP BY user_id
  )
SELECT
  u.username,
  COALESCE(o.order_count, 0) AS orders,
  COALESCE(o.total_spent, 0) AS spent
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id
ORDER BY spent DESC
LIMIT 20;

-- Recursive CTE (hierarchical data, e.g. org chart)
WITH RECURSIVE org_chart AS (
  -- Anchor
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

-- Recursive CTE (bill of materials)
WITH RECURSIVE bom AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM parts WHERE id = 1
  UNION ALL
  SELECT p.id, p.name, p.parent_id, b.depth + 1
  FROM parts p
  INNER JOIN bom b ON p.parent_id = b.id
)
SELECT * FROM bom;

-- ── Transaction Basics ──
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- If all OK:
COMMIT;
-- If something goes wrong:
-- ROLLBACK;

-- ── Transaction Isolation Levels ──
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default), SERIALIZABLE

-- ── Savepoints (partial rollback) ──
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_transfer;
UPDATE inventory SET qty = qty - 1 WHERE product_id = 42;
-- Oops, inventory update failed
ROLLBACK TO after_transfer;
COMMIT;

-- ── Deadlock Handling ──
SET innodb_lock_wait_timeout = 50;  -- wait 50s for lock

-- ── Explicit Locking ──
SELECT * FROM users WHERE id = 1 FOR UPDATE;         -- exclusive lock
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE; -- shared lock
SELECT * FROM users WHERE id = 1 FOR SKIP LOCKED;    -- skip locked rows

-- ── Row Locking Example (prevent double spend) ──
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Check balance in application...
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;

-- ── View Deadlocks ──
SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_LOCKS;

-- ── User Management ──
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'%';
GRANT ALL PRIVILEGES ON myapp.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

ALTER USER 'appuser'@'%' IDENTIFIED BY 'NewPassword456!';
REVOKE DELETE ON myapp.* FROM 'appuser'@'%';
DROP USER 'old_user'@'%';
SHOW GRANTS FOR 'appuser'@'%';

-- ── Database & Table Management ──
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE myapp;
SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE users;

-- ── Backup & Restore ──
-- mysqldump -u root -p myapp > backup.sql
-- mysqldump -u root -p --single-transaction --routines --triggers myapp > full_backup.sql
-- mysql -u root -p myapp < backup.sql

-- ── Replication: Configure Master ──
-- my.cnf
-- [mysqld]
-- server-id = 1
-- log_bin = mysql-bin
-- binlog_format = ROW
-- binlog_expire_logs_seconds = 604800

SHOW MASTER STATUS;
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- ── Replication: Configure Slave ──
-- [mysqld]
-- server-id = 2
-- relay_log = relay-bin
-- read_only = ON

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='master-host',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='repl_password',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=154;
START REPLICA;
SHOW REPLICA STATUS\G

-- ── Variables ──
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 500;
SET SESSION wait_timeout = 600;

-- ── Stored Procedure ──
DELIMITER //
CREATE PROCEDURE transfer_funds(
  IN p_from INT, IN p_to INT, IN p_amount DECIMAL(10,2)
)
BEGIN
  DECLARE v_balance DECIMAL(10,2);

  SELECT balance INTO v_balance FROM accounts WHERE id = p_from FOR UPDATE;

  IF v_balance < p_amount THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;

  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
END //
DELIMITER ;

CALL transfer_funds(1, 2, 500.00);

-- ── Trigger ──
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE users SET order_count = order_count + 1 WHERE id = NEW.user_id;
  INSERT INTO order_log (order_id, action, created_at) VALUES (NEW.id, 'created', NOW());
END //
DELIMITER ;

-- ── Event Scheduler (periodic task) ──
SET GLOBAL event_scheduler = ON;

DELIMITER //
CREATE EVENT cleanup_expired_sessions
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM sessions WHERE expires_at < NOW();
END //
DELIMITER ;

-- ── JSON Functions (MySQL 5.7+) ──
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  attrs JSON
);

INSERT INTO products VALUES (1, 'Laptop', '{"color": "silver", "ram": "16GB", "storage": "512GB"}');

SELECT name, attrs->>"$.color" AS color, attrs->>"$.ram" AS ram FROM products;
SELECT name FROM products WHERE JSON_CONTAINS(attrs, '"16GB"', '$.ram');
SELECT JSON_KEYS(attrs) AS keys FROM products WHERE id = 1;
SELECT JSON_ARRAYAGG(name) AS names FROM products;
UPDATE products SET attrs = JSON_SET(attrs, '$.price', 999) WHERE id = 1;