第4章数据库的查询、视图和游标

4.1关系运算

1.选择

选择（selection）是单目运算，其运算对象是一个表。该运算按给定的条件，从表中选出满足条件的行，形成一个新表，作为运算结果。

2.投影

投影（projection）也是单目运算，其运算对象是一个表。该运算从表中选出指定的属性值组成一个新表。

3.连接

连接（join）是把表中的行按照给定的条件进行拼接而形成的新表。数据库中最常用的是“自然连接”。进行自然连接时要求两个表有共同属性（列），自然连接运算的结果表是在参与操作的两个表的共同属性上进行等值连接后，再去除重复的属性后所得的新表。

4.2数据库的查询：SELECT

4.2.1 SELECT 语句：

SELECT <输出列>                          /*指定查询结果输出列*/        

[INTO 新表]                              /*指定查询结果存入新表*/

[FROM {<表源>}[,...]]                    /*指定查询源：表或视图*/

[WHERE <条件>]                           /*指定查询条件*/  

[GROUP BY <分组条件>]                    /*指定查询结果分组条件*/

[HAVING <分组统计条件>]                  /*指定查询结果分组统计条件*/

[ORDER BY <排序顺序>]                    /*指定查询结果排序顺序*/

 

a.选择所有的列:使用*表示选择一个表或视图中的所有列。

USE pxscj

GO

SELECT *

FROM xsb

GO

b.选择一个表中的指定的列

USE pxscj

GO

SELECT 学号,姓名,总学分

FROM xsb

WHERE 专业 = '计算机'

GO

c.定义列别名

USE pxscj

GO

SELECT 学号 AS number,姓名 AS name,总学分 AS mark

FROM xsb

WHERE 专业 = '计算机'

GO

或

USE pxscj

GO

SELECT number = 学号,name = 姓名,mark = 总学分

FROM xsb

WHERE 专业 = '计算机'

d.替换查询结果中的数据

CASE

WHEN 条件1 THEN 表达式1

WHEN 条件2 THEN 表达式3

......

ELSE 表达式

END

示例：

USE pxscj

GO

SELECT 学号,姓名,等级 =

CASE

WHEN 总学分 IS NULL THEN '尚未选课'

WHEN 总学分<50 THEN '不及格'

WHEN 总学分>=50 and 总学分<=52 THEN '合格'

ELSE '优秀'

END

FROM xsb

WHERE 专业= '计算机'

GO

e.计算列值：使用select对列进行查询时，在结果中可以输出对列值计算后的值，即select子句可使用表达式作为结果，语法格式如下：

SELECT 表达式 [,表达式]

示例：

USE pxscj

GO

SELECT 学号,课程号,成绩120= 成绩*1.20

FROM cjb

WHERE 学号='191301'

f.消除结果集中的重复行

对表只选择其某些列时，可能会出现重复行，可以使用DISTINCT关键字消除结果集中的重复行，使用关键词ALL时，将保留结果集的所有行，省略时默认为ALL，格式如下：

SELECT DISTINCT | ALL 列名 [,列名...]

示例：

USE pxscj

GO

SELECT DISTINCT 专业 FROM xsb

g.限制结果集返回固定行数

[TOP [PERCENT][WITH TIES]]

指示只能从查询结果集返回指定的第一组行或指定百分比数目的行。“表达式”可以指定数目或百分比数目的行。若带PERCENT关键字，则表示返回结果集的前%行

示例：

SELECT TOP 6 姓名,专业,总学分

FROM xsb

SELECT TOP (10) PERCENT 姓名,专业,总学分

FROM xsb

h.聚合函数

SELECT子句中的表达式中还可以包含所谓的聚合函数。聚合函数常用语对一组值进行计算，然后返回单个值。聚合函数通常与GROUP BY子句一起使用，如果一个SELECT子句中

有一个GROUP BY 子句，则这个聚合函数对所有的列起作用，如果没有，则SELECT子句只产生一行作为结果。下表为SQL Server提供的聚合函数：

函数名	说明
AVG	求组中值的平均值
BINARY_CHECKSUM	返回对表中的行或表达式列表计算的二进制校检值，可用于检测表中行的更改
CHECKSUM	返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引
CHECKSUM_AGG	返回组中值的校验值
COUNT	求组中的项数，返回int类型整数
COUNT_BIG	求组中的项数，返回bigint类型整数
GROUPING	产生一个附加的列
GROUPING_ID	为聚合列列表中的每一行创建一个值以标识聚合级别
MAX	求最大值
MIN	求最小值
SUM	返回表达式中所有值的和
STDEV	返回给定表达式中所有值的统计标准偏差
STDEVP	返回给定表达式中所有值的填充统计标准偏差
VAR	返回给定表达式中所有值的统计方差
VARP	返回给定表达式中所有值填充的统计方差

常用函数：SUM和AVG,MAX和MIN,COUNT

 

SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式如下：

SUM/AVG ([ALL|DISTINCT] 表达式)

示例：求所有课程总学分和选修101课程学生的平均成绩

SELECT 总学分 =SUM (学分)

FROM kcb

GO

SELECT 平均成绩 =AVG(成绩)

FROM cjb

WHERE 课程号=101

GO

 

MAX和MIN

SUM/AVG ([ALL|DISTINCT] 表达式)

示例：求选修101课程学生的最高分和最低分

SELECT MAX(成绩) AS '计算机基础最高分',MIN(成绩) AS '计算机基础最低分'

FROM cjb WHERE 课程号 = '101'

 

COUNT 用于统计组中满足条件的行数和总行数，其语法格式如下：

COUNT( { [ALL |DISTINCT ]表达式 } | * )

示例：求学生的总数、总学分在50分以上的人数和专业个数

SELECT COUNT(*) AS '学生的总数',COUNT(DISTINCT 专业) AS '专业个数'

FROM xsb GO

SELECT COUNT(总学分) AS '总学分大于50分的人数'

FROM xsb WHERE 总学分>50;

GO

使用COUNT(*)时返回检索的行的总数目，不论其是否包含NULL值

COUNT_BIG函数的格式功能与COUNT都相同，区别仅在于COUNT_BIG返回bigint类型值。

 

4.2.2 选择查询条件：WHERE

1.表达式比较

表达式1 {比较云算符} 表达式2，其中表达式是除text、ntext和image以外类型的表达式。当两个表达式的值均不为空时，比较运算返回逻辑值TRUE（真）或FALSE（假）。而当两个表达式的值中有一个为空值或都为空值时，比较运算将返回UNKNOWN。可以将多个判定运算的结果通过逻辑云算符（NOT、AND、OR）再组成更为复杂的查询条件

示例：查询xsb中通信工程专业总学分大于等于42的同学的情况。

SELECT * FROM xsb WHERE 专业= ‘通信工程’ AND 总学分>=42

2.模式匹配

LIKE谓词用于指出字符串是否与指定的字符串相匹配，返回逻辑值TRUE或FALSE。LIKE谓词表达式的语法格式如下：

表达式[NOT] LIKE 模式串 [ESCAPE 转义符]

说明：

表达式：一般为字符串表达式，在查询语句中可以是列名。

模式串：可以使用通配符

转义符：应为有效的SQL Server字符，没有默认值，且必须为单个字符。当模式串中含有与通配符相同的字符时，应通过该字符前的转义符指明其为模式串中的一个匹配字符。使用ESCAPE可以指定转义符。

NOT LIKE：使用NOT LIKE  与LIKE 的作用相反。

使用带“%”通配符的LIKE时，模式字符串中的所有字符都有意义，包括起始或尾随空格。

通配符说明：

通配符	说明
%	代表倒数
_(下画线)	代表单个字符
[]	指定范围（如[a-f]、[0-9]或集合[abcdef]中的任何单个字符）
[^]	指定不属于范围（如[^a-f]、[^0-9]或集合[^abcdef]中的任何单个字符）

示例：查询xsb表中姓“王”且单名的学生情况

SELECT *

FROM xsb

WHERE 姓名 LIKE '王_'

GO

示例：查询xsb中学号倒数第5个数字为9，且倒数第1个数字在1~5之间的学生学号、姓名及专业。

SELECT 学号,姓名,专业

FROM xsb

WHERE 学号 LIKE '_9___[1-5]'

GO

 

3.范围比较 BETWEEN 和 IN

当要查询的条件为某个值的范围时，可以使用BETWEEN关键字

表达式 [NOT] BETWEEN 表达式1 AND 表达式2     

注意：表达式1的值不能大于表达式2,包含表达式1和表达式2的值

 

示例：查询xsb中不在1995年出生的学生情况

SELECT *

FROM xsb

WHERE 出生时间 NOT LIKE '1995______'

SELECT *

FROM xsb

WHERE 出生时间 NOT BETWEEN '1995-01-01' AND '1995-12-31'

IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE，格式如下：

表达式 IN (表达式[,...])

示例：查询xsb中专业为软件工程或通信工程的学生情况

SELECT *

FROM xsb

WHERE 专业 IN ('软件工程' ,'通信工程')

SELECT *

FROM xsb

WHERE 专业 ='软件工程' OR 专业 ='通信工程'

 

4.空值比较

当需要判断一个表达式的值是否为空值时，使用IS NULL 关键字，格式如下：

表达式 IS [NOT] NULL

示例：查询总分尚不明确的学生情况

SELECT * FROM xsb WHERE 备注 IS NULL

 

5.子查询

在查询条件中，可以使用另一个的查询的结果作为条件的一部分。例如，判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。

T-SQL允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE、DELETE语句中。子查询通常与IN、EXISTS谓词及比较运算符综合使用。

a.IN子查询：IN子查询用于进行一个给定值是否在子查询结果集中的判断，语法格式如下：

表达式 [NOT] IN （子查询）  当表达式与子查询的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE.

示例：查询选修了课程号为206的课程的学生情况。

SELECT * FROM xsb,cjb WHERE xsb.学号=cjb.学号 AND 课程号 =206

SELECT * FROM xsb WHERE 学号 IN (SELECT 学号 FROM cjb WHERE 课程号 = 206)

b.比较子查询：IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，语法格式如下：

表达式 {比较运算符} {ALL |ANY }(子查询)   其中ALL、SOME、ANY说明对比较运算的限制。ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE

否则返回FALSE；SOME或ANY表示表达式只要与子查询结果集中的某个值满足比较关系，就返回TRUE,否则返回FALSE

示例：查找选修了离散数学的学生学号

SELECT 学号 FROM cjb WHERE 课程号 =  (SELECT 课程号 FROM kcb WHERE 课程名 ='离散数学')

SELECT 学号 FROM cjb WHERE 课程号 IN (SELECT 课程号 FROM kcb WHERE 课程名 ='离散数学')

示例：查找比所有计算机系的学生年龄都大的学生

SELECT * FROM xsb WHERE 出生时间<ALL (SELECT 出生时间 FROM xsb WHERE 专业 = '计算机')

示例：查找206号课程成绩不低于101号课程最低成绩的学生学号。

SELECT 学号 FROM cjb WHERE 课程号 = '206' AND 成绩!<ANY(SELECT 成绩 FROM cjb WHERE 课程号='101')

c.EXISTS子查询： 用于测试子查询的结果是否为空表，若不为空，则EXISTS返回TRUE，否则返回FALSE

[NOT] EXISTS (子查询)

示例：查找选修206号课程的学生姓名

SELECT 姓名 FROM xsb WHERE 学号 IN(SELECT 学号 FROM cjb WHERE 课程号 = 206)

SELECT 姓名 FROM xsb WHERE EXISTS (SELECT *FROM cjb WHERE 学号= xsb.学号 AND 课程号= '206')

示例：从xsb表中查找所有女学生的姓名、学号及其与“191301”号学生的年龄差距

SELECT 学号,姓名,YEAR(出生时间)-YEAR((SELECT 出生时间 FROM xsb WHERE 学号= '191301')) AS 年龄差距 FROM xsb WHERE 性别=0  YEAR函数用于取出日期类型数据的年份。

 

6.CONTAINS谓词

若需要在表中搜索指定的单词、短语或近义词等，可以使用CONTAINS谓词。CONTAINS谓词用于在表中搜索指定的字符串，可以精确匹配，也可以模糊匹配，还可以是加权匹配。要使用CONTAINS谓词，全文索引服务在运行状态，并且应在操作的表上事先建立全文索引。

CONTAINS ({列|*},'<包含查询条件>'[,LANGUAGE 语言项])

说明：

“列”表示在指定的列中搜索，可以指定多个列，类型为char、varchar、nchar、nvarchar、text、ntext、image、xml和varbinary的列是可进行全文搜索的有效列。

“*”表示在所有列中搜索，“语言项”表示用户查询时所用的语言。

<包含查询条件>指定要在列中搜索的文本和匹配条件，语法格式如下：

{<简单项>|<前缀项>|<派生项>|<临近项>|<加权项>}|{ (<包含查询条件>) [{<AND>|<ADN NOT>|<OR>}] <包含查询条件> [...] }

<简单项>：用于说明搜索的单词还是短语，格式如下：单词|"短语" 单词是不包含空格和标点符号的字符串；短语是含一个或多个空格的字符串

<前缀项>：给出了要搜索的单词或短语必须匹配的前缀 格式如下：{"单词*"|"短语*"}

<派生项>：说明搜索包含原词的派生词，所谓派生词是指原词的名词单、复数形式或动词的各种时态等，格式如下：FORMSOF({INFLECTIONAL|THESAURUS},<简单项>[,...])  INFLECTIONAL选项表示指定要对指定的简单字词使用与语言相关的词干分析器。THESAURUS选项表示指定使用对应于列全文语言或指定的查询语言的同一词库。

<临近项>：表示搜索包含NEAR或“~”运算符左右两边的词或短语，格式如下：{<简单项>|<前缀项>} {NEAR|~} {<简单项>|<前缀项>} [...]

<加权项>：指明本语句是加权搜索，即查询的数据与给定的权重进行加权匹配，格式如下：ISABOUT({{<简单项>|<前缀项>|<派生项>|<临近项>} [WEIGHT(加权值) ] } [,...]) ，其中加权值是一个0-1之间的数，表示权重。

示例：使用CONTAINS谓词搜索xsb表中包含字符“不及格”的所有行。

SELECT * FROM xsb WHERE CONTAINS(*,'不及格')

 

7.FREETEXT谓词

与CONTAINS谓词类似，FREETEXT谓词也用于在一个表中搜索单词或短语，并要求表已建立全文索引，格式如下：

FREETEXT({列|列列表|*}, '自由文本串' [,LANGUAGE语言选项]) ，  其中自由文本串是要搜索的字符串。

示例：使用FREETEXT谓词搜索xsb表中包含字符“琳”的所有行。

SELECT * FROM xsb WHERE FREETEXT(*,'琳')        FREETEXT谓词只能与SELECT语句一起使用。

 

8.指定查询对象FROM

SELECT的查询对象由FROM子句指定，查询对象主要包括表或视图，称之为表源。可以使用AS选项为表指定别名，AS关键字也可以省略，直接给出别名即可。别名主要用在相关的子查询及连接查询中。如果FROM子句指定了表别名，则这条SELECT语句中的其他子句都必须使用表别名来代替原始表名。

示例：查找选修了与学号为191302的同学所选修的全部课程相同的同学的学号。

SELECT DISTINCT 学号 FROM cjb AS CJ1 WHERE NOT EXISTS (SELECT * FROM cjb AS CJ2 WHERE CJ2.学号 ='191302' AND NOT EXISTS(SELECT * FROM cjb AS CJ3 WHERE CJ3.学号=CJ1.学号 AND CJ3.课程号=CJ2.课程号))

SELECT DISTINCT 学号 FROM cjb WHERE  课程号   in ( SELECT 课程号 FROM cjb WHERE 课程号 in (SELECT 课程号  FROM cjb WHERE 学号=191302))

 

a.导出表 导出表表示由子查询中SELECT语句的执行而返回的表，但必须使用AS关键字为子查询产生的中间表定义一个别名。

示例：从xsb中查找总学分大于50的男同学的姓名和学号

SELECT 姓名,学号,总学分 FROM (SELECT 姓名,学号,性别,总学分 FROM xsb WHERE 总学分>=50) AS student WHERE 性别=1

SELECT 姓名,学号,性别,总学分 FROM xsb WHERE 总学分>=50 AND 性别=1

 

9.行集函数

行集函数通常返回一个表或视图。主要的行集函数有CONTAINSTABLE、FREETEXTTABLE、OPENDATASOURCE、OPENQUERY、OPENROWSET和OPENXML.另外OPENROWSET函数还可以用于插入图片文件、文本文件、word文件、excel文件等内容

示例：插入图片文件

USE test1

GO

CREATE TABLE Test

(

TestID  int IDENTITY(1,1),

BLOBName  varchar(50),

BLOBData  varbinary(MAX)

)

INSERT INTO Test(BLOBName,BLOBData) SELECT 'picture',BulkColumn FROM OPENROWSET(Bulk 'D:\picture.jpg',SINGLE_BLOB) AS BLOB

 

10表值函数

 

11.行转列表和列转行表

行转列表格式如下：<行转列表>::= 表源 PIVOT <行转列子句> [AS] 表别名  行转列子句的格式：<行转列子句>::= (聚合函数名(值列) FOR 转换列 IN (<列列表>))

PIVOT子句将表值表达式的某一列中的唯一值转换为输出中的多个列来转换表值表达式，即实现了将行值转化为列，并在必要时对最终输出中所需的任何其余的列值执行聚合。其经常用在生成交叉表格

报表以汇总数据。其中，“表源”是输入表或表值表达式；“表列名”为输出表的别名；“值列”是需要聚合的列名；“转换列”是要转换的列；“列列表”列出了“转换列”列的值，这些值将成为输出表的表列名，可以使用AS子句定义这些值的列别名。

示例：查找xsb表中1995年1月1日以前出生的学生的姓名和总学分，并列出其属于计算机专业还是通信工程专业的情况，1表示是，0表示否。

SELECT 姓名,总学分,计算机,通信工程 FROM xsb PIVOT (COUNT(学号) FOR 专业 IN(计算机,通信工程)) AS PVT WHERE 出生时间<'1995-01-01'

 

列转行表格式如下：<列转行表>::= 表源 UNPIVOT <列转行子句> 表别名  列转行子句的格式：<列转行子句>::= (值列 FOR 转换列 IN (<列列表>))

示例：将kcb表中的开课学期和学分列转换为行输出

SELECT 课程号,课程名,选项,内容 FROM kcb UNPIVOT (内容 FOR 选项 IN (学分,开课学期))unpvt

 

12.连接：连接是两元运算，可以对两个或多个表进行查询，结果通常是含有参加连接运算的两个或多个表的指定列的表。

a.连接谓词:可以在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连接，将这种表示形式成为连接谓词表示形式。

示例：查找pxscj数据库中每个学生的情况及选修的课程情况

SELECT xsb.*,cjb.* FROM xsb,cjb WHERE xsb.学号=cjb.学号

连接谓词中的比较符可以是< <= = > >= != <> !< !>,当比较符为=时，就是等值连接

a.b自然连接：它在目标列中取出相同的字段名

示例：通过自然连接进行上述查询

SELECT xsb.*,cjb.课程号,cjb.成绩 FROM xsb,cjb WHERE xsb.学号=cjb.学号

a.c多表连接：

示例：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩

SELECT xsb.学号,姓名,课程名,成绩 FROM xsb,kcb,cjb WHERE xsb.学号=cjb.学号 AND cjb.课程号=kcb.课程号 AND 课程名='计算机基础' AND 成绩>=80

 

b.以JOIN关键字指定的连接

T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有所增强。FROM子句的<连接表>表示将多个表连接起来。格式如下：

<连接表>::={<表源> <类型> <表源> ON <查询条件> |<表源> CROSS JOIN <表源> |左表源 {CROSS | OUTER} APPLY 右表源| [() <连接表> [] ]}

说明：

<表源>：准备要连接的表；

<类型>表示连接类型，语法格式如下：

<类型>::=[{INNER | { {LEFT |RIGHT|FULL} [OUTER]}} [<连接提示>]] JOIN ，

其中INNER表示内连接，OUTER表示外连接

ON：用于指定连接条件，<查询条件>为连接的条件。

APPLY运算符：使用APPLY运算符可以实现查询操作的外部表表达式返回的每个行调用表值函数。“左表源”为外部表值表达式，“右表源”为表值函数。通过对右表源求值来获得左表源每一行的计算结果，

生成的行被组合起来作为最终输出。APPLY运算符生成的列的列表是左表源中的列集，后跟右表源返回的列的列表。CROSS APPLY仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY既返回生成结果集的行，又返回不生成结果集的行。

b.a内连接

示例：查找pxscj数据库每个学生的情况及选修的课程情况

SELECT * FROM xsb INNER JOIN cjb ON xsb.学号=cjb.学号

内连接是系统默认的，可以省略INNER关键字，使用内连接后让可以使用WHERE子句指定条件,内连接还可用于多表的连接

示例：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩

SELECT xsb.学号,姓名,课程名,成绩 FROM xsb JOIN cjb JOIN kcb ON cjb.课程号=kcb.课程号 ON xsb.学号=cjb.学号 WHERE 课程名='计算机基础' AND 成绩>=80

示例：查找不同课程成绩相同的学生的学号、课程号和成绩。

SELECT a.学号,a.课程号,b.课程号,a.成绩 FROM cjb a JOIN cjb b ON a.课程号 !=b.课程号 AND a.成绩=b.成绩 AND a.学号 =b.学号

b.b外连接：指定了OUTER关键字的为外连接，外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行，OUTER关键词可以省略。外连接包括三种

左外连接：结果表中除了包括满足连接条件的行外，还包括左表的所有的行

右外连接：结果表中除了包括满足连接条件的行外，还包括右表的所有的行

完全外连接：结果表中除了包括满足连接条件的行外，还包括两个表的所有的行

示例：查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要包括其情况

SELECT xsb.*,课程号 FROM xsb LEFT OUTER JOIN cjb ON xsb.学号=cjb.学号

示例：查找被选修了的课程的选修情况和所有开设的课程名

SELECT cjb.*,课程名 FROM cjb RIGHT JOIN kcb ON cjb.课程号=kcb.课程号

c.交叉连接， 交叉连接实际上是将两个表进行笛卡尔积运算，结果表是由第一个表的每一行与第二个表的每一行进行拼接后形成的表，因此结果表的行数等于两个表的行数之积。交叉连接也可以用WHERE子句限定。

示例：列出所有学生的所有可能的选课情况

SELECT 学号,姓名,课程号,课程名 FROM xsb CROSS JOIN kcb

 

13.指定查询结果分组方法：GROUP BY

GROUP BY 子句主要用于根据字段进行分组。例如，根据学生所学专业对xsb中的所有行进行分组，结果是每个专业的学生成为一组。GROUP BY子句有ISO标准和非ISO标准。以下是ISO标准语法格式：

GROUP BY {<列表达式> |ROLLUP (<复合元素列表>) |CUBE(<复合元素列表>) |GROUPING SETS(<分组集合项列表>)}

说明：

<列表达式>：指定分组的字段名表达式

ROLLUP：生成简单的GROUP BY聚合行、小计行或超聚合行，还生成一个总计行，返回的分组数等于<复合元素列表>中的表达式数+1，功能与非ISO标准语法中的WITH ROLLUP子句类似。

CUBE：生成简单的GROUP BY聚合行、ROLLUP超聚合行和交叉表格行。CUBE针对<复合元素列表>中表达式的所有排列输出一个分组。生成的分组数等于2n，其中，n为<复合元素列表>中的表达式数，功能与WITH CUBE子句类似。

GROUPING SETS：在一个查询中指定数据的多个分组。仅聚合指定组，而不聚合由CUBE或ROLLUP生成的整组聚合。其结果与针对指定的组执行UNION ALL运算等效。GROUPING SETS可以包含单个元素或元素列表。

由于ISO标准的GROUP BY子句只有在数据库兼容级别时才能使用。设置数据库的兼容级别格式如下：

ALTER DATABASE 数据库名 SET COMPATIBILITY_LEVEL ={90|100|110} 90,100,110分别代表SQL Server 2005，SQL Server 2008，SQL Server 2012

示例：在pxscj数据库上产生一个结果集，包括每个专业的男生人数、女生人数、总人数及学生总人数

SELECT 专业,性别,COUNT(*) AS '人数' FROM xsb GROUP BY ROLLUP(专业,性别)

示例：在pxscj数据库上产生一个结果集，包括每个专业的男生人数、女生人数、总人数、男生总数、女生总数及学生总人数

SELECT 专业,性别,COUNT(*) AS '人数' FROM xsb GROUP BY CUBE(专业,性别)

示例：生成一个结果集，分别根据专业和性别对人数进行聚合

SELECT 专业,性别,COUNT(*) AS '人数' FROM xsb GROUP BY GROUPING SETS(专业,性别)

 

14.指定查询结果分组后的筛选条件：HAVING

使用GROUP BY子句和聚合函数对数据进行分组后，还可以使用HAVING子句对分组数据进行进一步的筛选。

HAVING语法格式：

[HAVING <查询条件>] ，其中查询条件与WHERE子句的查询条件类似，不过HAVING子句中可以使用聚合函数，而WHERE子句中不可以。

示例：查找平均成绩在85分以上的协会说呢过的学号和平均成绩

SELECT 学号,AVG(成绩) AS '平均成绩' FROM cjb GROUP BY 学号 HAVING AVG(成绩)>=85

注意：在SELECT语句中，当WHERE、GROUP BY、HAVING子句都被使用时，要注意他们的作用和执行顺序。WHERE用于筛选由FROM子句指定的数据对象，GROUP BY用于对WHERE 的结果进行分组，HAVING则是对GROUP BY以后的分组数据进行过滤。

示例：查询选修课程超过两门且成绩都在80分以上的学生的学号

SELECT 学号 FROM cjb WHERE 成绩>=80 GROUP BY 学号 HAVING COUNT(*)> 2

示例：查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩

SELECT 学号,AVG(成绩) AS '平均成绩' FROM cjb WHERE 学号 IN (SELECT 学号 FROM xsb WHERE 专业='通信工程') GROUP BY 学号 HAVING AVG(成绩)>=85

先执行WHERE查询条件中的子查询，得到通信工程专业所有学生的学好集，然后对cjb表中的每一条记录，判断其学号字段值是否在前面所求得的学号集中，若否，则跳过记录，继续处理下一条记录，若是，则加入WHERE查询的结果集

对cjb表均筛选完后，按学号进行分组，再在各分组记录中选出平均成绩值大于等于85的记录形成最后的结果集。

 

15.指定查询结果排序顺序：ORDER BY

格式如下：[ORDER BY {排序表达式 [COLLATE 排序名] [ASC|DESC]}]

说明，“排序表达式”可以是列名、表达式或一个正整数，当它是一个正整数时，表示按表中该位置上的列排序；“排序名”是windows排序规则名称或SQL排序规则名称；关键字ASC标识升序排列；DESC表示降序排列，系统默认ASC。

a.对查询的结果排序输出

示例：将计算机专业学生的“计算机基础”课程成绩按降序排列

SELECT 姓名,课程名,成绩 FROM xsb,kcb,cjb WHERE xsb.学号=cjb.学号 AND cjb.课程号=kcb.课程号 AND 课程名='计算机基础' AND 专业='计算机' ORDER BY 成绩 DESC

b.对结果排序附加汇总：可以与COMPUTE BY（已废弃，不再使用）子句一起使用，在对结果排序的而同时还产生附加的分类汇总行。

示例：将学生按专业排序，并汇总各专业人数和平均学分

SELECT 学号,姓名,出生时间,总学分 FROM xsb ORDER BY 专业 COMPUTE COUNT(学号),AVG(总学分) BY 专业

 

16.其他：INTO/UNION/EXCEPT/INTERSECT/CTE

a.INTO：使用INTO子句可以将SELECT查询所得的结果保存到一个新建的表中，语法：[INTO 新表]

示例：由xsb表创建“计算机学生表”包括学号和姓名

SELECT 学号,姓名 INTO 计算机系学生 FROM xsb WHERE 专业='计算机'

b.UNION:使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集，语法格式如下：

{<查询规范>|(<查询表达式>)} UNION [ALL] <查询规范>|(<查询表达式>) [UNION [ALL] <查询规范>|(<查询表达式>)[...]]

规则：所有查询的列数和列的顺序必须相同；数据类型必须兼容；关键字ALL标示合并的结果中包括所有的行，不去重复行，不使用ALL时则在合并的结果中去除重复行。含有UNION的SELECT查询也称联合查询，若不指定INTO子句，结果将合并到第一个表中

示例：在“计算机学生”和“通信工程学生”表中查找学号为191301和学号为221301的两位同学的姓名

SELECT * FROM 计算机系学生 WHERE 学号='191301' UNION ALL SELECT * FROM 通信工程学生 WHERE 学号='221301'

UNION操作常用于归档数据，如归档月报表形成年报表、归档各部门数据等，还可以与GROUP BY及ORDER BY一起使用，用来对合并所得的结果表进行分组或排序

c.EXCEPT/INTERSECT:用于比较两个查询的结果，返回非重复值，格式如下：

{<查询规范>|(<查询表达式>)} {EXCEPT|INTERSECT} {<查询规范>|(<查询表达式>)}

EXCEPT从EXCEPT关键字的左边查询中返回右边查询没有找到的所有非重复值。INTERSECT返回INTERSECT关键字左右两边的两个查询都返回的所有非重复值。EXCEPT/INTERSECT返回的结果集的列名与关键字左侧的查询返回的列名相同。

示例：查找专业为计算机但性别不为男的学生信息。

SELECT * FROM xsb WHERE 专业='计算机' AND 性别=0

SELECT * FROM xsb WHERE 专业='计算机' EXCEPT SELECT * FROM xsb WHERE 性别=1

示例：查找总学分大于42且性别为男的学生信息

SELECT * FROM xsb WHERE 总学分>=42 AND 性别=1

SELECT * FROM xsb WHERE 总学分>=42 INTERSECT SELECT * FROM xsb WHERE 性别=1

d.CTE:在SELECT语句的最前面可以使用一条WITH子句来指定临时结果集，格式如下：[WITH<公用表表达式>[,...]] SELECT...

<公用表表达式>::=表达式名[(列名[,...])] AS (CTE查询定义)

临时命名的结果集也称为公用表表达式（Common Table Expression，CTE），在SELECT、INSERT、DELETE、UPDATE或CREATE VIEW语句中都可以建立一个CTE。CTE相当于一个临时表，只不过他的生命周期在该批处理语句执行完后就结束。

“表达式名”是CTE的名称，“列名”指定查询语句“CTE查询定义”返回数据字段名称，其个数要和“CTE查询定义”返回的字段个数相同。若不定义，则直接命名查询语法的数据集合字段名称为返回数据的字段名称。CTE下方的SELECT语句可以直接查询CTE中的数据。

示例：使用CTE从cjb表中查询选了101号课程的学生学号、成绩，并定义新的列名为number、point。再使用SELECT语句从CTE和xsb中查询姓名为“王林”的学生学号和成绩情况。

WITH cte_stu(number,point) AS (SELECT 学号,成绩 FROM cjb WHERE 课程号='101') SELECT number,point FROM cte_stu,xsb WHERE xsb.姓名='王林' AND xsb.学号=cte_stu.number

 

4.3视图

视图是从一个或多个表导出的表。视图是数据库的用户使用数据库的观点，能够根据用户的观点定义数据结构，视图与表（有时为与视图区别，也称表为基本表）不同，视图是一个虚表，即视图所对应的的数据不进行实际c存储，数据库中只存储视图的定义，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。视图一经定义后，就可以像表一样被查询、修改、删除、更新。注意：只有当前的数据库中才能创建视图。视图的命名必需遵循标识符命名规则，不能与表同名，不能把规则、默认值或触发器与视图相关联。

4.3.2 创建视图：CREATE VIEW

CREATE VIEW cxs1 AS SELECT 学号,姓名,性别,出生时间,总学分 FROM xsb WHERE 专业=  '计算机'

语法格式如下：

CREATE VIEW [架构名 .] 视图名[(列[,...])]

[WITH<视图属性>[,...]]

AS SELECT 语句 [;]

[WITH CHECK OPTION ]

说明：

（1）架构名：数据库架构名

（2）列：列名，它是视图中包含的列，可以有多个列名。若使用与源表或视图相同的列名，则不必给出列名

（3）WITH<视图属性>：指出视图的属性

（4）SELECT语句：用来创建视图的SELECT语句，可在SELECT语句中查询多个表或视图，以表明新创建的视图所参照的表或视图，SELECT语句限制：有权限，不能用ORDER BY和INTO子句，不能在临时表或表变量上创建视图

（5）WITH CHECK OPTION 指出在视图上所进行的修改都要符合SELECT语句所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。’

<视图属性>::={[ENCRYPTION][SCHEMABINDING][VIEW_METADATA]}

ENCRYPTION:说明在系统表syscomments中存储CREATE VIEW语句时进行加密

SCHEMABINDING：说明将视图与其所依赖的表或视图结构相关联

VIEW_METADATA：当引用视图的浏览模式的元数据时，向DBLIB、ODBC、OLEDB API返回有关视图的元数据信息，而不返回基本表的元数据信息

示例：创建ccj视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都符合专业为计算机这个条件

CREATE VIEW ccj WITH ENCRYPTION AS SELECT xsb.学号,课程号,成绩 FROM xsb,cjb WHERE xsb.学号=cjb.学号 AND 专业='计算机' WITH CHECK OPTION

 

分区视图

分区视图在一台或多台服务器间水平连接一组成员表中的分区数据，使数据看起来就像来自一个表，一般情况下，如果视图为下列格式，则其为分区视图

CREATE VIEW 视图名

AS

SELECT <选择列表1>

FROM T1

UNION ALL

SELECT <选择列表2>

FROM T2

UNION ALL

......

SELECT <选择列表n>

FROM Tn

如果要创建分布式分区视图，则需要连接到相应的服务器，使用其他服务器的表时需要指定表所属的服务器、数据库、架构名等信息。

4.3.3查询视图：SELECT

定义完视图后就可以像查询基本表那样对视图进行查询

4.3.4更新视图：INSERT/UPDATE/DELETE

可更新视图要满足以下条件：创建视图的SELECT语句中没有聚合函数，且没有TOP、GROUP BY、UNION子句及DISTINC关键字；创建视图的SELECT语句中不包含从基本表列通过计算机所得的列；创建视图的SELECT语句的FROM子句中至少要包含一个基本表。

 

可更新分区的视图。在实现分区视图之前，必须先实现水平分区表。原始表被分成若干个小的成员表，每个成员表包含与原始表相同数量的列，并且每一列具有与原始表中相应列同样的特性（如数据类型、大小、排序等）设计好成员表后，每个表基于键值的范围存储原始表的一块水平区域。键值范围基于分区列中的数据值。每一成员表中的值范围通过分区列上的CHECK约束强制，并且范围之间不能重叠。对这样的基本表使用UNION ALL联合运算符所创建的分区视图就是可更新的。

 

通过INSTEAD OF 触发器创建的可更新视图。对视图进行更新操作时，要注意基本表对数据的各种约束和规则要求。

示例：向cxs视图中插入以下记录('191315','刘明义',1,'1996-3-2','计算机',50,NULL)

INSERT INTO cxs VALUES ('191315','刘明义',1,'1996-3-2','计算机',50,NULL)  cxs依赖表xsb，查询xsb可以查看插入记录，当视图所依赖的表有多个时，不能向该视图插入数据，因为会影响多个基本表

示例：修改数据使用UPDATE将cxs视图中所有学生的总学分+1

UPDATE cxs SET 总学分=总学分+1

示例：删除数据，使用DELETE语句可以通过视图删除基本表的数据。但要注意，对于依赖多个基本表的视图，不能用DELETE语句， 删除cxs中女同学的记录

DELETE FROM cxs WHERE 性别=0

 

4.3.5修改视图定义：ALTER VIEW

语法格式如下：ALTER VIEW [架构名.]视图名[(列[,...])] [WITH<视图属性>[,...]] AS SELECT 语句[;] [WITH CHECK OPTION] 参数大多与CREATE VIEW相同

示例：视图cjj是加密存储视图，修改其定义，包括学号、姓名、选修的课程号、课程名和成绩

ALTER VIEW cjj WITH ENCRYPTION

AS

SELECT xsb.学号,xsb.姓名,cjb.课程号,kcb.课程名,成绩 FROM xsb,cjb,kcb WHERE xsb.学号=cjb.学号 AND cjb.课程号=kcb.课程号 AND 专业='计算机' WITH CHECK OPTION

4.3.6删除视图 DROP VIEW

语法：DROP VIEW [架构名.]视图名[,...]

 

 

4.4游标

SQL Server通过游标（Cursor）提供了对一个结果集进行逐行处理的能力，游标可以看做一种特殊的指针，它与某个查询结果相联系，可以指向结果集的任意位置，以便对指定位置的数据进行处理。使用游标可以在查询数据的同时对数据进行处理。

游标分两类：前端（客户端）游标和后端（服务器端）游标

游标的使用遵循以下步骤：声明游标-打开游标-读取数据-关闭游标-删除游标

4.4.1声明游标：DECLARE CURSOR

两种标准：SQL-92标准和T-SQL扩展游标声明标准

(1)SQL-92标准

语法格式如下：

DECLARE 游标名 [INSENSITIVE] [SCROLL] CURSOR

FOR SELECT语句

[FOR READ ONLY | UPDATE [OF 列名...]]

说明：

游标名：它是与某个查询结果集相联系的符号名，要符合SQL Server标识符的命名规则。

INSENSITIVE:指定系统将创建供所定义的游标使用数据的临时复本，对游标的所有请求都从tempdb中的该临时表中得到应答。因此，在对该游标进行提取操作时返回的数据中不反映对基本表所做的修改，并且对该游标不允许修改。如果省略INSENSITIVE，则任何用户对基本表提交的删除和更新都反应在后面的提取中。

SCROLL：说明所声明的游标可以前滚、后滚，可使用所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）.如果省略SCROLL，则只能使用NEXT提取选项

SELECT语句：由该查询产生与所声明的游标相关联的结果集，该语句中不能出现INTO或FOR BROWSE

READ ONLY:说明所声明的游标为只读的。UPDATE指定游标中可以更新的列。若有参数“OF 列名”，则只能修改给出的这些列；若在UPDATE中未指出列，则可以修改所有列。

示例：定义一个符合SQL-92标准的游标声明

DECLARE xs_curl CURSOR

FOR

SELECT 学号,姓名,性别,出生时间,总学分 FROM xsb WHERE 专业='计算机' FOR READ ONLY

 

(2)T-SQL扩展游标声明标准

语法格式如下：

DECLARE 游标名  CURSOR

[LOCAL | GLOBAL]                                     /*游标作用域*/

[FORWARD_ONLY | SCROLL ]                             /*游标移动方向*/

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]           /*游标类型*/

[READ_ONLY|SCROLL_LOCKS | OPTIMISTIC ]               /*访问属性*/

[TYPE_WARNING]                                       /*类型转换警告信息*/

FOR SELECT语句                                       /*SELECT查询语句*/

[FOR UPDATE  [OF 列名[,...]]]                        /*可修改的列*/

说明：

LOCAL | GLOBAL：说明游标的作用域，LOCAL声明的是局部游标，作用域为创建它的批处理、存储过程或触发器。GLOBAL声明全局游标，它在由连接执行的任何存储过程或批处理中都可以使用，在连接释放时，游标自动释放。若两者都没指定，则默认值由default to local cursor数据库选项的设置控制。

FORWARD_ONLY | SCROLL:说明游标移动的方向，FORWARD_ONLY表示游标只能从第一行滚动到最后一行，即该游标只能支持FETCH的NEXT提取选项。SCROLL含义与SQL-92标准相同。

STATIC | KEYSET | DYNAMIC | FAST_FORWARD：

游标的四种类型，

静态游标：关键字STATIC，与92标准的INSENSITIVE关键字功能相同；

动态游标：关键在DYNAMIC，反映对结果集汇总所做的修改，结果集中的行数据值、顺序和成员在每次提取时都会改变，所有用户做的UPDATE、INSERT和DELETE语句均通过游标反映出来，不支持ABSOLUTE提取；

只进游标：关键在FAST_FOWRWARD，只支持游标从头到尾顺序提取数据，对所有由当前用户或其他用户提交并影响结果集中的行的INSERT、UODATE和DELETE语句对数据的修改在从游标中提取时可立即反映出来，但因只进游标不能向后滚动，所以在行提取后对行所做的更改对游标是不可见的；

键集驱动游标：关键字KEYSET，这种游标是由称为键的列或列的组合控制的，成员和顺序是固定的，键集驱动游标中数据行的键值在游标打开时建立在tempdb数据库中，可以通过键集驱动游标修改基本表中的非关键字列的值，但不可插入数据。

游标类型与移动方向之间的关系：

FAST_FORWARD不能与SCROLL一起使用，且FAST_FORWARD与FORWARD_ONLY只能选一个；若指定了移动方向为FORWARD_ONLY，而没有用STATIC、KETSET或DYNAMIC关键字，则默认为动态游标；若移动方向FORWARD_ONLY和SCROLL都没指定，若指定游标类型为STATIC、KETSET或DYNAMIC，则移动方向默认为SCROLL；否则为FORWARD_ONLY。

READ_ONLY|SCROLL_LOCKS | OPTIMISTIC：指明游标或基本表的访问属性，READ_ONLY说明游标只读，不能通过该游标更细数据；SCROLL_LOCKS说明通过游标完成的定位更新和定位删除可以成功。如果声明中已指定了关键字FAST_FORWARD，则不能指定SCROLL_LOCK。OPTIMISTIC说明，如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功，若声明中指定了关键字FAST_FORWARD，则不能指定SCROLL_LOCK。OPTIMISTIC说明，如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功，若声明中指定了关键字FAST_FORWARD，则

TYPE_WARNING：若游标从所请求的类型隐形转换为另一种类型，则给客户端发送警告消息

FOR SELECT语句：与92标准相同

FOR UPDATE：指出右表中可以更新的列，若有参数OF 列名[,...]，则只能修改给出的这些列；若在UPDATE中未指出列，则可以修改所有列。

示例：定义一个T-SQL扩展游标声明。

DECLARE xs_cur2 CURSOR

DYNAMIC

FOR

SELECT 学号,姓名,总学分 FROM xsb WHERE 专业='计算机' FOR UPDATE OF 总学分

该语句声明一个名为xs_cur2的动态游标，可前后滚动，可对总学分列进行修改。

 

4.4.2打开游标：OPEN

OPEN {{[GLOBAL] 游标名} | 游标变量名}  其中游标名是要打开的游标名，“游标变量名”是用于引用一个游标。GLOBAL说明打开的是全局游标，否则打开局部游标。

示例：定义游标xs_cur3，然后打开游标，输出其行数

DECLARE xs_cur3 CURSOR

LOCAL SCROLL SCROLL_LOCKS

FOR SELECT 学号,姓名,总学分 FROM xsb FOR UPDATE OF 总学分

OPEN xs_cur3

SELECT '游标xs_cur3数据行数' = @@CURSOR_ROWS

 

4.4.3读取数据：FETCH

游标打开后，即可使用FETCH语句从中读取数据，格式如下：

FETCH

[ [NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n |@nvar}|RELATIVE{n |@nvar}] FROM ]

{{[GLOBAL] 游标名} |@游标变量名}

[INTO @变量名[,...]]

说明：

游标名：要从中提取数据的游标名。

@游标变量名：引用要进行提取操作的已打开的游标。

NEXT|PRIOR|FIRST|LAST：说明读取数据的位置。NEXT说明读取当前行的下一行，并且使其成为当前行。如果FETCH NEXT是对游标的第一次提取操作，则读取的是结果集的第一行。NEXT为默认的游标提取选项。PRIOR说明读取当前行的前一行，并且使其置位当前行。FIRST读取游标中的第一行并将其作为当前行。LAST读取游标中的最后一行并将其作为当前行。FIRST和LAST不能在只进游标中使用。

ABSOLUTE{n |@nvar}和RELATIVE{n |@nvar}：给出读取数据的位置与游标头或当前位置的关系，其中n必须为长整型常量，变量@nvar必须为smallint、tinyint或int类型。

ABSOLUTE{n |@nvar}：若n 或@nvar为正数，则读取从游标头开始的第n行，并将读取的行变成新的当前行；若n 或@nvar为负数，则读取游标尾之前的第n行，并将读取的行变成新的当前行，若n 或@nvar为0，则没有行返回。

RELATIVE{n |@nvar}：若n 或@nvar为正数，则读取当前行之后的第n行，并将读取的行变成新的当前行；若n 或@nvar为负数，则读取当前行之前的第n行，并将读取的行变成新的当前行，若n 或@nvar为0，则读取当前行。如果在对游标的第一次提取操作时将FETCH RELATIVE中的n或@nvar指定为负数或零，则没有返回行。

INTO:说明将读取的游标数据存放到指定的变量中。

GLOBAL:全局游标。

示例：只读游标的使用

DECLARE xs_curl CURSOR

FOR

SELECT 学号,姓名,性别,出生时间,总学分 FROM xsb WHERE 专业='计算机' FOR READ ONLY

GO

OPEN xs_curl

GO

FETCH NEXT FROM xs_curl

GO

FETCH NEXT FROM xs_curl

GO

由于xs_cur1是只进游标，所以只能使用NEXT提取数据。

示例：动态游标的使用

DECLARE xs_cur2 CURSOR

DYNAMIC

FOR

SELECT 学号,姓名,总学分 FROM xsb WHERE 专业='计算机' FOR UPDATE OF 总学分

OPEN xs_cur2

FETCH NEXT FROM xs_cur2

FETCH NEXT FROM xs_cur2

FETCH RELATIVE 2 FROM xs_cur2

FETCH PRIOR FROM xs_cur2

FETCH RELATIVE -3 FROM xs_cur2

SELECT '执行情况' = @@FETCH_STATUS

FETCH FIRST FROM xs_cur2

从游标xs_cur2中提取第一行数据的代码：FETCH NEXT FROM xs_cur2

从游标xs_cur2中提取下一行数据的代码：FETCH NEXT FROM xs_cur2

从游标xs_cur2中提取上一行数据的代码：FETCH PRIOR FROM xs_cur2

从游标xs_cur2中提取最后一行数据的代码：FETCH LAST FROM xs_cur2

读取当前行的下两行的代码：FETCH RELATIVE 2 FROM xs_cur2  如果是-2，则读取当前行的上两行

xs_cur2是动态游标，可以前滚、后滚，可以使用FETCH语句中除ABSOLUTE以外的提取选项。

FETCH语句的执行状态保存在全局变量@@FETCH_STATUS中，其值为0时表示上一个FETCH执行成功；为1时表示所要读取的行不在结果集中；为-2时表示被读取的行已不存在（已被删除） FETCH RELATIVE -3 FROM xs_cur2 SELECT 'FETCH执行情况'=@@FETCH_STATUS

 

4.4.4关闭和删除游标：CLOSE/DEALLOCATE

关闭游标，语法格式如下：

CLOSE{{[GLOBAL] 游标名}|@游标变量名 }

示例：CLOSE xs_cur2

删除游标：若确认游标不再需要，就要释放其定义占用的系统空间，即删除游标，语法格式如下：

DEALLOCATE{{[GLOBAL] 游标名}|@游标变量名 }

示例：DEALLOCATE xs_cur2