计算机三级数据库技术备考笔记（六）

第六章 高级数据查询

• 一般数据查询功能扩展
  • 使用TOP限制结果集
    • 当使用SELECT语句进行查询时,有时只希望列出结果集中的前几行数据,而不是全部数据。这时就可以使用TOP谓词来限制输出的结果。
    • 使用TOP谓词的语法格式为
      TOP n [percent ] [WITH TIES ]
      ​其中，n为非负整数。
    • 1) TOP n : 取查询结果的前n行数据;
    • 2) TOP n pereent: 取查询结果的前n%行数据;
    • 3) WITH TIES:表示包括最后一行取值并列的结果。
    • TOP谓词写在SELECT单词的后边(如果有DISTINCT的话,则TOP写在DISTINCT的后边)、查询列表的前边。
    • 注意: 在使用TOP谓词时,应该与ORDER BY子句一起使用,这样的前几名才有意义。当使用WHTH TIES选项时则要求必须使用ORDER BY子句。
  • 使用CASE函数
    • 可以在查询语句中使用CASE函数,以达到分情况显示不同类型的数据的目的。CASE函数是一种多分支表达式,它计算条件列表并返回多个可能的结果表达式中的一个。
    • CASE数分为简单CASE数和搜索CASE数两种类型。
    • 简单CASE数的语法格式为:
      CASE 测试表达式
      WHEN 简单表达式1 THEN 结果表达式1
      WHEN 简单表达式2 THEN 结果表达式2
      …
      WHEN 简单表达式n THEN 结果表达式n
      [ELSE 结果表达式 n+1 ]
      ​END
      • 简单CASE函数的执行过程为:
      • 计算测试表达式,然后按从上到下的顺序对每个WHEN子句的简单表达式进行计算。
      • 如果某个简单表达式的值与测试表达式的值相匹配,则返回与第一个取值为True的WHEN对应的结果表达式的值。
      • 如果所有的简单表达式的值都不与测试表达式的值相匹配,则当指定了ELSE子句时,将返回ELSE子句中指定的结果表达式的；若没有指定ELSE子句,则返回NULL值。
    • 搜索CASE数的语法格式为:
      CASE
      WHEN 布尔表达式1 THEN 结果表达式1
      WHEN 布尔表达式2 THEN 结果表达式2
      …
      WHEN 布尔表达式n THEN 结果表达式n
      [ ELSE 结果表达式 n+1]
      END
      • 搜索CASE数的各个WHEN子句的布尔表达式可以使用比较运算,也可以使用逻辑运算符。
  • 将查询结果保存到新表中
    • 当使用SELECT语句查询数据时,产生的结果是保存在内存中的。如果希望将查询结果永久保存下来,比如保存在一个表中,则可以通过在SELECT语句中使用INTO子句实现。
    • 包含INTO子句的SELECT语句的语法格式为:
      SELECT 查询列表序列 INTO <新表名>
      FROM 数据源
      …
      --其他行过滤、分组等子句
      • 其中“新表名"是要存放查询结果的表名。这个语句将查询的结果保存在“新表名"所指定的表中。实际上这个语句包含如下三个功能:
      • 1)根据查询语句列出的列以及其类型创建一个新表;
      • 2)执行查询语句;
      • 3)将查询的结果插人到新表中。
      • 用INTO子句创建的新表可以是永久表(在执行这个语句时所使用的数据库中被物理的创建,并且是存储在磁盘上的表),也可以是临时表(在tempdb数据库中创建此表,其生存期是有限的)。
    • 临时表又根据其使用范围分为局部临时表和全局临时表两种。
      • 局部临时表通过在表名前加一个“#"来标识,局部临时表的生存期与创建此局部临时表的用户的连接生存期相同,它只能在创建此局部临时表的当前连接中使用
      • 全局临时表通过在表名前加两个“#"来标识,比如:##T1。全局临时表的生存期与创建全局临时表的用户的连接生存期相同,并且在生存期内可以被所有的连接使用。
• 查询结果的并、交、差运算
  • 并运算
    • 并运算可将两个或多个查询语句的结果集合并为一个结果集,这个运算可以使用UNION运算符实现。
    • 使用UNION谓词的语法格式为:
      SELECT 语句1
      UNION [ ALL]
      SELECT 语句2
      UNION [ ALL]
      …
      SELECT 语句 n
      • 其中:ALL表示在结果集中包含所有查询语句产生的全部记录,包括重复的记录。如果没有指定ALL,则系统默认是删除合并后结果集中的重复记录。
      • 使用UNION时,需要注意以下几点:
        • 所有要进行UNION操作的查询,其SELECT列表中列的个数必须相同,而且对应列的语义应该相同。
        • 各查询语句中每个列的数据类型必须与其他查询中对应列的数据类型是隐式兼容的,即只要它们能进行隐式转换即可。
        • 合并后的结果采用第一个SELECT语句的列标题
        • 如果要对查询的结果进行排序,则ORDERBY子句应该写在最后一个查询语句之后,目排序的依据列应该是第一个查询语句中出现的列名。
  • 交运算
    • 交运算将返回同时在两个集合中出现的记录,即返回两个查询结果集中各个列的值均相同的记录,并用这些记录构成交运算的结果。
    • 实现交运算的SQL运算符为INTERSECT,其语法格式为:
      SELECT 语句1
      INTERSECT
      SELECT 语句2
      INTERSECT
      …
      SELECT 语句 n
    • INTERSECT运算对各查询语句的要求同UNION运算
  • 差运算
    • 差运算将返回在第一个集合中有但第二个集合中没有的数据。
    • 实现差运算的SQL运算符为EXCEPT,其语法格式为:
      SELECT 语句1
      EXCEPT
      SELECT 语句2
      EXCEPT
      ​…
      ​SELECT 语句 n
    • EXCEPT运算对各查询语句的要求同UNION运算
• 相关子查询
  • 在SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块。如果一个SELECT语句是嵌套在一个SELECT、INSERT、UPDATE或DELETE语句中,则称为子查询或内层查询,包含子查询的语句称为主查询或外层查询。一个子查询也可以嵌套在另外一个子查询中。为了与外层查询有所区别,总是把子查询写在圆括号中。与外层查询类似,子查询语句中也必须包含SELECT子句和FROM子句,并可以根据需要选择WHERE子句、GROUP BY子句和HAVING子句。
  • 包含子查询的语句通常采用以下格式中的一种:
    • WHERE 表达式 [NOT] IN (子查询)
    • WHERE 表达式 比较运算符( 子查询)
    • WHERE [NOT] EXISTS (子查询)
    • 通常,子查询一般用在外层查询的WHERE子句或HAVING子句中,与比较运算符或逻辑运算符一起构成查询条件。对于返回结果为单值的子查询语句,可以出现在任何允许使用表达式的地方
  • 使用子查询进行基于集合的测试
    • 使用子查询进行基于集合的测试时,通过运算符IN和NOT IN,将一个表达式的值与子查询返回的结果集进行比较。其形式为:
      WHERE 表达式 [NOT] IN (子查询)
    • 这种形式的子查询的语句是分步骤实现的,即先执行子查询,然后在子查询的结果基础上再执行外层查询。子查询返回的结果实际上就是一个集合,外层查询就是在这个集合上使用IN运算符进行比较。
  • 使用子查询进行比较测试
    • 使用子查询进行比较测试时,通过比较运算符(=、<>、<>、<=、>=),将一个表达式的值与子查询返回的单值进行比较。如果比较运算的结果为True,则比较测试返回True;如果比较运算的结果为False,则比较测试返回False。
    • 使用子查询进行比较测试的形式为:
      WHERE 表达式 比较运算符 (子查询)
    • 使用子查询进行的比较测试要求子查询语句必须是返回单值的查询语句
    • 注意:由于聚合函数不能出现在WHERE子句中,因此,当一个列的值与一个聚合函数的结果进行比较时,必须用子查询先得到聚合函数的结果,然后在此结果基础之上再执行外层查询的比较
  • 使用子查询进行存在性测试
    • 使用子查询进行存在性测试时,通常使用EXISTS谓词,其形式为:
      WHERE [NOT] EXISTS (子查询)
    • 带EXISTS谓词的子查询不返回查询的结果,只产生逻辑真值和逻辑假值
    • EXISTS 的含义是:当子查询中有满足条件的数据时,EXISTS 返回真值,否则返回假值。
    • NOT EXISTS 的含义是:当子查询中有满足条件的数据时,NOT EXISTS返回假值,当子查询中不存在满足条件的数据时,NOT EXISTS 返回真值。
    • 注1:带EXISTS谓词的查询是先执行外层查询,然后再执行内层查询。由外层查询的值决定内层查询的结果;内层查询的执行次数由外层查询的结果数决定
    • 注2:由于带EXISTS的子查询只返回真或假值,因此在子查询里不必指定查询列表。所以在有EXISTS的子查询中,子查询中的目标列通常都用" * "。
• 其他形式的子查询
  • 替代表达式的子查询
    • 替代表达式的子查询是指在SELECT语句的选择列表中嵌人一个只返回一个标量值的SELECT语句,这个查询语句通常都是通过一个聚合函数来返回一个单值。
  • 派生表
    • 派生表(有时也称为内联视图)是将子查询作为一个表来处理,这个由子查询产生的新表就被称为“派生表”,这很类似于临时表。可以在查询语句中用派生表来建立与其他表的连接关系,在生成派生表后,在查询语句中对派生表的操作与普通表一样。
• 其他一些查询功能
  • 开窗函数
    • 在SQL Server 中,一组行被称为一个窗口,开窗函数是指可以用于“分区”或“分组"计算的函数。这些函数结合OVER子句对组内的数据进行编号,并进行求和、计算平均值等统计。因此,从这个角度来说,SUM、AVG以及ROW_NUMBER(对数据进行编号的函数)等都可以称为开窗函数。
    • 开窗函数可以分别应用于每个分区,把每个分区看成是一个窗口,并为每个分区进行计算开窗函数必须放在OVER子前边。
    • SQLServer提供了两种开窗函数:排名开窗函数和聚合开窗函数。
    • 将OVER子句与聚合函数结合使用
      • OVER子旬用于确定在应用关联的开窗函数之前对行集的分区和排序。
      • 将OVER子聚合数结合使用的语法格式为:
        <OVER_CLAUSE> :: =
        OVER( [PARTITION BY value_expression.... [n] ] )
      • 其中各参数说明如下:
        • PARTITION BY: 将结果集划分为多个分区。开窗数分别应用于每个分区,并为每个分区计算函数值。
        • value_expression: 指定对行集进行分区所依据的列,该列必须是在FROM子句中生成的列,而且不能引用选择列表中的表达式或别名。value_expression可以是列表达式、替代表达式的子查询、标量函数或用户定义的变量。
        • 可以在单个查询中使用多个开窗函数,每个函数的OVER子句在分区和排序上可以不同。
    • 将 OVER 子句与排名函数一起使用
      • 排名函数为分区中的每一行返回一个排名值。根据所用函数的不同,某些行可能与其他行具有相同的排名值。排名函数具有不确定性。
      • SQL Server提供了四个排名函数: RANK、DENSE_RANK、NTILE和ROW_NUMBER。
      • (1) RANK()函数。使用RANK()函数的语法格式为:
        RANK( ) OVER ( [ <partition_by_clause>....[n] ]
        <order_by_elause> )
        • 其中各参数含义如下:
          • <partition_by_cause>: 将FROM 子句生成的结果集划分成排名函数适用的分区
          • <order_by_elause>: 指定应用于分区中的行时所基于的排序依据列。
        • RANK()函数返回结果集中每行数据在每个分区内的排名。每个分区内行的排名从1开始
        • 如果排序时有值相同的行,则这些值相同的行具有相同的排名。
        • RANK()函数并不一定返回连续整数。
      • (2)DENSE_RANK()函数
        • DENSE_RANK()函数与RANK()数的作用基本一样,使用方法也一样,唯一的区别是DENSE_RANK()函数的排名中间没有任何间断,即该函数返回的是个连续的整数值。
      • (3)NTILE()函数。NTILE()函数的语法格式如下:
        NTILE (integer_expression )
        0VER ( [<partition_by_clause> ] <order_by_clause > )
        • NTILE()函数的作用是将有序分区中的行划分到指定数目的组中,每个组有一个编号,编号从1开始。对于每一行,NTILE()函数将返回此行所属的组的编号。
      • (4)ROW_NUMBER()函数。ROW_NUMBER()函数的语法格式如下:
        ROW NUMBER( )
        0VER( [ <parition_by_clause> ] <order_by_clause> )
        • ROW_NUMBER()函数返回结果集中每个分区内行的序列号每个分区的第一行从1开始。
  • 公用表表达式
    • 公用表表达式(Common Table Expression,CTE)是将查询语句产生的结果集指定一个临时命名的名字,这些命名的结果集就称为公用表表达式。命名后的公用表表达式后就可以在SELECT,INSERT,UPDATE、DELETE等语句中被多次引用。公用表表达式还可以包括对自身的引用,这种表达式称为递归公用表表达式