← All Cheat Codes Library / Cheat Codes / MySQL

🐬 MySQL Cheat Codes

MySQL Cheat Codes

MySQL Cheat Codes for InnoDB queries, indexes, replication, and performance optimization. 20 essential cheat codes ready to copy and execute.

🌐 All Engines 🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

# 1 SELECT DQL

Retrieve columns from MySQL table.

Syntax:

SELECT col1, col2 FROM tbl;

Working Example:

SELECT id, username FROM users;

# 2 WHERE DQL

Filter output matching condition.

Syntax:

SELECT * FROM tbl WHERE col = val;

Working Example:

SELECT * FROM products WHERE price > 50;

# 3 INNER JOIN Joins

Match rows across joined tables.

Syntax:

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.fk;

Working Example:

SELECT u.name, o.id FROM users u INNER JOIN orders o ON u.id = o.user_id;

# 4 LEFT JOIN Joins

Keep all left rows with matching right rows.

Syntax:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.fk;

Working Example:

SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id;

# 5 GROUP BY Aggregations

Group rows for calculation.

Syntax:

SELECT col, COUNT(*) FROM tbl GROUP BY col;

Working Example:

SELECT status, COUNT(*) FROM orders GROUP BY status;

# 6 HAVING Aggregations

Filter aggregated results.

Syntax:

SELECT col, SUM(x) FROM tbl GROUP BY col HAVING SUM(x) > 1000;

Working Example:

SELECT dept, SUM(sales) FROM rep GROUP BY dept HAVING SUM(sales) > 10000;

# 7 INSERT INTO DML

Add new records into table.

Syntax:

INSERT INTO tbl (col1) VALUES (val1);

Working Example:

INSERT INTO customers (name) VALUES ('Acme Corp');

# 8 UPDATE SET DML

Modify column values in rows.

Syntax:

UPDATE tbl SET col = val WHERE condition;

Working Example:

UPDATE orders SET status = 'shipped' WHERE id = 101;

# 9 DELETE FROM DML

Remove records from table.

Syntax:

DELETE FROM tbl WHERE condition;

Working Example:

DELETE FROM logs WHERE created_at < '2026-01-01';

# 10 ON DUPLICATE KEY UPDATE DML

Execute upsert on unique key duplicate.

Syntax:

INSERT INTO tbl (id, val) VALUES (1, 10) ON DUPLICATE KEY UPDATE val = val + 10;

Working Example:

INSERT INTO views (page_id, count) VALUES (5, 1) ON DUPLICATE KEY UPDATE count = count + 1;

# 11 GROUP_CONCAT Aggregations

Combine text values into single string.

Syntax:

SELECT GROUP_CONCAT(col SEPARATOR ', ') FROM tbl GROUP BY grp;

Working Example:

SELECT dept_id, GROUP_CONCAT(name SEPARATOR ', ') FROM emp GROUP BY dept_id;

# 12 DATE_FORMAT Functions

Format DATETIME into custom string.

Syntax:

SELECT DATE_FORMAT(dt, '%Y-%m-%d') FROM tbl;

Working Example:

SELECT order_id, DATE_FORMAT(created_at, '%W, %M %e') FROM orders;

# 13 LIMIT / OFFSET Pagination

Paginate result rows.

Syntax:

SELECT * FROM tbl ORDER BY col LIMIT count OFFSET skip;

Working Example:

SELECT * FROM items ORDER BY id DESC LIMIT 10 OFFSET 20;

# 14 CREATE TABLE DDL

Define new table with AUTO_INCREMENT key.

Syntax:

CREATE TABLE tbl (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));

Working Example:

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL);

# 15 ALTER TABLE DDL

Add or drop columns from table.

Syntax:

ALTER TABLE tbl ADD COLUMN col datatype;

Working Example:

ALTER TABLE users ADD COLUMN age INT;

# 16 CREATE INDEX DDL

Build index on table columns.

Syntax:

CREATE INDEX idx_name ON tbl(col);

Working Example:

CREATE INDEX idx_user_email ON users(email);

# 17 EXPLAIN FORMAT=JSON Performance

Analyze MySQL optimizer execution details.

Syntax:

EXPLAIN FORMAT=JSON SELECT * FROM tbl WHERE col = val;

Working Example:

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42;

# 18 SHOW PROCESSLIST DBA

Inspect active database connections.

Syntax:

SHOW FULL PROCESSLIST;

Working Example:

SHOW FULL PROCESSLIST;

# 19 KILL PROCESS DBA

Terminate stuck client thread ID.

Syntax:

KILL process_id;

Working Example:

KILL 1234;

# 20 JSON_EXTRACT Functions

Parse nested JSON column property.

Syntax:

SELECT JSON_UNQUOTE(JSON_EXTRACT(json_col, '$.key')) FROM tbl;

Working Example:

SELECT JSON_UNQUOTE(JSON_EXTRACT(meta, '$.role')) FROM users;