图书馆 SQL查询系统 提升训练

create database Library_data


use Library_data
create table books(
bookId char(10) primary key,
bookSytle char(10),
bookName varchar(50),
bookWriter varchar(30),
bookPublish varchar(30),
bookPrice float
)
create table reader(
readerId char(10) primary key,
readerDeparment varchar(30) not null,
readerName varchar(8) not null,
readerSex char(2) not null constraint CK_sex check(readerSex in ('男','女')),
readerZC varchar(30),
readerAddress varchar(30)
)
create table borrow(
readerId char(10),
bookId char(10),
checkOutTime char(8),
checkInTime char(8),
primary key(readerId,bookId),
foreign key(readerId)references reader(readerId),
foreign key(bookId)references books(bookId)
)


use Library_data
Insert Into books values ('445501','TP3/12','数据库导论','王强','科学出版社',17.90 ) 
Insert Into books values ('445502','TP3/12','数据库导论','王强','科学出版社',17.90 )
Insert Into books values ('445503','TP3/12','数据库导论','王强','科学出版社',17.90)
Insert Into books values ('332211','TP5/10','计算机基础','李伟','高等教育出版社',18.00 )
Insert Into books values ('112266','TP3/12','FoxBASE','张三','电子工业出版社',23.60 )
Insert Into books values ('665544','TS7/21','高等数学','刘明','高等教育出版社',20.00 )
Insert Into books values ('114455','TR9/12','线性代数','孙业','北京大学出版社',20.80 )
Insert Into books values ('113388','TR7/90','大学英语','胡玲','清华大学出版社',12.50 )
Insert Into books values ('446601','TP4/13','数据库基础','马凌云','人民邮电出版社',22.50 )
Insert Into books values ('446602','TP4/13','数据库基础','马凌云','人民邮电出版社', 22.50)
Insert Into books values ('446603','TP4/13','数据库基础','马凌云','人民邮电出版社',22.50 )
Insert Into books values ('449901','TP4/14','FoxPro大全','周虹','科学出版社',32.70 )
Insert Into books values ('449902','TP4/14','FoxPro大全','周虹','科学出版社',32.70 )
Insert Into books values ('118801','TP4/15','计算机网络','黄力钧','高等教育出版社',21.80 )
Insert Into books values ('118802','TP4/15','计算机网络','黄力钧','高等教育出版社',21.80 )


Insert Into reader values('111','信息系','王维利','女','教授','1号楼424')
Insert Into reader values('112','信息系','李立','男','副教授','2号楼316')
Insert Into reader values('113','经济系','张三','男','讲师','3号楼105')
Insert Into reader values('114','信息系','周华发','男','讲师','1号楼316')
Insert Into reader values('115','信息系','赵正义','男','工程师','1号楼224')
Insert Into reader values('116','计算机系','李明','男','副教授','1号楼318')
Insert Into reader values('117','计算机系','李小峰','男','副教授','1号楼214')
Insert Into reader values('118','计算机系','许鹏飞','男','助工','1号楼216')
Insert Into reader values('119','国际贸易','刘大龙','男','讲师','1号楼318')
Insert Into reader values('120','国际贸易','李雪','女','讲师','4号楼506')
Insert Into reader values('121','国际贸易','李爽','女','讲师','4号楼510')
Insert Into reader values('122','财会系','王纯','女','助教','4号楼512')
Insert Into reader values('123','财会系','沈小霞','男','副教授','2号楼202')
Insert Into reader values('124','财会系','朱海','男','副教授','2号楼210')
Insert Into reader values('125','信息系','马英明','男','助教','2号楼212')


use Library_data
delete from borrow
Insert into borrow values('112','445501','2017-3-19','2017-3-20')
Insert into borrow values('125','332211','2017-2-12','2017-3-1')
Insert into borrow values('111','445503','2017-8-21','2017-8-29')
Insert into borrow values('112','112266','2017-3-14','2017-3-19')
Insert into borrow values('114','665544','2017-10-2','2017-10-11')
Insert into borrow values('120','114455','2017-11-2','2017-11-21')
Insert into borrow values('120','118801','2017-10-18','2017-10-24')
Insert into borrow values('119','446603','2017-12-12','2017-12-26')
Insert into borrow values('112','449901','2017-10-23','2017-10-29')
Insert into borrow values('115','449902','2017-8-21','2017-8-30')
Insert into borrow values('118','118801','2017-9-10','2017-10-2')




use Library_data
1、找出姓李的读者姓名和所在单位
select readerName,readerDeparment from reader where readerName like '李%';
2、列出图书库中所有藏书的书名及出版单位
select bookName,bookPublish from books;y


3、查找高等教育出版社的所有图书及单价，结果按单价降序排序
select bookName,bookPrice from books where bookPublish='高等教育出版社'
4、查找价格介于10到20元之间的图书种类，结果按出版单位和单价升序排列
select bookSytle,bookPublish,bookPrice from books where bookPrice between 10 and 20 order by bookPublish,bookPrice
5、查找书名以计算机打头的所有图书和作者
select bookName,bookWriter from books where bookName like '计算机%'
6、检索同时借阅了总编号为112266和449901两本书的借书证号
select jy1.readerId from borrow jy1,borrow jy2 where (jy1.bookId = '112266')and (jy2.bookId = '449901') and(jy1.readerId = jy2.readerId);
7、查找所有借了书的读者的姓名及所在单位
select distinct readerName,readerDeparment from reader,
borrow where borrow.readerId = reader.readerId
8、找出李某所借图书的所有图书的书名及借书日期
select bookName,readerName,checkouttime from reader,books,borrow where readerName like '李%' and reader.readerId = borrow.readerId and books.bookId = borrow.bookId
select * from borrow


9、查询2017年10月以后借书的读者借书证号、姓名和单位
select distinct reader.readerId,readerName,readerDeparment from reader,borrow where reader.readerId = borrow.readerId and borrow.checkouttime>='2017-10-1'
10、找出借阅了FoxPro大全一书的借书证号
select distinct borrow.readerId from borrow,books where borrow.bookId = books.bookId and bookName like 'FoxPro大全'
11、找出与赵正义在同一天借书的读者姓名、所在单位及借书日期
select reader.readerName,reader.readerdeparment,borrow.checkouttime from borrow,reader 
where borrow.readerId = reader.readerId and checkouttime in(
select checkouttime from reader,borrow where borrow.readerId = reader.readerId and readerName like '赵正义')
12、查询2017年7月以后没有借书的读者借书证号、姓名及单位
select readerId,readerName,readerdeparment from reader where readerId not in (
select readerId from borrow where checkouttime>'2017-07-01')




/*创建图书管理数据库并完成下面SQL高级查询
1）求科学出版社图书的最高单价、最低单价、平均单价
2）求信息系当前借阅图书的读者人数
3）求出各个出版社图书的最高价格、最低价格和册数
4）找出藏书中各个出版单位的册数、价值总额
*/
select max(bookPrice) as 最高价,min(bookPrice) as 最低价,avg(bookPrice) as 平均价 
from books where bookPublish like '科学出版社'


select '信息系当前借阅图书的读者人数：',count(distinct readerId) from borrow where readerId in
(select readerId from reader where readerdeparment like '信息系')


select bookPublish, Max(bookPrice),min(bookPrice),avg(bookPrice) from books group by bookPublish;


select bookPublish,'册数：',count(*),'价值总额：',sum(bookPrice) from books
group by bookPublish order by sum(bookPrice),count(*) desc