glunty

MySQL Cheat Sheet

A searchable reference of common MySQL 8 commands and what each one does.

Syntax What it does
mysql -u user -p Connect to the MySQL server as a user and prompt for a password.
SHOW DATABASES; List all databases the current account can see.
USE mydb; Select a database as the default for later statements.
SHOW TABLES; List the tables in the current database.
DESCRIBE users; Show the columns, types, and keys of a table.
SHOW COLUMNS FROM users; List the columns of a table with their definitions, like DESCRIBE.
SOURCE dump.sql Run every SQL statement from a file in the current session.
mysqldump -u user -p mydb > dump.sql Export a database to a SQL file from the shell.
SHOW STATUS; Display server status counters such as connections and queries.
exit Close the mysql client session; quit works too.
SELECT * FROM users; Return every column and row from a table.
SELECT name, email FROM users; Return only the columns you name.
SELECT * FROM users WHERE age > 18; Return only the rows that match a condition.
SELECT * FROM users LIMIT 10; Return at most the given number of rows.
SELECT * FROM users LIMIT 10 OFFSET 20; Skip some rows, then return the next batch, useful for paging.
SELECT * FROM users ORDER BY name ASC; Sort the result rows by a column, ascending or descending.
SELECT * FROM users WHERE name LIKE 'a%'; Match rows by a pattern, where % is any run of characters.
SELECT * FROM users WHERE id IN (1, 2, 3); Match rows whose value appears in a list.
SELECT * FROM users WHERE age BETWEEN 18 AND 30; Match rows within an inclusive range.
SELECT DISTINCT city FROM users; Return each unique value once and drop duplicates.
SELECT city, COUNT(*) FROM users GROUP BY city; Collapse rows into groups and aggregate each group.
SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5; Filter grouped results after aggregation, unlike WHERE which filters rows first.
SELECT name AS full_name FROM users u; Give a column or table a short alias.
SELECT * FROM a INNER JOIN b ON a.id = b.a_id; Return only rows that have a match in both tables.
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id; Return all left-table rows, with NULLs where the right has no match.
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id; Return all right-table rows, with NULLs where the left has no match.
SELECT * FROM a CROSS JOIN b; Return every combination of rows from both tables, the Cartesian product.
SELECT * FROM emp e JOIN emp m ON e.mgr_id = m.id; Join a table to itself using two aliases, a self join.
SELECT * FROM a JOIN b ON a.id = b.a_id; The ON clause states the condition two rows must meet to join.
SELECT * FROM a JOIN b USING (id); Shorthand for ON when both tables share the join column name.
INSERT INTO users (name, email) VALUES ('Ann', 'a@x.com'); Add one new row with the given column values.
INSERT INTO users (name) VALUES ('Ann'), ('Bob'); Add several rows in a single statement.
UPDATE users SET age = 21 WHERE id = 1; Change column values in the rows that match a condition.
DELETE FROM users WHERE id = 1; Remove the rows that match a condition.
REPLACE INTO users (id, name) VALUES (1, 'Ann'); Insert a row, or delete and reinsert it when the key already exists.
INSERT INTO users (id, hits) VALUES (1, 1) ON DUPLICATE KEY UPDATE hits = hits + 1; Insert a row, or update the existing one when a unique key collides.
TRUNCATE TABLE logs; Delete every row quickly by recreating the table.
CREATE DATABASE shop; Create a new empty database.
CREATE TABLE users (id INT, name VARCHAR(100)); Create a table with named, typed columns.
INT, VARCHAR(n), TEXT, DATETIME Common column types: whole number, short string, long text, and date and time.
id INT AUTO_INCREMENT Auto-generate the next integer for each new row, often the key.
PRIMARY KEY (id) Mark a column as the unique, non-null row identifier.
FOREIGN KEY (user_id) REFERENCES users (id) Require each value to exist in a column of another table.
ALTER TABLE users ADD COLUMN age INT; Alter a table, here by adding a new column.
ALTER TABLE users MODIFY COLUMN name VARCHAR(200); Change the type or size of an existing column.
CREATE INDEX idx_name ON users (name); Create an index to speed up lookups on a column.
DROP TABLE users; Permanently delete a table and all of its data.
CREATE USER 'ann'@'localhost' IDENTIFIED BY 'pw'; Create a login account with a password.
GRANT ALL ON shop.* TO 'ann'@'localhost'; Give a user privileges on a database or table.
REVOKE ALL ON shop.* FROM 'ann'@'localhost'; Take privileges back from a user.
FLUSH PRIVILEGES; Reload the grant tables so privilege changes take effect.
SHOW GRANTS FOR 'ann'@'localhost'; List the privileges granted to a user.
SELECT NOW(); Return the current date and time from the server.
SELECT CONCAT(first, ' ', last); Join several strings into one value.
SELECT COALESCE(a, b, c); Return the first value in the list that is not null.
SELECT IFNULL(phone, 'n/a'); Return a fallback value when the first argument is null.
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; Page through results by skipping rows then taking a batch.

Runs entirely in your browser. Nothing you type is sent anywhere; open DevTools and watch the Network tab to verify zero requests.

What this tool does

This is a searchable quick reference for the MySQL 8 statements you reach for every day, from connecting on the command line to querying, joining, inserting, shaping schema, and managing users. Each row pairs the exact syntax with a plain-English note on what it does. Type in the filter box to search across both columns, or tap a category button to focus on one area. The whole list is built into the page, so it works offline and sends nothing anywhere.

How to use it

Start typing in the Filter box. Entering join surfaces every join form; entering grant jumps to the privilege commands; entering insert shows the ways to add rows. The category buttons (CLI, Query, Joins, Modify, Schema, Admin) narrow the table to one area and combine with the text filter, so you can pick Schema and type alter to zero in on table changes. Clear the box to see the full sheet again.

Common use cases

  • Recalling the exact syntax for a statement you use rarely, like ON DUPLICATE KEY UPDATE or REPLACE INTO.
  • Copying a working query into your terminal instead of guessing flags.
  • Teaching a teammate the difference between INNER JOIN, LEFT JOIN, and a self join.
  • Setting up a new account with CREATE USER, GRANT, and REVOKE.
  • Reviewing the core MySQL workflow before an interview or a new job.

Common pitfalls

  • UPDATE or DELETE with no WHERE. Omitting the condition changes or removes every row in the table. Always run a SELECT with the same WHERE first to see what will be affected.
  • TRUNCATE is not an UPDATE. TRUNCATE TABLE recreates the table and cannot be rolled back the way a DELETE in a transaction can, so treat it as permanent.
  • HAVING versus WHERE. WHERE filters individual rows before grouping, while HAVING filters the groups after aggregation. Using WHERE on an aggregate like COUNT(*) is an error.
  • Forgetting the database. Many commands act on the current database, so run USE mydb first or fully qualify names as db.table to avoid touching the wrong schema.

Frequently asked questions

How do I list tables in MySQL?
First pick a database with USE mydb, then run SHOW TABLES to list every table in it. To see the structure of one table, run DESCRIBE tablename or SHOW COLUMNS FROM tablename, which list the columns, types, and keys. SHOW DATABASES lists the databases you can reach before you choose one.
What are the basic differences between MySQL and PostgreSQL?
Both are free relational databases that speak SQL, so everyday SELECT, INSERT, and JOIN syntax is nearly identical. MySQL uses AUTO_INCREMENT for generated keys and is known for being simple and widely hosted, while PostgreSQL uses SERIAL or identity columns and leans toward strict standards, richer types, and advanced features. For a basic cheat sheet like this one the commands mostly carry over, with small differences in administration and data types.
What does AUTO_INCREMENT do?
AUTO_INCREMENT tells MySQL to fill an integer column with the next unused number each time you insert a row, so you do not have to supply an id yourself. It is almost always paired with a PRIMARY KEY to give every row a unique identifier. You can read the value assigned to the most recent insert with SELECT LAST_INSERT_ID().
How do I create a user and grant access in MySQL?
Create the account with CREATE USER, for example CREATE USER 'ann'@'localhost' IDENTIFIED BY 'pw'. Then give it rights with GRANT, such as GRANT ALL ON shop.* TO 'ann'@'localhost'. Older MySQL versions needed FLUSH PRIVILEGES afterward, but modern versions apply GRANT changes immediately. Use REVOKE to remove rights and SHOW GRANTS FOR the account to review them.
What is the difference between an INNER JOIN and a LEFT JOIN?
An INNER JOIN returns only the rows that have a match in both tables, so unmatched rows on either side disappear from the result. A LEFT JOIN returns every row from the left table plus any matching rows from the right, filling the right-side columns with NULL where there is no match. Reach for a LEFT JOIN when you want to keep all records from the first table, such as listing every customer even those with no orders.
Does this MySQL cheat sheet send my searches anywhere?
No. The whole command list is baked into the page and every search and filter runs in your browser with JavaScript, so nothing you type leaves your device. Open your browser DevTools and watch the Network tab while you search to confirm there are zero requests.

Embed this tool

Free for any use; attribution appreciated. Paste this on your site:

The embed runs the same tool that lives at this URL. No tracking; no ads inside the embed. Resize height as needed for your layout.

Cite this tool

For academic, journalistic, or technical references. Pick a format:

Citations use 2026 as the publication year. Access date is left as a fillable placeholder where the citation style expects one.

Embedded tool from glunty.com