数据库笔记1-现代SQL

SQL用于关系数据库的声明式查询语言。它最初是在1970年代作为IBM System R项目的一部分开发的。IBM最初称它为“SEQUEL”（结构化英语查询语言）。这个名称在1980年代改为“SQL”（结构化查询语言）。

SQL并不是一种过时的语言。它每隔几年都会更新新功能。SQL-92是数据库管理系统（DBMS）声称支持SQL所必须支持的最低标准。每个厂商都在一定程度上遵循这个标准，但也有很多专有扩展。

以下列出了每个新版本SQL标准发布的一些主要更新：

• SQL:1999 正则表达式，触发器
• SQL:2003 XML，窗口，序列
• SQL:2008 截断，高级排序
• SQL:2011 时间数据库，流水线DML
• SQL:2016 JSON，多态表

关系语言

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

• 数据操作语言（DML）：包括SELECT、INSERT、UPDATE和DELETE语句。
• 数据定义语言（DDL）：包括表、索引、视图和其他对象的模式定义。
• 数据控制语言（DCL）：包括安全性和访问控制。
• 还包括视图定义、完整性和参照约束以及事务处理。 关系代数基于集合（无序且不允许重复）。SQL基于袋（无序但允许重复）。

聚合函数

聚合函数以一个元组包作为输入，然后生成一个单一的标量值作为输出。聚合函数几乎只能用于SELECT输出列表中。 - SUM(COL): 计算COL中的值的总和 - AVG(COL)：计算COL中值的平均值 - MIN(COL)：计算COL中的最小值 - MAX(COL)：计算COL中的最大值 - COUNT(COL)：计算关系中元组的数量

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

假设下面的表定义如下：

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

/* 在一个包含聚合函数的查询中，如果在SELECT子句中除了聚合函数的列外还包含了其他列，那么这些其他列的输出是未定义的。 */
SELECT AVG(s.gpa), e.cid
FROM enrolled AS e, student AS s
WHERE e.sid = s.sid;

/* 在一个包含聚合函数的SQL查询中，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子句根据聚合计算的结果来过滤输出结果。大多数SQL数据库支持，但并不和SQL标准兼容 */
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 子句中使用聚合函数而不是别称 */
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;

字符串操作

SQL标准中，字符串是大小写敏感且只能用单引号包含。下面有一些函数能在任何查询的部分操作字符串 - 模式匹配： LIKE关键词用于字符串匹配 % 匹配任何子字符串（包括空字符串） 而 _ 匹配一个字符 - 字符串函数 SQL-92 定义了字符串函数，比如SUBSTRING(S,B,E) 和 UPPER(S) - 连接： 两个竖杠|| 可以连接2个或者多个字符串组成一个字符串

输出重定向

除了将查询结果返回给客户端（例如终端）外，还可以告诉数据库管理系统（DBMS）将查询结果存储到另一个表中。这样你可以在后续查询中访问这些数据。

有两种方式可以存储查询结果：

1. 新建表： 将查询输出存储到一个新的（永久）表中。

   SELECT DISTINCT cid INTO CourseIds FROM enrolled;

   在这个示例中，SELECT DISTINCT cid INTO CourseIds FROM enrolled; 创建了一个名为 CourseIds 的新表，并将查询结果（enrolled 表中所有不重复的 cid）存储到这个新表中。

2. 已有表： 将查询输出存储到数据库中已经存在的一个表中。目标表必须具有与查询输出相同数量的列，并且列类型相同，但查询输出中的列名不需要与目标表中的列名匹配。

   INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled);

   在这个示例中，INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled); 将查询结果（enrolled 表中所有不重复的 cid）插入到已经存在的 CourseIds 表中。目标表 CourseIds 必须有与查询输出相同数量和类型的列，但列名可以不同。

这样做的好处是可以在后续的查询中使用这些存储的结果，避免重复执行相同的查询，提高查询效率。

输出控制

我们可以用 ORDER BY 排序结果，LIMIT 限制结果数量。

SELECT sid, grade FROM enrolled WHERE cid = '15-721'
ORDER BY grade;
/* 默认排序是升序，但可以指定降序 */
SELECT sid, grade FROM enrolled WHERE cid = '15-721'
ORDER BY grade DESC;

/* 可以指定多个关键列来多重排序 */
SELECT sid, grade FROM enrolled WHERE cid = '15-721'
ORDER BY grade DESC, sid ASC;

/* 也可以使用任意表达式来排序 */
SELECT sid FROM enrolled WHERE cid = '15-721'
ORDER BY UPPER(grade) DESC, sid + 1 ASC;

/* 可以使用LIMIT 和OFFSET 指定结果范围，LIMIT指定结果数量，OFFSET 指定开始的结果 */
SELECT sid, name FROM student WHERE login LIKE '%@cs'
LIMIT 20 OFFSET 10;

窗口函数

窗口函数可以滑动计算一组相关的元组，有点像聚合函数但元组并没有分组成一个单独的元组 有以下窗口函数 - ROW NUMBER: 当前行的数量 - RANK: 排序后的当前行的位置

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

分组（Grouping）

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

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

在这个查询中，ROW_NUMBER()函数会根据cid分组，并在每个分组内为每一行分配一个唯一的行号。

排序（Ordering）

你也可以在OVER子句中使用ORDER BY来确保结果的确定性排序，即使数据库内部发生了变化。

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

在这个查询中，ROW_NUMBER()函数会根据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;

这个查询的目的是找到每门课程中成绩第二高的学生。内部查询使用RANK()函数根据cid分组，并按成绩升序排序。然后，外部查询过滤出排名为2的行，即每门课程中成绩第二高的学生。

通过这些示例，可以看到如何使用窗口函数进行分组和排序，以及如何确保查询结果的确定性排序。这些技术对于执行复杂的分析和报告任务非常有用。

嵌套查询

在单个查询中调用其他查询以执行更复杂的逻辑。嵌套查询通常难以优化。外部查询的范围包含在内部查询中（即内部查询可以访问外部查询的属性），但反过来则不行。

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

• SELECT 输出目标： sql SELECT (SELECT 1) AS one FROM student;

• FROM 子句： sql SELECT name FROM student AS s, (SELECT sid FROM enrolled) AS e WHERE s.sid = e.sid;

• WHERE 子句： sql 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
);

以上这些示例展示了如何在SQL中使用嵌套查询来执行复杂的逻辑操作。通过这些嵌套查询，可以实现更精细的数据筛选和操作。

横向连接 (Lateral Joins)

LATERAL 操作符允许嵌套查询引用在其之前的其他嵌套查询中的属性。（你可以将其想象为一个 for 循环，允许你为表中的每个元组调用另一个查询。）

示例：

计算每门课程的注册学生人数和平均 GPA，并按注册人数降序排序。

SELECT * FROM course AS c
LATERAL (SELECT COUNT(*) AS cnt FROM enrolled
         WHERE enrolled.cid = c.cid) AS t1,
LATERAL (SELECT AVG(gpa) AS avg FROM student AS s
         JOIN enrolled AS e ON s.sid = e.sid
         WHERE e.cid = c.cid) AS t2
ORDER BY t1.cnt DESC;

在上述示例中，第一个 LATERAL 计算每门课程的注册学生人数，第二个 LATERAL 计算每门课程注册学生的平均 GPA。

### 公共表表达式 (Common Table Expressions)

公共表表达式（CTEs）是编写复杂查询时替代窗口函数或嵌套查询的一种方法。它们提供了一种在较大查询中编写辅助语句的方式。CTEs 可以被看作是限定于单个查询的临时表。

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

示例：生成一个名为 cteName 的 CTE，它包含一个单一属性值为 “1” 的元组。然后从这个 CTE 中选择所有属性。

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 引用自身。这使得在 SQL 查询中实现递归成为可能。使用递归 CTEs，SQL 具有图灵完备性，这意味着它在计算上与更通用的编程语言一样具有表现力（尽管可能更繁琐）。

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

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

这个查询使用递归 CTE 来生成从 1 到 10 的数字序列。首先选择 1，然后通过递归将计数器加 1，直到计数器达到 10。