-- 图书借阅数据库设计
网站建设哪家好，找创新互联公司！专注于网页设计、网站建设、微信开发、小程序定制开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了连平免费建站欢迎大家使用！
-- 记录每一本图书借出和还回的操作
-- 书的信息
DROP DATABASE IF EXISTS db_lib;
CREATE DATABASE db_lib;
use db_lib
SET @READER_MAX = 10;
SET @EVERY_DAY_FEE=0.1;
SELECT 10 INTO @temp;
-- 新建相关的表
CREATE TABLE books (
id INT PRIMARY KEY AUTO_INCREMENT,
name CHAR(255) NOT NULL,
price DECIMAL(8,2) DEFAULT 0
);
CREATE TABLE readers (
id INT PRIMARY KEY AUTO_INCREMENT,
name CHAR(255) NOT NULL,
borrowed INT DEFAULT 0
);
CREATE TABLE lib_books (
id INT PRIMARY KEY AUTO_INCREMENT,
info INT,
FOREIGN KEY(info) REFERENCES books(id),
stat CHAR(10) NOT NULL DEFAULT "在馆"
);
-- 记录借书和还书
CREATE TABLE lib_loan (
id INT AUTO_INCREMENT,
who INT,
which INT,
check_out DATETIME NOT NULL DEFAULT NOW(),
check_in DATETIME DEFAULT NULL,
renew BOOLEAN NOT NULL DEFAULT 0,
latest DATE NOT NULL,
fee DECIMAL(8,2) DEFAULT NULL,
PRIMARY KEY(id),
FOREIGN KEY(who) REFERENCES readers(id),
FOREIGN KEY(which) REFERENCES lib_books(id)
);
-- 给馆藏图书添加10本书
-- 存储过程
DELIMITER $$$
CREATE PROCEDURE add_books( IN n INT)
BEGIN
-- 需要有多行数据放入变量，需要用 游标 cursor
-- i 局部变量
DECLARE i INT DEFAULT 1;
-- @temp全局变量
SELECT COUNT(*) INTO @temp
FROM books;
WHILE i=n DO
INSERT INTO lib_books (info) VALUES ( FLOOR(RAND()*@temp)+1 );
SET i = i+1;
END WHILE;
END $$$
DELIMITER ;
-- 触发器 trigger ,管理图书被借出后，藏书和读者信息的维护
-- 表中数据相关的操作： 增 删 改 （查）
-- 一个表中某条记录被修改：OLD 代表原有的那条记录 NEW代表新的那条记录
-- [BEFORE | AFTER] [INSERT | UPDATE | (DELETE) ]
DELIMITER $$$
CREATE TRIGGER before_insert_loan
BEFORE INSERT ON lib_loan
FOR EACH ROW
BEGIN
-- OLD NEW 已知
DECLARE v1 CHAR(100);
DECLARE v2 INT;
SET NEW.latest =DATE_ADD(NEW.check_out,INTERVAL 1 month);
-- 只能修改NEW
SELECT stat INTO v1
FROM lib_books
WHERE id=NEW.which;
SELECT borrowed INTO v2
FROM readers
WHERE id=NEW.who;
IF ( v1"在馆" ) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "图书已借出！";
END IF ;
IF (v2=@READER_MAX) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "读者已经到达最大借阅数！";
END IF ;
END $$$
DELIMITER ;
-- 书借出的数据维护
DELIMITER $$$
CREATE TRIGGER after_insert_loan
AFTER INSERT ON lib_loan
FOR EACH ROW
BEGIN
UPDATE lib_books
SET stat = "已借出"
WHERE id = NEW.which ;
UPDATE readers
SET borrowed = borrowed + 1
WHERE id = NEW.who;
END $$$
DELIMITER ;
-- 书归还的数据维护
DELIMITER $$$
CREATE TRIGGER after_update_loan
AFTER UPDATE ON lib_loan
FOR EACH ROW
BEGIN
IF(OLD.check_in is NULL AND NEW.check_in is NOT NULL) THEN
UPDATE lib_books
SET stat="在馆"
WHERE id= NEW.which;
UPDATE readers
SET borrowed=borrowed -1
WHERE id=OLD.who;
END IF;
END $$$
DELIMITER ;
DELIMITER $$$
CREATE TRIGGER before_update_loan
BEFORE UPDATE ON lib_loan
FOR EACH ROW
BEGIN
DECLARE x INT;
IF(OLD.renew=0 AND NEW.renew=1 ) THEN
SET NEW.latest=DATE_ADD(OLD.latest,INTERVAL 1 month);
END IF;
-- 计算超期费用
IF(OLD.check_in is NULL AND NEW.check_in is NOT NULL) THEN
SET x=DATEDIFF(NEW.check_in,NEW.latest);
IF(x=0 )THEN
SET NEW.fee =0 ;
ELSE
SET NEW.fee=@EVERY_DAY_FEE*x;
END IF;
END IF;
END $$$
DELIMITER ;
-- 添加若干的测试数据
INSERT INTO books (name, price)
VALUES
("javascript程序设计", 19.8),
("数据库系统开发", 20.8),
("mysql数据库必知必会", 28.8);
INSERT INTO readers ( name )
VALUES
("Alice"),
("Bob"),
("Carl");
-- 测试语句
SHOW TABLES;
-- 根据书的信息添加10本馆藏图书
CALL add_books( 10) ;
SELECT * from lib_books;
-- 借书
-- 2号读者借了2号书
INSERT INTO lib_loan (who , which ) VALUES (2,2);
INSERT INTO lib_loan (who , which ) VALUES (2,3);
INSERT INTO lib_loan (who , which ) VALUES (2,4);
INSERT INTO lib_loan (who , which ) VALUES (1,2);
INSERT INTO lib_loan (who , which ) VALUES (1,6);
INSERT INTO lib_loan (who , which ) VALUES (2,1);
INSERT INTO lib_loan (who , which ) VALUES (1,1);
-- 还书
UPDATE lib_loan
SET check_in='2022-12-15 12:00:00'
WHERE id = 1;
-- 续借
UPDATE lib_loan
SET renew = 1
WHERE id = 4;
SELECT * FROM lib_books;
SELECT * FROM readers;
SELECT * FROM lib_loan;
HashMap的常用方法
map的存储和修改方式 map.put(key, value);
map是否包含某个元素map.containsKey(key);
map某个key的value值map.get(key);
参考代码
import java.util.HashMap;
public class BookTest {
static HashMapString, Integer map=new HashMapString, Integer();
static{//初始化图书信息
map.put("三国志", 8);
map.put("红楼梦", 0);
map.put("西游记", 15);
}
public static void main(String[] args) {//main方法
jieShu("水浒");
jieShu("三国志");
huanShu("三国志");
jieShu("红楼梦");
huanShu("红楼梦");
jieShu("红楼梦");
}
public static void jieShu(String name){//借书
if(!map.containsKey(name)){//查询图书馆是否有这本书
System.out.println("书籍："+name+"\t查无此书！");
return;
}
if(map.get(name)=0){//查询图书数量是否为0
System.out.println("书籍："+name+"\t已全部借出，请明日在来");
return;
}
map.put(name, map.get(name)-1);
System.out.println("书籍："+name+"\t借书成功。"+"\t库存："+map.get(name));
}
public static void huanShu(String name){//还书
if(!map.containsKey(name)){
System.out.println("查无此书");
}else{
map.put(name, map.get(name)+1);
System.out.println("书籍："+name+"\t还书成功。"+"\t库存："+map.get(name));
}
}
}
测试输出
书籍：水浒 查无此书！
书籍：三国志 借书成功。 库存：7
书籍：三国志 还书成功。 库存：8
书籍：红楼梦 已全部借出，请明日在来
书籍：红楼梦 还书成功。 库存：1
书籍：红楼梦 借书成功。 库存：0
switch(key){
case 1:
//借书
break;
case 2:
//还书
break;
}