金典SQL笔记SQL语句汇总(三)
ty 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 ,FPr
ovince, 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, FYea