CMU15445-lecture2

第二课：现代SQL

1 关系语言

埃德加·科德在20世纪70年代初发表了一篇关于关系模型的重要论文。起初，他只定义了数据库管理系统（DBMS）如何在关系模型上执行查询的数学表示。用户只需使用声明性语言（即SQL）指定他们想要的结果。DBMS负责确定生成该答案的最有效计划。关系代数基于集合（无序，无重复项）。SQL基于多重集（无序，允许重复项）。

2 SQL历史

关系数据库的声明式查询语言。它最初在1970年代作为IBM System R项目的一部分进行开发。IBM最初将其命名为“SEQUEL”（结构化英语查询语言）。该名称在1980年代更改为“SQL”（结构化查询语言）。

该语言由不同类型的命令组成：

数据操作语言（DML）：SELECT、INSERT、UPDATE和DELETE语句。
数据定义语言（DDL）：用于定义表、索引、视图和其他对象的模式定义。
数据控制语言（DCL）：用于安全性和访问控制。

SQL并不是一门死语言，它每隔几年都会更新新的功能。SQL-92是一个DBMS必须支持的最低标准，以便声称支持SQL。每个供应商在一定程度上遵循这个标准，但也存在许多专有扩展。

每个SQL标准新版发布时都会带来一些重大的更新，一些主要的更新如下所示：

SQL:1999 正则表达式、触发器
SQL:2003 XML、Windows、序列
SQL:2008 截断、高级排序
SQL:2011 时态数据库、通道化DML
SQL:2016 JSON、多态表

3 Joins

合并一个或多个表的列，生成一个新的表。用于表示涉及跨多个表的数据的查询。

例如：哪些学生在15-721课程中得到了A？

示例数据库：

CREATE TABLE student ( 
	sid INT PRIMARY KEY, 
	name VARCHAR(16), 
	login VARCHAR(32) UNIQUE, 
	age SMALLINT, 
	gpa FLOAT 
); 
CREATE TABLE course ( 
	cid VARCHAR(32) PRIMARY KEY, 
	name VARCHAR(32) NOT NULL 
); 
CREATE TABLE enrolled ( 
	sid INT REFERENCES student (sid), 
	cid VARCHAR(32) REFERENCES course (cid), 
	grade CHAR(1) 
);

查询：

SELECT s.name
FROM enrolled AS e, student AS s
WHERE e.grade = 'A' AND e.cid = '15-721'
AND e.sid = s.sid;

4 聚合

聚合函数以元组的包作为输入，然后产生一个标量值作为输出。聚合函数几乎只能在SELECT输出列表中使用。

AVG（COL）：COL中值的平均值
MIN（COL）：COL中的最小值
MAX（COL）：COL中的最大值
COUNT（COL）：关系中元组的数量

示例：获取以‘@cs’登录名的学生人数。

以下三个查询是等价的：

1
2
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 语句可以包含多个聚合：

示例：使用“@cs”登录获取学生数量及其平均 GPA。

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

一些聚合函数（例如COUNT,SUM,AVG）支持DISTINCT关键字：

示例：通过’@cs’登录获取独特学生数量和他们的平均GPA。

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

聚合操作之外的其他列输出未定义（在下面的示例中e.cid未定义）。

示例：获取每门课程中学生的平均GPA。

1
2
3

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

在SELECT输出子句中的非聚合值必须出现在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子句根据聚合计算结果对输出结果进行过滤。这使得HAVING的行为类似于GROUP BY的WHERE子句。示例：获取平均学生GPA大于3.9的课程集合。

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标准。要使查询符合标准，我们必须在HAVING子句的主体中重复使用AVG(S.GPA)。

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

5 字符串操作

SQL标准规定字符串是区分大小写且只能用单引号括起来。在查询的任何部分中可以使用用于操作字符串的函数。模式匹配：LIKE关键字用于谓词中的字符串匹配。

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

字符串函数 SQL-92定义了字符串函数。许多数据库系统除了标准函数外还实现了其他函数。标准字符串函数的例子包括SUBSTRING(S, B, E)和UPPER(S)。

连接：两个竖杠（“||”）将两个或多个字符串连接成一个字符串。

6 日期和时间

操作日期和时间属性的操作。可用于输出或谓词。关于日期和时间操作的具体语法在不同的系统之间差异很大。

7 输出重定向

可以让数据库管理系统将查询结果存储到另一个表中，而不是将结果返回给客户端（例如终端）。然后，您可以在后续查询中访问这些数据。

新表：将查询的输出存入一个新表中

1	`SELECT DISTINCT cid INTO CourseIds FROM enrolled;`

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

1	`INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled);`

8 输出控制

由于结果集中的SQL是无序的，我们必须使用ORDER BY子句来对元组进行排序：

1 2	`SELECT sid, grade FROM enrolled WHERE cid = '15-721' ORDER BY grade;`

默认的排序顺序是升序（ASC）。我们可以手动指定DESC来反转顺序。

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

我们可以使用多个ORDER BY子句来打破平局或进行更复杂的排序：

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

在ORDER BY子句中，我们还可以使用任意的表达式。

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

默认情况下，DBMS 将返回查询生成的所有元组。我们可以使用 LIMIT 子句来限制结果元组的数量：

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

我们还可以提供一个偏移量来返回结果中的一段范围：

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

除非我们在LIMIT中使用ORDER BY子句，否则数据库管理系统在每次调用查询时可能会产生不同的结果元组，因为关系模型不强制排序。

9 嵌套查询

在单个查询中调用其他查询来执行更复杂的逻辑。嵌套查询通常很难优化。

外部查询的范围包括内部查询（即内部查询可以访问外部查询的属性），但反过来则不行。

内部查询可以出现在查询的几乎任何部分中：

选择输出目标

1	`SELECT (SELECT 1) AS one FROM student;`

FROM从句

1
2
3

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

WHERE从句

1 2	`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的范围也会有所不同。

例如：找到已经至少选修了一门课程的学生记录中id最高的学生。

SELECT student.sid, name 
	FROM student 
	JOIN (SELECT MAX(sid) AS sid 
				FROM enrolled) AS max_e 
    ON student.sid = max_e.sid;

嵌套查询结果表达式：

ALL（全部）：必须满足子查询中所有行的表达式。
ANY（任意）：必须至少满足子查询中的一行的表达式。
IN（在范围内）：等同于=ANY()。
EXISTS（存在）：至少返回一行。

例子：找出所有没有学生注册的课程。

SELECT * FROM course 
	WHERE NOT EXISTS( 
		SELECT * FROM enrolled 
			WHERE course.cid = enrolled.cid 
);

10 窗口函数

一个窗口函数在一组相关的元组上执行“滑动”计算，类似于聚合，但是元组并不被分组为单个输出元组。

函数：窗口函数可以是我们上面讨论过的任何聚合函数。还有一些特殊的窗口函数：

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

分组：通过OVER子句指定在计算窗口函数时如何将元组分组在一起。使用PARTITION BY来指定分组。

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

即使数据库在内部进行更改，我们也可以在OVER函数中添加ORDER BY来确保结果的确定性排序。

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

重要提示：在窗口函数排序之后，DBMS会计算排名（RANK），而在排序之前，它会计算行号（ROW NUMBER）。

例如：找到每门课程中成绩第二高的学生。

SELECT * FROM ( 
	SELECT *, RANK() OVER (PARTITION BY cid 
					ORDER BY grade ASC) AS rank 
    FROM enrolled) AS ranking 
WHERE ranking.rank = 2;

11 通用表表达式

在编写更复杂的查询时，通用表表达式（CTE）是窗口或嵌套查询的替代方法。它们提供了一种在更大的查询中为用户编写辅助语句的方法。CTE 可以被视为范围限定为单个查询的临时表。

WITH 子句将内部查询的输出绑定到具有该名称的临时结果。

示例：生成一个名为 cteName 的 CTE，其中包含单个元组，且单个属性设置为“1”。选择此 CTE 中的所有属性。 cteName。

WITH cteName AS ( 
		SELECT 1 
) 
SELECT * FROM cteName;

我们可以在AS之前将输出列绑定到名称上。

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

一个查询可以包含多个CTE声明：

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

在WITH关键字后加上RECURSIVE关键字使得CTE能够引用其自身。这样可以在SQL查询中实现递归。通过递归CTE，SQL可以被证明是图灵完备的，这意味着它在计算表达能力上与更通用的编程语言一样强大（虽然可能稍微复杂一些）。

例如：打印从1到10的数字序列。

WITH RECURSIVE cteSource (counter) AS ( 
		( SELECT 1 ) 
		UNION 
		( SELECT counter + 1 FROM cteSource 
			WHERE counter < 10 ) 
) 
SELECT * FROM cteSource;

cmu15445

#数据操作语言 #SQL #数据定义语言 #数据控制语言 #Joins #聚合函数 #字符串操作 #日期和时间 #嵌套查询 #窗口函数 #通用表表达式

CMU15445-lecture2

https://www.polarishaw.top/2024/06/06/第二课：现代SQL/

作者

Polaris

发布于

2024年6月6日

许可协议

茶6.3-6.7 上一篇

CMU15445-lecture1 下一篇