📋 MySQL Cheat Sheet

Database Commands

-- Show all databases
SHOW DATABASES;

-- Create a database
CREATE DATABASE bookstore;
CREATE DATABASE IF NOT EXISTS bookstore;

-- Use a database
USE bookstore;

-- Show which database is selected
SELECT DATABASE();

-- Drop a database
DROP DATABASE bookstore;
DROP DATABASE IF EXISTS bookstore;
                

Table Commands

-- Show all tables in the current database
SHOW TABLES;

-- Describe a table's structure
DESCRIBE books;
DESC books;
SHOW COLUMNS FROM books;

-- Create a table
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) DEFAULT 0.00,
    publication_year INT,
    author_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- Alter a table
ALTER TABLE books ADD COLUMN isbn VARCHAR(13);
ALTER TABLE books DROP COLUMN isbn;
ALTER TABLE books MODIFY COLUMN title VARCHAR(300) NOT NULL;
ALTER TABLE books RENAME COLUMN title TO book_title;
ALTER TABLE books RENAME TO publications;

-- Drop a table
DROP TABLE books;
DROP TABLE IF EXISTS books;

-- Truncate (delete all rows, keep structure)
TRUNCATE TABLE books;
                

Data Types

Constraints

Foreign Key Actions

FOREIGN KEY (author_id) REFERENCES authors(id)
    ON DELETE CASCADE        -- Delete child rows when parent is deleted
    ON DELETE SET NULL       -- Set to NULL when parent is deleted
    ON DELETE RESTRICT       -- Prevent deletion if children exist (default)
    ON UPDATE CASCADE        -- Update child values when parent key changes
                

CRUD Operations

-- INSERT (Create)
INSERT INTO books (title, price, author_id) VALUES ('1984', 11.99, 1);
INSERT INTO books (title, price) VALUES
    ('Dune', 14.99),
    ('Neuromancer', 12.99);

-- SELECT (Read)
SELECT * FROM books;
SELECT title, price FROM books;
SELECT title AS book_title, price AS cost FROM books;   -- Aliases
SELECT DISTINCT author_id FROM books;                   -- Unique values

-- UPDATE
UPDATE books SET price = 13.99 WHERE id = 1;
UPDATE books SET price = price * 1.10 WHERE price < 10; -- Increase by 10%

-- DELETE
DELETE FROM books WHERE id = 5;
DELETE FROM books WHERE price IS NULL;
                

Filtering & Sorting

-- WHERE clause
SELECT * FROM books WHERE price > 10;
SELECT * FROM books WHERE price BETWEEN 10 AND 20;
SELECT * FROM books WHERE author_id IN (1, 3, 5);
SELECT * FROM books WHERE title LIKE '%gatsby%';          -- Contains
SELECT * FROM books WHERE title LIKE 'The%';              -- Starts with
SELECT * FROM books WHERE publication_year IS NULL;
SELECT * FROM books WHERE price > 10 AND author_id = 1;
SELECT * FROM books WHERE price > 20 OR author_id = 2;
SELECT * FROM books WHERE NOT author_id = 3;

-- ORDER BY
SELECT * FROM books ORDER BY price ASC;                   -- Low to high
SELECT * FROM books ORDER BY price DESC;                  -- High to low
SELECT * FROM books ORDER BY author_id ASC, price DESC;   -- Multi-column

-- LIMIT & OFFSET
SELECT * FROM books LIMIT 10;                             -- First 10 rows
SELECT * FROM books LIMIT 10 OFFSET 20;                   -- Rows 21–30
                

Aggregate Functions

-- Basic aggregates
SELECT COUNT(*) FROM books;
SELECT COUNT(DISTINCT author_id) FROM books;
SELECT SUM(price) FROM books;
SELECT AVG(price) FROM books;
SELECT MIN(price), MAX(price) FROM books;

-- GROUP BY
SELECT author_id, COUNT(*) AS total, AVG(price) AS avg_price
FROM books
GROUP BY author_id;

-- HAVING (filter groups — like WHERE but for aggregates)
SELECT author_id, COUNT(*) AS total
FROM books
GROUP BY author_id
HAVING total >= 3;
                

Joins

-- INNER JOIN (rows that match in both tables)
SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.author_id = a.id;

-- LEFT JOIN (all rows from left table + matches from right)
SELECT b.title, a.name
FROM books b
LEFT JOIN authors a ON b.author_id = a.id;

-- RIGHT JOIN (all rows from right table + matches from left)
SELECT b.title, a.name
FROM books b
RIGHT JOIN authors a ON b.author_id = a.id;

-- CROSS JOIN (every combination — Cartesian product)
SELECT b.title, c.name
FROM books b
CROSS JOIN categories c;

-- Self-join (table joined to itself)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Multi-table join
SELECT b.title, a.name AS author, p.name AS publisher
FROM books b
JOIN authors a ON b.author_id = a.id
JOIN publishers p ON b.publisher_id = p.id;

-- Many-to-many (via junction table)
SELECT b.title, g.name AS genre
FROM books b
JOIN book_genres bg ON b.id = bg.book_id
JOIN genres g ON bg.genre_id = g.id;
                

Subqueries

-- Scalar subquery (returns one value)
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

-- IN with subquery (returns a list)
SELECT name FROM authors
WHERE id IN (SELECT DISTINCT author_id FROM books WHERE price > 20);

-- EXISTS (true if subquery returns any rows)
SELECT name FROM authors a
WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id);

-- Subquery in FROM (derived table)
SELECT avg_by_author.author_id, avg_by_author.avg_price
FROM (
    SELECT author_id, AVG(price) AS avg_price
    FROM books GROUP BY author_id
) AS avg_by_author
WHERE avg_by_author.avg_price > 15;
                

Indexes & Views

-- Create an index
CREATE INDEX idx_author_id ON books(author_id);
CREATE UNIQUE INDEX idx_email ON customers(email);
CREATE INDEX idx_name_year ON books(author_id, publication_year);  -- Composite

-- Show indexes
SHOW INDEX FROM books;

-- Drop an index
DROP INDEX idx_author_id ON books;

-- Analyze a query's execution plan
EXPLAIN SELECT * FROM books WHERE author_id = 3;

-- Create a view
CREATE VIEW cheap_books AS
SELECT title, price FROM books WHERE price < 10;

-- Use the view like a table
SELECT * FROM cheap_books;

-- Replace a view
CREATE OR REPLACE VIEW cheap_books AS
SELECT title, price, author_id FROM books WHERE price < 15;

-- Drop a view
DROP VIEW IF EXISTS cheap_books;
                

Stored Procedures & Functions

-- Stored procedure
DELIMITER //
CREATE PROCEDURE get_books_by_author(IN p_author_id INT)
BEGIN
    SELECT title, price FROM books WHERE author_id = p_author_id;
END //
DELIMITER ;

-- Call a procedure
CALL get_books_by_author(1);

-- Procedure with OUT parameter
DELIMITER //
CREATE PROCEDURE count_books(IN p_author_id INT, OUT p_count INT)
BEGIN
    SELECT COUNT(*) INTO p_count FROM books WHERE author_id = p_author_id;
END //
DELIMITER ;

CALL count_books(1, @total);
SELECT @total;

-- Stored function (returns a value)
DELIMITER //
CREATE FUNCTION discount_price(original DECIMAL(10,2), pct INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN original * (1 - pct / 100);
END //
DELIMITER ;

-- Use the function
SELECT title, price, discount_price(price, 20) AS sale_price FROM books;

-- Drop
DROP PROCEDURE IF EXISTS get_books_by_author;
DROP FUNCTION IF EXISTS discount_price;
                

Transactions

-- Basic transaction
START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback on error
START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    -- Something goes wrong...
ROLLBACK;

-- Savepoints
START TRANSACTION;
    INSERT INTO orders (...) VALUES (...);
    SAVEPOINT after_order;
    INSERT INTO order_items (...) VALUES (...);
    -- Problem with items? Roll back to savepoint
    ROLLBACK TO after_order;
    -- Fix and retry...
COMMIT;
                

User Management

-- Create a user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'SecurePass123!';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE ON bookstore.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON bookstore.* TO 'admin'@'localhost';

-- View privileges
SHOW GRANTS FOR 'app_user'@'localhost';

-- Revoke privileges
REVOKE DELETE ON bookstore.* FROM 'app_user'@'localhost';

-- Change password
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewPass!';

-- Lock / unlock
ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;
ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;

-- Delete a user
DROP USER IF EXISTS 'app_user'@'localhost';

-- Roles (MySQL 8+)
CREATE ROLE 'reader';
GRANT SELECT ON bookstore.* TO 'reader';
GRANT 'reader' TO 'alice'@'localhost';
SET DEFAULT ROLE ALL TO 'alice'@'localhost';

-- List all users
SELECT User, Host FROM mysql.user;
                

Import & Export

# --- TERMINAL COMMANDS (not inside mysql>) ---

# Dump a database
mysqldump -u root -p bookstore > backup.sql

# Dump specific tables
mysqldump -u root -p bookstore books authors > partial.sql

# Dump all databases
mysqldump -u root -p --all-databases > full_backup.sql

# Schema only (no data)
mysqldump -u root -p --no-data bookstore > schema.sql

# Production-safe dump (InnoDB, no locking)
mysqldump -u root -p --single-transaction --routines --triggers bookstore > backup.sql

# Restore a dump
mysql -u root -p bookstore < backup.sql

# CLI CSV import
mysqlimport -u root -p --local --fields-terminated-by=',' --ignore-lines=1 bookstore books.csv
                

-- --- INSIDE MYSQL ---

-- Export to CSV
SELECT id, title, price
INTO OUTFILE '/tmp/books.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM books;

-- Import from CSV
LOAD DATA INFILE '/tmp/books.csv'
INTO TABLE books
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Check file path restrictions
SHOW VARIABLES LIKE 'secure_file_priv';
                

Operators Quick Reference

String Functions

Date & Time Functions

Numeric Functions