SQL Cheat Sheet: All Commands (Quick Reference)

This is the SQL command reference you keep open in a second tab. Every command that matters - grouped by what it does, each with real syntax and a short runnable example - so you can find the exact shape you need in seconds, then adapt and move on.

SQL is a big language, but you use a small, repeating core of it every day. The trick is knowing which command belongs to which job: DDL defines structure, DML changes data, DQL reads it, and a handful of clauses (JOIN, GROUP BY, WHERE, window functions) shape the results. This page walks the whole surface area in that order. Where the big three databases disagree - MySQL, PostgreSQL, and SQL Server - the differences are called out inline so you are not caught out when you switch engines.

How to use this cheat sheet

Read it once top to bottom to see the shape of the language, then treat it as a lookup. Every command sits under the category that matches its job, so if you want to read data you jump to the SELECT section, and if you want to change structure you jump to DDL. Each entry gives you the syntax skeleton plus a tiny, runnable example you can paste into your client and adapt by swapping table and column names. The examples all use one small schema - a customers table and an orders table - so the pieces connect as you scroll. Where a keyword differs between engines, a blue note tells you the MySQL, PostgreSQL, and SQL Server spelling so you never copy syntax that silently fails on the database in front of you. Nothing here needs to be memorised: the goal is to make the right shape findable in seconds.

What this cheat sheet covers

DDL: CREATE, ALTER, DROP, TRUNCATE, RENAME
Data types
Constraints
DML: INSERT, UPDATE, DELETE, UPSERT
DQL: the SELECT statement
Operators & wildcards
JOINs
Set operators: UNION, INTERSECT, EXCEPT
Aggregate, string, date & numeric functions
Subqueries & CTEs
Window functions
Views, indexes & transactions
How often you will use each command

1. DDL - defining structure

Data Definition Language commands create and change the shape of the database itself: databases, tables, indexes, and views. They are not undone by a rollback in most engines (they auto-commit), so treat them with care. For a deeper walkthrough see the DDL commands guide.

Command	Purpose	Minimal example
`CREATE DATABASE`	Make a new database	`CREATE DATABASE shop;`
`CREATE TABLE`	Define a table and its columns	`CREATE TABLE t (id INT);`
`CREATE INDEX`	Speed up lookups on a column	`CREATE INDEX ix ON t(id);`
`CREATE VIEW`	Save a query as a virtual table	`CREATE VIEW v AS SELECT ...;`
`ALTER TABLE`	Add, change, or drop columns	`ALTER TABLE t ADD c INT;`
`DROP`	Delete an object entirely	`DROP TABLE t;`
`TRUNCATE`	Empty a table fast, keep structure	`TRUNCATE TABLE t;`
`RENAME`	Rename a table	`RENAME TABLE t TO t2;`

CREATE

Start with a database, then the tables inside it. IF NOT EXISTS makes the statement safe to re-run.

SQL

CREATE DATABASE IF NOT EXISTS shop;

CREATE TABLE customers (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(150) UNIQUE,
  country    CHAR(2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Auto-increment differs by engine. MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL or GENERATED ALWAYS AS IDENTITY, and SQL Server uses IDENTITY(1,1). The column concept is the same; only the keyword changes.

An index makes reads on a column fast; a view stores a query under a name so you can select from it like a table.

SQL

-- Index the column you filter or join on most
CREATE INDEX idx_customers_country ON customers(country);

-- A view is a saved query, queried like a table
CREATE VIEW uae_customers AS
SELECT id, name, email FROM customers WHERE country = 'AE';

ALTER TABLE

Change a table after it exists. The three moves you make constantly are add, modify, and drop a column.

SQL

-- Add a column
ALTER TABLE customers ADD phone VARCHAR(20);

-- Change a column's type (MySQL)
ALTER TABLE customers MODIFY phone VARCHAR(30);

-- Drop a column
ALTER TABLE customers DROP COLUMN phone;

MODIFY vs ALTER COLUMN. MySQL changes a column type with MODIFY column type. PostgreSQL and SQL Server use ALTER COLUMN - Postgres as ALTER COLUMN phone TYPE VARCHAR(30), SQL Server as ALTER COLUMN phone VARCHAR(30). Same intent, three spellings.

DROP, TRUNCATE & RENAME

DROP removes the object and all its data. TRUNCATE keeps the empty table but wipes every row instantly and resets identity counters - it is far faster than DELETE for clearing a whole table but cannot be filtered with WHERE. RENAME just changes a name.

SQL

DROP TABLE IF EXISTS old_logs;
TRUNCATE TABLE staging_orders;
RENAME TABLE customers TO clients;   -- MySQL
-- PostgreSQL / SQL Server: ALTER TABLE customers RENAME TO clients;

TRUNCATE vs DELETE. Both empty rows, but they are not the same. DELETE is DML: it removes rows one at a time, can be filtered with WHERE, fires row triggers, and can be rolled back. TRUNCATE is DDL: it deallocates the whole table's data in one operation, ignores WHERE, resets the identity counter, and in MySQL auto-commits so it cannot be undone. Reach for TRUNCATE only when you truly want the table completely empty and fast.

2. Data types

Pick the smallest type that comfortably holds your data. The exact names vary a little between engines, but they group into four families.

Family	Common types	Use for
Numeric	`INT`, `BIGINT`, `SMALLINT`, `DECIMAL(p,s)`, `NUMERIC`, `FLOAT`, `DOUBLE`	IDs, counts, money (use `DECIMAL`, never `FLOAT`, for currency)
String	`CHAR(n)`, `VARCHAR(n)`, `TEXT`	Names, emails, notes; `CHAR` for fixed-length codes
Date / time	`DATE`, `TIME`, `DATETIME`, `TIMESTAMP`	Calendar dates, timestamps, audit columns
Boolean / other	`BOOLEAN`, `ENUM`, `JSON`, `UUID`, `BLOB`	Flags, fixed choice lists, structured data, binaries

Booleans are not universal. PostgreSQL has a real BOOLEAN. MySQL treats BOOLEAN as an alias for TINYINT(1). SQL Server has no boolean type - use BIT (0 or 1). Store money as DECIMAL(10,2) everywhere to avoid rounding errors.

3. Constraints

Constraints are rules the database enforces on every row so bad data never lands. Declare them in CREATE TABLE or add them later with ALTER TABLE.

Constraint	Guarantees	Example
`PRIMARY KEY`	Unique + not null; the row's identity	`id INT PRIMARY KEY`
`FOREIGN KEY`	Value must exist in another table	`REFERENCES customers(id)`
`UNIQUE`	No two rows share this value	`email VARCHAR(150) UNIQUE`
`NOT NULL`	A value is always required	`name VARCHAR(100) NOT NULL`
`CHECK`	Value passes a condition	`CHECK (total >= 0)`
`DEFAULT`	Fills a value when none is given	`status VARCHAR(20) DEFAULT 'new'`
`AUTO_INCREMENT`	Auto-numbers new rows	`id INT AUTO_INCREMENT`

SQL

CREATE TABLE orders (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  total       DECIMAL(10,2) CHECK (total >= 0),
  status      VARCHAR(20) DEFAULT 'new',
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

4. DML - changing data

Data Manipulation Language adds, edits, and removes rows. The full tour lives in the DML commands guide; here is the fast version.

INSERT

SQL

-- Single row
INSERT INTO customers (name, email, country)
VALUES ('Sara Ahmed', 'sara@example.com', 'AE');

-- Multiple rows in one statement
INSERT INTO customers (name, country) VALUES
  ('Omar Ali', 'SA'),
  ('Lina Yusuf', 'EG');

-- Insert the result of a query
INSERT INTO archive_customers (id, name)
SELECT id, name FROM customers WHERE created_at < '2024-01-01';

UPDATE and DELETE

Both take a WHERE clause that decides which rows are affected. Leave it off and you hit every row in the table.

SQL

UPDATE customers SET country = 'SA' WHERE id = 42;

DELETE FROM customers WHERE id = 42;

Always test the WHERE first. Run SELECT * FROM customers WHERE id = 42; before the UPDATE or DELETE. If the SELECT returns the exact rows you mean to change, the write is safe.

UPSERT - insert or update

An upsert inserts a row, or updates it if a key already exists. Each engine spells it differently.

MySQL

INSERT INTO stock (sku, qty) VALUES ('A1', 5)
ON DUPLICATE KEY UPDATE qty = qty + 5;

PostgreSQL

INSERT INTO stock (sku, qty) VALUES ('A1', 5)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + 5;

SQL Server

MERGE stock AS t
USING (SELECT 'A1' AS sku, 5 AS qty) AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET t.qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

5. DQL - the SELECT statement

SELECT is the command you write most. The clauses always appear in this order, though the database evaluates them roughly as FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. See the DQL commands guide for the full clause order.

SQL

SELECT DISTINCT country, COUNT(*) AS customers
FROM customers
WHERE created_at >= '2025-01-01'
GROUP BY country
HAVING COUNT(*) > 10
ORDER BY customers DESC
LIMIT 5;

Columns: list them explicitly instead of SELECT * in real code - it is faster and self-documenting.
DISTINCT: removes duplicate rows from the result.
Aliases: AS renames a column or table (SUM(total) AS revenue, customers AS c).
ORDER BY: sort with ASC (default) or DESC; sort by several columns for tie-breaks.
LIMIT / OFFSET: paginate results.

Here is the same set of clauses at a glance - what each one does and the smallest example that shows it working.

Clause	Job	Tiny example
`WHERE`	Keep only rows that match a condition	`WHERE total > 100`
`DISTINCT`	Drop duplicate rows	`SELECT DISTINCT country`
`GROUP BY`	Fold rows into one per group	`GROUP BY country`
`HAVING`	Filter groups after aggregation	`HAVING COUNT(*) > 10`
`ORDER BY`	Sort the result set	`ORDER BY total DESC`
`LIMIT`	Cap how many rows come back	`LIMIT 5`

Row limiting is engine-specific. MySQL and PostgreSQL use LIMIT 10 OFFSET 20. SQL Server uses OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY (or the older SELECT TOP 10). Same goal, different syntax.

6. Operators & wildcards

The WHERE clause is where the logic lives. These operators cover nearly every real filter.

Group	Operators	Example
Comparison	`=` `<>` `<` `>` `<=` `>=`	`total >= 100`
Logical	`AND` `OR` `NOT`	`country = 'AE' AND total > 0`
Range	`BETWEEN a AND b`	`total BETWEEN 100 AND 500`
List	`IN (...)`	`status IN ('paid','shipped')`
Pattern	`LIKE` with `%` and `_`	`email LIKE '%@gmail.com'`
Null test	`IS NULL` / `IS NOT NULL`	`shipped_at IS NULL`

In LIKE patterns, % matches any run of characters (including none) and _ matches exactly one character. So 'S%' is anything starting with S, and 'S_ _ _' (no spaces) matches a four-letter name beginning with S.

SQL

SELECT name FROM customers
WHERE name LIKE 'S%'            -- starts with S
   AND email LIKE '%@gmail.com' -- ends with gmail
   AND country IN ('AE', 'SA')
   AND phone IS NOT NULL;

NULL is not equal to anything - not even another NULL. Test missing values with IS NULL / IS NOT NULL, never = NULL. Note also that PostgreSQL LIKE is case-sensitive (use ILIKE for case-insensitive), while MySQL LIKE is usually case-insensitive by default.

7. JOINs

A JOIN combines rows from two tables on a matching key. The type controls what happens to rows that have no match on the other side. Here is one-line syntax for each.

Join	Returns	Syntax
INNER	Only rows matched in both tables	`FROM a INNER JOIN b ON a.id = b.a_id`
LEFT	All left rows; NULLs where right has no match	`FROM a LEFT JOIN b ON a.id = b.a_id`
RIGHT	All right rows; NULLs where left has no match	`FROM a RIGHT JOIN b ON a.id = b.a_id`
FULL	All rows from both sides	`FROM a FULL JOIN b ON a.id = b.a_id`
CROSS	Every combination (Cartesian product)	`FROM a CROSS JOIN b`
SELF	A table joined to itself	`FROM emp e JOIN emp m ON e.mgr_id = m.id`

SQL

-- Every order with its customer's name
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

-- All customers, even those with zero orders
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

MySQL has no FULL JOIN. Emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION. PostgreSQL and SQL Server support FULL OUTER JOIN directly. Want the full picture? Read the deep dive on JOINs.

8. Set operators

Set operators stack the results of two SELECTs that have the same columns. UNION removes duplicates; UNION ALL keeps them and is faster. INTERSECT returns rows in both; EXCEPT returns rows in the first but not the second.

SQL

SELECT email FROM customers
UNION
SELECT email FROM newsletter_signups;

-- Emails on both lists
SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_signups;

MySQL added INTERSECT and EXCEPT in 8.0.31. Older MySQL versions have only UNION / UNION ALL; emulate the rest with joins or NOT IN. SQL Server calls the difference operator EXCEPT; Oracle calls it MINUS.

9. Aggregate, string, date & numeric functions

Aggregate functions collapse many rows into one value; scalar functions transform a single value. The full catalogue is in the SQL functions reference and the functions list.

SQL

SELECT
  COUNT(*)        AS orders,
  SUM(total)     AS revenue,
  AVG(total)     AS avg_order,
  MIN(total)     AS smallest,
  MAX(total)     AS largest
FROM orders;

String functions

Function	Does	Example → result
`CONCAT`	Join strings	`CONCAT(name,' (',country,')')`
`UPPER` / `LOWER`	Change case	`UPPER('ae')` → `AE`
`LENGTH`	Character count	`LENGTH('sql')` → `3`
`SUBSTRING`	Slice text	`SUBSTRING('sql',1,2)` → `sq`
`TRIM`	Strip surrounding spaces	`TRIM(' hi ')` → `hi`
`REPLACE`	Swap substrings	`REPLACE('a-b','-','_')` → `a_b`
`LEFT` / `RIGHT`	First / last N characters	`LEFT('sql-cheat',3)` → `sql`
`POSITION` / `LOCATE`	Index of a substring	`POSITION('@' IN email)`
`LPAD` / `RPAD`	Pad to a fixed width	`LPAD('7',3,'0')` → `007`

Date & numeric functions

Function	Does	Example
`NOW` / `CURRENT_TIMESTAMP`	Current date and time	`SELECT NOW();`
`CURRENT_DATE`	Today's date	`WHERE due = CURRENT_DATE`
`DATEDIFF`	Days between dates	`DATEDIFF(d2, d1)`
`EXTRACT`	Pull a part of a date	`EXTRACT(YEAR FROM created_at)`
`ROUND`	Round a number	`ROUND(19.99, 1)` → `20.0`
`COALESCE`	First non-null value	`COALESCE(phone, 'n/a')`
`DATE_ADD` / `+ INTERVAL`	Shift a date by an interval	`DATE_ADD(due, INTERVAL 7 DAY)`
`CEIL` / `FLOOR`	Round up / down to integer	`CEIL(19.1)` → `20`
`ABS`	Absolute value	`ABS(-42)` → `42`
`MOD` / `%`	Remainder of a division	`MOD(10, 3)` → `1`
`CAST`	Convert between types	`CAST('42' AS INT)`

String concatenation varies wildly. Standard SQL and PostgreSQL use the || operator (name || ' ' || country). MySQL uses CONCAT() (its || means OR by default). SQL Server uses + or CONCAT(). When in doubt, CONCAT() works on MySQL, Postgres, and SQL Server alike.

10. Subqueries & CTEs

A subquery is a SELECT nested inside another statement. A CTE (Common Table Expression) with WITH does the same job but reads top to bottom, which is far easier to follow when queries get long. Both appear in the advanced SQL queries guide.

Subquery

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);

CTE

WITH big_spenders AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 1000
)
SELECT c.name, b.spent
FROM big_spenders b
JOIN customers c ON c.id = b.customer_id;

A recursive CTE references itself to walk hierarchies - org charts, category trees, number series. Use WITH RECURSIVE.

Recursive CTE

WITH RECURSIVE nums AS (
  SELECT 1 AS n            -- anchor row
  UNION ALL
  SELECT n + 1 FROM nums   -- recursive step
  WHERE n < 10
)
SELECT n FROM nums;      -- 1..10

SQL Server drops the RECURSIVE keyword. It writes recursive CTEs with plain WITH nums AS (...); MySQL and PostgreSQL require WITH RECURSIVE. The anchor-plus-UNION ALL structure is identical.

11. Window functions

Window functions compute across a set of rows related to the current row - running totals, rankings, row numbers - without collapsing them the way GROUP BY does. Every row stays; you just get an extra calculated column. The OVER clause defines the window, optionally partitioned and ordered.

Function	Returns
`ROW_NUMBER()`	Sequential number per row in the window
`RANK()`	Rank with gaps after ties (1,1,3)
`DENSE_RANK()`	Rank with no gaps (1,1,2)
`LAG()` / `LEAD()`	Value from the previous / next row
`SUM() OVER`	Running or partitioned total

SQL

-- Rank orders within each country by size
SELECT country, id, total,
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY total DESC) AS rn,
  LAG(total) OVER (PARTITION BY country ORDER BY id) AS prev_total
FROM orders;

For the full treatment - frames, RANGE vs ROWS, and real reporting patterns - see the window functions guide.

12. Views, indexes & transactions

Views

A view is a stored query you can select from like a table. It simplifies complex logic and hides columns you do not want exposed.

SQL

CREATE VIEW monthly_revenue AS
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
       SUM(total) AS revenue
FROM orders GROUP BY month;

SELECT * FROM monthly_revenue;   -- query it like a table

Indexes

An index is a lookup structure that turns a full table scan into a fast seek. Index the columns you filter, join, and sort on - but not every column, since each index slows down writes.

SQL

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE UNIQUE INDEX idx_customers_email ON customers(email);
DROP INDEX idx_orders_customer ON orders;   -- MySQL syntax

Transactions

A transaction groups statements so they all succeed or all roll back together - essential for money moves and any multi-step write. SAVEPOINT lets you roll back part of the way.

SQL

BEGIN;   -- or START TRANSACTION
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  SAVEPOINT after_debit;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK to undo everything

DDL usually cannot be rolled back. In MySQL, statements like CREATE, ALTER, and DROP auto-commit and silently end any open transaction. PostgreSQL is the exception - most of its DDL is fully transactional and can sit inside BEGIN ... ROLLBACK.

13. How often you will use each command

Not all commands earn equal time. In everyday application and analytics work, reading and filtering data dominates; schema changes are occasional. This is a representative picture of where your keystrokes actually go.

Share of everyday SQL you will write

SELECT / WHERE92%

JOIN74%

GROUP BY / aggregates61%

INSERT / UPDATE / DELETE55%

CTEs / subqueries40%

Window functions28%

DDL (CREATE / ALTER)18%

The takeaway: master the SELECT family and JOINs first, because they are 80 percent of the job. Everything else in this sheet is the long tail you reach for when a specific problem demands it. Bookmark this page, then go deeper with the focused guides - the SQL syntax guide for exact grammar, the functions list for every built-in, and the beginner course to build it all into muscle memory.

The SQL Cheat Sheet: All Commands