---
title: "MySQL Reference Cheatsheet | FindUtils"
description: "MySQL queries, joins, indexes, user management, and database administration"
url: https://findutils.com/cheatsheets/mysql-reference/
---

# MySQL Reference Cheatsheet

MySQL queries, joins, indexes, user management, and database administration 64 commands in 9 sections, filed under [Programming](https://findutils.com/cheatsheets/programming/).

## Connection & Basics

- `mysql -u root -p`: Connect to MySQL as root
- `mysql -h host -u user -p dbname`: Connect to remote database
- `mysql -u user -p < dump.sql`: Import SQL file
- `mysqldump -u user -p dbname > dump.sql`: Export database to SQL file
- `mysqldump -u user -p --all-databases > all.sql`: Export all databases
- `SHOW DATABASES;`: List all databases
- `USE database_name;`: Switch to a database
- `SHOW TABLES;`: List tables in current database
- `DESCRIBE table_name;`: Show table structure and columns
- `SHOW CREATE TABLE table_name;`: Show table creation SQL

## Database & Table Management

- `CREATE DATABASE dbname CHARACTER SET utf8mb4;`: Create database with UTF-8 support
- `DROP DATABASE dbname;`: Delete a database
- `CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255));`: Create a table
- `DROP TABLE table_name;`: Delete a table
- `TRUNCATE TABLE table_name;`: Delete all rows (faster than DELETE)
- `RENAME TABLE old_name TO new_name;`: Rename a table
- `ALTER TABLE users ADD email VARCHAR(255) AFTER name;`: Add column after specific column
- `ALTER TABLE users DROP COLUMN email;`: Remove a column
- `ALTER TABLE users MODIFY name VARCHAR(500);`: Change column type

## SELECT Queries

- `SELECT * FROM users;`: Select all columns and rows
- `SELECT name, email FROM users WHERE id = 1;`: Select specific columns with filter
- `SELECT DISTINCT status FROM orders;`: Select unique values only
- `SELECT * FROM users ORDER BY created_at DESC;`: Sort results descending
- `SELECT * FROM users LIMIT 10 OFFSET 20;`: Paginate results (page 3)
- `SELECT * FROM users WHERE name LIKE "%john%";`: Pattern matching search
- `SELECT * FROM users WHERE id IN (1, 2, 3);`: Filter by list of values
- `SELECT * FROM users WHERE age BETWEEN 18 AND 30;`: Filter by range
- `SELECT * FROM users WHERE email IS NOT NULL;`: Filter for non-null values

## INSERT, UPDATE & DELETE

- `INSERT INTO users (name, email) VALUES ('Alice', 'a@b.com');`: Insert a single row
- `INSERT INTO users (name) VALUES ('A'), ('B'), ('C');`: Insert multiple rows
- `INSERT INTO target SELECT * FROM source;`: Insert from another table
- `UPDATE users SET name = 'Bob' WHERE id = 1;`: Update specific rows
- `UPDATE users SET status = "active" WHERE last_login > NOW() - INTERVAL 30 DAY;`: Update with date condition
- `DELETE FROM users WHERE id = 1;`: Delete specific rows
- `DELETE FROM logs WHERE created_at < "2025-01-01";`: Delete old records
- `INSERT INTO ... ON DUPLICATE KEY UPDATE name = VALUES(name);`: Upsert (insert or update)

## JOINs

- `SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id;`: Inner join (matching rows only)
- `SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;`: Left join (all from left table)
- `SELECT * FROM orders RIGHT JOIN users ON orders.user_id = users.id;`: Right join (all from right table)
- `SELECT * FROM t1 CROSS JOIN t2;`: Cross join (cartesian product)
- `SELECT * FROM users u JOIN orders o ON u.id = o.user_id JOIN items i ON o.id = i.order_id;`: Multi-table join
- `SELECT * FROM employees e JOIN employees m ON e.manager_id = m.id;`: Self join

## Aggregation & Grouping

- `SELECT COUNT(*) FROM users;`: Count all rows
- `SELECT status, COUNT(*) FROM users GROUP BY status;`: Count by group
- `SELECT AVG(price), SUM(price) FROM orders;`: Average and sum
- `SELECT MAX(salary), MIN(salary) FROM employees;`: Max and min values
- `SELECT status, COUNT(*) FROM orders GROUP BY status HAVING COUNT(*) > 10;`: Filter groups with HAVING
- `SELECT YEAR(created_at), COUNT(*) FROM users GROUP BY YEAR(created_at);`: Group by date part

## Indexes & Performance

- `CREATE INDEX idx_email ON users(email);`: Create an index on column
- `CREATE UNIQUE INDEX idx_email ON users(email);`: Create unique index
- `CREATE INDEX idx_name_email ON users(name, email);`: Create composite index
- `DROP INDEX idx_email ON users;`: Delete an index
- `SHOW INDEX FROM users;`: List indexes on a table
- `EXPLAIN SELECT * FROM users WHERE email = "a@b.com";`: Analyze query execution plan
- `EXPLAIN ANALYZE SELECT * FROM users;`: Execute and show actual timing
- `SHOW PROCESSLIST;`: Show active queries and connections

## User & Access Management

- `CREATE USER 'app'@'%' IDENTIFIED BY 'pass';`: Create user with password
- `GRANT ALL ON dbname.* TO 'app'@'%';`: Grant all privileges on database
- `GRANT SELECT, INSERT ON dbname.* TO 'reader'@'%';`: Grant specific privileges
- `REVOKE ALL ON dbname.* FROM 'app'@'%';`: Revoke all privileges
- `DROP USER 'app'@'%';`: Delete a user
- `FLUSH PRIVILEGES;`: Reload privilege tables
- `SHOW GRANTS FOR 'app'@'%';`: Show user privileges

## Sources

- `https://dev.mysql.com/doc/refman/8.4/en/`: MySQL 8.4 manual: SQL syntax, indexes, accounts, and administration.

## Related

- Cheatsheet: [Barcode Formats](https://findutils.com/cheatsheets/barcode-formats/)
- Cheatsheet: [JavaScript ES6+](https://findutils.com/cheatsheets/javascript-es6/)
- Cheatsheet: [Python Essentials](https://findutils.com/cheatsheets/python-essentials/)
- Guide: [Convert a SQL Dump to CSV Without Restoring a Database](https://findutils.com/guides/sql-to-csv/)
- Guide: [Convert SQL INSERT Statements to JSON With the Right Types](https://findutils.com/guides/sql-to-json/)
- Guide: [Convert JSON to SQL INSERT Statements Without Losing Types](https://findutils.com/guides/json-to-sql/)
- Tool: [CSV to SQL](https://findutils.com/convert/csv-to-sql/)
- Tool: [JSON to SQL](https://findutils.com/convert/json-to-sql/)
- Tool: [SQL to CSV](https://findutils.com/convert/sql-to-csv/)