3.子查询
1、查询从来没有借过书的读者姓名。(分别使用 not in、not exists和连接查询三种写法实现,体会写法的不同之处)
--方式1: not in SELECT readerName FROM Reader WHERE readerNo not in (SELECT readerNo FROM Borrow ); --方式2: not exists SELECT readerName FROM Reader a WHERE NOT EXISTS(SELECT * FROM Borrow b WHERE a.readerNo = b.readerNo); --方式3: 连接查询 SELECT a.readerName FROM Reader a LEFT JOIN Borrow b ON a.readerNo = b.readerNo where b.readerNo IS null;
2、查询目前没有在借“经济类”图书的读者编号、读者姓名和出生日期。(分别使用 not in、not exists和派生表查询三种写法实现,体会写法的不同之处)
--方式1: not in SELECT readerNo,readerName,SUBSTRING(identifycard, 7, 8) AS birthday FROM Reader WHERE readerNo NOT IN( SELECT readerNo FROM Borrow WHERE bookNo in ( SELECT bookNo FROM Book WHERE classNo =( SELECT classNo FROM BookClass WHERE className = '经济类') ) GROUP BY readerNo ) --方式2: not exists SELECT r.readerNo, r.readerName, SUBSTRING(r.identifycard, 7, 8) AS birthday FROM Reader r WHERE NOT EXISTS ( SELECT 1 FROM Borrow b INNER JOIN Book b2 ON b.bookNo = b2.bookNo INNER JOIN bookClass bc ON b2.classNo = bc.classNo WHERE b.readerNo = r.ReaderNo AND bc.className = '经济类' ); --方式3: 派生表 SELECT r.ReaderNo, r.ReaderName, SUBSTRING(r.identifycard, 7, 8) AS birthday FROM Reader r WHERE r.readerNo NOT IN ( SELECT readerNo FROM ( SELECT b.readerNo, b2.classNo FROM Borrow b INNER JOIN Book b2 ON b.bookNo = b2.bookNo ) AS borrow_class INNER JOIN BookClass bc ON borrow_class.classNo = bc.classNo WHERE bc.className = '经济类' ); --方法4:多表查询 SELECT a.readerNo,a.readerName,SUBSTRING(a.identifycard, 7, 8) AS birthday FROM Reader a,Borrow b,Book c WHERE a.readerNo = b.readerNo AND b.bookNo = c.bookNo AND c.classNo not in ( SELECT b.classNo FROM Book a LEFT JOIN BookClass b ON a.classNo = b.classNo WHERE b.className = '经济类') GROUP BY a.readerNo,a.readerName,a.identifycard;
3、借阅过政治经济学图书有借阅过数据库系统概念图书的读者编号和读者姓名。
政治经济学','数据库系统概念')
1.查询借阅过政治经济学
图书的读者编号和读者姓名
2.查询借阅过数据库系统概念
图书的读者编号和读者姓名
SELECT b.readerNo,b.readerName FROM Borrow a LEFT JOIN Reader b ON a.readerNo = b.readerNo WHERE a.readerNo IN( SELECT readerNo FROM book a LEFT JOIN Borrow b ON a.bookNo = b.bookNo WHERE a.bookName = '政治经济学' OR a.bookName = '数据库系统概念') GROUP BY b.readerNo,b.readerName;
4、查询既借过“政治经济学”图书又借过“数据库系统概论”图书的读者编号、读者姓名以及这两种图书的名称、借书日期和归还日期。(分别使用in和union两种方法实现,体会写法的不同)
第一种方法,使用使用in
#第一种方法,使用使用in SELECT a.readerNo,a.readerName,b.bookName,c.borrowDate,c.returnDate FROM Reader a,Book b,Borrow c WHERE a.readerNo = c.readerNo AND b.bookNo = c.bookNo AND b.bookName IN('政治经济学','数据库系统概念') AND a.readerNo IN( SELECT a.readerNo FROM Borrow a,Book b WHERE a.bookNo = b.bookNo AND b.bookName = '政治经济学' AND a.readerNo IN( SELECT a.readerNo FROM Borrow a,Book b WHERE a.bookNo = b.bookNo AND b.bookName = '数据库系统概念') ) #第二种方法,使用使用union SELECT a.readerNo,a.readerName,b.bookName,c.borrowDate,c.returnDate FROM Reader a,Book b,Borrow c WHERE a.readerNo = c.readerNo AND b.bookNo = c.bookNo AND b.bookName = '政治经济学' AND a.readerNo IN ( SELECT a.readerNo FROM Borrow a,Book b WHERE a.bookNo = b.bookNo AND b.bookName = '数据库系统概念') UNION SELECT a.readerNo,a.readerName,b.bookName,c.borrowDate,c.returnDate FROM Reader a,Book b,Borrow c WHERE a.readerNo = c.readerNo AND b.bookNo = c.bookNo AND b.bookName = '数据库系统概念' AND a.readerNo IN ( SELECT a.readerNo FROM Borrow a,Book b WHERE a.bookNo = b.bookNo AND b.bookName = '政治经济学')
5、查询至少借阅过读者张小娟所借阅过的所有图书的读者编号、读者姓名和工作单位。
SELECT b.readerNo,b.readerName,b.workUnit FROM Borrow a ,Reader b WHERE bookNo IN ( SELECT bookNo FROM Borrow WHERE readerNo =( SELECT readerNo FROM Reader WHERE readerName = '张小娟') ) GROUP BY b.readerNo,b.readerName,b.workUnit;
6、查询至少有3本在借图书的读者编号、读者姓名以及在借图书的图书编号、图书名称,按读者编号升序、借阅日期降序排序输出。
SELECT a.readerNo,a.readerName,c.bookNo,c.bookName FROM Reader a, Borrow b ,Book c WHERE a.readerNo = b.readerNo AND b.returnDate IS NULL GROUP BY a.readerNo,a.readerName,c.bookNo,c.bookName HAVING count(b.bookNo)>2 ORDER BY a.readerNo
未解决按照借阅日期降序
7、查找价格高于数据库系统概念的图书清单。(使用连接查询和子查询两种写法,体会写法的不同之处)
#第一种方法,使用连接查询 SELECT a.bookNo,a.classNo,a.bookName,a.authorName,a.price,a.publishingDate,a.shopDate,a.shopNum FROM Book AS a INNER JOIN Book AS b ON b.bookName = '数据库系统概念' WHERE b.price < a.price #第二种方法,使用子查询 SELECT * FROM Book WHERE price > (SELECT price FROM Book where bookName = '数据库系统概念')
8、查询从来没有接过书的单位名称。(分别使用 not in、not exists和连接查询三种写法实现,体会写法的不同之处)
#第一种方法,使用 not in SELECT a.workUnit FROM Reader a,Borrow b WHERE a.readerNo not in (b.readerNo) GROUP BY a.workUnit; #第二种方法,使用 not exists SELECT workUnit FROM Reader WHERE NOT EXISTS( SELECT readerNo FROM Borrow WHERE Reader.readerNo = Borrow.readerNo); #第三种方法,使用 连接查询 SELECT workUnit FROM Reader a LEFT OUTER JOIN Borrow b ON a.readerNo = b.readerNo GROUP BY a.workUnit;
9、查询借的最多的图书的图书编号和图书名称。
SELECT b.bookNo,b.bookName FROM Book b WHERE b.bookNo = ( SELECT bookNo FROM Borrow GROUP BY bookNo HAVING COUNT(bookNo)=( SELECT TOP 1 count(bookNo) FROM Borrow GROUP BY bookNo ORDER BY count(bookNo) DESC) )
10、再图书表中计算每本书的价格与平均价格之差,结果显示图书编号、图书名称和平均差(价格-avg(价格)),并按照平均差降序排列。
SELECT bookNo,bookName,price-(SELECT top 1 (SELECT SUM(price) FROM Book)/(SELECT count(price) FROM Book ) as subs FROM Book ) AS avgSub FROM Book GROUP BY bookNo,bookName,price ORDER BY avgSub ASC
11、根据图书表查找每类图书的价格最高的图书,显示分类号、图书名称和价格,并按照分类号升序。(分别使用exists、not exists和派生表查询三种写法实现,体会写法的不同之处)
#1. 使用exists查询 SELECT a.classNo,a.bookName,a.price FROM Book a WHERE EXISTS ( SELECT 1 FROM Book b WHERE a.classNo = b.classNo GROUP BY b.classNo HAVING MAX(b.price) = a.price ) GROUP BY classNo,bookName,price ORDER BY a.classNo; #2. 使用not exists查询 SELECT a.classNo,a.bookName,a.price FROM Book a WHERE NOT EXISTS ( SELECT 1 FROM Book b WHERE a.classNo = b.classNo AND b.price > a.price ) GROUP BY classNo,bookName,price ORDER BY a.classNo ASC; #3. 使用派生表查询 SELECT classNo,bookName,price FROM Book where price IN( SELECT MAX(price) as maxPrice FROM Book GROUP BY classNo) ORDER BY classNo ASC;
12、查询所有借的图书的图书编号、图书名称、借阅次数以及根据借阅次数的排名(借阅次数越大,排名就靠前,类似根据成绩排名),并按照借阅次数降序排列。
使用两种方法:方法1:使用DENSE_RANK()over(order by )或者RANK()over(order by)(延申掌握ROW_NUMBER(),NTILE(数字)over(order by ),row_number,
方法2:使用借阅次数的比较法,提示:先求出图书编号、图书名称、借阅次数,再根据借阅次数做比较,如果想得到的排名为1、2、2、4就不添加去重,如果得到的排名为:1、2、2、3则需要添加distinct。
13、查询借阅量排名第三的图书名称和图书编号。
SELECT a.bookName,a.bookNo FROM Book a INNER JOIN ( SELECT TOP 3 bookNo,COUNT(*) AS borrow_count FROM Borrow GROUP BY bookNo ORDER BY borrow_count DESC ) AS b ON a.bookNo = b.bookNo;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 我与微信审核的“相爱相杀”看个人小程序副业
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~