SICP_SQL_笔记

2026年1月3日 · 556 字 · 3 分钟

摘要

本文为《计算机程序的构造和解释》（SICP，CS61A 南大引进版）SQL部分课程核心笔记，聚焦程序设计的底层逻辑与核心思想，旨在帮助学习者搭建“知其然更知其所以然”的编程认知框架。适合正在学习 CS61A 课程的学习者梳理知识体系，也可作为编程入门者夯实基础、理解程序设计本质的参考资料。

SQL 数据库查询(Structured Query Language)

注意事项

句末记得加分号!
记得所有的字符串都要加""!
SQL中的中的取等不用==!用=即可!!
审清楚题干!!

主题 1：声明式编程与 SQL 基础 (Declarative Programming & SQL Basics)

主题概述

SQL 是一种声明式语言 (Declarative Language)。与传统的命令式编程（如 Python，需分步指明“怎么做”）不同，声明式编程只需告诉计算机“想要什么”，由解释器决定如何执行。

核心概念

1. 数据库结构 (Database Structure)

表 (Table)：存储数据的核心单元，由固定数量的列 (Columns) 和多行数据条目 (Rows/Records) 组成。
列 (Column)：具有特定的名称和描述。
行 (Row)：为每一列提供具体的值。

2. 创建表

在 SQL 中，可以通过组合已有的数据或从头定义来创建表：

使用 SELECT 创建的是临时数据行,用于展示给用户看而不存储
使用CREATE TABLE <NAME> AS存储数据
使用 UNION 将多个 SELECT 语句连接起来,创建多行。
语法示例：

CREATE TABLE dogs AS
  SELECT "abraham" AS name, "long" AS fur UNION
  SELECT "barack", "short" UNION
  SELECT "clinton", "long";

3. 选择数据 (The SELECT Statement)

最基本的查询结构，用于从表中提取特定数据。

SELECT会做什么?
- 无FROM: 可以制造单行,用逗号将同一行内不同列的内容隔开多用于CREATE中;可以用AS 取名作为一列的列名称
- 有FROM: 从FROM的TABLE中用列名称把一列拿出来(即列名称会被evaluate成为多个行的值)
- SELECE始终决定了列规模
- SELECT * FROM选择全部

语法顺序：SELECT [列名] FROM [表名] WHERE [过滤条件] ORDER BY [排序方式] LIMIT [数量];
WHERE 关键字：用于根据布尔条件过滤行。(输入表的行子集(subset of the rows of the input table))
ORDER BY 关键字：对结果进行排序（ASC 升序，默认；DESC 降序）。
LIMIT关键字: 限制输出行数

4. `SELECT`语句中的算术(Arithmetic)

-> 直接使用运算符号

CREATE TABLE restaurant AS
SELECT 101 AS table, 2 AS single, 2 AS couple UNION
SELECT 102 , 0 , 3 UNION
SELECT 103 , 3 , 1;

sqlite> SELECT table, single + 2 * couple AS 
total FROM restaurant;

主题 2：连接与别名 (Joins & Aliasing)

主题概述

连接 (Join) 允许我们组合多个表的数据。当我们连接两个表时，结果是一个新表，其中包含原始表行之间的每一种可能组合（笛卡尔积）。

核心概念 (Core Concepts)

1. 连接表 (Joining Tables)

语法：在 FROM 子句中列出多个表名，用逗号分隔。

SELECT * FROM parents, dogs WHERE child = name;

2. 别名 (Aliasing)

当连接同一个表两次（Self-join）或多个表有重名列时，必须使用别名。

语法：使用 AS 关键字（可省略）。
作用：消除歧义。
示例：寻找所有兄弟姐妹（拥有共同父母的两个孩子）：

SELECT a.child AS first, b.child AS second
  FROM parents AS a, parents AS b
  WHERE a.parent = b.parent AND a.child < b.child;
*注意：使用 `a.child < b.child` 是为了去除重复项并避免自己与自己配对。*

扩展: 显式连接 -> JOIN ON语句

使用显式语句连接,使表格连接更清晰明了

Why JOIN?

数据库中的表通常是为了消除冗余而拆分的（例如：一张表存学生信息，一张表存班级信息）。连接 (Join) 的目的就是根据两个表之间的公共列（通常是 ID），将分散的数据重新组合成一行。

CS61A 风格: 隐式连接 (Implicit Join)

这是 PPT 中主要演示的方法。它实际上是先做笛卡尔积（Cross Join），然后用 WHERE 过滤。

语法：在 FROM 子句中用逗号分隔表名，在 WHERE 子句中指定连接条件。
示例：

SELECT name, course 
FROM students, enrollments 
WHERE students.sid = enrollments.sid;

现代标准风格: 显式连接 (Explicit JOIN)

这是更推荐的写法，语义更清晰，将“连接逻辑”和“过滤逻辑”分开了。

语法：使用 JOIN 关键字，并用 ON 指定连接条件。
示例：

SELECT name, course 
FROM students 
INNER JOIN enrollments 
ON students.sid = enrollments.sid;

常见的连接类型 (Types of Joins)

为了演示，假设我们有两张表：

Table A (Students): {1: 'Alice', 2: 'Bob', 3: 'Charlie'}
Table B (Grades): {1: 'A', 2: 'B', 4: 'C'} (注意：Charlie 没有成绩，ID 4 的成绩属于不存在的学生)

(1) 内连接 (INNER JOIN)

定义：只保留两个表中都存在匹配关系的行（交集）。
CS61A 写法：FROM A, B WHERE A.id = B.id
标准写法：FROM A INNER JOIN B ON A.id = B.id
结果：Alice (A), Bob (B)。(Charlie 和 ID 4 被丢弃)

(2) 左连接 (LEFT OUTER JOIN)

定义：保留左表（FROM 后的表）的所有行。如果右表没有匹配，右表的列显示为 NULL。
标准写法：FROM A LEFT JOIN B ON A.id = B.id
结果：
Alice - A
Bob - B
Charlie - NULL (因为 Charlie 在左表，但右表没成绩)
注：SQLite 支持 LEFT JOIN，但不支持 RIGHT JOIN。

(3) 交叉连接 (CROSS JOIN / Cartesian Product)

每一行 A 与每一行 B 配对，产生所有可能的组合。

在输出中连接string使用||

主题 3：聚合与分组 (Aggregation & Grouping)

主题概述

聚合函数允许我们对一组行执行操作并返回单个值，通常与分组配合使用。

核心概念 (Core Concepts)

1. 聚合函数 (Aggregate Functions)

COUNT(*)：计算行数。
SUM(col)：求某一列的和。(注:求行的值需要使用多个+运算符)
MIN(col) / MAX(col)：某一列中的最小值/最大值。
AVG(col)：某一列的平均值。

2. 分组查询 (GROUP BY)

GROUP BY <expression>后表达式求值结果相同被化为同一组,表达式中可以使用多个列名和运算符

作用：将结果集划分为多个组，对每个组分别应用聚合函数。
HAVING 过滤：用于过滤组，类似于 WHERE 过滤行。
示例：统计每种毛发类型的狗的数量，且只显示数量大于 1 的组：

SELECT fur, COUNT(*) FROM dogs GROUP BY fur HAVING COUNT(*) > 1;

主题 4：修改表记录 (Modifying Records)

主题概述

除了查询，SQL 还提供了创建空表及增删改查记录的语句。

核心概念 (Core Concepts)

1. 表的生命周期

CREATE TABLE：创建一个没有初始数据的空表。
CREATE TABLE [name]([column_defs]);
DROP TABLE：从数据库中永久删除表。
DROP TABLE [IF EXISTS] [name];

例:

CREATE TABLE parents(parent, child);
CREATE TABLE dogs(name, fur, phrase DEFAULT 'woof');
DROP TABLE dogs;
DROP TABLE IF EXISTS parents;

2. 数据操作 (DML)

INSERT：插入新行。

INSERT INTO [table]([columns]) VALUES([values]), ([values]);

e.g.
INSERT INTO dogs(name, fur) VALUES('fillmore', 'curly');
INSERT INTO dogs VALUES('delano', 'long', 'hi!');
INSERT INTO dogs(fur, phrase) VALUES('curly', 'bark');

UPDATE：修改现有记录。

UPDATE [table] SET [column] = [expression] WHERE [condition];

e.g.
UPDATE dogs SET phrase = 'WOOF' WHERE fur = 'curly';

DELETE：删除现有行。

DELETE FROM [table] WHERE [condition];

e.g.
DELETE FROM dogs WHERE name = 'herbert';

做题技巧

确定输入,输出,一列一列join
有时可创建辅助表来帮助思考(FROM后面加括号,写一串SELECT语句)

SQL 数据库查询(Structured Query Language)

注意事项

主题 1：声明式编程与 SQL 基础 (Declarative Programming & SQL Basics)

主题概述

核心概念

1. 数据库结构 (Database Structure)

2. 创建表

3. 选择数据 (The SELECT Statement)

4. SELECT语句中的算术(Arithmetic)

主题 2：连接与别名 (Joins & Aliasing)

主题概述

核心概念 (Core Concepts)

1. 连接表 (Joining Tables)

2. 别名 (Aliasing)

扩展: 显式连接 -> JOIN ON语句

Why JOIN?

(1) 内连接 (INNER JOIN)

(2) 左连接 (LEFT OUTER JOIN)

(3) 交叉连接 (CROSS JOIN / Cartesian Product)

主题 3：聚合与分组 (Aggregation & Grouping)

主题概述

核心概念 (Core Concepts)

1. 聚合函数 (Aggregate Functions)

2. 分组查询 (GROUP BY)

主题 4：修改表记录 (Modifying Records)

主题概述

核心概念 (Core Concepts)

1. 表的生命周期

2. 数据操作 (DML)

做题技巧

4. `SELECT`语句中的算术(Arithmetic)