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