SQL server数据库资料第02章(有红色标注)

第2章 数据检索

2.1 基础知识

T-SQL语句中的SELECT语句用于从数据库表或视图中查询数据，并且可以从一个或多个表/视图中选择一个或多个行/列。虽然SELECT 语句的完整语法比较复杂，但下面会分为一个个子句进行介绍，其主要子句包括：SELECT子句、FROM子句、WHERE子句、GROUP BY子句、HAVING子句、ORDER BY子句。在查询之间还可以使用UNION、EXCEPT和INTERSECT运算符，将各个查询的结果合并或比较到一个结果集中。

SELECT语句的语法基本结构如下： SELECT [ ALL | DISTINCT ]

[ TOP expression [ PERCENT] [ WITH TIES] ] < select_list >

[ INTO new_talbe ]

[ FROM { } [ ,…n ] ] [ WHERE ]

[ GROUP BY [ ALL ] group_by_expression [ ,…n ] [ WITH {CUBE | ROLLUP }] ]

[ HAVING ]

2.1.1 简单的数据查询

SELECT子句的作用是指定查询返回的列。 FROM子句的作用是指定要查询数据的来源。

WHERE子句的作用是定义要返回的行应满足的条件。可包含谓词BETWEEN、

CONTAINS 、EXISTS 、FREETEXT 、IN 、IS [NOT] NULL 、LIKE ，以及使用逻辑运算符 AND、OR 和 NOT 的一个或多个谓词的组合的搜索条件。

WHERE子句中谓词LIKE的语法是：

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]  match_expression：任何有效的字符数据类型的表达式。

 pattern：在match_expression 中搜索并且可以包括下列有效通配符的特定字符串。

表2.1 通配符 通配符 % _(下划线) [ ] 说明 包含零个或多个字符的任意字符串。 任何单个字符。 指定范围([a-f])或集合([abcdef])中的任何单个字符。 示例 WHRER title LIKE „%computer%‟ 将查询在书名中任意位置包含单词“computer”的所有书名。 WHERE au_fname LIKE „_ean‟ 将查询以ean结尾的所有4个字母的名字(Dean、Sean等)。 WHERE au_lname LIKE „[C-P]arsen‟ 将查询以arsen结尾并且以介于C与P之间的任何单个字符开始的作者姓氏，例如Larsen、Karsen等。 WHERE au_lname LIKE „de[^l]%‟ 将查询以de开始并且其后的字母不为l的所有作者的姓氏。 [^] 不属于指定范围([a-f])或集合([abcdef])中的任何单个字符。

15

第2章 数据检索

Name like ‘张%’ or Name like ‘王%’

Name like ‘[张王]%’ 2.1.2 联接相关表的查询

用FROM子句指定查询哪些表，使用JOIN操作符可检索多个相关表中列的数据并将结果归为一个数据结果集。JOIN操作符可联接两个及两个以上的表，也可自联接。

联接操作的类型有以下几种：

 INNER：指定返回所有匹配的行对。放弃两个表中不匹配的行。如果未指定任何联

接类型，此设置为默认设置。

 FULL [ OUTER ] ：指定在结果集中包括左表或右表中不满足联接条件的行，并将

对应于另一个表的输出列设为 NULL。这是对通常由 INNER JOIN 返回的所有行的补充。

 LEFT [ OUTER ] ：指定在结果集中包括左表中所有不满足联接条件的行，并在由

内部联接返回所有的行之外，将另外一个表的输出列设为 NULL。

 RIGHT [OUTER] ：指定在结果集中包括右表中所有不满足联接条件的行，且在由

内部联接返回的所有行之外，将与另外一个表对应的输出列设为 NULL。  CROSS ：指定两个表的叉积。

2.1.3 使用聚合函数的查询

聚合函数可以对一组值执行计算，并返回单个值。除了 COUNT 以外，聚合函数都会忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。

聚合函数只能在以下位置作为表达式使用：

 SELECT 语句的选择列表（子查询或外部查询）  HAVING 子句

 COMPUTE 或 COMPUTE BY 子句 Transact-SQL 提供了下列聚合函数：

表2.2 聚合函数 函数 AVG MIN MAX SUM CHECKSUM CHECKSUM_AGG COUNT COUNT_BIG STDEV STDEVP VAR VARP GROUPING 返回组中各值的平均值。 返回表达式中的最小值。 返回表达式中的最大值。 返回表达式中所有值的和或仅非重复值的和。SUM 只能用于数字列。 返回按照表的某一行或一组表达式计算出来的校验和值。CHECKSUM 用于生成哈希索引。 返回组中各值的校验和。 返回组中的项数。返回 int 数据类型值。 返回组中的项数。返回 bigint 数据类型值。 返回指定表达式中所有值的标准偏差。 返回指定表达式中所有值的总体标准偏差。 返回指定表达式中所有值的方差。 返回指定表达式中所有值的总体方差。 当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。 16

说明 SQL Server 2008高级开发与应用

数据查询中使用GROUP BY子句指定用来放置输出行的组。如果 SELECT 子句

中包含聚合函数，则 GROUP BY 将计算每组的汇总值。指定GROUP BY 时，选择列表中任意非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。其语法为：

[ GROUP BY [ ALL ] group_by_expression [ ,...n ] [ WITH { CUBE | ROLLUP } ] ] GROUP BY子句的参数如下：

 ALL：包含所有组和结果集，甚至包含那些其中任何行都不满足 WHERE 子句指

定的搜索条件的组和结果集。

 group_by_expression：进行分组所依据的表达式。group_by_expression 也称为组合

列。group_by expression 可以是列，也可以是引用由 FROM 子句返回的列的非聚合表达式。  CUBE：指定在结果集内不仅包含由 GROUP BY 提供的行，还包含汇总行。GROUP

BY 汇总行针对每个可能的组和子组组合在结果集内返回。GROUP BY 汇总行在结果中显示为 NULL，但用来表示所有值。使用 GROUPING 函数可确定结果集内的空值是否为 GROUP BY 汇总值。

 ROLLUP：指定在结果集内不仅包含由 GROUP BY 提供的行，还包含汇总行。按

层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取决于列分组时指定使用的顺序。更改列分组的顺序会影响在结果集内生成的行数。

2.1.4 数据集的操作

在查询之间还可以使用UNION、EXCEPT和INTERSECT运算符，将各个查询的结果合并或比较到一个结果集中。

相当于数学中的集合操作A并B , A-B, A交B

UNION将两个或更多查询的结果组合到单个结果集中，该结果集由从属于联合的所有查询的所有行组成。其语法：

{ < query_specification > | ( < query_expression > ) } UNION [ ALL ]

< query_specification> | ( < query_expression > ) [ UNION [ ALL ]

< query_specification | ( < query_expression > ) [ ...n ] ] 参数：

 < query_specification > | ( < query_expression > ) ：查询说明或查询表达式，它返回

要与来自其他查询说明或查询表达式的数据进行组合的数据。列的定义（UNION 运算的一部分）不必完全相同，但它们必须能够通过隐式转换相互兼容。  UNION：指定组合多个结果集并返回为单个结果集。

 ALL：将所有行合并到结果中，包括重复的行。如果不指定，将删除重复的行。 使用EXCEPT 和 INTERSECT运算符可以比较两个查询的结果，返回非重复值。EXCEPT 从左查询中返回右查询没有找到的所有非重复值。INTERSECT 返回 INTERSECT 操作数左右两边的两个查询都返回的所有非重复值。

以下是将使用 EXCEPT 或 INTERSECT 的两个查询的结果集组合起来的基本规则：  所有查询中的列数和列的顺序必须相同。  数据类型必须兼容。

17

第2章 数据检索

使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。

APPLY 有两种形式： CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。

2.1.5常用的内置函数

SQL Server 2008为T-SQL提供了很多函数，每个函数都能实现不同的功能，例如前面介绍过的COUNT函数和SUM函数。SQL Server 2008将函数分为聚合函数、配置函数、游标函数、日期和时间函数、数学函数、元数据函数、行集函数、安全函数、字符串函数、系统统计函数、文本和图像函数以及其他函数十二类。下面列出一些常用的内置函数。

表2.3 日期和时间函数

函数 DATEADD DATEDIFF DATENAME DATEPART DAY GETDATE GETUTCDATE 新 datetime 值 返回跨两个指定日期的日期边界数和时间边界数 返回表示指定日期的指定日期部分的字符串 返回表示指定日期的指定日期部分的整数 返回一个整数，表示指定日期的天 DAY(date) datepart 部分 以 datetime 值的 SQL Server 标准内部格式返回当前系统日期和时间 返回表示当前的 UTC 时间（通用协调时间或格林尼治标准时间）的 datetime 值。当前的 UTC 时间得自当前的本地时间和运行 Microsoft SQL Server 实例的计算机操作系统中的时区设置 MONTH 返回表示指定日期的“月”部分的整数 MONTH(date) GETUTCDATE( ) GETDATE( ) DATEPART(datepart,date) 说明 返回给指定日期加上一个时间间隔后的date) 语法 DATEADD(datepart,number,DATEDIFF(datepart,startdate,enddate) DATENAME(datepart,date) YEAR SYSDATETIME 返回表示指定日期的年份的整数 以 datetime2 值的 SQL Server 标准内部格式返回当前系统日期和时间 YEAR(date) SYSDATETIME( ) SYSDATETIMEOFFSET 以 datetimeoffset 值的 SQL Server 标准内部格式返回当前系统包含时区信息的日期和时间 SYSDATETIMEOFFSET( ) 18

SQL Server 2008高级开发与应用

SYSGETUTCDATE 返回表示当前的 UTC 时间（通用协调时间或格林尼治标准时间）的 datetime2值。当前的 UTC 时间得自当前的本地时间和运行 Microsoft SQL Server 实例的计算机操作系统中的时区设置 SYSGETUTCDATE( ) ★ 注意：表2.3中后三个函数为SQL Server 2008新增日期和时间函数

表2.4 系统函数

函数 CAST和CONVERT @@ERROR @@TRANCOUNT @@ROWCOUNT Fn_helpcollections ISNULL NEWID SCOPE_IDENTITY 说明 将一种数据类型的表达式显式转换为另一种数据类型的表达式 返回执行的上一个T-SQL语句的错误号 返回当前连接的活动事务数 返回受上一语句影响的行数 返回SQL Server 2008支持的所有排序规则的列表 使用指定的替换值替换NULL 创建uniqueidentifier类型的唯一值 返回插入到同一作用域中的标识列内的最后一个标识值。一个范围是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。 UPDATE() 返回一个布尔值，指示是否对表或视图的指定列进行了INSERT或UPDATE尝试。可以在T-SQL INSERT或UPDATE触发器主体中的任意位置使用UPDATE()，以测试触发器是否应执行某些操作。 XACT_STATE … 报告会话的事物状态的标量函数，指示会话是否具有活动事务以及是否可以提交事务。 …

表2.5 字符串函数

函数 LEFT RIGHT UPPER LOWER SUBSTRING REPLACE LEN PATINDEX CHARINDEX … 说明 返回字符串中从左边开始指定个数的字符 返回字符串中从右边开始指定个数的字符 返回小写字符数据转换为大写的字符表达式 将大写字符数据转换为小写字符数据后返回字符表达式 返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分 用第三个表达式替换第一个字符串表达式中出现的所有第二个指定字符串表达式的匹配项 返回指定字符串表达式的字符（而不是字节）数，其中不包含尾随空格 回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零 返回字符串中指定表达式的开始位置 …

2.2 典型例题

19

第2章 数据检索

2.2.1 简单的数据查询

【例2.1】带SELECT子句和FROM子句的简单查询

（1）显示表中的所有的列

SELECT * FROM HumanResources.Employee

（2）显示表中的若干列 SELECT LoginID, JobTitle, BirthDate, MaritalStatus, Cender

FROM HumanResources.Employee

（3）显示表中的某一列，去掉重复的列值

SELECT DISTINCT Color FROM Production.Product

【例2.2】带WHERE子句的查询 （1）SELECT * FROM Production.Product

WHERE Color=’Silver’ AND ListPrice>200

（2）SELECT * FROM Production.Product

WHERE Color=’Silver’ AND (ListPrice>200 OR ListPrice=0) ★注意：用圆括号控制操作的次序

【例2.3】结果集的操作 （1）查询中使用列别名和表别名，结果集按某字段排序

SELECT PP.FirstName AS ‘FirstName’, PP.LastName AS ‘Last Name’ , PP.PersonType AS ‘Person Category’

FROM Person.Person AS PP ORDER BY PersonType

（2）结果集允许将多列及字符串文字组合成一列

SELECT PP.FirstName+‘ ’+ PP.LastName AS „Name‟, PP.PersonType AS „Category‟ FROM Person.Person AS PP ORDER BY PersonType （3）结果集中为每行添加一列

SELECT PP.FirstName+„ ‟+ PP.LastName AS „Employee Name‟, P.PersonType AS „Category‟, ‘Employee’ AS Description

FROM Person.Person AS PP WHERE PP.PersonType = ‟EM‟;

2.2.2 联接相关表的查询

【例2.4】已知“库存信息”表和“订单信息”表的记录如下，举例说明JOIN各类型的用法。

图2.1“库存信息”和“订单信息”表记录

（1） SELECT * FROM 库存信息 INNER JOIN 订单信息

ON 库存信息.产品名称=订单信息.产品名称

20

SQL Server 2008高级开发与应用

图2.2 INNER JOIN

（2）

SELECT * FROM 库存信息 LEFT OUTER JOIN 订单信息 ON 库存信息.产品名称=订单信息.产品名称

图2.3 LEFT INNER JOIN

（3）

SELECT * FROM 库存信息 RIGHT OUTER JOIN 订单信息 ON 库存信息.产品名称=订单信息.产品名称

图2.4 RIGHT INNER JOIN

（4） SELECT * FROM 库存信息 FULL OUTER JOIN 订单信息

ON 库存信息.产品名称=订单信息.产品名称

图2.5 FULL INNER JOIN

（5） SELECT * FROM 库存信息 CROSS JOIN 订单信息 图2.6 CROSS JOIN

21

第2章 数据检索

2.2.3 使用聚合函数的查询

【例2.5】有如下关系模式的订单表：订单（订单ID,商品ID,货主城市,订购日期,货运费, 发货日期,…）。

（1）统计每个城市的订单总数。这是GROUP BY的基本用法。

SELECT 货主城市,COUNT(订单ID) AS 订单总数 FROM 订单

GROUP BY 货主城市

图2.7 GROUP BY的用法

（2）按年份统计每个城市的订单总数。这是在GROUP BY中使用表达式的例子

SELECT 货主城市,YEAR(订购日期) AS 订购年份,COUNT(订单ID) AS 订单总数 FROM 订单 GROUP BY

货主城市,YEAR(订购日期) ORDER BY 货主城市,YEAR(订购日期)

图2.8 GROUP BY中使用表达式

（3）使用CUBE运算符对所有字段进行汇总

SELECT 货主城市,COUNT(订单ID) AS 订单总数 FROM 订单

GROUP BY 货主城市 WITH CUBE

图2.9 CUBE运算符的用法

22

SQL Server 2008高级开发与应用

（4）按年份统计并汇总（按年份统计每个城市的订单总数）

SELECT 货主城市,YEAR(订购日期) AS 订购年份,COUNT(订单ID) AS 订单总数

FROM 订单 GROUP BY

货主城市,YEAR(订购日期) WITH CUBE

（5）按城市统计并按城市汇总。这是使用ROLLUP运算符对第一个字段进行汇总。运行结果如图2.11所示。

SELECT 货主城市,YEAR(订购日期)AS 订购年份,COUNT(订单ID) AS 订单总数 FROM 订单 GROUP BY

货主城市,YEAR(订购日期) WITH ROLLUP

（6）按年份统计并按年份汇总。运行结果如图2.12所示。

SELECT 货主城市,YEAR(订购日期) AS 订购年份,COUNT(订单ID)AS 订单总数 FROM 订单 GROUP BY

YEAR(订购日期),货主城市 WITH ROLLUP

23

北京所有年份订单数

常州所有年份订单数

南京所有年份订单数 2007年所有地区订单数

总汇总行

图2.10 CUBE运算符的用法

第2章 数据检索

图2.11 图2.12

（7）聚合函数GROUPING的例子。GROUPING语法：GROUPING ( column_name )

 参数column_name：GROUP BY 子句中的列，用于测试 CUBE 或 ROLLUP 空

值。

SELECT 货主城市, GROUPING(货主城市), YEAR(订购日期)AS 订购年份,

GROUPING(YEAR(订购日期)), COUNT(订单ID) AS 订单总数

FROM 订单

GROUP BY 货主城市,YEAR(订购日期) WITH CUBE

图2.13 GROUPING的用法

（8）统计2009年1月1日以来每个城市的订单总数及运费金额，使用GROUP BY ALL对所有数据分组。 SELECT 货主城市,COUNT(订单ID) AS 订单总数,SUM(货运费) AS 货运费总数

FROM 订单

WHERE 订购日期>'2009-1-1' GROUP BY 货主城市

图2.14 GROUP BY的用法

如果用GROUP BY ALL得到结果就不同了。

24

SQL Server 2008高级开发与应用

SELECT 货主城市,COUNT(订单ID) AS 订单总数,SUM(货运费) AS 货运费总数 FROM 订单

WHERE 订购日期>'2009-1-1' GROUP BY ALL 货主城市

2009年1月1日之后没有订单记录的城市也会在结果集

图2.15 GROUP BY ALL的用法

★注意：GROUP BY ALL 参数不能与CUBE和ROLLUP同时使用。

（9）查看订单数超过10的城市，以及这些城市的总订单数和总运费。 可以使用HAVING对分组设置查询条件。HAVING子句用于指定组或聚合的搜索条件，通常在GROUP BY子句中使用。如果不使用GROUP BY子句，则HAVING的行为与WHERE子句一样。HAVING子句与WHERE子句的区别是：汇总函数只能在HAVING子句中使用。

SELECT 货主城市,COUNT(订单ID) AS 订单总数,SUM(货运费) AS 货运费总数 FROM 订单

GROUP BY 货主城市 HAVING COUNT(订单ID)>10

图2.16 HAVING的用法

可以使用COMPUTE子句归类。COMPUTE用于分组统计，生成合计作为附加的汇总列出现在结果集的最后。当与 BY 一起使用时，COMPUTE 子句在结果集内生成控制中断和小计。可在同一查询内指定 COMPUTE BY 和 COMPUTE。

基本语法：

[ COMPUTE { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } ( expression ) } [ ,...n ] [ BY expression [ ,...n ] ] ]

参数：

 AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM 指定要执

行的聚合。

 Expression指明要用来汇总函数处理的字段或表达式。必须出现在选择列表中，

不能中使用选择列表中所指定的列别名。

 BY expression指明要进行分类的字段（别名）或表达式，此字段名称必须出

现在ORDER BY子句中的一项。

25

第2章 数据检索

（10）汇总归类（查看还没有发货的订单，以及运费的总数。）

SELECT 订单ID,货主城市,货运费 FROM 订单

WHERE 发货日期 IS NULL COMPUTE SUM(货运费)

图2.17 COMPUTE的用法

（11）按城市查看还没有发货的订单，以及运费的总数。（使用COMPUTE BY归类）

SELECT 订单ID,货主城市,货运费 FROM 订单

WHERE 发货日期 IS NULL

ORDER BY 货主城市 DESC COMPUTE SUM(货运费) BY 货主城市

图2.18 COMPUTE BY的用法

2.2.4 数据集的操作

【例2.6】公司要组织供应商和客户开交流会，需要一份通知名单，分别从“供应商”表和“客 户”表查出所有的联系人，并将其合并为一个表。

26

SQL Server 2008高级开发与应用

图2.19 “供应商”表记录 图2.20 “客户”表记录

这是UNION的一个例子。 （1）包含重复行，结果如图2.21所示。

SELECT 联系人姓名,地址,电话 FROM 供应商 UNION ALL

SELECT 联系人姓名,地址,电话 FROM 客户

（2）去掉重复行，结果如图2.22所示。

SELECT 联系人姓名,地址,电话 FROM 供应商 UNION

SELECT 联系人姓名,地址,电话 FROM 客户

（3）加入临时通知人的查询语句如下，结果如图2.23所示。

SELECT 联系人姓名,地址,电话 FROM 供应商 UNION

SELECT 联系人姓名,地址,电话 FROM 客户 UNION

SELECT '赵一','北京中医药大学','010-123456'

图2.21 UNION ALL 图2.22 UNION 图2.23 UNION

有时查询的结果需要排序，可以对UNION操作的结果集进行排序。在UNION结果集里也可以分组查询。 （4）按联系人的姓名排序，结果如图2.24所示。 SELECT 联系人姓名,地址,电话 FROM 供应商

UNION

SELECT 联系人姓名,地址,电话 FROM 客户 ORDER BY 联系人姓名

★注意：ORDER BY子句只能用在整个UNION语句的最后，是针对UNION之后的结果集进行排序，不能用在单个查询语句里。 （5）按地址分组查看相同地址对应的人数，结果如图2.25所示。

SELECT 地址,COUNT(地址) AS 联系人数 FROM

27

第2章 数据检索

SELECT 联系人姓名,地址,电话 FROM 供应商 UNION

SELECT 联系人姓名,地址,电话FROM 客户 ) AS 临时表 GROUP BY 地址

★ 注意：GROUP BY子句只能用在单个查询结果里，不能用在UNION之后的查询结

果集中。如果要在UNION之后的查询结果集中分组或过滤记录，只能将UNION之后的查询结果集视为个数据表，然后在这个数据表中进行分组或过滤记录操作。

(

图2.24 UNION结果集排序图 2.25 UNION结果集分组

【例2.7】EXCEPT和INTERSECT的例子。

图2.26 “供应商”表记录 图2.27 “客户”表记录

（1）查询不是客户的供应商，结果如图2.28所示。

SELECT 联系人姓名,地址,电话FROM 供应商 EXCEPT

SELECT 联系人姓名,地址,电话FROM 客户

（2）查询既是供应商又是客户的详细信息，结果如图2.29所示。

SELECT 联系人姓名,地址,电话FROM 供应商 INTERSECT

SELECT 联系人姓名,地址,电话FROM 客户

图2.28 EXCEPT 图2.29 INTERSECT

2.2.5常用的内置函数

28

SQL Server 2008高级开发与应用

【例2.8】日期和时间函数举例。

（1）假设下列代码运行在2010年9月15日，运行结果如图2.30所示。

图2.30 日期和时间函数的应用

（2）假设下列代码运行在2010年9月15日，运行结果如图2.31所示。

图2.31日期和时间函数的应用

【例2.9】系统函数举例。 （1）CAST和CONVERT的语法如下，运行下列SQL语句，结果如图2.32所示。

29

第2章 数据检索

图2.32 系统函数的应用

（2）返回以 L 开头并且是二进制排序规则的所有排序规则的名称，结果如图2.33。

图2.33系统函数的应用

【例2.10】字符串函数举例，结果如图2.34所示。

30

SQL Server 2008高级开发与应用

图2.34 字符串函数的应用

2.3 实训练习

【练习1】已知有一名为Teaching_System的教学管理系统数据库，其中主要的数据表对应的关系模式如下：

Tb_Dept( DeptId, DeptName)

Tb_Spec( SpecId, SpecName, DeptId)

Tb_TeachingYear( TeachingYearId, TeachingYearName) Tb_Term( TermId, TermName) Tb_Title( TitleId, TitleName)

Tb_Teacher( TeacherId, TeacherName, DeptId, Sex, Birthday,TPassword,TitleId) Tb_Class( ClassId, ClassName, DeptId, TeacherId, ClassStuNumber)

Tb_Student( StuId, StuName, DeptId, SpecId, ClassId, Sex, Birthday, SPassword, ZipCode, Address, StuMemo)

Tb_Course( CourseId, CourseName, DeptId, CourseGrade, LessonTime, CourseOutline)

Tb_CourseClass( CourseClassId, CourseId, TeacherId, TeachingYearId, TeachingTermId, TeachingPlace, TeachingTime, CommonPart, MiddlePart, LastPart, MaxNumber, SelectedNumber, FullFlag)

Tb_SelectCourse( StuId, CourseClassId, SelectDate) Tb_TeachingEvaluation( CourseClassId, StuId, TeacherId, TeachingAttitude, TeachingContent, TeachingMethod, SpareInstruction, Homework)

Tb_Grade( GradeSeedId, StuId, CourseId, CourseClassId, ClassId, CommonScore, MiddleScore, LastScore, TotalScore, RetestScore, Grade, EvaluationFlag, LockFlag)

执行以下SQL语句，理解GROUP BY子句、HAVING子句、COMPUTE子句的用法，文字说明语句实现的功能。

（1） SELECT CourseId,AVG(TotalScore) AS AvgScore

FROM Tb_Grade GROUP BY CourseId

（2） SELECT CourseId,ClassId,AVG(TotalScore) AS AvgScore

FROM Tb_Grade GROUP BY CourseId,ClassId

（3） SELECT CourseId,ClassId,AVG(TotalScore) AS AvgScore

FROM Tb_Grade GROUP BY CourseId,ClassId WITH CUBE

（4） SELECT CourseId,ClassId,AVG(TotalScore) AS AvgScore

FROM Tb_Grade GROUP BY CourseId,ClassId WITH ROLLUP

（5） SELECT CourseId,GROUPING(CourseId),ClassId,GROUPING(ClassId),

AVG(TotalScore) AS AvgScore

FROM Tb_Grade GROUP BY CourseId,ClassId WITH ROLLUP

（6） SELECT CourseId,ClassId,AVG(TotalScore) AS AvgScore

31

第2章 数据检索

（7） （8）

FROM Tb_Grade GROUP BY CourseId,ClassId HAVING AVG(TotalScore)>75 SELECT CourseId,TotalScore

FROM Tb_Grade COMPUTE AVG(TotalScore) SELECT CourseId,TotalScore

FROM Tb_Grade ORDER BY CourseId COMPUTE AVG(TotalScore) BY CourseId

【练习2】已知有一个名为Sales.PotentialClients的表。这个表中包含一名为EmailAddress的列。现在的任务是开发一个返回Sales.PotentialClients表中有效的“.com”email地址的报表。一个有效的email地址必须是在@符号前至少有一个字符，“.com”前@符号后至少有一个字符，（比如abc@dcf.com是有效的email地址）。请写出这个返回满足企业需求数据的T-SQL语句。

【练习3】现有一个名为Employees的表。表的关系模式如下：Employees( EmployeeId, ReportsTo, EmployeesName)，其中表记录如图2.35所示。请写出T-SQL语句返回所有的雇员和他们各自直接领导者的列表，结果如图2.36所示。

图2.35 Employees表记录

图2.36 查询结果

【练习4】bbc表中记录了每个地区的名称、人口和土地面积，关系模式如下：

bbc(region,population,area)。写出T-SQL语句完成下列查询要求。 （1）显示所有国家的总人口数。

（2）显示每个地区的总人口数和总面积。

（3）显示每个地区的总人口数和总面积,仅显示那些面积超过1000000的地区。

【练习5】现有两个结构相同的表Products和NewProducts，它们都包含Product, Description两列数据。请写出T-SQL语句显示两表中都有的行。

【练习6】现有一个名为Subcategories的表，包含了短袜、女士背心和安全帽的子分类。另外一张表Products只包含短袜和女士背心子类的产品。表记录如图2.37、图2.38所示。你有下列查询语句：

SELECT s.Name, p.Name AS ProductName FROM Subcategories s OUT APPLY

(SELECT * FROM Products pr WHERE pr.SubcategoryID=s.SubcategoryID) p

32

SQL Server 2008高级开发与应用

WHRE s.Name IS NOT NULL;

请分析这个查询的结果，如果OUT APPLY改成CROSS APPLY，查询结果又会怎样呢？

图2.37 Subcategories表 图2.38 Products表

33

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文