日拱一卒,伯克利教你用SQL处理数据
大家好,日拱一卒,我是梁唐。
我们继续伯克利CS61A公开课之旅,这是倒数第二节实验课了,胜利近在眼前。
这一次的实验课关于SQL处理,对应作业12。如果之前错过了的小伙伴刚好可以这一次补上。这节课的内容非常扎实,基本上涵盖了SQL当中常用的所有语法,虽然说通过一篇文章或者是一节课入门某个技术有些夸张。但至少打下一个比较扎实的基础还是没有问题的。
和之前的实验一样,我们还是需要先去官网下载实验资料。
这一次老师准备的数据当中有点问题,会导致部分题目测试无法通过。我们需要改动的文件只有lab12.sql
和lab12_extra.sql
这两个,其余的都是测试以及数据文件。
准备好了之后,我们就可以开始今天的实验课了!
SQLite
Setup
设置
最简单的使用SQLite的方法,就是从官网下载预编译的二进制文件。官网:http://www.sqlite.org/download.html
Windows
- 访问官网中for Windows的预编译模块,点击sqlite-tools-win32-x86-*.zip下载
- 解压缩,解压缩之后会获得
sqlite3.exe
文件 - cd到对应的目录,并检查sqlite版本
macOS Yosemite (10.10) or newer
优胜美地(10.10)或更高版本
SQLite已经内置了,检查版本是否在3.8.3以上:
Ubuntu
最简单的方法就是使用apt-get工具进行安装,版本可能会稍微落后最新版
Usage
注意:如果你下载了预编译的二进制,确保sqlite3.exe
文件和你的.sql
文件在同一个目录
当你在.sql
文件编写完成之后,你可以测试和验证你的输出。使用一下两种命令:
- 运行代码之后立即退出SQLite:
- 运行代码之后仍然在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
来对数字进行排序,并且limit
20条大于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;
- 日拱一卒,麻省理工教你信息安全和密码学
- LeetCode周赛301,离大谱,手速场掉分,质量场掉大分……
- 日拱一卒,元编程不是元宇宙,麻省理工教你makefile、依赖管理和CI
- LeetCode周赛300,差点AK,刚拿到的勋章要丢了……
- 日拱一卒,麻省理工教你性能分析,火焰图、系统调用栈,黑科技满满
- 日拱一卒,麻省理工教你Debug,从此debug不再脱发
- 日拱一卒,麻省理工教你学Git,工程师必备技能
- 日拱一卒,配置开发环境不再愁,麻省理工手把手教你
- 日拱一卒,麻省理工CS入门课,命令行这样用也太帅了
- 日拱一卒,麻省理工YYDS,一节课让我学会用vim
- 日拱一卒,麻省理工教你CS基础,那些酷炫无比的命令行工具
- LeetCode周赛297,一小时AK你也可以
- 听说你入行好几年还只会cd和ls,麻省理工开了这门课……
- 日拱一卒,伯克利CS61A完结撒花
- 日拱一卒,伯克利教你用SQL处理数据
- LeetCode周赛296,难度较低的新手练习场
- 日拱一卒,伯克利教你Python核心技术,迭代器和生成器
- 日拱一卒,伯克利CS61A,教你用Python写一个Python解释器
- 日拱一卒,伯克利教你lisp,神一样的编程语言
- LeetCode周赛295,赛后看了大佬的代码,受益匪浅