SQL查询：集合操作与嵌套查询详解

### SQL 查询：集合操作与嵌套查询详解 #### 1. SQL 中的正则表达式与关系代数 在 SQL 里，随着文本数据重要性的提升，SQL:1999 引入了功能更强大的 `LIKE` 运算符的升级版——`SIMILAR`。这个运算符允许在文本搜索时使用丰富的正则表达式作为模式。这些正则表达式和 Unix 操作系统中用于字符串搜索的正则表达式类似，不过语法稍有不同。 SQL 的集合操作在关系代数中也存在，但主要区别在于，在 SQL 里它们是多重集操作，因为表是元组的多重集。例如下面的查询： ```sql FROM Sailors S WHERE S.sname LIKE 'B %B' ``` 满足该条件的水手只有 Bob，他的年龄是 63.5。 #### 2. UNION、INTERSECT 和 EXCEPT 操作 SQL 提供了三种集合操作构造，用于扩展基本查询形式。由于查询结果是行的多重集，所以使用并集、交集和差集等操作是很自然的。SQL 支持的这些操作分别名为 `UNION`、`INTERSECT` 和 `EXCEPT`。此外，SQL 还提供了其他集合操作，如 `IN`（检查元素是否在给定集合中）、`op ANY`、`op ALL`（使用比较运算符 `op` 将值与给定集合中的元素进行比较）以及 `EXISTS`（检查集合是否为空）。`IN` 和 `EXISTS` 可以加 `NOT` 前缀，其含义会相应改变。 下面通过几个具体查询来了解这些操作的应用： - **查询 Q5**：查找预订了红色或绿色船只的水手姓名。 ```sql SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND (B.color = 'red' OR B.color = 'green') ``` 这个查询可以很容易地通过 `WHERE` 子句中的 `OR` 连接词来表达。 - **查询 Q6**：查找预订了红色和绿色船只的水手姓名。 如果简单地将查询 Q5 中的 `OR` 替换为 `AND`，会得到预订了既是红色又是绿色船只的水手姓名，但由于船只的 `bid` 是主键，同一艘船不能有两种颜色，所以这个变体查询总是返回空集。正确的查询如下： ```sql SELECT S.sname FROM Sailors S, Reserves R1, Boats B1, Reserves R2, Boats B2 WHERE S.sid = R1.sid AND R1.bid = B1.bid AND S.sid = R2.sid AND R2.bid = B2.bid AND B1.color = 'red' AND B2.color = 'green' ``` 不过这个查询较难理解且执行效率不高。更好的解决方案是使用 `UNION` 和 `INTERSECT`。 查询 Q5 可以重写为： ```sql SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red' UNION SELECT S2.sname FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green' ``` 查询 Q6 可以重写为： ```sql SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red' INTERSECT SELECT S2.sname FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green' ``` 但这个查询存在一个小问题，如果有两个叫 Horatio 的水手，一个预订了红色船只，另一个预订了绿色船只，那么即使没有一个叫 Horatio 的水手同时预订了红色和绿色船只，`Horatio` 这个名字也会被返回。这是因为我们使用 `sname` 来识别水手，而 `sname` 不是 `Sailors` 表的键。如果在查询中选择 `sid` 而不是 `sname`，就可以计算出预订了红色和绿色船只的水手的 `sid`。 - **查询 Q19**：查找预订了红色船只但未预订绿色船只的水手的 `sid`。 ```sql SELECT S.sid FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red' EXCEPT SELECT S2.sid FROM Sailors S2, Reserves R2, Boats B2 WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green' ``` 实际上，由于 `Reserves` 关系包含 `sid` 信息，我们可以使用更简单的查询： ```sql SELECT R.sid FROM Boats B, Reserves R WHERE R.bid = B.bid AND B.color = 'red' EXCEPT SELECT R2.sid FROM Boats B2, Reserves R2 WHERE R2.bid = B2.bid AND B2.color = 'green' ``` 需要注意的是，`UNION`、`INTERSECT` 和 `EXCEPT` 可以用于任何两个可联合兼容的表，即具有相同的列数，且按顺序对应的列具有相同的类型。 另外，与基本查询形式默认不消除重复项（除非指定 `DISTINCT`）不同，`UNION` 查询默认会消除重复项。若要保留重复项，需要使用 `UNION ALL`，结果中一行的副本数量总是 `m + n`，其中 `m` 和 `n` 分别是该行在两个联合部分中出现的次数。类似地，`INTERSECT ALL` 会保留重复项，结果中一行的副本数量是 `min(m, n)`；`EXCEPT ALL` 也会保留重复项，结果中一行的副本数量是 `m - n`，其中 `m` 对应第一个关系。 #### 3. 嵌套查询 嵌套查询是 SQL 最强大的功能之一。嵌套查询是指一个查询中嵌入了另一个查询，嵌入的查询称为子查询。子查询本身也可以是嵌套查询，因此可以构建非常深层次嵌套结构的查询。在编写查询时，有时需要表达一个条件，该条件涉及一个需要先计算的表，用于计算这个辅助表的查询就是子查询，它会作为主查询的一部分出现。子查询通常出现在查询的 `WHERE` 子句中，有时也会出现在 `FROM` 子句或 `HAVING` 子句中。 ##### 3.1 嵌套查询介绍 下面通过几个例子来了解嵌套查询： - **查询 Q1**：查找预订了 103 号船只的水手姓名。 ```sql SELECT S.sname FROM Sailors S WHERE S.sid IN ( SELECT R.sid FROM Reserves R WHERE R.bid = 103 ) ``` 嵌套子查询计算出预订了 103 号船只的水手的 `sid` 集合，顶层查询则检索 `sid` 在该集合中的水手姓名。`IN` 运算符用于测试一个值是否在给定的元素集合中，这里使用 SQL 查询生成要测试的集合。如果要查找未预订 103 号船只的水手，只需将 `IN` 替换为 `NOT IN`。 理解嵌套查询的最佳方式是从概念上的评估策略来思考。在这个例子中，策略是检查 `Sailors` 表中的每一行，并针对每一行在 `Reserves` 表上评估子查询。一般来说，定义查询语义的概念评估策略可以扩展到嵌套查询