第06章数据检索

实现数据查询是创建数据库的重要功能之一，在SQL Server中，查询数据是通过SELECT语句实现的。
SELECT语句能够从服务器的数据库中检索符合用户要求的数据，并以结果集的方式返回客户端。

利用SELECT语句检索数据

SELECT 语句是Transact-SQL语言从数据库中获取信息的一个基本语句。该语句可以实现从一个或多个数据库中的一个或多个表中查询信息，并将结果显示为另外一个二维表的形式，称之为结果集（result set）。

SELECT语句的基本的语法格式可归纳如下：
SELECT select_list 
[INTO new_table]
[ FROM table_source ]
[ WHERE search_condition ]
[ GROUP BY group_by_expression][WITH ROLLUP]
[ HAVING search_condition ] 
[ ORDER BY order_expression [ ASC | DESC ] ]

3 .利用INTO子句生成新表

利用SELECT INTO 可将几个表或视图中的数据组合成一个表。也可用于创建一个包含选自链接服务器的数据的新表。
【例6.8】利用SELECT…INTO创建新表。在teaching数据库中创建一个新表学生成绩 st_score，包括学生学号、姓名、课程号和期末成绩。
分析：学生学号、姓名、课程号和期末成绩分别在teaching数据库中的student表和score表中，访问两个表中的数据时，重复的数据列需要说明来源。

数据过滤

在WHERE子句中指定搜索条件可以限定查询返回的结果集，称为过滤数据。常用的过滤类型有比较运算、字符串运算、逻辑运算、指定范围或指定列值及未知值的运算。

1. 空值查询

涉及空值的查询用NULL来表示。CREATE TABLE语句或ALTER TABLE 语句中的NULL表明在列中允许存在被称为NULL的特殊数值，它不同于数据库中的其他任何值。
在SELECT语句中，WHERE子句通常会返回比较的计算结果为真的行。为了取得列中含有NULL的行，Transact-SQL语句包含了操作符功能IS [NOT] NULL。

需要注意的是，一个列值是空值或者不是空值，不能表示为：“=NULL”或“<>NULL”，而要表示为：“IS NULL”或“IS NOT NULL”。
WHERE子句有以下通用格式：
COLUMN IS [NOT] NULL

GROUP BY子句和HAVING子句

1. GROUP BY子句的使用

GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。如果聚合函数没有使用 GROUP BY子句，则只为 SELECT语句报告一个聚合值。
将一列或多列定义成为一组，使组内所有的行在那些列中的数值相同。出现在查询的SELECT 列表中的每一列都必须同时出现在GROUP BY子句中。
SELECT 语句中的 WHERE和HAVING子句控制用源表中的那些行来构造结果集。WHERE和HAVING是筛选，这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用来构造结果集。
HAVING 子句通常与 GROUP BY 子句结合使用，尽管指定该子句时也可以不带 GROUP BY。HAVING 子句指定在应用 WHERE 子句的筛选后要进一步应用的筛选。

多表连接

分类

CROSS JOIN：交叉连接（笛卡儿积） ，结果只包含两个表中所有行的组合，指明两个表之间的笛卡儿积操作。
INNER JOIN：内连接，结果只包含满足条件的列。
LEFT OUTER JOIN：左外连接，结果包含满足条件的行及左侧表中的全部行。
RIGHT OUTER JOIN：右外连接，结果包含满足条件的行及右侧表中的全部行。
FULL OUTER JOIN：完全连接（左连接和右连接的并），结果包含满足条件的行和两侧表中的全部行。

合并多个结果集

UNION操作符可以将多个SELECT语句的返回结果组合到一个结果集中。当要检索的数据在不同的结果集中，并且不能够利用一个单独的查询语句得到时，可以使用UNION合并多个结果集。

将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。UNION 运算不同于使用联接合并两个表中的列的运算。
使用 UNION 合并两个查询结果集时，所有查询中的列数和列的顺序必须相同且数据类型必须兼容。
UNION操作符基本语法格式如下：

SELECT_statement UNION [all] SELECT_statement

【例7.9】建立t1、t2两个表，合并其结果集示例。
分析：虽然2个表的结构不同，但需要合并的两个结果集结构和列的数据类型兼容。

CREATE TABLE t1 (a int, b nchar(4), c nchar(4))
      INSERT INTO t1 VALUES (1, 'aaa', 'jkl')
      INSERT INTO t1 VALUES (2, 'bbb', 'mno')
      INSERT INTO t1 VALUES (3, 'ccc', 'pqr')

CREATE TABLE t2 (a nchar(4), b float)
     INSERT INTO t2 VALUES('kkk', 1.000)
     INSERT INTO t2 VALUES('mmm', 3.000)


SELECT a, b FROM t1 UNION  SELECT b, a FROM t2

使用子查询

概述

子查询就是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询。部分子查询和连接可以相互替代，使用子查询也可以替代表达式。通过子查询可以把一个复杂的查询分解成一系列的逻辑步骤，利用单个语句的组合解决复杂的查询问题。

注意：

SQL Server 2016对嵌套查询的处理过程是从内层向外层处理
一般情况下，包含子查询的查询语句可以建议写成连接查询的方式。
在有些方面，连接的性能要优于子查询，原因是连接不需要查询优化器执行排序等额外的操作。

2.利用子查询做表达式

在Transact-SQL语句中，可以把子查询的结果当成一个普通的表达式来看待，用在其外查询的选择条件中。此时子查询必须返回一个值或单个列值列表，此时的子查询可以替换WHERE子句中包含IN关键字的表达式。

【例7.10】查询学号为17123567897的学生的入学成绩、所有学生的平均入学成绩及该学生成绩与所有学生的平均入学成绩的差。
【例7.11】获取期末成绩中含有高于93分的学生的学号、姓名、电话和Email。
【例7.12】查询选修课程的多于2门、且期末成绩均在85分以上的学生的学号、姓名、电话和E_mail。

3.利用子查询关联数据

子查询可以作为动态表达式，该表达式可以随着外层查询的每一行的变化而变化。
即查询处理器为外部查询的每一行计算子查询的值，每次计算一行，而该子查询每次都会作为该行的一个表达式取值并返回到外层查询。

使得动态执行的子查询与外部查询有一个非常有效的连接，从而将复杂的查询分解为多个简单而相互关联的查询。

创建关联子查询时，外部查询有多少行，子查询就执行多少次。

【例7.13】查询期末成绩比该选修课程平均期末成绩低的学生的学号、课程号和期末成绩。

分析：

在本例中，对score表采用别名形式，一个表就相当于2个表。
子查询执行时使用的a.courseno相当于一个常量。
在别名为b的表中根据分组计算平均分。
然后与外层查询的值进行比较。该过程很费时间。

SELECT studentno,courseno,final
FROM score as a
WHERE final < (SELECT AVG(final)
               FROM score as b
               WHERE a.courseno=b.courseno
               group by  courseno )

4.利用子查询生成派生表

利用子查询可以生成一个派生表，用于替代FROM子句中的数据源表，派生表可以定义一个别名，即子查询的结果集可以作为外层查询的源表。实际上是在FROM子句中使用子查询。

【例7.14】查询期末成绩高于85分、总评成绩高于90分的学生的学号、课程号和总评成绩。

分析： 利用子查询过滤出期末成绩高于85分的结果集，以TT命名，然后再对结果集TT中的数据进行查询。

SELECT TT.studentno,TT.courseno,
        TT.final*0.8+TT.daily*0.2 AS '总评成绩'
FROM  (SELECT *
       FROM score
       WHERE final>85) AS TT
WHERE TT.final*0.8+TT.daily*0.2>90

5.使用子查询修改表数据

利用子查询修改表数据就是利用一个嵌套在INSERT、UPDATE或DELETE语句的子查询成批的添加、更新和删除表中的数据。

SELECT
INSERT 语句中的 SELECT 子查询可用于将一个或多个其他的表或视图的值添加到表中。使用 SELECT 子查询可同时插入多行。
格式：insert into student select...

【例7.15】创建一个表sc_17，将score表中17级学生的相关数据添加到sc_17表中，并要求计算总评成绩。

分析：
子查询的选择列表必须与 INSERT 语句列的列表匹配。
如果INSERT 语句没有指定列的列表，则选择列表必须与正向其插入的表或视图的列匹配且顺序一致。

CREATE TABLE sc_17(studentno nchar(11) not null,
               courseno nchar(6) not null,
               total numeric (6,2) not null)
GO
INSERT INTO  sc_17(studentno, courseno,total)
       SELECT  studentno, courseno,final*0.8+daily*0.2
       FROM score
       WHERE substring(studentno,1,2)='17'
GO
SELECT * FROM sc_17

UPDATE

【例7.16】将sc_17表中含有总分低于80课程的所有学生总分增加5%。

分析：利用UPDATE成批修改表数据，可以在WHERE子句的利用子查询实现。

UPDATE  sc_17
SET total=total*1.05
WHERE  courseno in 
                 (SELECT courseno
                  FROM  sc_17
                   where total<80 )

DELETE
同样在DELETE语句中利用子查询可以删除符合条件的数据行。实际上是通过将子查询的结果作为删除条件表达式中的一部分。

DELETE  sc_17
  SELECT courseno
  FROM  sc_17
  where total<80

6. EXISTS和NOT EXISTS子句

含义：存在不存在某数据
EXISTS 是SQL语句中的运算符号，在子查询中，如果存在一些匹配的行，结果为TURE。在执行过程中，一旦查找到第1个匹配的行，查询就结束。NOT EXISTS 与 EXISTS 的工作方式类似。

例7.17】查询student表中是否存在1999年12月12日以后出生的学生，如果存在，输出学生的学号、姓名、生日和电话。

分析：只要存在一行数据符合条件，则WHERE条件就返回TURE，于是输出所有行。

利用游标处理结果集

概述

关系数据库的大部分管理操作都与Transact-SQL中的查询语句SELECT有着密切的联系。SELECT语句一般返回的是包含多条记录的、存放在客户机内存中的结果集。

作用：
当用户需要访问一个结果集中的某条具体记录时，就需要使用游标功能。

表示：
使用英文单词CURSOR来表示游标。
分类：使用关键字GLOBAL和LOCAL表示一个游标声明为全局游标和局部游标。
作为全局游标，一旦被创建就可以在任何位置上访问，而作为局部游标则只能在声明和创建的函数或存储过程中对它进行访问。当多个不同的过程或函数需要访问和管理同一结果集时，应使用全局游标。

而局部游标管理起来更容易一些，因而其安全性也相对较高。
局部游标可以在一个存储过程、触发器或用户自定义的函数中声明。由于其作用域受存储过程的限制，所以在自身所处的过程中对游标的任何操作都不会对其他过程中声明的游标产生影响。

游标在T-SQL中使用游标(CURSOR)的步骤如下。

声明游标。
在使用游标之前，首先需要声明游标。

打开游标。
声明一个游标之后，还必须使用OPEN语句打开游标，才能对其进行访问。
打开一个游标意味着在游标中输入了相关的记录信息。
当打开游标时，SQL Server数据会自动在TempDB数据库中创建一个工作表来保存与该游标相关的数据集。

获取记录信息。
如果需要获取某一条记录的信息，还需要使用Fetch语句来获取该记录的值，一条Fetch语句会执行两步操作：首先将游标当前指向的记录保存到一个局部变量中，然后游标将自动移向下一条记录。将一条记录读入某个局部变量后，就可以根据需要对其进行处理了。
关闭游标。
当不需要使用游标功能时，可以使用Close函数来关闭该游标，释放那些被该游标锁定的记录集。
释放游标。
最后还需要使用Deallocate语句释放游标自身所占用的资源。

游标的运用

使用游标，可以定位到某一指定的记录，而且可以对所定位记录的数据进行更改。实际上，游标就是指向内存中结果集的指针，可以实现对内存中的结果集进行各种操作，操作完毕后，才能将数据存放到硬盘上。

DECLARE 声明游标基本格式：

DECLARE  cursor_name  CURSOR[ LOCAL|GLOBAL][FORWARD_ONLY
|SCROLL][STATIC|DYNAMIC][READ_ONLY]
FOR  select_statement [;]

打开游标：
设计可以使用全局函数@@CURSOR_ROWS来指定或获取与游标关联的数据记录行数。
使用OPEN语句打开上例中游标cEmploy的代码如下:

OPEN cEmploy

使用FETCH获取记录信息
使用FETCH函数可以在一个打开的游标中遍历记录集中的记录。使用FETCH函数获取游标中的一条记录，并将它保存到相应的变量中后，游标将自动地被定位到下一条记录上。

   FETCH [[NEXT | PRIOR | FIRST | LAST |
   ABSOLUTE{ n | @nvar | RELATIVE { n | @nvar}]
    FROM ] cursor_name [INTO @variable_name[ ,...n ]]

关闭游标：
要关闭打开的cEmploy游标，可以使用如下命令：

CLOSE cEmploy

释放游标：
关闭游标后，仍需要进一步释放游标本身占有的系统资源。此时，可使用DEALLOCATE语句完成此项操作。可使用DEALLOCATE语句彻底释放该游标自身所占有的系统资源。

DEALLOCATE cEmploy

除此之外，还可以将游标作为存储过程的输出参数。随着离开该存储过程，离开了代表游标变量的作用域，该游标将被自动地释放，而无须显式地使用DEALLOCATE语句来释放游标

查看游标的信息
在使用游标进行记录行定位的过程中，需要不断的关注游标的属性和状态信息。通常这些工作是由存储过程和函数来完成的。
利用函数察看游标的状态：SQL Server 2016服务器为编程人员提供了3个用于处理游标的函数。下面依次对这些函数进行介绍。

CURSOR_STATUS函数

CURSOR_STATUS是一个标量函数，在调用游标的存储过程时，可以通过该函数来检查输出参数是否已成功地获得了游标和结果集。

返回值：
CURSOR_STATUS函数可以返回一个游标的当前状态。SQL Server 2016的游标状态包括以下5种情况，如表7-2所示。

@@CURSOR_ROWS函数

@@CURSOR_ROWS实际上是SQL Server 2016提供的一个系统型全局函数（或变量）。

@@CURSOR_ROWS可用于返回当前游标最后一次被打开时所含的记录数。

此外，也可使用该函数来设置，并控制打开一个游标时要包含的记录数。对于一个动态游标，该函数将返回-1，因为对于一个动态游标来说，是不可能准确地获取其全部记录的信息的，而且此时也无法保障不会有其他潜在访问操作影响该记录集。

返回值：
该函数的返回值代表最后一次打开游标时所包含的记录数。
在编写应用程序时，很可能需要一次打开多个游标。因此，如果需要记录每次打开游标时的记录数，则应该通过变量来保存它们。

【例7.22】声明游标，利用函数查看游标对teacher表进行检索的状态。

--例7.22声明游标，查看游标对teacher表进行检索的状态。
USE teaching
GO
--声明变量
DECLARE @teacherno nchar(6),@tname nchar(8),@major nchar(10),
          @msg_STATUS int,@msg_ROWS int
--声明游标
DECLARE teacher_cursor CURSOR LOCAL STATIC
FOR 
  SELECT teacherno,tname,major 
  FROM teacher
--打开游标
OPEN teacher_cursor 
--提取第1行数据并赋给变量
FETCH FIRST FROM teacher_cursor INTO @teacherno,@tname, @major 
SELECT @msg_STATUS=CURSOR_STATUS ('local','teacher_cursor')
SELECT @msg_ROWS=@@CURSOR_ROWS
PRINT @msg_STATUS
PRINT @msg_ROWS
--提取第3行数据并输出
FETCH ABSOLUTE 3 FROM teacher_cursor
SELECT @msg_STATUS=CURSOR_STATUS ('local','teacher_cursor')
SELECT @msg_ROWS=@@CURSOR_ROWS
PRINT @msg_STATUS
PRINT @msg_ROWS
--提取当前行开始的第5行数据并输出
FETCH RELATIVE 5 FROM teacher_cursor
SELECT @msg_STATUS=CURSOR_STATUS ('local','teacher_cursor')
SELECT @msg_ROWS=@@CURSOR_ROWS
PRINT @msg_STATUS
PRINT @msg_ROWS
--关闭和释放游标
CLOSE teacher_cursor
DEALLOCATE teacher_cursor


/*--提取最后一行数据
FETCH  LAST FROM teacher_cursor
SELECT @msg_STATUS=CURSOR_STATUS ('local','teacher_cursor')
SELECT @msg_ROWS=@@CURSOR_ROWS
PRINT @msg_STATUS
PRINT @msg_ROWS
*/

@@FETCH_STATUS函数

@@FETCH_STATUS函数可以用于检查上一次执行的FETCH语句是否成功，返回值的含义如表7-3所示。

利用系统存储过程察看游标属性
在声明游标后，可使用下列系统存储过程确定游标的特性。

【例7.23】利用sp_cursor_list 系统存储过程显示游标的属性。


USE teaching
GO
--声明变量
DECLARE @teacherno nchar(6),@tname nchar(8)
--声明游标
DECLARE teacher_cursor CURSOR 
FOR 
  SELECT teacherno,tname 
  FROM teacher
--声明游标变量
DECLARE @teacher_cursor CURSOR 
--执行sp_cursor_list 系统存储过程
EXEC teaching.dbo.sp_cursor_list @cursor_return = @teacher_cursor OUTPUT,@cursor_scope = 2
--打开游标
OPEN teacher_cursor 
--提取第一行数据并赋给变量
FETCH NEXT FROM teacher_cursor INTO @teacherno,@tname

--利用@@FETCH_STATUS测试游标状态，0值表示游标指向合法行记录
WHILE @@FETCH_STATUS = 0

BEGIN

--提取下一行数据
FETCH NEXT FROM @teacher_cursor
END
CLOSE @teacher_cursor
DEALLOCATE @teacher_cursor

--关闭和释放游标
CLOSE teacher_cursor
DEALLOCATE teacher_cursor

posted @ 2022-11-09 09:49 kingwzun 阅读(85) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

kingwzun

第06章 数据检索