-- 图书借阅数据库设计
为北京等地区用户提供了全套网页设计制作服务，及北京网站建设行业解决方案。主营业务为成都网站制作、成都网站设计、北京网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！
-- 记录每一本图书借出和还回的操作
-- 书的信息
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;
switch(key){
case 1:
//借书
break;
case 2:
//还书
break;
}
package Management;
import java.util.List;
import java.util.ArrayList;
public class Management {
public static ListBook bookList=null;
public Management() {
// TODO Auto-generated constructor stub
bookList=new ArrayListBook(100);
}
public void addbook(Book book)
{
bookList.add(book);
}
public Book[] findBook(String bookName)
{
Book [] book=new Book[100];
int j=0;
for(int i=0;ibookList.size();i++)
{
if(bookName.equals(bookList.get(i).getName()))
{
book[j]=bookList.get(i);
j++;
}
}
return book;
}
public Book[] ShowAllBook()
{
Book[]book=new Book[100];
for(int i=0;ibookList.size();i++)
{
book[i]=bookList.get(i);
bookList.get(i).printInfo();
}
return book;
}
}
class Book
{
private String name;
private String author;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
void printInfo()
{
System.out.println("书名为"+this.name+"作者为"+this.author);
}
}
class Reader
{
private String name;
public void borrowbook(Book book) {
Management liabry=new Management();
for(int i=0;iliabry.bookList.size();i++)
{ if(book.getAuthor().equals(liabry.bookList.get(i).getAuthor())book.getName().equals(liabry.bookList.get(i).getName()))
{
liabry.bookList.remove(i);
System.out.println("借书成功！");
break;
}
}
}
public void backbook(Book book) {
Management liabry=new Management();
liabry.addbook(book);
System.out.println("还书成功");
}
}