前言

本文是学习standford CS145 Introduction to Databases系列视频的第二篇笔记,内容主要包括第五章和第六章查询Relational Model的两种表达形式:

  1. 关系代数(Relational Algebra)
  2. SQL

视频链接:Introduction to Databases - Jennifer Widom - Stanford

一、Relational Algebra

1. 基本的运算符

在下述表述中,$E$代表表达式,可以为任意下述符号和关系(也就是表)的组合

$\Pi_{A_1, A_2, …, A_n} (E)$: project运算,$A_1, A_2, …, A_n$为列名,用于取出特定的几列

$\sigma_{condition} (E)$:select运算,$condition$表示应当满足的条件,用于筛选特定的几行

$E_1 \times E_2$:叉积运算取$E_1$的所有元组和$E_2$的所有元组做组合,结果行数为两者行数的乘积,列数为两者列数之和

$E_1 \cup E_2$:并运算,为二元运算符,其中,$E_1$和$E_2$应有相同的结构,结果为两者元组之和去除重复行

$E_1 - E_2$:差运算,为二元运算符,对于其中,$E_1$和$E_2$应有相同的结构,结果为前者去除重复行

$\rho_{R(A_1, A_2, …, A_n)} (E) $:rename运算,其中$R(A_1, A_2, …, A_n)$代表某种方法$R$,将$E$中的每一列依次重命名为$A_1, A_2, …, A_n$。

2. 其他常用运算符

以下常用运算符可以由基本的运算符所组合而成,不会增加原有符号体系的表达能力,只是便于书写和更精炼的表达

$E_1 \bowtie E_2$: 自然连接(natural join),相当于叉积运算后,取相同列名的值相同的元组作为结果,即

$E_1 \bowtie_{\theta} E_2$: $\theta$连接(theta join),相当于叉积运算后,保留满足条件$\theta$的元组作为结果,即

$E_1 \cap E_2$:交运算,,其中,$E_1$和$E_2$应有相同的结构,结果为两者元组的所有重复行,可以由并运算和差运算表达,如下:

二、 SQL

1. 基本查询

1
2
3
SELECT  A_1, A_2, ..., A_n 
FROM R_1, R_2, ..., R_n
WHERE condition

上述语句相当于关系代数中的:

由关系代数表达式,不难得到语句执行的顺序为,先FROM,再WHERE,最后再SELECT

注意:SQL基于multiset,允许重复元组的存在,而关系代数基于set,不允许重复元组

示例:

本节所有示例均基于三个表,结构如下
College(cName, state, enrollment) 主键为cName
student(sID, sName, GPA, sizeHS) 主键为sID
Apply(sID, cName, major, decision) 主键为sID, cName, major

1
2
3
4
5
6
7
8
select student.sID, sName, GPA, Apply.cName, enrollment
from student, college, Apply
where Apply.sID = student.sID and Apply.cName = College.cName --and连接多个条件
order by GPA desc; --order by attName 按属性升序, order by arrName desc 按属性降序

select sID, major
from Apply
where major like '%bio%'; --like接模式串可模糊查询

2. 表变量和集合运算符

2.1 表变量(Table Variables)

可以在FROM后的表达式,为每个关系赋别名将其区分开,由于FROM是较先执行的,别名可以在WHERE和SELECT中出现,示例如下:

1
2
3
4
--查询GPA相等的两个学生的信息
select s1.sID, s1.sName, s1.GPA, s2.sID, s2.sName, s2.GPA
from student sl,student s2
where s1.GPA = s2.GPA and s1.sID <> s2.sID;

后续可将复杂的查询结果命名为一个临时表,用于简化语句,详见”3.子查询 3.2from中的子查询”

2.2 集合运算符(Set Operators)

并运算:union去除重复行并作排序,union all保留重复行且不作排序

1
2
3
4
5
--在select后接 as newName重命名列名
--对结构相同的两个关系做并运算
select cName as name from Co1lege
union
select sName as name from Student;

交运算:intersect

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--查询申请了CS且申请了EE的学生的ID
select sID from Apply where major ='CS'
intersect
select sID from Apply where major = 'EE'

--相当于
select distinct A1.sID --加distinct是由于叉积运算时,其他属性如cName不同会使结果的sID存在重复
from Apply A1, Apply A2
where A1.sID = A2.sID and A1.major ='CS' and A2.major ='EE';

--in 在后续"子查询"中介绍
--也相当于
select sID
from Apply
where sID in (select sID from Apply where major = 'CS' )
and sID in (select sID from Apply where major = 'EE')

差运算:except

1
2
3
4
5
6
7
8
9
10
--查询申请了CS但没有申请EE的学生的ID
select sID from Apply where major ='CS'
except
select sID from Apply where major = 'EE'

--相当于
select sID
from Apply
where sID in (select sID from Apply where major = 'CS' )
and sID not in (select sID from Apply where major = 'EE')

3. 子查询(Subqueries)

3.1 where中的子查询

(1) in / not in

1
2
3
4
select sID
from Apply
where sID in (select sID from Apply where major = 'CS' )
and sID not in (select sID from Apply where major = 'EE')

(2) exists / not exists :往往在from为表起别名,在where的exists后接表达式使其满足一定条件

1
2
3
4
5
6
7
8
9
10
--查询所在州有其他学校的学校的名字和所在州名
select cName, state
from College c1
where exists (select * from College C2 where c2.state = c1.state);

--查询GPA最高的学生的名字
select sName
from student C1
where not exists (select * from Student C2 where C2.GPA > C1.GPA);
--不存在比C1的GPA要高的C2,即C1的GPA最高

(3) op any / op all (op为运算符:<, >, =, …):
any相当于数学意义的存在符号$\exists$
all相当于数学意义的任意符号$\forall$
那么有,not exp1 op any(exp2) 等价于 exp1 op all(exp2)
exp1 op any(exp2) 等价于 not exp1 op all(exp2)

1
2
3
4
5
6
7
8
9
--查询GPA最高的学生的名字及其GOA
select sName, GPA from Student
where GPA >= all(select GPA from Student) --这个学生的GPA大于任意其他学生的GPA

--查询注册人数最少的学校的名字
select cName
from College c1
where not enrollment <= any(select enrollment from College c2
where c2.cName <> c1.cName); --不存在另一个学校注册人数大于等于这个学校的注册人数

3.2 from中的子查询

在进行以下查询时,一些复杂的项,如GPA*(sizeHS/1000.0),需要重复书写

1
2
3
4
--查询GPA和scaledGPA相差超过1.0的学生的信息
select sID, sName, GPA, GPA*(sizeHS/1000.0) as scaledGPA
from student
where GPA*(sizeHs/1000.0) - GPA > 1.0 or GPA-GPA*(sizeHS/1000.0) > 1.0;

由于查询的结果也是一张表,我们可以将其命名,并嵌套用于新一轮的查询,改进如下

1
2
3
4
5
--查询GPA和scaledGPA相差超过1.0的学生的信息
select *
from (select sID, sName, GPA, GPA*(sizeHS/1000.0) as scaledGPA
from student) G
where abs(G.GPA - G.scaledGPA) > 1.0;

3.3 select中的子查询

当一个表达式恰好能返回一个结果时,可以将表达式置于select中,如:

1
2
3
4
5
6
7
--查询每个学校申请的人当中的最高GPA
select distinct college.cName, state, GPA
from College, Apply, Student
where College.cName = Apply.cName and Apply.sID = Student.sID
and GPA >= all(select GPA from Student, Apply
where Student.sID = Apply.sID
and Apply.cName = College.cName);

等价于
1
2
3
4
5
6
7
8
9
--查询每个学校申请的人当中的最高GPA
select cName, state,
(select distinct GPA
from Apply, Student
where College.cName = Apply.cName and Apply.sID = Student.sID
and GPA >= all(select GPA from Student, Apply
where Student.sID = Apply.sID
and Apply.cName = College.cName)) as GPA
from College;

4. 连接运算符(Join Operators)

JOIN需要接 ON E 或者 USING($A_1$, $A_2$, …, $A_n$ ),E为表达式,$A_1$, $A_2$, …, $A_n$为列名。通常ON和USING不能同时出现。

注意:所有的JOIN运算都不符合结合律

INNER JOIN

INNER可以省略

1
2
FROM E_1 JOIN  E_2   
ON condition

相当于$\theta$连接,即$ \Pi_{Shema_1 \cup Shema_2}(\sigma_{condition}(E_1 \times E_2) ) $

1
FROM E_1 NATURAL JOIN  E_2   

相当于自然连接,即$\Pi_{Shema_1 \cup Shema_2} (\sigma_{E_1.A_1 = E_2.A_1, E_1.A_2 = E_2.A_2,…, E_1.A_n = E_2.A_n} ( E_1 \times E_2))$,会隐式合并相同列(不建议),于是可以用USING人为指定:

1
FROM E_1 JOIN  E_2  USING(A_1, A_2)

LEFT/RIGHT/FULL OUTER JOIN

OUTER 可以省略
LEFT JOIN 保留左边,即使右边没有相匹配
RIGHT JOIN 保留右边。即使左边没有相匹配
FULL JOIN 左右都保留

5. 聚合查询(Aggregation)

5.1 聚合函数

  1. max:求最大值
  2. min:求最小值
  3. avg:求平均值
  4. sum:求和
  5. count:求计数

5.2 分组查询

GROUP BY 将FROM得到的关系做分组,通常,使用了GROUP BY后,SELECT中只能出现聚合函数以及分组所用的属性

HAVING 后接表达式对分组后的新关系再做限制,通常HAVING后的表达式是对聚合函数值得限制

示例:

1
2
3
4
5
--查询每个学校申请每个专业的人的最低GPA和最高GPA
select cName, major, min(GPA), max(GPA)
from Student, Apply
where Student.sID = Apply.sID
group by cName, major;

1
2
3
4
5
--查询每个学生申请的学校的数量
select Student.sID, count(distinct cName) --count distinct的组合很好用
from Student, Apply
where Student.sID = Apply.sID
group by student.sID;

6. NULL值

在查询中要特别注意NULL值:当一个属性为NULL值时,不会被WHERE中的数学表达式所限定,故有is null 专门判断NULL值

1
2
3
select sID, sName ,GPA
from Student
where GPA > 3.5 or GPA <= 3.5 or GPA is null;

SELCET中能够查询到值为空的属性:

1
2
select distinct GPA
from student;

1
2
3
4
5
6
7
8
9
10
+---+-------+
| | GPA |
+---+-------+
| 1 | NULL |
| 2 | 2.9 |
| 3 | 3.5 |
| 4 | 3.4 |
| 5 | 3.1 |
| 6 | 3.2 |
+---+-------+

7. Modification Statements

对表作修改的语句有:

7.1 插入操作

1
2
3
4
5
INSERT INTO tableName 
VALUE (A_1, A_2, A_3, ...)

INSERT INTO tableName ()
select-statement --将查询的结果插入到表中

示例:

1
2
3
4
5
--将没有申请学校的学生设置为申请了Carnegie Mellon的CS
insert into Apply
select sID, 'Carnegie Mellon', 'CS', null
from Student
where sID not in(select sID from Apply);

7.2 删除操作

1
2
DELETE FROM tableName () 
WHERE condition --删除满足条件的行

示例:

1
2
3
4
5
6
7
--删除申请表中申请专业超过两个的人
delete from Apply
where sID in
(select sID
from Apply
group by sID
having count(distinct major) > 2);

7.3 更新操作

1
2
3
4
UPDATE tableName
SET A_1 = E_1, A_2 = E_2, ...
WHERE condition
--将满足条件的行中的属性修改为指定值,这个指定值也可以是只返回一个值的查询语句

示例:

1
2
3
4
--将所有申请Carnegie Mellon并且GPA小于3.6的申请修改为经济学economics
update Apply
set major ='economics'
where cName ='Carnegie Mellon'and sID in (select sID from student where GPA < 3.6);