sql_cheatsheet

现代 SQL 速查表

本速查表提供了基本和现代 SQL 命令的快速参考，并为实际使用进行了组织。它涵盖了从基本查询到窗口函数和 CTE 等高级功能的所有内容。SQL 是 PostgreSQL、MySQL、SQL Server 和 Oracle 等关系型数据库的标准语言。

1. 基本查询 (DQL)

-- 从表中选择所有列
SELECT * FROM table_name;

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

-- 为列和表使用别名以提高可读性
SELECT c.name AS customer_name, o.order_date
FROM customers AS c;

-- 从列中获取唯一值
SELECT DISTINCT category FROM products;

2. 使用 WHERE 过滤数据

-- 基本过滤
SELECT * FROM users WHERE status = 'active';

-- 组合条件
SELECT * FROM products WHERE price > 100.00 AND in_stock = TRUE;
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Books';

-- IN 运算符用于值列表
SELECT * FROM orders WHERE status IN ('shipped', 'delivered');

-- BETWEEN 运算符用于范围
SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-12-31';

-- LIKE 运算符用于模式匹配（% = 通配符）
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 处理 NULL 值
SELECT * FROM users WHERE phone_number IS NULL;
SELECT * FROM users WHERE phone_number IS NOT NULL;

3. 排序和限制结果

-- 排序结果（ASC 为默认值）
SELECT * FROM products ORDER BY price DESC;

-- 按多列排序
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

-- 限制行数（MySQL/PostgreSQL）
SELECT * FROM articles ORDER BY published_date DESC LIMIT 10;

-- 限制行数（SQL Server）
SELECT TOP 10 * FROM articles ORDER BY published_date DESC;

-- 带偏移量的限制（ANSI SQL 标准）
SELECT * FROM articles ORDER BY published_date DESC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

4. 数据修改 (DML)

-- 插入单行（最佳实践：指定列）
INSERT INTO users (username, email) VALUES ('john_doe', 'john.doe@example.com');

-- 更新现有行（务必使用 WHERE！）
UPDATE products SET price = price * 1.10 WHERE category = 'Imported';

-- 删除行（务必使用 WHERE！）
DELETE FROM logs WHERE log_date < '2022-01-01';

-- 删除表中所有行（比 DELETE 快，无法回滚）
TRUNCATE TABLE staging_data;

5. 聚合和分组

聚合函数：COUNT()、SUM()、AVG()、MIN()、MAX()

-- 计数行
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(phone_number) AS users_with_phone FROM users; -- 忽略 NULL 值

-- 分组数据并应用聚合函数
SELECT category, AVG(price) AS avg_price, COUNT(*) AS num_products
FROM products
GROUP BY category;

-- 使用 HAVING 过滤组
SELECT department, SUM(salary) AS total_payroll
FROM employees
GROUP BY department
HAVING SUM(salary) > 500000;

6. 表连接

-- 内连接：只返回两个表中匹配的行
SELECT u.name, o.order_id FROM users u INNER JOIN orders o ON u.id = o.user_id;

-- 左连接：返回左表的所有行，以及右表中匹配的行
SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接：返回右表的所有行，以及左表中匹配的行
SELECT u.name, o.order_id FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

-- 全外连接：当任一表中有匹配时返回所有行
SELECT u.name, o.order_id FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;

-- 交叉连接：返回两个表的笛卡尔积（所有组合）
SELECT u.name, p.product_name FROM users u CROSS JOIN products p;

7. 数据定义 (DDL)

-- 创建新数据库
CREATE DATABASE new_app_db;

-- 创建带约束的新表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    hire_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 修改表
ALTER TABLE employees ADD COLUMN status VARCHAR(20) DEFAULT 'active';
ALTER TABLE employees DROP COLUMN hire_date;

-- 删除表（删除结构和数据）
DROP TABLE old_employees;

8. 高级 SQL

子查询与公用表表达式 (CTEs)

-- WHERE 子句中的子查询
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE is_active = TRUE);

-- 公用表表达式（CTE）以提高可读性
WITH active_categories AS (
  SELECT id FROM categories WHERE is_active = TRUE
)
SELECT * FROM products WHERE category_id IN (SELECT id FROM active_categories);

窗口函数

-- 按部门内薪资对员工进行排名
SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees;

UNION 与 UNION ALL

-- 组合结果，去除重复项（较慢）
SELECT email FROM customers UNION SELECT email FROM leads;

-- 组合结果，保留所有重复项（较快）
SELECT email FROM customers UNION ALL SELECT email FROM leads;

CASE 语句

-- SELECT 语句中的条件逻辑
SELECT
  order_id,
  total_amount,
  CASE
    WHEN total_amount > 1000 THEN 'High Value'  -- 高价值
    WHEN total_amount > 500  THEN 'Medium Value' -- 中等价值
    ELSE 'Low Value'                            -- 低价值
  END AS order_tier
FROM orders;

9. 事务控制 (TCL)

-- 将语句分组到一个事务中
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 如果所有命令都成功，则保存更改
COMMIT;

-- 如果发生错误，则撤销自 BEGIN 以来所有更改
ROLLBACK;