在之前的课程学习中，我们接触的两大语言——python和Scheme都属于命令型语言，其程序代码描述其运算过程，通过解释器评估并运行；而下面我们将接触另一种语言——结构化查询语言（SQL）。它属于声明式语言，其代码直接反映其希望得到的结果，而解释器则需要自主实现如何得到结果。
SQL是数据库系统中常用的语言，专门用于处理表格数据。当然，SQL存在若干变体，但是其核心语法基本一致。【本课程只关注通用的代码功能】
关于SQL的基本使用，可参见Kaggle SQL教程或SQLite官方文档。
这里我们使用SQLite进行SQL代码编写和运行，当然也可以使用在线SQL编辑器。

SQL基本操作

基于新数据创建表格

创建一个表格：CREATE TABLE ... AS SELECT，其中...为表名，SELECT之后为数据。
对于SELECT语句，其后面接着的是表格列的描述，具体格式为[expression] AS [name]，其中[name]为选择结果对应的列名；
- 如果要选取多个列，则用逗号分隔；
- AS [name]可以略去（此时列名默认取expression名称）；
- 如果expression是单个数字或字符串，那么就会返回只有一行的数据；如果要实现多行数据的构造，那么就需要使用多个SELECT语句并用UNION连接，后面的AS [name]不需要写（列名只需要在第一个SELECT中定义）
  
  注：通过UNION结合得到的表格数据默认会根据关键字升序排序。
- 最后以分号结束；
一个简单的示例：

CREATE TABLE parents AS
    SELECT "abraham" AS parent, "barack" AS child   UNION
    SELECT "abraham"          , "clinton"           UNION
    SELECT "delano"           , "herbert"           UNION
    SELECT "fillmore"         , "abraham"           UNION
    SELECT "fillmore"         , "delano"            UNION
    SELECT "fillmore"         , "grover"            UNION
    SELECT "eisenhower"       , "fillmore";

得到以下parents表格：

Parent	Child
abraham	barack
abraham	clinton
delano	herbert
fillmore	abraham
fillmore	delano
fillmore	grover
eisenhower	fillmore

另一种构建表格的方法是：
- 先建立一个空表：CREATE TABLE [table] ([column1] [type1],[column2] [type 2],...);；
- 再导入数据：INSERT INTO [table] VALUES (value1),(value2),...。（其中value为括号分隔的单行数据）

基于已有表格创建新表格

如果需要取已有表的数据，则格式为：SELECT [columns] FROM [table]，其中[columns]为列名（用*表示所有列），[table]为表名。
- 当然，后面还可以再加筛选条件，包括：
  - WHERE [condition]（支持逻辑运算符and、or、not，也可以用LIKE进行模糊匹配，如LIKE %dog%）；
  - 排序ORDER BY [order]（默认升序，后面加DESC表示降序）；
  - 行数限制limit [start, num]（取从start开始后的num行）；
- 最终会得到满足条件的包含特定列名的表格。
继续使用上面的示例：

SELECT * FROM parents;
SELECT child FROM parents WHERE parent = "abraham";

结果：

child
barack
clinton

表达式运算

SELECT语句中的[expression]支持算术运算（列名作为向量）。示例如下：

CREATE TABLE ints AS
SELECT "zero" AS word, 0 AS one, 0 AS two, 0 AS four, 0 AS eight UNION
SELECT "one", 1, 0, 0, 0 UNION
SELECT "two", 0, 2, 0, 0 UNION
SELECT "three", 1, 2, 0, 0 UNION
SELECT "four", 0, 0, 4, 0 UNION
SELECT "five", 1, 0, 4, 0 UNION
SELECT "six", 0, 2, 4, 0 UNION
SELECT "seven", 1, 2, 4, 0 UNION
SELECT "eight", 0, 0, 0, 8 UNION
SELECT "nine", 1, 0, 0, 8;

SELECT word, one+two+four+eight AS value FROM ints;
/*
返回结果（在sqlite中，也可以使用.mode column指令显示完整的表格）：
eight|8
five|5
four|4
nine|9
one|1
seven|7
six|6
three|3
two|2
zero|0
*/

SQL支持的算术运算包括：
- 组合运算：+，-，*，/（如果是整数则为地板除），%，AND，OR；
- 转化运算：abs，round，not，-（取负）；
- 比较运算：<，<=，>，>=，!=，<>（等价于!=），=（只要一个等号）。
当然，SQL表达式也支持字符串运算，下面按照使用频率进行排序：
1. 拼接两个字符串：SELECT "hello," || " world"，得到"hello, world"；
2. 字符串取子串&字符串查找：
```
CREATE TABLE phrase AS SELECT "hello, world" AS s;
SELECT SUBSTR(s, 4, 2) || SUBSTR(s, INSTR(s, " ") + 1, 1) FROM phrase;
```
  返回low。
3. 将字符串看作结构化数据（如列表）【一般不这么做】。

连接表格

SQL的另一大常见操作是连接两个表格（通过某种规则），使用JOIN语句。
格式：SELECT * FROM [table1] JOIN [table2] ON [condition]，即根据[condition]条件将[table2]拼接到[table1]上。（当然*可改为选择指定列）
- 如果将ON [condition]去掉，则会得到两个表格所有行组合得到的表格（一般不这么做）；
- [condition]示例：titles.tconst = ratings.tconst（即表格1.列名=表格2.列名）；另外，在JOIN [table]之后也可以为表格设置别名（JOIN [table] AS [t]），这样在后面的ON条件（以及前面的SELECT语句）中就可以使用别名调用对应列；
- 另一种格式：SELECT * FROM [table1] , [table2] WHERE [condition]【不太正式，不推荐】；
- 当然，也可以通过多个JOIN连接多个表格；
- 后面仍然可以跟ORDER BY、LIMIT等语句。
在某些情况下，我们还可以将表格进行自拼接（Self Joining），此时就必须对表格设置不同的别名。

分组聚合

对表格使用聚合函数则是SQL的另一个重要功能。
- 和python、R类似，SQL支持的聚合函数包括：MAX、MIN、SUM、AVG等等，最终返回的都是只有一行的表格。
而聚合函数与分组函数结合会最大程度发挥其作用。
- 分组的格式：SELECT [columns] FROM [table] GROUP BY [expression] HAVING [expression];，其中GROUP BY后面跟分组的列名，而HAVING为可选项，用于筛选需要的组别。
- 一种HAVING示例：HAVING COUNT(*)>1，表示筛选组内行数超过1的分组。【注意与WHERE条件的区别】

综上，一个完整的请求语句格式为：SELECT ... FROM ... (ON ...) (WHERE ...) (GROUP BY ...) (HAVING ...) (ORDER BY ...) (LIMIT ...);

表格与数据库

创建与删除表格

现在我们回到表格本身。之前我们只提到两种创建表格的方法（接AS SELECT语句或括号内设置列）。下面我们对CREATE TABLE语句进行补充（完整的CREATE TABLE参数可见SQLite，这里只涉及一小部分）：
- CREATE TABLE IF NOT EXISTS：若已存在同名表格，则不会创建新表且不会报错；
- 对于括号内的column-def，除了列名+类型之外，还可以设置：
1. UNIQUE：保证列中不会有重复元素（示例：CREATE TABLE numbers (n UNIQUE,note);）
2. DEFAULT：设置一列元素的初始化默认值（示例：CREATE TABLE numbers (n, note DEFAULT "No comment");）
删除表格的语句则相对简洁很多：DROP TABLE (IF EXISTS) [table-name]

修改表格

对表格的修改包括插入、删除与更新内容。

在表格中插入行
- 前面我们已经提到使用INSERT TABLE [table] VALUES (value)...插入数据。实际上，我们可以不插入完整的一行，只要将[table]替换为[table(columns)]，就可以只设置插入行指定列的数据（其他列使用默认值）。
- 另外，VALUES (value)...也可以换为SELECT语句（见上）。
更新表格
- 使用UPDATE语句，格式为：UPDATE [table] SET [column1] = [expression1](,[column2] = [expression2]...) (WHERE [condition])
在表格中删除行
- 使用DELETE语句，格式为：DELETE FROM [table] (WHERE [condition])
- 如果没有WHERE，则会删去所有行，得到一个空表。

数据库

数据库可以理解为表格的集合，一般以.db格式存储。其创建与使用见下。

在python中执行SQL语句

在python中，通过导入内置的sqlite3库，就可以执行sql语句（可参考官方文档）。示例：

import sqlite3
db = sqlite3.Connection("n.db") # 加载数据库（若不存在则创建新数据库）
db.execute("CREATE TABLE nums AS SELECT 2 UNION SELECT 3;")
db.execute("INSERT INTO nums VALUES (?),(?), (?);", range(4, 7)) # 在SQL语句中设置?，并在语句之外设置参数
print(db.execute("SELECT * FROM nums;").fetchall()) # [(2,),(3,),(4,),(5,),(6,)]
db.commit() # 保存表格到n.db

最后附上一个课程里的Blackjack小游戏，可以试着玩玩：

points = {'A': 1, 'J': 10, 'Q': 10, 'K':10}
points.update({n: n for n in range(2, 11)})

def hand_score(hand):
    """Total score for a hand."""
    total = sum([points[card] for card in hand])
    if total <= 11 and 'A' in hand:
        return total + 10
    return total

db = sqlite3.Connection('cards.db')
sql = db.execute
sql('DROP TABLE IF EXISTS cards')
sql('CREATE TABLE cards(card, place);')

def play(card, place):
    """Play a card so that the player can see it."""
    sql('INSERT INTO cards VALUES (?, ?)', (card, place))
    db.commit()

def score(who):
    """Compute the hand score for the player or dealer."""
    cards = sql('SELECT * from cards where place = ?;', [who])
    return hand_score([card for card, place in cards.fetchall()])

def bust(who):
    """Check if the player or dealer went bust."""
    return score(who) > 21

player, dealer = "Player", "Dealer"

def play_hand(deck):
    """Play a hand of Blackjack."""
    play(deck.pop(), player)
    play(deck.pop(), dealer)
    play(deck.pop(), player)
    hidden = deck.pop()

    while 'y' in input("Hit? ").lower():
        play(deck.pop(), player)
        if bust(player):
            print(player, "went bust!")
            return

    play(hidden, dealer)

    while score(dealer) < 17:
        play(deck.pop(), dealer)
        if bust(dealer):
            print(dealer, "went bust!")
            return

    print(player, score(player), "and", dealer, score(dealer))

deck = list(points.keys()) * 4
random.shuffle(deck)
while len(deck) > 10:
    print('\nDealing...')
    play_hand(deck)
    sql('UPDATE cards SET place="Discard";')

到这里CS61A的主要内容就基本结束了，剩下一些边边角角的东西（比如教材的第四章）笔者就不考虑记录了。

简单说说感想吧：这是笔者完结的第一个完整的UCB课程，成就感还是很强的。当初是看到CS自学指南的推荐，笔者自身又具备一定的编程基础，遂来学习。或许是学这门课的人挺多，网上的资料非常齐全，甚至还有中文版翻译教材（伟大！），加上完善的lab、homework与project评测系统，所以学起来不算很费劲。
- 学完之后的最主要感想是大开眼界！从没见过这样教编程的（至少国内没见过类似的课），难度方差不小，从基础入门到解释器构造（编程语言实现），可以说萃取了大学计算机课程之精华（程序设计基础+数据结构+编译原理/数据库）
- 另一方面，虽然说如今LLM已经能够解决很多代码问题，但笔者认为基本的coding能力以及debug能力依然很重要【不然LLM可能都无法理解人的需求】。~~再不济成为能工智人也未尝不可~~
- 总之学爽了！推荐推荐！

CS61A Chapter 16

CS61A Chapter 16

SQL基本操作

基于新数据创建表格

基于已有表格创建新表格

表达式运算

连接表格

分组聚合

表格与数据库

创建与删除表格

修改表格

数据库

在python中执行SQL语句