mysql数据库图书借还系统

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255),
    Author VARCHAR(255),
    ISBN VARCHAR(13),
    PublishDate DATE,
    Status ENUM('Available', 'Borrowed')
);

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(255),
    ContactInfo VARCHAR(255)
);

CREATE TABLE BorrowRecords (
    RecordID INT PRIMARY KEY,
    BookID INT,
    UserID INT,
    BorrowDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

START TRANSACTION;
UPDATE Books SET Status = 'Borrowed' WHERE BookID = ?;
INSERT INTO BorrowRecords (BookID, UserID, BorrowDate) VALUES (?, ?, NOW());
COMMIT;

CREATE INDEX idx_bookid ON BorrowRecords(BookID);
SELECT * FROM BorrowRecords WHERE BookID = ?;

SELECT * FROM Books WHERE BookID = ? FOR UPDATE;
UPDATE Books SET Status = 'Borrowed' WHERE BookID = ?;