Developer Cheatsheets

Developer Cheatsheets

Built for developers, by XinhND

v2.1.0

Ready

MySQL Cheat Sheet

Complete reference guide for MySQL with interactive examples and live playground links

Basic Operations

3 items

Database Operations
Table Operations
Data Types

Queries

3 items

SELECT Queries
JOIN Operations
Aggregate Functions

Data Modifications

3 items

INSERT Operations
UPDATE Operations
DELETE Operations

Advanced Features

3 items

Indexes
Transactions
Stored Procedures

Optimization

3 items

Query Optimization
Performance Tuning
Backup and Recovery

Click on any section to jump directly to it

Basic Operations

Database Operations

Basic database operations

MySQL
-- Create database
CREATE DATABASE mydb;

-- Use database
USE mydb;

-- Show databases
SHOW DATABASES;

-- Drop database
DROP DATABASE mydb;

-- Show tables
SHOW TABLES;

-- Show table structure
DESCRIBE users;
SHOW COLUMNS FROM users;

Table Operations

Table creation and modification

MySQL
-- Create table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Alter table
ALTER TABLE users
    ADD COLUMN phone VARCHAR(20),
    MODIFY COLUMN name VARCHAR(150),
    DROP COLUMN age;

-- Drop table
DROP TABLE users;

-- Truncate table
TRUNCATE TABLE users;

-- Rename table
RENAME TABLE old_name TO new_name;

Data Types

Common MySQL data types

MySQL
-- Numeric Types
INT, BIGINT, TINYINT, SMALLINT
DECIMAL(10,2), FLOAT, DOUBLE

-- String Types
CHAR(10), VARCHAR(255), TEXT
TINYTEXT, MEDIUMTEXT, LONGTEXT

-- Date and Time
DATE, TIME, DATETIME, TIMESTAMP
YEAR

-- Binary Types
BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB

-- Other Types
BOOLEAN, ENUM('value1', 'value2')
JSON, GEOMETRY

Queries

SELECT Queries

SELECT query patterns

MySQL
-- Basic SELECT
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- With conditions
SELECT * FROM users WHERE age > 18;

-- Sorting
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY age DESC, name ASC;

-- Limiting results
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Distinct values
SELECT DISTINCT country FROM users;

-- Aliases
SELECT u.name AS user_name, p.title AS post_title
FROM users u
JOIN posts p ON u.id = p.user_id;

JOIN Operations

JOIN operations and patterns

MySQL
-- Inner Join
SELECT users.name, orders.order_number
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- Left Join
SELECT users.name, orders.order_number
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Right Join
SELECT users.name, orders.order_number
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- Cross Join
SELECT users.name, products.name
FROM users
CROSS JOIN products;

-- Self Join
SELECT a.name AS employee, b.name AS manager
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;

Aggregate Functions

Aggregate functions and grouping

MySQL
-- Basic aggregates
SELECT 
    COUNT(*) as total_users,
    AVG(age) as average_age,
    MAX(age) as max_age,
    MIN(age) as min_age,
    SUM(points) as total_points
FROM users;

-- Group By
SELECT 
    country,
    COUNT(*) as user_count,
    AVG(age) as avg_age
FROM users
GROUP BY country
HAVING COUNT(*) > 10;

-- With conditions
SELECT 
    department,
    COUNT(*) as employee_count
FROM employees
WHERE salary > 50000
GROUP BY department;

Data Modifications

INSERT Operations

INSERT operations and patterns

MySQL
-- Single row insert
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

-- Multiple rows
INSERT INTO users (name, email, age)
VALUES 
    ('Jane Doe', 'jane@example.com', 25),
    ('Bob Smith', 'bob@example.com', 35);

-- Insert from select
INSERT INTO new_users (name, email)
SELECT name, email FROM old_users
WHERE status = 'active';

-- Insert ignore
INSERT IGNORE INTO users (email, name)
VALUES ('john@example.com', 'John Doe');

UPDATE Operations

UPDATE operations and patterns

MySQL
-- Basic update
UPDATE users
SET name = 'John Smith',
    email = 'john.smith@example.com'
WHERE id = 1;

-- Update with join
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.total_orders = u.total_orders + 1
WHERE o.status = 'completed';

-- Update with limit
UPDATE users
SET status = 'inactive'
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 1000;

-- Update with case
UPDATE products
SET price = CASE
    WHEN category = 'premium' THEN price * 1.1
    WHEN category = 'standard' THEN price * 1.05
    ELSE price
END;

DELETE Operations

DELETE operations and patterns

MySQL
-- Basic delete
DELETE FROM users WHERE id = 1;

-- Delete with join
DELETE u FROM users u
JOIN inactive_users i ON u.id = i.user_id
WHERE i.last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Delete with limit
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 MONTH)
LIMIT 1000;

-- Delete with order
DELETE FROM users
WHERE status = 'inactive'
ORDER BY last_login ASC
LIMIT 100;

Advanced Features

Indexes

Index creation and management

MySQL
-- Create index
CREATE INDEX idx_email ON users(email);

-- Create unique index
CREATE UNIQUE INDEX idx_username ON users(username);

-- Create composite index
CREATE INDEX idx_name_age ON users(name, age);

-- Create fulltext index
CREATE FULLTEXT INDEX idx_content ON posts(content);

-- Drop index
DROP INDEX idx_email ON users;

-- Show indexes
SHOW INDEXES FROM users;

Transactions

Transaction management

MySQL
-- Basic transaction
START TRANSACTION;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
    UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Transaction with rollback
START TRANSACTION;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
    UPDATE users SET balance = balance - 100 WHERE id = 1;
    
    IF (SELECT balance FROM users WHERE id = 1) < 0 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;

-- Savepoints
START TRANSACTION;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
    SAVEPOINT order_created;
    
    UPDATE users SET balance = balance - 100 WHERE id = 1;
    IF (SELECT balance FROM users WHERE id = 1) < 0 THEN
        ROLLBACK TO SAVEPOINT order_created;
    END IF;
COMMIT;

Stored Procedures

Stored procedures and functions

MySQL
-- Create procedure
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
    SELECT o.*, p.name as product_name
    FROM orders o
    JOIN products p ON o.product_id = p.id
    WHERE o.user_id = user_id;
END //
DELIMITER ;

-- Call procedure
CALL GetUserOrders(1);

-- Procedure with parameters
DELIMITER //
CREATE PROCEDURE UpdateUserBalance(
    IN user_id INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    START TRANSACTION;
        UPDATE users 
        SET balance = balance + amount 
        WHERE id = user_id;
        
        INSERT INTO transactions (user_id, amount)
        VALUES (user_id, amount);
    COMMIT;
END //
DELIMITER ;

Optimization

Query Optimization

Query optimization techniques

MySQL
-- Explain query
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Force index
SELECT * FROM users FORCE INDEX (idx_email)
WHERE email = 'john@example.com';

-- Optimize table
OPTIMIZE TABLE users;

-- Analyze table
ANALYZE TABLE users;

-- Show process list
SHOW PROCESSLIST;

-- Kill process
KILL 123;

Performance Tuning

Performance tuning and monitoring

MySQL
-- Show variables
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%cache%';

-- Set variables
SET GLOBAL innodb_buffer_pool_size = 1073741824;
SET GLOBAL query_cache_size = 67108864;

-- Show status
SHOW STATUS LIKE '%Innodb_buffer%';
SHOW STATUS LIKE '%Threads%';

-- Show engine status
SHOW ENGINE INNODB STATUS;

-- Show table status
SHOW TABLE STATUS LIKE 'users';

Backup and Recovery

Backup and recovery procedures

MySQL
-- Create backup
mysqldump -u username -p database_name > backup.sql

-- Restore from backup
mysql -u username -p database_name < backup.sql

-- Backup specific tables
mysqldump -u username -p database_name table1 table2 > backup.sql

-- Backup with compression
mysqldump -u username -p database_name | gzip > backup.sql.gz

-- Restore compressed backup
gunzip < backup.sql.gz | mysql -u username -p database_name

-- Backup with conditions
mysqldump -u username -p --where="created_at > '2023-01-01'" database_name table_name > backup.sql

MySQL - Interactive Developer Reference

Hover over code blocks to copy or run in live playground