SQL cheatsheet

Comprehensive SQL command reference for querying, managing, and formatting database results

Development

Querying Data

Select all columns from a table

SELECT * FROM table_name;

Select specific columns

SELECT column1, column2 FROM table_name;

Select with a condition

SELECT * FROM table_name WHERE column = 'value';

Select with multiple conditions (AND)

SELECT * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2';

Select with multiple conditions (OR)

SELECT * FROM table_name WHERE column1 = 'value1' OR column2 = 'value2';

Select with LIKE pattern matching

SELECT * FROM table_name WHERE column LIKE '%pattern%';

Select with IN clause

SELECT * FROM table_name WHERE column IN ('value1', 'value2', 'value3');

Select with BETWEEN range

SELECT * FROM table_name WHERE column BETWEEN 10 AND 20;

Formatting Results for Sharing

Format record as key-value pairs (PostgreSQL)

Outputs a single text field with key-value pairs, one per line, perfect for copying and pasting into emails

SELECT 
  'name: ' || name || E'\n' ||
  'email: ' || email || E'\n' ||
  'city: ' || city || E'\n' ||
  'dateOfBirth: ' || dateOfBirth
  AS record_details
FROM users
WHERE name = 'Eric Byrd';

Format record as key-value pairs (MySQL)

MySQL version using CONCAT for combining fields with labels

SELECT 
  CONCAT(
    'name: ', name, '\n',
    'email: ', email, '\n',
    'city: ', city, '\n',
    'dateOfBirth: ', dateOfBirth
  ) AS record_details
FROM users
WHERE name = 'Eric Byrd';

Format all columns as key-value using JSON (PostgreSQL)

Automatically formats all columns as readable JSON with proper indentation

SELECT jsonb_pretty(to_jsonb(t)) AS formatted_record
FROM users t
WHERE name = 'Eric Byrd';

Format as vertical display (MySQL)

Use \G instead of ; to display results vertically in MySQL CLI

SELECT * FROM users WHERE name = 'Eric Byrd'\G

Sorting and Limiting

Order results ascending

SELECT * FROM table_name ORDER BY column ASC;

Order results descending

SELECT * FROM table_name ORDER BY column DESC;

Limit number of results

SELECT * FROM table_name LIMIT 10;

Limit with offset

SELECT * FROM table_name LIMIT 10 OFFSET 20;

Get top N results

SELECT * FROM table_name ORDER BY column DESC LIMIT 5;

Inserting Data

Insert a single row

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

Insert multiple rows

INSERT INTO table_name (column1, column2) VALUES 
  ('value1', 'value2'),
  ('value3', 'value4'),
  ('value5', 'value6');

Insert from another table

INSERT INTO table_name (column1, column2)
SELECT column1, column2 FROM other_table;

Updating Data

Update all rows

UPDATE table_name SET column = 'new_value';

Update with a condition

UPDATE table_name SET column = 'new_value' WHERE id = 1;

Update multiple columns

UPDATE table_name SET column1 = 'value1', column2 = 'value2' WHERE id = 1;

Increment a numeric value

UPDATE table_name SET count = count + 1 WHERE id = 1;

Deleting Data

Delete all rows (use with caution!)

DELETE FROM table_name;

Delete with a condition

DELETE FROM table_name WHERE id = 1;

Delete with multiple conditions

DELETE FROM table_name WHERE status = 'inactive' AND created_at < '2023-01-01';

Joins

Inner join

SELECT t1.*, t2.column
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id;

Left join

SELECT t1.*, t2.column
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.table1_id;

Right join

SELECT t1.*, t2.column
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.id = t2.table1_id;

Full outer join

SELECT t1.*, t2.column
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.id = t2.table1_id;

Self join

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

Aggregation

Count rows

SELECT COUNT(*) FROM table_name;

Count distinct values

SELECT COUNT(DISTINCT column) FROM table_name;

Sum values

SELECT SUM(column) FROM table_name;

Average value

SELECT AVG(column) FROM table_name;

Min and max values

SELECT MIN(column), MAX(column) FROM table_name;

Group by with count

SELECT column, COUNT(*) FROM table_name GROUP BY column;

Group by with having clause

SELECT column, COUNT(*) FROM table_name GROUP BY column HAVING COUNT(*) > 10;

Table Management

Create a table

CREATE TABLE table_name (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Drop a table

DROP TABLE table_name;

Drop table if exists

DROP TABLE IF EXISTS table_name;

Add a column

ALTER TABLE table_name ADD COLUMN new_column VARCHAR(50);

Drop a column

ALTER TABLE table_name DROP COLUMN column_name;

Rename a column

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Modify column type

ALTER TABLE table_name ALTER COLUMN column_name TYPE VARCHAR(200);

Indexes

Create an index

CREATE INDEX idx_column ON table_name(column);

Create a unique index

CREATE UNIQUE INDEX idx_column ON table_name(column);

Create a composite index

CREATE INDEX idx_multiple ON table_name(column1, column2);

Drop an index

DROP INDEX idx_column;

List all indexes

SELECT * FROM pg_indexes WHERE tablename = 'table_name';

Transactions

Start a transaction

BEGIN;

Commit a transaction

COMMIT;

Rollback a transaction

ROLLBACK;

Transaction example

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

String Functions

Concatenate strings

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

Convert to uppercase

SELECT UPPER(column) FROM table_name;

Convert to lowercase

SELECT LOWER(column) FROM table_name;

Get string length

SELECT LENGTH(column) FROM table_name;

Trim whitespace

SELECT TRIM(column) FROM table_name;

Substring

SELECT SUBSTRING(column, 1, 10) FROM table_name;

Replace text

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

Date and Time

Get current date

SELECT CURRENT_DATE;

Get current timestamp

SELECT CURRENT_TIMESTAMP;

Extract year from date

SELECT EXTRACT(YEAR FROM date_column) FROM table_name;

Extract month from date

SELECT EXTRACT(MONTH FROM date_column) FROM table_name;

Date arithmetic (add days)

SELECT date_column + INTERVAL '7 days' FROM table_name;

Date difference

SELECT AGE(date_column1, date_column2) FROM table_name;

Format date

SELECT TO_CHAR(date_column, 'YYYY-MM-DD') FROM table_name;

Subqueries

Subquery in WHERE clause

SELECT * FROM table_name WHERE column > (SELECT AVG(column) FROM table_name);

Subquery with IN

SELECT * FROM table1 WHERE id IN (SELECT table1_id FROM table2 WHERE condition);

Subquery in SELECT

SELECT column, (SELECT COUNT(*) FROM table2 WHERE table2.id = table1.id) AS count FROM table1;

Correlated subquery

SELECT * FROM table1 t1 WHERE column > (SELECT AVG(column) FROM table1 t2 WHERE t2.category = t1.category);

Common Table Expressions (CTEs)

Basic CTE

WITH cte_name AS (
  SELECT column FROM table_name WHERE condition
)
SELECT * FROM cte_name;

Multiple CTEs

WITH cte1 AS (
  SELECT * FROM table1
),
cte2 AS (
  SELECT * FROM table2
)
SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

Recursive CTE

WITH RECURSIVE cte AS (
  SELECT id, parent_id, name, 1 AS level
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, cte.level + 1
  FROM categories c
  JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

Window Functions

Row number

SELECT column, ROW_NUMBER() OVER (ORDER BY column) AS row_num FROM table_name;

Rank

SELECT column, RANK() OVER (ORDER BY column DESC) AS rank FROM table_name;

Partition by

SELECT column, COUNT(*) OVER (PARTITION BY category) AS category_count FROM table_name;

Running total

SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM transactions;

Lead and lag

SELECT date, value, LAG(value) OVER (ORDER BY date) AS prev_value, LEAD(value) OVER (ORDER BY date) AS next_value FROM table_name;

Database Information

List all tables (PostgreSQL)

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

List all tables (MySQL)

SHOW TABLES;

Describe table structure (PostgreSQL)

SELECT * FROM information_schema.columns WHERE table_name = 'table_name';

Describe table structure (MySQL)

DESCRIBE table_name;

Get database size (PostgreSQL)

SELECT pg_size_pretty(pg_database_size(current_database()));

Get table size (PostgreSQL)

SELECT pg_size_pretty(pg_total_relation_size('table_name'));

Enum Types

Drop all enum types (PostgreSQL)

Generates and executes DROP commands for every enum type in your PostgreSQL database. Run in TablePlus SQL query tab to remove all custom enum types.

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (
        SELECT n.nspname AS schema_name, t.typname AS type_name
        FROM pg_type t
        JOIN pg_namespace n ON n.oid = t.typnamespace
        WHERE t.typtype = 'e'
          AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    ) LOOP
        EXECUTE format('DROP TYPE IF EXISTS %I.%I CASCADE;', r.schema_name, r.type_name);
    END LOOP;
END $$;

Performance

Explain query plan

EXPLAIN SELECT * FROM table_name WHERE column = value;

Explain analyze (with execution)

EXPLAIN ANALYZE SELECT * FROM table_name WHERE column = value;

Vacuum table (PostgreSQL)

VACUUM table_name;

Analyze table statistics

ANALYZE table_name;

Note: SQL (Structured Query Language) is used to manage and query relational databases. This cheatsheet covers common SQL commands for PostgreSQL and MySQL. Commands may vary slightly between database systems. The Formatting Results for Sharing section shows how to format query results as key-value pairs, perfect for sharing database records with clients via email.

Hello World

Hello You

Quick Tools