Advanced SQL

本节介绍 SQL 的部分复杂用法

回顾

在 Relational Model 下构建查询语句的方式分为两种:Procedural 和 Non-Procedural。第一节课中已经介绍了 Relational Algebra,它属于 Procedural 类型,而本节将介绍的 SQL 属于 Non-Procedural 类型。使用 SQL 构建查询时,用户只需要指定它们想要的数据,而不需要关心数据获取的方式,DBMS 负责理解用户的查询语义,选择最优的方式构建查询计划。

SQL 的历史

  • ”SEQUAL" from IBM's System R prototype

    • Structured English Query Language

    • Adopted by Oracle in the 1970s

  • IBM releases DB2 in 1983

  • ANSI Standard in 1986. ISO in 1987

    • Structured Query Language

当前 SQL 的标准是 SQL 2016,而目前大部分 DBMSs 至少支持 SQL-92 标准,具体的系统对比信息可以到这里查询。

SQL 主要特性介绍

SQL 基于

概要

  • Aggregations + Group By

  • String / Date / Time Operations

  • Output Control + Redirection

  • Nested Queries

  • Common Table Expressions

  • Window Functions

本节使用的示例数据库如下所示:

student(sid, name, login, gpa)

sid

name

login

age

gpa

53666

Kanye

[email protected]

39

4.0

53668

Bieber

[email protected]

22

3.9

enrolled(sid, cid, grade)

sid

cid

grade

53666

15-445

C

53688

15-721

A

course(cid, name)

cid

name

15-445

Database Systems

15-721

Advanced Database Systems

Aggregates

Aggregates 通常返回一个值,它们包括:

  • AVG(col)

  • MIN(col)

  • MAX(col)

  • SUM(col)

  • COUNT(col)

举例如下:

count.sql
SELECT COUNT(login) AS cnt
FROM student WHERE login LIKE '%@cs';
SELECT COUNT(*) AS cnt
FROM student WHERE login LIKE '%@cs';
SELECT COUNT(1) AS cnt
FROM student WHERE login LIKE '%@cs';
multiple.sql
SELECT AVG(gpa), COUNT(sid)
FROM student WHERE login LIKE '%@cs';
distinct.sql
SELECT COUNT(DISTINCT login)
FROM student WHERE login LIKE '%@cs';

aggregate 与其它通常的查询列不可以混用,比如:

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

不同 DBMSs 的输出结果不一样,严格模式下,DBMS 应该抛错。

Group By

group by 就是把记录按某种方式分成多组,对每组记录分别做 aggregates 操作,如求每门课所有学生的 GPA 平均值:

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

所有非 aggregates 操作的字段,都必须出现在 group by 语句,如下面示例中的 e.cid 和 s.name:

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

Having

基于 aggregation 结果的过滤条件不能写在 WHERE 中,而应放在 HAVING 中,如:

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;

Output Redirection

将查询结果储存到另一张表上:

  • 该表必须是已经存在的表

  • 该表的列数,以及每列的数据类型必须相同

SQL-92.sql
MySQL.sql
SQL-92.sql
SELECT DISTINCT cid INTO CourseIds
FROM enrolled;
INSERT INTO CourseIds (
SELECT DISTINCT cid FROM enrolled
);
MySQL.sql
CREATE TABLE CourseIds (
SELECT DISTINCT cid FROM enrolled
);

Output Control

Order By

语法:ORDER BY <column*> [ASC|DESC]

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

按多个字段分别排序:

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

Limit

语法:LIMIT <count> [offset]

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

Nested Queries

nested queries 包含 inner queries 和 outer queries,前者可以出现在 query 的任何位置,且 inner queries 可以引用 outer queries 中的表信息。

例 1:获取所有参与 '15-445' 这门课所有学生的姓名:

SELECT name FROM student
WHERE sid IN (
SELECT sid FROM enrolled
WHERE cid = '15-445'
);
SELECT (SELECT S.name FROM student AS S
WHERE S.sid = E.sid) AS sname
FROM enrolled AS E
WHERE cid = '15-445';

语法中支持的谓词包括:

  • ALL: 所有 inner queries 返回的记录都必须满足条件

  • ANY:任意 inner queries 返回的记录满足条件即可

  • IN:与 ANY 等价

  • EXISTS:inner queries 返回的表不为空

SELECT name FROM student
WHERE sid ANY (
SELECT sid FROM enrolled
WHERE cid = '15-445'
)

例 2:找到至少参与一门课程的所有学生中,id 最大的

SELECT sid, name FROM student
WHERE sid >= ALL (
SELECT sid FROM enrolled
);
SELECT sid, name FROM student
WHERE sid IN (
SELECT MAX(sid) FROM enrolled
);
SELECT sid, name FROM student
WHERE sid IN (
SELECT sid FROM enrolled
ORDER BY sid DESC LIMIT 1
);

例 3:找到所有没有学生参与的课程

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

nested queries 比较难被优化(具体原因暂不知道)

Common Table Expressions

在一些复杂查询中,创建一些中间表能够使得这些查询逻辑更加清晰:

WITH cteName AS (
SELECT 1
)
SELECT * FROM cteName
WITH cteName (col1, col2) AS (
SELECT 1, 2
)
SELECT col1 + col2 FROM cteName
WITH
cteName1(col) AS (SELECT 1),
cteName2(col) AS (SELECT 2)
SELECT C1.col + C2.col FROM cteName1 AS C1, cteName2 AS C2;

例 1:找到所有参与课程的学生中 id 最大的

WITH cteSource(maxId) AS (
SELECT MAX(sid) FROM enrolled
)
SELECT name FROM student, cteSource
WHERE student.sid = cteSource.maxId

例 2:打印 1-10

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

String Operations

DBMS

String Case

String Quotes

SQL-92

Sensitive

Single Only

Postgres

Sensitive

Single Only

MySQL

InSensitive

Single/Double

SQLite

Sensitive

Single/Double

Oracle

Sensitive

Single Only

如在 condition 中判断两个字符串忽略大小写后是否相等:

string-equal.sql
/* SQL-92 */
WHERE UPPER(name) = UPPER('KaNyE')
/* MySQL */
WHERE name = "KaNyE"

String Matching

SELECT * FROM enrolled AS e
WHERE e.cid LIKE '15-%';
SELECT * FROM student AS s
WHERE s.login LIKE '%@c_';

String Operations

SQL-92 定义了一些 string 函数,如

SELECT SUBSTRING(name, 0, 5) AS abbrv_name
FROM student WHERE sid = 53688;
SELECT * FROM student AS s
WHERE UPPER(e.name) LIKE 'KAN%';

不同 DBMS 有不同的 string 函数(没有完全遵从 SQL-92 标准),如连接两个 strings

concatenation.sql
/* SQL-92 */
SELECT name FROM student
WHERE login = LOWER(name) || '@cs';
/* MySQL */
SELECT name FROM student
WHERE login = LOWER(name) + '@cs';
SELECT name FROM student
WHERE login = CONCAT(LOWER(name), '@cs')

Date/Time Operations

不同的 DBMS 中的定义和用法不一样,具体见各 DBMS 的文档。

Window Functions

主要用于在一组记录中,对每一条记录进行一些计算,如:

例 1:

SELECT *, ROW_NUMBER() OVER () AS row_num
FROM enrolled;

会得到类似下表:

window functions - example1

例 2:

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

可是得到类似下表:

window functions - example 2

例 3:找到每门课获得最高分的学生

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

参考

slide, video