高级 SQL

今天的课纲 ，我们要讲 聚合函数 + Group By；处理字符串、⽇期和时间类型；然后还有 Output control。接着是更复杂的Nested Queries、Common Table Expressions(CTE)和window functions。

SQL概述

SQL的全称是Structured English Query Language，是IBM发明的一种声明式语言，实现了关系代数或关系演算。现在简称Structured Query Language

SQL是声明式的语言，就像上节课的关系代数，一切的具体实现由DBMS决定。

SQL特性

在2016版本中，他们添加了对JSON和多态表的⽀持，2003版本中则添加了对XML的⽀持，在 1999版本中，则⽀持了正则和触发器

它的特性在不断演化，但在标准出来之前，每个公司就已经发明了他们⾃⼰专有的东⻄——所以没人去遵守标准

SQL的内容

从技术层⾯⽽⾔，SQL并不是⼀⻔单⼀语⾔，它是DML、DDL、DCL的集合，还包含了一些其他东西比如：如如何定义视图，如何定义完整性以及奇怪的参照约束，以及事务

bag algebra

SQL基于bag algebra，元素可重复且无序，而不像list和set

如果我们想让我们的元素有顺序，或者你想保证没有重复元素出现，那么本质上来说，数据 库必须为我们做⼀些额外⼯作才能提供这种功能

只有在指明需要排序或去重时，SQL才会提供这些功能

e.g.

aggregations、DISTINCT、GROUP BY、HAVING

一个函数，将多个tuple作为输入，经过计算产生单个结果。

在SQL-92标准中定义了AVG()、MIN()、MAX()、SUM() 以及 COUNT()。这只是⼀种基础标准，在之后的标准和其他数据库系统中，它们还提供了例如中位数，取模以及 标准偏差之类的东⻄。某些数据库系统也允许你使⽤你⾃⼰的聚合函数

单个聚合函数

aggregation can only appear in the output list of the select statement，不能有其他字段

• 这里的 login 没有实际意义，可以用*代替，它代表了该tuple中的所有属性
• 实际上更进⼀步，我们可以将这个“*“⽤1替换，即每数⼀个tuple，tuple的数量就加1

为了得出答案，数据库系统可以去选择这种查询的不同变体，它们中的某些在性能上可能有不同的差异

多个聚合函数

我们可以在单个查询中放⼊多个聚合函数

DISTINCT 去重

某种意义上来说，这可能有点荒谬,因为，想必不存在有两个学⽣的登录账户是相同的情况，不然这就出问题了

如果加入其他非聚合字段…

DBMS可能不知道这个cid是哪个，所以这通常是不被允许的

MySQL原则上允许你⼲不合规的事，这是traditional mode，但也可以设置为更加严格。因此mysql下这种行为是被允许的

这里它随便挑了个cid，其他的DBMS可能不允许你这么做比如PostgreSQL

GROUP BY

修复这个问题的⼀个⽅法就是使⽤GROUP BY，然后对每个分组使用聚合函数。它使得我们可以在聚合函数中提取信息

HAVING

如果想要过滤的聚合结果该怎么办？where中无法访问聚合的结果，因为where先于聚合函数执行，它把过滤的元组交给聚合函数进行运算。

解决这个问题的⽅法就是使⽤HAVING

我们可以利用这一系列语句进行查询优化（后文）

strings

区分⼤⼩写，并且使⽤单引号来进⾏声明

SQLite 中字符串是⼤⼩写敏感的，但你可以⽤单双引号标记他们。
MySQL中是不区分⼤⼩写的，你可以使⽤单双引号

对于‘KaNyE’，在SQL标准下，你不得不使⽤upper函数进⾏⼤写转换，来做这项匹配⼯作。在MySQL中不⽤这么做

like 与通配符

他会尝试去匹配通配符所能匹配的字符串。出于某种原因，这⾥（数据库）使⽤⼀个%来代替我们常⽤ * 号所表达的意思，同样 _ 的意思是匹配⼀个字符

字符串函数

你所期望的substring，upperlower，trim之类的函数，它们都是SQL-92标准中的⼀部分

对于字符串函数，数学函数和⽇期函数来讲，它们可以出现在查询中的任何地⽅。

拼接字符串：||、+、concat

Postgres 和Oracle ⼤概是所有（数据库）系 统中遵循SQL标准最好的。SQL server 、DB2其次，SQLite还⾏，MySQL最差

在MySQL下不能⽤ + 、不能⽤ || ，只能通过concat()函数

日期类型：时间戳

可以单纯记录录时间戳⽽不⽤时间格式，它们可以切换时间粒度、可以跟踪：有时是秒、有时是毫秒或者是更细粒度的。

当你对时间进⾏操作和从它们中提取信息时，结果就会变得不可靠。对于所有不同系统来说，语法可以千差万别

now

• 例如mysql用NOW()返回时间戳，也有CURRENT_TIMESTAMP关键字
• 而SQLite使用CURRENT_TIMESTAMP()
• Postgres有CURRENT_TIMESTAMP关键字而没有函数

日期字符串转换为日期

EXTRACT 提取出这个⽇期中的天数，DATE 把日期字符串转换为日期。

日期减法：MySQL、SQLite、Postgres

用减法时可能会出现问题：

MySQL把字符串转换成数字类型20180829-20180101=728

想要修复需要：

转换为UNIX中的时间戳然后相减，再转换为天。另外还有一种方式 DATEDIFF

在SQLite中，它们并没有DATEDIFF函数，只能将当前时间戳转换为公历然后相减：

输出控制

输出重定向：INTO

一个查询产生了大量的结果，它会在终端上打印出来，你可把这些结果直接写入一张表、或者保存到数据库缓存中，即所谓的输出重定向。

你可以使⽤ INTO 关键字，它在执⾏过程中会将输出重定向到新表，也可以插入已存在的表（要求兼容）

事情变得很奇怪的地⽅在于，当你在你的表中设置⼀些约束后，当你再去写⼊数据时，SELECT 语句实际就会违反这些限制。。。课上并没说清什么意思

输出控制

ORDER BY

默认升序；可以根据多个属性排序

限制输出数量以及位置：LIMIT&OFFSET

跳过10个然后输出20个：

多次调用LIMIT的结果可能会不同——因为数据库是无序的

嵌套查询

将⼀个查询的输出结果作为另⼀个查询的输⼊，可以看作一个函数

• 大部分查询优化器都会对嵌套查询用JOIN尝试重写
• 很难优化，如果内外查询进行交叉引用
• 内查询可以出现的在任何地方

所有的这些操作符（例如，IN，EXISTS，ANY）所试图表达的是在内部查询中的是否存在 有满⾜条件的任何tuple，并不会真的每个都要去遍历⼀遍

构建嵌套查询：先外后内

⼀个构建嵌套查询的办法是先从外部查询开始构建，这点⼀直很重要

IN

内部查询输出的sid要和WHERE中sid的匹配

IN是最糟糕的操作符，实际上此处是有两个for循环，我们一遍又一遍地在执行内部产生完全相同输出的查询——实际只需要执行一次

ALL & ANY & EXIST

• ALL表示的是在内部⼦查询中满⾜我所有条件的每个tuple
• ANY表示的是当满⾜内嵌查询中表达式时，它会返回任意⼀条满⾜条件的数据

So，本质上来讲，IN和ANY⼲的是⼀样的事情

EXISTS则表示⾄少返回⼀⾏数据

=

内嵌查询⽆须写在WHERE⼦句中

实际上，它们可以放在任何地⽅

——这实际上是在找两个查询的sname的交集，先查enrolled表再查student表，在SELECT中做了一次JOIN

不得不使用嵌套查询

单个查询无法完成一些事情

最高sid的学生姓名

如果我们想得到最高sid的学生姓名，我们看起来会写一个这样的查询：

但这是不可行的，group by 看起来也不能奏效。So，我们知道我们想将sid和name作为输出

可以使用大于等于ALL，或者IN+MAX，或者LIMIT+ORDERBY

没被学⽣注册的课

NOT EXISTS即我们不想匹配内部查询所得结果的任何内容。

现在，此处我们实际上要做的，就是将内部查询中的课程id和外部查询的课程id绑定起来

window 函数

window函数会另外加一个列表示计算出的某些属性，这里OVER前的函数也可以是聚合函数

某些特殊的window函数所做的事情就是，⽐如为当前⾏引⼊⼀个⾏号

OVER

OVER⼦句的作⽤是⽤来表示我们该如何切分数据，有点像GROUP BY的意思。可以配合PARTITION BY和ORDER BY 使用

CTE common table expressions

CTE引⼊WITH⼦句，WITH⼦句会在你执⾏正常的查询之前先⼀步执 ⾏。

• WITH执行的查询结果仅能在该查询中使用
• 可以替代嵌套查询和视图，且看起来更加“模块化”

它和嵌套查询⾮常相似，但我们会在稍后看到⼀些情况，某些你可以在CTE中做到的事情，但换成嵌套查询却做不到

WITH输出的别名

e.g.