📖 Guide
MySQL — Complete Reference
Comprehensive MySQL cheat sheet covering queries, joins, indexes, transactions, and database administration.
98 commands across 12 categories
Database ManagementTable OperationsCRUDFiltering & SortingJoinsAggregationSubqueriesIndexesUsers & PermissionsData TypesCommon FunctionsTransactions
Database Management
| Command | Description |
|---|---|
CREATE DATABASE dbname; | Create a new database |
CREATE DATABASE IF NOT EXISTS dbname; | Create database only if it doesn't exist |
DROP DATABASE dbname; | Delete a database and all its tables |
SHOW DATABASES; | List all databases on the server |
USE dbname; | Switch to a database |
SELECT DATABASE(); | Show currently selected database |
ALTER DATABASE dbname CHARACTER SET utf8mb4; | Change database character set |
SHOW CREATE DATABASE dbname; | Show the CREATE statement for a database |
Table Operations
| Command | Description |
|---|---|
CREATE TABLE users (\n id INT AUTO_INCREMENT PRIMARY KEY,\n name VARCHAR(100) NOT NULL,\n email VARCHAR(255) UNIQUE\n); | Create a table with columns and constraints |
SHOW TABLES; | List all tables in current database |
DESCRIBE users; | Show table structure (columns, types, keys) |
ALTER TABLE users ADD age INT; | Add a column to existing table |
ALTER TABLE users DROP COLUMN age; | Remove a column |
ALTER TABLE users MODIFY name VARCHAR(200); | Change column type |
ALTER TABLE users RENAME TO members; | Rename a table |
DROP TABLE IF EXISTS users; | Delete table if it exists |
TRUNCATE TABLE users; | Delete all rows (faster than DELETE, resets auto-increment) |
CRUD
| Command | Description |
|---|---|
INSERT INTO users (name, email) VALUES ('Dan', 'dan@ex.com'); | Insert a single row |
INSERT INTO users (name, email) VALUES\n ('A', 'a@x.com'),\n ('B', 'b@x.com'); | Insert multiple rows at once |
SELECT * FROM users; | Select all columns from table |
SELECT name, email FROM users WHERE id = 1; | Select specific columns with condition |
UPDATE users SET name = 'Daniel' WHERE id = 1; | Update rows matching condition |
DELETE FROM users WHERE id = 1; | Delete rows matching condition |
INSERT INTO users (name, email) VALUES ('Dan', 'dan@ex.com')\n ON DUPLICATE KEY UPDATE name = 'Dan'; | Insert or update on duplicate key |
REPLACE INTO users (id, name) VALUES (1, 'Dan'); | Insert or replace existing row |
Filtering & Sorting
| Command | Description |
|---|---|
WHERE age > 18 AND status = 'active' | Filter with conditions (AND, OR, NOT) |
WHERE name LIKE 'Dan%' | Pattern matching (% = any chars, _ = one char) |
WHERE age BETWEEN 18 AND 30 | Range filter (inclusive) |
WHERE status IN ('active', 'pending') | Match any value in a list |
WHERE email IS NULL | Check for NULL values (use IS, not =) |
ORDER BY name ASC, age DESC | Sort results (ASC default, DESC descending) |
LIMIT 10 OFFSET 20 | Limit results with pagination |
SELECT DISTINCT country FROM users; | Return only unique values |
Joins
| Command | Description |
|---|---|
SELECT * FROM users\n INNER JOIN orders ON users.id = orders.user_id; | Inner join — only matching rows from both tables |
SELECT * FROM users\n LEFT JOIN orders ON users.id = orders.user_id; | Left join — all left rows, NULLs for unmatched right |
SELECT * FROM users\n RIGHT JOIN orders ON users.id = orders.user_id; | Right join — all right rows, NULLs for unmatched left |
SELECT * FROM a CROSS JOIN b; | Cross join — cartesian product of both tables |
SELECT * FROM users u\n JOIN orders o ON u.id = o.user_id\n JOIN products p ON o.product_id = p.id; | Multi-table join with aliases |
SELECT * FROM users\n LEFT JOIN orders ON users.id = orders.user_id\n WHERE orders.id IS NULL; | Find rows with no match (anti-join) |
SELECT a.name, b.name FROM users a\n JOIN users b ON a.manager_id = b.id; | Self join — join table to itself |
Aggregation
| Command | Description |
|---|---|
SELECT COUNT(*) FROM users; | Count total rows |
SELECT SUM(amount) FROM orders; | Sum of a numeric column |
SELECT AVG(age) FROM users; | Average value |
SELECT MIN(price), MAX(price) FROM products; | Minimum and maximum values |
SELECT country, COUNT(*) FROM users GROUP BY country; | Group rows and aggregate |
GROUP BY country HAVING COUNT(*) > 5 | Filter groups (WHERE filters rows, HAVING filters groups) |
SELECT country, COUNT(*) AS total FROM users\n GROUP BY country\n ORDER BY total DESC; | Alias aggregation result and sort |
SELECT country, GROUP_CONCAT(name SEPARATOR ', ') FROM users GROUP BY country; | Concatenate grouped values into string |
Subqueries
| Command | Description |
|---|---|
SELECT * FROM users WHERE id IN\n (SELECT user_id FROM orders); | Subquery in WHERE clause |
SELECT *, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count\nFROM users; | Correlated subquery in SELECT |
SELECT * FROM users WHERE age >\n (SELECT AVG(age) FROM users); | Compare against aggregate subquery |
SELECT * FROM (\n SELECT country, COUNT(*) AS cnt FROM users GROUP BY country\n) AS sub WHERE cnt > 5; | Derived table (subquery in FROM) |
SELECT * FROM users WHERE EXISTS\n (SELECT 1 FROM orders WHERE orders.user_id = users.id); | EXISTS — check if subquery returns any rows |
WITH cte AS (\n SELECT country, COUNT(*) AS cnt FROM users GROUP BY country\n)\nSELECT * FROM cte WHERE cnt > 5; | Common Table Expression (CTE, MySQL 8.0+) |
Indexes
| Command | Description |
|---|---|
CREATE INDEX idx_name ON users(name); | Create an index on a column |
CREATE UNIQUE INDEX idx_email ON users(email); | Create unique index (enforces uniqueness) |
CREATE INDEX idx_comp ON orders(user_id, status); | Composite index on multiple columns |
DROP INDEX idx_name ON users; | Remove an index |
SHOW INDEX FROM users; | List all indexes on a table |
EXPLAIN SELECT * FROM users WHERE name = 'Dan'; | Show query execution plan (check if index is used) |
CREATE FULLTEXT INDEX idx_ft ON posts(title, body); | Full-text search index |
ALTER TABLE users ADD INDEX idx_age(age); | Add index via ALTER TABLE |
Users & Permissions
| Command | Description |
|---|---|
CREATE USER 'app'@'localhost' IDENTIFIED BY 'pass'; | Create a new user |
GRANT ALL PRIVILEGES ON dbname.* TO 'app'@'localhost'; | Grant full access to a database |
GRANT SELECT, INSERT ON dbname.users TO 'app'@'localhost'; | Grant specific privileges on a table |
REVOKE INSERT ON dbname.users FROM 'app'@'localhost'; | Remove specific privilege |
SHOW GRANTS FOR 'app'@'localhost'; | View user privileges |
DROP USER 'app'@'localhost'; | Delete a user |
ALTER USER 'app'@'localhost' IDENTIFIED BY 'newpass'; | Change user password |
FLUSH PRIVILEGES; | Reload privilege tables (after manual edits) |
Data Types
| Command | Description |
|---|---|
INT / BIGINT | Integer (4 bytes) / large integer (8 bytes) |
DECIMAL(10,2) | Exact decimal — use for money (10 digits, 2 after point) |
FLOAT / DOUBLE | Approximate floating point (avoid for money) |
VARCHAR(255) | Variable-length string up to specified max |
TEXT / MEDIUMTEXT / LONGTEXT | Large text fields (64KB / 16MB / 4GB) |
DATETIME / TIMESTAMP | Date+time (DATETIME: no timezone, TIMESTAMP: UTC-converted) |
DATE / TIME | Date only (YYYY-MM-DD) / time only (HH:MM:SS) |
BOOLEAN | Alias for TINYINT(1) — 0 is false, 1 is true |
JSON | Native JSON column type (MySQL 5.7+) |
ENUM('a','b','c') | Column restricted to listed values |
Common Functions
| Command | Description |
|---|---|
NOW() | Current date and time |
CURDATE() / CURTIME() | Current date / current time |
DATE_FORMAT(date, '%Y-%m-%d') | Format date as string |
DATEDIFF(d1, d2) | Days between two dates |
CONCAT(first, ' ', last) | Concatenate strings |
COALESCE(val1, val2, 'default') | Return first non-NULL value |
IFNULL(expr, 'fallback') | Return fallback if expr is NULL |
CAST(val AS UNSIGNED) | Convert value to different type |
LENGTH(str) / CHAR_LENGTH(str) | Byte length / character length of string |
SUBSTRING(str, start, len) | Extract substring |
Transactions
| Command | Description |
|---|---|
START TRANSACTION; | Begin a transaction |
COMMIT; | Save all changes made in transaction |
ROLLBACK; | Undo all changes made in transaction |
SAVEPOINT sp1; | Create a savepoint within transaction |
ROLLBACK TO sp1; | Roll back to a savepoint |
SET autocommit = 0; | Disable auto-commit (each statement needs explicit COMMIT) |
SELECT ... FOR UPDATE; | Lock selected rows until transaction ends |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; | Set isolation level for next transaction |
📖 Free, searchable command reference. Bookmark this page for quick access.