(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);