sqlANYcreate-tableddl
sql
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL
);Use explicit types and constraints to enforce data quality.
SQL DDL and DML
Create and change schema objects, then insert, update, delete, merge, and manage transactional data changes.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
CREATE, ALTER, and DROP
Build and change schema objects.
sqlANYalter-tableddl
sql
ALTER TABLE users
ADD COLUMN last_login_at TIMESTAMP;ALTER TABLE changes schema without recreating the table.
sqlANYalter-tableconstraintforeign-key
sql
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);Constraints help maintain relational integrity.
sqlANYdrop-tableddl
sql
DROP TABLE old_logs;Be careful: dropping a table deletes its data. Some engines support IF EXISTS.
sqlANYtruncateddlmaintenance
sql
TRUNCATE TABLE staging_events;TRUNCATE is often faster than DELETE for clearing a table.
Constraints and Indexes
Enforce integrity and speed up lookups.
sqlANYprimary-keyconstraint
sql
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);Primary keys uniquely identify each row.
sqlANYuniqueconstraint
sql
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);Unique constraints prevent duplicate values.
sqlANYcheck-constraintconstraint
sql
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price >= 0);CHECK constraints enforce domain rules inside the database.
sqlANYindexperformanceddl
sql
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);Indexes speed up reads but add write overhead and storage cost.
sqlANYindexcomposite-indexperformance
sql
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);Column order matters in composite indexes.
INSERT, UPDATE, and DELETE
Modify table data.
sqlANYinsertdml
sql
INSERT INTO users (user_id, email, created_at)
VALUES (1, 'a@example.com', CURRENT_TIMESTAMP);Specify column names explicitly for clarity and safety.
sqlANYinsertbulk-insert
sql
INSERT INTO roles (role_id, role_name)
VALUES
(1, 'admin'),
(2, 'editor'),
(3, 'viewer');Multi-row INSERT is concise and often more efficient than repeated single-row inserts.
sqlANYinsertselect
sql
INSERT INTO active_customers (customer_id, email)
SELECT customer_id, email
FROM customers
WHERE status = "active";Great for staging and snapshot tables.
sqlANYupdatedml
sql
UPDATE users
SET status = 'inactive'
WHERE last_login_at < DATE '2024-01-01';Always confirm the WHERE clause before running UPDATE in production.
sqlANYdeletedml
sql
DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;DELETE removes rows one by one from the table.
sqlANYmergeupsertdml
sql
MERGE INTO inventory AS target
USING incoming_inventory AS src
ON target.sku = src.sku
WHEN MATCHED THEN UPDATE SET quantity = src.quantity
WHEN NOT MATCHED THEN INSERT (sku, quantity) VALUES (src.sku, src.quantity);MERGE support and exact syntax vary by dialect. Some engines use ON CONFLICT or ON DUPLICATE KEY UPDATE instead.
Transactions
Control units of work and isolation boundaries.
sqlANYtransactionsbegincommitrollback
sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;If something fails, use ROLLBACK instead of COMMIT.
sqlANYtransactionssavepoint
sql
BEGIN;
SAVEPOINT before_optional_step;
UPDATE orders SET status = "archived" WHERE order_id = 10;
ROLLBACK TO SAVEPOINT before_optional_step;
COMMIT;Savepoints let you recover from part of a transaction without aborting the whole unit of work.
sqlANYtransactionsisolation
sql
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;Isolation level support can vary across engines, but this is a core transactional concept.
More in SQL
SQL Indexing and Performance
Query tuning patterns with EXPLAIN, indexing, keyset pagination, deduplication, and optimization-oriented SQL recipes.
SQL Window Functions and CTEs
Use CTEs, recursive queries, ranking, running totals, partitions, and lag/lead patterns for analytical SQL.
SQL Functions and Aggregation
Reference for string, date, numeric, conditional, null-handling, and aggregate SQL functions.
SQL Joins and Subqueries
Advanced relational query patterns for joins, subqueries, EXISTS, IN, set operations, and correlated queries.
SQL Cheat Sheet
Core SQL syntax and patterns for querying, filtering, sorting, joining, grouping, and modifying data.