金典 SQL笔记(8)

------------------------文本方便拷贝------------------------

page269-301子查询


--子查询
--SQL允许将一个查询语句作为一个结果集供其他 SQL语句使用, 就像使用
--普通的表一样,被当做结果集的查询语句被称为子查询
--所有可以使用表的地方都可以使用子查询 ,比如select * from T
--上面的T 就可以用子查询来代替 select * from (select * from T2 where
--age >= 30) 这里(select * from T2 where age >= 30) 就是子查询
--可以将子查询看做为一张临时表 ,这张表在查询开始的时候被创建 ,在查询结束
--的时候被销毁子查询大大简化了复杂的 SQL 语句编程

--建表及测试数据
--T_Reader 读者信息FYearOfBirth 读者出身年份 FProvince读者省份
--FYearOfJoin 读者入会年份
CREATE TABLE T_Reader(Fid INT NOT NULL,FName VARCHAR(50 ),
FYearOfBirth INT, FCity VARCHAR( 50),FProvince VARCHAR(50 ),
FYearOfJoin INT)

--书籍信息FYearPublished 初版年份FCategoryId所属分类
CREATE TABLE T_Book(Fid INT NOT NULL,FName VARCHAR(50 ),
FYearPublished INT, FCategoryId INT)

--分类信息
CREATE TABLE T_Category(FId INT NOT NULL,FName VARCHAR(50 ))

--T_ReaderFavorite 读者和类别的对应关系 FReaderId读者主键
--FCategoryId分类主键
CREATE TABLE T_ReaderFavorite(FCategoryId INT,FReaderId INT)

--测试数据
INSERT INTO T_Category(FId ,FName) VALUES(1 ,'Story') --故事
INSERT INTO T_Category(FId ,FName) VALUES(2 ,'History') --历史
INSERT INTO T_Category(FId ,FName) VALUES(3 ,'Theory') --理论
INSERT INTO T_Category(FId ,FName) VALUES(4 ,'Technology') --技术
INSERT INTO T_Category(FId ,FName) VALUES(5 ,'Art') --艺术
INSERT INTO T_Category(FId ,FName) VALUES(6 ,'Philosophy') --哲学
                                                     --
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(1 ,'Tom', 1979,'TangShan' ,'Hebei', 2003)
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(2 ,'Sam', 1981,'LangFang' ,'Hebei', 2001)    
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(3 ,'Jerry', 1966,'DongGuan' ,'DongGuan', 1995)
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(4 ,'Lily', 1972,'JiaXing' ,'ZheJiang', 2005)       
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(5 ,'Marry', 1985,'BeiJing' ,'BeiJing', 1999)    
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(6 ,'Kelly', 1977,'ZhuZhou' ,'HuNan', 1995)    
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(7 ,'Tim', 1982,'YangZhou' ,'HuNan', 2001)    
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(8 ,'King', 1979,'JiNan' ,'ShanDong', 1997)    
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(9 ,'John', 1979,'QingDao' ,'ShanDong', 2003)                                                    
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(10 ,'Lucy', 1978,'LuoYany' ,'HeNan', 1996)   
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(11 ,'July', 1983,'ZhuMaDian' ,'HeNan', 1999)   
INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)
VALUES(12 ,'Fige', 1981,'JinCheng' ,'ShanXi', 2003)   

INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(1 ,'About J2EE', 2005,4 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(2 ,'Learning Hibernate', 2003,4 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(3 ,'Two Cites', 1999,1 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(4 ,'Jane Eyre', 2001,1 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(5 ,'Oliver Twist', 2002,1 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(6 ,'HisTory of china', 1982,2 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(7 ,'HisTory of England', 1860,2 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(8 ,'HisTory of America', 1700,2 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(9 ,'HisTory of the World', 2008,2 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(10 ,'Atom', 1930,3 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(11 ,'RELATIVITY', 1945,3 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(12 ,'Computer', 1970,3 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(13 ,'Astronomy', 1971,3 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(14 ,'How To Singing', 1771,5 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(15 ,'DaoDeJing', 2001,6 )
INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )
VALUES(16 ,'ObediencetoAuthority', 1995,6 )

INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(1 ,1)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(5 ,2)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(2 ,3)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(3 ,4)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(5 ,5)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(1 ,6)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(1 ,7)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(4 ,8)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(6 ,9)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(5 ,10)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(2 ,11)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(2 ,12)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(1 ,12)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(3 ,1)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(1 ,3)
INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)
VALUES(4 ,4)

--子查询有两种类型,一种是只返回一个单值的子查询 ,这时它可以
--用在单值可以使用的地方 ,可以把它看成一个拥有返回值的函数
--另外一种是返回一列值的子查询 ,这时的子查询可以看成在内存
--中的临时表

--单值子查询
--单值子查询的限制,返回值必须只有一行记录 ,而且只能有一个列,
--这样的子查询又被称为标量子查询标量子查询可以在 select语句
--表达式中, 以及where语句中等很多场合
--范例select 语句列表中使用 ,可以把它看成函数就易懂了
SELECT 1 AS f1,2 ,(SELECT MIN(FYearPublished ) FROM T_Book),
(SELECT MAX(FYearPublished ) FROM T_BOOK ) AS f4

--范例列值子查询
SELECT T_Reader. FName,t2 .FYearPublished, t2.FName
FROM T_Reader,( SELECT * FROM T_Book WHERE FYearPublished < 1800 ) t2

--单值子查询扩展进阶
--这个子查询,可以 SELECT MAX(FYearPublished) FROM T_BOOK 选中直接执行
select FId, FName,(SELECT MAX( FYearPublished) FROM T_BOOK )
FROM T_Category
--升级下
--这个子查询无法选中单独执行 ,上个子查询没有依赖外部查询字段 ,所以可以
--单独选中执行
--从而能查询到一类图书最新出版的年份
select FId, FName,
(SELECT MAX(FYearPublished ) FROM T_BOOK
 WHERE T_BOOK .FCategoryId = T_Category.FId )
FROM T_Category

--where 子句中的标量查询
--标量子查询不仅可以在select语句的列表中 ,还可以用在where子句中 ,而且
--实际应用的子查询很多时候都是用在 where子句中的
select FReaderId from dbo.T_ReaderFavorite
WHERE FCategoryId =
(SELECT FId from T_Category WHERE FName = 'Story')

--首先建立内连接
--可以查看俩表的交集
--然后根据年份条件筛选利用子查询查找最早的初版年份
select c. FId,b .FName, b.FYearPublished
FROM T_Book AS b INNER JOIN T_Category AS c
ON b. FCategoryId = c .FId
WHERE b. FYearPublished =
(SELECT MIN(FYearPublished ) FROM T_Book WHERE T_Book .FCategoryId = c.FId)

--集合运算符in,any,all,exists 与子查询的结合
--如果子查询是多行多列,这样的子查询可以作为临时表
--如果子查询是多行当列,这样的子查询其实是一个结果集
--SQL 对这样的结果集提供了in,any,all exeists 等操作符

--需求: 检索所有图书出版年份内入会的读者信息
SELECT * FROM T_Reader
WHERE FYearOfJoin IN
(select FYearPublished FROM T_BOOK)


--SQL 中any 和some使用和功能都是相同的和 in运算符不同
--any必须和其他比较运算符共同使用 ,而且比较将比较运算符放
--在any 关键字符前 ,所比较的值也需求匹配子查询中的任意值
SELECT * FROM T_Reader
WHERE FYearOfJoin =any
(select FYearPublished FROM T_BOOK)
--这个查询结果和上面in的查询结果一样的
--也就是说'=any' 等价于In 运算符
--而'<>any' 等价于not in 运算符
--但是像'<any' 就没法等价了还有其他更详细的需要看
--TSQL 技术解密可以更深入

--查找任何一个会员出生前初版的图书
SELECT * FROM T_Reader
WHERE FYearOfJoin <any
(select FYearPublished FROM T_BOOK)


--ALL运算符
--ALL 运算符要求比较的值需要匹配子查询中的所有值
--ALL 运算符同样不能单独使用 ,必须和比较运算符共同使用
--下面的SQL 语句用来检索在所有会员入会之前出版的图书 :
SELECT * FROM T_Book
WHERE FYearPublished <ALL
(SELECT FYearOfJoin FROM T_Reader)



--上面那个不是等同于可以用 min函数取最小值
SELECT * FROM T_Book
WHERE FYearPublished <( SELECT min (FYearOfJoin) FROM T_Reader )
--ALL运算符同样不能与固定的集合匹配 ,比如下面的SQL是错误的
SELECT * FROM T_Book WHERE FYearPublished <ALL( 2001,2002 ,2003)
--不过这个限制并不会妨碍功能的实现 ,因为没有必要对固定的集合进行
--ALL匹配由于带匹配的集合固定完全可以由其他方式实现诸如
SELECT * FROM T_Book WHERE FYearPublished < 2001

--当使用ALL 运算符的时候 ,如果待匹配的集合为空,也就是子查询没有
--返回任何数据的时候,不论与什么比较符搭配使用 ALL返回的值永远是
--true. 如下这个查询语句FProvince='没有省份 ' 查询出来是没有匹配
--数据的, 但是结果上面的思维 ,会把T_Book 所有数据查询出来 ;
--按照正常的思维返回的结果应该是空才对 ALL运算符的语义就是这样
--使用的时候格外注意
SELECT * FROM T_Book
WHERE FYearPublished <ALL
(SELECT FYearOfJoin FROM T_Reader
 WHERE FProvince ='没有省份 ')

 --exists 运算符
 --和 in ,any,all 运算符不同exists运算符是单目运算符 ,它不与列匹配
 --因此它也不要求待匹配的集合是单列的 exists运算符用来检查每一行
 --是否匹配子查询 ,可以认为exists 就是用来测试子查询的结果是否为空的
 --,如果结果集为空则匹配结果为 false,否则匹配结果为true
 --EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不
 --返回任何数据，而是返回值 True或False
 --EXISTS 指定一个子查询，检测行的存在。
 
 --下面三个结果都相同
 SELECT * FROM T_BOOK
WHERE EXISTS
(SELECT FName FROM T_Reader
 WHERE FProvince = 'ShanXi' )
 
SELECT * FROM T_BOOK
WHERE EXISTS
(SELECT Null)

 SELECT * FROM  T_BOOK


--从前面几个例子来看,使用 exists运算符要么是匹配返回表中的所有
--数据, 要么就是不匹配不返回任何数据 ,好像exists 运算符并没有太
--大意义其实上面的例子在实际中并不实用 ,exists要和相关子查询
--一起使用才有意义在相关子查询中引用外部查询中的这个字段 ,这样
--匹配外部子查询中的每行数据的时候 ,相关子查询就会根据当前行的
--信息来进行匹配判断,这样就可以实现非常丰富的功能呢
 
--测试下和in 的区别
SELECT * FROM T_BOOK
WHERE FCategoryId IN
(SELECT Fid from T_Category WHERE FName = 'History')

SELECT * FROM T_BOOK
WHERE EXISTS
(SELECT Fid from T_Category WHERE FName = 'History'
AND Fid = T_BOOK.FCategoryId )



--其他类型SQL 语句中的子查询
--子查询在insert 中的应用
--将查询出来的结果批量插入
--语法字段需要一一对应
--insert into T_ReaderFavorite2(FCategoryId,FReaderId)
--SELECT FCategoryId,FReaderId FROM T_ReaderFavorite2
--还有其它附加条件where 计算等都可以只需要对应字段类型

--子查询在update 语句中的应用
--范例
UPDATE T_Book
SET FYearPublished =
(SELECT MAX(FYearPublished ) FROM T_Book)

--范例
--所有同类书超过本的图书初版日期改为
--UPDATE T_Book b1
--SET FYearPublished = 2005
--WHERE (SELECT COUNT(*) FROM T_Book2 b2
--       WHERE b1.FCategoryId=b2.FCategoryId)>3

--子查询在delete 语句中的应用
--删除同类书超过本
delete T_Book b1
WHERE ( SELECT COUNT (*) FROM T_Book2 b2
       WHERE b1. FCategoryId=b2 .FCategoryId)> 3