MySql练习题_1

建表语句

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50742
 Source Host           : localhost:3306
 Source Schema         : examination

 Target Server Type    : MySQL
 Target Server Version : 50742
 File Encoding         : 65001

 Date: 23/06/2023 15:23:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `bno` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `bname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `author` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `publish` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `price` decimal(10, 2) DEFAULT NULL,
  PRIMARY KEY (`bno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for borrow
-- ----------------------------
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow`  (
  `rno` int(11) NOT NULL,
  `bno` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `bdate` datetime(0) DEFAULT NULL,
  `rdate` datetime(0) DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for reader
-- ----------------------------
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader`  (
  `rno` int(11) NOT NULL AUTO_INCREMENT,
  `rname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `sex` int(255) DEFAULT NULL,
  `tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `department` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`rno`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

操作题

-- --3、查询机械工业出版社、科学出版社、人民邮电出版社的图书信息
SELECT * FROM `book` where publish in ('机械工业出版社','科学出版社','人民邮电出版社');
-- --4、查询有归还日期的借阅信息
select * from borrow where rdate is not null;
-- --5、查询单价在20元以上、30元以下的机械工业出版社出版的图书名及单价；
select * from book where price >20 and price <30
-- 6、查询机械工业出版社或科学出版社出版的图书名、出版社及单价；
select bname,publish,price from book where publish in ('机械工业出版社','科学出版社')
-- 7、查找姓名以王、张、或李开头的所有读者的读者号及姓名；
select * from reader where rname like '王%' or rname like '张%' or rname like '李%'
-- 8、查询读者的读者号、姓名、借阅的图书名、借出日期及归还日期，并按照读者号作升序排序；
select a.rno,a.rname,c.bname,b.bdate,b.rdate from reader a
left join borrow b on a.rno=b.rno
left join book c on b.bno=c.bno
-- 9、查询借阅图书数超过2本的读者号、总本书，并按照借阅本数从大到小排序；
select a.rno,count(b.bno) as bookCount from reader a
left join borrow b on a.rno=b.rno
GROUP BY a.rno having  bookCount > 2
ORDER BY bookCount desc
-- 10、查询读者的基本信息及其借阅情况（借阅情况指历史借阅数量，在借数量）；
select a.rname,count(c.rno) as 历史借阅数量,count(b.rno) as 在借数量 from reader a
left join borrow b on a.rno=b.rno and b.rdate is null
left join borrow c on a.rno=c.rno and c.rdate is not null
GROUP BY a.rname;

select r.rno as 读者号,r.rname as 姓名,count(*) as 历史借阅量,
count(*)-count(b.rdate) as 在借数量
from borrow as b inner join reader as r on r.rno=b.rno
group by b.rno;