Mysql练习题---2级

目录

首先建立表：

建card表 

建student表

建立book表：

建立borrowrecored表： 

插入数据： 

1.根据学生学号 查询 2020年-1-1之后借过哪些书籍

2.根据书籍编号 查询2020-1-1之前都有那哪些学生借阅过

 3.根据学生编号,查阅该生借阅过哪些书籍

4.根据学生编号查询该生一共借阅过多少本书

5.根据书籍编号查询书籍一共被借阅过多少次

6.根据学院名称查询该学院的学生一共借过哪些书籍

7.查询哪个学生最爱看书

8.每本书被借阅了多少次

9.查询那本书最受欢迎

---

首先建立表：

建card表 

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;

建student表

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;

建立book表：

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;

建立borrowrecored表： 

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 `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');

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');

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');

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之后借过哪些书籍

SELECT
b.bdate,
s.sname
FROM
student s
LEFT JOIN card c ON c.cid=s.cid
LEFT JOIN borrowrecored b ON b.cid=s.cid
LEFT JOIN book bo ON bo.bid=b.bid
WHERE
b.bdate>'2020-1-1';

2.根据书籍编号 查询2020-1-1之前都有那哪些学生借阅过

SELECT
b.bdate,
s.sname
FROM
student s
LEFT JOIN card c ON c.cid=s.cid
LEFT JOIN borrowrecored b ON b.cid=s.cid
LEFT JOIN book bo ON bo.bid=b.bid
WHERE
b.bdate<'2020-1-1';

 

 3.根据学生编号,查阅该生借阅过哪些书籍

SELECT
s.sname,
bo.bname
FROM
student s
LEFT JOIN card c ON c.cid=s.cid
LEFT JOIN borrowrecored b ON b.cid=s.cid
LEFT JOIN book bo ON bo.bid=b.bid
WHERE
s.sid=1;

4.根据学生编号查询该生一共借阅过多少本书

SELECT
s.sname,
COUNT(bo.bname)
FROM
student s
LEFT JOIN card c ON c.cid=s.cid
LEFT JOIN borrowrecored b ON b.cid=s.cid
LEFT JOIN book bo ON bo.bid=b.bid
WHERE
s.sid=1
GROUP BY
s.sname;

5.根据书籍编号查询书籍一共被借阅过多少次

SELECT
bo.bname,
bo.bid,
COUNT(s.sname)
FROM
student s
LEFT JOIN card c ON c.cid=s.cid
LEFT JOIN borrowrecored b ON b.cid=s.cid
LEFT JOIN book bo ON bo.bid=b.bid
WHERE
bo.bid='b1'
GROUP BY
bo.bname,
bo.bid;

6.根据学院名称查询该学院的学生一共借过哪些书籍

SELECT
bo.bname,
bo.bid,
COUNT(s.sname)
FROM
student s
LEFT JOIN card c ON c.cid=s.cid
LEFT JOIN borrowrecored b ON b.cid=s.cid
LEFT JOIN book bo ON bo.bid=b.bid
WHERE
s.sdepartment='机械设计'
GROUP BY
bo.bname,
bo.bid;

7.查询哪个学生最爱看书

SELECT
s.sname name,
s.sid id,
COUNT(bo.bid) ci
FROM
student s
LEFT JOIN card c ON c.cid=s.cid
LEFT JOIN borrowrecored b ON b.cid=s.cid
LEFT JOIN book bo ON bo.bid=b.bid
GROUP BY
s.sname,
s.sid
ORDER BY
COUNT(bo.bid) DESC
LIMIT 1;

8.每本书被借阅了多少次

SELECT
bo.bname name,
bo.bid id,
COUNT(s.sname) ci
FROM
student s
LEFT JOIN card c ON c.cid=s.cid
LEFT JOIN borrowrecored b ON b.cid=s.cid
LEFT JOIN book bo ON bo.bid=b.bid
GROUP BY
bo.bname,
bo.bid;

 

9.查询那本书最受欢迎

SELECT
bo.bname name,
bo.bid id,
COUNT(s.sname) ci
FROM
student s
LEFT JOIN card c ON c.cid=s.cid
LEFT JOIN borrowrecored b ON b.cid=s.cid
LEFT JOIN book bo ON bo.bid=b.bid
GROUP BY
bo.bname,
bo.bid
ORDER BY
COUNT(s.sname) DESC
LIMIT 1;