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

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