SQL Cheat Sheet

A complete reference of SQL commands with examples and usage instructions. Find the command you need using the search bar or browse by category.

37 commands found

Filter by category:

`SELECT`

Basic

Retrieve data from one or more tables

Syntax:

SELECT column1, column2 FROM table_name WHERE condition

Examples:

SELECT * FROM users; Select all columns from users table

SELECT name, email FROM users; Select specific columns

SELECT * FROM users WHERE age > 25; Select with condition

SELECT COUNT(*) FROM users; Count total rows

Notes:

The most fundamental SQL command for querying data

`INSERT`

Basic

Add new rows to a table

Syntax:

INSERT INTO table_name (column1, column2) VALUES (value1, value2)

Examples:

INSERT INTO users (name, email) VALUES ('John', 'john@email.com'); Insert single row

INSERT INTO users VALUES ('Jane', 'jane@email.com', 30); Insert without specifying columns

INSERT INTO users (name, email) VALUES ('Bob', 'bob@email.com'), ('Alice', 'alice@email.com'); Insert multiple rows

Notes:

Use specific column names for better maintainability

`UPDATE`

Basic

Modify existing data in a table

Syntax:

UPDATE table_name SET column1 = value1 WHERE condition

Examples:

UPDATE users SET age = 31 WHERE name = 'John'; Update specific row

UPDATE users SET email = 'newemail@domain.com', age = age + 1; Update multiple columns

UPDATE users SET status = 'active' WHERE created_date > '2023-01-01'; Update with date condition

Notes:

Always use WHERE clause to avoid updating all rows accidentally

`DELETE`

Basic

Remove rows from a table

Syntax:

DELETE FROM table_name WHERE condition

Examples:

DELETE FROM users WHERE id = 5; Delete specific row

DELETE FROM users WHERE age < 18; Delete multiple rows

DELETE FROM users; Delete all rows (use with caution)

Notes:

Always use WHERE clause unless you really want to delete all rows

`CREATE DATABASE`

Database

Create a new database

Syntax:

CREATE DATABASE database_name

Examples:

CREATE DATABASE myapp; Create a new database

CREATE DATABASE IF NOT EXISTS myapp; Create only if doesn't exist

CREATE DATABASE myapp CHARACTER SET utf8mb4; Create with specific character set

Notes:

Database names should follow naming conventions

`DROP DATABASE`

Database

Delete an entire database

Syntax:

DROP DATABASE database_name

Examples:

DROP DATABASE myapp; Delete database permanently

DROP DATABASE IF EXISTS myapp; Delete only if exists

Notes:

This permanently deletes all data - use with extreme caution

`USE`

Database

Select a database to work with

Syntax:

USE database_name

Examples:

USE myapp; Switch to myapp database

USE information_schema; Switch to system database

Notes:

Must select a database before performing table operations

`SHOW DATABASES`

Database

List all databases

Syntax:

SHOW DATABASES

Examples:

SHOW DATABASES; List all available databases

SHOW DATABASES LIKE 'app%'; List databases matching pattern

Notes:

Useful for exploring available databases

`CREATE TABLE`

Tables

Create a new table

Syntax:

CREATE TABLE table_name (column1 datatype, column2 datatype)

Examples:

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255)); Create simple table

CREATE TABLE posts (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Create table with auto-increment and timestamp

CREATE TABLE IF NOT EXISTS categories (id INT PRIMARY KEY, name VARCHAR(50) UNIQUE); Create only if doesn't exist

Notes:

Define appropriate data types and constraints for each column

`ALTER TABLE`

Tables

Modify an existing table structure

Syntax:

ALTER TABLE table_name ADD/DROP/MODIFY column_name datatype

Examples:

ALTER TABLE users ADD phone VARCHAR(20); Add new column

ALTER TABLE users DROP COLUMN phone; Remove column

ALTER TABLE users MODIFY email VARCHAR(320); Change column type

ALTER TABLE users RENAME TO customers; Rename table

Notes:

Be careful when modifying tables with existing data

`DROP TABLE`

Tables

Delete a table and all its data

Syntax:

DROP TABLE table_name

Examples:

DROP TABLE users; Delete table permanently

DROP TABLE IF EXISTS temp_table; Delete only if exists

Notes:

This permanently deletes the table and all data

`DESCRIBE`

Tables

Show table structure

Syntax:

DESCRIBE table_name

Examples:

DESCRIBE users; Show column details

DESC users; Short form of DESCRIBE

SHOW COLUMNS FROM users; Alternative syntax

Notes:

Useful for understanding table schema

`INNER JOIN`

Joins

Return rows that have matching values in both tables

Syntax:

SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column

Examples:

SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id; Join users with their posts

SELECT * FROM orders o INNER JOIN customers c ON o.customer_id = c.id; Join orders with customer details

Notes:

Only returns rows where the join condition is met in both tables

`LEFT JOIN`

Joins

Return all rows from left table and matching rows from right table

Syntax:

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column

Examples:

SELECT u.name, p.title FROM users u LEFT JOIN posts p ON u.id = p.user_id; All users, with posts if they exist

SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id; Customer order counts

Notes:

Includes all rows from the left table, even if no match in right table

`RIGHT JOIN`

Joins

Return all rows from right table and matching rows from left table

Syntax:

SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column

Examples:

SELECT u.name, p.title FROM users u RIGHT JOIN posts p ON u.id = p.user_id; All posts, with user info if available

Notes:

Less commonly used than LEFT JOIN

`FULL OUTER JOIN`

Joins

Return rows when there is a match in either table

Syntax:

SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column

Examples:

SELECT u.name, p.title FROM users u FULL OUTER JOIN posts p ON u.id = p.user_id; All users and all posts

Notes:

Not supported in all databases (e.g., MySQL)

`COUNT`

Functions

Count the number of rows

Syntax:

COUNT(column_name) or COUNT(*)

Examples:

SELECT COUNT(*) FROM users; Count all rows

SELECT COUNT(email) FROM users; Count non-null emails

SELECT status, COUNT(*) FROM users GROUP BY status; Count by group

Notes:

COUNT(*) includes NULL values, COUNT(column) excludes them

`SUM`

Functions

Calculate the sum of numeric values

Syntax:

SUM(column_name)

Examples:

SELECT SUM(amount) FROM orders; Total order amount

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id; Sum by customer

Notes:

Only works with numeric columns

`AVG`

Functions

Calculate the average of numeric values

Syntax:

AVG(column_name)

Examples:

SELECT AVG(age) FROM users; Average user age

SELECT department, AVG(salary) FROM employees GROUP BY department; Average salary by department

Notes:

Automatically excludes NULL values from calculation

`MIN/MAX`

Functions

Find minimum or maximum values

Syntax:

MIN(column_name) or MAX(column_name)

Examples:

SELECT MIN(price), MAX(price) FROM products; Price range

SELECT MIN(created_date) FROM users; First user registration

Notes:

Works with numeric, date, and string columns

`CONCAT`

Functions

Concatenate strings

Syntax:

CONCAT(string1, string2, ...)

Examples:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; Combine first and last name

SELECT CONCAT('Hello, ', name, '!') FROM users; Create greeting message

Notes:

Use CONCAT_WS for separator-based concatenation

`SUBSTRING`

Functions

Extract part of a string

Syntax:

SUBSTRING(string, start, length)

Examples:

SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username FROM users; Extract username from email

SELECT SUBSTRING(phone, 1, 3) AS area_code FROM contacts; Extract area code

Notes:

Position starts from 1, not 0

`UPPER/LOWER`

Functions

Convert string case

Syntax:

UPPER(string) or LOWER(string)

Examples:

SELECT UPPER(name) FROM users; Convert to uppercase

SELECT LOWER(email) FROM users; Convert to lowercase

Notes:

Useful for case-insensitive comparisons

`WHERE`

Filtering

Filter rows based on conditions

Syntax:

WHERE condition

Examples:

SELECT * FROM users WHERE age >= 18; Numeric comparison

SELECT * FROM users WHERE name LIKE 'John%'; Pattern matching

SELECT * FROM users WHERE email IS NOT NULL; NULL check

SELECT * FROM users WHERE age BETWEEN 25 AND 35; Range condition

Notes:

Use appropriate operators: =, !=, <, >, <=, >=, LIKE, IN, BETWEEN

`ORDER BY`

Filtering

Sort query results

Syntax:

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]

Examples:

SELECT * FROM users ORDER BY name; Sort by name (ascending)

SELECT * FROM users ORDER BY age DESC; Sort by age (descending)

SELECT * FROM users ORDER BY last_name, first_name; Sort by multiple columns

Notes:

ASC is default, use DESC for descending order

`GROUP BY`

Filtering

Group rows that have the same values

Syntax:

GROUP BY column1, column2

Examples:

SELECT department, COUNT(*) FROM employees GROUP BY department; Count employees by department

SELECT status, AVG(age) FROM users GROUP BY status; Average age by status

Notes:

Must use aggregate functions with non-grouped columns

`HAVING`

Filtering

Filter groups based on aggregate conditions

Syntax:

HAVING condition

Examples:

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; Departments with more than 5 employees

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000; High-value customers

Notes:

Use HAVING for aggregate conditions, WHERE for row conditions

`LIMIT`

Filtering

Limit the number of rows returned

Syntax:

LIMIT number [OFFSET number]

Examples:

SELECT * FROM users LIMIT 10; Get first 10 users

SELECT * FROM users ORDER BY created_date DESC LIMIT 5; Get 5 newest users

SELECT * FROM users LIMIT 10 OFFSET 20; Skip 20 rows, then get 10

Notes:

Useful for pagination and performance optimization

`Subquery`

Advanced

Query within another query

Syntax:

SELECT ... WHERE column IN (SELECT ...)

Examples:

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); Users who have orders

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products); Products above average price

Notes:

Can be used in SELECT, WHERE, and FROM clauses

`UNION`

Advanced

Combine results from multiple queries

Syntax:

SELECT ... UNION SELECT ...

Examples:

SELECT name FROM customers UNION SELECT name FROM suppliers; All names from both tables

SELECT 'Customer' as type, name FROM customers UNION SELECT 'Supplier', name FROM suppliers; Combined with type indicator

Notes:

UNION removes duplicates, use UNION ALL to keep them

`CASE`

Advanced

Conditional logic in SQL

Syntax:

CASE WHEN condition THEN result ELSE result END

Examples:

SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END as category FROM users; Categorize by age

SELECT name, CASE status WHEN 'active' THEN 'Active User' WHEN 'inactive' THEN 'Inactive User' ELSE 'Unknown' END FROM users;

Status labels

Notes:

Useful for creating computed columns and conditional logic

`CREATE INDEX`

Performance

Create an index to improve query performance

Syntax:

CREATE INDEX index_name ON table_name (column1, column2)

Examples:

CREATE INDEX idx_email ON users (email); Index on email column

CREATE INDEX idx_name_age ON users (last_name, first_name); Composite index

CREATE UNIQUE INDEX idx_username ON users (username); Unique index

Notes:

Indexes speed up queries but slow down writes

`EXPLAIN`

Performance

Show query execution plan

Syntax:

EXPLAIN SELECT ...

Examples:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com'; Analyze query performance

EXPLAIN ANALYZE SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id; Detailed execution analysis

Notes:

Essential for query optimization and performance tuning

`PRIMARY KEY`

Constraints

Define a primary key constraint

Syntax:

column_name datatype PRIMARY KEY

Examples:

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100)); Single column primary key

CREATE TABLE order_items (order_id INT, product_id INT, PRIMARY KEY (order_id, product_id)); Composite primary key

Notes:

Ensures uniqueness and creates clustered index

`FOREIGN KEY`

Constraints

Define a foreign key constraint

Syntax:

FOREIGN KEY (column) REFERENCES table(column)

Examples:

CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id)); Basic foreign key

ALTER TABLE posts ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; Add foreign key with cascade delete

Notes:

Maintains referential integrity between tables

`UNIQUE`

Constraints

Ensure column values are unique

Syntax:

column_name datatype UNIQUE

Examples:

CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) UNIQUE); Unique email constraint

ALTER TABLE users ADD CONSTRAINT uk_username UNIQUE (username); Add unique constraint

Notes:

Prevents duplicate values in specified columns

`NOT NULL`

Constraints

Ensure column cannot be empty

Syntax:

column_name datatype NOT NULL

Examples:

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100) NOT NULL); Required name field

ALTER TABLE users MODIFY email VARCHAR(255) NOT NULL; Make existing column required

Notes:

Prevents NULL values in specified columns

SQL Best Practices

Query Optimization

Use SELECT with specific columns instead of SELECT *
Always use WHERE clauses to limit results
Create indexes on frequently queried columns
Use LIMIT for large datasets
Avoid functions in WHERE clauses
Use EXPLAIN to analyze query performance

Data Safety

Always backup before running DROP or DELETE
Test queries on small datasets first
Use transactions for multiple related operations
Include WHERE clauses in UPDATE and DELETE
Use appropriate data types and constraints
Normalize your database design