hav-cs50-merge-08

哈佛 CS50 中文官方笔记（九）

从 Scratch 开始构建

原文：cs50.harvard.edu/scratch/notes/9/

广播

• Scratch 中的一个有用功能将允许我们的精灵之间相互通信。

• 我们将添加我们的猫和恐龙精灵，并将恐龙旋转使其面向猫。

• 现在，我们将让猫向恐龙问候：

   when green flag clicked
    say [Hello, Dinosaur!] for (2) seconds 
  

• 我们还将让恐龙回话，但它应该等待两秒钟：

   when green flag clicked
    wait (2) seconds
    say [Hello, Cat!] for (2) seconds 
  

• 但是现在，如果我们想让猫只问候一秒钟，我们必须记得改变恐龙等待的时间。随着我们交互的增多，或者舞台上精灵的增多，这将会变得更加复杂。

• 结果表明，我们的猫精灵可以使用广播，即发送消息或信号的能力。而我们的恐龙精灵在接收到该消息时会做出响应。

• 在积木的“事件”类别中，我们将使用“广播”积木。我们将使用下拉菜单选择“新消息”，并将其命名为“greet”：

   broadcast (greet v) 
  

• 我们将更改猫的脚本以使用该积木在完成时发送消息：

   when green flag clicked
    say [Hello, Dinosaur!] for (2) seconds
    broadcast (greet v) 
  

• 对于我们的恐龙，我们可以使用“当我收到”积木：

   when I receive [greet v]
    say [Hello, Cat!] for (2) seconds 
  

  • 现在，当我们的猫完成问候后，我们的恐龙总是会做出回应。

控制鸭子

• 让我们在舞台添加一只鸭子以控制控制鸭子。

• 我们可以使用箭头键来控制它，但让我们在舞台上添加两个箭头精灵，并将其中一个旋转使其向上，另一个向下：

  

• 对于指向上方的箭头，我们将告诉它在点击时广播一个“up”的消息：

   when this sprite clicked
    broadcast (up v) 
  

• 对于指向下方的箭头，我们将广播另一个消息，“down”：

   when this sprite clicked
    broadcast (down v) 
  

• 对于我们的鸭子，我们将告诉它根据接收到的消息上下移动：

   when I receive [up v]
    change y by (10)
  
    when I receive [down v]
    change y by (-10) 
  

• 现在，每个箭头精灵在点击时都会广播一个消息，而鸭子在接收到消息时会移动。

访问鱼

• 舞台也可以广播消息，正如我们将在访问鱼中看到的。

• 我们将把背景更改为“水下 1”，并添加一个鱼精灵。

• 现在，我们可以点击右下角的背景，并在代码选项卡中，在事件部分添加“当舞台点击”积木：

   when stage clicked
    broadcast (visit v) 
  

  • 然后，我们将广播一个新的消息，我们将其命名为“visit”。

• 当我们的鱼接收到“visit”消息时，我们将告诉它去我们的鼠标指针：

   when I receive [visit v]
    point towards (mouse-pointer v)
    glide (1) secs to (mouse-pointer v) 
  

  • 现在，点击旗帜后，我们可以在舞台上的任何地方点击，我们的鱼就会移动到那里。

星星

• 让我们看看我们如何能够使用星星克隆或复制一个精灵。

• 使用我们朴素的白色背景和星星精灵，我们将添加以下积木：

   when this sprite clicked
    create clone of (myself v)
  
    when I start as a clone
    glide (1) secs to (random position v) 
  

  • “创建克隆”和“当我作为克隆体开始”积木位于积木的“控制”部分。

  • 现在，每次点击星星时，它都会复制自己。然后，副本将运行“当我作为克隆体开始”下的任何脚本，将自己移动到舞台上的随机位置。

晶石捕捉

• 让我们移除我们的星星，并使用Crystal Catch构建一个完全完整的游戏。

• 我们将从我们的猫开始，尝试构建一个捕捉“Crystal”精灵的游戏。我们最终希望水晶从天空落下，而猫在它们到达地面之前捕捉到它们。

• 首先，我们将添加用于猫左右移动的箭头键的方块：

   when [left arrow v] key pressed
    change x by (-10)
  
    when [right arrow v] key pressed
    change x by (10) 
  

• 然后，我们希望我们的猫在点击绿色旗帜时从舞台中央开始：

   when green flag clicked
    go to x: (0) y: (-125)
    say (Catch the crystals without letting them hit the ground!) for (4) seconds
    broadcast (begin v) 
  

  • 我们还会为我们的用户提供一些说明。

  • 在游戏开始之前，我们应该隐藏水晶，然后创建它的克隆，因为我们希望出现许多水晶。在此之前，我们需要我们的猫广播一条消息，“开始”。

• 现在，我们可以让我们的水晶在游戏开始时隐藏自己，并创建自己的克隆：

   when green flag clicked
    hide
  
    when I receive [begin v]
    create clone of (myself v) 
  

• 然后，当水晶作为一个克隆体开始时，它应该显示自己：

   when I start as a clone
    show
    go to x: (0) y: (160)
    forever
    change y by (-2) 
  

  • 在我们的水晶出现后，它将从舞台顶部中央开始，并不断向下移动，形成一个永无止境的循环。

• 注意，我们一次构建游戏的一个组件，并且我们可以始终启动程序来确保到目前为止我们所做的一切都在工作。

• 接下来，我们的水晶需要检查它是否接触到了猫：

   when I start as a clone
    show
    go to x: (0) y: (160)
    forever
    change y by (-2)
    if <touching (Cat v) ?> then 
  

• 我们将创建一个新的变量“catches”来表示得分，并在猫的脚本中重置它，因为我们也在那里进行其他重置：

   when green flag clicked
    go to x: (0) y: (-125)
    set [catches v] to (0)
    say (Catch the crystals without letting them hit the ground!) for (4) seconds
    broadcast (begin v) 
  

• 现在，我们可以回到我们的水晶脚本，并添加当它接触到我们的猫时需要执行的方块：

   when I start as a clone
    show
    go to x: (0) y: (160)
    forever
    change y by (-2)
    if <touching (Cat v) ?> then
    change [catches v] by (1)
    create clone of (myself v)
    delete this clone 
  

  • 我们需要将“catches”变量增加 1，以跟踪我们的得分。

  • 然后，我们需要创建一个新的水晶，并让原始水晶删除自己。

  • 新的水晶将从屏幕顶部开始，因为它是一个新的克隆。

• 让我们改变水晶位置的 x 值到一个随机的值，这样我们的游戏就有了一些不可预测性。舞台的左右两侧将是很大的数字，所以我们将使用-200 和 200：

   when I start as a clone
    show
    go to x: (pick random (-200) to (200)) y: (160) 
  

  • 现在，我们的水晶每次都会出现在不同的位置。

• 如果我们没有捕捉到水晶，我们可能想要计算到目前为止的失误次数。

• 我们将创建一个新的变量“misses”，它将跟踪我们的猫没有捕捉到水晶的次数。每次游戏开始时，我们将将其重置为 0：

   when green flag clicked
    go to x: (0) y: (-125)
    set [catches v] to (0)
    set [misses v] to (0)
    say (Catch the crystals without letting them hit the ground!) for (4) seconds
    broadcast (begin v) 
  

• 现在，我们可以让我们的水晶检查它是否接触到了地面（或边缘）：

   when I start as a clone
    show
    go to x: (0) y: (160)
    forever
    change y by (-2)
    if <touching (Cat v) ?> then
    change [catches v] by (1)
    create clone of (myself v)
    delete this clone
    end
    if <touching (edge v) ?> then
    change [misses v] by (1)
    create clone of (myself v)
    delete this clone
    end 
  

  • 如果我们的水晶到达边缘，我们将增加“misses”的值，然后创建一个新的克隆体，并删除这个克隆体。

• 我们可能想要限制可以有的失误次数，因此我们可以有一个条件来检查这一点：

   if <touching (edge v) ?> then
    change [misses v] by (1)
    if <(misses) = (3)> then
    broadcast (game over v)
    delete this clone
    end
    create clone of (myself v)
    delete this clone
    end 
  

  • 在我们错过之后，我们将检查数字是否为三。如果是，我们将为我们的猫广播一条消息，然后删除这个克隆体。

• 最后，在我们的猫的脚本中，我们可以说出我们收到消息时的得分：

   when I receive [game over v]
    say (join (Your score is) (catches)) for (5) seconds 
  

• 我们可以尝试这样做，并看到我们的程序按预期工作。我们可以通过让水晶不断克隆自己来一次创建多个水晶：

   when I receive [begin v]
    forever
    create clone of (myself v)
    wait (15) seconds 
  

  • 现在，每 15 秒就会创建一个新的水晶。

• 但是我们注意到，当游戏结束时，新的水晶仍然会继续创建。所以，我们需要让我们的猫在我们的程序中停止一切：

   when I receive [game over v]
    say (join (Your score is) (catches)) for (5) seconds
    stop [all v] 
  

• 通过这些示例，希望你能看到所有这些组件、工具和概念如何被用来在 Scratch 中构建有趣和令人兴奋的项目。

• 我们鼓励你创建一些自己的东西，并与你的朋友、家人和我们分享。感谢你加入我们，一起学习 Scratch 编程入门！

SQL

第六讲

原文：cs50.harvard.edu/sql/notes/0/

• 简介

• 什么是数据库？

• SQL

  • 问题

• SQLite 入门

• 终端技巧

• SELECT

  • 问题

• LIMIT

• WHERE

• NULL

• LIKE

  • 问题

• 范围

  • 问题

• ORDER BY

  • 问题

• 聚合函数

  • 问题

• 结束

简介

• 数据库（和 SQL）是用于交互、存储和管理信息的工具。尽管我们在这个课程中使用的工具是新的，但数据库是一个古老的概念。

• 看一下几千年前的一个图表。它有行和列，似乎包含寺庙工人的津贴。可以称这个图表为一个表，甚至是一个电子表格。

• 根据上图所示，我们可以得出以下结论：

  • 一个表存储了一些信息集（这里，工人的津贴）。

  • 表中的每一行存储该集合中的一个项目（这里，一个工人）。

  • 每一列都有该项目的某个属性（这里，特定月份的津贴）。

• 让我们考虑一个现代的背景。假设你是一名图书管理员，负责组织关于这个图中书籍标题和作者的信息。

  

• 组织信息的一种方式是将每本书的书名后面跟其作者，如下所示。

  

  • 注意，现在每本书都是这个表中的一行。

  • 每一行都有两列——每列都是书籍的不同属性（书名和作者）。

• 在今天的信息时代，我们可以使用像 Google Sheets 这样的软件来存储表格，而不是纸张📝或石板🪨。然而，在这个课程中，我们将讨论数据库而不是电子表格。

• 超越电子表格转向数据库的三个原因是

  • 规模：数据库不仅可以存储数以万计的项目，甚至可以存储数以百万、数十亿计的项目。

  • 更新容量：数据库能够在一秒内处理多个数据更新。

  • 速度：数据库允许更快的信息查找。这是因为数据库为我们提供了访问不同算法以检索信息。相比之下，电子表格只能使用 Ctrl+F 或 Cmd+F 逐个查看搜索结果。

什么是数据库？

• 数据库是一种组织数据的方式，你可以对其执行四个操作

  • create

  • read

  • update

  • delete

• 数据库管理系统（DBMS）是使用图形界面或文本语言与数据库交互的方式。

• 数据库管理系统的例子：MySQL、Oracle、PostgreSQL、SQLite、Microsoft Access、MongoDB 等。

• 选择数据库管理系统会基于以下因素

  • 成本：专有软件与免费软件，

  • 支持量：像 MySQL、PostgreSQL 和 SQLite 这样的免费和开源软件需要你自己设置数据库，这是它们的缺点。

  • 重量：像 MySQL 或 PostgreSQL 这样的功能更全面的系统比 SQLite 这样的系统更重，运行时需要更多的计算资源。

• 在本课程中，我们将从 SQLite 开始，然后转向 MySQL 和 PostgreSQL。

SQL

• SQL 代表结构化查询语言。它是一种用于与数据库交互的语言，通过它可以创建、读取、更新和删除数据库中的数据。关于 SQL 的一些重要注意事项

  • 它是有结构的，正如我们将在本课程中看到的，

  • 它有一些可以用来与数据库交互的关键字，并且

  • 它是一种查询语言——它可以用来对数据库中的数据进行提问。

• 在本课中，我们将学习如何编写一些简单的 SQL 查询。

问题

SQL 有子集吗？

• SQL 是美国国家标准协会（ANSI）和国际标准化组织（ISO）的标准。大多数数据库管理系统都支持 SQL 语言的一些子集。所以，例如，对于 SQLite，我们使用的是 SQLite 支持的 SQL 子集。如果我们想将代码移植到像 MySQL 这样的不同系统，我们可能需要更改一些语法。

SQLite 入门

• 值得注意的是，SQLite 不仅是我们在这个课程中使用的，它还用于许多其他应用程序，包括手机、桌面应用程序和网站。

• 现在，考虑一个包含长期入选 国际布克奖 的书籍的数据库。每年有 13 本书入选，我们的数据库包含了 5 年的此类长期入选名单。

• 在我们开始与这个数据库交互之前：

  • 登录到 CS50 的 Visual Studio Code。这是我们编写代码和编辑文件的地方。

  • SQLite 环境已经在你的 Codespace 中设置好了！在终端中打开它。

终端技巧

这里有一些在终端上编写 SQL 代码的有用技巧。

• 要清除终端屏幕，请按 Ctrl + L。

• 要获取终端中之前执行的指令，请按上箭头键。

• 如果你的 SQL 查询太长，在终端中换行，你可以按回车键并继续在下一行编写查询。

• 要退出数据库或 SQLite 环境，请使用 .quit。

SELECT

• 我们数据库中实际上有什么数据？为了回答这个问题，我们将使用我们的第一个 SQL 关键字 SELECT，它允许我们从数据库表中选择一些（或全部）行。

• 在 SQLite 环境中，运行

  SELECT * 
  FROM "longlist"; 
  

  这将选择名为 longlist 的表中的所有行。

• 我们得到的结果包含表中所有行的所有列，这有很多数据。我们可以通过选择表中的特定列来简化它，比如标题。让我们试试

  SELECT "title" 
  FROM "longlist"; 
  

• 现在，我们看到这个表中标题的列表。但如果我们想在搜索结果中看到标题和作者怎么办？为此，我们运行

  SELECT "title", "author" 
  FROM longlist; 
  

问题

在表和列名周围使用双引号是必要的吗？

• 在表和列名周围使用双引号是一个好习惯，这些被称为 SQL 标识符。SQL 还包含字符串，我们用单引号来包围字符串，以区分它们和标识符。

这个数据库中的数据是从哪里来的？

• 这个数据库包含来自各种来源的数据。

• 长书单（2018-2023 年）来自 Booker Prize 网站。

• 这些书籍的评分和其他信息来自 Goodreads。

我们如何知道数据库中有哪些表和列？

• 数据库模式包含数据库的结构，包括表和列名。在本课程的后期，我们将学习如何获取数据库模式并理解它。

SQLite 3 是区分大小写的吗？为什么查询中的一些部分是大写，而另一些是小写？

• SQLite 不区分大小写。然而，我们确实遵循一些样式约定。观察这个查询：

  SELECT *
  FROM "longlist"; 
  

  SQL 关键字用大写字母书写。这在提高较长的查询的可读性方面特别有用。表和列名用小写字母。

LIMIT

• 如果一个数据库有数百万行，选择所有行可能没有意义。相反，我们可能只想浏览它包含的数据。我们使用 SQL 关键字 LIMIT 来指定查询输出中的行数。

• SELECT "title" 
  FROM "longlist" 
  LIMIT 10; 
  

  这个查询给我们数据库中的前 10 个标题。这些标题在查询输出中的顺序与数据库中的顺序相同。

WHERE

• 关键字 WHERE 用于根据条件选择行；它将输出满足指定条件的行。

• SELECT "title", "author" 
  FROM "longlist" 
  WHERE "year" = 2023; 
  

  这给我们提供了 2023 年长名单书籍的标题和作者。请注意，2023 没有引号，因为它是一个整数，而不是字符串或标识符。

• 可以用于在 SQL 中指定条件的运算符有 =（“等于”）、!=（“不等于”）和 <>（也是“不等于”）。

• 要选择非精装书的书籍，我们可以运行以下查询

  SELECT "title", "format" 
  FROM "longlist" 
  WHERE "format" != 'hardcover'; 
  

  • 注意，hardcover 用单引号，因为它是一个 SQL 字符串，而不是标识符。

• != 可以用运算符 <> 替换以获得相同的结果。修改后的查询将是

  SELECT "title", "format" 
  FROM "longlist" 
  WHERE "format" <> 'hardcover'; 
  

• 获取相同结果的另一种方法是使用 SQL 关键字 NOT。修改后的查询将是

  SELECT "title", "format" 
  FROM "longlist" 
  WHERE NOT "format" = 'hardcover'; 
  

• 要组合条件，我们可以使用 SQL 关键字 AND 和 OR。我们还可以使用括号来指示如何在复合条件语句中组合条件。

• 要选择 2022 年或 2023 年入选的书籍的标题和作者

  SELECT "title", "author" 
  FROM "longlist" 
  WHERE "year" = 2022 OR "year" = 2023; 
  

• 要选择 2022 年或 2023 年入选的非精装书籍

  SELECT "title", "format" 
  FROM "longlist" 
  WHERE ("year" = 2022 OR "year" = 2023) AND "format" != 'hardcover'; 
  

  这里，括号表示应该先评估 OR 子句，然后再评估 AND 子句。

NULL

• 表可能存在缺失数据。NULL 是一种用于表示某些数据没有值或不存在于表中的类型。

• 例如，我们数据库中的书籍都有一个翻译者和一个作者。然而，只有一些书籍被翻译成英文。对于其他书籍，翻译者值将是 NULL。

• 与 NULL 一起使用的条件是 IS NULL 和 IS NOT NULL。

• 要选择没有翻译者的书籍，我们可以运行

  SELECT "title", "translator" 
  FROM "longlist"
   WHERE "translator" IS NULL; 
  

• 让我们尝试反过来：选择那些有翻译者的书籍。

  SELECT "title", "translator" 
  FROM "longlist"
  WHERE "translator" IS NOT NULL; 
  

LIKE

• 此关键字用于选择与指定字符串大致匹配的数据。例如，LIKE 可以用来选择标题中包含特定单词或短语的书籍。

• LIKE 与运算符 %（匹配给定字符串周围的任意字符）和 _（匹配单个字符）结合使用。

• 要选择标题中包含“love”一词的书籍，我们可以运行

  SELECT "title"
  FROM "longlist"
  WHERE "title" LIKE '%love%'; 
  

  % 匹配 0 或多个字符，因此此查询将匹配包含“love”前后有 0 或多个字符的书籍标题——即包含“love”的标题。

• 要选择标题以“The”开头的书籍，我们可以运行

  SELECT "title" 
  FROM "longlist" 
  WHERE "title" LIKE 'The%'; 
  

• 上述查询也可能返回标题以“Their”或“They”开头的书籍。要仅选择标题以“The”开头的书籍，我们可以添加一个空格。

  SELECT "title" 
  FROM "longlist" 
  WHERE "title" LIKE 'The %'; 
  

• 假设表中有一本书的名称是“Pyre”或“Pire”，我们可以通过运行以下命令来选择它

  SELECT "title" 
  FROM "longlist" 
  WHERE "title" LIKE 'P_re'; 
  

  如果我们的数据库中存在像“Pore”或“Pure”这样的书籍标题，此查询也可能返回，因为 _ 匹配任何单个字符。

问题

我们可以在查询中使用多个 % 或 _ 符号吗？

• 是的，我们可以！示例 1：如果我们想选择标题以“The”开头并在中间某处有“love”的书籍，我们可以运行

  SELECT "title" 
  FROM "longlist" 
  WHERE "title" LIKE 'The%love%'; 
  

• 注意：我们当前数据库中没有书籍与这个模式匹配，所以此查询返回空结果。

• 示例 2：如果我们知道表中有一本书的标题以“T”开头并且有四个字母，我们可以尝试通过运行以下命令来找到它

  SELECT "title" 
  FROM "longlist" 
  WHERE "title" LIKE 'T____'; 
  

在 SQL 中字符串比较是否大小写敏感？

• 在 SQLite 中，字符串与 LIKE 的比较默认是大小写不敏感的，而与 = 的比较则是大小写敏感的。（注意，在其他 DBMS 中，数据库的配置可能会改变这一点！）

范围

• 我们也可以在条件中使用运算符 <、>、<= 和 >= 来匹配值范围。例如，要选择 2019 年至 2022 年（包括）之间入选的所有书籍，我们可以运行

  SELECT "title", "author" 
  FROM "longlist" 
  WHERE "year" >= 2019 AND "year" <= 2022; 
  

• 另一种获取相同结果的方法是使用关键字 BETWEEN 和 AND 来指定包含范围。我们可以运行

  SELECT "title", "author" 
  FROM "longlist" 
  WHERE "year" BETWEEN 2019 AND 2022; 
  

• 要选择评分在 4.0 或更高的书籍，我们可以运行

  SELECT "title", "rating" 
  FROM "longlist" 
  WHERE "rating" > 4.0; 
  

• 要进一步通过投票数限制所选书籍，并且只包含至少有 10,000 票的书籍，我们可以运行

  SELECT "title", "rating", "votes" 
  FROM "longlist" 
  WHERE "rating" > 4.0 AND "votes" > 10000; 
  

• 要选择页数少于 300 页的书籍，我们可以运行

  SELECT "title", "pages" 
  FROM "longlist" 
  WHERE "pages" < 300; 
  

问题

对于范围运算符如 < 和 >，数据库中的值必须是整数吗？

• 不，值可以是整数或浮点数（即“十进制”或“实数”）。在创建数据库时，有方法可以为列设置这些数据类型。

ORDER BY

• ORDER BY 关键字允许我们按某种指定的顺序组织返回的行。

• 以下查询按评分从低到高选择我们数据库中的前 10 本书。

  SELECT "title", "rating" 
  FROM "longlist" 
  ORDER BY "rating" LIMIT 10; 
  

• 注意我们得到的是底部的 10 本书，因为 ORDER BY 默认选择升序。

• 相反，要选择前 10 本书

  SELECT "title", "rating" 
  FROM "longlist" 
  ORDER BY "rating" DESC LIMIT 10; 
  

  注意使用 SQL 关键字 DESC 来指定降序。ASC 可以用来显式指定升序。

• 要选择评分最高的前 10 本书，并且将投票数作为平局时的决定因素，我们可以运行

  SELECT "title", "rating", "votes" 
  FROM "longlist"
  ORDER BY "rating" DESC, "votes" DESC 
  LIMIT 10; 
  

  注意到在 ORDER BY 子句中的每一列，我们指定了升序或降序。

问题

按标题字母顺序排序书籍，我们可以使用 ORDER BY 吗？

• 是的，我们可以。查询会是

  SELECT "title" 
  FROM "longlist" 
  ORDER BY "title"; 
  

聚合函数

• COUNT、AVG、MIN、MAX 和 SUM 被称为聚合函数，允许我们对多行数据执行相应的操作。根据它们的本质，以下每个聚合函数都将只返回单个输出——聚合值。

• 查找数据库中所有书籍的平均评分

  SELECT AVG("rating") 
  FROM "longlist"; 
  

• 将平均评分四舍五入到两位小数

  SELECT ROUND(AVG("rating"), 2) 
  FROM "longlist"; 
  

• 重命名显示结果的列

  SELECT ROUND(AVG("rating"), 2) AS "average rating" 
  FROM "longlist"; 
  

  注意使用 SQL 关键字 AS 来重命名列。

• 要选择数据库中的最高评分

  SELECT MAX("rating") 
  FROM "longlist"; 
  

• 要选择数据库中的最低评分

  SELECT MIN("rating") 
  FROM "longlist"; 
  

• 要统计数据库中总票数

  SELECT SUM("votes") 
  FROM "longlist"; 
  

• 统计我们数据库中书籍的数量

   SELECT COUNT(*) 
   FROM "longlist"; 
  

  • 记住我们使用了 * 来选择数据库中的每一行和每一列。在这种情况下，我们正在尝试统计数据库中的每一行，因此我们使用 *。

• 统计翻译者的数量

  SELECT COUNT("translator") 
  FROM "longlist"; 
  

  • 我们观察到翻译者的数量少于数据库中的行数。这是因为 COUNT 函数不会计算 NULL 值。

• 要统计数据库中出版者的数量

  SELECT COUNT("publisher") 
  FROM "longlist"; 
  

• 与翻译者一样，此查询将统计非 NULL 的出版者值的数量。然而，这可能会包括重复值。另一个 SQL 关键字 DISTINCT 可以用来确保只计算不同的值。

  SELECT COUNT(DISTINCT "publisher") 
  FROM "longlist"; 
  

问题

使用标题列的 MAX 会给你最长的书名吗？

• 不，使用标题列的 MAX 会给你“最大”的（在这种情况下，最后的）标题按字母顺序排列。同样，MIN 会给出第一个标题按字母顺序排列。

结束

• 这就带我们来到了关于 SQL 查询的 Lecture 0 的结论！要退出 SQLite 提示符，你可以输入 SQLite 关键字 .quit，这将带你回到常规终端。

• 到下次见面为止！

第一讲

原文：cs50.harvard.edu/sql/notes/1/

• 简介

• 实体关系图

  • 问题

• 键

  • 主键

  • 外键

  • 问题

• 子查询

• IN

  • 问题

• JOIN

  • 问题

• 集合

  • 问题

• 组

  • 问题

• 结束

简介

• 数据库可以有多个表。在上一节课中，我们看到了一个列出国际布克奖提名书籍的数据库。现在我们将看到这个数据库内部有许多不同的表——包括书籍、作者、出版社等。

• 首先，在您的Codespace终端中使用 SQLite 打开数据库。

• 我们可以使用以下 SQLite 命令查看我们数据库中的所有表：

  .tables 
  

  此命令返回longlist.db中的表名——总共 7 个。

• 这些表之间有一些关系，因此我们称数据库为关系数据库。查看longlist.db中的表列表，并尝试想象它们之间的关系。以下是一些例子：

  • 作者写书。

  • 出版社出版书籍。

  • 书籍由翻译者翻译。

• 考虑我们的第一个例子。以下是authors和books表的快照，包括作者姓名和书名列！

  

• 仅从这两列来看，我们如何判断谁写了哪本书？即使我们假设每本书都紧挨着其作者，仅查看authors表也不会提供关于该作者所写书籍的信息。

• 组织书籍和作者的一些可能方式是...

  • 荣誉制度：authors表中的第一行将始终对应于books表中的第一行。这个系统的问题是一个人可能会犯错误（添加了一本书但忘记了添加相应的作者，或者反之）。此外，一个作者可能写过多本书，或者一本书可能由多个作者合著。

  • 回到单表方法：如果一位作者写多本书或者一本书由多个作者合著，这种方法可能会导致冗余（数据重复）。以下是一个包含一些冗余数据的单表方法的快照。

    

• 考虑了这些想法后，似乎有两个不同的表是最有效的方法。让我们看看关系数据库中表之间可以以哪些不同的方式相互关联。

• 考虑这种情况，每位作者只写一本书，每本书也只由一位作者撰写。这被称为一对一关系。

  

• 另一方面，如果一位作者可以写多本书，那么关系是一对多关系。

  

• 这里，我们看到另一种情况，不仅一位作者可以写多本书，而且多本书也可以由多位作者合著。这是一个多对多关系。

  

实体关系图

• 我们刚刚描述了数据库表中一对一、一对多和多对多关系。可以使用实体关系（ER）图来可视化这些关系。

• 这里是longlist.db中表的 ER 图。

  erDiagram
    "Author" }|--|{ "Book" : "wrote"
    "Publisher" ||--|{ "Book" : "published"
    "Translator" }o--|{ "Book" : "translated"
    "Book" ||--o{ "Rating" : "has" 
  

• 每个表都是我们数据库中的一个实体。表与表之间，或实体之间的关系，由标记实体之间线条的动词表示。

• 图中的每条线都使用鸟爪符号表示。

  • 第一行带有圆圈的线条看起来像线上标记的 0。这一行表示没有关系。

  • 第二行带有垂直线的线条看起来像线上标记的 1。具有此箭头的实体必须至少有一个与另一张表中的行相关联的行。

  • 第三行看起来像一只鸟爪，有很多分支。这一行表示该实体与另一张表中的多行相关。

    

• 例如：

  • 我们从左到右阅读这个符号。一位作者写一本书（或者，每位作者都可以有一本书与他们相关联）。

    

  • 现在，不仅一位作者可以写一本书，一本书也可以由一位作者编写。

    

  • 通过这个添加，一位作者至少写一本书，一本书至少由一位作者编写。换句话说，一位作者可以与一本或多本书相关联，一本书可以由一位或多位作者编写。

    

• 让我们重新审视我们数据库的 ER 图。

  erDiagram
    "Author" }|--|{ "Book" : "wrote"
    "Publisher" ||--|{ "Book" : "published"
    "Translator" }o--|{ "Book" : "translated"
    "Book" ||--o{ "Rating" : "has" 
  

• 观察连接书籍和翻译者实体的线条，我们可以说书籍不需要有翻译者。它们可以有零到多个翻译者。然而，数据库中的翻译者至少翻译一本书，可能还翻译多本书。

问题

如果我们有一个数据库，我们如何知道存储在其中的实体之间的关系？

• 实体之间的确切关系完全取决于数据库的设计者。例如，是否每位作者只能写一本书或多本书，这是在设计数据库时需要做出的决定。实体关系图（ER diagram）可以被视为一种工具，用于将这些决定传达给想要了解数据库及其实体之间关系的人。

一旦我们知道某些实体之间存在关系，我们如何在数据库中实现这种关系？

• 我们很快就会看到如何使用 SQL 中的键来关联表。

键

主键

• 在书籍的情况下，每本书都有一个唯一的标识符，称为 ISBN。换句话说，如果你通过 ISBN 搜索一本书，只会找到一本书。在数据库术语中，ISBN 是一个主键——它是表中每个项目的唯一标识符。

  

• 受到 ISBN 这一想法的启发，我们可以想象为我们的出版社、作者和翻译分配唯一的 ID！这些 ID 将是它们所属表的唯一主键。

外键

• 键也有助于在 SQL 中关联表。

• 外键是从另一个表中取出的主键。通过引用另一个表的主键，它通过在它们之间形成链接来帮助关联表。

  

  注意到books表的主键现在成为了ratings表中的一列。这有助于形成两个表之间的一对多关系——一本书（在books表中找到）可以有多个评分（在ratings表中找到）。

• 如我们所见，ISBN 是一个长的标识符。如果每个字符占用一个字节的内存，存储一个单独的 ISBN（包括连字符）将需要 17 个字节的内存，这相当多！

• 幸运的是，我们不一定非得使用 ISBN 作为主键。我们可以简单地使用数字 1、2、3……等等来构建自己的主键，只要每本书都有一个唯一的数字来标识它。

• 之前，我们看到了如何实现books和ratings实体之间的一对多关系。这里有一个多对多关系的例子。

  

现在有一个名为authored的表，它将books表的主键（book_id）映射到authors表的主键（author_id）。

问题

作者和书的 ID 可以相同吗？例如，如果author_id是 1，而authored表中的book_id也是 1，会发生混淆吗？

• 像authored这样的表被称为“联合”或“连接”表。在这样的表中，我们通常知道哪个主键被哪个列引用。在这种情况下，由于我们知道第一列只包含authors的主键，第二列也只包含books的主键，所以即使值匹配也是可以的！

如果我们有很多这样的联合表，那不会占用太多空间吗？

• 是的，这里有一个权衡。像这样的表占用更多空间，但它们也使我们能够拥有许多多对多关系，没有冗余，就像我们之前看到的。

在更改书籍或作者的 ID 时，ID 是否也会在其他表中更新？

• 更新后的 ID 仍然需要是唯一的。鉴于这一点，ID 通常被抽象化，我们很少更改它们。

子查询

• 子查询是另一个查询中的查询。这些也被称为嵌套查询。

• 考虑这个用于一对多关系的示例。在books表中，我们有一个 ID 来表示出版社，这是从publishers表中取的外键。要找出 Fitzcarraldo Editions 出版的书籍，我们需要两个查询——一个是从publishers表中找出 Fitzcarraldo Editions 的publisher_id，第二个是使用这个publisher_id来找出 Fitzcarraldo Editions 出版的所有书籍。这两个查询可以通过子查询的概念合并成一个。

  SELECT "title"
  FROM "books"
  WHERE "publisher_id" = (
      SELECT "id"
      FROM "publishers"
      WHERE "publisher" = 'Fitzcarraldo Editions'
  ); 
  

  注意：

  • 子查询在括号中。括号中最里面的查询将首先运行，然后是外部查询。

  • 内部查询被缩进。这是按照子查询的风格约定进行的，以提高可读性。

• 要找出《记忆的纪念》的所有评分

  SELECT "rating"
  FROM "ratings"
  WHERE "book_id" = (
      SELECT "id"
      FROM "books"
      WHERE "title" = 'In Memory of Memory'
  ); 
  

• 要选择这本书的平均评分

  SELECT AVG("rating")
  FROM "ratings"
  WHERE "book_id" = (
      SELECT "id"
      FROM "books"
      WHERE "title" = 'In Memory of Memory'
  ); 
  

• 下一个示例是用于多对多关系。要找出写了《航班》的作者（们），需要查询三个表：books、authors和authored。

  SELECT "name"
  FROM "authors"
  WHERE "id" = (
      SELECT "author_id"
      FROM "authored"
      WHERE "book_id" = (
        SELECT "id"
        FROM "books"
        WHERE "title" = 'Flights'
      )
  ); 
  

  首先运行的查询是最深层的查询——找到《航班》的 ID。然后，找到写了《航班》的作者（们）的 ID。最后，使用这个 ID 检索作者名称。

IN

• 这个关键字用于检查所需值是否在给定的列表或值集中。

• 作者和书籍之间的关系是多对多的。这意味着一个特定的作者可能写过多本书。要找出数据库中 Fernanda Melchor 所写的所有书籍的名称，我们可以使用以下IN关键字。

  SELECT "title"
  FROM "books"
  WHERE "id" IN (
      SELECT "book_id"
      FROM "authored"
      WHERE "author_id" = (
          SELECT "id"
          FROM "authors"
          WHERE "name" = 'Fernanda Melchor'
      )
  ); 
  

  注意，最内层的查询使用=而不是IN运算符。这是因为我们期望找到名为 Fernanda Melchor 的唯一作者。

问题

如果内部查询的值未找到怎么办？

• 在这种情况下，内部查询将返回空结果，这会促使外部查询也返回空结果。因此，外部查询依赖于内部查询的结果。

需要使用四个空格来缩进子查询吗？

• 不。用于缩进子查询的空格数量可以变化，查询中每行的长度也可以变化。但将查询拆分并缩进子查询的核心思想是使它们易于阅读。

我们如何实现表之间的多对一关系？

• 考虑这种情况，一本书由多个作者共同撰写。我们会有一个 authored 表，对于相同的书 ID 有多个条目。这些条目中的每一个都会有不同的作者 ID。值得注意的是，外键值可以在表中重复，但主键值总是唯一的。

JOIN

• 此关键字允许我们将两个或多个表组合在一起。

• 要了解 JOIN 的工作原理，请考虑海狮及其迁徙模式的数据库。以下是数据库的快照。

  

• 要找出海狮 Spot 走了多远，或者回答有关每只海狮的类似问题，我们可以使用嵌套查询。或者，我们可以将 sea lions 和 migrations 表连接起来，使得每只海狮也有其对应的信息，作为同一行的扩展。

• 我们可以在海狮 ID（两张表之间的共同因素）上连接表，以确保正确的行相互对齐。

• 在测试之前，请确保使用 .quit SQLite 命令退出 longlist.db。然后，打开 sea_lions.db。

• 要连接表

  SELECT *
  FROM "sea_lions"
  JOIN "migrations" ON "migrations"."id" = "sea_lions"."id"; 
  

  注意：

  • ON 关键字用于指定在连接的表中哪些值匹配。如果没有匹配的值，则无法连接表。

  • 如果一个表中有任何 ID 在另一个表中不存在，则该行将不会出现在连接表中。这种连接称为 INNER JOIN。

• 其他允许我们保留某些不匹配 ID 的连接表的方法是 LEFT JOIN、RIGHT JOIN 和 FULL JOIN。这些都是 OUTER JOIN 的一种。

• LEFT JOIN 优先考虑左表（或第一张表）中的数据。

  SELECT *
  FROM "sea_lions"
  LEFT JOIN "migrations" ON "migrations"."id" = "sea_lions"."id"; 
  

  此查询将保留 sea_lions 表中的所有海狮数据——左表。连接表中的某些行可能部分为空。如果右表没有特定 ID 的数据，就会发生这种情况。

• 类似地，RIGHT JOIN 保留右表（或第二张表）的所有行。FULL JOIN 允许我们看到所有表的全部内容。

• 如我们所见，OUTER JOIN 可能会导致连接表中出现空或 NULL 值。

• 海狮数据库中的两张表都有 id 列。由于我们连接表时使用的值在两张表中都有相同的列名，因此实际上在连接时我们可以省略查询的 ON 部分。

  SELECT *
  FROM "sea_lions"
  NATURAL JOIN "migrations"; 
  

  注意，在这种情况下结果中没有重复的 id 列。此外，这种连接与 INNER JOIN 的工作方式类似。

问题

在海狮数据库中，ID 是如何创建的？它们来自 sea_lions 表还是 migrations 表？

• 每只海狮的 ID 很可能是研究人员追踪这些海狮迁徙模式时分配的。也就是说，ID 不是在任一表中生成的，而是在数据本身的源头分配的。

如果我们试图连接三个表，我们如何知道哪一个是左表或右表？

• 对于每个JOIN语句，关键字之前的第一张表是左表。与JOIN关键字相关的是右表。

当我们连接表时，结果连接表会被保存吗？我们可以在不再次连接的情况下稍后引用它吗？

• 在我们使用JOIN的方式中，结果是临时表或结果集。它可以在查询期间使用。

有许多不同的JOIN类型。我们应该使用默认的哪一个？

• 最简单的一种——就是JOIN——实际上是一个INNER JOIN，这也是 SQL 的默认设置。

集合

• 在深入研究集合之前，我们需要退出海狮数据库，切换到longlist.db。

• 在执行查询时，我们看到的查询结果被称为结果集。这是一种 SQL 中的集合。

• 让我们再举一个例子。在我们的书籍数据库中，我们有作者和翻译者。一个人可以是作者或翻译者。如果这两个集合有交集，那么一个人也可能是书籍的作者和翻译者。我们可以使用INTERSECT运算符来找到这个集合。

  

  SELECT "name" FROM "translators"
  INTERSECT
  SELECT "name" FROM "authors"; 
  

• 如果一个人是作者或翻译者，或者两者都是，那么他们属于两个集合的并集。换句话说，这个集合是通过合并作者和翻译者集合形成的。

  

  SELECT "name" FROM "translators"
  UNION
  SELECT "name" FROM "authors"; 
  

  注意，每个作者和每个翻译者都包含在这个结果集中，但只出现一次！

• 对上一个查询进行轻微调整，我们可以根据一个人是作者还是翻译者，在结果集中得到他们的职业。

  SELECT 'author' AS "profession", "name" 
  FROM "authors"
  UNION
  SELECT 'translator' AS "profession", "name" 
  FROM "translators"; 
  

• 以下集合包括了所有既是作者又是仅是作者的人。EXCEPT关键字可以用来找到这样的集合。换句话说，从作者集合中减去翻译者集合，形成这个集合。

  

  SELECT "name" FROM "authors"
  EXCEPT
  SELECT "name" FROM "translators"; 
  

  我们可以验证，交集集中的任何作者-翻译者都没有出现在这个结果集中。

• 同样，我们可以使用EXCEPT来找到只做翻译者的集合。

• 我们如何找到这个集合，其中的人要么是作者或翻译者，但不能两者都是？

  

• 这些运算符可以用来回答许多不同的问题。例如，我们可以找到 Sophie Hughes 和 Margaret Jull Costa 共同翻译的书籍。

  SELECT "book_id" FROM "translated"
  WHERE "translator_id" = (
      SELECT "id" from "translators"
      WHERE "name" = 'Sophie Hughes'
  )
  INTERSECT
  SELECT "book_id" FROM "translated"
  WHERE "translator_id" = (
      SELECT "id" from "translators"
      WHERE "name" = 'Margaret Jull Costa'
  ); 
  

  这里嵌套的每个查询都找到了一个翻译者的书籍 ID。使用 INTERSECT 关键字来交集结果集，并给出他们合作过的书籍。

问题

我们可以使用 INTERSECT、UNION 等操作对 3-4 个集合进行操作吗？

• 是的，绝对可以。要交集 3 个集合，我们必须使用 INTERSECT 操作符两次。一个重要的注意事项——我们必须确保要组合的集合中有相同数量和类型的列。

组

• 考虑到 ratings 表。对于每本书，我们想要找到这本书的平均评分。为此，我们首先需要按书籍将评分分组，然后对每个书籍（每个组）的评分进行平均。

  SELECT "book_id", AVG("rating") AS "average rating"
  FROM "ratings"
  GROUP BY "book_id"; 
  

  在这个查询中，使用了 GROUP BY 关键字为每本书创建组，然后将组的评分合并成一个平均评分！

• 现在，我们只想看到那些评分很高的书籍，平均评分超过 4 分。

  SELECT "book_id", ROUND(AVG("rating"), 2) AS "average rating"
  FROM "ratings"
  GROUP BY "book_id"
  HAVING "average rating" > 4.0; 
  

  注意，这里使用 HAVING 关键字来指定组条件，而不是 WHERE（只能用于指定单个行的条件）。

问题

是否可以看到每本书的评分数量？

• 是的，这需要使用 COUNT 关键字进行轻微的修改。

  SELECT "book_id", COUNT("rating")
  FROM "ratings"
  GROUP BY "book_id"; 
  

是否也可以对这里获得的数据进行排序？

• 是的，可以。比如说，我们想要找到每个评分很高的书籍的平均评分，并按降序排列。

  SELECT "book_id", ROUND(AVG("rating"), 2) AS "average rating"
  FROM "ratings"
  GROUP BY "book_id"
  HAVING "average rating" > 4.0
  ORDER BY "average rating" DESC; 
  

结束

• 这把我们带到了关于关联的第一讲 的结论。

第二讲

原文：cs50.harvard.edu/sql/notes/2/

• 介绍

• 创建数据库模式

  • 规范化

  • 关联

  • 问题

• 创建表

  • 问题

• 数据类型和存储类

• 类型亲和力

• 向我们的表中添加类型

  • 问题

• 表约束

  • 问题

• 列约束

• 修改表

  • 问题

• 鳍

介绍

• 在这次讲座中，我们将学习如何设计我们自己的数据库模式。

• 到目前为止，我们主要使用的是国际布克奖长名单上的书籍数据库。现在，我们将深入内部，看看可以使用哪些命令来创建这样的数据库。

• 首先，让我们在我们的终端上打开第 0 周的数据库 longlist.db。作为提醒，这个数据库只包含一个名为 longlist 的表。要查看表的快照，我们可以运行

  SELECT "author", "title"
  FROM "longlist"
  LIMIT 5; 
  

  这为我们提供了来自表 longlist 的前 5 行的作者和标题。

• 这里是一个 SQLite 命令（不是一个 SQL 关键字），它可以进一步说明这个数据库是如何创建的。

  .schema 
  

  运行此命令后，我们看到用于创建表 longlist 的 SQL 语句。这显示了 longlist 内部的列以及每个列可以存储的数据类型。

• 接下来，让我们在我们的终端上打开第 1 周的相同数据库。这个版本的 longlist.db 包含了相互关联的不同表。

• 再次运行 .schema 后，我们看到许多命令——每个数据库中的表都有一个。有一种方法可以查看指定表的模式：

  .schema books 
  

  现在我们看到用于创建 books 表的语句。我们还能看到每个列的列名和数据类型。例如，"title" 列存储文本，而 "publisher_id" 列是整数。

创建数据库模式

• 现在我们已经看到了现有数据库的模式，让我们创建自己的！我们的任务是使用数据库模式来表示波士顿市的地铁系统。这包括地铁站点、不同的列车线路以及乘坐列车的人们。

  

• 为了进一步分解这个问题，我们需要决定……

  • 我们将在波士顿地铁数据库中有什么类型的表，

  • 每个表将有哪些列，以及

  • 我们应该在每一列中放入哪些类型的数据。

规范化

• 观察这个创建表示波士顿地铁数据的表的初步尝试。这个表包含地铁乘客姓名、乘客当前所在的车站以及在该车站执行的操作（如进入和离开）。它还记录了乘客在地铁卡上的付费金额和余额。这个表还包含每个乘客“交易”的 ID，作为主键。

  

• 这个表中存在哪些冗余？

  • 我们可以选择将乘客姓名分离到一个单独的表中，以避免多次重复名称。我们需要为每个乘客提供一个 ID，以便将新表与这个表关联起来。

  • 我们可以选择将地铁车站移动到不同的表，并为每个地铁车站分配一个 ID，用作这里的外键。

• 以这种方式分离我们的数据的过程称为规范化。在规范化过程中，我们将每个实体放入自己的表中——就像我们对乘客和地铁车站所做的那样。关于特定实体的任何信息，例如乘客的地址，都放入实体的表中。

关联

• 我们现在需要决定我们的实体（乘客和车站）之间的关系。一个乘客可能会访问多个车站，一个地铁站可能有多于一个乘客。鉴于这一点，这将是一个多对多关系。

• 我们也可以使用 ER 图来表示这种关系。

  

  在这里，我们看到每个乘客必须访问至少一个车站才能被认为是乘客。然而，一个车站可能没有乘客访问它，因为这可能是暂时出了故障。然而，一个车站可能有多个乘客访问它，这在 ER 图中用鸟脚符号表示。

问题

乘客和车站之间的关系必须像这里描述的那样精确吗？例如，为什么车站可以有 0 个乘客？

• 设计数据库的人需要决定实体之间的关系。可以添加一个约束，说明一个车站必须至少有一个乘客才能被认为是车站。

CREATE TABLE

• 现在我们已经有了两张表的架构，让我们继续创建这些表。

• 让我们打开一个新的数据库，命名为 mbta.db —— MBTA 代表马萨诸塞湾交通管理局，它运营波士顿地铁。

• 如果我们运行 .schema，我们将看不到任何内容，因为在这个数据库中还没有创建任何表。

• 在这个数据库中，我们运行以下命令来创建第一个乘客表：

  CREATE TABLE riders (
      "id",
      "name"
  ); 
  

  运行此命令后，终端上不会显示任何结果。但如果我们再次运行 .schema，现在我们将看到我们定义的 riders 表的架构！

• 同样，让我们也创建一个车站的表。

  CREATE TABLE stations (
      "id",
      "name",
      "line"
  ); 
  

  在这里，我们添加了一个名为 "line" 的列来存储车站所属的列车线路。

• .schema 现在显示了我们 riders 和 stations 的模式。

• 接下来，我们将创建一个表格来关联这两个实体。这些表格通常被称为连接表、关联实体或连接表！

  CREATE TABLE visits (
      "rider_id",
      "station_id"
  ); 
  

  表的每一行都告诉我们特定骑手访问过的站点。

问题

在 CREATE TABLE 括号内缩进行是必要的吗？

• 不，不是严格意义上的。然而，我们总是缩进列名以遵守样式约定！

数据类型和存储类

• SQLite 有五种存储类：

  • 空值（Null）：无，或空值

  • 整数：没有小数点的数字

  • 实数：小数或浮点数

  • 文本：字符或字符串

  • 二进制大对象（Blob）：用于存储二进制对象（适用于图像、音频等）

• 存储类可以容纳多种数据类型。

• 例如，这些是隶属于整数存储类的数据类型。

  

  SQLite 负责将输入值存储在正确的数据类型下。换句话说，我们作为程序员只需要选择一个存储类，SQLite 就会完成剩下的工作！

• 考虑这个问题：我们会使用哪种存储类来存储票价？每个选择都有其优势和局限性。

  • 整数：我们可以将 10 美分的票价存储为数字 10，但这并不清楚地表明票价是 10 美分还是 10 美元。

  • 文本：我们可以将票价存储为文本，如“$0.10”。然而，现在将很难执行像加起来一个骑手的票价这样的数学运算。

  • 实数：我们可以使用浮点数存储票价，如 0.10，但无法精确地以二进制形式存储浮点数，并且——根据我们需要多精确——这样做可能会导致后续的计算错误。

类型亲和力

• 在创建表时可以指定列的数据类型。

• 然而，SQLite 中的列并不总是存储特定的一种数据类型。它们据说有类型亲和力，这意味着它们试图将输入值转换为它们具有亲和力的类型。

• SQLite 中有五种类型亲和力：文本、数值（基于输入值最佳转换的整数或实数值）、整数、实数和二进制大对象。

• 考虑一个对整数有类型亲和力的列。如果我们尝试将“25”（数字 25 但以文本形式存储）插入到这个列中，它将被转换为整数数据类型。

• 类似地，将整数 25 插入到对文本有类型亲和力的列中，将数字转换为它的文本等价物，“25”。

将类型添加到我们的表中

• 要再次创建我们数据库中的表，我们首先需要删除（或删除）现有的表。

• 让我们尝试以下命令

  DROP TABLE "riders"; 
  

  DROP TABLE "stations"; 
  

  DROP TABLE "visits"; 
  

  运行这些语句没有输出，但 .schema 显示表已经被删除。

• 接下来，让我们创建一个可以运行以从头创建表的架构文件。这比我们之前所做的好，因为我们之前是逐个表地输入CREATE TABLE命令，因为这允许我们轻松地编辑和查看整个架构。

• 创建一个名为schema.sql的文件。注意，扩展名.sql使得我们的编辑器能够对 SQL 关键字进行语法高亮。

• 在文件中，让我们再次输入架构，但这次是带有亲和类型。

  CREATE TABLE riders (
      "id" INTEGER,
      "name" TEXT
  );
  
  CREATE TABLE stations (
      "id" INTEGER,
      "name" TEXT,
      "line" TEXT
  );
  
  CREATE TABLE visits (
      "rider_id" INTEGER,
      "station_id" INTEGER
  ); 
  

• 现在，我们在数据库中读取此文件以实际创建表。这是一个包含数据类型的更新后的 ER 图。

  

问题

之前，我们能够查询数据库中的表，并在类似表格的结构中看到结果。我们如何让相同类型的结果显示在这里？

• 我们还没有向表中添加任何数据。在第三讲中，我们将看到如何在我们创建的表中插入、更新和删除行！

我们对布尔类型有类型亲和力吗？

• 在 SQLite 中我们不做这样的事情，但其他数据库管理系统可能提供这个选项。一种解决方案是使用 0 或 1 的整数值来表示布尔值。

表约束

• 我们可以使用表约束来对表中某些值施加限制。

• 例如，主键列必须具有唯一值。我们用于此的表约束是PRIMARY KEY。

• 类似地，外键值的一个约束是它必须在相关表的主键列中找到！这种表约束，不出所料，被称为FOREIGN KEY。

• 让我们在schema.sql文件中添加主键和外键约束。

  CREATE TABLE riders (
      "id" INTEGER,
      "name" TEXT,
      PRIMARY KEY("id")
  );
  
  CREATE TABLE stations (
      "id" INTEGER,
      "name" TEXT,
      "line" TEXT,
      PRIMARY KEY("id")
  );
  
  CREATE TABLE visits (
      "rider_id" INTEGER,
      "station_id" INTEGER,
      FOREIGN KEY("rider_id") REFERENCES "riders"("id"),
      FOREIGN KEY("station_id") REFERENCES "stations"("id")
  ); 
  

  注意，我们创建了两个主键列，即riders和stations的 ID，然后在visits表中将这些主键作为外键引用。

• 在visits表中，没有主键。然而，SQLite 默认为每个表提供一个主键，称为行 ID。尽管行 ID 是隐式的，但它可以被查询！

• 也可以创建由两列组成的复合主键。例如，如果我们想给visits表创建一个由骑手和站点 ID 组成的复合主键，我们可以使用这种语法。

  CREATE TABLE visits (
      "rider_id" INTEGER,
      "station_id" INTEGER,
      PRIMARY KEY("rider_id", "station_id")
  ); 
  

  在这种情况下，我们可能希望允许骑手访问站点多次，所以我们不会采用这种方法。

问题

我们能否为visits表包含自己的主键？

• 是的！如果出于某种原因，visits表需要显式的主键，我们可以创建一个 ID 列并将其设为主键。

列约束

• 列约束是一种应用于表中指定列的约束类型。

• SQLite 有四种列约束：

  • CHECK：允许检查条件，例如列中的所有值都必须大于 0。

  • DEFAULT：如果为行未提供值，则使用默认值。

  • NOT NULL：规定列中不能插入空或空值。

  • UNIQUE：规定该列中的每个值都必须是唯一的。

• 包含这些约束的更新模式如下所示：

  CREATE TABLE riders (
      "id" INTEGER,
      "name" TEXT,
      PRIMARY KEY("id")
  );
  
  CREATE TABLE stations (
      "id" INTEGER,
      "name" TEXT NOT NULL UNIQUE,
      "line" TEXT NOT NULL,
      PRIMARY KEY("id")
  );
  
  CREATE TABLE visits (
      "rider_id" INTEGER,
      "station_id" INTEGER,
      FOREIGN KEY("rider_id") REFERENCES "riders"("id"),
      FOREIGN KEY("station_id") REFERENCES "stations"("id")
  ); 
  

  NOT NULL 约束确保指定了车站名称和线路。另一方面，乘客不需要共享他们的名字，因为没有对乘客名字应用约束。同样，每个车站必须有一个唯一的名称，这是由 UNIQUE 约束规定的。

• 主键列以及由此派生的外键列必须始终具有唯一值，因此没有必要显式指定 NOT NULL 或 UNIQUE 列约束。表约束 PRIMARY KEY 包含这些列约束。

修改表

• 考虑以下更新的 ER 图，其中实体“Rider”已被新的实体“Card”所取代，用于表示 CharlieCards。在波士顿地铁中，CharlieCards 可以充值并用于进出车站。

  

• 注意，一张卡片可以被滑动多次，但每次只能在一个车站进行。

• “Card” 实体有一个 ID，它也是其主键。

• 现在还有一个名为“Swipe”的实体，它有自己的 ID 和类型。“Swipe”还记录了卡片被滑动的时间和扣除的金额（相当于乘坐地铁所需的金额）！

• 现在，为了在我们的数据库中实施这些更改，我们首先需要删除 riders 表。

  DROP TABLE "riders"; 
  

• 运行 .schema 命令会显示更新后的模式，其中不包括 riders 表。

• 接下来，我们需要一个 swipes 表来表示更新后的 ER 图中的“Swipe”实体。我们可以按以下方式修改 visits 表。

  ALTER TABLE "visits"
  RENAME TO "swipes"; 
  

• 再次运行 .schema 命令，我们可以看到表 visits 已被重命名为 swipes。然而，这并不是唯一需要的更改。我们还需要添加一些列，例如滑动类型。

  ALTER TABLE "swipes"
  ADD COLUMN "swipetype" TEXT; 
  

  注意，在添加此列时也提到了类型亲和力 TEXT。

• 我们还可以在 ALTER TABLE 命令中重命名一个列。如果我们想将列 "swipetype" 重命名为更简洁的名称，可以尝试以下操作。

  ALTER TABLE "swipes"
  RENAME COLUMN "swipetype" TO "type"; 
  

• 最后，我们有能力删除（或移除）一个列。

  ALTER TABLE "swipes"
  DROP COLUMN "type"; 
  

  再次运行 .schema 命令，我们可以确认表中的列 "type" 已被删除。

• 也可以回到最初我们拥有的模式文件 schema.sql，并在那里直接进行这些更改，而不是修改表。以下是一个更新的 schema.sql。

  CREATE TABLE "cards" (
      "id" INTEGER,
      PRIMARY KEY("id")
  );
  
  CREATE TABLE "stations" (
      "id" INTEGER,
      "name" TEXT NOT NULL UNIQUE,
      "line" TEXT NOT NULL,
      PRIMARY KEY("id")
  );
  
  CREATE TABLE "swipes" (
      "id" INTEGER,
      "card_id" INTEGER,
      "station_id" INTEGER,
      "type" TEXT NOT NULL CHECK("type" IN ('enter', 'exit', 'deposit')),
      "datetime" NUMERIC NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "amount" NUMERIC NOT NULL CHECK("amount" != 0),
      PRIMARY KEY("id"),
      FOREIGN KEY("station_id") REFERENCES "stations"("id"),
      FOREIGN KEY("card_id") REFERENCES "cards"("id")
  ); 
  

• 让我们花几分钟时间阅读更新后的模式，并记录下看起来有所变化的地方！

  • cards 和 swipes 表被添加，并使用 NOT NULL 列约束来要求 swipes 中的某些值。

  • "datetime"列被赋予类型亲和力数值型——这是因为数值类型可以存储和显示日期值。

  • 根据需要调整外键映射，使得 "card_id" 是一个外键，引用 cards 表的 ID。

  • "datetime"列被分配了一个默认值，以便在没有提供的情况下自动获取当前的时间戳。注意使用了CURRENT_TIMESTAMP——它返回年、月、日、小时、分钟和秒合并成一个值。

  • 有一个检查确保滑动支付金额不是 0。这是通过列约束CHECK实现的，它与表达式"amount" != 0一起使用，以确保值不是 0。

  • 同样，对"type"也有一个检查，以确保其值是‘enter’、‘exit’和‘deposit’之一。这样做是因为当 CharlieCard 被滑动时，通常是为了这三个目的之一，所以让"type"只假设这些值是有意义的。注意使用了IN关键字来执行这个检查！有没有办法使用OR运算符来实现这个检查？

问题

在尝试删除riders表时，出现了一个错误，因为我们正在使用riders的 ID 作为外键。在这种情况下，如何删除该表呢？

• 在数据库中删除表时，会检查外键约束。在删除riders表之前，我们首先需要删除外键列"rider_id"。

不同数据库管理系统（如 MySQL 或 PostgreSQL）的语法有何不同？

• 大多数 SQLite 语法肯定也适用于其他数据库管理系统。然而，如果我们尝试移植我们的 SQLite 代码，可能需要进行一些最小限度的修改。

如果在 SQLite 中未指定列的类型亲和力，会发生什么？

• 默认的类型亲和力是数值型，因此该列将被分配数值型亲和力。

Fin

• 这就带我们来到了关于 SQL 设计的第二讲的内容总结！关于 CharlieCard 名称起源的一个有趣故事，请阅读来自 Celebrate Boston 的这篇文章。

第三讲

原文：cs50.harvard.edu/sql/notes/3/

• 简介

• 数据库模式

• 插入数据

  • 问题

• 其他约束

• 插入多行

  • 问题

• 删除数据

  • 问题

• 更新数据

• 触发器

  • 创建“卖出”触发器

  • 创建“购买”触发器

  • 问题

• 软删除

• 结束

简介

• 上周，我们学习了如何创建自己的数据库模式。在本讲中，我们将探讨如何在数据库中添加、更新和删除数据。

• 波士顿 MFA（美术博物馆）是波士顿一个拥有一个世纪历史的博物馆。MFA 管理着大量历史和当代艺术品和文物的收藏。他们可能使用某种类型的数据库来存储有关他们的艺术和文物的数据。

• 当一个新的艺术品被添加到他们的收藏中时，我们可以想象他们会将相应的数据插入到他们的数据库中。同样，也存在一些用例，其中可能需要读取、更新或删除数据。

• 现在，我们将专注于在波士顿 MFA 数据库中创建（或插入）数据。

数据库模式

• 考虑到 MFA 可能用于其收藏的此架构。

  

• 每行数据包含一件艺术品的标题以及accession_number，这是博物馆内部使用的唯一 ID。还有一个表示艺术品获取日期的日期。

• 表中包含一个 ID，用作主键。

• 我们可以想象，MFA 的数据库管理员运行一个 SQL 查询，将每一件艺术品插入到表中。

• 为了理解这是如何工作的，让我们首先创建一个名为mfa.db的数据库。接下来，我们将模式文件schema.sql读入数据库。此模式文件已经提供给我们，帮助我们创建collections表。

• 为了确认表已创建，我们可以从表中选择。

  SELECT * FROM "collections"; 
  

  这应该会得到一个空的结果，因为表还没有任何数据。

插入数据

• INSERT INTO SQL 语句用于将一行数据插入到指定的表中。

  INSERT INTO "collections" ("id", "title", "accession_number", "acquired")
  VALUES (1, 'Profusion of flowers', '56.257', '1956-04-12'); 
  

  我们可以看到，这个命令需要指定将接收新数据的表中的列列表以及要添加到每个列中的值，顺序相同。

• 运行INSERT INTO命令不会返回任何内容，但我们可以运行一个查询来确认该行现在已存在于collections表中。

  SELECT * FROM "collections"; 
  

• 我们可以通过多次插入来向数据库添加更多行。然而，手动输入主键值（如 1、2、3 等）可能会导致错误。幸运的是，SQLite 可以自动填充主键值。为了使用此功能，在插入行时我们可以完全省略 ID 列。

  INSERT INTO "collections" ("title", "accession_number", "acquired")
  VALUES ('Farmers working at dawn', '11.6152', '1911-08-03'); 
  

  我们可以通过运行以下命令来检查这一行是否已插入，其id为 2：

  SELECT * FROM "collections"; 
  

  注意 SQLite 填充主键值的方式是通过递增前一个主键值——在这种情况下，是 1。

问题

如果我们删除具有主键 1 的行，SQLite 是否会自动将主键 1 分配给下一个插入的行？

• 不，SQLite 实际上会选择表中最高的主键值并将其递增以生成下一个主键值。

其他约束

• 打开文件schema.sql将显示数据库的模式。

  CREATE TABLE "collections" (
      "id" INTEGER,
      "title" TEXT NOT NULL,
      "accession_number" TEXT NOT NULL UNIQUE,
      "acquired" NUMERIC,
      PRIMARY KEY("id")
  ); 
  

• 规定访问编号必须是唯一的。如果我们尝试插入一个具有重复访问编号的行，将会触发一个看起来像Runtime error: UNIQUE constraint failed: collections.accession_number (19)的错误。

• 这个错误告诉我们，我们正在尝试插入的行违反了模式中的约束——具体来说，在这个场景中是UNIQUE约束。

• 同样，我们可以尝试添加一个具有NULL标题的行，违反了NOT NULL约束。

  INSERT INTO "collections" ("title", "accession_number", "acquired")
  VALUES(NULL, NULL, '1900-01-10'); 
  

  运行此命令后，我们又将看到类似Runtime error: NOT NULL constraint failed: collections.title (19)的错误。

• 以这种方式，模式约束是保护我们免于添加不符合我们数据库模式的行的护栏。

插入多行

• 在向数据库写入时，我们可能需要一次插入多行。一种方法是在INSERT INTO命令中使用逗号分隔行。

  

  以这种方式一次插入多行允许程序员获得一些便利。这同样是一种更快、更高效地将行插入数据库的方法。

• 现在我们将两幅新的画作插入到collections表中。

  INSERT INTO "collections" ("title", "accession_number", "acquired") 
  VALUES 
  ('Imaginative landscape', '56.496', NULL),
  ('Peonies and butterfly', '06.1899', '1906-01-01'); 
  

  博物馆可能并不总是确切知道一幅画是在何时获得的，因此acquired值可能是NULL，正如我们刚刚插入的第一幅画的情况。

• 要查看更新的表，我们可以像往常一样选择表中的所有行。

  SELECT * FROM "collections"; 
  

• 我们的数据也可以以逗号分隔值格式或 CSV 存储。观察以下示例，可以看到每行的值是通过逗号分隔的。

  

• SQLite 使得直接将 CSV 文件导入我们的数据库成为可能。为此，我们需要从头开始。让我们离开这个数据库mfa.db然后将其删除。

• 我们已经有一个名为 mfa.csv 的 CSV 文件，其中包含我们需要的数据。打开这个文件后，我们可以注意到第一行包含列名，这些列名与我们的表 collections 的模式中的列名完全匹配。

• 首先，让我们再次创建数据库 mfa.db 并像之前一样读取模式文件。

• 接下来，我们可以通过运行 SQLite 命令来导入 CSV 文件。

  .import --csv --skip 1 mfa.csv collections 
  

  第一个参数 --csv 告诉 SQLite 我们正在导入一个 CSV 文件。这将帮助 SQLite 正确解析文件。第二个参数表示 CSV 文件的第一个行（标题行）需要被跳过，或者不插入到表中。

• 我们可以通过查询 collections 表来查看所有数据，以确认 mfa.csv 中的每一幅画都已成功导入到表中。

• 我们刚刚插入的 CSV 文件包含了每行数据的唯一键值（1, 2, 3 等）。然而，我们处理的大多数 CSV 文件可能不会包含 ID 或主键值。我们如何让 SQLite 自动插入它们？

• 为了尝试这个方法，让我们在我们的代码空间中打开 mfa.csv 并删除标题行中的 id 列，以及每个列中的值。编辑完成后，mfa.csv 应该看起来像这样：

  title,accession_number,acquired
  Profusion of flowers,56.257,1956-04-12
  Farmers working at dawn,11.6152,1911-08-03
  Spring outing,14.76,1914-01-08
  Imaginative landscape,56.496,
  Peonies and butterfly,06.1899,1906-01-01 
  

• 我们还将删除 collections 表中已经存在的所有行。

  DELETE FROM "collections"; 
  

• 现在，我们想要将这个 CSV 文件导入到一个表中。然而，根据我们的模式，collections 表的每一行都必须有四个列。这个新的 CSV 文件中的每一行只有三个列。因此，我们无法像以前那样继续导入。

• 要成功导入没有 ID 值的 CSV 文件，我们将需要使用一个临时表：

  .import --csv mfa.csv temp 
  

  注意我们在这个命令中没有使用 --skip 1 参数。这是因为 SQLite 能够识别 CSV 数据的第一行作为标题行，并将其转换为新 temp 表的列名。

• 我们可以通过查询 temp 表来查看其中的数据。

  SELECT * FROM "temp"; 
  

• 接下来，我们将从 temp 表中选择数据（不包含主键）并将其移动到 collections 表中，这正是我们的目标！我们可以使用以下命令来实现这一点。

  INSERT INTO "collections" ("title", "accession_number", "acquired") 
  SELECT "title", "accession_number", "acquired" FROM "temp"; 
  

  在此过程中，SQLite 将自动在 id 列中添加主键值。

• 为了清理我们的数据库，我们也可以在移动数据后删除 temp 表。

  DROP TABLE "temp"; 
  

问题

我们能否在插入表时将列放置在特定的位置？

• 虽然我们可以更改 INSERT INTO 命令中值的顺序，但我们通常不能更改列名的顺序。列名的顺序遵循创建表时使用的相同顺序。

如果我们尝试插入的多行中的任意一行违反了表约束，会发生什么？

• 当尝试将多行插入到表中时，如果其中任意一行违反了约束，插入命令将导致错误，并且不会插入任何行！

在从 CSV 文件插入数据后，其中一个单元格为空且不是NULL。为什么会发生这种情况？

• 当我们从 CSV 文件导入数据时，acquired值中的一个缺失了！这被解释为文本，因此被读取到表中作为空文本值。我们可以在导入后运行查询，将这些空值转换为NULL，如果需要的话。

删除数据

• 我们之前看到运行以下命令从collections表中删除了所有行。（我们现在实际上不想运行这个命令，否则我们会丢失表中的所有数据！）

  DELETE FROM "collections"; 
  

• 我们也可以删除符合特定条件的行。例如，要从我们的collections表中删除“春游”画作，我们可以执行以下命令：

  DELETE FROM "collections"
  WHERE "title" = 'Spring outing'; 
  

• 要删除任何获得日期为NULL的画作，我们可以执行以下命令：

  DELETE FROM "collections"
  WHERE "acquired" IS NULL; 
  

• 和我们通常做的那样，我们将通过从表中选择所有数据来确保删除操作按预期工作。

  SELECT * FROM "collections"; 
  

  我们看到“春游”和“想象风景”画作不再在表中。

• 要删除 1909 年之前的画作相关行，我们可以执行以下命令：

  DELETE FROM "collections"
  WHERE "acquired" < '1909-01-01'; 
  

  使用<运算符，我们正在查找 1909 年 1 月 1 日之前获得的画作。这些是在运行查询时将被删除的画作。

• 可能存在删除某些数据会影响数据库完整性的情况。外键约束是一个很好的例子。外键列引用不同表的主键。如果我们删除主键，外键列将没有任何可引用的内容！

• 现在考虑 MFA 数据库的更新模式，它不仅包含关于艺术品的信息，还包含艺术家信息。艺术家和收藏两个实体之间存在多对多关系——一幅画可以由许多艺术家创作，而单个艺术家也可以创作许多艺术品。

  

• 这里是一个实现上述 ER 图的数据库。

  

  artists 和 collections 表具有主键——ID 列。created 表通过其两个外键列引用这些 ID。

• 给定这个数据库，如果我们选择删除未知的艺术家（ID 为 3），那么created表中具有artist_id为 3 的行会发生什么？让我们试一试。

• 在打开mfa.db后，现在我们可以通过运行.schema命令来查看更新的模式。created表确实有两个外键约束，一个针对艺术家 ID，一个针对收藏 ID。

• 现在，我们可以尝试从artists表中删除数据。

  DELETE FROM "artists"
  WHERE "name" = 'Unidentified artist'; 
  

  在运行此命令时，我们得到一个与我们在本课程中之前看到的非常相似的错误：运行时错误：外键约束失败（19）。这个错误通知我们，删除这些数据将违反在created表中设置的外键约束。

• 我们如何确保约束不被违反？一种可能性是在从artists表删除之前，先从created表中删除相应的行。

  DELETE FROM "created"
  WHERE "artist_id" = (
      SELECT "id"
      FROM "artists"
      WHERE "name" = 'Unidentified artist'
  ); 
  

  这个查询有效地删除了艺术家与其作品之间的关联。一旦关联不再存在，我们就可以在不违反外键约束的情况下删除艺术家的数据。为此，我们可以运行

  DELETE FROM "artists"
  WHERE "name" = 'Unidentified artist'; 
  

• 在另一种可能性中，我们可以指定当通过外键引用的 ID 被删除时采取的操作。为此，我们使用关键字ON DELETE后跟要执行的操作。

  • ON DELETE RESTRICT：这限制我们在外键约束违反时删除 ID。

  • ON DELETE NO ACTION：这允许删除由外键引用的 ID，但不会发生任何操作。

  • ON DELETE SET NULL：这允许删除由外键引用的 ID，并将外键引用设置为NULL。

  • ON DELETE SET DEFAULT：这与之前的行为相同，但允许我们设置默认值而不是NULL。

  • ON DELETE CASCADE：这允许删除由外键引用的 ID，并继续级联删除引用的外键行。例如，如果我们使用此方法删除艺术家 ID，所有艺术家与艺术品的关联也会从created表中删除。

• 最新版本的架构文件实现了上述方法。外键约束现在看起来像

  FOREIGN KEY("artist_id") REFERENCES "artists"("id") ON DELETE CASCADE
  FOREIGN KEY("collection_id") REFERENCES "collections"("id") ON DELETE CASCADE 
  

  现在运行以下DELETE语句不会导致错误，并将级联删除从artists表传播到created表：

  DELETE FROM "artists"
  WHERE "name" = 'Unidentified artist'; 
  

  要检查级联删除是否工作，我们可以查询created表：

  SELECT * FROM "created"; 
  

  我们观察到没有行具有 ID 3（从artists表中删除的艺术家 ID）。

问题

我们刚刚删除了 ID 为 3 的艺术家。有没有办法让下一个插入的行具有 ID 3？

• 默认情况下，正如我们之前讨论的，SQLite 将选择表中存在的最大 ID 并递增以获得下一个 ID。但我们在创建列时可以使用AUTOINCREMENT关键字来指示任何被删除的 ID 应重新用于表中插入的新行。

更新数据

• 我们可以轻松想象出数据库中的数据需要更新的场景。也许，在 MFA 数据库的案例中，我们发现原本映射到“未知的艺术家”的画作“黎明时分劳作的农民”实际上是由艺术家李银创作的。

• 我们可以使用更新命令来更改，比如说，一幅画的关系。以下是更新命令的语法。

  

• 让我们使用上述语法在created表中更改“黎明时分劳作的农民”的关联。

  UPDATE "created"
  SET "artist_id" = (
      SELECT "id"
      FROM "artists"
      WHERE "name" = 'Li Yin'
  )
  WHERE "collection_id" = (
      SELECT "id"
      FROM "collections"
      WHERE "title" = 'Farmers working at dawn'
  ); 
  

  查询的第一部分指定了要更新的表。下一部分检索李因的 ID 以设置为新的 ID。最后一部分选择 created 中的行（多行），这些行将更新为李因的 ID，即画作“黎明时分劳作的农民”！

触发器

• 触发器是一个 SQL 语句，在响应另一个 SQL 语句（如 INSERT、UPDATE 或 DELETE）时自动运行。

• 触发器对于维护数据一致性和在相关表之间自动化任务非常有用。

创建“销售”触发器

• 考虑包含一个 collections 表和一个新的 transactions 表的 MFA 数据库。

  CREATE TABLE "transactions" (
      "id" INTEGER,
      "title" TEXT,
      "action" TEXT,
      PRIMARY KEY("id")
  ); 
  

• 当艺术品被出售（从 collections 中删除）时，我们希望它自动在 transactions 中记录为“销售”动作。

  CREATE TRIGGER "sell" 
  BEFORE DELETE ON "collections"
  BEGIN
      INSERT INTO "transactions" ("title", "action")
      VALUES (OLD."title", 'sold');
  END; 
  

• 这个触发器在从 collections 中删除行之前运行。

• OLD 是一个特殊的关键字，它指的是即将被删除的行。

• OLD."title" 访问即将被删除的行的标题列。

• 触发器自动在 transactions 中插入一条记录，动作标记为“销售”。

创建“购买”触发器

• 当艺术品被购买（插入到 collections）时，我们希望它在 transactions 中记录为“购买”动作。

  CREATE TRIGGER "buy" 
  AFTER INSERT ON "collections"
  BEGIN
      INSERT INTO "transactions" ("title", "action")
      VALUES (NEW."title", 'bought');
  END; 
  

• 这个触发器在 collections 中插入新行之后运行。

• NEW 是一个特殊的关键字，它指的是正在插入的行。

• NEW."title" 访问新插入行的标题列。

问题

触发器内可以包含多个 SQL 语句吗？

• 是的，你可以在 BEGIN 和 END 块内包含多个语句，用分号分隔。

软删除

• 软删除（或软删除）意味着将数据标记为已删除，而不是真正从数据库中移除它。

• 例如，我们可以在 collections 表中添加一个 deleted 列，默认值为 0：

  ALTER TABLE "collections"
  ADD COLUMN "deleted" INTEGER DEFAULT 0; 
  

• 要“删除”一行，我们会更新 deleted 列为 1：

  UPDATE "collections"
  SET "deleted" = 1
  WHERE "title" = 'Farmers working at dawn'; 
  

• 然后，为了查询仅非删除行：

  SELECT * FROM "collections"
  WHERE "deleted" != 1; 
  

• 这样，如果需要的话，数据可以被恢复，并且保持完整的历史记录。

• 然而，遵守要求数据真正被删除的数据隐私法规仍然很重要。

结束

• 这就带我们来到了关于 SQL 写作的第三讲结束！

第四讲

原文：cs50.harvard.edu/sql/notes/4/

• 介绍

• 视图

• 简化

  • 问题

• 聚合

  • 问题

• 公用表表达式（CTE）

• 分区

  • 问题

• 安全

• 软删除

• 结束

介绍

• 到目前为止，我们已经学习了允许我们设计复杂数据库并将数据写入其中的概念。现在，我们将探讨从这些数据库中获取视图的方法。

• 让我们回到包含国际布克奖长名单书籍的数据库。以下是该数据库中表的快照。

  

• 要找到韩江（Han Kang）所著的书籍，我们需要遍历上述三个表中的每一个——首先找到作者的 ID，然后相应的书籍 ID，最后是书籍标题。相反，有没有一种方法可以将三个表中的相关信息组合成一个视图？

• 是的，我们可以使用 SQL 中的JOIN命令根据它们之间的相关列将两个或多个表中的行组合起来。以下是这些表如何连接以对齐作者及其书籍的视觉表示。

  

  这使得观察出韩江（Han Kang）是《白书》的作者变得简单。

• 也可以想象在这里删除 ID 列，这样我们的视图看起来就像下面这样。

  

视图

• 视图是由查询定义的虚拟表。

• 假设我们编写了一个查询来连接三个表，就像之前的例子一样，然后选择相关列。由这个查询创建的新表可以保存为视图，以便稍后进一步查询。

• 视图对于以下用途很有用：

  • 简化：将来自不同表的数据组合起来以便更简单地查询，

  • 聚合：运行聚合函数，如求和，并存储结果，

  • 分区：将数据划分为逻辑部分，

  • 安全：隐藏应保持安全的列。虽然视图还有其他有用的方式，但在本讲中，我们将关注上述四个方面。

简化

• 让我们在 SQLite 中打开longlist.db并运行.schema命令来验证我们之前示例中看到的三个表是否已创建：authors、authored和books。

• 要选择 Fernanda Melchor 所著的书籍，我们会编写这个嵌套查询。

  SELECT "title" FROM "books"
  WHERE "id" IN (
      SELECT "book_id" FROM "authored"
      WHERE "author_id" = (
          SELECT "id" FROM "authors"
          WHERE "name" = 'Fernanda Melchor'
      )
  ); 
  

• 上述查询很复杂——嵌套查询中有三个SELECT查询。为了简化，让我们首先使用JOIN创建包含作者及其书籍的视图。

• 在新的终端中，让我们再次连接到longlist.db，并运行以下查询。

  SELECT "name", "title" FROM "authors"
  JOIN "authored" ON "authors"."id" = "authored"."author_id"
  JOIN "books" ON "books"."id" = "authored"."book_id"; 
  

  • 注意，指定如何连接两个表，或者它们连接的列是很重要的。

  • 小贴士：一个表的主键列通常与另一个表的对应外键列相连接！

  • 运行此命令将显示一个包含所有作者姓名及其所写书籍标题的表格。

• 要将之前步骤中创建的虚拟表保存为视图，我们需要更改查询。

  CREATE VIEW "longlist" AS
  SELECT "name", "title" FROM "authors"
  JOIN "authored" ON "authors"."id" = "authored"."author_id"
  JOIN "books" ON "books"."id" = "authored"."book_id"; 
  

  这里创建的视图称为longlist。现在我们可以像使用 SQL 中的表一样使用这个视图。

• 让我们编写一个查询来查看这个视图中的所有数据。

  SELECT * FROM "longlist"; 
  

• 使用这个视图，我们可以大大简化查找 Fernanda Melchor 所写书籍所需的查询。

  SELECT "title" FROM "longlist" WHERE "name" = 'Fernanda Melchor'; 
  

• 视图作为一个虚拟表，创建时不会消耗更多的磁盘空间。视图中的数据仍然存储在底层表中，但仍然可以通过这个简化的视图访问。

问题

我们能否操纵视图以使其有序，或者以不同的方式显示？

• 是的，我们可以像在表中一样在视图中对书籍进行排序。

  • 例如，让我们按书籍标题的顺序显示longlist视图中的数据。

    SELECT "name", "title"
    FROM  "longlist"
    ORDER BY "title"; 
    

  • 我们也可以让视图本身有序。我们可以通过在创建视图所用的查询中包含一个ORDER BY子句来实现这一点。

聚合

• 在longlist.db中，我们有一个包含每本书单独评分的表。在之前的几周中，我们看到了如何找到每本书的平均评分，并四舍五入到两位小数。

  SELECT "book_id", ROUND(AVG("rating"), 2) AS "rating" 
  FROM "ratings"
  GROUP BY "book_id"; 
  

• 通过显示每本书的标题，以及每本书被列入长名单的年份，可以使上述查询的结果更有用。这些信息存在于books表中。

  SELECT "book_id", "title", "year", ROUND(AVG("rating"), 2) AS "rating" 
  FROM "ratings"
  JOIN "books" ON "ratings"."book_id" = "books"."id"
  GROUP BY "book_id"; 
  

  • 在这里，我们使用JOIN将ratings和books表中的信息结合起来，通过书籍 ID 列进行连接。

  • 注意这个查询的操作顺序——特别是将GROUP BY操作放在查询末尾，在两个表连接之后。

• 这聚合的数据可以存储在视图中。

  CREATE VIEW "average_book_ratings" AS
  SELECT "book_id" AS "id", "title", "year", ROUND(AVG("rating"), 2) AS "rating" 
  FROM "ratings"
  JOIN "books" ON "ratings"."book_id" = "books"."id"
  GROUP BY "book_id"; 
  

  • 现在，让我们查看这个视图中的数据。

    SELECT * FROM "average_book_ratings"; 
    

• 在向ratings表添加更多数据以获取最新的聚合数据时，我们只需简单地使用上述类似的SELECT命令重新查询视图即可！

• 每次创建视图时，它都会被添加到模式中。我们可以通过运行.schema来验证这一点，观察longlist和average_book_ratings现在已经成为这个数据库模式的一部分。

• 要创建不存储在数据库模式中的临时视图，我们可以使用CREATE TEMPORARY VIEW。此命令创建一个仅在数据库连接期间存在的视图。

• 要找到每本书的年度平均评分，我们可以使用我们已创建的视图。

  SELECT "year", ROUND(AVG("rating"), 2) AS "rating" 
  FROM "average_book_ratings" 
  GROUP BY "year"; 
  

  注意，我们从 average_book_ratings 中选择了 rating 列，该列已经包含了每本书的平均评分。接下来，我们按年份对这些评分进行分组，并再次计算平均评分，这样就得到了每年的平均评分！

• 我们可以将结果存储在一个临时视图中。

  CREATE TEMPORARY VIEW "average_ratings_by_year" AS
  SELECT "year", ROUND(AVG("rating"), 2) AS "rating" FROM "average_book_ratings" 
  GROUP BY "year"; 
  

问题

可以使用临时视图来测试查询是否有效吗？

• 是的，这是一个临时视图的绝佳用例！为了稍微概括一下，当我们想要以某种方式组织数据而不需要长期存储这种组织时，我们会使用临时视图。

公用表表达式（CTE）

• 正规视图在我们数据库模式中永久存在。临时视图在我们与数据库的连接期间存在。CTE 是仅对单个查询存在的视图。

• 让我们使用公用表表达式（CTE）而不是临时视图来重新创建包含每年平均书籍评分的视图。首先，我们需要删除现有的临时视图，这样我们就可以重用名称 average_book_ratings。

  DROP VIEW "average_book_ratings"; 
  

• 接下来，我们创建一个包含每本书平均评分的 CTE。然后，我们使用每本书的平均评分来计算每年的平均评分，这与我们之前的方法非常相似。

  WITH "average_book_ratings" AS (
      SELECT "book_id", "title", "year", ROUND(AVG("rating"), 2) AS "rating" FROM "ratings"
      JOIN "books" ON "ratings"."book_id" = "books"."id"
      GROUP BY "book_id"
  )
  SELECT "year" ROUND(AVG("rating"), 2) AS "rating" FROM "average_book_ratings"
  GROUP BY "year"; 
  

分区

• 视图可以用来分区数据，或者将其分解成对我们或应用程序有用的更小的部分。例如，国际布克奖的网站为每次获奖的年份都有一个入选书籍的页面。然而，我们的数据库将所有入选的书籍存储在一个单独的表中。为了创建网站或其他目的，可能需要为每年的书籍创建不同的表（或视图）。

• 让我们创建一个视图来存储 2022 年入选的书籍。

  CREATE VIEW "2022" AS
  SELECT "id", "title" FROM "books"
  WHERE "year" = 2022; 
  

  • 我们也可以在这个视图中查看数据。

    SELECT * FROM "2022"; 
    

问题

视图可以更新吗？

• 不可以，因为视图不像表那样包含任何数据。视图实际上在每次查询时都会从底层表中提取数据。这意味着当底层表被更新时，下一次查询视图时，它将显示来自表的新数据！

安全

• 视图可以通过限制对某些数据的访问来增强数据库的安全性。

• 考虑一个共享出行公司的数据库，其中有一个名为 rides 的表，其结构如下。

  

• 如果我们将这些数据提供给分析师，他们的工作是找出最受欢迎的骑行路线，那么提供个别乘客的姓名将是不相关的，实际上也是不安全的。乘客姓名可能被归类为个人信息（PII），公司不允许无差别地共享这些信息。

• 在这种情况下，视图可以派上用场——我们可以与分析师分享一个包含骑行起点和目的地的视图，但不包含乘客姓名。

• 为了尝试这个，让我们在我们的终端中打开 rideshare.db。运行 .schema 应该会揭示这个数据库中的一个名为 rides 的表。

• 我们可以创建一个包含相关列的视图，同时完全省略rider列。但在这里，我们将更进一步，创建一个rider列来显示表中每行的匿名骑手。这将向分析师表明，尽管我们在数据库中有骑手姓名，但这些姓名为了安全起见已被匿名化。

  CREATE VIEW "analysis" AS
  SELECT "id", "origin", "destination", 'Anonymous' AS "rider" 
  FROM "rides"; 
  

  • 我们可以查询这个视图来确保它是安全的。

    SELECT * FROM "analysis"; 
    

• 尽管我们可以创建一个匿名化数据的视图，但 SQLite 不允许访问控制。这意味着我们的分析师可以简单地查询原始的rides表，并看到我们在analysis视图中费尽心思省略的所有骑手姓名。

软删除

• 正如我们在前几周看到的，软删除涉及将行标记为已删除，而不是从表中删除它。

• 例如，名为“黎明时分劳作的农民”的艺术品通过将collections表中的deleted列的值从 0 更改为 1 被标记为已删除。

  

• 我们可以想象创建一个视图来仅显示未删除的艺术品。

• 要尝试这个，让我们在我们的终端中打开mfa.db。collections表还没有deleted列，所以我们需要添加它。这里的默认值将是 0，以表示该行未被删除。

  ALTER TABLE "collections" 
  ADD COLUMN "deleted" INTEGER DEFAULT 0; 
  

• 现在，让我们对艺术品“黎明时分劳作的农民”执行软删除，通过将其deleted列更新为 1。

  UPDATE "collections" 
  SET "deleted" = 1 
  WHERE "title" = 'Farmers working at dawn'; 
  

• 我们可以创建一个视图来显示未删除行的信息。

  CREATE VIEW "current_collections" AS
  SELECT "id", "title", "accession_number", "acquired" 
  FROM "collections" 
  WHERE "deleted" = 0; 
  

  • 我们可以显示这个视图中的数据来验证“黎明时分劳作的农民”不存在。

    SELECT * FROM "current_collections"; 
    

  • 在从底层表collections中软删除行后，它将在任何进一步的查询中从current_collections视图中被移除。

• 我们已经知道无法向视图中插入数据或从视图中删除数据。然而，我们可以设置一个触发器来向底层表插入或删除数据！INSTEAD OF触发器允许我们这样做。

  CREATE TRIGGER "delete"
  INSTEAD OF DELETE ON "current_collections"
  FOR EACH ROW
  BEGIN
      UPDATE "collections" SET "deleted" = 1 
      WHERE "id" = OLD."id";
  END; 
  

  • 每次我们尝试从视图中删除行时，这个触发器将更新底层表collections中行的deleted列，从而完成软删除。

  • 我们在更新子句中使用关键字OLD来表示在collections中更新的行的 ID 应该与我们要从current_collections中删除的行的 ID 相同。

• 现在，我们可以从current_collections视图中删除一行。

  DELETE FROM "current_collections" 
  WHERE "title" = 'Imaginative landscape'; 
  

  我们可以通过查询视图来验证这是否有效。

  SELECT * FROM "current_collections"; 
  

• 类似地，我们可以创建一个触发器，在我们尝试将数据插入视图时将其插入到底层表中。

• 这里有两个需要考虑的情况。我们可能试图将已存在于底层表中的、但已被软删除的行插入到视图中。我们可以编写以下触发器来处理这种情况。

  CREATE TRIGGER "insert_when_exists"
  INSTEAD OF INSERT ON "current_collections"
  FOR EACH ROW 
  WHEN NEW."accession_number" IN (
      SELECT "accession_number" FROM "collections"
  )
  BEGIN
      UPDATE "collections" 
      SET "deleted" = 0 
      WHERE "accession_number" = NEW."accession_number";
  END; 
  

  • WHEN关键字用于检查艺术品的登记号是否已存在于collections表中。这是因为，正如我们从上周所知，登记号唯一地标识了表中每一件艺术品。

  • 如果艺术品确实存在于底层表中，我们将它的deleted值设置为 0，表示软删除的撤销。

• 第二种情况发生在我们尝试插入一个在底层表中不存在的行时。以下触发器处理这种情况。

  CREATE TRIGGER "insert_when_new"
  INSTEAD OF INSERT ON "current_collections"
  FOR EACH ROW
  WHEN NEW."accession_number" NOT IN (
      SELECT "accession_number" FROM "collections"
  )
  BEGIN
      INSERT INTO "collections" ("title", "accession_number", "acquired")
      VALUES (NEW."title", NEW."accession_number", NEW."acquired");
  END; 
  

  • 当插入数据的登记号不在collections中时，它将行插入到表中。

Fin

• 这就带我们来到了关于 SQL 中查看的第四讲的内容总结！