【项目实训】图书馆借还书系统的数据查询

8 篇文章 0 订阅
7 篇文章 1 订阅

(1)查询“人民邮电出版社”的相关图书信息,结果包含图书编号、书名、作者、出版社和定价。
mysql> select bid as 图书编号,bname as 书名,author as 作者, publisher as 出版社, price as 价格
    -> from book
    -> where publisher='人民邮电出版社';

(2)查询女性读者的相关信息,结果包含读者姓名、性别和所属院系。
mysql> select rname as 姓名,sex as 性别, dept as 所属院系
    -> from reader
    -> where sex = '女';

(3)查询 2017 年至 2019 年出版的相关图书信息,结果包含图书编号、书名、作者、出版日期和定价。
mysql> select bid as 图书编号,bname as 书名,author as 作者,publishdate as 出版日期,price as 定价
    -> from book
    -> where publishdate between "2017-01-01" and "2019-12-31";

(4)查询图书定价打7折后的图书编号、书名和打折后价格。
mysql> select bid as 书名, bname as 书名, price*0.7 as 打折后价格
    -> from book;

(5)查询所有馆存图书的总类别数量和总库存数量。
mysql> select count(bid) as 总类别数量, sum(total) as 总库存数量
    -> from book;

(6)查询借阅的书名称中包含“数据”的读者的借阅信息,结果包含读者姓名和书名。
mysql> select rname as 读者姓名, bname as 书名
    -> from borrow join reader on reader.rid= borrow.rid
    -> join book on borrow.bid = book.bid
    -> where bname like "%数据%";

(7)查询读者为“教师”的借阅信息,结果包含读者姓名、书名和借阅日期。
mysql> select rname as 读者姓名,bname as 书名,borrowtime as 借阅日期
    -> from borrow join reader on reader.rid= borrow.rid
    -> join book on borrow.bid = book.bid
    -> where typeno in(select typeno from readertype where typename="教师");

(8)查询尚未还书的相关读者信息,结果包含读者编号、读者姓名、书名和借阅日期。
mysql> select borrow.rid as 读者编号,rname as 读者姓名,bname as 书名, borrowtime as 借阅日期
    -> from borrow join reader on reader.rid= borrow.rid
    -> join book on borrow.bid = book.bid
    -> where returntime is null;

(9)查询每本图书的借阅次数,将结果按照借阅次数降序排列,结果包含图书编号、书名、借阅次数、作者和出版社。
mysql> select borrow.bid as 图书编号,bname as 书名,count(borrowtime) as 借阅次数,
    -> author as 作者,publisher as 出版社
    -> from borrow,book where borrow.bid=book.bid
    -> group by borrow.bid
    -> order by count(borrowtime) desc;

(10)查询每个院系的借阅次数,将结果按照借阅次数的降序排序,结果包含院系名称和借阅次数。
mysql> select reader.dept as 院系名称, count(borrowtime) as 借阅次数
    -> from borrow,reader where borrow.rid=reader.rid
    -> group by reader.dept
    -> order by count(borrowtime) desc;

(11)查询借阅了书名为“大数据技术基础”的图书但尚未还书的相关读者信息,结果包含读者编号、读者姓名、书名和借阅日期。
mysql> select a.rid as 读者编号,a.rname as 读者姓名, b.bname as 书名,
    -> c.borrowtime as 借阅日期
    -> from borrow c join reader a on c.rid=a.rid
    -> join book b on b.bid=c.bid
    -> where returntime is null and c.bid=
    -> (select d.bid from book d where bname="大数据技术基础");

(12)查询定价高于平均定价的相关图书信息,结果包含图书编号、书名、作者、出版社和定价。
mysql> select bid as 图书编号, bname as 书名, author as 作者,
    -> publisher as 出版社, price as 定价
    -> from book where price >
    -> (select avg(price)  from book);

(13)查询从未被读者借阅的相关图书信息,结果包含图书编号、书名、作者、出版社和定价。
mysql> select bid as 图书编号, bname as 书名, author as 作者,
    -> publisher as 出版社, price as 定价
    -> from book where bid not in
    -> (select bid from borrow);

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

婧婧子♔♔♔

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值