日拱一卒，伯克利教你用SQL处理数据

语言: CN / TW / HK
时间 2022-06-07 20:15:18

大家好，日拱一卒，我是梁唐。

我们继续伯克利CS61A公开课之旅，这是倒数第二节实验课了，胜利近在眼前。

这一次的实验课关于SQL处理，对应作业12。如果之前错过了的小伙伴刚好可以这一次补上。这节课的内容非常扎实，基本上涵盖了SQL当中常用的所有语法，虽然说通过一篇文章或者是一节课入门某个技术有些夸张。但至少打下一个比较扎实的基础还是没有问题的。

课程链接

实验原始文档

我的Github

和之前的实验一样，我们还是需要先去官网下载实验资料。

这一次老师准备的数据当中有点问题，会导致部分题目测试无法通过。我们需要改动的文件只有lab12.sql和lab12_extra.sql这两个，其余的都是测试以及数据文件。

准备好了之后，我们就可以开始今天的实验课了！

SQLite

Setup

设置

最简单的使用SQLite的方法，就是从官网下载预编译的二进制文件。官网：http://www.sqlite.org/download.html

Windows

  1. 访问官网中for Windows的预编译模块，点击sqlite-tools-win32-x86-*.zip下载
  2. 解压缩，解压缩之后会获得sqlite3.exe文件
  3. cd到对应的目录，并检查sqlite版本

macOS Yosemite (10.10) or newer

优胜美地（10.10）或更高版本

SQLite已经内置了，检查版本是否在3.8.3以上：

Ubuntu

最简单的方法就是使用apt-get工具进行安装，版本可能会稍微落后最新版

Usage

注意：如果你下载了预编译的二进制，确保sqlite3.exe文件和你的.sql文件在同一个目录

当你在.sql文件编写完成之后，你可以测试和验证你的输出。使用一下两种命令：

  1. 运行代码之后立即退出SQLite：

  1. 运行代码之后仍然在SQLite当中，有点类似于使用-i运行Python代码。你可以使用.help查看你能运行的命令：

使用.exit或者.quit退出SQLite，你也可以使用Ctrl-C，如果你看到了..>说明你可能忘记了语句末尾的;

Topics

SQL Basics

Creating Tables

创建表

你可以从零开始或者从已有的表来创建新的表。

下面是从零开始创建表的方式：

注意：在你创建表的时候，你不必在之后的select语句当中重复使用as进行重命名

下面是我们是使用create table语句创建表的例子，union用来合并多行，as用来给列进行重命名。

sql CREATE TABLE Football AS SELECT 30 AS Berkeley, 7 AS Stanford, 2002 AS Year UNION SELECT 28, 16, 2003 UNION SELECT 17, 38, 2014;

这样我们就创建好了一个叫做Football的表，它拥有三个属性（列）：Berkeley, Stanford和Year。之后我们可以通过表的列名访问这些值。

如果想要从已有的表来创建新表，对其他表使用select即可。

Selecting From Tables

通常，我们会从已有的表中选出我们需要的列来创建新表。select语句可以包括一些可选分支，比如：

  • from：告诉SQL我们想要选择数据的表
  • where： 根据一些条件进行过滤
  • order by： 根据某个或某些属性进行排列
  • limit： 限制输出结果的行数

注意参数：

  • [columns]：select要选择的列名，通过逗号分隔。*用来表示所有列
  • [tables]：逗号分隔的表名，表示我们想要获取数据的表
  • [condition]： 一个bool表达式
  • [attributes]：逗号分割的属性，通常是列名
  • [limit]：一个整数

我们可以使用select语句从表中选出所有的属性的所有值。另外，我们可以通过加上where条件对数据进行筛选。现在，我们对Year > 2002进行过滤。这会使得select仅仅保留Year大于2002的行

这表示我们选择了Berkeley中2002年之后的分数。

Expressions in SQL

下面是一些基础的运算：

  • 比较符：=, >, <, <=, >=, <>，最后一个表示不等于
  • bool：and, or
  • 算数运算符：+, -, *, /

我们还可以对string进行拼接，使用||。有些类似于Python当中对字符串执行+

注意：SQL不区分大小写，我们使用大写只是一种风格，这会使得代码阅读更加方便

Joins

我们可以使用join引入另外一张表的某些列，join可以用在不同的表之间，也可以用在同一张表上（需要我们创建别名）。下面我们引用了football表两次，使用as将football一直绑定成了a，一直绑定成了b。

这个表查询了什么结果呢？

你可能已经注意到了，我们实际上并没有使用什么运算符来实现join。实际上，join是当我们在from之后列举了超过一张表之后间接进行的。在这个了尺子当中，我们把Football表和它自身join，并且给了表一个别名a和b。这样我们就可以区分每张表的属性以及完成select和比较了。比如a.Year < b.Year。

一种理解join的办法是它对两张表进行了cross-product（笛卡尔积），也就是将左表的每一行都与右边的每一行进行拼接，会创建一个新的，更大的表。

下图展示了两张表join时产生的结果：

两张都是3行数据的表join时产生了9行的数据，本质上是左表的每一行与右边的每一行都会产生一个连接。

Required Questions

Getting to Know Your Fellow 61A Students

熟悉你61A课程的同班同学

上周末，我们要求你和你的同学们在google form里完成了一个在线的简单调查。在这节课上，我们将会使用SQL来对调查的结果进行交互，来看看能不能从数据中找到一些有趣的点。

首先，看一下sp18data.sql，检查一下其中定义的表，注意一下它的结构：

  • students：这次调研的主要结果。每一列表示了一个调研中不同的问题，除了第一列，是调研被提交的时间。这张表中每一行的时间各不相同

| Column Name | Question | | ----------- | ------------------------------------------------------------ | | time | 问卷被提交时的时间戳 | | number | 1到100中你最喜欢的数 | | color | 你最喜欢的颜色 | | seven | 选择"the number 7 below" 选项：7你不是我的老板选这个别选7seventhe number 7 below | | song | 如果你的余生只能听下列一首歌，你会选择哪个？ Options:"Smells Like Teen Spirit" by Nirvana"The Middle" by Zedd"Fur Elise" by Ludwig van Beethoven"Finesse ft. Cardi B" by Bruno Mars"God's Plan" by Drake"Down With The Sickness" by Disturbed"Everytime We Touch" by Cascada | | date | 从今年选一天 | | pet | 如果你可以选择世界上任何一个动物作为宠物，你会选择哪个？ | | denero | 选择你最喜欢的John DeNero的照片 | | smallest | 猜一个你觉得其他人都不会猜的最小正整数 |

  • checkboxes：在问卷中，学生可以选择不止一个选项，从0到10，包括2018，9000和9001。每一行拥有一个时间（是一个独一无二的标识符），以及一个值，True表示学生选择了这一列，False表示没有。这张表中的列名是如下的字符串：'0', '1', '2', '4', '5', '6' , '7', '8', '9', '10', '2018', '9000', '9001'.

因为问卷是匿名的，所以我们使用问卷提交的时间戳作为标识符。students中的时间和checkboxes的时间相吻合。比如，students中有一行的时间是4/13/2018 15:25:03和checkboxes表中同样时间的行匹配。它们属于相同的google form，并且属于同一个学生。

你将在lab12.sql中编写代码，和其他实验一样，你可以使用ok命令来测试代码，下面两种方式都可以，任选一个即可。

bash sqlite3 < lab12.sql sqlite3 --init lab12.sql

Q1: What Would SQL print?

SQL语句将会输出什么结果？

首先，在SQLite3中加载表：

bash sqlite3 --init lab12.sql

在我们开始之前，检查我们为你提供的表的schema：

sql sqlite> .schema

这会告诉我们当前有的表名，以及它的属性。

让我们来看一些表中的数据，由于表中的数据很多，所以仅仅输出20行就足够了：

sql sqlite> SELECT * FROM students LIMIT 20;

如果你好奇其他人的答案，你可以在文本编辑器中打开sp18data.sql

对于下列每一个SQL语句，思考一下它查询的内容，然后试着自己运行一下查看结果：

```sql sqlite> SELECT * FROM students; -- This is a comment. * is shorthand for all columns!

sqlite> SELECT color FROM students WHERE number = 16;

sqlite> SELECT song, pet FROM students WHERE color = "blue" AND date = "12/25";

```

Q2: Obedience

让我们来问一个关于数据的简单问题来热身：让我们来看看学生是否听话和他们最喜欢的工作人员动物形象是否有关？

编写一个SQL语句来创建一张表，它包含students中seven和denero列。

你应该获得如下输出：

sql CREATE TABLE obedience AS SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

使用ok命令进行测试：python3 ok -q obedience

答案

sql CREATE TABLE obedience AS select seven, denero from students;

Q3: The Smallest Unique Positive Integer

谁会猜准最小且没有别人猜到的数字呢？让我们来看看！

不幸的是，我们还没有学过SQL中聚合的语法，它可以帮助我们计算每个特定的值出现的次数。所以我们只能手动检查数据来寻找它了。然而，一个匿名的小精灵告诉我们最小的独一无二的整数大于15！

编写一个SQL查询，创建一张表，拥有time和smallest两列我们检查要用到的数据。为了让我们检查起来更加简单，使用where来限制答案必须要超过15。order by来对数字进行排序，并且limit20条大于15的数据。

你得到输出结果的前五行应该是这样的：

sql CREATE TABLE smallest_int AS SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

使用ok来进行测试：python3 ok -q smallest-int

在你成功通过ok测试之后，看一眼你刚刚创建的smallest_int表，手动找到答案。如果你好奇如何使用聚合来求解，可以靠近问题8.

答案

sql CREATE TABLE smallest_int AS select time, smallest from students where smallest > 15 order by smallest limit 20;

Q4: Matchmaker, Matchmaker

你有没有想过通过61A找到你的灵魂伴侣呢？你很幸运！通过这些数据，你可以很容易找到你的完美另一半。如果两个学生想要相同的宠物，在音乐上拥有相同的偏好，那么他们注定在一起！为了给潜在的情侣提供更多的信息，让我们也考虑一下他们最喜欢的颜色。

为了匹配学生，你将要用students join它自己。当你进行join时，SQLite将会匹配两张表的每一行。所以不要让一个人和他自己匹配，或者是同一个匹配出现两次。

重要注意：当匹配两个人时，确保提交时间更小的人在前，这样可以保证你通过测试

提示：当join表并且它们的类名也一样时，使用.符号加上表名来区分列名：[table_name].[column_name]。这可能会看起来很长，可以考虑给表名起一个别名：

编写SQL创建一个拥有以下4列的表：

  • pet：情侣共同喜欢的宠物
  • song：情侣共同喜欢的歌
  • color：第一个人喜欢的颜色
  • color：第二个人喜欢的颜色

你将会获得以下结果：

使用ok进行测试：python3 ok -q matchmaker

答案

sql CREATE TABLE matchmaker AS select a.pet, a.song, a.color as color1, b.color as color2 from students as a, students as b where a.pet = b.pet and a.song = b.song and a.time < b.time;

Optional Questions

接下来的问题都是额外的练习——它们都在lab12_extra.sql文件中。非常推荐你也完成它们

Q5: Great Students Think Alike

我们在上个学期也进行了一个类似的问卷

其余问题都没有改动，只是删除了hilfinger 问题（数据在fa17data.sql中）。让我们使用join来玩一下这份数据。

好的学生都是相似的，我们想要打印出在date, color, pet上拥有相同喜爱的学生信息，他们在最喜欢的number上有哪些差别呢？

编写一个SQL查询来创建一个拥有5列的表：

  • 共同喜欢的date
  • 共同喜欢的color
  • 共同喜欢的pet
  • 这个学期学生最喜欢的number
  • 上个学期学生最喜欢的number

你应该得到这样的结果：

使用ok命令进行测试：python3 ok -q greatstudents

答案

emmm，这题老师的测试数据有问题……

sql CREATE TABLE greatstudents AS select a.date, a.color, a.pet, a.number as number_18, b.number as number_17 from students as a, students_pt1 as b where a.pet = b.pet and a.date = b.date and a.color = b.color;

Q6: Sevens

让我们来看看students和checkboxes表中的数据，看看学生是否真的很喜欢数字7，并且在服从性问题当中也选了'7'。特别的，我们想看看符合一下条件的学生在让学生选择7的问题当中是否也选了'7'（数据中seven这一列）

条件：

  • 他们最喜欢的数字是7
  • 在checkboxes表中'7'这一列是True

为了同时检查students和checkboxes表中的数据，我们需要使用join。

我们怎么指定where条件，能够让我们select出我们需要的行，并且保证这些数据属于同一个学生呢？如果你发现你输出的结果数量非常大，那么你可能在where当中少了一个关键条件。

注意：checkboxes表中的列都是和实际数字相关的字符串，所以你必须要加上引号来使用它。比如这张表的别名是a，你想要查看学生是否选择了9001，那么你需要写成a.'9001'

编写一个SQL查询来创建一张表，只有一列seven。筛选出最喜欢的数字是7，并且在checkboxes表中也选了7的结果

结果的前10行应该是这样的：

使用ok命令进行测试：python3 ok -q sevens

答案

sql CREATE TABLE sevens AS select seven from students as a, checkboxes as b where a.time = b.time and a.number = 7 and b.'7' = 'True';

The COUNT Aggregator

每种宠物有多少人喜欢呢？被选择最多的日期是什么？有多少学生选择了图片1作为教授DeNero或者教授Hilfinger？上个学期学生的最喜欢数字的平均数和这个学期有区别吗？

想要回答这一类的问题，我们需要使用SQL aggregation（聚合），让我们能够在多行之间聚合数据。

为了使用SQL aggregation，我们可以对表中的行根据一个或多个属性进行分组（group）。

当我们有了分组之后，我们可以聚合每一组的数据，求出以下数据：

  • 最大值 (MAX),
  • 最小值 (MIN),
  • 组中行的数量 (COUNT),
  • 值的平均数 (AVG),

使用聚合的select语句通常有两个标志：一个聚合函数（max, min, count, avg等），以及一个group by条件。group by [columns(s)]将列值相同的行进行分组。在这个章节当中，我们仅仅会使用count，它会计算每个分组当中行的数量。

比如下面一个查询将会输出十大最受喜欢的数字，以及选择它们的数量：

这个select语句首先将students表中的行按照number进行分组。接着，在每一个分组当中，我们使用了count聚合函数。通过select出number和count(*)，我们可以得到出现次数最多的number以及它被选择的次数。

我们需要对count(*)的结果使用order by，这里我们给count(*)起了一个别名叫做count，DESC表示按照降序排序。所以我们就选出了出现次数最大的top 10

Q7: Let's Count

让我们来做一些有趣的事，对于接下来的每一个查询，我们都为它在lab12_extra.sql中创建了表。所以给对应的表填写代码，在使用ok来进行运行。

提示：如果你不是想要对某一个属性进行统计次数，而只是想要统计行数，可以使用count(*)

上个学期最喜欢的数字是什么呢？

上个学期最受欢迎的top10的宠物有哪些？

这个学期top10的宠物有哪些？

这学期有多少人选择了'dog'作为他们的理想宠物？

虽然很接近，但我们的查询并不能准确反映出人们最喜欢的宠物是什么。比如a dog和dog不一样，就不会被计入统计。让我们来使用like来看看本学期到底有多少人喜欢狗。like可以比较字符子串。我们可以在where中使用，比如`where [column_name] like '%[word]%' 来寻找多少学生喜欢广义上的狗。

我们可以对任何列统计学生的喜好，但让我们回到服从性问题。看看这学期有多少听话的学生为Denero教授选择了图片。我们可以选出seven = '7'的行，接着使用group by denero，最后可以count一下

使用ok进行测试：python3 ok -q lets-count

答案

sp17的数据有些问题，会导致答案对不上

```sql CREATE TABLE fa17favnum AS select number, count(*) as cnt from students_pt1 group by number order by cnt desc limit 1;

CREATE TABLE fa17favpets AS select pet, count(*) as cnt from students_pt1 group by pet order by cnt desc limit 10;

CREATE TABLE sp18favpets AS select pet, count(*) as cnt from students group by pet order by cnt desc limit 10;

CREATE TABLE sp18dog AS select pet, count(*) as cnt from students where pet = 'dog' group by pet;

CREATE TABLE sp18alldogs AS select pet, count(*) as cnt from students where pet like '%dog%';

CREATE TABLE obedienceimages AS select seven, denero, count(*) as cnt from students where seven = '7' group by seven, denero; ```

Q8: The Smallest Unique Positive Integer (Part 2)

现在，让我们回顾一下之前选择最小不重复正整数的问题，来近距离看看count聚合。

编写一个SQL查询，使用count聚合来创建一张表，将smallest和它出现的次数存储下来。当你完成之后， 你会看到如下结果：

使用ok进行测试：python3 ok -q smallest-int-count

看起来数字18只有一个人选择，你是那个幸运儿吗？

答案

sql CREATE TABLE smallest_int_count AS select smallest, count(*) as cnt from students group by smallest;

「」
「」