2. Relational Algebra to SQL Grammar

Relational Algebra

选择 (Selection), FROM

符号: S/ $\sigma$ (sigma), $S_{\text{condition}}(\text{relation})$
用法: 从关系中选择满足特定逻辑条件的行(元组)的子集;
条件构成: 条件是 bool combitation ( $\wedge$ 表示 AND, $\vee$ 表示 OR)的项;每个项的形式为 Attr1 <oper> Attr2 或 Attr <oper> value, 其中 <oper> 包括 {>, $\ge$ , =, $\le$ , <};
示例:
- $\sigma_{country=‘USA’ $\wedge$ sport=‘gymnastics’}(Athlete): 从 Athlete 关系中选择国家为 ‘USA’ 且运动为 ‘gymnastics’ 的运动员;
- 对应的SQL语句示例: SELECT * FROM Athlete WHERE sport = 'gymnastics' AND country = 'USA';

投影 (Projection), SELECT

符号: $\pi$ (pi)
用法: 从关系中 选择特定的列 (attributes), 并舍弃不需要的列;此操作还会移除重复的元组, 因为经典关系模型是一个 Set, set 属性确保没有重复的;
语法: $\pi$ Attr_list(relation)
示例:
- $\pi_{sport, country}(Athlete)$ : 从 Athlete 关系中选择 ‘sport’ 和 ‘country’ 列;
- 对应的SQL语句示例: SELECT sport, country FROM Athlete;

集合差 (Set-Difference)

符号: - (Minus)
用法: 返回属于第一个关系但不属于第二个关系的元组;
输入条件: 两个关系必须是 Union-Compatible 的, 即具有相同 number 和 type 的 attritbutes, 并且 same order;
结果字段名: 使用第一个输入关系的字段名;
语法: Relation1 - Relation2
示例:
- name - AthleteName: 返回 name 关系中的 record, 但不在 AthleteName 关系中, 即找出不是运动员的名字;
- 对应的SQL语句示例: SELECT * FROM name MINUS SELECT * FROM AthleteName; (在某些系统中也称为 EXCEPT);

并集 (Union)

符号: $\cup$
用法: 返回属于第一个关系或属于第二个关系的元组;
输入条件: 同 Minus 的要求, 两个关系必须是 Union-Compatible;
语法: Relation1 $\cup$ Relation2
示例:
- $\sigma$color='red' Boats $\cup$ $\sigma$color='green' Boats: 返回颜色为红色或绿色的船只;

重命名 (Rename)

符号: r/ $\rho$ (rho)
用法: 用于重命名关系或关系中的属性;
用例:
- 作为简写;
- 执行自连接(Self-joins), 也就是利用同一个表两次需要重命名;
- 当两个关系具有相同字段名时进行区分, 比如 Student.name 和 Employee.name 就需要重命名;
语法: $\rho_{AFTER}(BEFORE)$ 或 $\rho$ (AFTER, BEFORE) (重命名关系); 也可以重命名列, 如 A1.aid name1;
示例:
- $\rho$ (A1, Athlete) $\times$ $\rho$ (A2, Athlete): 将 Athlete 关系重命名为 A1 和 A2, 用于自连接;

笛卡尔积 (Cross-Product / Cartesian Product)

符号: $\times$
用法: 允许我们将两个关系组合起来;结果是第一个关系的每一行与第二个关系的每一行组合;
结果模式: 包含来自两个关系的所有字段, 名称默认继承(如果需要, 可以使用表名进行消歧);
语法: Relation1 $\times$ Relation2
示例:
- Athlete $\times$ Olympics: 将 Athlete 关系中的每一行与 Olympics 关系中的每一行组合;

连接 (Join)

符号: $\bowtie$
Conditional Join or $\Theta$ -Join: R $\bowtie_{condition}$ S, 其中 c 是连接条件, 先做cross product, 然后在结果上应用一个选择条件 condition;
Equijoin: R $\bowtie$ S, 其中连接条件是 R 和 S 之间所有 同名属性的相等;等价于 natural join;
Natural Join: R $\bowtie$ S, 自动基于所有 同名属性 进行连接;如果没有同名属性, 则结果等价于笛卡尔积;
示例:
- $\pi$ sname( $\sigma$ bid=103(Reserves) $\bowtie$ Sailors): 查找预订了船只 #103 的水手的名字;这里隐式地使用了连接, 将 Reserves 和 Sailors 根据共享属性 sid 进行连接;

交集 (Intersection)

符号: $\cap$
用法: 从基本操作构建的附加操作, 非常有用;返回同时属于两个关系的元组;
语法: Relation1 $\cap$ Relation2
示例:
- $\pi$ sname(Tempred $\cap$ Tempgreen $\bowtie$ Sailors): 查找预订了红色和绿色船只的水手的名字;Tempred 包含预订红色船只的水手 sid, Tempgreen 包含预订绿色船只的水手 sid;

除法 (Division)

符号: /
用法: 通常用于处理"找到所有满足某个条件 (for all) 的实体"这类查询;例如, “找到预订了所有船只的水手”;
定义: 如果关系 A 有两个字段 x 和 y, 关系 B 只有一个字段 y, 那么 A/B = { | $\forall$ $\in$ B, <x, y> $\in$ A}; 对于 B 中的每一个 y 元组(例如船只), A 中都存在一个 <x,y> 元组, 所有这样的 x 元组的集合;
语法: RelationA / RelationB
示例:
- 假设 sailor 表是一个 (sid, bid) 表示水手-船只租赁关系;
- $\rho_{sids}$ ( $\pi_{sid}$ , bid(Reserves) / $\pi$ bid(Boats)) $\bowtie$ Sailors: 查找预订了所有船只的水手的名字;
  理解这些算符对于理解SQL查询处理至关重要;

SQL Grammar

Constraints

对于一个变量如果有数值要求, 可以使用 CHECK 约束;例如:

1
2
3

-- CONSTRAINT age_country_valid   INFO: this is optional
CHECK ( age >= 18 
  AND country <> 'USA' )

注意这个是 ER 图中无法显示的内容

Assertions

如果要建立一个横跨多个表的约束, 可以使用 ASSERTION;例如:

There should be no athletes from the USA who are under 18 years old in the Beijing Olympics.

CREATE ASSERTION valid_age_country
CHECK ( NOT EXISTS (
  SELECT * FROM Athlete A, Olympics O
  WHERE A.country = 'USA'
    AND A.age < 18
    AND O.city = 'Beijing'
    AND O.year = 2008
));

但是 CHECK 和 ASSERTION 其速度非常的慢, 所以在实际应用中很少使用, 更多的是通过触发器(Triggers)来实现

Triggers

可以在 INSERT / UPDATE / DELETE 发生时自动执行程序化的检查;
大多数数据库都支持触发器;
缺点是触发器是过程式的 (procedural), 写起来比单纯的声明性约束复杂

trigger 一般有三部分组成:

event: 如何触发这个 trigger, 例如 INSERT / UPDATE / DELETE
condition: 触发器的条件 (在 event 发生后, 执行 condition 检查), 类似于 IF … THEN … 的逻辑
action: 当条件满足时执行的操作;
- 可以是更新, 插入, 删除, 或者抛出错误信息;
- 动作可以定义在 BEFORE 或 AFTER 事件发生时执行:
  - BEFORE Trigger: 在事件执行前运行;
  - AFTER Trigger: 在事件执行后运行;

触发器在执行时有两种粒度:

Row-level Trigger(行级触发器)
- 每修改一行就执行一次触发器;
- 例如: 更新 100 行数据, 就会触发 100 次;
- 常用于需要逐行检查和处理的约束;
Statement-level Trigger(语句级触发器)
- 每条 SQL 语句只触发一次;
- 不管修改多少行, 只运行一次;
- 常用于整体日志记录, 统计等;

CASCADE with Triggers (bind variable, `:OLD`, `:NEW` 关键字)

DISTINCT

ORDER BY 语法

通过 ORDER BY 子句可以对查询结果进行排序;默认是 升序 (ASC), 也可以指定 降序 (DESC);

1
2
3

SELECT * FROM Athlete
WHERE country = 'USA'
ORDER BY age DESC, sport ASC;

UNION 语法

Select all athletes from USA or Canada

1
2
3

SELECT * FROM Athlete WHERE country = 'USA'
UNION
SELECT * FROM Athlete WHERE country = 'Canada';

INTERSECT 语法

`HAVING` 子句

这个 HAVING 一般用在 aggregate 函数之后的输出结果的筛选

1
2
3

SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
HAVING condition;

比如在计算所有员工的工资总和时, 只想要那些总和大于等于 250000 的结果:

1
2
3

SELECT SUM(Salary) AS Total_Salary
FROM Employee
HAVING SUM(Salary) >= 250000;

Aggregate Functions 聚合函数

SQL 聚合函数用于对一组行执行计算并返回单个值;当我们需要在 SQL 数据库中对大型数据集进行汇总, 分析或分组时, 这些函数特别有用
与 SQL 中的 GROUP BY 子句一起使用, 以对每个组的数据进行汇总;常用的聚合函数包括 COUNT(), SUM(), AVG(), MIN() 和 MAX()

COUNT

COUNT(*): 统计所有行;
COUNT(column_name): 统计指定列中的非 NULL 值;
COUNT(DISTINCT column_name): 统计列中的唯一非 NULL 值;

比如在统计一个表中各个职业的人数时, 可以使用 COUNT 函数:

1 2	SELECT job, COUNT(*) AS num_athletes -- 这里 COUNT 统计对象是按照 job 进行分类的 FROM people

`GROUP BY` 语法

GROUP BY 语句将具有一个或多个列中相同值的行分组;它通常用于创建汇总, 例如按地区统计的总销售额或按年龄组统计的用户数量

与 SELECT 语句一起使用
在 WHERE 过滤后对行进行分组
可以与 SUM(), COUNT(), AVG()等聚合函数组合使用
使用 HAVING 子句过滤分组结果
在 WHERE 之后, 但在 HAVING 和 ORDER BY 之前

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2;

SQL 执行顺序

Query execution order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY