SQL Server 2008数据库开发经典案例教程-电子教案 习题解答等-吕玉桂 电子教案 第8章子查询

1
本章目标
⏹掌握子查询的使用
•子查询返回单一值
•子查询返回值列表
⏹•并集(Union)
•交集(Intersection)
•减(Except)
2
子查询
⏹
问题：显示价格最高书籍的书号、书名和价格⏹解决方法：•查询出最高价格
⏹select MAX(price) from BookInfo
•以价格为条件查询出书号、书名和价格⏹
select bookid,bookname,price from BookInfo (select MAX(price) from BookInfo)
外部查询内部查询
3
子查询
⏹
子查询就是一个嵌套在SELECT 、INSERT 、UPDATE 或DELETE 语句或其⏹语句也称为外部查询⏹子查询的SELECT 查询总是使用圆括号括起来。

基本语法：SELECT 列名……..
FROM 表名WHERE 条件=(SELECT 列名
FROM 表名WHERE 条件）
4
返回单个值的子查询
⏹
当子查询返回单个值时，外部查询条件和子查询之间使用比较运算符（>、>=、<、<=、=、！=）⏹子查询返回单一值的方法：•聚合函数
•DISTINCT
1，先求书籍的平均价格
【例8-1】显示价格超过平均价格的图书的书号、书名、作者和价格。

SELECT Bookid,bookName,Price FROM BookInfo WHERE Price >(SELECT AVG(price) FROM BookInfo)比较运算符
5返回单个值的子查询
【例8-2】查询具有和“JavaWeb 开发技术详解”相同出版社和相同出版年限的书籍的书号、书名和出版日期
解决方法：
•查询书名为“JavaWeb 开发技术详解”的出版社ID 和⏹
SELECT publisherID FROM BookInfo WHERE ⏹SELECT YEAR(publishDate) FROM BookInfo WHERE •代码为：⏹FROM BookInfo
6
返回值列表的子查询
子查询返回值列表即子查询返回的不是单个值，外部查询的检索条件WHERE 和子查
7
返回值列表的子查询
【例8-3】查询有销售记录的人员信息
•先求子查询，即在销售主表SalesMaster 中查⏹•因为销售人员不止一个，所以外部查询和子查询之间使用IN ⏹WHERE UserID IN (SELECT UserID FROM SalesMaster)
8返回值列表的子查询
⏹
有书籍的书号、书名、作者和价格。

⏹分析：
•先求出类别是“JAVA “的所有书籍的价格列表⏹SELECT Price,BookID,CategoryID FROM (SELECT categoryid FROM Category WHERE CategoryName ='JAVA')
⏹
SELECT BookID ,BookName ,Author,Price
FROM BookInfo WHERE Price >ALL(SELECT 值列表
9
9EXISTS 存在性测试
相关子查询•在子查询中引用外部查询的列，即子查询的执行与外部查询有关【例8-4】显示同类图书中价格最高的的书籍信息，显示内容包括书号、书名、价格和类别。

分析：对BookInfo 表的每一条记录，与同类图书的价格比较，求出最高价格的书籍
SELECT BookID ,BookName ,Price ,CategoryID
FROM BookInfo a
WHERE a.Price =(
SELECT max(b.price)
FROM BookInfo b
WHERE a.CategoryID =b.CategoryID )
10
EXISTS 存在性测试
EXISTS 存在性测试•指定一个子查询，测试行是否存在，也就是对于外部查询的每一行，检查子查询是否是空集
合，如不是空集合，主查询返回值，否则主查询没有返回值该题目涉及销售细表SalesDetails 和书籍表BookInfo,因为查询的是书籍信息，所以BookInfo 表应该放在外部查询位置，销售细表SalesDetails 应放在子查询位置【例8-5】查询已销售的书籍信息，显示内容包括书号、书名和作者。

SELECT BookID,BookName,Author FROM BookInfo WHERE EXISTS
(SELECT * FROM SalesDetails WHERE BookID=BookInfo.BookID )
11
NOT Exists 子查询
【例8-6】查询没有销售记录的用户信息，显示内容包括用户ID 、用户名和入职月份。

•分析：
该题目外部查询是用户UserInfo 表，子查询是销售主表SalesMaster,由于查询的是没有销售记录的用户，所以使用NOT EXISTS 关键字判断子查询是否有记录返回用户id 用户id
SELECT erId,erName,YEAR(a.hiredate) '入职年份'FROM UserInfo a WHERE NOT EXISTS (
SELECT * FROM SalesMaster b WHERE erID=erID )
12
集合操作
⏹
集合操作是一种以两个或多个SELECT 查询结果集为操作对象，通过对这些查询结⏹集合操作的运算符主要包括以下3种•并集(UNION)
•交集(INTERSECT)
•差(EXCEPT)
13并集（UNION ）
⏹并集
•并集是将两个或更多查询的结果合并为单个结⏹UNION 的使用限制：
•集合操作符所涉及的查询应有相同的列数，对应的列必须具有相同的数据类型。

句。

⏹UNION 和UNION ALL
1414
并集（UNION ）
【例8-7】把“机械工业出版社”出版的图书和“人民邮电出版社”出版社的图书一起显示输出●查询“机械工业出版社”的图书的T-SQL 如下
●查询“人民邮电出版社”的图书的T-SQL SELECT BookID ,BookName,Author,PublisherID FROM BookInfo WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName ='机械工业出版社')SELECT BookID ,BookName,Author,PublisherID FROM BookInfo
WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName ='人民邮电出版社')SELECT BookID ,BookName,Author,PublisherID FROM BookInfo WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName ='机械工业出版社')UNION SELECT BookID ,BookName,Author,PublisherID FROM BookInfo WHERE PublishERID=(SELECT PublisherID FROM Publisher WHERE PublisherName ='人民邮电出版社')
15
并集（UNION ）【例8-8】将新数据显示在查询中SELECT '009' AS 用户ID,'张三' as 用户名, '男' as 性别, 20 as
年龄UNION
SELECT UserID ,UserName,Sex,Age FROM UserInfo
16
交集(INTERSECT) 交集(INTERSECT)
•INTERSECT 运算符比较两个查询的结果，返都包含的记录
•INTERSECT 运算符的使用限制与UNION 运算符相同。

SELECT BookID as 书ID FROM SalesDetails INTERSECT SELECT BookID FROM BookInfo
17
差(EXCEPT)
差(EXCEPT)
•运算符比较两个查询的结果，返回非重复值。

•EXCEPT 运算符获取在结果集A 中但不在结果•EXCEPT 运算符的使用限制与UNION 运算符相同
18
差(EXCEPT)
【例8-10】查询BookInfo 表中价格低于40的书籍中去掉2006年出版的书籍信息，显示内容包括书号、书名、价格和出版日期SELECT BookID 书ID ,BookName 书名,Price 价格,PublishDate 出版日期FROM BookInfo WHERE Price <40
EXCEPT
SELECT BookID ,BookName ,Price ,PublishDate FROM BookInfo
WHERE YEAR(PublishDate) ='2006'
19总结
⏹和DELETE 中的查询。

⏹一般子查询不依赖于外部查询，执行时先执行内部查询再执行外部查询，通常用比较运算符和IN ，⏹相关子查询即子查询的条件依赖于外部查询，执行时先执行外部查询，然后根据外部查询返回的记录行数重复键字。

⏹集合运算。