SQL cheat sheet
The 128 keywords and functions you will use most, on one page. The everyday ones come first. Print it, or keep it open beside your editor.
Everyday 70
% (wildcard)- Inside LIKE, stand for any run of characters, including none at all.
+ - * / %- Do arithmetic: add, subtract, multiply, divide and find a remainder.
;- End one SQL statement so the next one can begin.
< > <= >=- Compare values to check which is smaller or larger.
= <> !=- Compare two values to check if they are equal or different.
? placeholder- Mark a spot in a query where a value is safely filled in later.
AND- Require that both conditions are true for a row to be kept.
AS- Give a column, calculation or table a new, clearer name.
ASC / DESC- Sort in ascending order (the default) or descending order.
AVG()- Work out the average of the values in a column.
BEGIN- Start a transaction so several changes succeed or fail together.
BETWEEN- Check whether a value falls inside a range, including both ends.
CASE- Choose a value based on conditions, like an if-then inside a query.
COALESCE()- Return the first value in a list that is not missing.
COMMIT- Save all the changes made since the transaction began.
COUNT(*)- Count how many rows there are, including rows with missing values.
COUNT(column)- Count the rows where a column has a value, skipping NULLs.
COUNT(DISTINCT)- Count how many different values appear in a column.
CREATE INDEX- Build a lookup structure that makes searching a column faster.
CREATE TABLE- Make a new table with named columns and their types.
date()- Get a date as YYYY-MM-DD text, optionally shifted by modifiers.
DEFAULT- Fill in a value automatically when an insert leaves a column out.
DELETE FROM- Remove rows from a table, usually limited with WHERE.
DISTINCT- Remove duplicate rows so each result appears only once.
DROP TABLE- Delete a whole table along with all of its rows.
ELSE- Give the value CASE uses when none of the conditions are true.
END- Close a CASE expression so the query can carry on.
FOREIGN KEY- Mark a column that points to the primary key of another table.
FROM- Name the table a query reads its rows from.
GROUP BY- Collect rows that share a value so totals are worked out per group.
HAVING- Keep only the groups that pass a condition, applied after grouping.
IN- Check whether a value matches any item in a list.
IN (subquery)- Check whether a value appears in the results of another query.
INSERT INTO- Add one or more new rows to a table you name.
INTEGER PRIMARY KEY- Make a column that SQLite fills with a new number for each row.
IS NOT NULL- Check that a value is present rather than missing.
IS NULL- Check whether a value is missing, since = NULL never matches.
JOIN- Combine rows from two tables where a matching condition holds.
LEFT JOIN- Keep every row from the first table, even when nothing matches.
LENGTH()- Count how many characters are in a piece of text.
LIKE- Match text against a pattern that uses % and _ as wildcards.
LIMIT- Return at most a given number of rows from a query.
LOWER()- Turn every letter in a piece of text into a lowercase letter.
MAX()- Find the largest value in a column, or in each group.
MIN()- Find the smallest value in a column, or in each group.
NOT- Flip a condition so true becomes false and false becomes true.
NOT NULL- Refuse any row that leaves this column empty.
NULL- A special marker meaning a value is missing or unknown.
ON- Give the condition that decides which rows of a join match up.
OR- Keep a row when at least one of the conditions is true.
ORDER BY- Sort the result rows by one or more columns.
PRIMARY KEY- Mark the column whose value uniquely identifies each row.
REFERENCES- Name the table and column that a foreign key points to.
ROLLBACK- Undo all the changes made since the transaction began.
ROUND()- Round a number to a chosen number of decimal places.
SELECT- Choose which columns or values a query returns.
SELECT *- Return every column of a table, handy for a quick look at the data.
SET- Say which columns to change and what their new values are.
subquery- A query placed inside another query, written in brackets.
SUM()- Add up all the values in a column, skipping missing ones.
table aliases- Give a table a short name so its columns are quicker to write.
TRIM()- Remove spaces from the start and end of some text.
UNIQUE- Refuse any row that repeats a value already in this column.
UPDATE- Change values in rows that already exist in a table.
UPPER()- Turn every letter in a piece of text into a capital letter.
VALUES- List the values for each new row in an INSERT statement.
WHEN ... THEN- Give one condition inside CASE and the value to use when it is true.
WHERE- Keep only the rows that pass a condition you write.
WITH- Name a query step up front so later parts can use it like a table.
||- Join two pieces of text together into one longer string.
Also useful 58
-- comment- Start a note that SQL ignores until the end of the line.
/* */- Wrap a note across several lines so SQL skips over it.
:name placeholder- Mark a named spot in a query where a value is safely filled in later.
_ (wildcard)- Inside LIKE, stand for exactly one character of any kind.
ALTER TABLE ADD COLUMN- Add a new column to a table that already exists.
ALTER TABLE RENAME- Give an existing table or column a new name.
CHECK- Refuse any row whose values fail a condition you write.
correlated subquery- A subquery that refers to the current row of the outer query.
CREATE UNIQUE INDEX- Build an index that also refuses duplicate values.
CREATE VIEW- Save a query under a name so you can select from it like a table.
date modifiers- Add words like '+7 days' or 'start of month' to shift a date.
DENSE_RANK()- Rank rows so ties share a number with no gaps after them.
DROP INDEX- Delete an index you no longer need, leaving the table as it was.
DROP VIEW- Delete a saved view without touching the tables it reads from.
ESCAPE- Pick a character that makes % or _ match literally inside LIKE.
EXISTS- Check whether a subquery returns at least one row.
EXPLAIN QUERY PLAN- Show how SQLite will run a query, such as whether it uses an index.
FULL JOIN- Keep every row from both tables, matched where possible.
GLOB- Match text with case-sensitive * and ? wildcards in SQLite.
IF NOT EXISTS- Skip creating something when it is already there, avoiding an error.
IFNULL()- Swap a missing value for a fallback value you choose.
IIF()- Pick one of two values depending on whether a condition is true.
INNER JOIN- The full name for JOIN, which keeps only rows that match in both tables.
INSERT ... SELECT- Add rows to a table using the results of a query.
INSTR()- Find the position where some text first appears inside another text.
julianday()- Turn a date into a day number so you can subtract two dates.
LAG()- Read a value from the row before the current one.
LEAD()- Read a value from the row after the current one.
NOT BETWEEN- Check that a value falls outside a range of values.
NOT EXISTS- Check that a subquery returns no rows at all.
NOT IN- Check that a value matches none of the items in a list.
NOT LIKE- Keep rows whose text does not match a LIKE pattern.
NULLS FIRST / NULLS LAST- Choose whether missing values appear at the start or end of a sort.
OFFSET- Skip a number of rows before returning results, often used for paging.
ON DELETE CASCADE- Delete linked rows automatically when the row they point to is deleted.
ON DELETE RESTRICT- Block deleting a row while other rows still point to it.
ON DELETE SET NULL- Empty the linking column when the row it points to is deleted.
OVER- Turn a function into a window function that looks across related rows.
PARTITION BY- Split rows into separate groups for a window function to work within.
PRAGMA- Run a SQLite command that reports or changes database settings.
RANK()- Rank rows so ties share a number and the next rank skips ahead.
RELEASE- Keep the changes since a savepoint and forget that savepoint.
REPLACE()- Swap every copy of one piece of text for another inside a value.
RETURNING- Show the rows that an INSERT, UPDATE or DELETE just changed.
RIGHT JOIN- Keep every row from the second table, even when nothing matches.
ROLLBACK TO- Undo changes back to a savepoint without ending the transaction.
ROW_NUMBER()- Number the rows 1, 2, 3 in order, with no shared numbers.
rowid- The hidden number SQLite gives every row in an ordinary table.
running SUM- Use SUM with OVER and ORDER BY to keep a running total down the rows.
SAVEPOINT- Mark a point inside a transaction that you can undo back to later.
scalar subquery- A subquery that returns a single value you can use like a number.
self-join- Join a table to itself to compare rows within the same table.
sqlite_master- A built-in table that lists every table, index and view in the database.
strftime()- Format a date or time as text using codes like %Y and %m.
STRICT- Make SQLite refuse values that do not match a column's type.
SUBSTR()- Take part of a text value, starting at a chosen position.
UNION ALL- Stack the rows of two queries on top of each other, keeping duplicates.
WITH RECURSIVE- Build rows step by step, useful for walking trees and hierarchies.
PlainFinch SQL cheat sheet · plainfinch.com/sql/cheat-sheet/ · version 3.25.2