SQL Cheatsheet

This cheatsheet provides a comprehensive and practical reference for SQL commands. It covers data retrieval, manipulation, table operations, constraints, joins, aggregation functions, and advanced SQL patterns. Use it to boost your productivity and master database operations.

Data Retrieval

SELECT * FROM table_name
Retrieve all columns from a table
SELECT column1, column2 FROM table_name
Retrieve specific columns from a table
SELECT DISTINCT column FROM table_name
Retrieve unique values from a column
SELECT * FROM table_name WHERE condition
Retrieve rows that meet a specific condition
SELECT * FROM table_name LIMIT 10
Retrieve a limited number of rows
SELECT * FROM table_name OFFSET 10 LIMIT 10
Retrieve rows with pagination
SELECT * FROM table_name ORDER BY column ASC
Sort results in ascending order
SELECT * FROM table_name ORDER BY column DESC
Sort results in descending order
SELECT COUNT(*) FROM table_name
Count the number of rows in a table
SELECT column, COUNT(*) FROM table_name GROUP BY column
Group results by a column
SELECT * FROM table_name WHERE column LIKE 'pattern%'
Search for pattern matches
SELECT * FROM table_name WHERE column IN (value1, value2)
Match against multiple possible values
SELECT * FROM table_name WHERE column BETWEEN value1 AND value2
Select rows within a range

Data Modification

INSERT INTO table_name (column1, column2) VALUES (value1, value2)
Insert a new row
INSERT INTO table_name VALUES (value1, value2, ...)
Insert a complete row
INSERT INTO table_name SELECT * FROM other_table
Insert data from another table
UPDATE table_name SET column = value WHERE condition
Update existing rows
DELETE FROM table_name WHERE condition
Delete rows
TRUNCATE TABLE table_name
Delete all rows from a table
MERGE INTO target_table USING source_table ON condition WHEN MATCHED THEN UPDATE SET column = value WHEN NOT MATCHED THEN INSERT (columns) VALUES (values)
Perform UPSERT operation (if supported)

Table Operations

CREATE TABLE table_name (column1 datatype, column2 datatype)
Create a new table
CREATE TABLE table_name AS SELECT * FROM other_table
Create a table from a query result
ALTER TABLE table_name ADD column datatype
Add a new column
ALTER TABLE table_name DROP COLUMN column
Remove a column
ALTER TABLE table_name MODIFY column datatype
Change column data type
ALTER TABLE table_name RENAME TO new_name
Rename a table
DROP TABLE table_name
Delete a table
TRUNCATE TABLE table_name
Remove all rows from a table
CREATE TEMPORARY TABLE temp_table (column1 datatype)
Create a temporary table
CREATE TABLE IF NOT EXISTS table_name (column1 datatype)
Create a table only if it doesn't exist

Constraints

CREATE TABLE table_name (id INT PRIMARY KEY, ...)
Define a primary key
CREATE TABLE table_name (column VARCHAR(50) NOT NULL, ...)
Define a NOT NULL constraint
CREATE TABLE table_name (column VARCHAR(50) UNIQUE, ...)
Define a unique constraint
CREATE TABLE table_name (... CHECK (condition))
Define a check constraint
CREATE TABLE table_name (... FOREIGN KEY (column) REFERENCES other_table(other_column))
Define a foreign key
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column)
Add a primary key constraint
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column) REFERENCES other_table(other_column)
Add a foreign key constraint
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Remove a constraint

Joins

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id
Inner join - returns matching rows
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
Left join - returns all rows from left table
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id
Right join - returns all rows from right table
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id
Full outer join - returns all rows from both tables
SELECT * FROM table1 CROSS JOIN table2
Cross join - returns Cartesian product
SELECT * FROM table1 JOIN table2 USING (column_name)
Join using a common column name
SELECT * FROM table1 NATURAL JOIN table2
Join based on columns with the same name
SELECT * FROM table1 t1, table2 t2 WHERE t1.id = t2.id
Old-style join syntax

Aggregation Functions

SELECT COUNT(*) FROM table_name
Count number of rows
SELECT COUNT(column) FROM table_name
Count non-NULL values in a column
SELECT SUM(column) FROM table_name
Sum values in a column
SELECT AVG(column) FROM table_name
Calculate average of a column
SELECT MIN(column) FROM table_name
Find minimum value in a column
SELECT MAX(column) FROM table_name
Find maximum value in a column
SELECT GROUP_CONCAT(column SEPARATOR ',') FROM table_name
Concatenate values (MySQL)
SELECT STRING_AGG(column, ',') FROM table_name
Concatenate values (PostgreSQL, SQL Server)
SELECT column, COUNT(*) FROM table_name GROUP BY column
Group by a column
SELECT column, COUNT(*) FROM table_name GROUP BY column HAVING COUNT(*) > 5
Filter grouped results

Subqueries

SELECT * FROM table_name WHERE column IN (SELECT column FROM other_table)
Subquery in WHERE clause
SELECT * FROM (SELECT * FROM table_name) AS derived
Subquery in FROM clause
SELECT column, (SELECT MAX(column) FROM other_table) FROM table_name
Subquery in SELECT clause
SELECT * FROM table_name WHERE column > ALL (SELECT column FROM other_table)
Comparison with ALL results
SELECT * FROM table_name WHERE column > ANY (SELECT column FROM other_table)
Comparison with ANY results
SELECT * FROM table_name WHERE EXISTS (SELECT 1 FROM other_table WHERE condition)
Check if subquery returns results

Indexes

CREATE INDEX idx_name ON table_name (column)
Create an index on a column
CREATE UNIQUE INDEX idx_name ON table_name (column)
Create a unique index
CREATE INDEX idx_name ON table_name (column1, column2)
Create a composite index
DROP INDEX idx_name
Remove an index
CREATE FULLTEXT INDEX idx_name ON table_name (column)
Create a fulltext index (for text search)
CREATE SPATIAL INDEX idx_name ON table_name (geo_column)
Create a spatial index
SHOW INDEX FROM table_name
Show indexes on a table (MySQL)
SELECT * FROM pg_indexes WHERE tablename = 'table_name'
Show indexes on a table (PostgreSQL)

Transactions

BEGIN TRANSACTION
Start a transaction
COMMIT
Save changes and end transaction
ROLLBACK
Discard changes and end transaction
SAVEPOINT savepoint_name
Create a savepoint
ROLLBACK TO savepoint_name
Rollback to a savepoint
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Set transaction isolation level

Views

CREATE VIEW view_name AS SELECT * FROM table_name WHERE condition
Create a view
CREATE OR REPLACE VIEW view_name AS SELECT * FROM table_name
Create or update a view
DROP VIEW view_name
Delete a view
CREATE MATERIALIZED VIEW view_name AS SELECT * FROM table_name
Create a materialized view (PostgreSQL)
REFRESH MATERIALIZED VIEW view_name
Refresh a materialized view (PostgreSQL)

Data Types & Functions

CAST(expression AS datatype)
Convert data to another type
CONVERT(expression, datatype)
Convert data (SQL Server)
COALESCE(val1, val2, ...)
Return first non-NULL value
NULLIF(val1, val2)
Return NULL if val1 = val2
CASE WHEN condition THEN result1 ELSE result2 END
Conditional logic
CURRENT_DATE
Get current date
CURRENT_TIME
Get current time
CURRENT_TIMESTAMP
Get current date and time
DATE_FORMAT(date, format)
Format date (MySQL)
TO_CHAR(date, format)
Format date (PostgreSQL)
DATEDIFF(date1, date2)
Difference between dates
CONCAT(str1, str2, ...)
Concatenate strings
SUBSTRING(string, start, length)
Extract substring
UPPER(string)
Convert to uppercase
LOWER(string)
Convert to lowercase
TRIM(string)
Remove leading/trailing spaces
ROUND(number, decimals)
Round a number

Database Administration

CREATE DATABASE db_name
Create a new database
DROP DATABASE db_name
Delete a database
USE db_name
Switch to a database
CREATE USER 'username'@'host' IDENTIFIED BY 'password'
Create a user (MySQL)
CREATE ROLE role_name
Create a role
GRANT privileges ON object TO user_or_role
Grant privileges
REVOKE privileges ON object FROM user_or_role
Revoke privileges
SHOW DATABASES
List all databases
SHOW TABLES
List all tables in a database
DESCRIBE table_name
Show table structure
SHOW CREATE TABLE table_name
Show CREATE statement for a table
BACKUP DATABASE db_name TO DISK = 'path'
Backup a database (SQL Server)
RESTORE DATABASE db_name FROM DISK = 'path'
Restore a database (SQL Server)

Common SQL Patterns

WITH cte_name AS (SELECT * FROM table) SELECT * FROM cte_name
Common Table Expression (CTE)
SELECT * FROM table_name FOR UPDATE
Lock rows for update
SELECT DISTINCT ON (column) * FROM table_name ORDER BY column, id
Get first row per group (PostgreSQL)
SELECT * FROM table1 UNION SELECT * FROM table2
Combine results without duplicates
SELECT * FROM table1 UNION ALL SELECT * FROM table2
Combine results with duplicates
SELECT * FROM table1 INTERSECT SELECT * FROM table2
Rows in both results
SELECT * FROM table1 EXCEPT SELECT * FROM table2
Rows in first result but not second
SELECT ROW_NUMBER() OVER (ORDER BY column) FROM table_name
Add sequential row numbers
SELECT RANK() OVER (PARTITION BY col1 ORDER BY col2) FROM table_name
Rank rows within groups
SELECT LAG(column, 1) OVER (ORDER BY id) FROM table_name
Access previous row value
SELECT LEAD(column, 1) OVER (ORDER BY id) FROM table_name
Access next row value
SELECT SUM(column) OVER (PARTITION BY other_column) FROM table_name
Window function for running totals

Categories

  • Data Retrieval

    Commands for querying and retrieving data from tables, including filtering, sorting, and limiting results.

  • Data Modification

    Commands for inserting, updating, and deleting data in tables.

  • Table Operations

    Commands for creating, altering, and managing database tables.

  • Constraints

    Commands for defining and managing data integrity constraints.

  • Joins

    Commands for combining data from multiple tables using various join types.

  • Aggregation Functions

    Commands for performing calculations across sets of rows.

  • Subqueries

    Commands for using queries within other queries for complex operations.

  • Indexes

    Commands for creating and managing indexes to optimize query performance.

  • Transactions

    Commands for managing transactional operations and ensuring data consistency.

  • Views

    Commands for creating and managing virtual tables based on SQL queries.

  • Data Types & Functions

    Common SQL functions for data conversion, manipulation, and calculations.

  • Database Administration

    Commands for managing databases, users, and permissions.

  • Common SQL Patterns

    Advanced SQL patterns and techniques for solving common problems.

Features

  • Quick search functionality
  • Organized by functional categories
  • Clear command descriptions
  • Common SQL syntax compatible with most systems
  • Database-specific variations noted where relevant
  • Easy to copy commands
  • Advanced SQL patterns for real-world problems
  • Responsive design for all devices