/*
数据库实验三
2017/04/06
*/
USE [BookDB]
GO
/*数据库的创建*/CREATEDATABASE [BookDB]
ONPRIMARY
( NAME = N'BookDB',
FILENAME = N'D:\SQLDatabase\BookDB.mdf' ,
SIZE = 5120KB ,
MAXSIZE = 10240KB ,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'BookDB_log',
FILENAME = N'D:\SQLDatabase\BookDB_log.ldf' ,
SIZE = 1024KB ,
MAXSIZE = 5120KB ,
FILEGROWTH = 1024KB )
GO
/*BookClass表的创建*/
CREATETABLE BookClass(
classNo char(3) NOTNULLprimarykey,--分类号
className char(20) NOTNULL,--分类名称
);
GO
/*Book图书表的创建*/CREATETABLE Book(
bookNo char(10) NOTNULLprimarykey,--图书编号
classNo char(3) NOTNULL,--分类号
bookName char(40) NOTNULL,--图书名称
authorName char(8) NOTNULL,--作者名称
publishingName char(20) NotNULL,--出版社名称
publishingNo char(17) NotNULL,--出版号
price numeric(7,2) NOTNULL,--单价
publishingDate datetime NOTNULL,--出版时间
shopDate datetime NOTNULL,--入库时间
shopNum numeric(3) NOTNULL,--入库数量
Foreignkey (classNo) references BookClass(classNo),
);
GO
/*Reader 读者表的创建*/CREATETABLE Reader(
readerNo char(8) NOTNULLprimarykey,--读者编号
readerName char(8) NOTNULL,--姓名
sex char(2) NOTNULL,--性别
identitycard char(18) NotNull,--身份证号
workUnit char(50) NOTNULL,--工作单位
borrowCount tinyint NOTNULL,--借书数量
);/*Borrow 借阅表的创建*/CREATETABLE Borrow(
readerNO char(8) NOTNULL,--读者编号
bookNO char(10) NOTNULL,--图书编号
borrowDate datetime NOTNULL,--借阅日期
shouldDate datetime NOTNULL,--应归还日期
returnDate datetime ,--归还日期
primarykey(readerNo,bookNo,borrowDate),
Foreignkey(readerNo) references Reader(readerNo),
Foreignkey(bookNO) references Book(bookNo),
);/*数据插入*//*图书分类表 BookClass 插入数据*/insert BookClass values('001','经济类')
insert BookClass values('002','外语类')
insert BookClass values('003','计算机类')
/*图书表 Book 插入数据*/
insert Book values('B200101001','001','政治经济学','宋涛','中国人民大学出版社','P1991010100010001',31.80,'19910101','19950111',100)
insert Book values('B200201001','002','大学英语','郑树棠','外语教学与研究出版社','P1992010100010001',35.20,'19920101','19960111',130)
insert Book values('B200301001','003','数据库系统原理','吴京慧','清华大学出版社','P1993010100010001',58.20,'19930101','19950111',160)
insert Book values('B200101002','001','微观经济学','张蕊','高等教育出版社','P1991010100010002',41.80,'19910102','19950112',110)
insert Book values('B200101003','001','宏观经济学','袁明圣','中国财经经济出版社','P1991010100010003',51.80,'19910103','19950113',120)
insert Book values('B200201002','002','商务英语','马升烨','上海外语学院出版社','P1992010100010002',45.20,'19920102','19960112',140)
insert Book values('B200201003','002','商务英语2','江宇佳','西安交通大学出版社','P1992010100010003',55.20,'19920103','19960113',150)
insert Book values('B200301002','003','组网技术','万征','浙江大学出版社','P1993010100010002',38.20,'19930102','19950112',170)
insert Book values('B200301003','003','人工智能','费翔林','电子工业出版社','P1993010100010003',43.20,'19930103','19950113',180)
insert Book values('B200301004','003','算法设计与分析','陈慧南','科学出版社','P1993010100010004',58.20,'19930104','19950114',190)
/*读者表 Reader 插入数据*/
insert Reader values('R2005001','张小娟','F','412723199001014321','统一股份有限公司',0)
insert Reader values('R2006001','刘凤','F','412723199003014321','联合股份有限公司',0)
insert Reader values('R2007001','高代鹏','M','412723199005014321','洪都股份有限公司',0)
insert Reader values('R2008001','陈辉','M','412723199111014321','南昌市电脑研制公司',0)
insert Reader values('R2009001','李虹冰','F','412723199208014321','富士康科技集团',0)
insert Reader values('R2005002','张露','F','412723199002014321','兴隆股份有限公司',0)
insert Reader values('R2006002','喻自强','M','412723199004014321','万事达股份有限公司',0)
insert Reader values('R2007002','张晓梅','F','412723199112014321','世界技术开发公司',0)
insert Reader values('R2008002','张良','M','412723199110014321','上海生物研究室',0)
insert Reader values('R2009002','韩福平','M','412723199209014321','合生元有限公司',0)
/*借阅表 Borrow 插入数据*/
insert Borrow values('R2005001','B200101001','20110901','20111001',null)
insert Borrow values('R2006001','B200101001','20110907','20111007','20111005')
insert Borrow values('R2007001','B200201001','20110913','20111013','20111010')
insert Borrow values('R2008001','B200301001','20110915','20111015','20111013')
insert Borrow values('R2009001','B200301001','20110918','20111018',null)
insert Borrow values('R2005001','B200101002','20110902','20111002','20111001')
insert Borrow values('R2005001','B200101003','20110903','20111003',null)
insert Borrow values('R2005002','B200101001','20110904','20111004','20111003')
insert Borrow values('R2005002','B200101002','20110905','20111005',null)
insert Borrow values('R2005002','B200101003','20110906','20111006','20111008')
insert Borrow values('R2006001','B200101002','20110908','20111008','20111006')
insert Borrow values('R2006001','B200101003','20110909','20111009','20111012')
insert Borrow values('R2009002','B200201001','20110910','20111010','20111008')
insert Borrow values('R2006002','B200201002','20110911','20111011','20111008')
insert Borrow values('R2006002','B200201003','20110912','20111012','20111008')
insert Borrow values('R2007001','B200201002','20110914','20111014',null)
insert Borrow values('R2007001','B200201003','20110915','20111015','20111010')
insert Borrow values('R2009002','B200201001','20110916','20111016','20111014')
insert Borrow values('R2007002','B200201002','20110917','20111017','20111014')
insert Borrow values('R2007002','B200201003','20110917','20111017','20111014')
/* 1-5 为简单查询 */
--1. 查询1991年出生的读者姓名、工作单位和身份证号;
SELECT readerName,workUnit,identitycard
FROM Reader
Whereyear(substring(identitycard,7,4))=1991
--2. 查询在富士康科技集团工作的读者编号、姓名和性别;
SELECT readerNo,readerName,sex
FROM Reader
WHERE workUnit='富士康科技集团'
--3. 查询图书名中含有"数据库"的图书的详细信息;SELECT *
FROM Book
WHERE bookName like'%数据库%'
--4. 查询吴文君老师编写的单价不低于40元的每种图书的图书编号、入库数量;
SELECT bookNo,shopNum
FROM Book
WHERE price>=40AND authorName='吴文君'
--5. 查询在1995-1996年之间入库的图书编号、出版时间、入库时间和图书名称,并按入库时间排序输出;
SELECT bookNo,publishingDate,shopDate,bookName
FROM Book
WHEREyear(shopDate)=1995ORyear(shopDate)=1996ORDERBY shopDate ASC
/* 6-10 为连接查询 */
--6. 查询借阅了图书编号为'B200101002'图书的读者姓名、借书日期、还书日期;
SELECT readerName,borrowDate,returnDate
FROM Borrow,Reader
WHERE Borrow.readerNO=Reader.readerNo AND bookNo='B200101002'
--7. 查询读者张晓梅借阅的图书编号、图书名称、借书日期和归还日期;
SELECT c.bookNO,a.bookName,c.borrowDate,c.returnDate
FROM Book a,Reader b,Borrow c
WHERE a.bookNo=c.bookNO AND b.readerNo=c.readerNO AND b.readerName='张晓梅'
--8. 查询'洪都股份有限公司'没有归还所借图书的读者编号、读者姓名、图书名称、借书日期和应归还日期;
SELECT b.readerNO,a.readerName,c.bookName,b.borrowDate,b.shouldDate
FROM Reader a,Borrow b,Book c
WHERE a.readerNo=b.readerNO AND b.bookNO=c.bookNo AND a.workUnit='洪都股份有限公司'AND b.returnDate ISNULL
--9. 查询借阅了清华大学出版社出版的图书的读者编号、读者姓名、图书名称、借书日期和归还日期;
SELECT b.readerNo,b.readerName,a.bookNo,a.bookName,c.borrowDate,c.returnDate
FROM Book a,Reader b,Borrow c
WHERE a.bookNo=c.bookNO AND b.readerNo=c.readerNO AND a.publishingName='清华大学出版社'
--10. 查询借书时间在2010-2011年之间的读者编号、读者姓名、图书编号、图书名称;
SELECT b.readerNo,b.readerName,a.bookNo,a.bookName
FROM Book a,Reader b,Borrow c
WHERE a.bookNo=c.bookNO AND b.readerNo=c.readerNO AND (year(c.borrowDate)=2010ORyear(c.borrowDate)=2011)
/* 11-12题,使用聚合函数,以及排序;*/
--11. 查询每种类别的图书分类号、最高价格和平均价格,并按最高价格的降序输出;
SELECT classNo,max(price) 最高价格,avg(price) 平均价格
FROM Book
GROUPBY classNo
ORDERBYmax(price) DESC
--12. 查询图书分类号小于009号图书的入库数量;
SELECTsum(shopNum) 入库数量
FROM Book
WHERE classNo<'009'
/* 13-17题,使用连接,子查询还有聚合函数;*/
--13. 查询所借图书的总价在100元以上的读者编号、读者姓名和所借图书的总价; (要求很短,看似简单,其实比较复杂);
SELECT b.readerNo,b.readerName,sum(a.price) 总价
FROM Book a,Reader b,Borrow c
WHERE a.bookNo=c.bookNO AND b.readerNo=c.readerNO
GROUPBY b.readerNo,b.readerName
HAVINGSUM(a.price)>=100
--14. 查询没有借书的读者姓名和工作单位(分别使用IN子查询和存在量词子查询表达);
SELECT readerName,workUnit
FROM Reader
WHERE readerNo NOTIN (SELECT readerNo FROM BORROW)
SELECT readerName,workUnit
FROM Reader
WHERENOTEXISTS(SELECT *
FROM Borrow
WHERE Reader.readerNo=Borrow.readerNo)
--15. 查询借阅了图书编号为'B200101002'图书的读者编号、姓名以及他们所借阅尚未归还的所有图书的图书名称、借书日期(分别使用IN子查询和存在量词子查询表达)
SELECT b.readerNo,b.readerName,a.bookName,c.borrowDate
FROM Book a,Reader b,Borrow c
WHERE a.bookNo=c.bookNO AND b.readerNo=c.readerNO AND b.readerNo IN (SELECT readerNO FROM Borrow WHERE bookNo='B200101002') AND c.returnDate ISNULLSELECT c.readerNO,c.readerName,a.bookName,b.borrowDate
FROM Book a,Borrow b,Reader c
WHERE a.bookNO=b.bookNO and c.readerNO=b.readerNO AndEXISTS( SELECT readerNO
FROM Borrow
WHERE bookNO='B200101002'And c.readerNO=Borrow.readerNO) And b.returnDate isnull
--16. 查询没有借阅图书编号以'B2001'开头的图书的读者编号、姓名以及他们所借阅图书的图书名称、借书日期(分别使用IN子查询和存在量词子查询表达)。
SELECT b.readerNo,b.readerName,a.bookName,c.borrowDate
FROM Book a,Reader b,Borrow c
WHERE a.bookNo=c.bookNO AND b.readerNo=c.readerNO AND(b.readerNo NOTIN (SELECT readerNO FROM Borrow WHERE bookNo like'B2001%'))
SELECT c.readerNO,readerName,bookName,borrowDate
FROM Book a,Borrow b,Reader c
WHERE a.bookNO=b.bookNO and c.readerNO=b.readerNO AndNOTEXISTS( SELECT Borrow.readerNO
FROM Borrow
WHERE c.readerNO=Borrow.readerNO And bookNO LIKE'B2001%')
--17. 查询在2010-2011年之间借阅但没有归还图书的读者编号、读者姓名、读者工作单位以及他们所借阅过的所有图书的图书编号、图书名称和借书日期(分别使用IN子查询和存在量词子查询表达);
SELECT b.readerNo,b.readerName,b.workUnit,a.bookNo,a.bookName,c.borrowDate
FROM Book a,Reader b,Borrow c
WHERE a.bookNo=c.bookNO AND b.readerNo=c.readerNO AND b.readerNo IN (SELECT readerNO FROM Borrow WHERE (year(borrowDate)=2010ORyear(borrowDate)=2011) AND returnDate ISNULL)
SELECT c.readerNO,readerName,workUnit,a.bookNO,bookName,borrowDate
FROM Book a,Borrow b,Reader c
WHERE a.bookNO=b.bookNO and c.readerNO=b.readerNO AndEXISTS( SELECT Borrow.readerNO
FROM Borrow
WHERE c.readerNO=Borrow.readerNO Andyear(borrowDate)between 2010And2011And returnDate isnull)
/* 18-22题,使用集合运算;*/(稍后做)
--18. 查询既借阅了"商务英语"图书又借阅了"大学英语"两本图书的读者编号、读者姓名、借书日期和图书名称;
SELECT b.readerNo,b.readerName,c.borrowDate,a.bookName
FROM Book a,Reader b,Borrow c
WHERE a.bookNo=c.bookNO AND b.readerNo=c.readerNO AND b.readerNo IN ( SELECT c.readerNo FROM Book a,Borrow c WHERE a.bookNo=c.bookNO AND a.bookName='商务英语'INTERSECTSELECT c.readerNo FROM Book a,Borrow c WHERE a.bookNo=c.bookNO AND a.bookName='大学英语')
--19. 查询没有借阅"经济类"图书的读者编号、读者姓名和出生日期(分别使用IN子查询和存在量词子查询表达);
SELECT readerNo,readerName,substring(identitycard,7,8) 出生日期
FROM Reader
WHERE readerNo IN ( SELECT readerNo
FROM borrow
WHERE bookNO IN ( SELECT bookNo
FROM BOOK
WHERE classNo NOTIN ( SELECT classNO
FROM BookClass
WHERE className='经济类')))
SELECT a.readerNO,readerName,SUBSTRING(identitycard,7,8) birthday
FROM Reader a
WHERENOTEXISTS( SELECT *
FROM Borrow b,Book c,BookClass d
WHERE b.bookNO=c.bookNO and c.classNO=d.classNO and a.readerNO=b.readerNO and d.className='经济类')
--20. 查询至少与读者"张晓梅"所借的图书一样的读者编号、读者姓名和工作单位;
SELECTDISTINCT a.readerNo,a.readerName,a.workUnit --DISTINCT 消除重复数据
FROM Reader a,book b,Borrow c
WHERE a.readerNo=c.readerNO AND b.bookNo=c.bookNO AND B.bookNo IN (SELECT bookNO FROM BORROW WHERE readerNo IN (SELECT readerNo FROM Reader WHERE readerName='张晓梅'))
--21. 查询借阅了图书类别为002号的所有图书的读者编号、读者姓名以及他们所借阅过的所有图书的图书名称和借阅日期;
SELECT B.readerNo,B.readerName,A.bookName,C.borrowDate
FROM BOOK A,READER B,BORROW C
WHERE A.bookNo=C.bookNO AND B.readerNo=C.readerNO AND B.readerNo IN ( SELECT a.readerNo
FROM BORROW a,( SELECT bookNo
FROM BOOK
WHERE classNo='002') b
WHERE a.bookNO=b.bookNo
GROUPBY readerNo
HAVINGcount(a.readerNo)=( SELECTcount(bookNo)
FROM BOOK
WHERE classNo='002'))
--22. 查询借阅了图书类别为002号的所有图书的读者编号、读者姓名以及他们所借阅的这些(002号)图书的图书名称和借阅日期;
SELECT B.readerNo,B.readerName,A.bookName,C.borrowDate
FROM BOOK A,READER B,BORROW C
WHERE A.bookNo=C.bookNO AND B.readerNo=C.readerNO AND B.readerNo IN ( SELECT a.readerNo
FROM BORROW a,( SELECT bookNo
FROM BOOK
WHERE classNo='002') b
WHERE a.bookNO=b.bookNo
GROUPBY readerNo
HAVINGcount(a.readerNo)=( SELECTcount(bookNo)
FROM BOOK
WHERE classNo='002')) AND a.classNo='002'
/* 23--26题,是分组和组过滤,以及排序输出;*/
--23. 查询至少借阅了3本图书的读者编号、读者姓名、图书编号、图书名称,按读者编号排序输出;
SELECT b.readerNo,b.readerName,a.bookNo,a.bookName
FROM Book a,Reader b,Borrow c
WHERE a.bookNo=c.bookNO AND b.readerNo=c.readerNO AND b.readerNo IN (SELECT readerNo FROM borrow GROUPBY readerNo HAVINGcount(bookNo)>=3)
ORDERBY b.readerNo ASC
--24. 查询所借阅的图书总价最高的读者编号、读者姓名和出生日期;
SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday
FROM Reader
WHERE readerNo IN( SELECT readerNo
FROM Borrow,Book
WHERE Borrow.bookNo=Book.bookNo AND returnDate ISNULLGROUPBY readerNo
HAVINGSUM(price) >=ALL(SELECTSUM(price)
FROM Borrow,Book
WHERE Borrow.bookNo=Book.bookNo AND returnDate ISNULLGROUPBY readerNo) )
--25. 将"经济类"图书的单价提高10%;(稍后做)
UPDATE Book
SET price=price/1.1WHERE classNo IN (SELECT classNo FROM BookClass WHERE className='经济类')
SELECT bookName,price --查询结果
FROM Book
WHERE classNo IN (SELECT classNo FROM BookClass WHERE className='经济类')
--26. 对于年龄在25-35岁之间的读者所借阅的应归还还未归还的图书,将其归还日期修改为系统当天日期; (稍后做)
UPDATE Borrow
SET returnDate=getdate()
WHERE returnDate isnullAND readerNO IN (SELECT readerNo FROM Reader WHERE (year(getdate())-year(substring(identitycard,7,8))>=25ANDyear(getdate())-year(substring(identitycard,7,8))<=35))
SELECT * --查询结果
FROM Borrow,Reader
WHERE borrow.readerNO=reader.readerNo AND (year(getdate())-year(substring(identitycard,7,8))>=25ANDyear(getdate())-year(substring(identitycard,7,8))<=35)