2022-08-17 学习笔记 day40-sql语言-sql查询练习题(2)

练习需要用到的表以及数据

直接复制即可
注意：此处创建了test数据库，以及相关表，请检查该库下是否有表重复，防止原表被覆盖)

CREATE DATABASE IF NOT EXISTS `test`;
USE `test`;


DROP TABLE IF EXISTS `card`;
CREATE TABLE `card`  (
  `cid` varchar(6)  NOT NULL,
  `copendate` date NULL DEFAULT NULL,
  `cprivilige` char(1)  NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB;


INSERT INTO `card` VALUES ('c1', '2020-01-01', 'A');
INSERT INTO `card` VALUES ('c2', '2020-02-02', 'B');
INSERT INTO `card` VALUES ('c3', '2020-03-03', 'C');



DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(6)  DEFAULT NULL,
  `sage` int(2) NULL DEFAULT NULL,
  `sdepartment` varchar(20)  NULL DEFAULT NULL,
  `senterdate` date NULL DEFAULT NULL,
  `cid` varchar(6)  NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `fk_student_cid`(`cid`) USING BTREE,
  CONSTRAINT `fk_student_cid` FOREIGN KEY (`cid`) REFERENCES `card` (`cid`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE = InnoDB;


INSERT INTO `student` VALUES (1, '张三', 20, '机械设计', '2019-06-06', 'c1');
INSERT INTO `student` VALUES (2, '李四', 19, '机械设计', '2019-09-08', 'c2');
INSERT INTO `student` VALUES (3, '王五', 21, '软件学院', '2019-10-10', 'c3');

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `bid` varchar(6)  NOT NULL,
  `bname` varchar(30) NULL DEFAULT NULL,
  `bauthor` varchar(50)NULL DEFAULT NULL,
  `bpublishdate` date NULL DEFAULT NULL,
  PRIMARY KEY (`bid`) USING BTREE
) ENGINE = InnoDB;


INSERT INTO `book` VALUES ('b1', '盗墓笔记', '唐家三少', '2010-02-02');
INSERT INTO `book` VALUES ('b2', '西游记', '吴承恩', '2016-06-06');
INSERT INTO `book` VALUES ('b3', '红楼梦', '曹雪芹', '2015-05-05');
INSERT INTO `book` VALUES ('b4', '水浒传', '施耐庵', '2014-04-04');
INSERT INTO `book` VALUES ('b5', '三国演义', '罗贯中', '2012-02-02');


DROP TABLE IF EXISTS `borrowrecored`;
CREATE TABLE `borrowrecored`  (
  `cid` varchar(6)  NOT NULL,
  `bid` varchar(6)  NOT NULL,
  `bdate` date NULL DEFAULT NULL,
 PRIMARY KEY (`cid`, `bid`) USING BTREE,
 INDEX `fk_borrowrecored_bid`(`bid`) USING BTREE,
FOREIGN KEY (`bid`) REFERENCES `book` (`bid`)
   ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`cid`) REFERENCES `card` (`cid`)
   ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;


INSERT INTO `borrowrecored` VALUES ('c1', 'b1', '2019-11-03');
INSERT INTO `borrowrecored` VALUES ('c1', 'b3', '2019-12-15');
INSERT INTO `borrowrecored` VALUES ('c2', 'b1', '2019-12-16');
INSERT INTO `borrowrecored` VALUES ('c2', 'b2', '2020-02-02');
INSERT INTO `borrowrecored` VALUES ('c2', 'b3', '2020-03-06');
INSERT INTO `borrowrecored` VALUES ('c2', 'b4', '2020-01-05');
INSERT INTO `borrowrecored` VALUES ('c3', 'b1', '2019-12-18');
INSERT INTO `borrowrecored` VALUES ('c3', 'b2', '2020-03-08');

练习题

	
-- 对于下面的查询可以封装成函数，之后会单独写一篇相关文章来封装函数


-- 1根据学生学号 查询 2020年-1-1之后借过哪些书籍
-- 假设学号为1，日期为2020-1-1
SELECT 
	student.sid,book.bname,borrow.bdate
FROM 
	student student 
	JOIN card card ON student.cid = card.cid
	JOIN borrowrecored borrow ON borrow.cid = card.cid
	JOIN book book ON book.bid = borrow.bid
WHERE
	sid = 1 AND borrow.bdate > '2020-1-1';

-- 根据学生编号,查阅该生借阅过哪些书籍
SELECT 
	student.sid,book.bname,borrow.bdate
FROM 
	student student 
	JOIN card card ON student.cid = card.cid
	JOIN borrowrecored borrow ON borrow.cid = card.cid
	JOIN book book ON book.bid = borrow.bid
WHERE
	sid = 1;


-- 2根据书籍编号 查询2020-1-1之前都有那哪些学生借阅过
-- 假设书籍编号为1
SELECT 
	student.sname,book.bid,book.bname,borrow.bdate
FROM 
	student student 
	JOIN card card ON student.cid = card.cid
	JOIN borrowrecored borrow ON borrow.cid = card.cid
	JOIN book book ON book.bid = borrow.bid

WHERE 
	book.bid = 1 AND borrow.bdate < '2020-1-1';

-- 3根据学生编号查询该生一共借阅过多少本书
-- 假设学生编号为1
SELECT 
	COUNT(borrow.bid)
FROM 
	student student 
	JOIN card card ON student.cid = card.cid
	JOIN borrowrecored borrow ON borrow.cid = card.cid
	JOIN book book ON book.bid = borrow.bid
WHERE 
	student.sid = 1;

	
	
-- 4根据书籍编号查询书籍一共被借阅过多少次(查阅次数是指拥有该书籍的学生个数？)
-- 假设书籍编号为b1
SELECT 
	COUNT(student.sid)
FROM 
	student student 
	JOIN card card ON student.cid = card.cid
	JOIN borrowrecored borrow ON borrow.cid = card.cid
	JOIN book book ON book.bid = borrow.bid
WHERE 
	book.bid = 'b1';


-- 5根据学院名称查询该学院的学生一共借过哪些书籍
-- 假设学院的名称是机械设计
SELECT 
	student.sdepartment,student.sname,group_concat(book.`bname`) 
FROM 
	student student 
	JOIN card card ON student.cid = card.cid
	JOIN borrowrecored borrow ON borrow.cid = card.cid
	JOIN book book ON book.bid = borrow.bid
WHERE 
	student.sdepartment  = '机械设计'
GROUP BY 
	student.sdepartment,student.sname;

-- 6查询哪个学生最爱看书(此处只找第一个，想要全部找出请参考第七题)
SELECT 
	* 
FROM 
	student
WHERE 
	student.sid = (
		SELECT 
			student.sid
		FROM 
			student student 
			JOIN card card ON student.cid = card.cid
			JOIN borrowrecored borrow ON borrow.cid = card.cid
			JOIN book book ON book.bid = borrow.bid

		GROUP BY 
			student.sid
		ORDER BY 
			COUNT(borrow.bid) DESC 
		LIMIT 1
	);
	

-- 7查询那本书最受欢迎(可能会有多本受欢迎程度相同的书)
SELECT 
	*
FROM 
	book
WHERE 
	book.bid IN (
		SELECT 
			t2.bid
		FROM 

				(
					SELECT 
						COUNT(student.sid) cstudent
					FROM 
						student student 
						JOIN card card ON student.cid = card.cid
						JOIN borrowrecored borrow ON borrow.cid = card.cid
						JOIN book book ON book.bid = borrow.bid
					GROUP BY 
						book.bid
					ORDER BY 
						cstudent DESC
					LIMIT 1	

				) t1 
				

			JOIN
				(
					SELECT 
						book.bid,COUNT(student.sid) cstudent
					FROM 
						student student 
						JOIN card card ON student.cid = card.cid
						JOIN borrowrecored borrow ON borrow.cid = card.cid
						JOIN book book ON book.bid = borrow.bid
					GROUP BY 
						book.bid
					ORDER BY 
						cstudent DESC
				) t2
			ON 
				t1.cstudent = t2.cstudent
	
	);
	


-- 每本书被借阅了多少次

SELECT 
	book.bid,book.bname,COUNT(student.sid)
FROM 
	student student 
	JOIN card card ON student.cid = card.cid
	JOIN borrowrecored borrow ON borrow.cid = card.cid
	JOIN book book ON book.bid = borrow.bid

GROUP BY 
	book.bid,book.bname