Tao's Blog
0%

[CMU-15-445/645]-Advanced SQL

Posted on Edited on In Comment Counts:
6.1k 6 mins.

这是我在学习Databases Systems CMU 15-445/645/ Fall 2019过程记录的一些笔记，这节课介绍了SQL的各种语法和应用，主要包括聚合/窗口函数、对字符串和日期的操作、输出控制和重定向以及常用表达式，一部分代码可能逻辑上有点模糊，最好自己跑一下深化理解。

前言

关于 relational language 的复习

在上一篇中主要介绍了Relational Language的一些基本概念以及相对应的Relational Algebra的一些基本用法。这里面有一个很重要的概念是在使用Relational language的时候只需要说明他们想要得到什么样的结果/答案，并不用去关心如何得到这个结果（包括怎么用relational algebra的基本操作去得到答案），这一部分应该由DBMS(数据库管理系统)去完成以及优化每一次查询操作（包括重新排列用户给的操作以及产生一个查找计划）。

SQL的历史

SQL最初是从IBM的 system R 中一个叫 “SEQUEL” 的语言演变而来，意思是 “Structred English Query Language”，并在19世纪79年代被 Oracle 采用。在1983年 IBM 发布了DB2，接下来在1986 年被ANSI 作为标准，ISO 在1987 年将他命名为 SQL (Structured Query Language)，目前最新的 SQL 标准到 2016 年，大部分 DBMS 至少支持 SQL-92。

SQL 实际上包括了三种语言，分别是：

  • Data Manipulation Language (DML)：插入，更新，删除数据
  • Data Definition Language (DDL)：创建定义数据库
  • Data Conrtol Language (DCL)：权限控制

除此之外还包括：查看定义（View Definition），添加控制条件保证数据完整性等等。另外，跟之前提到relational model不一样，SQL是基于 Bag(无序，但是允许重复数据) 的，而不是Set(无序且不允许重复)。

SQL

聚合（Aggregations） + 分组(Group By)

假设我们现在有一个数据库包括以下三个表格，主要表示了学生信息和课程信息以及他们之间关系：

Aggregates （聚合）

聚合操作是一系列函数的总称，这些函数的特点是取一系列 tuples 然后返回一个单元素(single value)，具体包括以下这些：

  • AVG(col): 返回该列的平均值，下面以此类推
  • MIN(col)
  • MAX(col)
  • SUM(col)
  • COUNT(col):返回该列元素个数

下面看一下这些函数的具体使用场景。

假设我们想要获取用“@cs”邮箱登录的学生个数，我们可能会用以下SQL语句，我们会注意到聚合函数只会用在 SELECT 语句的输出列表中（很显然，因为聚合函数只会产生一个元素所以肯定是最终结果）

1
2
SELECT COUNT(login) AS cnt
    FROM student WHERE login LIKE '%@cs'

我们还可以用以下两种语句来进一步简化：

1
2
3
4
5
6
7
// ver 2
SELECT COUNT(*) AS cnt
    FROM student WHERE login LIKE '@cs'

// ver 3
SELECT COUNT(1) AS cnt
    FROM student WHERE login LIKE '%@cs'

我们还可以同时使用多个聚合函数，假设我们想获得“@cs”邮箱登录的学生个数和他们的平均GPA，可以用以下方式查找，最后会输出含一个tuple（平均GPA和学生个数）的表：

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

聚合函数中 COUNT, SUM, AVG 还支持对其输入参数用关键字 DISTINCT 来进行去重，用以下方式可以获得用”@cs”邮箱登录的不重复学生人数：

1
2
SELECT COUNT(DISTINCT login) AS cnt
    FROM student WHERE login LIKE '%@cs'

在使用聚合函数的过程要注意在聚合函数以外的列的输出是未定义的(undefined)，具体参考以下例子

1
2
3
4
// 获取每门课上注册学生的平均GPA
SELECT AVG(s.gpa), e.cid
    FROM enrolled as e, student AS s
    WHERE e.sid = s.sid

程序在不同程序(pavlo，mysql, sqlite)的运行结果，可以发现对这种未定义的行为，不同程序的标准一样，可能会直接报错，也有可能随机挑选一个值输出，所以要尽可能避免出现这种情况：

分组（group by）

要实现上述提到的查找（获取每门课上注册学生的平均GPA），我们还需要加入分组操作来将输入的tuples分成不同部分并对每个部分进行聚合运算，代码和结果如下所示：

1
2
3
4
SELECT AVG(s.gpa), e.cid
    FROM enrolled AS e, student AS s
    WHERE e.sid = s.sid
    GROUP BY e.sid

注意：所有出现在 SELECT 输出语句的非聚合值必须都要出现 GROUP BY 语句里，如：

1
2
3
4
5
// s.name 要出现在 GROUP BY 语句里
SELECT AVG(s.gpa), e.cid， s.name
    FROM enrolled AS e, student AS s
    WHERE e.sid = s.sid
    GROUP BY e.sid

如果我们想对输出的table进行筛选，比如选出平均gpg高于3.9的，我们可能会想到以下语句，但是聚合计算的结果不能出现ADD语句里面，我们可以用HAVGING语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
// avg_gpa 不能出现在 AND 作为条件！
SELECT AVG(s.gpa) AS avg_gpa, e.cid
    FROM enrolled AS e, student AS s
    WHERE e.sid = s.sid
    AND avg_gpa > 3.9
    GROUP BY e.sid

// avg_gpa 可以出现在HAVING语句里作为筛选条件
SELECT AVG(s.gpa) AS avg_gpa, e.cid
    FROM enrolled AS e, student AS s
    WHERE e.sid = s.sid
    GROUP BY e.sid
    HAVING avg_gpa > 3.0;

成果运行结果如下：

通过这种方式可以很方便筛选我们想要的结果，而且不用考虑实现过程。

String & Date/Time Operation

下面对SQL里面有关string, date 和 time 的操作进行介绍

String 字符串操作

针对String的操作，不同DBMS的它的要求（单/双引号，区分/不区分大小写）不一样，具体参考下表

LIKE 关键字可以用来进行字符串匹配，匹配操作为：'%'匹配任何子串（包括空串），’_’ 匹配任何单个字符，具体例子如下：

1
2
3
4
5
6
7
// 找到所有 e.cid 以 '15-' 开头的
SELECT * FROM enrolled AS e
    WHERE e.cid LIKE '15-%'

// 找到所有登录名 以 '@c' + 一个字符串结尾的
SELECT * FROM student AS s
    WHERE s.login LIKE '%@c_'

SQL 还定义了一些字符串操作的函数既可以用来输出结果也能用来作为条件，部分例子如下：

1
2
3
4
5
6
7
// 取长度为5的子串
SELECT SUBSTRING(name, 0, 5) AS abbrv_name
    FROM student WHERE sid = 53688

// 转大写
SELECT * FROM student AS s
    WHERE UPPER(s.name) LIKE 'KAN%'

SQL标准里面规定可以用 '||' 来链接两个或多个字符串，其他不同DBMS也有不同的操作符/函数支持：

1
2
3
4
5
6
7
8
9
10
11
// SQL-92
SELECT name FROM student
    WHERE login = LOWER(name) || '@cs'

// MSSQL
SELECT name FROM student
    WHERE login = LOWER(name) + '@cs'

// mySQL
SELECT name FROM student
    WHERE login = CONCAT(LOWER(name), '@cs')

DATA/TIME 时间/日期操作

这一部分主要包括了SQL规定的一些关于操作和修改日期/时间的操作，可以作为输出或中间参数。同样，这些操作可能在不同DBMS上有不同的支持程度和具体语法，下面举一部分例子。

不同的DBMS有不同的方法获取获取当前日期，比如函数：NOW()、CURRENT_TIMESTAMP()，或者关键字CURRENT_TIMESTAMP;

也可以可以通过 EXTRACT 或者 '-' 或者 DATEDIFF 等等各种方法来获取日期信息（同样根据不同DBMS有不同语法）

Output Control + Redirection

Output redirection 输出重定向

SQL 支持将输出保存到另一个表格里面，这个操作需要满足两个条件：

  • 输出表格不能已经被定义
  • 输出表格需要和输入表格有相同类型并且列数相同

比如：

1
2
3
4
5
6
7
8
// SQL-02
SELECT DISTINCT cid INTO CourseIds
    From enrolled;

// mySQL
CREATE TABLE CourseIDs (
    SELECT DISTINCT cid FROM enrolled
);

同时也支持通过 INSECT 关键字将 SELECT 输出的 tuple 插入到已经存在的表格里：

  • 内部的 SELECT 必须输出和目标表格相同列数的 tuple
  • 不同 BDMS 对两者重复项有不同的处理选项和语法
1
2
INSERT INTO CourseIds
(SELECT DISTINCT cid FROM enrolled)

Output Control

SQL 支持用 ORDER 关键字根据 tuple 中的一列或多列的值进行排序，语法是

我们还可以用 LIMIT 来限制输出 tuples 的数量，并用 OFFSET 来规定一个输出范围

1
2
3
4
// LIMIT <count> [OFFSET]
SELECT sid, name FROM student
    WHERE login LIKE '%@cs'
    LIMIT 20 OFFSET 10

Nested Queries 嵌套查找

这里指的是查找指令中包括另一个查找指令，这种情况通常很难优化，但是会出现在各种地方，比如：

1
2
3
// 这里第一个出现的sid是关联在student上的，第二个是关联在enrolled上的
SELECT name FROM student WHERE
    sid IN (SELECT sid FROM enrolled)

如果有多个查找命令时，可以用以下几种方式指定输出的结果

  • ALL：输出的 tuple 需要满足所有查找命令
  • ANY：输出的 tuple 需要满足至少一个查找命令
  • IN：和ANY等效
  • EXISTS：至少返回一行

例子

1
2
3
4
5
6
7
8
9
10
11
12
// 获得上这门课的学生的姓名
SELECT name FROM student
    WHERE sid = ANY(
        SELECT sid FROM enrolled
        WHERE cid = '15-445'
    )

// ver2：查找命令也可以放在另一个查找命令的输出语句里
SELECT (SELECT S.name FROM student AS S
        WHERE S.sid = E.SID) AS sname
FROM enrolled AS E
WHERE cid = '15-445'

如果想要获得一个比较复杂的结果，比如找到一个学生，他至少注册了一门课，并且在这里面id是最高的，我们可能想到用以下方法

1
2
3
4
// 不合标准！s.name 需要出现在HAVING里，否则结果未定义
SELECT MAX(e.sid), s.name
    FROM enrolled AS e, student AS s
    WHERE e.sid = s.sid

这个时候我们可以用到ALL关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// sid 需要满足两个SELECT里面所有的表达式
SELECT sid, name FROM student
WHERE sid => ALL(
    SELECT sid FROM enrolled
)

// ver2: 也可以用IN
SELECT sid, name FROM student
WHERE sid IN (
    SELECT MAX(sid) FROM enrolled
)

// ver3: 通过排序+规定范围输出
SELECT sid, name FROM student
WHERE sid IN (
    SELECT sid FROM enrolled
    ORDER BY sid DESC LIMIT 1
)

假设我们需要找到所有没人注册的课程，可以用NOT EXIST：

1
2
3
4
5
6
// 内部的参数也可以用到外部查找命令的表格
SELECT * FROM course
WHERE NOT EXISTS(
    SELECT * FROM enrolled
    WHERE course.cid = enrolled.cid
)

Window Functions 窗口函数

窗口函数指的是对一系列 tuples 关于单行进行计算，和聚合函数类似，但是最后tuple不会集中在一个tuple上，语法是

1
2
3
4
// FUNC-NAME 可以是聚合函数或者别的特殊函数
// OVER 内部可以定义怎么切分数据或者对数据排序
SELECT ... FUNC-NAME(...) OVER (...)
    FROM tableNAME

上述的聚合函数可以包括之前提到的所有函数，特殊函数则是指：

  • ROW_NUMBER() 当前行的行号
  • RANK() 当前行的排序位置

OVER()的内部可以用PARTITION BY来指定特定的组，如：

假如我们想找到每门课上拿最高分的学生，可以：

Common Table Expressions 常见的表格表达式

除了用嵌套查找以外还能用一些常见表达式来进行查找，可以看成一个临时的表格，如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
// with 产生了一个tuple里面只有一个值1
WITH cteName AS (
    SELECT 1
)
SELECT * FROM cteName


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

// 之前提到的例子，找到一个学生，他至少注册了一门课，并且在这里面id是最高的
WITH cteSource (maxId) AS (
    SELECT MAX(sid) FROM enrolled
)
SELECT name FROM student, cteSource
WHERE student.sid = cteSource.maxId

// recursion，打印1到10
WITH RECURSIVE cteSource (counter) AS (
    (SELECT 1)
    UNION ALL
    (SELECT counter + 1 FROM cteSource
    WHERE counter < 10)
)
SELECT * FROM cteSource