SQL 与表：把「怎么算」交出去

0. 本讲导读

到上一讲为止，这门课教你的全部内容都可以概括成一句话：把「怎么算」写清楚。Python 的求值规则、环境图里的一帧一帧、递归怎么展开又怎么回代、Scheme 的解释器如何 eval 和 apply、尾调用怎么把栈压平、宏怎么在求值之前改写代码——每一样都是在描述步骤。你是那个安排步骤的人，机器只是老实照做。

本讲换一件事做。你会遇到一种叫 SQL 的语言，它属于声明式编程（declarative programming）：你只描述你想要的结果长什么样，怎么算出来是机器的事。 你不写循环，不写递归，不写函数调用；你写「我要一张表，它有这两列，只保留纬度大于 40 的那些行，按名字倒序排」。剩下的——先扫哪张表、要不要建索引、按什么顺序过滤才最快——由数据库管理系统（database management system，DBMS）自己决定。（这个「自己决定」的部分是 CS 186 和 DATA 101 的内容，本课不展开。）

还有第二件事，比范式的切换更朴素但同样重要：持久化。你到目前为止写的每一个程序，数据都活在内存里的那些帧中。程序一结束，全局帧连同它绑定的一切统统消失；断电更是什么都不剩。数据库解决的正是这个问题——数据存在磁盘上，程序退出了它还在，明天再来查还是那些行。

所以本讲要回答的问题是：当数据不再是「程序运行期间内存里的一个列表」，而是「一张长期存在、可能有几千万行的表」时，你用什么语言、什么思路去问它问题？

往前看，上一讲的尾调用与宏是「你能对求值过程施加多大的控制」的顶点；本讲反过来，是「你主动放弃对求值过程的控制」。往后看，下一讲会在本讲的基础上加上聚合（aggregation）——COUNT、SUM、MAX、GROUP BY、HAVING——那些是「把很多行压成一行」的工具，而本讲的每一个子句都是它们的地基。本讲对应的作业是 HW 06。

1. 数据库：为什么不能就用一个 Python 列表

先说清楚不引入数据库会卡在哪。假设你在写一个记录奶茶订单的程序，最自然的想法是用一个列表装字典：

orders = [
    {'name': 'Rabia',   'item': 'Black Milk Tea',       'ice': 'Regular'},
    {'name': 'Richard', 'item': '61A Special Milk Tea', 'ice': 'Light'},
]

这段代码没有任何错。但它有三个致命的局限：

1. 活不过程序的生命周期。 orders 是全局帧里的一个绑定。python3 shop.py 跑完，进程退出，全局帧被回收，这个列表连同里面的字典一起消失。明天再跑一次，你拿到的是空列表。你可以自己写文件读写，但那意味着你要自己设计格式、自己处理写到一半断电、自己保证两个进程同时写不会互相覆盖——这些每一件都很难。
2. 查询要自己写循环。 「找出所有点了黑奶茶且要常温冰的人」你得写一个 for 加两个 if。「找出点了同一种奶茶的两个人」你得写双重循环。数据量一大，你还得自己想怎么让它快起来。
3. 没有结构约束。 没人拦着你往列表里塞一个 {'nmae': 'Sriya'}（拼错了 key），或者塞一个 ice 为整数 3 的记录。错误要等到读取时才炸。

数据库把这三件事一次性解决掉。

三个术语

「每一列只有一种数据类型」这一条就是上面第 3 个局限的解药：你没法把一个整数塞进一个声明为字符串的列里而不被发现，也没法凭空多出一个列。

一张表长什么样

本讲从头到尾都会用到这张 cities 表：

三列：latitude 和 longitude 是整数列，name 是字符串列。四行，每一行是一条记录，描述一座城市。注意字符串在 SQL 里用单引号，不是双引号——双引号在 SQL 里表示「标识符」（列名/表名），意思完全不同。

2. SQL：一门只描述结果的语言

结构化查询语言（Structured Query Language，SQL）是一门用于操作数据库的声明式编程语言。它的名字读作 “sequel” 或者一个字母一个字母念 “S-Q-L”，两种读法都通行。

SQL 有很多方言（variant），61A 用的是 SQLite。而且本课只用 SQL 的一个子集：数据查询语言（Data Query Language，DQL）——只读数据库的那部分。真正的 SQL 还能建库、改表、插入删除、管权限，那些都不在本课范围里。

声明式意味着什么

把你学过的三种范式跟它并排放一下，差别一眼就出来了：

体会一下这句话的分量：在前三种范式里，「这段程序对不对」等价于「按我写的步骤走一遍，结果对不对」；在 SQL 里，「这段查询对不对」等价于「我描述的那张结果表，是不是我真正想要的那张」。你没有步骤可追。这是本讲最需要转过来的一个弯——很多人写 SQL 卡住，是因为还在脑子里找「循环变量现在等于几」。SQL 里没有循环变量，只有一张一张的中间表。

语法上的几条硬规矩

• SQL 大小写不敏感，但按约定，关键字全部写成大写。select * from cities; 能跑，但没人这么写。大写关键字是为了让你一眼看清骨架。
• 每条 SQL 语句必须以分号 ; 结尾。
• 跟 Scheme 一样，空白基本只影响可读性——最低要求是关键字、列名、表名之间至少隔一个空格。你可以把一条查询写成一行，也可以每个子句一行。
• 单行注释用 --（两个连字符），后面到行尾都是注释。

-- 这是注释。下面这三种写法完全等价：
SELECT latitude, name FROM cities;

SELECT latitude, name
FROM cities;

SELECT     latitude,     name       FROM
   cities;

3. SELECT：所有查询的起点

SELECT 做的事只有一件：创建一张新表。它不修改任何已有的表。这句话要背下来，因为它是理解后面一切的钥匙。

创建新表有两种来源：从零凭空造，或者从已有的表里取列。

用法一：凭空造一张表

SELECT <expression>;

不带 FROM 的 SELECT 会造出一张只有一行的表，行里装着你写的那些表达式的值：

sqlite> SELECT 5 + 3;
8

sqlite> SELECT 'hello' AS greeting, 2 * 3 AS six;
hello|6

这看起来像个玩具，但它是你验证 SQL 表达式行为的最快工具——想知道 '100%' < '75%' 到底是真是假？直接 SELECT '100%' < '75%';。它相当于 SQL 世界里的 Python REPL。

用法二：从已有表取列

SELECT [col1], [col2], ... FROM <table>;

SELECT * FROM <table>;

列名之间用逗号隔开，可以选 1 个或多个；* 是「所有列」的简写。

SELECT latitude, name FROM cities;

FROM cities  →  latitude | longitude | name
                --------------------------------
                38       | 122       | 'Berkeley'
                41       | 74        | 'New York'
                45       | 93        | 'Minneapolis'
                34       | 118       | 'Los Angeles'

SELECT latitude, name  →  latitude | name
                          -------------------------
                          38       | 'Berkeley'
                          41       | 'New York'
                          45       | 'Minneapolis'
                          34       | 'Los Angeles'

这种「一张表进、一张表出」的画法，就是 SQL 版本的环境图。往后每遇到一条不理解的查询，都把中间表一张一张画出来，问题基本就自己解开了。

sqlite> SELECT name FROM bobaa;
Parse error: no such table: bobaa

sqlite> SELECT nmae FROM boba;
Parse error: no such column: nmae

4. 运算符与 AS：算出来的列、拼出来的字符串、改过名的列

SELECT 后面跟的不一定是列名，可以是任意表达式。表达式里能用的运算符跟 Python 高度相似，但有四个地方不一样，而且每一个都会咬人。

算术与比较

「= 用来比较」这一条对刚学完 Python 的人特别别扭——你被反复教育过 = 是赋值、== 才是比较。但 SQL 的 DQL 子集里根本没有赋值这回事（你不改任何东西，只是描述结果），单个 = 就空出来给比较用了。SQLite 出于兼容也接受 ==，但读别人的 SQL 时你看到的几乎全是 =。

sqlite> SELECT 3 = 3, 3 <> 4, 'a' || 'b', 7 / 2, 7.0 / 2;
1|1|ab|3|3.5

注意输出：SQLite 里没有独立的布尔类型，真是 1，假是 0。这跟 Python 的 True/False 打印成 True/False 不同，第一次看到别以为出错了。

AS：给列起别名

问题来了。如果你写 SELECT 2 * pieces FROM boba;，这一列该叫什么名字？SQLite 会拿你写的那个表达式的文本当列名，于是列名变成 2 * pieces——难看，而且后面没法拿它做事。AS 就是为这个而生的，它给列或表达式起一个别名（alias），也叫「aliasing 一个列」。

SELECT name || ', lat ' || latitude AS label FROM cities;

在本机的 SQLite 上实际跑一遍（cities 表已按第 1 节建好）：

Los Angeles, lat 34
Berkeley, lat 38
Metropolis, lat 38
New York, lat 41
Minneapolis, lat 45

三件事同时发生了：|| 把字符串接起来；整数 latitude 被自动转成字符串参与拼接；AS label 把这一列命名为 label。

5. WHERE：按条件把行删掉

SELECT 管列，WHERE 管行。WHERE 子句用来根据某个条件过滤掉一些行。

SELECT <expression(s) and/or column(s)>
FROM <table>
WHERE <condition>;

机制非常直白：拿 FROM 给出的表，一行一行地代入条件求值，为真就留下，为假就扔掉。 这确实就是一个「对每一行做一次判断」的循环——只不过循环是数据库替你写的。

SELECT latitude, name FROM cities WHERE latitude > 40;

FROM cities        WHERE latitude > 40      SELECT latitude, name
4 行 3 列      →   2 行 3 列            →   2 行 2 列

38 122 Berkeley        41 74 New York           41  New York
41 74  New York        45 93 Minneapolis        45  Minneapolis
45 93  Minneapolis
34 118 Los Angeles

关键一点：WHERE 的条件里可以用 SELECT 没有选中的列。 上面如果写 WHERE longitude < 100，也完全合法，尽管 longitude 不出现在结果里。原因还是执行顺序——WHERE 跑的时候，整张原表都还在手上，裁列是之后的事。

WHERE 里的类型陷阱

这是本讲最容易栽的地方，随堂练习专门为它设了一道题。看这张 boba 表的一部分：

sweetness 这一列装的是字符串：'100%'、'50%'，因为百分号本身是字符。于是：

sqlite> SELECT * FROM boba WHERE sweetness = 100;
        <-- 一行都没有

sqlite> SELECT * FROM boba WHERE sweetness > '75%';
        <-- 一行都没有

sqlite> SELECT '100%' < '75%';
1

6. LIKE：字符串的模式匹配

WHERE name = 'Berkeley' 只能做完全相等的匹配。可如果你想问「名字里带空格的城市有哪些」「所有以 Milk Tea 结尾的饮品」，相等就不够用了。LIKE 子句负责这一类字符串匹配，靠两个通配符（wildcard）来描述模式：

并且：在 SQLite 里 LIKE 的匹配是大小写不敏感（case insensitive）的。 这是它跟 = 最大的行为差异，考试和作业里都爱考。

SELECT name FROM cities WHERE name LIKE '% %';

模式 '% %' 读作「任意多个字符 + 一个空格 + 任意多个字符」，也就是名字里含有空格。逐行判定：

把图里的规律提炼成三个句式，写模式的时候直接套：

在 boba 表上验证一次大小写不敏感（表里存的都是 '... Milk Tea'，注意大写的 M 和 T）：

sqlite> SELECT name FROM boba WHERE name LIKE '%milk tea';
61A Special Milk Tea
61A Special Milk Tea
61A Special Milk Tea
Black Milk Tea
Black Milk Tea
Black Milk Tea
Thanos Milk Tea
Wintermelon Milk Tea
Wintermelon Milk Tea
Wintermelon Milk Tea

模式全小写，照样把大写的都捞出来了。另外注意结果里有重复——WHERE 只负责删行，它不去重；表里本来就有三行 'Black Milk Tea'，出来就是三行。

7. ORDER BY 与 LIMIT：排序与截断

到这里你已经能控制「哪些行」和「哪些列」了，还差两件事：行的顺序，和只要前几行。

ORDER BY

SELECT <columns>
FROM <table>
WHERE <condition>
ORDER BY <columns> ASC/DESC;

ORDER BY 按一列或多列给行排序。默认升序（ascending），写 DESC 表示降序（descending），ASC 是显式的升序（不写也一样）。

多列排序的规则是：先按最左边那列排；左边这列相等的行（也就是「打平了」的行），再用右边下一列来分胜负——从左往右依次打破平局。

用扩充后的 cities 表看（多了一行 Metropolis，它跟 Berkeley 同为纬度 38）：

SELECT * FROM cities ORDER BY latitude, name DESC;

-- ORDER BY latitude, name DESC        -- ORDER BY latitude DESC, name
34  118  Los Angeles                   45  93   Minneapolis
38  130  Metropolis                    41  74   New York
38  122  Berkeley                      38  122  Berkeley
41  74   New York                      38  130  Metropolis
45  93   Minneapolis                   34  118  Los Angeles

LIMIT

加上 LIMIT 子句可以只保留输出的前 n 行。

SELECT <columns>
FROM <table>
WHERE <condition>
ORDER BY <columns>
LIMIT <n>;

LIMIT 是最后一个执行的子句，这一点决定了它的正确用法。「找出纬度最小的两座城市」要这么写：

sqlite> SELECT * FROM cities ORDER BY latitude LIMIT 2;
34|118|Los Angeles
38|122|Berkeley

8. JOIN（一）：cross join 就是一个嵌套 for 循环

到目前为止所有查询都只碰一张表。可现实里的数据是拆开存的：一个大学的数据库会有一张 courses 表和一张 students 表，你想知道「哪个学生在上哪门课」，答案不在任何一张表里，而在两张表的关联中。

把多张表的数据组合起来的操作叫 join（连接）。最简单的一种叫 cross join（交叉连接，也叫笛卡儿积）：把表 A 的每一行跟表 B 的每一行都配一遍。

result = []
for row_a in table_a:          # 外层循环：左表
    for row_b in table_b:      # 内层循环：右表
        result.append(row_a + row_b)   # 两行首尾拼成一行

把它画出来

幻灯片用一个 2 行的表和一个 3 行的表演示了整个过程，结果应当是 2 × 3 = 6 行。分两步看：

隐式连接（implicit join）

隐式连接指的是不使用 JOIN 关键字的连接写法——你直接在 FROM 后面用逗号列出两张或更多的表：

SELECT <columns>
FROM <table1>, <table2>
WHERE <condition>;

光写 FROM table1, table2 你就已经得到了那张 m×n 的大表。但那张大表通常是没意义的——里面绝大多数行是胡乱搭配。WHERE 的作用就是从这张大表里把有意义的行挑出来。

在真实数据上跑一遍

随堂代码里有两张小表。orders（谁点了什么）：

menu（每种饮品多少钱）：

问题：「每个人点的东西各多少钱？」名字在 orders 里，价格在 menu 里，必须连起来。先看不加条件时那张大表到底长什么样——4 行 × 4 行 = 16 行，实测输出的前 12 行：

sqlite> SELECT * FROM orders, menu;
Rabia  |Black Milk Tea      |Regular|61A Special Milk Tea|8
Rabia  |Black Milk Tea      |Regular|Black Milk Tea      |5
Rabia  |Black Milk Tea      |Regular|Thanos Milk Tea     |7
Rabia  |Black Milk Tea      |Regular|Wintermelon Milk Tea|6
Rebecca|Thanos Milk Tea     |Extra  |61A Special Milk Tea|8
Rebecca|Thanos Milk Tea     |Extra  |Black Milk Tea      |5
Rebecca|Thanos Milk Tea     |Extra  |Thanos Milk Tea     |7
Rebecca|Thanos Milk Tea     |Extra  |Wintermelon Milk Tea|6
Richard|61A Special Milk Tea|Light  |61A Special Milk Tea|8
Richard|61A Special Milk Tea|Light  |Black Milk Tea      |5
Richard|61A Special Milk Tea|Light  |Thanos Milk Tea     |7
Richard|61A Special Milk Tea|Light  |Wintermelon Milk Tea|6
    ... 还有 Sriya 的 4 行

看清楚了：Rabia 点的是黑奶茶，却跟菜单上四种饮品各配了一次。16 行里只有 4 行是「这个人点的东西 = 菜单上这一项」，那 4 行才是我们要的。用 WHERE 把它们挑出来：

SELECT name, item, ice, price
FROM orders, menu
WHERE orders.item = menu.item;

这条查询跑不起来，真实报错是：

Parse error: ambiguous column name: item

原因很实在：连接之后的大表里有两个叫 item 的列（一个来自 orders，一个来自 menu），你写光秃秃的 item，数据库不知道你指哪个。WHERE 里之所以没事，是因为那里已经写了 orders.item 和 menu.item——这个「表名点列名」的写法是下一节的主题。先把它补上，查询就通了：

SELECT name, orders.item, ice, price
FROM orders, menu
WHERE orders.item = menu.item;

Rabia  |Black Milk Tea      |Regular|5
Rebecca|Thanos Milk Tea     |Extra  |7
Richard|61A Special Milk Tea|Light  |8
Sriya  |Black Milk Tea      |Regular|5

9. JOIN（二）：点记号、显式 JOIN，与自连接

点记号（dot notation）

上一节那个 ambiguous column name: item 说明了一件事：join 之后，「列名」可能不再唯一。点记号就是用来消歧的——写 <表名>.<列名>，明确指出你要哪张表的哪一列。

规则有三条：

• 点记号只在列名有歧义时是必需的（两张表有重名的列）。
• 但习惯上应该一直用，因为它让人一眼看出每个列的出处。查询一长，这个可读性收益非常大。
• 点记号可以用完整表名，也可以用表的别名。表也能用 AS 起别名，通常起成一两个字母。

讲义里的示例（records 表存职位信息，salaries 表存薪水，两张表都有 name 列）：

SELECT r.name, division, title, salary, salary2023
FROM records AS r, salaries AS s
WHERE r.name = s.name;

逐个成分拆开：

for r in records:
    for s in salaries:
        if r.name == s.name:
            yield (r.name, r.division, r.title, r.salary, s.salary2023)

显式连接（explicit join）

显式连接是使用 JOIN 关键字的写法，连接条件（也叫 join predicate，连接谓词）写在 ON 子句里：

SELECT <columns>
FROM <table1>
JOIN <table2>
ON <condition>;

把上面那条隐式连接改写成显式连接，两者完全等价：

-- Equivalent to implicit join example
SELECT r.name, division, title, salary, salary2023
FROM records AS r
JOIN salaries AS s
ON r.name = s.name;

用了 JOIN … ON 之后，你仍然可以再写 WHERE，用来做跟连接无关的其它过滤。这正是显式写法的价值所在。

SELECT o.name, o.item, o.ice, o.price
FROM orders AS o
JOIN menu AS m
ON o.item = m.item;

Parse error: no such column: o.price

自连接（self join）：一张表跟自己连

现在来看一个真正需要动脑子的场景：「找出点了同一款饮品的两个人」。这个问题的数据全在 orders 一张表里，但你要比较的是同一张表的两行。

SQL 没有「行变量」，你没法说「取第 i 行和第 j 行」。破局的办法是：把这张表当成两张表来用，各起一个别名。回到循环类比，这就是

for o1 in orders:
    for o2 in orders:
        ...

写成 SQL：

SELECT o1.name AS person1, o2.name AS person2
FROM orders AS o1, orders AS o2
WHERE o1.item = o2.item AND o1.name < o2.name;

这里的两个条件各解决一个问题，必须一个一个来看。orders 有 4 行，所以 FROM orders AS o1, orders AS o2 给出 4 × 4 = 16 行。

o1.name | o2.name | item
Rabia   | Rabia   | Black Milk Tea
Rabia   | Sriya   | Black Milk Tea
Rebecca | Rebecca | Thanos Milk Tea
Richard | Richard | 61A Special Milk Tea
Sriya   | Rabia   | Black Milk Tea
Sriya   | Sriya   | Black Milk Tea

Rabia | Sriya
Sriya | Rabia

person1 | person2
Rabia   | Sriya

sqlite> SELECT name, item FROM orders, orders;
Parse error: ambiguous column name: name

10. 执行顺序：SQL 不是从上往下跑的

这一节是本讲的收束点，也是把前面所有子句串起来的那根线。

因为 SQL 是声明式的，SQL 代码不一定按照从上到下、从左到右的顺序执行。你写的顺序是给人看的语法顺序；数据库真正干活的顺序是另一套。就本讲学到的这些子句而言，执行顺序是：

把它跟「每一步在做什么、表变成什么样」对照起来记：

SELECT o1.name AS person1, o2.name AS person2
FROM orders AS o1, orders AS o2
WHERE o1.item = o2.item AND o1.name < o2.name
ORDER BY person1
LIMIT 1;

写查询的六步法

执行顺序不只是拿来考的知识点，它直接给出了写查询的顺序。卡住的时候，按这六个问题一个一个问自己：

11. 实战：Boba 五题

随堂练习用的是 22.sql 里的三张表。先看 boba 表是怎么建的——这段代码本身就有个值得注意的地方：

CREATE TABLE boba AS
    SELECT 'Black Milk Tea' AS name, '100%' AS sweetness, 'Regular' AS ice, 23 AS pieces UNION
    SELECT 'Wintermelon Milk Tea', '50%', 'Light', 22 UNION
    SELECT '61A Special Milk Tea', '100%', 'Extra', 25 UNION
    SELECT 'Wintermelon Milk Tea', '75%', 'Light', 23 UNION
    SELECT 'Thanos Milk Tea', '50%', 'Regular', 12 UNION
    SELECT '61A Special Milk Tea', '75%', 'Regular', 27 UNION
    SELECT '61A Special Milk Tea', '75%', 'Extra', 24 UNION
    SELECT 'Black Milk Tea', '0%', 'Light', 24 UNION
    SELECT 'Thanos Milk Tea', '50%', 'Regular', 12 UNION
    SELECT 'Wintermelon Milk Tea', '25%', 'Regular', 22 UNION
    SELECT 'Black Milk Tea', '100%', 'Regular', 24;

这是第 3 节讲的「凭空造表」用法：每个 SELECT 造一行，UNION 把这些单行表并成一张表；只有第一行需要写 AS 来定列名，后面的行按位置对号入座。

Q1：取两列

题目：写一条查询，返回一张两列的表，内容是 boba 表的 name 和 ice 列。

按六步法：数据在 boba（第 1 步）；不用滤行（第 2 步跳过）；要 name 和 ice 两列（第 3 步）；不改名、不排序、不截断。

CREATE TABLE q1 AS
SELECT name, ice FROM boba;

结果是 10 行 2 列，跟原表行数一样——再强调一次，SELECT 只裁列，不删行。结果里会出现完全一样的行（比如两条 Black Milk Tea | Regular），因为原表里那两行的 sweetness 不同，只是被裁掉了。

Q2：按条件滤行，注意类型

题目：返回一张跟 boba 列相同的表，但只包含甜度为 100% 且 pieces 超过 20 的饮品。题面还特意提醒：小心，sweetness 列装的是字符串！

「列相同」→ 用 SELECT *；两个条件用 AND 连起来。

CREATE TABLE q2 AS
SELECT * FROM boba
WHERE sweetness = '100%' AND pieces > 20;

61A Special |100%|Extra  |25  → 甜度对，25>20 对 → 留
61A Special |75% |Extra  |24  → 甜度不对         → 删
61A Special |75% |Regular|27  → 甜度不对         → 删
Black       |0%  |Light  |24  → 甜度不对         → 删
Black       |100%|Regular|23  → 都对             → 留
Black       |100%|Regular|24  → 都对             → 留
Thanos      |50% |Regular|12  → 甜度不对         → 删
Wintermelon |25% |Regular|22  → 甜度不对         → 删
Wintermelon |50% |Light  |22  → 甜度不对         → 删
Wintermelon |75% |Light  |23  → 甜度不对         → 删

WHERE sweetness = 100      -- 字符串跟整数比，恒假 → 0 行
WHERE sweetness = '100'    -- 少了百分号，跟 '100%' 不相等 → 0 行
WHERE sweetness >= '100%'  -- 想「至少 100%」，实际按字符比 → 8 行

61A Special Milk Tea |100%|Extra  |25   ← 这 3 行是对的
Black Milk Tea       |100%|Regular|23
Black Milk Tea       |100%|Regular|24
61A Special Milk Tea |75% |Extra  |24   ← 下面 5 行全是混进来的
61A Special Milk Tea |75% |Regular|27
Wintermelon Milk Tea |25% |Regular|22
Wintermelon Milk Tea |50% |Light  |22
Wintermelon Milk Tea |75% |Light  |23

Q3：算出来的列 + 改名 + 排序

题目：造一张跟 boba 列相同的表，但每杯的 pieces 翻倍，并按 pieces 的数量排序。把翻倍后的那一列改名为 doubled_pieces。

三件事：算 2 * pieces、给它 AS doubled_pieces、ORDER BY。这里不能用 SELECT *，因为有一列要被替换掉，必须把四列全部列出来：

CREATE TABLE q3 AS
SELECT name, sweetness, ice, 2 * pieces AS doubled_pieces
FROM boba
ORDER BY pieces;

实测输出：

Thanos Milk Tea     |50% |Regular|24
Wintermelon Milk Tea|25% |Regular|44
Wintermelon Milk Tea|50% |Light  |44
Black Milk Tea      |100%|Regular|46
Wintermelon Milk Tea|75% |Light  |46
61A Special Milk Tea|75% |Extra  |48
Black Milk Tea      |0%  |Light  |48
Black Milk Tea      |100%|Regular|48
61A Special Milk Tea|100%|Extra  |50
61A Special Milk Tea|75% |Regular|54

Q4：连接两张表

题目：返回一张跟 orders 完全一样的表，但额外包含所点饮品的价格。挑战：用隐式连接和显式连接各写一遍。

第 1 步就定了全局：名字和冰量在 orders，价格在 menu，两张表都要，连接条件是「点的东西 = 菜单项」。

-- 隐式连接
CREATE TABLE q4 AS
SELECT o.name, o.item, o.ice, m.price
FROM orders AS o, menu AS m
WHERE o.item = m.item;

-- 显式连接（等价）
CREATE TABLE q4 AS
SELECT o.name, o.item, o.ice, m.price
FROM orders AS o
JOIN menu AS m
ON o.item = m.item;

两种写法实测输出完全一致：

Rabia  |Black Milk Tea      |Regular|5
Rebecca|Thanos Milk Tea     |Extra  |7
Richard|61A Special Milk Tea|Light  |8
Sriya  |Black Milk Tea      |Regular|5

注意 m.price 那个点记号：price 只在 menu 里有，不写点记号也不会歧义，但写上之后「这一列从哪来」就没有任何猜测空间了。第 9 节那个 no such column: o.price 的错误就是漏看了这一点。

Q5：自连接

题目：返回点了同一款饮品的人的名字。第一列叫 person1，第二列叫 person2。不能有重复的对，也不能把一个人跟自己配。

第 9 节已经把这道题完整推演过一遍了，这里只重贴答案：

CREATE TABLE q5 AS
SELECT o1.name AS person1, o2.name AS person2
FROM orders AS o1, orders AS o2
WHERE o1.item = o2.item AND o1.name < o2.name;  -- 或 o2.name > o1.name

结果一行：Rabia | Sriya。题面里那两句限制——「不能有重复的对」「不能跟自己配」——就是在提示你用严格小于而不是不等，这是本题唯一的技术点。

本讲小结

子句速查

运算符速查（对照 Python）

三句话

动手练习

用第 1 节和第 11 节的 cities、boba、orders、menu 四张表。建议在 code.cs61a.org 的 SQL 解释器里真的敲一遍再看答案。

练习 1（判断题）

下面这条查询会返回几行？不查数据，只靠推理。

SELECT * FROM boba, menu;

练习 2

下面两条查询，输出有什么不同？为什么？

-- A
SELECT name FROM cities ORDER BY latitude DESC LIMIT 2;

-- B
SELECT name FROM cities LIMIT 2;

练习 3

写一条查询，找出 boba 表里名字以 Milk Tea 结尾、且 ice 不是 'Regular' 的所有饮品的名字和冰量，按 pieces 从多到少排列。

SELECT name, ice
FROM boba
WHERE name LIKE '%Milk Tea' AND ice <> 'Regular'
ORDER BY pieces DESC;

61A Special Milk Tea |Extra    (pieces 25)
61A Special Milk Tea |Extra    (pieces 24)
Black Milk Tea       |Light    (pieces 24)
Wintermelon Milk Tea |Light    (pieces 23)
Wintermelon Milk Tea |Light    (pieces 22)

练习 4

下面这条查询想「找出每个人点的饮品价格」，但它有 bug。指出 bug 并说明症状（会报错还是给错结果？）。

SELECT o.name, m.price
FROM orders AS o, menu AS m;

练习 5

把 Q5 的条件从 o1.name < o2.name 改成 o1.name <= o2.name，结果会变成什么？改成 o1.item <> o2.item 呢？

Rabia   | Rabia
Rabia   | Sriya
Rebecca | Rebecca
Richard | Richard
Sriya   | Sriya