CMU15-445：Lecture #02 笔记

Lecture #02: Modern SQL

---

本文是对CMU15-445课程第二节笔记的一个粗略总结和翻译。仅供个人(M1kanN)复习使用。

---

1. Relational Languages

用户只需要使用声明性语言（即SQL）来指定他们想要的结果。DBMS
负责确定产生该答案的最有效计划。

关系代数基于 sets (unordered, no duplicates)。 SQL基于 bags (unordered, allows duplicates)

2. SQL History

• SEQUEL
  Structured English Query Language

  SQL
  Structured Query Language

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

  • Data Manipulation Language
    SELECT, INSERT, UPDATE, DELETE
  • Data Definition Language
    Schema definitions for tables, indexes, views, and other objects.
  • Data Control Language
    Security, access controls.

• SQL是不断发展的

3. Joins

结合一个或多个表的列，产生一个新的表。用来表达涉及跨越多个表的数据的查询，

• 本节课举例用的例子：

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

• 哪个学生在15-721拿到了A?

  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. Aggregates

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

• 函数：

  • AVG
  • MIN
  • MAX
  • COUNT

• 例子1：得到@cs登录的学生的人数

  SELECT COUNT(*) FROM student WHERE login LIKE '%@cs';
  -- *换成其他都行
  

• 例子2： 得到@cs登录的学生的人数和平均GPA

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

• 例子3：有些聚合函数支持DISTINCT关键字
  得到通过@cs登录的学生数量，以及他们的GPA, 要求学生不能重复！

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

• 例子4：得到在每个课上的学生的平均GPA

  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子句。
  例子5：获取学生平均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
  标准。为了使查询符合标准，我们必须在AVG(S.GPA)的主体中重复使用
  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;
  

5. String Operations

SQL标准是区分大小写的，而且只能是单引号！有一些函数可以处理字符串，可以在查询的任何部分使用。

• Pattern Matching: LIKE关键字
  • %
  • _
• String Function:
  SUBSTRING(S, B, E)
UPPER(S)
• Concatenation:
  ||

6. Date and Time

• 时间函数

  1. 当前日期时间
     NOW(), CURRENT_TIMESTAMP()
  2. 当前UNIX时间戳
     UNIX_TIMESTAMP()
  3. 当前日期
     CURRENT_DATE()
  4. 当前时间
     CURRENT_TIME()

• 日期时间转换函数

  1. 当前时间戳转换为北京时间
     FROM_UNIXTIME()

  2. 北京时间转换为时间戳
     UNIX_TIMESTAMP()

  3. 时间中解析年月日时间
     DATE_FORMAT(date, format)

     select DATE_FORMAT('2021-01-01 08:30:50','%Y-%m-%d')
     

• 日期时间运算函数

  1. 在某个时间的基础上加上或者减去某个时间
     DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)

  2. 返回两个日期值之间的天数
     DATEDIFF(expr1,expr2))

     select DATEDIFF('2021-01-02','2021-01-01')
     

  3. 时间差函数
     TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

     unit：天(DAY)、小时(HOUR），分钟(MINUTE)和秒(SECOND)，TIMESTAMPDIFF函数比DATEDIFF函数用起来更加灵活

7. Output Redirection

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

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

  SELECT DISTINCT cid INTO CourseIds FROM enrolled;
  

• Exustubg Table:

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

  INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled)
  

8. Output Control

因为SQL是无序的，我们可以用ORDER BY来对输出进行排序

ELECT sid, grade FROM enrolled WHERE cid = '15-721'
ORDER BY grade;

后面可以加DESC, ASC来指定排序策略

输出的数量可以用LIMIT n 进行指定

当然也可以用OFFSET 来提供一个bias。

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

9. Nested Queries

在其他查询中调用查询，在单个查询中执行更复杂的逻辑。嵌套查询往往难以优化。

外部查询的范围包括在内部查询中（即内部查询可以访问来自外部
查询），反之不行。

• 内部查询几乎可以出现在一个查询的任何部分。

  1. SELECT Output Targets

     SELECT (SELECT 1) AS one FROM student;
     

  2. FROM Clause:

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

  3. WHERE Clause

  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;
  

Nested Query Results Expressions:

• 关键字：

  • ALL
    Must satisfy expression for all rows in sub-query
  • ANY
    Must satisfy expression for at least one row in sub-query.
  • IN
    Equivalent to =ANY().
  • EXISTS
    At least one row is returned.

• 例子：
  找到所有没有学生注册的课

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

10. Window Function

A window function perform “sliding” calculation across a set of tuples that are related. Like an aggregation but tuples are not grouped into a single output tuple.

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

  1. ROW_NUMBER: 当前列的数字
  2. RANK: 当前列的顺序

• Grouping: OVER子句指定了在计算窗口函数时如何对图元进行分组。使用PARTITION BY来指定分组

  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。

  • 找到每门课程中成绩第二高的学生

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

11. Commom Table Expressions

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

可以理解为一个辅助表。

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查询中可以实现递归。有了递归的CTE，SQL被证明是图灵完备的，这意味着它在计算上的表现力不亚于更多的通用编程语言

• 例子：打印从1到10的数字

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