用mysql写存储过程与触发器

Trash mysql

发布时间 : 2018-04-26 13:32

字数:721 阅读 :

PB15121708 方林涛
建表
插入测试数据
查询测试
存储过程
1. 测试
触发器
插入测试数据前book，borrow表
插入测试数据后borrow，book表

#数据库系统与应用实验1 Report

PB15121708 方林涛

实验题目

设某图书馆数据库包含下面的基本表：
 Book（ID: char(8)，name:varchar2(10)，author:varchar2(10)，price:float， status: int）
图书号 ID 为主键，书名不能为空。状态（status）为 1 表示书被借出，0 表示在馆，
默认值为 0。
 Reader（ID:char(8)，name:varchar2(10)，age:int，address:varchar2(20)）读者号 ID
为主键。
 Borrow（book_ID:char(8)，Reader_ID:char(8)，Brrrow_Date:date， Return_Date:date）
其中：还期 Return_Date 为 NULL 表示该书未还。主键为（图书号，读者号），图
书号为外键，引用图书表的图书号，读者号为外键，引用读者表的读者号

1、创建上述基本表，并插入部分测试数据；
2、设计例子，验证实体完整性、参照完整性、用户自定义完整性；
3、用 SQL 语言完成下面小题，并测试运行结果：
4、设计存储过程，实现对 Book 表的 ID 的修改。
5、设计触发器，实现：当一本书被借出时，自动将 Book 表中相应图书的 status 修改为1；当某本书被归还时，自动将 status 改为 0。

实验环境

Mysql  5.7.20  
MacOSX

建表

1.1

1.2

1.3

插入测试数据

查询测试

1	select id,address from reader where name = 'Rose';

1
2
3

select book.name,datediff(return_date,borror_date) as Days 
from borrow,reader,book where book_id = book.id and reader_id = reader.id 
and reader.name = 'Rose';

mysql> select reader.name from reader
    -> where
    -> reader.id not in
    -> (
    -> select distinct reader_id from borrow
    -> );

1	select name,price from book where author = 'Ullman';

select book.id,book.name 
from book,reader,borrow 
where
reader.id = borrow.reader_id 
and book.id = borrow.book_id 
and reader.name = 'Lilin' 
and datediff(curdate(),return_date)<0;

select reader.name 
from reader,borrow 
where reader.id = borrow.reader_id 
group by reader.id 
having count(*)>=3;

select reader.name,reader.id 
from reader,borrow
 where borrow.reader_id = reader.id 
 and book_id not  in
(select distinct book_id 
from borrow,reader 
where borrow.reader_id = reader.id 
and reader.name = 'Lilin')

1	select name,id from book where name like '%Oracle%';

1 2	select reader_id,count(*) from borrow_view V where datediff(curdate(),borrow_date)<=365 group by reader_id;

存储过程

DELIMITER $
create procedure modify_bookid(
in old_id  char, in new_id char)
begin 
set foreign_key_checks = 0;
update book set id = new_id where id = old_id; 
update borrow set book_id = new_id where book_id = old_id;  
set foreign_key_checks = 1;
end$

测试

call modify_bookid('0','9')$

触发器

  CREATE PROCEDURE 存储过程名称(
            IN/OUT 参数 数据类型 ...)
        BEGIN

        END

  create procedure modify_bookid(
        in old_id  char, in new_id char)
        begin 
            set foreign_key_checks = 0;
            update book set id = new_id where id = old_id; 
                update borrow set book_id = new_id where book_id =                         old_id;  
            set foreign_key_checks = 1;
        end$





create trigger borrow_insert after insert  on borrow for each row
begin
if datediff(curdate(),new.return_date)>=0 then 
    update book set status = 0 where id = new.book_id;
else                                                  
    update book set status = 1 where id = new.book_id;
end if;
end$