CMU::15-445/645::Advanced SQL 笔记

高级 SQL

关系语言

Edgar Codd 在 20 世纪 70 年代初发表了关于关系模型的主要论文。他最初只定义了 DBMS 如何在关系模型 DBMS 上执行查询的数学符号。

用户只需要使用声明性语言（即 SQL）来指定他们想要的结果。DBMS 负责通过使用查询优化器重新组合操作确定产生该答案的最有效计划。

关系代数是基于 sets（无序的，没有重复的）。SQL 是基于 bags（无序的，允许重复）

SQL 历史

SQL。结构化查询语言

IBM 最初称其为 SEQUEL (Structured English Query Language)

由不同类别的命令组成。

• 数据操作语言（DML）。SELECT,INSERT,UPDATE,DELETE
• 数据定义语言（DDL）。模式定义。
• 数据控制语言（DCL）。安全，访问控制。

目前的 SQL 标准是 SQL:2016

各阶段引入的特性

• SQL:2016 → JSON, Polymorphic tables
• SQL:2011 → Temporal DBs, Pipelined DML
• SQL:2008 → TRUNCATE, Fancy sorting
• SQL:2003 → XML, windows, sequences, auto-gen IDs.
• SQL:1999 → Regex, triggers, OO

SQL 并没有死。它每隔几年就会有新的功能被更新。SQL-92 是一个 DBMS 必须支持的最低版本，以便声称他们支持 SQL。每个供应商都在一定程度上遵循该标准，但也有许多专有的扩展。

数据库例子

聚合

聚合函数接收一组 Tuple 作为其输入，然后产生一个单一的标量值作为其输出。只能在 SELECT 输出列表中使用。

举例：获取使用’@cs’登录的学生某个信息。以下三个查询是等价的。

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';

获取用’@cs’登录的学生数量和他们的平均 GPA。

select avg(gpa), count(sid) from student where login like '%@cs';

部分函数支持DISTINCT关键字

COUNT, SUM, AVG 支持 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：过滤聚合后的输出结果。就像一个GROUP BY的WHERE子句

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;

字符串操作

SQL 标准规定，字符串区分大小写，而且只能是单引号

事实上

有一些函数可以处理字符串，可以在查询的任何部分使用

select substring(name,0,5) as abbrv_name from student where sid = 53688
select * from student as s where upper(e.name) like 'kan%'

• substring
• upper
• lower
• concat

模式匹配

LIKE 关键字用于谓词中的字符串匹配

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

串联

两个竖条（"||"）将两个或多个字符串连接成一个单一的字符串

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')

时间日期操作

处理和修改日期/时间属性

可以在输出和谓词中使用

支持/语法有很大的不同

例子:获取一年中的第几天

pgsql

select date('2019-08-23') - date('2019-01-01') as days;

mysql

select datediff(date('2019-08-23')-date('2019-01-01')) as days

sqlite

not supprt

输出重定向

把查询结果存入另一张表

新表:将查询的输出存储到一个新的表中。

SELECT DISTINCT cid INTO CourseIds FROM enrolled;

现有的表:将查询的输出存储到数据库中已经存在的表中。该表目标表必须有与目标表相同数量和相同类型的列，但输出查询中的列名不需要匹配。

INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled);

输出控制

ORDER BY

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

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

默认情况下，DBMS 将返回由查询产生的所有 Tuple 。你可以使用 LIMIT 子句来限制结果 Tuple 的数量

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

也可以提供一个偏移量来返回结果中的一个范围

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

除非你使用带有LIMIT的ORDER BY子句，否则每次调用的结果中的 Tuple 可能是不同的。

嵌套查询

在其他查询中调用查询，以在单个查询中执行更复杂的逻辑。外部查询的范围包括在内部查询中（即内部查询可以访问外部查询的属性），但不能反过来。

内部查询几乎可以出现在查询的任何地方

从输出结果

SELECT (SELECT 1) AS one FROM student;

从子句

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

从 WHERE 子句

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

嵌套查询结果表达式

• ALL: 必须满足子查询中所有记录的表达式。
• ANY: 必须满足子查询中至少一条记录的表达式。
• IN: 相当于 ANY()
• EXISTS: 至少有一条记录被返回。

窗口函数

在一组 Tuple 中执行 “移动 “计算。像聚合操作，但它仍然返回原始 Tuple

Functions 可以是我们上面讨论的任何一个聚合函数。也可以是一个特殊的窗口函数。

• ROW NUMBER：当前行的编号。
• RANK：当前行的顺序位置。

Grouping The OVER clause specifies how to group together tuples when computing the window function. Use PARTITION BY to specify group.

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;

重要提示：DBMS 在窗口函数排序后计算 RANK，而在排序前计算 ROW_NUMBER

公共表表达式

公共表表达式（CTE）是窗口或嵌套查询的一种替代方法，可以用来编写更复杂的查询。我们可以把 CTE 看作是一个临时表，只用于一个查询。

WITH 子句将内部查询的输出与具有该名称的临时结果结合起来。生成一个名为 “cteName"的 CTE，它包含一个单一属性设置为 “1” 的 Tuple 。然后底部的查询只是返回 “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 ((SELECT 1) UNION (SELECT counter + 1 FROM cteSource WHERE counter < 10)) SELECT * FROM cteSource;