$devvkit resources --cheatsheet sql-cheatsheet

SQL Cheatsheet

[databases]

Essential SQL queries for PostgreSQL and MySQL — from basic SELECTs to window functions.

Structured Query Language (SQL) is the foundation of relational databases. This cheatsheet covers querying, filtering, joining, aggregating, and modifying data across PostgreSQL and MySQL.

Each command is written in standard SQL and should work on both engines unless noted otherwise.

Querying Data

$SELECT * FROM table

Select all columns from a table.Example: SELECT * FROM users

$SELECT col1, col2 FROM table

Select only specific columns.Example: SELECT id, email FROM users

$SELECT DISTINCT col FROM table

Return only unique values from a column.Example: SELECT DISTINCT status FROM orders

$SELECT COUNT(*) FROM table

Count the total number of rows.Example: SELECT COUNT(*) FROM users

Filtering & Sorting

$WHERE condition

Filter rows based on a condition.Example: WHERE age > 18 AND status = 'active'

$WHERE col BETWEEN a AND b

Filter rows where a column falls within a range.Example: WHERE price BETWEEN 10 AND 50

$WHERE col LIKE 'pattern%'

Pattern match using % (any chars) and _ (single char) wildcards.Example: WHERE name LIKE 'John%'

$WHERE col IN (val1, val2)

Filter rows matching any value in a list.Example: WHERE status IN ('active', 'pending')

$WHERE col IS NULL

Find rows where a column has no value.Example: WHERE deleted_at IS NULL

$ORDER BY col ASC/DESC

Sort results by one or more columns.Example: ORDER BY created_at DESC, name ASC

$LIMIT n OFFSET m

Paginate results — return n rows starting after m rows.Example: LIMIT 10 OFFSET 20

Joins

$INNER JOIN table ON condition

Return rows that have matching values in both tables.Example: INNER JOIN orders ON users.id = orders.user_id

$LEFT JOIN table ON condition

Return all rows from left table and matched rows from right. Unmatched right columns are NULL.Example: LEFT JOIN orders ON users.id = orders.user_id

$RIGHT JOIN table ON condition

Return all rows from right table and matched rows from left.Example: RIGHT JOIN orders ON users.id = orders.user_id

$FULL OUTER JOIN table ON condition

Return all rows from both tables, with NULLs where no match exists.Example: FULL OUTER JOIN orders ON users.id = orders.user_id

Aggregation

$GROUP BY col

Group rows that have the same value for aggregation.Example: GROUP BY category

$HAVING condition

Filter groups after aggregation (like WHERE for GROUP BY).Example: HAVING COUNT(*) > 5

$SELECT AVG(col), SUM(col), MIN(col), MAX(col)

Aggregate functions for statistics.Example: SELECT AVG(price), SUM(quantity) FROM order_items

$ROW_NUMBER() OVER (ORDER BY col)

Assign a sequential integer to each row within a partition.Example: SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM results

Modifying Data

$INSERT INTO table (cols) VALUES (vals)

Insert a new row with specified column values.Example: INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')

$INSERT INTO table (cols) VALUES (vals) RETURNING *

Insert a row and return the full inserted row (PostgreSQL).Example: INSERT INTO users (name) VALUES ('Bob') RETURNING *

$UPDATE table SET col = val WHERE condition

Update existing rows matching the condition.Example: UPDATE users SET name = 'Bob' WHERE id = 1

$DELETE FROM table WHERE condition

Delete rows matching the condition.Example: DELETE FROM users WHERE status = 'inactive'

Schema & Indexes

$CREATE TABLE name (cols)

Create a new table with specified columns and types.Example: CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)

$ALTER TABLE table ADD COLUMN col type

Add a new column to an existing table.Example: ALTER TABLE users ADD COLUMN avatar_url TEXT

$ALTER TABLE table DROP COLUMN col

Remove a column from an existing table.Example: ALTER TABLE users DROP COLUMN avatar_url

$CREATE INDEX idx_name ON table (col)

Create an index to speed up queries on a column.Example: CREATE INDEX idx_email ON users (email)

$CREATE UNIQUE INDEX idx_name ON table (col)

Create a unique index that prevents duplicate values.Example: CREATE UNIQUE INDEX idx_email ON users (email)

$DROP TABLE table

Permanently delete a table and all its data.Example: DROP TABLE temp_logs

SQL Cheatsheet

Essential SQL queries for PostgreSQL and MySQL — from basic SELECTs to window functions.

Structured Query Language (SQL) is the foundation of relational databases. This cheatsheet covers querying, filtering, joining, aggregating, and modifying data across PostgreSQL and MySQL.

Each command is written in standard SQL and should work on both engines unless noted otherwise.

Querying Data

SELECT * FROM table — Select all columns from a table.

Example: SELECT * FROM users

SELECT col1, col2 FROM table — Select only specific columns.

Example: SELECT id, email FROM users

SELECT DISTINCT col FROM table — Return only unique values from a column.

Example: SELECT DISTINCT status FROM orders

SELECT COUNT(*) FROM table — Count the total number of rows.

Example: SELECT COUNT(*) FROM users

Filtering & Sorting

WHERE condition — Filter rows based on a condition.

Example: WHERE age > 18 AND status = 'active'

WHERE col BETWEEN a AND b — Filter rows where a column falls within a range.

Example: WHERE price BETWEEN 10 AND 50

WHERE col LIKE 'pattern%' — Pattern match using % (any chars) and _ (single char) wildcards.

Example: WHERE name LIKE 'John%'

WHERE col IN (val1, val2) — Filter rows matching any value in a list.

Example: WHERE status IN ('active', 'pending')

WHERE col IS NULL — Find rows where a column has no value.

Example: WHERE deleted_at IS NULL

ORDER BY col ASC/DESC — Sort results by one or more columns.

Example: ORDER BY created_at DESC, name ASC

LIMIT n OFFSET m — Paginate results — return n rows starting after m rows.

Example: LIMIT 10 OFFSET 20

Joins

INNER JOIN table ON condition — Return rows that have matching values in both tables.

Example: INNER JOIN orders ON users.id = orders.user_id

LEFT JOIN table ON condition — Return all rows from left table and matched rows from right. Unmatched right columns are NULL.

Example: LEFT JOIN orders ON users.id = orders.user_id

RIGHT JOIN table ON condition — Return all rows from right table and matched rows from left.

Example: RIGHT JOIN orders ON users.id = orders.user_id

FULL OUTER JOIN table ON condition — Return all rows from both tables, with NULLs where no match exists.

Example: FULL OUTER JOIN orders ON users.id = orders.user_id

Aggregation

GROUP BY col — Group rows that have the same value for aggregation.

Example: GROUP BY category

HAVING condition — Filter groups after aggregation (like WHERE for GROUP BY).

Example: HAVING COUNT(*) > 5

SELECT AVG(col), SUM(col), MIN(col), MAX(col) — Aggregate functions for statistics.

Example: SELECT AVG(price), SUM(quantity) FROM order_items

ROW_NUMBER() OVER (ORDER BY col) — Assign a sequential integer to each row within a partition.

Example: SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM results

Modifying Data

INSERT INTO table (cols) VALUES (vals) — Insert a new row with specified column values.

Example: INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')

INSERT INTO table (cols) VALUES (vals) RETURNING * — Insert a row and return the full inserted row (PostgreSQL).

Example: INSERT INTO users (name) VALUES ('Bob') RETURNING *

UPDATE table SET col = val WHERE condition — Update existing rows matching the condition.

Example: UPDATE users SET name = 'Bob' WHERE id = 1

DELETE FROM table WHERE condition — Delete rows matching the condition.

Example: DELETE FROM users WHERE status = 'inactive'

Schema & Indexes

CREATE TABLE name (cols) — Create a new table with specified columns and types.

Example: CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)

ALTER TABLE table ADD COLUMN col type — Add a new column to an existing table.

Example: ALTER TABLE users ADD COLUMN avatar_url TEXT

ALTER TABLE table DROP COLUMN col — Remove a column from an existing table.

Example: ALTER TABLE users DROP COLUMN avatar_url

CREATE INDEX idx_name ON table (col) — Create an index to speed up queries on a column.

Example: CREATE INDEX idx_email ON users (email)

CREATE UNIQUE INDEX idx_name ON table (col) — Create a unique index that prevents duplicate values.

Example: CREATE UNIQUE INDEX idx_email ON users (email)

DROP TABLE table — Permanently delete a table and all its data.

Example: DROP TABLE temp_logs