SQL进阶基础（面试基础）— Part 2

前言

本文原题及实验数据均出自： http://www.cnblogs.com/edisonchou/p/3886801.html，但本文是基于SQL Server的训练模型，经过笔者的简单改动，现用于训练SQL/HQL。本文只针对DML语句进行训练，原文中还有很多DDL训练，如有兴趣，可自行训练。

本题用到下面三个关系表：

CARDS 借书卡。 ID卡号，NAME 姓名，CLASS 班级

BOOKS 图书。 ID书号，NAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数

BORROW 借书记录。 PK没卵用的列，CARDID 借书卡号，BOOKID 书号，RDATE 还书日期

备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

---

测试表构建及插入测试数据

本文中涉及的所有SQL均在MYSQL中进行测试，将建表语句以及插入数据语句导入MYSQL本地库中即可

下载_插入创建表和插入语句

---

题目、答案及讲解

（1）找出借书超过2本的读者（包含2本）,输出借书卡号及所借图书册数

SELECT
cardId ,COUNT(bookId)
FROM
borrow
GROUP BY cardId
HAVING COUNT(bookId)>=2


##解题思路
按卡号分组，统计每个人借了多少书
过滤掉2本以下的人

（2） 查询借阅了"水浒"一书的读者，输出姓名及班级

SELECT crd.`name`,crd.class
FROM
(SELECT cardId 
FROM
borrow
WHERE
bookId=
(SELECT
id
FROM
books 
WHERE `name`='水浒'))crdid,cards crd
WHERE crdid.cardId=crd.id


##解题思路
首先找到水浒的书号。从borrow表过滤出借了水浒的卡号。
两表关联，获取学生姓名，班级

（3）查询目前为止未还图书，输出借阅者（卡号）、书号及还书日期

SELECT brw.cardId,brw.bookId,brw.returnDate
FROM
borrow brw
WHERE brw.returnDate<CURDATE()

##解题思路
本题就是用还书日期和系统当前日期进行对比。
还书日期小于系统当前日期，目的是输出截止到现在还未还书的。
掌握MYSQL CURDATE（）获得当前时间函数

 （4）查询书名包括"网络"关键词的图书，输出书号、书名、作者

SELECT id,`name`,author
FROM
books
WHERE
`name` LIKE '%网络%'


##解题思路
模糊查询，网络前后都要有通配符

（5）查询现有图书中价格最高的图书，输出书名及作者

SELECT `name`,author
FROM
books
WHERE
price=
(SELECT MAX(price) FROM books)


##解题思路
从books表中把最高价格查出来。
过滤掉不等于这个价钱的即可

（6）查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出

SELECT cardId
FROM
borrow
WHERE bookId= 
(SELECT id
FROM
books
WHERE `name` = '计算方法')
and cardId NOT IN
(SELECT cardId
FROM
borrow
WHERE
bookId =
(SELECT id
FROM
books
WHERE `name` ='计算方法习题集'))
ORDER BY cardId DESC


##解题思路
首先要查出《计算方法习题集》和《计算方法》对应的bookid，
然后通过书号可以分别查出卡号，
最后过滤掉把买了《计算方法习题集》的卡号即可

（7）将"计科一班"班同学所借图书的还期都延长一周

UPDATE
borrow
SET returnDate=DATE_ADD(returnDate,INTERVAL 7 DAY)
WHERE cardId IN
(SELECT id
FROM
cards 
WHERE class='计科一班')


##解题思路
UPDATE语句格式：UPDATE 表名 SET 列 = 值 WHERE 列=条件
本文中考点如何将日期增加一周，要用到DATE_ADD()函数
首先查出计科一班卡号，将此值设为条件，按格式套用即可

（8）从BOOKS表中删除当前无人借阅的图书记录

DELETE FROM books 
WHERE id NOT IN
(SELECT DISTINCT bookId
FROM
borrow)


##解题思路
DEL语句格式：DELETE FROM 表 WHERE 列 = '条件'

从borrow找出借过的书，套用格式过滤掉即可

（9）查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出

SELECT cardId FROM borrow WHERE bookId=
(SELECT id
FROM
books
WHERE `name`='计算方法')
AND cardId IN
(SELECT cardId FROM borrow WHERE bookId=
(SELECT id
FROM
books
WHERE `name`='组合数学'))


##解题思路

首先分别查看两本书对应的Bookid，然后查出其对应cardid，
读了计算方法的CID,也在读了组合数学的CID中的号则证明， 两本都读了

---

---

作者：唐辰（数据界小白）