用sql系统编写一个图书借阅管理系统 ⑴功能要求 ①实现图书信息、类别、出版社等信息的管理； ②实现读者信息、借阅信息的管理； ③实现图书的借阅、续借、归还管理； ④实现超期罚款管理、收款管理； ⑵数据要求 ①创建视图实现查询功能； ②创建触发器，实现对基本表进行插入、修改或删除操作会使相应触发器运行，以保证操作不会破坏数据的完整性； ③创建存储过程； ④实现查询操作，指定查询统读者的借书量； ⑤建立数据库相关表之间的参照完整性约束； ⑶具体要求 ①图书信息管理：图书编号、书名、图书类型、作者、出版社、价格 ②图书类型：图书分类号、图书分类名称、描述信息 ③读者信息管理：读者证号、姓名、证件状态、联系方式、系部、专业班级 ④借阅管理：借书日期、应还日期、归还日期、罚款金额

很好，下面是一个简单的图书借阅管理系统的 SQL 实现：

首先创建基本表：

CREATE TABLE book (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    type_id INT,
    author VARCHAR(255),
    publisher VARCHAR(255),
    price DECIMAL(10, 2)
);

CREATE TABLE book_type (
    type_id INT PRIMARY KEY,
    type_name VARCHAR(255),
    description VARCHAR(255)
);

CREATE TABLE reader (
    reader_id INT PRIMARY KEY,
    name VARCHAR(255),
    id_status VARCHAR(255),
    contact VARCHAR(255),
    department VARCHAR(255),
    major_class VARCHAR(255)
);

CREATE TABLE borrowing (
    borrowing_id INT PRIMARY KEY,
    reader_id INT,
    book_id INT,
    borrow_date DATE,
    due_date DATE,
    return_date DATE,
    fine DECIMAL(10, 2),
    FOREIGN KEY (reader_id) REFERENCES reader(reader_id),
    FOREIGN KEY (book_id) REFERENCES book(book_id)
);

然后创建视图：

CREATE VIEW book_info AS 
SELECT book.book_id, book.title, book_type.type_name, book.author, book.publisher, book.price 
FROM book 
JOIN book_type ON book.type_id = book_type.type_id;

CREATE VIEW reader_borrowing AS 
SELECT reader.reader_id, reader.name, COUNT(borrowing.borrowing_id) AS borrowing_count 
FROM reader 
LEFT JOIN borrowing ON reader.reader_id = borrowing.reader_id 
GROUP BY reader.reader_id, reader.name;

接下来创建触发器：

CREATE TRIGGER check_borrowing_insert 
BEFORE INSERT ON borrowing 
FOR EACH ROW 
BEGIN
    IF (SELECT COUNT(*) FROM borrowing WHERE reader_id = NEW.reader_id AND return_date IS NULL) >= 5 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This reader has borrowed too many books';
    END IF;
END;

CREATE TRIGGER check_borrowing_update 
BEFORE UPDATE ON borrowing 
FOR EACH ROW 
BEGIN
    IF NEW.return_date IS NOT NULL AND NEW.return_date > OLD.due_date THEN
        SET NEW.fine = (NEW.return_date - OLD.due_date) * 0.1;
    END IF;
END;

CREATE TRIGGER check_borrowing_delete 
BEFORE DELETE ON borrowing 
FOR EACH ROW 
BEGIN
    IF OLD.return_date IS NULL AND OLD.due_date < CURDATE() THEN
        SET OLD.fine = (CURDATE() - OLD.due_date) * 0.1;
    END IF;
END;

然后创建存储过程：

CREATE PROCEDURE borrow_book (IN p_reader_id INT, IN p_book_id INT, IN p_borrow_date DATE, IN p_due_date DATE)
BEGIN
    INSERT INTO borrowing (reader_id, book_id, borrow_date, due_date) VALUES (p_reader_id, p_book_id, p_borrow_date, p_due_date);
END;

CREATE PROCEDURE return_book (IN p_borrowing_id INT, IN p_return_date DATE)
BEGIN
    UPDATE borrowing SET return_date = p_return_date WHERE borrowing_id = p_borrowing_id;
END;

最后建立参照完整性约束：

ALTER TABLE book ADD CONSTRAINT fk_book_type FOREIGN KEY (type_id) REFERENCES book_type(type_id);
ALTER TABLE borrowing ADD CONSTRAINT fk_borrowing_reader FOREIGN KEY (reader_id) REFERENCES reader(reader_id);
ALTER TABLE borrowing ADD CONSTRAINT fk_borrowing_book FOREIGN KEY (book_id) REFERENCES book(book_id);