1. 关系代数
关系数据库基于关系代数，核心操作：
| 操作 | SQL 对应 | 说明 |
|---|---|---|
| σ (选择) | WHERE | 按条件过滤行 |
| π (投影) | SELECT | 选择列 |
| ⋈ (连接) | JOIN | 表关联 |
| ∪ (并) | UNION | 合并结果集 |
| − (差) | EXCEPT | 差集 |
| ρ (重命名) | AS | 别名 |
2. SQL 核心语法
-- 条件聚合
SELECT
department,
COUNT(*) AS total_employees,
COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
AVG(salary) FILTER (WHERE status = 'active') AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
-- 窗口函数
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;
-- CTE 递归查询（组织架构）
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.level + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;
延伸阅读
继续阅读
探索更多技术文章
浏览归档，发现更多关于系统设计、工具链和工程实践的内容。