编辑代码

CREATE DATABASE test1 CHARSET=utf8;
use test1;

CREATE TABLE t_book(
BookID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(32),
Author VARCHAR(32),
Price DECIMAL(9,2),
BookCount INT DEFAULT 20,
BorrowCount INT) ENGINE=InnoDB;

CREATE TABLE t_card(
CardID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(32),
Addr VARCHAR(256),
Phone VARCHAR(11),
CONSTRAINT t_card_chk_1
CHECK(CHAR_LENGTH(Phone)=11)
) ENGINE=InnoDB;

CREATE TABLE t_borrow(
BorrowID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
DateIn DATETIME,
DateOut DATETIME,
BookID INT,
CardID INT,
CONSTRAINT fk_borrow_book
FOREIGN KEY (BookID)
REFERENCES t_book(BookID)
ON DELETE CASCADE,
CONSTRAINT fk_borrow_card
FOREIGN KEY (CardID)
REFERENCES t_card(CardID)
ON DELETE CASCADE
) ENGINE=InnoDB;





INSERT INTO t_book VALUES(0,'aaa','bbb',9.2,2,0);
INSERT INTO t_book(Name,Author,Price,BorrowCount) VALUES('ccc','ddd',9.2,4);
INSERT INTO t_book(Name,Author,Price,BorrowCount) VALUES('eee','ffff',9.2,4);
INSERT INTO t_book(Name,Author,Price,BorrowCount) VALUES('ggg','hhh',9.2,1);
INSERT INTO t_book VALUES(0,'iii','jjj',9.2,1,0);


INSERT INTO t_card VALUES(0,'paul','js','12345678901');
INSERT INTO t_card VALUES(0,'jey','sh','12345678902');

INSERT INTO t_borrow VALUES(0,'2025/04/17 14:49:00','2025/04/17 15:49:00',1,2);
INSERT INTO t_borrow VALUES(0,'2025/04/18 14:49:00','2025/04/18 15:49:00',2,1);
INSERT INTO t_borrow VALUES(0,'2025/04/19 14:49:00','2025/04/19 14:49:00',3,1);

UPDATE t_book SET Price=Price*1.1 WHERE Name='eee';

UPDATE t_borrow SET DateIn='2025/04/20 15:48:49' WHERE BorrowID=2;

SELECT * FROM t_book WHERE BookCount<2;
SELECT t_card.CardID,t_card.Name,t_book.Name,DateOut FROM t_book,t_card,t_borrow WHERE t_book.BookID=t_borrow.BookID AND t_card.CardID=t_borrow.CardID AND t_borrow.DateIn>t_borrow.DateOut;


DELIMITER $$
CREATE PROCEDURE prc_CountBorrow(No INT)
BEGIN
SELECT BorrowCount FROM t_book WHERE BookID=No;
END$$
DELIMITER ;

call prc_CountBorrow(1);