图书馆借阅表

人间惊鸿343

于 2024-06-26 17:43:20 发布

阅读量303

点赞数 10

文章标签：数据库

本文链接：https://blog.csdn.net/2403_83963322/article/details/139994862

版权

DDL
用户表 (Users)
图书表 (Books)
图书类别表 (BookCategories)
图书与类别关联表 (BookCategoryRelations)
借阅记录表 (BorrowRecords)
供应商表 (Suppliers)
采购记录表 (PurchaseRecords)

CREATE TABLE `bookcategories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `category_name` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
CREATE TABLE `bookcategoryrelations` (
  `relation_id` int(11) NOT NULL AUTO_INCREMENT,
  `book_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`relation_id`),
  UNIQUE KEY `book_id` (`book_id`,`category_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `bookcategoryrelations_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `bookcategoryrelations_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `bookcategories` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `books` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `author` varchar(100) NOT NULL,
  `publisher` varchar(100) DEFAULT NULL,
  `publication_year` year(4) DEFAULT NULL,
  `ISBN` varchar(13) DEFAULT NULL,
  `stock_quantity` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`book_id`),
  UNIQUE KEY `ISBN` (`ISBN`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
 
CREATE TABLE `borrowrecords` (
  `record_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `book_id` int(11) DEFAULT NULL,
  `borrow_date` date NOT NULL,
  `return_date` date DEFAULT NULL,
  PRIMARY KEY (`record_id`),
  KEY `user_id` (`user_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `borrowrecords_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `borrowrecords_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
CREATE TABLE `purchaserecords` (
  `record_id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier_id` int(11) DEFAULT NULL,
  `book_id` int(11) DEFAULT NULL,
  `purchase_date` date NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` decimal(10,4) NOT NULL,
  PRIMARY KEY (`record_id`),
  KEY `supplier_id` (`supplier_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `purchaserecords_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`supplier_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `purchaserecords_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE `suppliers` (
  `supplier_id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier_name` varchar(100) NOT NULL,
  `contact_name` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`supplier_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
 
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

DML

INSERT INTO Users (username, password, email, phone)
VALUES ('user1', 'pass1', 'user1@example.com', '1234567890');
 
INSERT INTO Users (username, password, email, phone)
VALUES ('user2', 'pass2', 'user2@example.com', '0987654321');
 
INSERT INTO Users (username, password, email, phone)
VALUES ('user3', 'pass3', NULL, '1112223333');
 
INSERT INTO Users (username, password, email, phone)
VALUES ('user4', 'pass4', 'user4@example.com', NULL);
 
INSERT INTO Users (username, password, email, phone)
VALUES ('user5', 'pass5', 'user5@example.com', '5556667777');
 
INSERT INTO Users (username, password, email, phone)
VALUES ('user6', 'pass6', NULL, '9998887777');
 
INSERT INTO Users (username, password, email, phone)
VALUES ('user7', 'pass7', 'user7@example.com', '7778889999');
 
INSERT INTO BookCategoryRelations (book_id, category_id)
VALUES (1, 1);
 
INSERT INTO BookCategoryRelations (book_id, category_id)
VALUES (2, 2);
 
INSERT INTO BookCategoryRelations (book_id, category_id)
VALUES (3, 3);
 
INSERT INTO BookCategoryRelations (book_id, category_id)
VALUES (4, 4);
 
INSERT INTO BookCategoryRelations (book_id, category_id)
VALUES (5, 5);
 
INSERT INTO BookCategoryRelations (book_id, category_id)
VALUES (6, 6);
 
INSERT INTO BookCategoryRelations (book_id, category_id)
VALUES (7, 7);
 
 
INSERT INTO Books (title, author, publisher, publication_year, ISBN, stock_quantity)
VALUES ('Book 1', 'Author 1', 'Publisher 1', 2020, '1234567890123', 10);
 
INSERT INTO Books (title, author, publisher, publication_year, ISBN, stock_quantity)
VALUES ('Book 2', 'Author 2', 'Publisher 2', 2019, '2345678901234', 15);
 
INSERT INTO Books (title, author, publisher, publication_year, ISBN, stock_quantity)
VALUES ('Book 3', 'Author 3', 'Publisher 3', 2021, '3456789012345', 20);
 
INSERT INTO Books (title, author, publisher, publication_year, ISBN, stock_quantity)
VALUES ('Book 4', 'Author 4', 'Publisher 4', 2018, '4567890123456', 25);
 
INSERT INTO Books (title, author, publisher, publication_year, ISBN, stock_quantity)
VALUES ('Book 5', 'Author 5', 'Publisher 5', 2022, '5678901234567', 30);
 
INSERT INTO Books (title, author, publisher, publication_year, ISBN, stock_quantity)
VALUES ('Book 6', 'Author 6', 'Publisher 6', 2023, '6789012345678', 35);
 
INSERT INTO Books (title, author, publisher, publication_year, ISBN, stock_quantity)
VALUES ('Book 7', 'Author 7', 'Publisher 7', 2024, '7890123456789', 40);
 
INSERT INTO BookCategories (category_name)
VALUES ('Category 1');
 
INSERT INTO BookCategories (category_name)
VALUES ('Category 2');
 
INSERT INTO BookCategories (category_name)
VALUES ('Category 3');
 
INSERT INTO BookCategories (category_name)
VALUES ('Category 4');
 
INSERT INTO BookCategories (category_name)
VALUES ('Category 5');
 
INSERT INTO BookCategories (category_name)
VALUES ('Category 6');
 
INSERT INTO BookCategories (category_name)
VALUES ('Category 7');
 
INSERT INTO BorrowRecords (user_id, book_id, borrow_date, return_date)
VALUES (1, 1, '2024-07-01', '2024-07-15');
 
INSERT INTO BorrowRecords (user_id, book_id, borrow_date, return_date)
VALUES (2, 2, '2024-07-05', '2024-07-20');
 
INSERT INTO BorrowRecords (user_id, book_id, borrow_date, return_date)
VALUES (3, 3, '2024-07-10', NULL);
 
INSERT INTO BorrowRecords (user_id, book_id, borrow_date, return_date)
VALUES (4, 4, '2024-07-15', NULL);
 
INSERT INTO BorrowRecords (user_id, book_id, borrow_date, return_date)
VALUES (5, 5, '2024-07-20', NULL);
 
INSERT INTO BorrowRecords (user_id, book_id, borrow_date, return_date)
VALUES (6, 6, '2024-07-25', NULL);
 
INSERT INTO BorrowRecords (user_id, book_id, borrow_date, return_date)
VALUES (7, 7, '2024-07-30', NULL);
 
INSERT INTO Suppliers (supplier_name, contact_name, phone, email)
VALUES ('Supplier 1', 'Contact 1', '1234567890', 'upplier1@example.com');
 
INSERT INTO Suppliers (supplier_name, contact_name, phone, email)
VALUES ('Supplier 2', 'Contact 2', '0987654321', 'upplier2@example.com');
 
INSERT INTO Suppliers (supplier_name, contact_name, phone, email)
VALUES ('Supplier 3', 'Contact 3', '1112223333', 'upplier3@example.com');
 
INSERT INTO Suppliers (supplier_name, contact_name, phone, email)
VALUES ('Supplier 4', 'Contact 4', '4445556666', 'upplier4@example.com');
 
INSERT INTO Suppliers (supplier_name, contact_name, phone, email)
VALUES ('Supplier 5', 'Contact 5', '7778889999', 'upplier5@example.com');
 
INSERT INTO Suppliers (supplier_name, contact_name, phone, email)
VALUES ('Supplier 6', 'Contact 6', '0001112222', 'upplier6@example.com');
 
INSERT INTO Suppliers (supplier_name, contact_name, phone, email)
VALUES ('Supplier 7', 'Contact 7', '3334445555', 'upplier7@example.com');
 
INSERT INTO PurchaseRecords (supplier_id, book_id, purchase_date, quantity, price)
VALUES (1, 1, '2024-06-01', 50, 20.00);
 
INSERT INTO PurchaseRecords (supplier_id, book_id, purchase_date, quantity, price)
VALUES (2, 2, '2024-06-05', 40, 18.00);
 
INSERT INTO PurchaseRecords (supplier_id, book_id, purchase_date, quantity, price)
VALUES (3, 3, '2024-06-10', 30, 25.00);
 
INSERT INTO PurchaseRecords (supplier_id, book_id, purchase_date, quantity, price)
VALUES (4, 4, '2024-06-15', 20, 30.00);
 
INSERT INTO PurchaseRecords (supplier_id, book_id, purchase_date, quantity, price)
VALUES (5, 5, '2024-06-20', 15, 22.00);
 
INSERT INTO PurchaseRecords (supplier_id, book_id, purchase_date, quantity, price)
VALUES (6, 6, '2024-06-25', 10, 15.00);
 
INSERT INTO PurchaseRecords (supplier_id, book_id, purchase_date, quantity, price)
VALUES (7, 7, '2024-06-30', 5, 10.00);

简单查询与多表联合复杂查询

简单查询

SELECT username AS '用户名', phone AS '手机号' FROM Users;
EXPLAIN SELECT * FROM Books WHERE title LIKE '%book%';
SELECT user_id, COUNT(*) AS '下单数量' FROM BorrowRecords GROUP BY user_id ORDER BY COUNT(*) DESC;

复杂查询

SELECT u.*, br.*
FROM Users u
JOIN BorrowRecords br ON u.user_id = br.user_id;
 
SELECT u.*
FROM Users u
JOIN (
    SELECT user_id, DATEDIFF(IFNULL(return_date, CURDATE()), borrow_date) AS borrowing_duration
    FROM BorrowRecords
    ORDER BY borrowing_duration DESC
    LIMIT 1
) t ON u.user_id = t.user_id;
 
SELECT u.*, br.*, b.*
FROM Users u
JOIN BorrowRecords br ON u.user_id = br.user_id
JOIN Books b ON br.book_id = b.book_id
WHERE b.book_id = (
    SELECT book_id
    FROM (
        SELECT book_id, COUNT(*) as borrow_count
        FROM BorrowRecords
        GROUP BY book_id
        ORDER BY borrow_count DESC
        LIMIT 1
    ) t
);

触发器-插入-修改-删除

1

DELIMITER //
 
CREATE TRIGGER insert_user_trigger AFTER INSERT ON Users
FOR EACH ROW
BEGIN
    -- 注释：在插入用户信息后，向借阅记录表中插入初始数据，借阅数量为 0
    INSERT INTO BorrowRecords (user_id, borrow_date, return_date) VALUES (NEW.user_id, CURDATE(), NULL);
END//
 
DELIMITER ;
-- 测试语句：
INSERT INTO Users (username, password, email, phone)
VALUES ('user8', 'pass8', 'user8@example.com', '8887776666');
 
SELECT * FROM BorrowRecords WHERE user_id = (SELECT LAST_INSERT_ID());

2

DELIMITER //
 
CREATE TRIGGER update_purchase_price_trigger BEFORE UPDATE ON PurchaseRecords
FOR EACH ROW
BEGIN
    DECLARE old_price DECIMAL(10, 4);
    DECLARE percentage_change DECIMAL(5, 2);
 
    -- 注释：获取旧的价格
    SET old_price = OLD.price;
 
    -- 注释：计算价格变动百分比
    SET percentage_change = ((NEW.price - old_price) / old_price) * 100;
 
    -- 注释：如果价格变动超过 10%，则不允许修改
    IF ABS(percentage_change) > 10 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '价格变动不能超过 10%';
    END IF;
END//
 
DELIMITER ;
-- 测试语句：
-- 尝试修改价格超过 10%
UPDATE PurchaseRecords SET price = 25.00 WHERE record_id = 1;
 
-- 尝试修改价格在 10%范围内
UPDATE PurchaseRecords SET price = 22.00 WHERE record_id = 1;

3

DELIMITER //
 
CREATE TRIGGER delete_supplier_trigger BEFORE DELETE ON Suppliers
FOR EACH ROW
BEGIN
    -- 注释：在删除供应商前，先删除采购记录表中该供应商的相关信息
    DELETE FROM PurchaseRecords WHERE supplier_id = OLD.supplier_id;
END//
 
DELIMITER ;
-- 测试语句：
DELETE FROM Suppliers WHERE supplier_id = 1;
 
SELECT * FROM PurchaseRecords WHERE supplier_id = 1;

存储过程创建

DELIMITER //
 
CREATE PROCEDURE create_borrowrecord(
    IN in_supplier_id INT,
    IN in_book_id INT,
    IN in_purchase_quantity INT
)
BEGIN
    -- 开始事务，确保数据操作的原子性
    START TRANSACTION;
 
    -- 插入采购记录
    INSERT INTO Purchaserecords (supplier_id, book_id, purchase_date, quantity, price)
    VALUES (in_supplier_id, in_book_id, CURDATE(), in_purchase_quantity, 0.00);
 
    -- 更新图书表的库存数量
    UPDATE Books
    SET stock_quantity = stock_quantity + in_purchase_quantity
    WHERE book_id = in_book_id;
 
    -- 提交事务
    COMMIT;
END//
 
DELIMITER ;

测试语句

CALL create_borrowrecord(8, 8, 10);  -- 假设添加新的供应商 8 和新的图书 8，采购数量为 10
 
SELECT * FROM Purchaserecords;  -- 查看采购记录是否添加成功
SELECT * FROM Books WHERE book_id = 8;  -- 查看图书 8 的库存是否更新