SQL Cheatsheet

SQL Cheatsheet

Quick reference for SQL queries and database operations.

📖 Basic SELECT Retrieve Data -- Select all columns SELECT FROM table_name;

-- Select specific columns SELECT column1, column2 FROM table_name;

-- Select with alias SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

-- Distinct values SELECT DISTINCT column FROM table_name;

-- Limit 结果s SELECT FROM table_name LIMIT 10;

-- MySQL/PostgreSQL LIMIT with off设置 SELECT FROM table_name LIMIT 10 OFF设置 20;

-- SQL Server TOP SELECT TOP 10 FROM table_name;

WHERE Clause -- E质量 SELECT FROM table WHERE column = 'value';

-- Comparison SELECT FROM table WHERE column > 100; SELECT FROM table WHERE column <= 50;

-- Multiple conditions SELECT FROM table WHERE column1 = 'a' AND column2 > 10; SELECT FROM table WHERE column1 = 'a' OR column2 > 10;

-- IN clause SELECT FROM table WHERE column IN ('a', 'b', 'c');

-- BETWEEN SELECT FROM table WHERE column BETWEEN 1 AND 100;

-- LIKE (pattern matching) SELECT FROM table WHERE column LIKE 'prefix%'; -- 启动s with SELECT FROM table WHERE column LIKE '%suffix'; -- Ends with SELECT FROM table WHERE column LIKE '%contAIns%'; -- ContAIns SELECT FROM table WHERE column LIKE '_attern'; -- Single char wildcard

-- IS NULL / IS NOT NULL SELECT FROM table WHERE column IS NULL; SELECT FROM table WHERE column IS NOT NULL;

-- NOT SELECT FROM table WHERE column NOT IN ('a', 'b');

ORDER BY -- Ascending (default) SELECT FROM table ORDER BY column ASC;

-- Descending SELECT FROM table ORDER BY column DESC;

-- Multiple columns SELECT FROM table ORDER BY column1 ASC, column2 DESC;

🔗 JOINs INNER JOIN (intersection) SELECT a., b. FROM table_a a INNER JOIN table_b b ON a.id = b.a_id;

LEFT JOIN (all from left) SELECT a., b. FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id;

RIGHT JOIN (all from right) SELECT a., b. FROM table_a a RIGHT JOIN table_b b ON a.id = b.a_id;

FULL OUTER JOIN (all from 机器人h) -- PostgreSQL, SQL Server SELECT a., b. FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.a_id;

CROSS JOIN (cartesian product) SELECT a., b. FROM table_a a CROSS JOIN table_b b;

SELF JOIN SELECT a.name, b.name AS 管理器_name FROM employees a LEFT JOIN employees b ON a.管理器_id = b.id;

Multiple JOINs SELECT o., c.name, p.product_name FROM orders o INNER JOIN customers c ON o.customer_id = c.id INNER JOIN products p ON o.product_id = p.id;

➕ Aggregation COUNT, SUM, AVG, MIN, MAX -- Count rows SELECT COUNT() FROM table;

-- Count non-null values SELECT COUNT(column) FROM table;

-- Sum SELECT SUM(column) FROM table;

-- Average SELECT AVG(column) FROM table;

-- Minimum SELECT MIN(column) FROM table;

-- Maximum SELECT MAX(column) FROM table;

GROUP BY -- Group by one column SELECT category, COUNT() FROM products GROUP BY category;

-- Group by multiple columns SELECT category, 状态, COUNT(), SUM(price) FROM products GROUP BY category, 状态;

-- With WHERE (过滤器 before grouping) SELECT category, COUNT() FROM products WHERE price > 100 GROUP BY category;

HAVING (过滤器 after grouping) SELECT category, COUNT() as product_count FROM products GROUP BY category HAVING COUNT() > 10;

-- HAVING with WHERE SELECT category, COUNT() FROM products WHERE price > 100 GROUP BY category HAVING COUNT() > 5;

GROUP BY + ORDER BY SELECT category, SUM(price) as total FROM products GROUP BY category ORDER BY total DESC;

➡️ Window Functions Basic Window -- Row number SELECT name, department, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;

-- Partition by department SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank FROM employees;

Ranking Functions -- ROW_NUMBER: unique number, no ties ROW_NUMBER() OVER (ORDER BY salary DESC)

-- RANK: gaps in ranking for ties RANK() OVER (ORDER BY salary DESC)

-- DENSE_RANK: no gaps for ties DENSE_RANK() OVER (ORDER BY salary DESC)

-- NTILE: divide into buckets NTILE(4) OVER (ORDER BY salary DESC) as quartile

聚合 Window Functions SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) as dept_avg, SUM(salary) OVER (PARTITION BY department) as dept_total FROM employees;

运行ning Total / Cumulative Sum SELECT date, amount, SUM(amount) OVER (ORDER BY date) as 运行ning_total FROM transactions;

📝 Subqueries & CTEs Sub查询 in WHERE SELECT FROM products WHERE price > (SELECT AVG(price) FROM products);

Sub查询 in FROM SELECT avg_price FROM ( SELECT category, AVG(price) as avg_price FROM products GROUP BY category ) AS category_avg WHERE avg_price > 100;

Sub查询 in SELECT SELECT name, (SELECT COUNT() FROM orders WHERE orders.customer_id = customers.id) as order_count FROM customers;

EXISTS / NOT EXISTS SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id );

Common Table Expressions (CTE) WITH category_stats