SQL Quick Reference Cheat Sheet

Database Operations
Table Operations
Data Manipulation
Querying Data
Joins
Aggregate Functions
Subqueries
String Functions
Date Functions
Window Functions
Transactions
Indexes
Views
Stored Procedures
Triggers

Database Operations

Create Database

CREATE DATABASE database_name;

Use Database

USE database_name;

Show Databases

SHOW DATABASES;

Drop Database

DROP DATABASE database_name;

Table Operations

Create Table

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype NOT NULL,
    column3 datatype DEFAULT value,
    column4 datatype UNIQUE,
    FOREIGN KEY (column) REFERENCES other_table(column)
);

Common Data Types

-- Numeric
INT, BIGINT, SMALLINT, TINYINT
DECIMAL(p,s), NUMERIC(p,s)
FLOAT, DOUBLE

-- String
CHAR(n), VARCHAR(n)
TEXT, MEDIUMTEXT, LONGTEXT

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

-- Boolean
BOOLEAN (TINYINT(1))

-- Binary
BLOB, MEDIUMBLOB, LONGBLOB

Modify Table

-- Add column
ALTER TABLE table_name ADD column_name datatype;

-- Drop column
ALTER TABLE table_name DROP COLUMN column_name;

-- Modify column
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;

-- Rename column
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- Rename table
ALTER TABLE old_table_name RENAME TO new_table_name;

Drop Table

DROP TABLE table_name;

Show Tables

SHOW TABLES;

Describe Table

DESCRIBE table_name;
-- or
SHOW COLUMNS FROM table_name;

Data Manipulation

INSERT - Add new rows

-- Insert single row
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- Insert multiple rows
INSERT INTO table_name (column1, column2, column3)
VALUES 
    (value1a, value2a, value3a),
    (value1b, value2b, value3b),
    (value1c, value2c, value3c);

-- Insert from another table
INSERT INTO table_name (column1, column2)
SELECT column1, column2 FROM other_table
WHERE condition;

UPDATE - Modify existing rows

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

-- Update with calculation
UPDATE table_name
SET price = price * 1.1
WHERE category = 'electronics';

DELETE - Remove rows

DELETE FROM table_name
WHERE condition;

-- Delete all rows (but keep table structure)
DELETE FROM table_name;

-- Faster delete (cannot be rolled back)
TRUNCATE TABLE table_name;

Querying Data

Basic SELECT

-- Select all columns
SELECT * FROM table_name;

-- Select specific columns
SELECT column1, column2 FROM table_name;

-- Select with alias
SELECT column1 AS alias1, column2 AS alias2
FROM table_name;

WHERE Clause

SELECT * FROM table_name
WHERE condition;

-- Operators: =, !=, <, >, <=, >=, <>, BETWEEN, LIKE, IN, IS NULL

WHERE Examples

-- Equality
WHERE age = 25

-- Comparison
WHERE salary > 50000

-- Range
WHERE age BETWEEN 18 AND 65

-- Pattern matching
WHERE name LIKE 'J%'        -- Starts with J
WHERE name LIKE '%son'      -- Ends with son
WHERE name LIKE '%a%'       -- Contains a
WHERE name LIKE '_a%'       -- Second char is a

-- Multiple values
WHERE country IN ('USA', 'Canada', 'Mexico')

-- NULL checking
WHERE email IS NULL
WHERE email IS NOT NULL

-- Logical operators
WHERE age > 18 AND country = 'USA'
WHERE city = 'NYC' OR city = 'LA'
WHERE NOT (age < 18)

ORDER BY

-- Ascending (default)
SELECT * FROM table_name
ORDER BY column1;

-- Descending
SELECT * FROM table_name
ORDER BY column1 DESC;

-- Multiple columns
SELECT * FROM table_name
ORDER BY column1 ASC, column2 DESC;

LIMIT

-- Limit number of rows
SELECT * FROM table_name
LIMIT 10;

-- Offset and limit (skip first 10, get next 20)
SELECT * FROM table_name
LIMIT 10, 20;
-- or
SELECT * FROM table_name
LIMIT 20 OFFSET 10;

DISTINCT

-- Get unique values
SELECT DISTINCT column1 FROM table_name;

-- Unique combinations
SELECT DISTINCT column1, column2 FROM table_name;

Joins

INNER JOIN

SELECT t1.column1, t2.column2
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.foreign_id;

LEFT JOIN (LEFT OUTER JOIN)

SELECT t1.column1, t2.column2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.foreign_id;

RIGHT JOIN (RIGHT OUTER JOIN)

SELECT t1.column1, t2.column2
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.id = t2.foreign_id;

FULL OUTER JOIN (MySQL workaround)

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

CROSS JOIN

SELECT * FROM table1
CROSS JOIN table2;

SELF JOIN

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id;

Aggregate Functions

Basic Aggregates

-- Count rows
SELECT COUNT(*) FROM table_name;
SELECT COUNT(column) FROM table_name;
SELECT COUNT(DISTINCT column) FROM table_name;

-- Sum values
SELECT SUM(column) FROM table_name;

-- Average
SELECT AVG(column) FROM table_name;

-- Minimum and Maximum
SELECT MIN(column) FROM table_name;
SELECT MAX(column) FROM table_name;

GROUP BY

SELECT column1, COUNT(*), AVG(column2)
FROM table_name
GROUP BY column1;

-- Multiple grouping columns
SELECT column1, column2, SUM(column3)
FROM table_name
GROUP BY column1, column2;

HAVING

-- Filter groups (use after GROUP BY)
SELECT column1, COUNT(*) as count
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 10;

-- HAVING with multiple conditions
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100 AND COUNT(*) >= 5;

Subqueries

Subquery in WHERE

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Subquery with IN

SELECT * FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'NY'
);

Subquery in FROM (Derived Table)

SELECT dept_name, avg_salary
FROM (
    SELECT department_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
JOIN departments ON dept_avg.department_id = departments.id;

Correlated Subquery

SELECT e1.name, e1.salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

EXISTS

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

String Functions

-- Concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Length
SELECT LENGTH(column) FROM table_name;

-- Substring
SELECT SUBSTRING(column, start, length) FROM table_name;
SELECT LEFT(column, n) FROM table_name;
SELECT RIGHT(column, n) FROM table_name;

-- Case conversion
SELECT UPPER(column) FROM table_name;
SELECT LOWER(column) FROM table_name;

-- Trim whitespace
SELECT TRIM(column) FROM table_name;
SELECT LTRIM(column) FROM table_name;
SELECT RTRIM(column) FROM table_name;

-- Replace
SELECT REPLACE(column, 'old', 'new') FROM table_name;

-- Position
SELECT POSITION('substring' IN column) FROM table_name;
SELECT LOCATE('substring', column) FROM table_name;

Date Functions

-- Current date/time
SELECT NOW();           -- Current datetime
SELECT CURDATE();       -- Current date
SELECT CURTIME();       -- Current time

-- Extract parts
SELECT YEAR(date_column) FROM table_name;
SELECT MONTH(date_column) FROM table_name;
SELECT DAY(date_column) FROM table_name;
SELECT HOUR(datetime_column) FROM table_name;

-- Date arithmetic
SELECT DATE_ADD(date_column, INTERVAL 7 DAY) FROM table_name;
SELECT DATE_SUB(date_column, INTERVAL 1 MONTH) FROM table_name;

-- Date difference
SELECT DATEDIFF(date1, date2) FROM table_name;
SELECT TIMESTAMPDIFF(MINUTE, datetime1, datetime2) FROM table_name;

-- Format date
SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM table_name;
SELECT DATE_FORMAT(datetime_column, '%W, %M %d, %Y %H:%i:%s') FROM table_name;

Window Functions

ROW_NUMBER

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;

RANK and DENSE_RANK

SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM students;

PARTITION BY

SELECT 
    department,
    name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

Aggregate Window Functions

SELECT 
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

LEAD and LAG

SELECT 
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day_revenue,
    LEAD(revenue, 1) OVER (ORDER BY date) as next_day_revenue
FROM daily_sales;

Transactions

-- Start transaction
START TRANSACTION;
-- or
BEGIN;

-- Execute queries
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit changes
COMMIT;

-- Or rollback if error
ROLLBACK;

Transaction with Savepoint

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT sp1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SAVEPOINT sp2;

-- Rollback to savepoint
ROLLBACK TO sp1;

COMMIT;

Indexes

Create Index

-- Single column index
CREATE INDEX idx_name ON table_name (column);

-- Multiple column index
CREATE INDEX idx_name ON table_name (column1, column2);

-- Unique index
CREATE UNIQUE INDEX idx_name ON table_name (column);

Drop Index

DROP INDEX idx_name ON table_name;

Show Indexes

SHOW INDEX FROM table_name;

Views

Create View

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Use View

SELECT * FROM view_name;

Update View

CREATE OR REPLACE VIEW view_name AS
SELECT new_columns
FROM table_name
WHERE new_condition;

Drop View

DROP VIEW view_name;

Stored Procedures

Create Procedure

DELIMITER //
CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(100))
BEGIN
    -- SQL statements
    SELECT column INTO param2
    FROM table_name
    WHERE id = param1;
END //
DELIMITER ;

Call Procedure

CALL procedure_name(value1, @result);
SELECT @result;

Drop Procedure

DROP PROCEDURE procedure_name;

Triggers

Create Trigger

DELIMITER //
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic
    SET NEW.created_at = NOW();
END //
DELIMITER ;

Trigger Types

BEFORE INSERT, AFTER INSERT
BEFORE UPDATE, AFTER UPDATE
BEFORE DELETE, AFTER DELETE

Drop Trigger

DROP TRIGGER trigger_name;

CASE Expression

Simple CASE

SELECT 
    name,
    CASE grade
        WHEN 'A' THEN 'Excellent'
        WHEN 'B' THEN 'Good'
        WHEN 'C' THEN 'Average'
        ELSE 'Needs Improvement'
    END AS performance
FROM students;

Searched CASE

SELECT 
    name,
    salary,
    CASE
        WHEN salary > 100000 THEN 'High'
        WHEN salary > 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_range
FROM employees;

Set Operations

UNION

-- Combine results, remove duplicates
SELECT column FROM table1
UNION
SELECT column FROM table2;

-- Keep duplicates
SELECT column FROM table1
UNION ALL
SELECT column FROM table2;

INTERSECT (MySQL 8.0+)

SELECT column FROM table1
INTERSECT
SELECT column FROM table2;

EXCEPT (MySQL workaround)

SELECT column FROM table1
WHERE column NOT IN (SELECT column FROM table2);

Common Table Expressions (CTEs)

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name
WHERE another_condition;

Recursive CTE

WITH RECURSIVE cte_name AS (
    -- Base case
    SELECT id, name, parent_id, 1 as level
    FROM table_name
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive case
    SELECT t.id, t.name, t.parent_id, c.level + 1
    FROM table_name t
    JOIN cte_name c ON t.parent_id = c.id
)
SELECT * FROM cte_name;

Best Practices

Use appropriate data types - Choose the smallest type that fits your data
Index strategically - Index columns used in WHERE, JOIN, ORDER BY
Avoid SELECT * - Specify only needed columns
Use LIMIT - When you don't need all rows
Use transactions - For multiple related updates
Normalize your database - Reduce redundancy
Use meaningful names - For tables, columns, and constraints
Comment complex queries - Explain non-obvious logic
Test with small datasets first - Before running on production data
Back up before major changes - Always have a recovery plan

Performance Tips

Use indexes on columns in WHERE, JOIN, ORDER BY clauses
Avoid functions in WHERE - They prevent index usage
Use EXPLAIN to analyze query performance
Limit returned rows with WHERE and LIMIT
Use EXISTS instead of IN for large subqueries
Avoid wildcard at start of LIKE pattern (%abc prevents index use)
Use appropriate JOIN types - Don't use LEFT JOIN when INNER JOIN suffices
Batch operations instead of row-by-row updates
Optimize subqueries - Consider using JOINs or EXISTS
Keep statistics updated - Run ANALYZE TABLE regularly

For more details, visit the MySQL Documentation

SQL Quick Reference Cheat Sheet

Table of Contents

Database Operations

Create Database

Use Database

Show Databases

Drop Database

Table Operations

Create Table

Common Data Types

Modify Table

Drop Table

Show Tables

Describe Table

Data Manipulation

INSERT - Add new rows

UPDATE - Modify existing rows

DELETE - Remove rows

Querying Data

Basic SELECT

WHERE Clause

WHERE Examples

ORDER BY

LIMIT

DISTINCT

Joins

INNER JOIN

LEFT JOIN (LEFT OUTER JOIN)

RIGHT JOIN (RIGHT OUTER JOIN)

FULL OUTER JOIN (MySQL workaround)

CROSS JOIN

SELF JOIN

Aggregate Functions

Basic Aggregates

GROUP BY

HAVING

Subqueries

Subquery in WHERE

Subquery with IN

Subquery in FROM (Derived Table)

Correlated Subquery

EXISTS

String Functions

Date Functions

Window Functions

ROW_NUMBER

RANK and DENSE_RANK

PARTITION BY

Aggregate Window Functions

LEAD and LAG

Transactions

Transaction with Savepoint

Indexes

Create Index

Drop Index

Show Indexes

Views

Create View

Use View

Update View

Drop View

Stored Procedures

Create Procedure

Call Procedure

Drop Procedure

Triggers

Create Trigger

Trigger Types

Drop Trigger

CASE Expression

Simple CASE

Searched CASE

Set Operations

UNION

INTERSECT (MySQL 8.0+)

EXCEPT (MySQL workaround)

Common Table Expressions (CTEs)

Recursive CTE

Best Practices

Performance Tips