Java项目-CSDN博客

本文链接：https://blog.csdn.net/lLlLlL__lL/article/details/133763488

项目需求

本图书管理系统主要用来管理图书信息，以及用户借阅图书相关信息

-- 创建图书信息表
CREATE TABLE `book`(
	 `bookId` INT(1)   AUTO_INCREMENT COMMENT '图书名称ID',
	 `bookName` VARCHAR(50)  NOT NULL COMMENT '图书名称',
	 `publisher` VARCHAR(50) NOT NULL COMMENT '出版社',
	 `author` VARCHAR(50) NOT NULL COMMENT '作者',
	 `bookType`  VARCHAR(50) DEFAULT NULL COMMENT '书籍类型',
	 `remain` INT(11) DEFAULT NULL COMMENT '库存',
	 PRIMARY KEY(`bookId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

DROP TABLE book

-- 添加数据
INSERT INTO `book`(`bookName`,`publisher`,`author`,`bookType`,`remain`) 
	VALUES('艺术设计必修课', '化学工业出版社', '霍金', '艺术类', 10);
INSERT INTO `book`(`bookName`,`publisher`,`author`,`bookType`,`remain`) 
	VALUES('中国力量超级工程', '四川少年儿童出版社', '四川少年', '科技类', 200);
INSERT INTO `book`(`bookName`,`publisher`,`author`,`bookType`,`remain`)
	VALUES ( '复活', '春风文艺出版社', '列夫·托尔斯泰', '文学类', 299);
INSERT INTO `book`(`bookName`,`publisher`,`author`,`bookType`,`remain`)
	VALUES ( '每天懂一点人情世故', '天津科学技术出版社', '章岩', '文学类', 1);
INSERT INTO `book`(`bookName`,`publisher`,`author`,`bookType`,`remain`)
	VALUES ( '孩子你要学会保护自己', '科学普及出版社', '未知', '文学类', 5);
INSERT INTO `book`(`bookName`,`publisher`,`author`,`bookType`,`remain`)
	VALUES ( '葫芦娃', '儿童出版社', '罗玲', '儿童类', 10);
INSERT INTO `book`(`bookName`,`publisher`,`author`,`bookType`,`remain`)
	VALUES ( '神秘的宇宙', '团结出版社', '詹姆斯•金斯', '科技类', 99);
INSERT INTO `book`(`bookName`,`publisher`,`author`,`bookType`,`remain`)
	VALUES ( '朗读者', '人民文学', '董卿', '文学类', 3);
	
-- 查看book表
SELECT * FROM book;

-- 创建管理员信息表
CREATE TABLE `admin`(
	`adminId` INT(1),
	`adminName` VARCHAR(10),
	`adminPassword` VARCHAR(20)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 添加数据
INSERT INTO `admin` VALUES(1001,'admin','admin');

-- 查看admin表
SELECT * FROM admin;

-- 创建用户信息表
CREATE TABLE `book_user`(
	`userId` INT(1) PRIMARY KEY AUTO_INCREMENT,
	`userName` VARCHAR(20) UNIQUE NOT NULL,
	`password` VARCHAR(20)  NOT NULL ,
	`phone` VARCHAR(11) NOT NULL UNIQUE,
	`birthday` VARCHAR(20) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 添加数据
INSERT INTO `book_user` (`userName`,`password`,`phone`,`birthday`) 
	VALUES('zhangsan','123456','13456789098','1999-2-2');


DROP TABLE book_user	

-- 查看book_user表
SELECT * FROM book_user;

-- 创建借阅信息表
CREATE TABLE `borrowinfo`(
	`userId` INT(1) NOT NULL,
	`bookId` INT(1) NOT NULL,
	`borrowtime` DATETIME DEFAULT NULL COMMENT '借阅时间',
	`returntime` DATETIME DEFAULT NULL COMMENT '归还时间',
	`isreturn` INT(11) DEFAULT NULL COMMENT '是否归还',     -- 1表示归还，0表示为归还
	PRIMARY KEY (`userId`,`bookId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


-- 添加数据
INSERT INTO `borrowinfo` (`userId`,`bookId`,`borrowtime`,`returntime`,`isreturn`)
	VALUES(1,1,'2023-9-10',NULL,0);
INSERT INTO `borrowinfo` (`userId`,`bookId`,`borrowtime`,`returntime`,`isreturn`)
	VALUES(1,2,'2023-9-15',NULL,0);	

DROP TABLE borrowinfo
	
-- 查看borrowinfo表
SELECT * FROM borrowinfo;








SELECT u.`userName` AS `userName`,
		u.`userName` AS `userName`,
		b2.`bookName` AS `bookName`,
		b1.`borrowtime` AS `borrowtime`,
		b1.`returntime` AS `returntime` 
		 FROM `book_user` AS u LEFT JOIN  `borrowinfo` AS b1 ON u.`userId` = b1.`userId`
							   LEFT JOIN `book` AS b2 ON b1.`bookId` = b2.`bookId`
							   WHERE u.`userName` = 'zhangsan';





查询借阅表中，未归还图书借阅时间超过60天的图书
SELECT b2.* 
	FROM borrowinfo AS b1
	JOIN book AS b2 ON b1.bookId = b2.bookId
	WHERE b1.isreturn = 0
	AND b1.borrowtime <= DATE_SUB(NOW(), INTERVAL 60 DAY);
	
	
SELECT * FROM borrowinfo WHERE userid = 1
	AND DATEDIFF(NOW(),borrowtime) > 60
	AND isreturn = 0;

SELECT COUNT(1) FROM borrowinfo WHERE userId = 1 AND isreturn = 0