Relational Languages

  • 埃德加·科德在20世纪70年代早期发表了一篇关于关系模型的主要论文。他最初只定义了DBMS如何在关系模型DBMS上执行查询的数学符号。
  • 用户只需要使用声明性语言(即SQL)来指定他们想要的结果。DBMS负责确定最有效的计划来得出这个答案。
  • 关系代数是基于集合（无序，无重复）。SQL基于包（未订购，允许重复）。

SQL History

  • SQL: Structured Query Language
  • IBM originally called it “SEQUEL”.
  • Comprised of different classes of commands:
    • Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE.
    • Data Definition Language (DDL): Schema definition.
    • Data Control Language (DCL): Security, access controls.
  • SQL并不是一种不变的语言。它每隔几年就会更新一次新功能。SQL-92是DBMS为了声称它们支持SQL而必须支持的最小值。每个供应商都在一定程度上遵循该标准，但也有许多专有的扩展。

Aggregates 聚合函数

聚合函数将一些元组作为输入，然后生成单个标量值作为输出。只能在已选择的输出列表中使用。

  • 可以在一个选择语句中使用多个聚合；

  • 一些聚合函数支持不同的关键字；

  • select后边的未聚合函数必须出现在Group by子句中；

  • 对聚合函数结果的过滤可以在WHERE子句后边使用HAVING子句；

    1
    2
    3
    4
    5
    SELECT AVG(s.gpa) AS avg_gpa, COUNT(DISTINCT login), e.cid
    	FROM enrolled AS e, student AS s
    	WHERE e.sid = s.sid
    GROUP BY e.cid
    HAVING avg_gpa > 3.9;

String Operations 字符串操作

SQL标准说，字符串只区分大小写，并且仅支持单引号。有一些函数可以操作字符串，可以用于查询的任何部分。

在这里插入图片描述

  • Pattern Matching: LIKE关键字用于谓词中的字符串匹配

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

  • Concatenation: 两个竖条（“||”）将把两个或更多的字符串连接在一起成一个字符串

Output Redirection 输出重定向

  • 您可以告诉DBMS将结果存储到另一个表中，而不是将查询结果返回给客户端（例如，终端）。然后，您可以在后续的查询中访问此数据。

  • New Table：将查询的输出存储到一个新的（永久）表中。

    1
    SELECT DISTINCT cid INTO CourseIds FROM enrolled;

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

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

Output Control 输出控制

ORDER BY: 由于结果SQL是无序的，所以您必须使用Order BY子句来对元组进行排序。您可以使用多个顺序子句来打破关系或进行更复杂的排序，也可以在Order By子句中使用任何任意的表达式：

LIMIT: 默认情况下，DBMS将返回由查询产生的所有元组。可以使用limit子句限制结果元组的数量。还可以提供一个偏移量，以返回结果中的一个范围。

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

Nested Queries 嵌套查询

调用其他查询内部的查询，以在单个查询中执行更复杂的逻辑。外部查询的范围包含在内部查询中（即内部查询可以从外部查询访问属性）。

内部查询可以（几乎）出现在查询中的任何位置：

  • SELECT Output Targets:

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

Nest查询结果表达式：

  • ALL: 必须满足子查询中的所有行的表达式；

  • ANY: 必须满足子查询中至少一行的表达式；

  • IN: 相当于 =ANY()；

  • EXISTS: 至少返回一行；

Window Functions 窗口功能

跨一组元组执行“moving”计算。就像一个聚合一样，但它仍然返回原始的元组。

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

  • ROW NUMBER:当前行的编号。

  • RANK: 当前行的顺序位置。

Grouping： OVER子句指定了在计算窗口函数时如何将元组组合在一起。使用分区BY来指定组。

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

DBMS在窗口函数排序之后计算RANK，而在排序之前计算ROW数。

Common Table Expressions 通用表表达式

  • 通用表表达式(CTEs)是窗口或嵌套查询的替代方法，可以编写更复杂的查询。我们可以把CTE看作是一个查询的临时表。

  • WITH子句将内部查询的输出绑定到具有该名称的临时结果。示例：生成一个名为“cteName”的CTE，它包含一个具有单一属性设置为“1”的单一元组。然后，底部的查询只需返回“cteName”中的所有属性。

    1
    2
    3
    4
    WITH cteName AS (
    	SELECT 1 
    )
    SELECT * FROM cteName;

  • 在WITH之后添加RECURSIVE关键字允许CTE引用自己。示例：打印从1到10的数字序列。

    1
    2
    3
    4
    5
    6
    7
    WITH RECURSIVE cteSource (counter) AS ( (SELECT 1)
        UNION
        (
            SELECT counter + 1 FROM cteSource
       		WHERE counter < 10)
        )
    SELECT * FROM cteSource;