练习需要用到的表以及数据
直接复制即可
注意:此处创建了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