CS145 Intro to databases 学习笔记2——Relational Algebra、SQL

前言

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

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

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

瞅一眼个人博客页喵QWQ:CS145 Intro to databases 学习笔记2——Relational Algebra、SQL | Andrew的个人博客 (andreww1219.github.io)

一、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 E_2={\Pi }_{Shem{a}_1\cup Shem{a}_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))$$

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

$$E_1{\bowtie }_{\theta }{E}_2={\Pi }_{Shem{a}_1\cup Shem{a}_2}({\sigma }_{\theta }(E_1\times E_2))$$

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

$$E_1\cap E_2=E_1-(E_1-E_2)$$

二、 SQL

1. 基本查询

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

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

$${\Pi }_{A_1,A_2,...,A_n}({\sigma }_{condition}(R_1\times R_2\times ...\times R_n))$$

由关系代数表达式，不难得到语句执行的顺序为，先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

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中出现，示例如下：

--查询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保留重复行且不作排序

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

交运算：intersect

--查询申请了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

--查询申请了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

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后接表达式使其满足一定条件

--查询所在州有其他学校的学校的名字和所在州名
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)

--查询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)，需要重复书写

--查询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;

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

--查询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中，如：

--查询每个学校申请的人当中的最高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);

等价于

--查询每个学校申请的人当中的最高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可以省略

FROM E_1 JOIN  E_2   
ON condition

相当于$\theta$连接，即${\Pi }_{Shem{a}_1\cup Shem{a}_2}({\sigma }_{condition}(E_1\times E_2))$

FROM E_1 NATURAL JOIN  E_2   

相当于自然连接，即${\Pi }_{Shem{a}_1\cup Shem{a}_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人为指定：

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后的表达式是对聚合函数值得限制

示例：

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

--查询每个学生申请的学校的数量
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值

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

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

select distinct GPA
from student;

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

7. Modification Statements

对表作修改的语句有：

7.1 插入操作

INSERT INTO tableName 
VALUE (A_1, A_2, A_3, ...)  

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

示例：

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

7.2 删除操作

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

示例：

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

7.3 更新操作

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

示例：

--将所有申请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);