计算机三级数据库技术 第7章 高级数据库查询

7.1 一般数据查询功能扩展

7.1.1 SELECT语句

SELECT [DISTINCT][TOP n] select_list -- 查询指定的列 
[INTO new_table] -- 将查询结果创建到新表 
[FROM table_source] -- 查询行所在的表 
[WHERE search_conditition] -- 指定返回行的搜索条件 
[GROUP BY group_by_expression] -- 指定查询结果的分组条件 
[HAVING search_condition] -- 指定组或聚合函数的搜索条件 
[ORDER BY order_expression[ASC|DESC]] -- 指定结果集的排序方式 
[COMPUTE expression] -- 在结果集的末尾生成汇总数据行

7.1.2 使用TOP限制结果集

TOP n [percent][WITH TIES]

        Top n 前n行

        Top n [percent] 前n%行

        [WITH TIES] :包括最后一行取值并列的结果。

[例1]查询单价最高的前三种商品的商品名、商品类别和单价，包括并列情况。

SELECT TOP 3 WITH TIES GoodsName，

GoodsClassName , SaleUnitPrice ，FORM

Table_Goods a JOIN Table_GoodsClass b

ON a.GoodsClassID=b.GoodsClassID ORDER BY

SaleUnitPrice DESC

7.1.3 使用CASE函数

➢分情况显示不同类型的数据。CASE函数是一种多分支表达式。

➢两种类型：

        简单CASE函数

        搜索CASE函数

语法:

CASE 
WHEN 布尔表达式1 then 结果表达式1 
WHEN 布尔表达式2 then 结果表达式2 
WHEN 布尔表达式n then 结果表达式n 
[ELSE 结果表达式n+1] 
END

[例]分析下列语句的作用（P110）

SELECT a.GoodsID,商品销售类别=CASE 
WHEN COUNT(b.GoodsID)>10 THEN '热门商品' 
WHEN COUNT(b.GoodsID)BETWEEN 5 AND 10 THEN '一般商品' 
WHEN COUNT(b.GoodsID)BETWEEN 1 AND 4 THEN '难销商品' 
ELSE '滞销商品' 
END 
FROM Table_Goods a LEFT JOIN Table_SaleBillDetail b 
ON a.GoodsID=b.GoodsID GROUP BY a.GoodsID

7.1.4 将查询结果保存到新表中

SELECT 查询列表序列 INTO <新表名> FROM 数据源.....(其他行过滤、分组语句)

注意：表名前加#为局部临时表，##为全局临时表，只有表名为永久表。

例子:

SELECT * INTO #HD_Customer FROM Table_Customer WHERE ......

---

7.2 查询结果的并、交、差运算

7.2.1 并运算

➢并运算( UNION ) : 将多个查询结果合并为一个结果集。

➢语法:

SELECT 语句1 UNION [ALL] SELECT 语句2 UNION [ALL] ......

使用UNION注意：

➢要进行合并的查询，SELECT中列数必须相同，语义相同。

➢每个相对应列的数据类型隐式兼容，如char(20)与varchar(40)。

➢合并后结果采用第一个SELECT语句的列标题。

➢若需排序，则GROUP BY语句写在最后一个SELECT之后，且排序的一句是第一个SELECT中的列名。

7.2.2 交运算

➢交运算：返回同时在两个集合中出现的记录。

➢语法：

SELECT 语句1 INTERSECT SELECT 语句2 INTERSECT ...... SELECT 语句n

7.2.3 差运算

➢差运算：返回第一个集合中有而第二个集合中没有的的记录。

➢语法：

SELECT 语句1 EXCEPT SELECT 语句2 EXCEPT...... SELECT 语句n

---

7.3 相关子查询

        子查询是一条包含在另一条SELECT语句里的SELECT语句。外层的SELECT语句叫外层查询，内层的SELECT语句叫内层查询（或子查询）。

        子查询总是写在圆括号中。

包括子查询的SELECT语句主要采用以下格式中的一种：

        （1）WHERE expression [NOT] IN (subquery)

        （2）WHERE expression comparison_operator [ANY|ALL]

        （3）WHERE [NOT] EXISTS (subquery)

7.3.1 使用子查询进行基于集合的测试

实例：

SELECT Cname,Address FROM Table_Customer 
WHERE Address 
IN(SELECT Address FROM Table_Customer WHERE Cname= '王晓') 
AND Cname ! = '王晓'

7.3.2 使用子查询进行比较测试

实例：查询单价最高的商品的名称和单价

SELECT Goodname，SaleUnitPrice FROM Table_Goods a 
WHERE SaleUnitPrice= (SELECT MAX(SaleUnitPrice) FROM Table_Goods)

7.3.3 使用子查询进行存在性测试

实例：查询购买了单价高于2000元商品额顾客的会员卡号。

SELECT DISTINCT CardID FROM Table_SaleBill 
WHERE EXISTS(SELECT * FROM Table_SaleBilDetail WHERE SaleBillID=Table_SaleBill.SaleBillID AND UnitPrice>2000)

---

7.4 其他形式的子查询

7.4.1 替代表达式的子查询

在SELECT的选择列表中嵌入了一个只返回一个标量值的子查询。

实例:

SELECT Cname,Address,(SELECT COUNT(*) FROM Table_Customer b ON a.CardID=b.CardID WHERE CustomerlID='C001') 
AS TotalTimes FROM Table_Custmer 
Where CustomerID='COO1'

7.4.2 派生表

➢也称为内联视图，是将子查询作为一个表处理，产生的新表为"派生表”。

➢实例：查询至少买了C001和C002两种商品的顾客号和顾客名。

SELECT CustomerID,CName FROM (SELECT * FROM Table_SaleBill a JOIN Table_SaleBillDetail b ON a.SaleBilID=b.SaleBillID WHERE GoodsID='G001') 
AS T1 
JOIN (SELECT * FROM Table SaleBill a JOIN Table_SaleBillDetail b ON a.SaleBilID=b.SaleBillID WHERE GoodsID='G002') AS T2 
ON T1.CardID=T2.CardID JOIN Table_Customer c ON c.CardID=T1.CardID

---

7.5 其他一些查询功能

7.5.1 开窗函数

➢在SQL Server中，一组行被称为一个窗口。

➢与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值 ，因为开窗函数所执行聚合计算的行集组是窗口。

➢与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER关键字。

➢开窗函数的调用格式为:

函数名（列） OVER （选项）

OVER关键字：表示把函数当成开窗函数而不是聚合函数。

SQL标准允许将所有聚合函数用做开窗函数，使用OVER关键字来区分这两种用法。

实例1：将OVER字句与聚合函数结合使用

设有课程表 Course(Cno, CName, Credit, Semester) 查询全部课程的Cno，CName，Semester，Credit信息及其该学期开设课程的总、最高、平均、最低Credit 。

SELECT Cno, CName, Semester, Credit SUM(Credit) 
OVER(PARTITION BY Semester) AS 'Total', AVG(Credit) OVER(PARTITION BY Semester) AS 'Avg'， MIN(Credit) OVER(PARTITION BY Semester) AS 'Min'， MAX(Credit) OVER(PARTITION BY Semester) AS 'Max' FROM Course

实例2：将OVER字句与排名函数一起使用

➢排名函数 RANK()

RANK() OVER([<partition_by_clause>,...[n]]<order_by_clause>)

➢注意：排名函数具有不确定性。排名从1开始，不一定是连续整数。

SELECT OrderID, ProductID, OrderQty RANK() OVER(PARTITION BY OrderID ORDER BY OrderQty DESC) AS RANK FROM OrderDetail ORDER BY OrderID

其他排名函数：

➢DENSE_RANK()

        排名是连续整数

➢NTILE()

        将有序分区中的行划分到指定数目的组中，编号从1开始，函数返回此行所属的组的编号。

➢ROW_NUMBER()

        返回结果集中每个分区内的序列号，每个分区的第一行从1开始。

7.5.2 公用表表达式

➢公用表表达式（CommonTableExpression,CTE）：将查询结果集指定个临时名字，这些命名的结果集就是公用表表达式。

➢格式：

WITH <common_table_expression>[,...n] <common_table_expression>::= Expression_name[(column_name[,...n])] AS (SELECT语句)

例：定义一个统计每个会员购买商品总次数的CTE，并利用CTE查询会员卡号和购买商品的次数。

定义：WITH BuyCount(CardID,Counts)

AS(SELECT CardID,Count(*) FROM Table_SaleBill GROUP BY CardID)

使用：AS(SELECT CardID,Counts FROM BuyCount ORDER BY Counts

---

章末测试

一、选择题

1、设有购买表（顾客号，商品号，购买时间）。现要查询顾客A与顾客B购买的相同商品。有下列查询语句：

I .

SELECT 商品号 FROM 购买表 WHERE 顾客号='A' AND 商品号 IN (SELECT 商品号 FROM 购买表 WHERE 顾客号='B')

II.

SELECT 商品号 FROM 购买表 WHERE 顾客号='A' EXCEPT SELECT 商品号 FROM 购买表 WHERE 顾客号='B'

III .

SELECT 商品号 FROM 购买表 WHERE 顾客号='A' INTERSECT SELECT 商品号 FROM 购买表 WHERE 顾客号='B'

IV .

SELECT 商品号 FROM 购买表 WHERE 顾客号='A' UNION SELECT 商品号 FROM 购买表 WHERE 顾客号='B'

上述语句中，能够实现该查询要求的是( )。

A.仅I和II

B.仅I和III

C.仅I和IV

D.仅III

答案：B

2、设有选课表（学号，课程号，成绩），现要统计每门课程的选课人数，并将结果保存到新表:选课情况表。下列语句中正确的是（ ）

A. SELECT 课程号,COUNT(*) 选课人数 FROM 选课表 INTO 选课情况表 GROUP BY 课程号

B. SELECT 课程号,COUNT(*) 选课人数 INTO 选课情况表 FROM 选课表 GROUP BY 课程号

C. SELECT课程号,COUNT(*) FROM 选课表 INTO 选课情况(课程号,选课人数) GROUP BY 课程号

D . SELECT课程号,COUNT(*) INTO 选课情况表(课程号,选课人数) FROM 选课表 GROUP BY 课程号

答案：B

3、设某数据库中有学生表（学号，姓名，所在系）和选课表（学号，课程号，成绩）。现要查询没选课的学生姓名和所在系。下列语句中能够实现该查询要求的是（ ）。

A . SELECT 姓名，所在系 FROM 学生表 a LEFT JOIN 选课表 b ON a.学号=b.学号 WHERE a.学号 IS NULL

B . SELECT 姓名，所在系 FROM 学生表 a LEFT JOIN 选课表 b ON a.学号=b.学号 WHERE b .学号 IS NULL

C . SELECT 姓名，所在系 FROM 学生表 a RIGHT JOIN选课表 b ONa.学号=b .学号WHEREa .学号IS NULL

D . SELECT 姓名，所在系 FROM 学生表 a RIGHT JOIN 选课表 b ON a.学号=b.学号 WHERE b.学号 IS NULL

答案：B

4、设在SQL Server2008中，用户U1在DB1数据库中创建了#Temp表。下列关于#Temp表的说法中，正确的是（ ）

A.在所有用户U1发起的连接中,都可以查询#Temp表数据

B.只有在创建#Temp表的连接中才可以查询#Temp数据

C.在创建#Temp表的连接未断开时，DB1数据库的所用户可以查询#Temp表数据

D.在创建#Temp表的连接断开时，DB1数据库的所有用以查询#Temp表数据

答案：B

二、填空

1、设某数据库中有旅客表A（旅客编号，城市）和旅客表B（旅客编号，城市），现将所有旅客的数据存储这两张表中。请补全如下查询语句，使得该查询语句能查询所有旅客所在的全部的不重复的城市。

SELECT 城市 FROM 旅客表A

（ ）

SELECT 城市 FROM 旅客表B

答案：UNION