CMU15445 Lecture 2 Advanced SQL

本节介绍SQL的高级用法

关系型语言

当用户使用声明式语言，他只需要说明他所需要的结果。DBMS会优化产生结果的过程。

Relational algebra是基于集合(无序，不可重复)，而SQL是基于背包(无序，可重复)

SQL历史

SQL来源于IBM的System R项目。其主要由三种命令组成：

• DML(Data Manipulation Language),SELECT, INSERT, UPDATE 和 DELETE等语句
• DDL(Data Definition Language),对于表，索引，视图，或其它对象的Schema的定义，就是建表，建索引的语句之类的
• DCL(Data Control Language),权限控制，访问控制
• 当然还有一些操作，诸如view的定义，完整性或引用约束，事务的开启与关闭等

SQL的一些高级语法

展示语法所用的数据库实例：

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

Aggreates

• MAX(col)，返回一列中值的最大值
• MIN(col)，返回一列中值的最小值
• AVG(col)，返回一列中值的平均值
• SUM(col)，返回一列中值的和
• COUNT(col),用于返回一列中值的#

如果想在student表中查询有着以'@cs'结尾的login的学生的数量，可以这样写(有三种方式)

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语句中使用多个aggregates函数

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

一些aggreagate函数支持DISTINCT关键字

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

输出相对于一个aggregate的其他列，会出错,该语句中e.cid是未定义的

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

这个时候需要用到group by命令，

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

如此一来就可以正确的显示了，将tuples按照group by划分为子集，再对子集做aggregate

另外，可以使用HAVING子句对aggregation之后的结果做过滤，HAVIN就像是GROUP BY的WHERE子句

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;

字符串操作

虽然MYSQL对于字母大小写不敏感，并且使用双引号与单引号皆可，但其他SQL不是这样的

模式匹配：

• "%"匹配任何的子串（包括空串）
• "_"匹配任意一个字符
  字符串连接：
• "||"用来连接两个字符串

输出重定向

除了将查询结果显示到终端，可以将结果放一个新的表中，可以用如下语句：

• 新建表：

SELECT DISTINCT cid INTO CourseIds FROM enrolled;

• 对于已经存在的表，可以将SELECT插入到数据库中已经存在的表中，SELECT的查询结果必须与表的列的数量与属性的类型一致，但是列名可以不一样：

INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled);

输出控制

可以用ORDER BY对SQL的结果进行排序，可以用谓词ASC与DESC与属性去对结果做想要的排序

SELECT sid FROM enrolled WHERE cid = '15-721' ORDER BY grade DESC;
SELECT sid 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子句限定查询结果的tuple的数量

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

也可以提供一个位移来到达获取一个范围的结果

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

如果不使用ORDER BY,那么使用LIMIT每次返回的结果可能不同

内嵌查询

inner queries和out queries组成一个nested queries，inner queries可以引用outer queries中的内容，但反过来不行
一个例子是，获取注册了15-445的学生的名字

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

内嵌查询支持的谓词：

• ALL: 所有 inner queries 返回的记录都必须满足条件
• ANY：任意 inner queries 返回的记录满足条件即可
• IN：与 = ANY() 等价
• EXISTS：inner queries 返回的表不为空
  ANY的用法，获取注册了15-445的学生的名字

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

ALL，IN的用法，找到至少参与一门课程的所有学生中，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
 );

NOT 的用法

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

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

DATA/TIME OPERAIONS

用来操作与修改DATA/TIME属性，每个SQL的用法差别极大

窗口函数

类似于聚合函数，但是会保留原tuple的所有元素

Common Table Expressions(CTE)

CTE就像对于一次查询的生成一个临时的表，使用WITH子句来绑定一个子查询的结果给WITH子句所给定的名字

关于递归CTE，可以查看这篇文章

参考

note2