MySQL Cheat Sheet

MySQL cheatsheet providing an advanced, categorized reference of MySQL syntax and features used in production databases.

Connecting to MySQL

Commands for connecting to and exiting the MySQL client.

mysql -u username -p
mysql -u username -p database_name

exit;

Database Management

Creating, selecting, and removing databases.

CREATE DATABASE db_name;
SHOW DATABASES;
USE db_name;
DROP DATABASE db_name;

SHOW TABLES;

Table Creation

Defining tables, primary keys, and constraints.

CREATE TABLE table_name (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  created_at DATETIME
);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Data Types

Common MySQL data types.

CHAR, VARCHAR, TEXT, MEDIUMTEXT, LONGTEXT
INT, BIGINT, FLOAT, DOUBLE, DECIMAL
DATE, TIME, DATETIME, TIMESTAMP, YEAR
ENUM, SET, BLOB

VARCHAR(255)
DECIMAL(10,2)

ALTER TABLE

Modifying existing tables.

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name MODIFY column_name datatype;
ALTER TABLE table_name RENAME new_table_name;

ALTER TABLE users ADD email VARCHAR(255);

DROP and TRUNCATE

Removing tables or data.

DROP TABLE table_name;
TRUNCATE TABLE table_name;

TRUNCATE TABLE logs;

INSERT

Adding rows to tables.

INSERT INTO table_name (col1, col2)
VALUES (val1, val2);

INSERT INTO users (name, age) VALUES ('Alice', 30);

INSERT Variants

Advanced insert behaviors.

INSERT IGNORE INTO table_name VALUES (...);
REPLACE INTO table_name VALUES (...);
INSERT INTO table_name
ON DUPLICATE KEY UPDATE column = value;

INSERT INTO users (email)
VALUES ('a@test.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

SELECT

Querying data from tables.

SELECT column_list
FROM table_name
WHERE condition
ORDER BY column
LIMIT n OFFSET m;

SELECT id, name FROM users WHERE age > 18 ORDER BY name;

WHERE Conditions

Filtering rows.

=, !=, <, >, <=, >=
BETWEEN, IN, LIKE, IS NULL

SELECT * FROM users WHERE name LIKE 'A%';

JOINs

Combining rows from multiple tables.

SELECT *
FROM table1
INNER JOIN table2 ON condition;

LEFT JOIN
RIGHT JOIN
CROSS JOIN

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

GROUP BY and HAVING

Aggregation and filtering grouped results.

SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > 1;

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

Aggregate Functions

Functions operating on groups.

COUNT(), SUM(), AVG(), MIN(), MAX()

SELECT AVG(price) FROM products;

Subqueries

Nested queries.

SELECT *
FROM table
WHERE column IN (SELECT column FROM other_table);

SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);

EXISTS

Checking existence of rows.

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM other_table WHERE condition);

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

UNION

Combining result sets.

SELECT column FROM table1
UNION
SELECT column FROM table2;

UNION ALL

Indexes

Improving query performance.

CREATE INDEX idx_name ON table(column);
CREATE UNIQUE INDEX idx_unique ON table(column);
DROP INDEX idx_name ON table;

CREATE INDEX idx_email ON users(email);

Views

Stored query definitions.

CREATE VIEW view_name AS
SELECT column FROM table;

DROP VIEW view_name;

Transactions

Atomic execution of statements.

START TRANSACTION;
COMMIT;
ROLLBACK;
SAVEPOINT sp_name;

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK;

Stored Procedures

Reusable SQL routines.

CREATE PROCEDURE proc_name ()
BEGIN
  SQL statements;
END;

CALL proc_name();

Functions

User-defined functions.

CREATE FUNCTION func_name(param datatype)
RETURNS datatype
BEGIN
  RETURN value;
END;

SELECT func_name(10);

Triggers

Automatic execution on table events.

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  SQL statements;
END;

DROP TRIGGER trigger_name;

String Functions

Common string operations.

CONCAT(), LENGTH(), LOWER(), UPPER()
SUBSTRING(), REPLACE(), TRIM()

SELECT CONCAT(first_name, ' ', last_name) FROM users;

Numeric Functions

Numeric calculations.

ABS(), ROUND(), CEIL(), FLOOR(), MOD(), SQRT()

SELECT ROUND(123.456, 2);

Date and Time Functions

Working with dates and times.

NOW(), CURDATE(), CURTIME()
DATE_ADD(), DATE_SUB(), DATEDIFF()

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);

Casting

Type conversion.

CAST(value AS datatype);

SELECT CAST('123' AS SIGNED);

Performance and Debugging

Inspecting query behavior.

EXPLAIN SELECT ...
SHOW PROCESSLIST;

EXPLAIN SELECT * FROM users WHERE email = 'a@test.com';

Backup and Restore

Exporting and importing data.

mysqldump -u user -p db_name > backup.sql
mysql -u user -p db_name < backup.sql