刚学MySQL没多久，做一个小型项目有点晕

Question

问题遇到的现象和发生背景

问题相关代码，请勿粘贴截图

运行结果及报错内容

我的解答思路和尝试过的方法

我想要达到的结果

Answer 1

一共三张表：阅读者、书、阅读者和书的对应关系表

/*
 Navicat Premium Data Transfer

 Source Server         : 127
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : test

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

 Date: 09/06/2022 11:14:08
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '书号',
  `book_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '书名',
  `author` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '作者',
  `product` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '出版社',
  `flag` smallint NULL DEFAULT NULL COMMENT '借阅状态：0-未出借。1-出借',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('1', '十日谈', '薄迦丘', '古罗马', 1);
INSERT INTO `book` VALUES ('10', '假如给我三天光明', '海伦·凯勒', '柔弱女子', 1);
INSERT INTO `book` VALUES ('11', '钢铁是怎样炼成的', '青少年', '前苏联', 1);
INSERT INTO `book` VALUES ('12', '毛主席语录', '毛主席', '马克思主义', 1);
INSERT INTO `book` VALUES ('13', '圣经', '基督教徒', '犹太人', 1);
INSERT INTO `book` VALUES ('14', '小王子', '孩子', '善良人性', 1);
INSERT INTO `book` VALUES ('15', '安徒生童话', '童话集册', '寓意', 1);
INSERT INTO `book` VALUES ('16', '共产党宣言', '思想巨著', '共产主义', 1);
INSERT INTO `book` VALUES ('17', '狂人日记', '白话文', '讽刺', 1);
INSERT INTO `book` VALUES ('18', '红楼梦', '四大名著', '小说', 1);
INSERT INTO `book` VALUES ('19', '童年', '高尔基', '社会', 1);
INSERT INTO `book` VALUES ('2', '鲁滨逊漂流记', '很多人', '英国', 1);
INSERT INTO `book` VALUES ('20', '格列佛游记', '格列佛', '英国政府', 1);
INSERT INTO `book` VALUES ('3', '战争与和平', '托尔斯泰', '西方出版社', 1);
INSERT INTO `book` VALUES ('4', '海底两万里', '凡尔纳', '科幻三部曲', 1);
INSERT INTO `book` VALUES ('5', '汤姆·索亚历险记', '马克·吐温', '儿童文学作品', 1);
INSERT INTO `book` VALUES ('6', '福尔摩斯探案集', '阿瑟·柯南道尔', '侦探小说', 1);
INSERT INTO `book` VALUES ('7', '昆虫记', '法布尔', '昆虫研究', 1);
INSERT INTO `book` VALUES ('8', '爱的教育', '师生之爱', '教育经典', 1);
INSERT INTO `book` VALUES ('9', '名人传', '罗曼·罗兰', '传记作品集', 1);




-- ----------------------------
-- Table structure for reader
-- ----------------------------
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '阅读者主键',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '地址',
  `sex` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '所在系',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of reader
-- ----------------------------
INSERT INTO `reader` VALUES (1, '张三', '中国甘肃省兰州市', '男', 20, '计算机');
INSERT INTO `reader` VALUES (2, '李四', '中国北京', '女', 18, '土木');
INSERT INTO `reader` VALUES (3, '王五', '中国四川', '女', 18, '交通运输');
INSERT INTO `reader` VALUES (4, '二麻子', '中国新疆', '男', 19, '经管');
INSERT INTO `reader` VALUES (5, '狗剩', '中国内蒙', '男', 22, '法律');
INSERT INTO `reader` VALUES (6, '刘翔', '中国河北', '男', 23, '体育');
INSERT INTO `reader` VALUES (7, '詹姆斯', '美国', '男', 25, '体育');
INSERT INTO `reader` VALUES (8, '姚明', '中国上海', '男', 26, '体育');
INSERT INTO `reader` VALUES (9, '张杰', '中国湖南', '男', 19, '艺术');
INSERT INTO `reader` VALUES (10, '王飞', '中国甘肃', '男', 24, '计算机');
INSERT INTO `reader` VALUES (11, '王斌霞', '中国甘肃', '女', 18, '医学');
INSERT INTO `reader` VALUES (12, '张四', '中国甘肃省兰州市', '男', 20, '计算机');
INSERT INTO `reader` VALUES (13, '李五', '中国北京', '女', 18, '土木');
INSERT INTO `reader` VALUES (14, '王柳', '中国北京', '女', 18, '土木');
INSERT INTO `reader` VALUES (15, '博尔特', '地球', '男', 23, '体育');
INSERT INTO `reader` VALUES (16, '科比', '美国', '男', 25, '体育');
INSERT INTO `reader` VALUES (17, '王菲', '中国湖南', '女', 19, '艺术');
INSERT INTO `reader` VALUES (18, '老王', '中国甘肃', '男', 24, '计算机');
INSERT INTO `reader` VALUES (19, '王老大', '中国甘肃', '男', 24, '计算机');
INSERT INTO `reader` VALUES (20, '张丽', '中国甘肃', '女', 24, '计算机');

-- ----------------------------
-- Table structure for reader_book
-- ----------------------------
DROP TABLE IF EXISTS `reader_book`;
CREATE TABLE `reader_book`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '阅读者与图书对应关系表ID',
  `reader_id` bigint NULL DEFAULT NULL COMMENT '阅读者主键',
  `book_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '图书主键',
  `out_time` datetime NULL DEFAULT NULL COMMENT '借出日期',
  `in_time` datetime NULL DEFAULT NULL COMMENT '归还日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of reader_book
-- ----------------------------
INSERT INTO `reader_book` VALUES (1, 1, '1', '2022-06-09 10:25:47', NULL);
INSERT INTO `reader_book` VALUES (2, 2, '2', '2022-06-08 10:25:57', NULL);
INSERT INTO `reader_book` VALUES (3, 3, '3', '2022-06-04 10:26:08', NULL);
INSERT INTO `reader_book` VALUES (4, 4, '4', '2022-06-01 10:26:19', NULL);
INSERT INTO `reader_book` VALUES (5, 5, '5', '2022-06-02 10:26:27', NULL);
INSERT INTO `reader_book` VALUES (6, 6, '6', '2022-05-05 10:26:37', NULL);
INSERT INTO `reader_book` VALUES (7, 7, '7', '2022-06-08 10:26:50', NULL);
INSERT INTO `reader_book` VALUES (8, 8, '8', '2022-06-15 10:27:01', NULL);
INSERT INTO `reader_book` VALUES (9, 9, '9', '2022-06-04 10:27:10', NULL);
INSERT INTO `reader_book` VALUES (10, 10, '10', '2022-03-17 10:27:22', NULL);
INSERT INTO `reader_book` VALUES (11, 11, '11', '2021-12-24 10:27:32', NULL);
INSERT INTO `reader_book` VALUES (12, 12, '12', '2022-06-18 10:30:55', NULL);
INSERT INTO `reader_book` VALUES (13, 13, '13', '2022-06-23 10:31:06', NULL);
INSERT INTO `reader_book` VALUES (14, 14, '14', '2022-06-17 10:31:16', NULL);
INSERT INTO `reader_book` VALUES (15, 15, '15', '2022-06-12 10:31:26', NULL);
INSERT INTO `reader_book` VALUES (16, 16, '16', '2022-06-25 10:31:36', NULL);
INSERT INTO `reader_book` VALUES (17, 17, '17', '2021-10-22 10:31:47', NULL);
INSERT INTO `reader_book` VALUES (18, 18, '18', '2020-04-23 10:32:02', NULL);
INSERT INTO `reader_book` VALUES (19, 19, '19', '2021-07-24 10:32:18', NULL);
INSERT INTO `reader_book` VALUES (20, 20, '20', '2021-09-30 10:32:32', NULL);


SET FOREIGN_KEY_CHECKS = 1;

答案：

SELECT * FROM reader where sex='女';


SELECT * FROM reader where name LIKE '刘%';


SELECT * FROM reader WHERE age BETWEEN 20 AND 35;


SELECT * FROM reader order By age DESC;


SELECT
    department,
    COUNT(*) as total
FROM reader a 
JOIN reader_book b ON a.id=b.reader_id
JOIN book c ON c.id=b.book_id
WHERE c.flag = 1
GROUP BY department;


SELECT 
    a.name,
    a.address,
    a.sex,
    a.age,
    a.department,
    c.book_name,
    c.author,
    c.product,
    case c.flag
    WHEN 1 THEN '已借出'
    WHEN 0 THEN '未借出'
    END AS flag
FROM reader a
JOIN reader_book b ON a.id=b.reader_id
JOIN book c ON c.id=b.book_id
WHERE a.sex="女";


SELECT 
    c.name,
    a.book_name,
    a.author,
    a.product,
    case a.flag
    WHEN 1 THEN '已借出'
    WHEN 0 THEN '未借出'
    END AS flag
FROM 
book a
JOIN 
(
    SELECT 
        a.book_id,
        b.name
    FROM reader_book a
    JOIN reader b ON a.reader_id=b.id
    WHERE b.name LIKE '张%' 
) AS c ON c.book_id=a.id
WHERE a.flag=1;


# 某个借阅者借阅........

insert into reader_book VALUES(null,21,21,now(),null)

# 某个借阅者归还........
UPDATE reader_book set in_time=now() WHERE reader_id=1 AND