Developer Reference

SQL Common Commands

Standard SQL syntax for querying, manipulating, and defining data.

Structured Query Language (SQL) is the standard language for relational database management systems. This cheat sheet covers key commands for CRUD operations: SELECT, INSERT, UPDATE, DELETE.

SQL Performance & Safety

  • Use Indexes: Index columns used in `WHERE`, `JOIN`, and `ORDER BY` clauses.
  • Use Parametrized Queries: Prevent SQL Injection by never concatenating user input.
  • Avoid SELECT *: Only fetch columns you need to reduce network load.
  • Don't perform math on columns: `WHERE year(date) = 2023` prevents index usage. Use `WHERE date >= '2023-01-01'`.

Data Query

Select all columns from a table
SELECT * FROM table

Data Manipulation

Insert new row
INSERT INTO table (col) VALUES (val)
Update existing rows
UPDATE table SET col = val
Delete rows (Always use WHERE!)
DELETE FROM table WHERE id = 1

Schema

Create a new table
CREATE TABLE name (cols)
Modify table structure
ALTER TABLE name ADD col type
Delete table permanently
DROP TABLE name

Joins

Combine rows from two calls
JOIN table ON t1.id = t2.id
Returns records with matching values in both tables
INNER JOIN
Returns all records from left table, and matched from right
LEFT JOIN

Aggregation

Group rows with same values
GROUP BY col
Count number of rows
COUNT(*)
Sum values in a column
SUM(col)
Concatenate values into a string
GROUP_CONCAT(col)

Formatting

Sort results
ORDER BY col DESC

Query

Pagination (MySQL/Postgres)
LIMIT 10 OFFSET 5

Window Functions

Window function: Assign row numbers
ROW_NUMBER() OVER (PARTITION BY..)
Window function: Rank with gaps
RANK()
Window function: Rank without gaps
DENSE_RANK()

Date Functions

Current date and time
NOW()
Difference between dates
DATEDIFF(d1, d2)

String Functions

Concatenate strings
CONCAT(s1, s2)
Extract substring
SUBSTRING(str, pos, len)

Constraints

Uniquely identifies each record
PRIMARY KEY
Links to another table
FOREIGN KEY

Transactions

Start a new transaction
BEGIN TRANSACTION
Save changes
COMMIT
Undo changes
ROLLBACK

Data Quality

Find missing data
WHERE email IS NULL

Cleaning

Remove duplicate values
DISTINCT col

Comments

SQL Comment (Single line)
-- Comment

Also included are essential clauses like JOIN, GROUP BY, and ORDER BY, which allow you to craft powerful queries for data analysis.