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是一个比较运算符, v对R中每个元素的比较都成立则为真
  • 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 NULL和IS 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