MySQL操作练习：library

查询前请创建对应的数据库和表：

create databases `library`;
use library;

create table book(
    bno char(20) not null primary key,
    bname varchar(50),
    author varchar(50),
    publish varchar(50),
    price float(255,0)
);

create table reader(
    rno char(10) not null primary key,
    rname char(8) not null,
    sex char(2) not null,
    tel char(8),
    department varchar(30),
    address varchar(30)
);

create table borrow(
    rno char(8) not null,
    bno char(20) not null,
    bdate char(10) not null,
    rdate char(10),
    constraint fk_r_no foreign key (rno) references reader(rno),
    constraint fk_b_no foreign key (bno) references book(bno)
);

insert into book values
('111100','科学出版社历史丛书','科学','科学出版社',108),
('111111','数据库编程','张作家','机械工业出版社',56),
('222222','数据库开发','西红柿','清华大学出版社',66),
('333333','猛兽岛大逃亡','能力书hi下','机械工业出版社',55),
('444444','SQL数据库案例','茶轩ing','机械工业出版社',12),
('555555','思维导论','awef','科学出版社',65),
('666666','算法设计','jiohg','清华大学出版社',22),
('777777','mysql数据库入门','fefef','机械工业出版社',96),
('888888','疯狂英语','awef','科学出版社',33),
('999999','世界地图','为世居大幅','机械工业出版社',88);

insert into reader values
('0001','张三','男','87818112','软件','尚品书院'),
('0002','里斯','男','87818283','网络','华软1号楼'),
('0003','王五','男','88320701','游戏','尚品书院'),
('0004','王小平','男','88320701','游戏','华软1号楼'),
('0005','高多多','男','87818998','会计','华软1号楼'),
('0006','瑞安','男','88320701','游戏','华软1号楼'),
('0007','斯琴','男','88320701','游戏','绿映楼'),
('0008','等等','男','88320701','游戏','蓝楹楼'),
('0009','热巴','男','87818282','工商','蓝楹楼'),
('0010','李思思','男','8789','软件','蓝楹楼'),
('0011','潍坊','男','8989741','软件','尚品书院');

insert into borrow values
('0001','111100','2022-11-31','2022-12-3'),
('0001','111111','2020-5-4','2020-6-20'),
('0001','333333','2019-7-13','2019-8-1'),
('0001','666666','2022-1-7','2022-2-3'),
('0003','666666','2021-7-31','2021-8-31'),
('0003','777777','2022-12-31','2021-2-22'),
('0004','888888','2022-6-3', null),
('0005','777777','2022-7-16', null),
('0008','999999','2022-7-31', null),
('0011','111111','2022-8-3', null),
('0011','222222','2022-8-9', null);

19、 查询读者的基本信息及其借阅情况（借阅情况指历史借阅数量，在借数量）；

 select reader.rno,rname,sex,tel,department,address,
 count(borrow.bdate) as 历史借阅, 
 count(borrow.bdate) - count(borrow.rdate) as 在借数量
 from reader
 left join borrow 
 on borrow.rno = reader.rno
 group by reader.rno;

20、 查询读者的读者号、姓名、借阅的图书名、借出日期及归还日期，

并按照读者号作升序排序；

select reader.rno,rname,bname,bdate,rdate
from reader
inner join borrow
on reader.rno = borrow.rno
inner join book
on borrow.bno = book.bno
order by rno;

21、 查询借阅了机械工业出版社，并显示读者号、姓名、书名、出版社、借出日期及归还日期

select reader.rno,rname,bname,publish,bdate,rdate
from reader
inner join borrow
on reader.rno = borrow.rno
inner join book
on borrow.bno = book.bno
where publish = '机械工业出版社';

22、 查询至少借阅过1本机械工业出版社的图书的读者的 读者号、姓名、借阅本数，并按借阅本数多少排序

select reader.rno,rname,bname,publish,count(*) as 借阅本数
from reader
inner join borrow
on reader.rno = borrow.rno
inner join book
on borrow.bno = book.bno
where publish = '机械工业出版社'
group by reader.rno
order by 借阅本数;

23、 查询与‘王小平’的办公电话相同的读者的姓名（王小平本人不再列出）；

select rname 
from reader 
where tel = (select tel from reader where rname = '王小平') 
and rname != '王小平';

24、 查询办公电话为‘88320701’的所有读者的借阅情况，要求包含借阅了图书的读者和没有借阅的读者，

显示他们的读者号、姓名、书名及借阅日期；

select reader.rno,rname,bname,bdate 
from reader
left join borrow
on reader.rno = borrow.rno
left join book
on borrow.bno = book.bno
where tel = '88320701';

25、 查询所有单价小于平均单价的图书的书号、书名及出版社；

select bno,bname,publish 
from book
where price < (select avg(price) from book);

26、 查询‘科学出版社’的图书的单价比‘机械工业出版社’最高单价还高的图书书名及单价；

方法一：

select bname,price 
from book
where publish = '科学出版社' 
and price > 
all(select price from book where publish = '机械工业出版社');

方法二：

select bname,price 
from book
where publish = '科学出版社' 
and price > 
(select max(price) from book where publish = '机械工业出版社');

27、 查询已经被借阅过并已经归还的图书信息；

select * 
from book
left join borrow
on borrow.bno = book.bno
where rdate is not null;

28、 查询从未被借阅过的图书信息；

select * 
from book
left join borrow
on borrow.bno = book.bno
where bdate is null;

29、 查询正在被借阅的图书信息；

select * 
from borrow
inner join book
on borrow.bno = book.bno
where rdate is null;

30、 查询软件系借了书还没有还的读者学号姓名。

方法一：

select reader.rno,rname 
from reader
inner join borrow
on borrow.rno = reader.rno
where rdate is null
group by rno;

方法二：

select reader.rno,rname 
from reader
left join borrow
on borrow.rno = reader.rno
where bdate is not null and rdate is null 
group by rno;

31、 查询借阅图书总数最多的宿舍楼

方法一：

select address
from reader
inner join borrow
on reader.rno = borrow.rno
group by address
order by count(*) desc limit 0,1 ;

方法二：
可以找出所有宿舍的借阅数量

select address,count(*) as num
from reader
inner join borrow
on reader.rno = borrow.rno
group by address;

可以找到最大借阅数的宿舍和借阅数，但不能仅显示地址
select address,max(num) from (...);

显示地址

select address from(
    select address,max(num) from (
        select address,count(*) as num
        from reader
        inner join borrow
        on reader.rno = borrow.rno
        group by address
    ) as temp
) as temp;