Lecture #02: 中级SQL

taoting 

本专栏是对CMU15445的笔记的翻译，原文地址： https:// 15445.courses.cs.cmu.edu /fall2020/notes/02-advancedsql.pdf

1 关系语言

在20世纪70年代，Edgar Codd发布了一篇关于关系模型的论文。最初，他只定义了数据库在关系模型上运行查询的数学符号。

用户只需要使用声明性的语言指定他们想要的结果（例如SQL）。数据库有责任去决定最有效率的计划并返回结果。

关系代数基于 set （无序，没有重复），SQL基于 bag （无序，有重复）。

2 SQL历史

用于关系型数据库的声明式查询语言，最初是在20世纪70年代作为IBM System R项目的一部分开发的。IBM最初称为“SEQUEL”（Structured English Query Language 结构化英文查询语言）。在20世纪80年代改名为“SQL”（Structured Query Language 结构化查询语言）。

这个语言由不同种类的命令组成：

Data Manipulation Language (DML 数据操纵语言)： SELETE（查询），INSERT（插入），UPDATE（更新）和DELETE（删除）。
Data Definition Language (DDL 数据定义语言)： 定义表，索引，视图和另外对象的模式。
Data Control Language (DCL 数据控制语言)： 安全，访问控制。

SQL不是一个死（一成不变）的语言，每隔几年就会添加新功能。SQL-92标准是一个数据库支持SQL的最低要求。每个供应商都在一定程度上遵循标准，但同时他们也有很多自己的扩展语法。

3 聚合

聚合函数接受一批元组作为输入并产生单个标量作为输出。聚合函数只能用于SELECT的输出列。

例子：获取学生用“@cs”登录的数量。下列3个查询是等价的：

SELECT COUNT(*) FROM student WHERE login LIKE '%@cs';
SELECT COUNT(login) FROM student WHERE login LIKE '%@cs';
SELECT COUNT(1) FROM student WHERE login LIKE '%@cs';

可以使用多个聚合函数在一个SELECT语句块中：

SELECT AVG(gpa), COUNT(sid)
  FROM student WHERE login LIKE '%@cs';

一些聚合函数支持DISTINCT关键字：

SELECT COUNT(DISTINCT login)
  FROM student WHERE login LIKE '%@cs';

同时输出聚合函数列和非聚合函数列是未定义行为（e.cid在这里是未定义行为）

注：未定义行为指该行为标准中未定义，不同数据库可能会返回不同的结果（甚至可能会报错）

SELECT AVG(s.gpa), e.cid
  FROM enrolled AS e, student AS s
 WHERE e.sid = s.sid;

因此，其他非聚合函数的列必须使用GROUP BY命令进行聚合：

SELECT AVG(s.gpa), e.cid
  FROM enrolled AS e, student AS s
 WHERE e.sid = s.sid
 GROUP BY e.cid;

HAVING：聚合后进行过滤，像WHERE子句中使用GROUP BY过滤这样

SELECT AVG(s.gpa) AS avg_gpa, e.cid
  FROM enrolled AS e, student AS s
 WHERE e.sid = s.sid
 GROUP BY e.cid
HAVING avg_gpa > 3.9;

4 字符串操作

SQL标准规定字符串 区分大小写 并 只能使用单引号 。有些函数可以在查询的任何地方使用并操纵字符串。

Pattern Matching（模式匹配）： LIKE关键字被用于在谓词中的字符串匹配。

“%” 匹配任何子串（包括空串）。
“_” 匹配单个字符。

Concatenation（连接）： 两个竖线（“||”）会连接2个或多个字符串变成1个字符串。

5 输出重定向

除了可以直接给用户返回结果，你还可以告诉数据库存储结果到另外一张表，然后你可以在后续查询中访问这些数据。

New Table（新表）： 存储查询的输出到新表中。

SELECT DISTINCT cid INTO CourseIds FROM enrolled;

Existing Table（已存在的表）： 存储查询的输入到已经存在表中。目标表必须有相同的列数和相同的类型，列名可以和输出列不匹配。

INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled);

6 输出控制

由于SQL是无序的，你必须使用ORDER BY子句去对结果进行排序：

SELECT sid FROM enrolled WHERE cid = '15-721'
  ORDER BY grade DESC;

你可以使用多个ORDER BY子句做更复杂的排序：

SELECT sid FROM enrolled WHERE cid = '15-721'
  ORDER BY grade DESC, sid ASC;

你可以使用任意的表达式在ORDER BY子句中：

SELECT sid FROM enrolled WHERE cid = '15-721'
  ORDER BY UPPER(grade) DESC, sid + 1 ASC;

通常，数据库会返回所有符合条件的元组。你可以使用LIMIT子句去限制返回元组的数量：

SELECT sid, name FROM student WHERE login LIKE '%@cs'
  LIMIT 10;

同时也可以提供offset去获得一个结果的区间：

SELECT sid, name FROM student WHERE login LIKE '%@cs'
  LIMIT 10 OFFSET 20;

除非你使用一个ORDER BY子句搭配LIMIT，否则数据库可能在每次查询会返回不同的元组，因为关系模型不在意顺序。

7 嵌套查询

嵌套查询可以使单个查询中运行更复杂的查询。外部查询的作用域在内部查询中（内部查询可以访问外部查询的属性），反之不行。

内部查询可以出现在查询的几乎所有地方：

SELECT输出目标：

SELECT (SELECT 1) AS one FROM student;

2. FROM子句：

SELECT name
  FROM student AS s, (SELECT sid FROM enrolled) AS e
 WHERE s.sid = e.sid;

3. WHERE子句：

SELECT name FROM student
 WHERE sid IN ( SELECT sid FROM enrolled );

例子：获取所有报名“15-445”的学生名字

SELECT name FROM student
 WHERE sid IN ( SELECT sid FROM enrolled WHERE cid = '15-445' );

注意，根据sid出现的不同位置，它拥有不同的作用域。

Nest Query Results Expressions（嵌套查询表达式）:

ALL：必须满足子查询中所有的行
ANY：必须满足子查询中至少1行
IN：等价于ANY()
EXISTS：至少1行被返回

8 窗口函数

跨元组执行“移动”计算。和聚合一样，但它依然返回原始元组。

Functions（函数）： 窗口函数可以是上面讨论的任意的聚合函数。同时还有一些特殊的窗口函数：

ROW_NUMBER：当前行的number
RANK：当前行的排序

Grouping（分组）： OVER子句指定当计算窗口函数的时候如何分组。使用PARTITION BY去指定分组。

SELECT cid, sid, ROW_NUMBER() OVER (PARTITION BY cid)
  FROM enrolled ORDER BY cid;

你也可以在OVER中使用ORDER BY去确定结果的顺序，即使数据库内部存储结构发生改变也不会影响结果。

SELECT *, ROW_NUMBER() OVER (ORDER BY cid)
  FROM enrolled ORDER BY cid;

重点：RANK函数在窗口函数排序后计算，ROW_NUBMER函数在排序前计算。

9 公用表表达式（CTE）

公用表表达式（CTE）是窗口函数和嵌套查询的替代方法去编写更加复杂的查询。可以认为CTE是在单次查询的一个临时表。

WITH子句将内部查询的输出绑定到临时的结果。

例子：生成一个CTE叫cteName包含1个tuple的1个属性，属性值为1。这个查询返回了cteName的所有属性及值。

WITH cteName AS (
    SELECT 1
SELECT * FROM cteName;

你可以绑定输出列的名字在AS之前：

WITH cteName (col1, col2) AS (
    SELECT 1, 2
SELECT col1 + col2 FROM cteName;

单个查询可以包括多个CTE的定义：

WITH cte1 (col1) AS (
    SELECT 1
cte2 (col2) AS (
    SELECT 2
SELECT * FROM cte1, cte2;

在WITH后添加RECURSIVE关键字允许CTE引用自己。

例子：打印1到10的序列

WITH RECURSIVE cteSource (counter) AS (