2022-08-17 第三小组 陈迪 学习笔记

-- 1根据学生学号 查询 2020年-1-1之后借过哪些书籍
SELECT
s.sid,s.sname,bk.bname,b.bdate
FROM borrowrecored b
LEFT JOIN student s ON s.cid = b.cid
LEFT JOIN book bk ON bk.bid = b.bid
WHERE b.bdate > '2020-01-01';
-- 2根据书籍编号 查询2020-1-1之前都有那哪些学生借阅过
SELECT
bk.bid,s.sname,bk.bname,b.bdate
FROM borrowrecored b
LEFT JOIN student s ON s.cid = b.cid
LEFT JOIN book bk ON bk.bid = b.bid
WHERE b.bdate < '2020-01-01'
ORDER BY bk.bid ;
-- 3根据学生编号查询该生一共借阅过多少本书
SELECT
s.cid,s.sname,count(b.bdate) sum
FROM borrowrecored b
LEFT JOIN student s ON s.cid = b.cid
LEFT JOIN book bk ON bk.bid = b.bid
GROUP BY s.cid
ORDER BY s.cid ;
-- 4根据书籍编号查询书籍一共被借阅过多少次
SELECT
bk.bid,bk.bname,count(b.bdate) sum
FROM borrowrecored b
LEFT JOIN student s ON s.cid = b.cid
LEFT JOIN book bk ON bk.bid = b.bid
GROUP BY bk.bid
ORDER BY bk.bid ;
-- 5根据学院名称查询该学院的学生一共借过哪些书籍
SELECT
s.sdepartment,bk.bid,bk.bname
FROM borrowrecored b
LEFT JOIN student s ON s.cid = b.cid
LEFT JOIN book bk ON bk.bid = b.bid
GROUP BY s.sdepartment,bk.bname
ORDER BY s.sdepartment,bk.bid ;
-- 6查询哪个学生最爱看书
SELECT n.cid,n.sname,MAX(sum)
FROM
(SELECT
s.cid,s.sname,count(b.bdate) sum
FROM borrowrecored b
LEFT JOIN student s ON s.cid = b.cid
LEFT JOIN book bk ON bk.bid = b.bid
GROUP BY s.cid) n;
-- 7查询那本书最受欢迎
SELECT n.bid,n.bname,MAX(sum)
FROM
(SELECT
bk.*,count(b.bdate) sum
FROM borrowrecored b
LEFT JOIN student s ON s.cid = b.cid
LEFT JOIN book bk ON bk.bid = b.bid
GROUP BY bk.bid) n;

心得体会

今天学习了子查询，发现了好多新的知识，今天老师让我们做了好多题，受益匪浅，希望以后可以继续努力。