视频封面

📹 视频元信息

  • 时长：约 1 小时 50 分钟（本讲完整内容）
  • 主讲：David J. Malan（CS50）

TL;DR 本讲从 Python 处理 CSV 的笨拙示例出发，引入专门用于数据库查询的语言 SQL。通过真实课堂数据和 IMDb 数据，演示了 CRUD 操作、表设计、主键/外键、JOIN、索引，并讨论了 Python 中调用 SQL 的安全性（SQL 注入、竞态条件）。

1. Introduction & Live Data Collection [00:01:16]

  • 本讲主题是 SQL（Structured Query Language），一种专门用于与数据库对话的编程语言。
  • 理解：Python 虽然通用，但对于某些任务（如数据库查询）并不高效；SQL 作为领域专用语言，能用更少的代码完成相同工作。
  • 讲师现场通过 Google Form 收集数据：两个问题——最喜欢的语言（Scratch / C / Python）和最喜欢的题目。
  • 收集的响应实时进入 Google Sheets，之后可导出为 CSV 文件。
  • 思考：现场收集数据是为了让后续 SQL 示例基于真实数据，增加说服力。

2. Flat-File Databases and CSV [00:04:16]

  • Flat-file database：最简单的数据库形式，通常就是一个文本文件，用约定符号分隔行列。
  • CSV（Comma-Separated Values）：最常用的文本数据库格式，数据按行存储，列之间用逗号分隔。
  • 第一行通常是列名（header），特殊标记为元数据。
  • 示例中，问题集名称 Hello, World 内含逗号，因此必须用双引号包裹来转义（如 "Hello, World"），避免被误认为列分隔符。
  • 理解：CSV 是轻量级、人类可读的格式，但缺少数据类型和约束，不适合复杂应用。

3. Analyzing CSV with Python: favorites.py [00:10:13]

  • 用 Python 的 csv 模块读取 favorites.csv，逐行遍历。
  • 最初使用 csv.reader，通过行索引访问列（如 row[1] 表示语言）。
  • 发现第一行“language”被当作语言输出，原因是读到了表头。用 next(reader) 跳过表头可修复。
  • 改进代码健壮性：改用 csv.DictReader，以列名（如 row["language"]）作为键访问，避免列顺序变化导致代码失效。
  • 统计三种语言的人数：先使用三个独立变量和条件判断，后来改用字典 counts 计数。
  • 理解：counts[favorite] = counts.get(favorite, 0) + 1 可以优雅地处理“键不存在”的情况，避免 KeyError。
  • 排序时 sorted(counts, key=counts.get, reverse=True) 可按计数值从高到低排序。
  • 易错点： Python 中单等号 = 是赋值，双等号 == 是比较。在 SQL 中不同语言有不同约定。
  • 最终得到的 14 行 Python 代码仅仅完成了“统计每种语言人数”这一简单任务，而 SQL 只需一行即可完成。

4. Transition to Relational Databases [00:35:13]

  • Relational database：能够将不同表的数据通过关系（如共享 ID）关联起来的数据库。
  • SQL 的核心功能可以用缩写 CRUD 记忆：
    • Create（创建）
    • Read（读取）
    • Update（更新）
    • Delete（删除）
  • 在 SQL 中，读取数据用 SELECT，创建表用 CREATE TABLE，插入数据用 INSERT，删除数据/表用 DELETE/DROP。
  • 数据存储在 表（table） 中，表由行（row）和列（column）组成，类似于电子表格中的工作表。
  • 思考：关系数据库在存储层面比 CSV 更严谨，可以定义数据类型、约束和关系。

5. SQLite: Importing CSV and Basic Queries [00:37:50]

  • 使用命令行程序 sqlite3 操作数据库。
  • 创建数据库：sqlite3 favorites.db
  • 导入 CSV：在 SQLite 提示符中使用 .mode csv 和 .import favorites.csv favorites。
  • .schema 可查看表结构，SELECT * FROM favorites; 显示全部数据。

5.1 SELECT 与聚合函数

  • SELECT * FROM favorites; —— 选择所有行和列。
  • SELECT language FROM favorites; —— 只选择一列。
  • SELECT COUNT(*) FROM favorites; —— 统计总行数（得到 313）。
  • SELECT DISTINCT language FROM favorites; —— 列出所有不重复的语言。
  • SELECT COUNT(DISTINCT language) FROM favorites; —— 统计不同语言的数量（3）。
  • 常见聚合函数： AVG、COUNT、DISTINCT、LOWER/UPPER、MAX、MIN。

5.2 WHERE 与条件过滤

  • SELECT COUNT(*) FROM favorites WHERE language = 'C'; → 59
  • SELECT COUNT(*) FROM favorites WHERE language = 'C' AND problem = 'Hello, World'; → 8
  • 易错点： SQL 中字符串需要用单引号 ' ' 包裹，而表示表名/列名的符号可以使用双引号或不加引号。
  • 理解：AND 相当于 Python 中的 and，而 C 中是 &&。

5.3 LIKE 与通配符

  • SELECT COUNT(*) FROM favorites WHERE language = 'C' AND problem LIKE 'Hello, %';
  • % 是 SQL 中的通配符，代表零个或多个字符。
  • 推导：LIKE 'Hello, %' 匹配所有以 “Hello, ” 开头的题目，包括 “Hello, World” 和 “Hello, It’s Me”。

5.4 GROUP BY、ORDER BY 与 LIMIT

  • SELECT language, COUNT(*) FROM favorites GROUP BY language; —— 按语言分组统计。
  • ORDER BY COUNT(*) DESC; —— 按统计值降序排序。
  • LIMIT 1; —— 只返回第一行，得到最受欢迎的语言。
  • 可以使用别名：SELECT language, COUNT(*) AS n FROM favorites GROUP BY language ORDER BY n DESC;
  • 理解：这些 SQL 查询只需一行，就替代了此前约 14 行的 Python 代码。

6. Insert, Delete, Update [00:47:00] (approx.)

  • INSERT INTO favorites (language, problem) VALUES ('SQL', 'Fiftyville');
    • 未指定的 timestamp 列自动填充为 NULL，表示“刻意缺失的值”。
  • DELETE FROM favorites WHERE timestamp IS NULL;
    • 易错点： 比较 NULL 不能用 =，必须用 IS NULL 或 IS NOT NULL。
  • UPDATE favorites SET language = 'SQL', problem = 'Fiftyville';
    • 警告： 如果没有 WHERE 子句，会更新所有行，且通常无法撤销。
  • 思考：这些命令强大但有风险，真实环境中必须极其谨慎，尤其在生产数据库上。

7. Real-World Data: IMDb & Database Design [01:00:00] (approx.)

  • 休息后进入大型数据集 IMDb（Internet Movie Database），包含约 23.4 万部电视剧（shows）。
  • 数据原本以 TSV（Tab-Separated Values）格式提供，已预先导入 SQLite 数据库 shows.db。
  • 数据库结构包括六张表：people、stars、shows、writers、genres、ratings。

7.1 为什么需要规范化设计

  • 对比三种存储电视节目和演员的方式：
    1. 把每个演员作为单独列 → 列数不固定，糟糕。
    2. 每行重复节目标题 → 严重数据冗余。
    3. 好设计：用三张表：
      • shows（节目实体）
      • people（人物实体）
      • stars（关系表，存储 show_id 和 person_id）
  • 理解：用整数 ID 代替长字符串，既提高查询效率，也减少存储空间。

7.2 主键、外键与数据约束

  • SHOW .schema 显示：
    • id INTEGER PRIMARY KEY —— 主键唯一标识每一行。
    • title TEXT NOT NULL —— 不允许 NULL 值。
    • year NUMERIC —— 特殊的数值类型。
    • show_id INTEGER NOT NULL —— 在 ratings 表中是外键。
    • FOREIGN KEY (show_id) REFERENCES shows(id) —— 建立与 shows 表的关系。
  • Primary key：本表行的唯一标识符。
  • Foreign key：在其他表中出现的、引用另一表主键的列，用于建立关系。

7.3 关系类型

  • 一对一（one-to-one）：shows 和 ratings 中，每部剧对应一条评分记录。
  • 一对多（one-to-many）：一部剧可以有多个类型（genres）。
  • 多对多（many-to-many）：演员和电视剧之间——一个演员演过多部剧，一部剧也有多位演员，需要 stars 这种中间表。

8. Advanced Queries: Subqueries and JOINs [01:15:00] (approx.)

  • Nested query（子查询）：将一个查询的结果作为另一个查询的输入。
    • 例：查找评分 ≥ 6.0 的剧名： 
      SELECT title FROM shows
WHERE id IN (
    SELECT show_id FROM ratings
    WHERE rating >= 6.0
)
LIMIT 10;
  • JOIN：将多个表按条件合并。
    • SELECT * FROM shows JOIN ratings ON shows.id = ratings.show_id;
  • 易错点： 使用 IN 匹配多个值；使用 = 匹配单个值。
  • 查找《The Office》所有演员的嵌套查询： 
    SELECT name FROM people
WHERE id IN (
    SELECT person_id FROM stars
    WHERE show_id = (
        SELECT id FROM shows
        WHERE title = 'The Office' AND year = 2005
    )
);
  • 查找 Steve Carell 参演的所有剧集，可以使用三重 JOIN： 
    SELECT title FROM shows
JOIN stars ON shows.id = stars.show_id
JOIN people ON stars.person_id = people.id
WHERE name = 'Steve Carell';
  • 理解：子查询是从内向外逐步求解，JOIN 则是把表横向连接后过滤，二者表达力等价。

9. Indexes for Performance [01:25:00] (approx.)

  • .timer on 可显示每条查询耗时。
  • 简单查询耗时 0.043 秒；创建索引后可以降到 0.001 秒。
  • 创建索引命令： 
    CREATE INDEX title_index ON shows(title);
CREATE INDEX name_index ON people(name);
CREATE INDEX person_index ON stars(person_id);
  • 内部实现是 B-tree（多叉平衡树），让查找从线性扫描变成对数级。
  • 权衡： 索引会占用额外空间，也会使插入/更新/删除变慢（需要维护树），但读多写少的系统（如 IMDb）收益远大于成本。
  • 推导：查询速度提升约 40 倍，意味着可以用少得多的服务器支撑相同用户量，显著降低成本。

10. Using SQL in Python [01:35:00] (approx.)

  • 通过 CS50 库在 Python 中使用 SQLite： 
    from cs50 import SQL
db = SQL("sqlite:///favorites.db")
favorite = input("Favorite: ")
rows = db.execute("SELECT COUNT(*) AS n FROM favorites WHERE language = ?", favorite)
print(rows[0]["n"])
  • db.execute 返回一个列表，每个元素是字典（键为列名）。
  • 在 SQL 字符串中使用 ? 作为占位符，第二个参数传入实际值。
  • 易错点： 占位符 ? 必须对应额外参数；忘记传参会导致错误 "more placeholders than values"。
  • 理解：这是“不要用字符串格式化拼接 SQL”的正确方式，可以防止 SQL 注入。

11. Security: Race Conditions & SQL Injection [01:40:00] (approx.)

11.1 Race Condition（竞态条件）

  • 示例：冰箱牛奶问题——两人同时发现没牛奶，又同时去买，结果买了两瓶。
  • 在数据库中，如果多台服务器同时执行“读取当前点赞数→加 1→写回”，可能互相覆盖，导致点赞数偏少。
  • 解决方案：事务（Transactions）
    • BEGIN 开始事务
    • 执行需要的查询和更新
    • COMMIT 提交所有更改
  • 理解：事务保证一系列 SQL 操作要么全部成功、要么全部失败，且执行期间不会被打断，类似“锁住冰箱”的机制。

11.2 SQL Injection（SQL 注入）

  • 恶意用户在输入中插入 SQL 语法（如单引号和 -- 注释符），改变原查询语义。
  • 示例：登录语句 
    SELECT * FROM users WHERE username = 'malan@harvard.edu' --' AND password = '...'
    -- 后的内容被当作注释忽略，因此无需密码即可登录。
  • 不要信任用户输入。
  • 正确防御：使用参数化查询（占位符 ?），让数据库库自动转义特殊字符。
  • 拓展：现代 AI 领域的“prompt injection”与 SQL 注入思路同源，都是通过输入意外指令来劫持系统。
  • 纪念：XKCD 漫画 “Little Bobby Tables” 生动说明了 SQL 注入的危害。

关键标签

SQL · CS50 · 数据库 · SQLite · CRUD · JOIN · 主键外键 · 索引 · Python · CSV · 安全 · SQL注入 · 事务 · 关系数据库

生成时间：2026-09-10 17:30:36 生成平台：VideoNote www.xygy.top