**
建表：
管理员：Administrator（角色名，密码，备注）
读者：TReader（借书证号，密码，姓名，性别，出生时间，专业，借书量，照片，备注，联系方式）
图书：TBook（ISBN，书名，作译者，出版社，出版年月，价格，复本量，库存量，分类号，内容提要，封面照片）
借阅：TLend（借书证号，ISBN，图书ID，借书时间，应还时间）
归还：HLend（编号、借书证号、ISBN、图书ID、借书时间、还书时间）
借书情况：TBLend（图书ID，ISBN，是否借出）
功能实现：
“生成图书借出情况”功能实现 PROCEDURE 此功能使用存储过程来实现，存储过程名为BookID_Generate，参数：ISBN(图书的ISBN)，count(复本量)，firstID（起始的图书ID）。使用循环向TBLend表中插入count条记录，图书ID的起始值为firstID，之后每增加一条借出记录图书ID值加1。
“图书借阅”功能实现 create procedure Book_Borrow 编写思路：
（1）此功能使用存储过程来实现，存储过程名称为Book_Borrow。参数：借书证号（in_ReaderID）、ISBN（in_ISBN）、图书ID（in_BookID）、执行信息（out_str）。根据存储过程的前3个参数，实现读者图书“借阅”。第四个参数为输出参数，将存储过程的执行情况以字符串形式赋予此参数。
（2）根据“借书证号”查询读者信息表（TReader）是否存在该读者，如果不存在，则将输出参数out_str赋值为“该读者不存在”并返回0，存储过程结束，表示不能借书。
（3）根据“ISBN”查询图书信息表（TBook），查询是否存在该图书，如果不存在，则将输出参数赋值为“该图书不存在”并返回0，存储过程结束，表示不能借书。
（4）根据“借书证号”查询读者信息表（TReader），查询该读者的借书量。如果借书量=5，则将输出参数赋值为“读者借书量不能大于5”并返回0，存储过程结束，表示不能借书。
（5）根据“ISBN”查询图书信息表（TBook）中该图书的库存量。如果库存量=0，则将输出参数赋值为“图书库存量为0”并返回0，存储过程结束，表示不能借书。
（6）查询借阅表（TLend），查看该读者是否已经借阅该图书，如果已经借过，则将输出参数赋值为“读者已经借过该书”并返回0，存储过程结束，表示不能借书。
（7）查询借阅表（TLend），查看该图书ID是否已经存在，如果存在则将输出参数赋值为“该图书ID已存在”并返回0，存储过程结束，表示不能添加借书记录。
（8）使借阅表（TLend）增加一条该读者借书记录；
读者信息表（TReader）中该读者的借书量加1；
图书信息表（TBook）该图书（对应ISBN）记录的库存量减1；
将是否借出表（TBLend）中该图书（对应的图书ID）记录的“是否借出”信息修改为1。
存储过程结束，将输出参数赋值为“借书成功”并返回1，表示借书成功。
（9）如果存储过程执行过程中遇到错误则回滚之前进行的操作，并将输出参数赋为“执行过程中遇到错误”并返回0，表示存储过程执行中遇到错误，回滚到执行存储过程前的状态。
“同步删除图书借出情况”功能实现 create trigger TBook_delete ON TBook 实现功能：在TBook表中删除一条图书记录时，同时删除TBLend表中与该书相关的记录，以及在TLend表中删除相应记录。
编写思路：该功能使用在TBook表中定义删除触发器的方法实现。触发器名称为Book_delete，触发器类型为DML触发器，在对TBook表进行了DELETE操作后激活。
“图书归还”功能实现 create trigger TLend_after_delete on TLend after delete 实现功能：
当读者“归还”图书时，即删除借阅表（TLend）中的一条借书记录时，
读者信息表（TReader）该读者的借书量减1；
图书信息表（TBook）该图书记录的库存量加1；
图书是否借出表（TBLend）的是否借出信息改为0 ×× TBook中库存量=副本量 ；
还书记录表（HTLend）添加一条该读者的还书记录。
“统计借书次数”功能实现 CREATE FUNCTION L_count( @ReaderID char(6) ) 实现功能：图书管理员输入读者的借书证号，可以统计读者总共借过多少次书，包括借过归还和在借的次数。
编写思路：本功能使用用户自定义标量函数实现，函数名为L_count。使用借书证号作为参数，参数名为ReaderID，通过查询借阅表TLend和还书记录表HLend中该读者的借书记录，将查得的次数相加得到该读者总的借书次数并返回
**
`use mbook;
CREATE TABLE `TBook`
(
`ISBN` char(18) NOT NULL PRIMARY KEY,
`书名` varchar(100) NOT NULL,
`作译者` varchar(100) NOT NULL,
`出版社` varchar(100) NOT NULL,
`出版年月` char(10) NULL,
`价格` float NULL,
`复本量` int NOT NULL,
`库存量` int NOT NULL DEFAULT '1',
`分类号` char(18) NULL,
`内容提要` varchar(150) NULL,
`封面照片` varbinary(200) NULL
);
use mbook;
CREATE TABLE `TReader`
(
`借书证号` char(6) NOT NULL PRIMARY KEY,
`密码` varchar(20) NOT NULL,
`姓名` char(8) NOT NULL,
`性别` bit NOT NULL,
`出生时间` date NOT NULL,
`专业` char(12) NOT NULL,
`借书量` int NOT NULL DEFAULT '0',
`照片` varbinary(200) NULL,
`备注` varchar(200) NULL
);
create table `HLend`
(
`编号` int not null primary key auto_increment,
`借书证号` char(6) not null,
`ISBN` char(18) not null,
`图书ID` char(10) not null,
`借书时间` datetime not null,
`还书时间` datetime not null
);
create table `TLend`
(
借书证号 char(6) not null,
ISBN char(18) not null,
图书ID char(10) primary key,
借书时间 datetime not null,
应还时间 datetime not null
);
create table `TBLend`
(
图书ID char(10) primary key,
ISBN char(18) not null,
是否借出 bit not null
);
CREATE TABLE Administrator
(
`角色名` char(20) NOT NULL PRIMARY KEY,
`密码` VARCHAR(20) NOT NULL,
`备注` VARCHAR(100) NULL
);
约束-constraints：
use mbook;
alter table TReader add check(借书量>=0 and 借书量<=5);
alter table TBook add constraint CK_TBook check(库存量<=复本量);
alter table tlend add constraint foreign key (readerId) references treader(readerId) on delete cascade;
alter table tlend add constraint foreign key (isbn) references tbook(isbn) on delete cascade;
视图:
use MBOOK;
DROP VIEW IF EXISTS `rbl`;
use MBOOK;
create view RBL
as
select TLend.借书证号,TReader.姓名,TReader.借书量,TLend.ISBN,
TBook.书名,TBook.出版社,TBook.价格,TLend.图书ID,TLend.借书时间
from TReader inner join TLend on TReader.借书证号=TLend.借书证号
inner join TBook on TLend.ISBN=TBook.ISBN
set global log_bin_trust_function_creators=TRUE;
DROP FUNCTION IF EXISTS `L_count`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `L_count`(ReaderID char(6)) RETURNS int(11)
BEGIN
declare count1 integer;
declare count2 integer;
select count(readerId) into count1 from tlend where readerId=ReaderID;
select count(readerId) into count2 from hlend where readerId=ReaderID;
return count1+count2;
END ;;
DELIMITER ;
触发器：
DROP TRIGGER IF EXISTS `tlend_AFTER_DELETE`;
DELIMITER ;;
CREATE TRIGGER `tlend_AFTER_DELETE` AFTER DELETE ON `tlend` FOR EACH ROW BEGIN
declare _ReaderID char(6);
declare _isbn char(18);
declare _Book_ID char(10);
declare _LTime datetime;
-- declare flag int;
set _ReaderID=old.readerId;
set _isbn=old.isbn;
set _Book_ID=old.bookId;
set _Ltime=old.borrowTime;
update TReader set borrowNum = borrowNum-1 where readerId=_ReaderID;
update TBook set repertory=repertory+1 where isbn=_isbn;
update TBLend set whetherBorrow=0 where bookId=_Book_ID;
insert into HLend(readerId,isbn,bookId,borrowTime,returnTime) values(_ReaderID,_isbn,_Book_ID,_LTime,NOW());
END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `TBook_delete`;
DELIMITER ;;
CREATE TRIGGER `TBook_delete` AFTER DELETE ON `tbook` FOR EACH ROW begin
delete from `TBLend` where ISBN=old.ISBN;
-- delete from `TLend` where ISBN=old.ISBN;
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for `Book_Borrow`
-- ----------------------------
DROP PROCEDURE IF EXISTS `Book_Borrow`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `Book_Borrow`(in_ReaderID char(6),in_isbn char(18),in_BookID char(10),out_str char(30) )
begin
if not exists(select * from TReader where readerId=in_ReaderID) then
set out_str='该读者不存在';
end if;
if not exists(select * from TBook where isbn=in_isbn) then
set out_str='该图书不存在';
end if;
if (select borrowNum from TReader where readerId=in_ReaderID)=5 then
set out_str='读者借书量不能大于5';
end if;
if (select repertory from TBook where isbn=in_isbn)=0 then
set out_str='图书库存量为0';
end if;
if (in_isbn in (select isbn from TLend where readerId=in_ReaderID) ) then
set out_str='读者已经借过该书';
end if;
if exists (select * from TLend where bookId=in_BookID) then
set out_str='该图书已经被借出';
end if;
set autocommit=0;
start transaction;
insert into TLend(readerId,isbn,bookId,borrowTime)values(in_ReaderID,in_isbn,in_BookID,GETDATE());
update TReader set borrowNum=borrowNum+1 where readerId=in_ReaderID;
update TBook set repertory=repertory-1 where isbnin_isbn;
update TBLend set whetherBorrow=1 where bookId=in_BookID;
commit;
end
;;
DELIMITER ;
DROP PROCEDURE IF EXISTS `BookID_Generate`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `BookID_Generate`(in_ISBN char(18),_count int,firstID char(10))
begin
declare cnt int ;
set cnt=_count;
while cnt>0
do
insert into `TBLend` values(firstID,in_ISBN,0);
set firstID=firstID+1;
set cnt=cnt-1;
end while;
end
;;
DELIMITER ;
