SQL Cheatsheet

Comprehensive SQL command reference with 70+ commands organized by category. Search, filter by category, save favorites, and click to copy any command.

SQL Cheatsheet

Essential SQL commands for database operations

87

Commands

11

Categories

0

Favorites

26

Sections

SELECT
Basic Queries
SELECT * FROM table_name

Select all columns

SELECT col1, col2 FROM table_name

Select specific columns

SELECT DISTINCT col FROM table_name

Select unique values

SELECT * FROM table_name LIMIT 10

Limit results

SELECT * FROM table_name OFFSET 10 LIMIT 10

Pagination

SELECT
WHERE Clause
SELECT * FROM t WHERE col = 'value'

Equal condition

SELECT * FROM t WHERE col != 'value'

Not equal

SELECT * FROM t WHERE col BETWEEN 1 AND 10

Range condition

SELECT * FROM t WHERE col IN ('a', 'b', 'c')

Multiple values

SELECT * FROM t WHERE col LIKE 'pat%'

Pattern matching

SELECT * FROM t WHERE col IS NULL

NULL check

SELECT
ORDER BY
SELECT * FROM t ORDER BY col ASC

Sort ascending

SELECT * FROM t ORDER BY col DESC

Sort descending

SELECT * FROM t ORDER BY c1 ASC, c2 DESC

Multiple sort columns

Modify
INSERT
INSERT INTO t (c1, c2) VALUES ('v1', 'v2')

Insert single row

INSERT INTO t VALUES ('v1', 'v2', 'v3')

Insert all columns

INSERT INTO t (c1, c2) VALUES ('a', 'b'), ('c', 'd')

Insert multiple rows

INSERT INTO t SELECT * FROM other_table

Insert from select

Modify
UPDATE
UPDATE t SET col = 'value' WHERE id = 1

Update single column

UPDATE t SET c1 = 'v1', c2 = 'v2' WHERE id = 1

Update multiple columns

UPDATE t SET col = col + 1 WHERE id = 1

Increment value

Modify
DELETE
DELETE FROM t WHERE id = 1

Delete specific rows

DELETE FROM t

Delete all rows

TRUNCATE TABLE t

Fast delete all (no rollback)

Tables
CREATE TABLE
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(100))

Create basic table

CREATE TABLE t (id SERIAL PRIMARY KEY, name TEXT NOT NULL)

Auto-increment (PostgreSQL)

CREATE TABLE IF NOT EXISTS t (id INT)

Create if not exists

Tables
ALTER TABLE
ALTER TABLE t ADD COLUMN col VARCHAR(50)

Add column

ALTER TABLE t DROP COLUMN col

Drop column

ALTER TABLE t RENAME COLUMN old TO new

Rename column

ALTER TABLE t RENAME TO new_name

Rename table

Tables
DROP & TRUNCATE
DROP TABLE t

Delete table

DROP TABLE IF EXISTS t

Delete if exists

TRUNCATE TABLE t

Remove all rows

JOINs
Join Types
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id

Inner join - matching rows only

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id

Left join - all from left table

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.t1_id

Right join - all from right table

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id

Full join - all from both tables

SELECT * FROM t1 CROSS JOIN t2

Cross join - cartesian product

JOINs
Join Variations
SELECT * FROM t1 JOIN t2 USING (col)

Join using common column

SELECT * FROM t1 NATURAL JOIN t2

Natural join - auto match columns

SELECT * FROM t1 a JOIN t1 b ON a.parent_id = b.id

Self join

💡 INNER JOIN is the default when using just JOIN

Aggregate
Aggregate Functions
SELECT COUNT(*) FROM t

Count all rows

SELECT COUNT(DISTINCT col) FROM t

Count unique values

SELECT SUM(col) FROM t

Sum of values

SELECT AVG(col) FROM t

Average value

SELECT MIN(col), MAX(col) FROM t

Min and max values

Aggregate
GROUP BY
SELECT col, COUNT(*) FROM t GROUP BY col

Group and count

SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 5

Filter groups

Subqueries
Subquery Types
SELECT * FROM t WHERE col IN (SELECT col FROM t2)

Subquery in WHERE

SELECT * FROM (SELECT * FROM t) AS sub

Subquery in FROM

SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id)

EXISTS subquery

Subqueries
CTE (Common Table Expression)
WITH cte AS (SELECT * FROM t) SELECT * FROM cte

Basic CTE

WITH RECURSIVE cte AS (...) SELECT * FROM cte

Recursive CTE

💡 CTEs improve readability and can be referenced multiple times

Constraints
Column Constraints
col INT PRIMARY KEY

Primary key

col VARCHAR(50) NOT NULL

Not null constraint

col VARCHAR(50) UNIQUE

Unique constraint

col INT DEFAULT 0

Default value

col INT REFERENCES other_table(id)

Foreign key

Constraints
Table Constraints
PRIMARY KEY (c1, c2)

Composite primary key

FOREIGN KEY (col) REFERENCES t(id) ON DELETE CASCADE

FK with cascade delete

Indexes
Create Indexes
CREATE INDEX idx ON t (col)

Create index

CREATE UNIQUE INDEX idx ON t (col)

Unique index

CREATE INDEX idx ON t (c1, c2)

Composite index

Indexes
Manage Indexes
DROP INDEX idx

Drop index

SHOW INDEX FROM t

Show indexes (MySQL)

Views
Create Views
CREATE VIEW v AS SELECT * FROM t WHERE condition

Create view

CREATE OR REPLACE VIEW v AS SELECT ...

Create or replace view

CREATE MATERIALIZED VIEW mv AS SELECT ...

Materialized view (PostgreSQL)

Views
Manage Views
DROP VIEW v

Drop view

REFRESH MATERIALIZED VIEW mv

Refresh materialized view

Admin
Database Management
CREATE DATABASE db_name

Create database

DROP DATABASE db_name

Drop database

SHOW DATABASES

List databases (MySQL)

\l

List databases (PostgreSQL)

Admin
Table Info
SHOW TABLES

List tables (MySQL)

\dt

List tables (PostgreSQL)

DESCRIBE t

Show table structure (MySQL)

Advanced
Transactions
BEGIN

Start transaction

COMMIT

Commit transaction

ROLLBACK

Rollback transaction

Advanced
Window Functions
SELECT col, ROW_NUMBER() OVER (ORDER BY col) FROM t

Row number

SELECT col, RANK() OVER (PARTITION BY c1 ORDER BY c2) FROM t

Rank within groups

SELECT col, SUM(col) OVER (ORDER BY id) FROM t

Running total

Advanced
Set Operations
SELECT * FROM t1 UNION SELECT * FROM t2

Union (no duplicates)

SELECT * FROM t1 UNION ALL SELECT * FROM t2

Union (with duplicates)

SELECT * FROM t1 INTERSECT SELECT * FROM t2

Intersection

Quick Reference

Select

SELECT * FROM t

Insert

INSERT INTO t VALUES

Update

UPDATE t SET c=v

Delete

DELETE FROM t WHERE

What is SQL?

SQL (Structured Query Language) is a standard programming language for managing and manipulating relational databases. It allows you to create, read, update, and delete data, as well as manage database structures and control access permissions.

SQL Categories

  • DQL (Data Query Language) - SELECT statements for retrieving data
  • DML (Data Manipulation Language) - INSERT, UPDATE, DELETE for modifying data
  • DDL (Data Definition Language) - CREATE, ALTER, DROP for managing structures
  • DCL (Data Control Language) - GRANT, REVOKE for access control
  • TCL (Transaction Control Language) - COMMIT, ROLLBACK for transactions

Best Practices

  • Use meaningful table and column names
  • Always use parameterized queries to prevent SQL injection
  • Create indexes on frequently queried columns
  • Use transactions for operations that must be atomic
  • Normalize your database design to reduce redundancy
  • Write readable queries with proper formatting and comments