MySQL Cheat Sheet: Commands, Queries & Admin Tips

MySQL is the world's most popular open-source relational database. This reference covers everything from the mysql CLI to indexes, joins, stored procedures, and performance tuning — copy-ready for daily work.

Quick reference

The 25 patterns that cover 90% of daily MySQL work.

MySQL CLI (mysql client)

# Connect
mysql -u root -p                          # local, prompt for password
mysql -u user -p -h 127.0.0.1 -P 3306 dbname
mysql -u user -pMyPassword dbname         # password inline (no space after -p)

# Run a query without entering the shell
mysql -u root -p -e "SHOW DATABASES;"

# Import / export
mysqldump -u root -p --single-transaction mydb > mydb.sql
mysqldump -u root -p --single-transaction --no-tablespaces mydb | gzip > mydb.sql.gz
mysql -u root -p mydb < mydb.sql

Useful shell commands:

Data types

Numeric

String

Date and time

Tip: Use DATETIME for app logic; TIMESTAMP auto-updates and is timezone-aware on retrieval.

DDL — CREATE, ALTER, DROP

-- Create database
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DROP DATABASE IF EXISTS mydb;

-- Create table
CREATE TABLE users (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  username   VARCHAR(50)  NOT NULL,
  role       ENUM('admin','user','guest') NOT NULL DEFAULT 'user',
  bio        TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Alter table
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;
ALTER TABLE users DROP COLUMN bio;
ALTER TABLE users RENAME COLUMN username TO display_name;
ALTER TABLE users ADD INDEX idx_created (created_at);
ALTER TABLE users DROP INDEX idx_role;

-- Foreign key
CREATE TABLE posts (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  title   VARCHAR(255) NOT NULL,
  body    TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Rename table
RENAME TABLE old_name TO new_name;

-- Drop
DROP TABLE IF EXISTS posts;
TRUNCATE TABLE logs;  -- fast delete all rows, resets AUTO_INCREMENT

SELECT — Querying data

-- Basic
SELECT id, email, role FROM users WHERE role = 'admin' ORDER BY id DESC LIMIT 20 OFFSET 40;

-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username REGEXP '^[a-z]';

-- NULL checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- IN / BETWEEN
SELECT * FROM users WHERE role IN ('admin', 'user');
SELECT * FROM logs  WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Aliases and expressions
SELECT
  id,
  CONCAT(first_name, ' ', last_name) AS full_name,
  YEAR(created_at)                   AS join_year,
  DATEDIFF(NOW(), created_at)        AS days_since_join
FROM users;

-- CASE expression
SELECT
  id,
  CASE role
    WHEN 'admin' THEN 'Administrator'
    WHEN 'user'  THEN 'Regular User'
    ELSE 'Other'
  END AS role_label
FROM users;

-- Subquery
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM posts WHERE created_at > NOW() - INTERVAL 7 DAY);

JOINs

-- INNER JOIN — only matching rows
SELECT u.email, p.title
FROM users u
INNER JOIN posts p ON p.user_id = u.id;

-- LEFT JOIN — all users, NULL if no posts
SELECT u.email, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id;

-- RIGHT JOIN — all posts, NULL if user deleted
SELECT u.email, p.title
FROM users u
RIGHT JOIN posts p ON p.user_id = u.id;

-- Self join — find users who share a role
SELECT a.email AS user1, b.email AS user2, a.role
FROM users a
INNER JOIN users b ON a.role = b.role AND a.id < b.id;

-- Multiple joins
SELECT u.email, p.title, c.body AS comment
FROM users u
JOIN posts    p ON p.user_id = u.id
JOIN comments c ON c.post_id = p.id
WHERE u.role = 'admin';

Aggregation and GROUP BY

SELECT
  role,
  COUNT(*)                          AS total,
  COUNT(phone)                      AS with_phone,   -- NULLs excluded
  MIN(created_at)                   AS first_joined,
  MAX(created_at)                   AS last_joined,
  GROUP_CONCAT(email ORDER BY id SEPARATOR ', ') AS emails
FROM users
GROUP BY role
HAVING total > 5
ORDER BY total DESC;

Aggregate functions:

INSERT, UPDATE, DELETE

-- Insert single row
INSERT INTO users (email, username, role)
VALUES ('alice@example.com', 'alice', 'user');

-- Insert multiple rows
INSERT INTO users (email, username) VALUES
  ('bob@example.com',   'bob'),
  ('carol@example.com', 'carol');

-- Insert from SELECT
INSERT INTO archive_users SELECT * FROM users WHERE created_at < '2023-01-01';

-- Upsert (insert or update on duplicate key)
INSERT INTO page_views (page, views)
VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE views = views + 1;

-- REPLACE (delete + insert — use with caution, triggers fire)
REPLACE INTO settings (key, value) VALUES ('theme', 'dark');

-- Update
UPDATE users SET role = 'admin' WHERE email = 'alice@example.com';
UPDATE users SET updated_at = NOW() WHERE id IN (1, 2, 3);

-- Update with join
UPDATE posts p
JOIN users u ON u.id = p.user_id
SET p.author_name = u.display_name
WHERE u.role = 'admin';

-- Delete
DELETE FROM users WHERE id = 42;
DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

-- Safe delete with LIMIT
DELETE FROM spam_queue ORDER BY id LIMIT 1000;

Indexes

-- Single-column index
CREATE INDEX idx_email ON users (email);

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

-- Composite index (order matters — leftmost prefix rule)
CREATE INDEX idx_role_created ON users (role, created_at);

-- Full-text index
CREATE FULLTEXT INDEX idx_ft_title ON posts (title, body);

-- Prefix index (for long VARCHAR/TEXT)
CREATE INDEX idx_title_prefix ON posts (title(50));

-- Drop index
DROP INDEX idx_email ON users;

-- Full-text search
SELECT id, title,
  MATCH(title, body) AGAINST ('mysql tutorial' IN NATURAL LANGUAGE MODE) AS score
FROM posts
WHERE MATCH(title, body) AGAINST ('mysql tutorial' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- Boolean full-text mode
SELECT * FROM posts
WHERE MATCH(title, body) AGAINST ('+mysql -oracle' IN BOOLEAN MODE);

Index types:

Tip: MySQL can only use one index per table per query (usually). Use composite indexes to cover common query patterns.

Transactions

START TRANSACTION;

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

-- Check for error before committing
COMMIT;
-- or on error:
ROLLBACK;

-- Savepoints
START TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (1, 99.99);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 5);
-- Something went wrong with items but keep the order:
ROLLBACK TO SAVEPOINT after_order;

COMMIT;

-- Isolation levels (session)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Options: READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ (default) | SERIALIZABLE

Window functions (MySQL 8.0+)

-- ROW_NUMBER per partition
SELECT
  user_id,
  title,
  created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM posts;

-- Latest post per user
SELECT user_id, title, created_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM posts
) ranked
WHERE rn = 1;

-- Running total
SELECT
  id,
  amount,
  SUM(amount) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS running_total
FROM payments;

-- RANK vs DENSE_RANK
SELECT
  user_id,
  post_count,
  RANK()       OVER (ORDER BY post_count DESC) AS rank_with_gaps,
  DENSE_RANK() OVER (ORDER BY post_count DESC) AS dense_rank,
  PERCENT_RANK() OVER (ORDER BY post_count DESC) AS percentile
FROM (SELECT user_id, COUNT(*) post_count FROM posts GROUP BY user_id) c;

-- LAG / LEAD
SELECT
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date)  AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY date) AS day_over_day
FROM daily_revenue;

Common Table Expressions (CTEs)

-- Basic CTE
WITH active_users AS (
  SELECT id, email FROM users WHERE last_login > NOW() - INTERVAL 30 DAY
)
SELECT au.email, COUNT(p.id) AS recent_posts
FROM active_users au
LEFT JOIN posts p ON p.user_id = au.id AND p.created_at > NOW() - INTERVAL 30 DAY
GROUP BY au.id;

-- Multiple CTEs
WITH
  admins AS (SELECT id FROM users WHERE role = 'admin'),
  admin_posts AS (SELECT * FROM posts WHERE user_id IN (SELECT id FROM admins))
SELECT title, created_at FROM admin_posts ORDER BY created_at DESC LIMIT 10;

-- Recursive CTE (e.g., category tree)
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;

JSON (MySQL 5.7.8+)

-- Store JSON
CREATE TABLE events (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  data JSON NOT NULL
);
INSERT INTO events (data) VALUES ('{"type":"click","page":"/home","user_id":42}');

-- Extract values (return type: JSON)
SELECT data -> '$.type'    AS type,
       data -> '$.user_id' AS user_id
FROM events;

-- Extract as text/number (unquotes strings)
SELECT data ->> '$.type'    AS type,    -- equivalent to JSON_UNQUOTE(data -> '$.type')
       data ->> '$.user_id' AS user_id
FROM events;

-- Filter by JSON field
SELECT * FROM events WHERE data ->> '$.type' = 'click';
SELECT * FROM events WHERE JSON_EXTRACT(data, '$.user_id') > 10;

-- Modify JSON
UPDATE events
SET data = JSON_SET(data, '$.processed', TRUE, '$.ts', NOW())
WHERE id = 1;

-- Useful JSON functions
SELECT
  JSON_ARRAY(1, 2, 3),                     -- [1, 2, 3]
  JSON_OBJECT('k', 'v'),                   -- {"k": "v"}
  JSON_KEYS(data),                         -- ["type","page","user_id"]
  JSON_LENGTH(data),                       -- 3
  JSON_CONTAINS(data, '"click"', '$.type') -- 1 or 0
FROM events LIMIT 1;

Stored procedures and functions

-- Stored procedure
DELIMITER $$

CREATE PROCEDURE get_user_posts(IN p_user_id INT, IN p_limit INT)
BEGIN
  SELECT id, title, created_at
  FROM posts
  WHERE user_id = p_user_id
  ORDER BY created_at DESC
  LIMIT p_limit;
END$$

DELIMITER ;

-- Call it
CALL get_user_posts(42, 10);

-- Stored function (returns a value)
DELIMITER $$

CREATE FUNCTION user_post_count(p_user_id INT)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
  DECLARE cnt INT;
  SELECT COUNT(*) INTO cnt FROM posts WHERE user_id = p_user_id;
  RETURN cnt;
END$$

DELIMITER ;

SELECT email, user_post_count(id) AS posts FROM users LIMIT 10;

-- Drop
DROP PROCEDURE IF EXISTS get_user_posts;
DROP FUNCTION  IF EXISTS user_post_count;

User management and permissions

-- Create user
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword!';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPassword!';  -- any host

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'localhost';
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost';

-- Grant specific table
GRANT SELECT ON mydb.users TO 'readonly'@'%';

-- View grants
SHOW GRANTS FOR 'appuser'@'localhost';

-- Revoke
REVOKE DELETE ON mydb.* FROM 'appuser'@'localhost';

-- Change password
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewPassword!';

-- Drop user
DROP USER IF EXISTS 'appuser'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Performance tips

-- EXPLAIN — read the execution plan
EXPLAIN SELECT u.email, COUNT(p.id)
FROM users u LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id\G

-- Key columns to check:
-- type: ALL (table scan) → bad; ref/range/const → good
-- key: which index was used (NULL = no index)
-- rows: estimated rows examined
-- Extra: "Using filesort" or "Using temporary" can indicate slow queries

-- EXPLAIN ANALYZE (MySQL 8.0.18+) — executes and shows actual times
EXPLAIN ANALYZE SELECT * FROM users WHERE role = 'admin';

-- Find slow queries
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;    -- log queries over 1 second

-- Find missing indexes
SELECT *
FROM sys.schema_tables_with_full_table_scans
ORDER BY rows_full_scanned DESC;

-- Table sizes
SELECT
  table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY size_mb DESC;

-- Active connections
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- Kill a query
KILL QUERY 42;   -- kill query but keep connection
KILL 42;         -- kill connection entirely

Index tips:

• Add indexes for columns in WHERE, JOIN ON, ORDER BY, GROUP BY
• Composite index column order: equality first, range last, then ORDER BY columns
• LIKE 'prefix%' uses an index; LIKE '%suffix' does not
• Avoid functions on indexed columns: WHERE YEAR(created_at) = 2024 won't use an index on created_at — use WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
• Run ANALYZE TABLE t; to refresh index statistics

Useful patterns

Pagination (offset — simple but slow on large tables)

SELECT id, title FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100;

Keyset / cursor pagination (fast at any depth)

-- First page
SELECT id, title FROM posts ORDER BY id DESC LIMIT 20;

-- Next page (last seen id = 1000)
SELECT id, title FROM posts WHERE id < 1000 ORDER BY id DESC LIMIT 20;

Batch delete (avoid locking the table)

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 90 DAY LIMIT 1000;
-- Repeat until affected rows = 0

Get auto-increment id after insert

INSERT INTO posts (user_id, title) VALUES (1, 'Hello');
SELECT LAST_INSERT_ID();  -- returns the new id

Pivot / crosstab

SELECT
  user_id,
  SUM(CASE WHEN status = 'active'   THEN 1 ELSE 0 END) AS active,
  SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive,
  SUM(CASE WHEN status = 'banned'   THEN 1 ELSE 0 END) AS banned
FROM user_status_log
GROUP BY user_id;

Common mistakes

MySQL vs PostgreSQL quick comparison

6 FAQ

Q: What's the difference between CHAR and VARCHAR?
CHAR(n) always stores exactly n characters (right-padded with spaces). VARCHAR(n) stores only as many characters as needed plus a 1–2 byte length prefix. Use CHAR for fixed-length data (e.g., country codes, MD5 hashes); VARCHAR for everything else.

Q: When should I use TIMESTAMP vs DATETIME?
Use TIMESTAMP when you need automatic timezone conversion and ON UPDATE CURRENT_TIMESTAMP. Use DATETIME when you want the stored value to remain the same regardless of the server's timezone setting (better for app-level timezone handling).

Q: How do I safely add a column to a large table without locking?
MySQL 5.6+ supports ALTER TABLE ... ADD COLUMN online (no full table lock for InnoDB) for most operations. Check with EXPLAIN first. For very large tables, use pt-online-schema-change (Percona Toolkit) or gh-ost to perform zero-downtime migrations.

Q: What causes "Too many connections" errors?
Each client connection holds a thread. If your application opens connections faster than they're closed, you'll hit the max_connections limit (default 151). Use a connection pool (e.g., HikariCP for Java, pgBouncer-equivalent for MySQL: ProxySQL). Check SHOW STATUS LIKE 'Threads_connected';.

Q: What's the difference between TRUNCATE and DELETE FROM?
TRUNCATE is DDL: it drops and recreates the table structure, which is much faster and resets AUTO_INCREMENT. It cannot be rolled back and doesn't fire DELETE triggers. DELETE FROM is DML: it removes rows one by one, can be rolled back inside a transaction, respects WHERE, and fires triggers.

Q: How do I find and kill a long-running query?

-- Find it
SELECT id, user, time, info FROM information_schema.processlist
WHERE command != 'Sleep' ORDER BY time DESC;

-- Kill the query (keeps connection alive)
KILL QUERY <id>;

For automatic protection, set SET GLOBAL wait_timeout = 60; and use the MAX_EXECUTION_TIME(ms) optimizer hint per query.