mysql使用事务实现图书馆借书操作

快来打我* 2022-03-22 04:52 577阅读 0赞

### 文章目录 ###

*   *   *  一、实例
         *  二、 相关表
         *   *   *  1、创建表
                 *  2、插入表记录
         *  三、事务处理

### 一、实例 ###

使用事务，完成图书馆借阅图书的操作。  
1、向借阅信息表插入借阅记录  
2、更新图书信息表的库存  
3、更新读者信息表的余额  
当库存为0或者余额小于100时，不能借书  
（事务要求把借阅图书的相关操作看做一个整体，要么全部执行，要么全部不执行）

身份证号为‘330100199201231107’的读者，今天借了一本编号为’10002‘的图书,请完成图书借阅操作。

### 二、 相关表 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hqdTIy_size_16_color_FFFFFF_t_70]  
**需求：**  
1、要求一个读者借书时间为一个月  
2、读者每借一本书，余额就扣除这本书价格的3%。

##### 1、创建表 #####

-- 创建数据库
    create database book;
    use book;
    -- 1 图书类别表
    create table category(
    id int primary key auto_increment,
    name varchar(20) not null,
    parent_id int not null
    );
    -- 2 图书信息表
    create table bookinfo(
    book_id int primary key auto_increment,
    category_id int,
    book_name varchar(20) not null,
    auther varchar(20) not null,
    price float(6,2) not null,
    press varchar(20) not null,
    public_date date not null,
    store int not null,
    constraint fk_cate foreign key(category_id) references category(id)
    )auto_increment=10000;
    -- 3读者信息表
    create table reader(
    card_id char(18) primary key,
    name varchar(20) not null,
    sex enum('男','女','保密') default'保密',
    age tinyint not null,
    phone_number char(11) not null,
    balance decimal(6,2) default 200
    );
    -- 4借阅信息表
    create table borrowinfo(
    book_id int,
    card_id char(18),
    borrow_date date not null,
    return_date date,
    is_return enum('是','否'),
    constraint pk_bookid_cardid primary key(book_id,card_id),
    constraint fk_bookid foreign key(book_id) references bookinfo(book_id),
    constraint fk_cardid foreign key(card_id) references reader(card_id)
    );

##### 2、插入表记录 #####

-- 1 插入类别记录
    insert into category(name,parent_id) values('计算机',0),('历史',0),('英语',0),('科学',0),('数学',0),('艺术',0);

![在这里插入图片描述][20190126013637451.]

-- 2 插入图书记录
    insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(1,'Android权威指南','王贺',58.8,'人民邮电出版社','2016-02-01',3);
    insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(1,'linux私房菜','鸟哥',76.5,'人民邮电出版社','2015-09-21',10);
    insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(1,'Java编程思想','布鲁斯',110,'机械工业出版社','2008-05-30',12);
    insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(1,'JavaScript DOM 编程艺术','吉米科',49.0,'人民邮电出版社','2016-02-01',4);
    insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(3,'英语常用口语100句','李希',28,'南方工业出版社','2002-04-25',6);
    insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(5,'高等数学一','王志胜',45,'吉林工业出版社','2010-04-25',6);
    insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(5,'高等数学2','王志胜',48,'吉林工业出版社','2010-04-25',6);
    insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(2,'三国鼎立','吴风',55,'人民出版社','2010-09-18',6);
    insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(2,'且看盛唐','文旭烟',32,'人民出版社','2013-11-29',6);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hqdTIy_size_16_color_FFFFFF_t_70 1]

-- 3 插入读者记录
    insert into reader values('330100199201231104','小明','男',29,'15787223423',300);
    insert into reader values('330100199201231105','小红','女',21,'15787223424',220);
    insert into reader values('330100199201231106','王迷','男',19,'15787223425',310);
    insert into reader values('330100199201231107','李思','女',24,'15787223426',230);
    insert into reader values('330100199201231108','小王','女',32,'15787223427',240);
    insert into reader values('330100199201231109','王霞','女',26,'15787223428',300);

![在这里插入图片描述][20190126013715363.]

### 三、事务处理 ###

delimiter//
    create procedure borrowpro(in myid char(18),in mybookid int)
    begin
    
    declare mystore int;
    declare mybalance decimal(6,2);
    declare myprice float(6,2);
    select store into mystore from bookinfo where book_id=mybookid;
    select balance into mybalance from reader where card_id=myid;
    select price into myprice from bookinfo where book_id=mybookid;
    
    set autocommit=0; #禁止自动提交
    -- 事务
    insert into borrowinfo values(mybookid,myid,curdate(),date_add(curdate(),interval 1 month),'否');
    update bookinfo set store=store-1 where book_id=mybookid;
    update reader set balance=balance-myprice*0.03 where card_id=myid;
    
    -- 不满足条件，回滚；否则提交事务
    if mystore=0 or mybalance<100 then
    	rollback; 
    else
    	commit; 
    end if;
    
    end//
    delimiter;
    
    call borrowpro('330100199201231107',10002);

运行结果：  
执行成功，借阅表插入记录，库存和余额都更新成功。  
![在这里插入图片描述][20190126022746863.]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hqdTIy_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hqdTIy_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hqdTIy_size_16_color_FFFFFF_t_70]: /images/20220322/697c42e7d0e14683b9800302a326cb88.png
[20190126013637451.]: /images/20220322/62c02fc1cc8b4181ba85e41d5afe2651.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hqdTIy_size_16_color_FFFFFF_t_70 1]: /images/20220322/76aa77c2785e45d0a976f6fea8d849b8.png
[20190126013715363.]: /images/20220322/ef9ea02913094eec83de006fc5b512c1.png
[20190126022746863.]: /images/20220322/5a17fa71027e4cceba14cca99919db50.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hqdTIy_size_16_color_FFFFFF_t_70 2]: /images/20220322/af675aca020a48f09dda08e54245e3b8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hqdTIy_size_16_color_FFFFFF_t_70 3]: /images/20220322/65d83336c31a414387a387bdccf1dc95.png