Skip to content

高级SQL

嵌套查询

例子

是一张学生表和参与课程表, 现在要你找到同时参加COMP5138和ISYS3207的学生的名字, 下列查询是否正确?

SELECT name FROM Student NATURAL JOIN Enrolled WHERE uos_code='COMP5138'
INTERSECT
SELECT name FROM Student NATURAL JOIN Enrolled WHERE uos_code='ISYS3207'

注意, 上述查询操作是错误的. 第一个查询会返回Adam, Lily; 第二个查询返回Lily, Adam. 最后的结果是Lily, Adam. 但是, 问题是, Lily是同一个Lily, Adam不是同一个Adam. 第一个查询返回的是来自澳大利亚的Adam, 第二个查询返回的是德国的Adam, 实际上, 无论是哪一个Adam都没有同时参与两门课, 只是两个人名字碰巧一样罢了.

解决这个问题的方法是使用嵌套查询, 即先基于sid选出同时参加两门课程的学生, 然后根据sid选出学生的名字.

SELECT name
FROM Student
WHERE sid IN (
    SELECT sid FROM Enrolled WHERE uos_code='COMP5138'
    INTERSECT
    SELECT sid FROM Enrolled WHERE uos_code='ISYS3207'
)

集合比较操作

SQL中用于子查询的集合比较操作有四种:

  • v [NOT] IN R: 判断值c是否在结果集合R中, 或者加上NOT, 判断是否不在集合中
  • [NOT] EXISTS R: 判断子查询R的结果集是否为空, R不为空为真, 或者NOT为空为真
  • v op ALL R: op是一个比较运算符, vR中每个元素的比较都成立则为真
  • v op SOME R: op是一个比较运算符, v至少对R中的一个元素比较成立则为真
例子
SELECT sid
FROM Enrolled
WHERE marks >= ALL (SELECT marks
                    FROM Enrolled)
SELECT name
FROM Student
WHERE sid NOT IN (SELECT sid
                  FROM Enrolled
                  WHERE semester='2024-S2')

视图

视图, View是数据库中的一种虚拟表. 它并不直接存储数据, 而是通过一条SQL查询定义的. 视图可以像普通表一样被查询, 但是它实际上是一个查询的结果集, 只有在使用的时候才会执行相应的查询操作. 视图的主要作用有:

  • 抽象化: 通过视图可以简化复杂的查询, 将多个表的复杂查询封装为一个虚拟表
  • 安全性: 通过视图可以控制用户访问的权限, 只让用户看到部分数据, 隐藏表中的其他信息
  • 复用性: 将频繁使用的查询逻辑封装为视图, 方便多次调用, 无需每次重写复杂的查询语句
例子
CREATE VIEW student_enrollment AS
        SELECT sid, name, title, semester
        FROM student NATURAL JOIN Enrolled NATURAL JOIN unitofstudy

聚合操作

SQL支持数种聚合操作. 包括COUNT, SUM, AVG, MAX, MIN, 除了COUNT之外, 所有的聚合操作都是用于单一属性的. 注意, 这些操作会应用于所有的重复项中, 除非使用DISTINCT声明.

例子

.

.

.

分组

除了对一个属性中进行聚合之外, 有时我们需要用GROUP BY对该属性下的某些组进行聚合.

例子

. 这里, Sales表格可以分为IBM和DELL, 我们对company进行分组, 用GROUP BY关键字, 然后会对IBM的amount, DELL的amount分别进行聚合, 而不是整一个属性amount进行聚合.

过滤

我们可以对HAVING子句对分组的结果进行过滤, 如HAVING SUM(amount) > 10000来筛选出销售总额大于10000的公司. 需要注意的是, 在SELECT或者HAVING子句中的字段必须是聚合函数的结果或是出现在GROUP BY子句中的字段.

评估流程

评估流程如所示.

例子

--->.

空值

一个元组可能有空值, 用NULL表示. NULL表示一个值不存在或者不可表示, 并不意味着值是0或者blank. IS NULLIS NOT NULL可用于检测空值.

例子
SELECT sid
FROM enrolled
WHERE marks IS NULL
Tip
  • 任何含有NULL的表达式的结果是NULL
  • 任何含有NULL的比较的结果是unkown
  • 如果WHERE表达式的结果是unkown, 则会被当作false处理

三值逻辑

三值逻辑, Three-valued Logic, 是一种非经典逻辑系统. 其中命题不仅仅有"真"和"假"两个值, 还引入了第三个逻辑值, 通常称为"未定"或者"未知". 这个逻辑系统用来处理传统二值逻辑无法应对的一些情况, 例如不完全信息, 不确定性或者模糊性.

  • OR:
    • (unknown OR true) = true
    • (unknown OR false) = unknown
    • (unknown OR unknown) = unknown
  • AND:
    • (true AND unknown) = unknown
    • (false AND unknown) = false
    • (unknown AND unknown) = unknown
  • NOT:
    • (NOT unknown) = unknown
Tip

除了COUNT(*), 像MIN, MAX, ...这些聚合函数都不会考虑NULL值, 也就是说, 它们只会计算不为NULL的数据. 若没有非NULL的值供聚合函数计算, 结果会返回NULL.

Comments