SQL 速查表

SQL 查询和数据库操作的快速参考。

📖 基本 SELECT

检索数据

-- 选择所有列
SELECT * FROM table_name;

-- 选择特定列
SELECT column1, column2 FROM table_name;

-- 使用别名选择
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

-- 去重
SELECT DISTINCT column FROM table_name;

-- 限制结果数量
SELECT * FROM table_name LIMIT 10;

-- MySQL/PostgreSQL 带偏移量的 LIMIT
SELECT * FROM table_name LIMIT 10 OFFSET 20;

-- SQL Server TOP
SELECT TOP 10 * FROM table_name;

WHERE 子句

-- 等于
SELECT * FROM table WHERE column = 'value';

-- 比较
SELECT * FROM table WHERE column > 100;
SELECT * FROM table WHERE column <= 50;

-- 多条件
SELECT * FROM table WHERE column1 = 'a' AND column2 > 10;
SELECT * FROM table WHERE column1 = 'a' OR column2 > 10;

-- IN 子句
SELECT * FROM table WHERE column IN ('a', 'b', 'c');

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

-- LIKE（模式匹配）
SELECT * FROM table WHERE column LIKE 'prefix%';  -- 以...开头
SELECT * FROM table WHERE column LIKE '%suffix';  -- 以...结尾
SELECT * FROM table WHERE column LIKE '%contains%';  -- 包含
SELECT * FROM table WHERE column LIKE '_attern';  -- 单个字符通配

-- 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

-- 升序（默认）
SELECT * FROM table ORDER BY column ASC;

-- 降序
SELECT * FROM table ORDER BY column DESC;

-- 多列排序
SELECT * FROM table ORDER BY column1 ASC, column2 DESC;

🔗 连接（JOIN）

INNER JOIN（交集）

SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id;

LEFT JOIN（左表全部）

SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id;

RIGHT JOIN（右表全部）

SELECT a.*, b.*
FROM table_a a
RIGHT JOIN table_b b ON a.id = b.a_id;

FULL OUTER JOIN（两表全部）

-- PostgreSQL, SQL Server
SELECT a.*, b.*
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id;

CROSS JOIN（笛卡尔积）

SELECT a.*, b.*
FROM table_a a
CROSS JOIN table_b b;

自连接（SELF JOIN）

SELECT a.name, b.name AS manager_name
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;

多表连接

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;

➕ 聚合

COUNT, SUM, AVG, MIN, MAX

-- 统计行数
SELECT COUNT(*) FROM table;

-- 统计非空值数量
SELECT COUNT(column) FROM table;

-- 求和
SELECT SUM(column) FROM table;

-- 平均值
SELECT AVG(column) FROM table;

-- 最小值
SELECT MIN(column) FROM table;

-- 最大值
SELECT MAX(column) FROM table;

GROUP BY

-- 按单列分组
SELECT category, COUNT(*)
FROM products
GROUP BY category;

-- 按多列分组
SELECT category, status, COUNT(*), SUM(price)
FROM products
GROUP BY category, status;

-- 带 WHERE（分组前过滤）
SELECT category, COUNT(*)
FROM products
WHERE price > 100
GROUP BY category;

HAVING（分组后过滤）

SELECT category, COUNT(*) as product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- HAVING 与 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;

➡️ 窗口函数

基本窗口

-- 行号
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- 按部门分区
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

排名函数

-- ROW_NUMBER：唯一编号，无并列
ROW_NUMBER() OVER (ORDER BY salary DESC)

-- RANK：并列时排名有间隔
RANK() OVER (ORDER BY salary DESC)

-- DENSE_RANK：并列时排名无间隔
DENSE_RANK() OVER (ORDER BY salary DESC)

-- NTILE：分桶
NTILE(4) OVER (ORDER BY salary DESC) as quartile

聚合窗口函数

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

运行总计 / 累计和

SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

📝 子查询与 CTE

WHERE 中的子查询

SELECT *
FROM products
WHERE price > (SELECT AVG(price) FROM products);

FROM 中的子查询

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

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
);

公用表表达式（CTE）

WITH category_stats AS (
  SELECT
    category,
    COUNT(*) as count,
    AVG(price) as avg_price
  FROM products
  GROUP BY category
)
SELECT * FROM category_stats
WHERE count > 10;

递归 CTE

WITH RECURSIVE employee_hierarchy AS (
  -- 锚点成员
  SELECT id, name, manager_id, 1 as level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 递归成员
  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

✏️ 修改数据

INSERT

-- Insert single row
INSERT INTO table (column1, column2)
VALUES ('value1', 'value2');

-- Insert multiple rows
INSERT INTO table (column1, column2)
VALUES
  ('value1', 'value2'),
  ('value3', 'value4');

-- Insert from another table
INSERT INTO table2 (column1, column2)
SELECT column1, column2 FROM table1 WHERE condition;

UPDATE

-- Update all rows (careful!)
UPDATE table
SET column1 = 'new_value';

-- Update specific rows
UPDATE table
SET column1 = 'new_value'
WHERE condition;

-- Update multiple columns
UPDATE table
SET column1 = 'value1', column2 = 'value2'
WHERE condition;

-- Update with join (PostgreSQL)
UPDATE products p
SET price = price * 1.1
FROM categories c
WHERE p.category_id = c.id AND c.name = 'Electronics';

DELETE

-- Delete all rows (careful!)
DELETE FROM table;

-- Delete specific rows
DELETE FROM table WHERE condition;

-- Delete with subquery
DELETE FROM products
WHERE category_id NOT IN (SELECT id FROM categories);

TRUNCATE (比 DELETE 清空所有行更快)

TRUNCATE TABLE table_name;

🏗️ 模式操作

CREATE TABLE

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT TRUE
);

-- With foreign key
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

ALTER TABLE

-- Add column
ALTER TABLE table ADD COLUMN new_column VARCHAR(100);

-- Modify column
ALTER TABLE table ALTER COLUMN column TYPE VARCHAR(200);

-- Rename column
ALTER TABLE table RENAME COLUMN old_name TO new_name;

-- Drop column
ALTER TABLE table DROP COLUMN column;

-- Add constraint
ALTER TABLE table ADD CONSTRAINT constraint_name UNIQUE (column);

DROP TABLE

DROP TABLE table_name;

-- Drop if exists
DROP TABLE IF EXISTS table_name;

CREATE INDEX

-- Basic index
CREATE INDEX idx_table_column ON table(column);

-- Composite index
CREATE INDEX idx_table_col1_col2 ON table(column1, column2);

-- Unique index
CREATE UNIQUE INDEX idx_table_column ON table(column);

-- Drop index
DROP INDEX idx_table_column;

💡 实用模式

分页

-- PostgreSQL, MySQL
SELECT * FROM table
ORDER BY id
LIMIT 10 OFFSET 40;  -- Page 5 (10 per page)

-- SQL Server
SELECT * FROM table
ORDER BY id
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY;

每组前 N 条

WITH ranked AS (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

查找重复项

SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > 1;

日期函数

-- Current date/time
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;
SELECT NOW();  -- PostgreSQL

-- Extract parts
SELECT EXTRACT(YEAR FROM date_column) FROM table;
SELECT EXTRACT(MONTH FROM date_column) FROM table;

-- Date arithmetic
SELECT date_column + INTERVAL '7 days' FROM table;
SELECT DATE_ADD(date_column, INTERVAL 7 DAY) FROM table;  -- MySQL

CASE 语句

SELECT
  name,
  price,
  CASE
    WHEN price < 50 THEN 'Budget'
    WHEN price < 100 THEN 'Mid-range'
    ELSE 'Premium'
  END as price_category
FROM products;

COALESCE（处理 NULL）

SELECT COALESCE(column, 'default_value') FROM table;

⚡ 性能提示

1. 使用 EXPLAIN 理解查询执行计划
2. 对 WHERE、JOIN 和 ORDER BY 中使用的列建立索引
3. 避免 SELECT * - 只选择需要的列
4. 使用 LIMIT 预览结果
5. 若不需要去重，优先使用 UNION ALL 而非 UNION
6. 避免在 WHERE 子句中对索引列使用函数
7. 对于大数据集，使用 EXISTS 代替 IN
8. 批量处理大型操作以避免锁

用 EXPLAIN 测试查询：

EXPLAIN SELECT * FROM table WHERE condition;
EXPLAIN ANALYZE SELECT * FROM table WHERE condition;  -- PostgreSQL