MySQL 基础教程[7]
- 问题1
- 问题1代码
- 问题2
- 问题2代码
- 本系列MySQL 基础教程通过“问题-代码”的方式介绍各类方法，每篇设置2个MySQL综合问题，并给出解决方案。
问题1
给定学校图书借阅数据库db_book中包含图书分类表tb_class、图书表tb_book、读者表tb_reader和借阅表tb_borrow。 tb_class包含的字段有classNo (分类号) 、className（分类名称) ; tb_book包含的字段有bookNo（图书编号) 、 classNo (分类号) 、bookName（图书名称) 、 authorName（作者姓名)、
pubName（出版书名称) 、ISBN (出版号) 、 price（单价) 、pubDate (出版时间) 、 shopDate（入库时间) 、
shopNum (入库数量) ; tb_reader包含的字段有readerNo (读者编号) 、readerName (读者姓名) 、 sex (性别) 、 age (年龄) 、 department (所在院系)； tb_borrow包含的字段有readerNo（读者编号）、bookNo（图书编号)、 borrowDate (借阅日期) 、 shouldDate（应还日期) 、returnDate（归还日期）。 请按要求完成以丁操作。
- 用 SQL语句删悇图书表中的字段: 入库时间。
- 用 SQL语句将姓多为"王依然”的读者性别改为“女"。设读者没有重名。
- 用SQL语句查询惜阅日期在2014-01-01以来的读者姓多、图书编号。
- 用SQL语句建立一个读者惜书总数的视图v_borrow, 要求字段显示为 “读者编号”和“借书总册数”。
- 在文件夹下有一个使用 SELECT … INTO OUTFILE 'filename’语句备份的各称为 backup.txt的文件, 所有可选参 数均使用默认值。请将该备份文件中的数据导入数据表tb_reader1中。
问题1代码
#【1】
alter table tb_book drop column shopDate;
#【2】
update tb_reader set sex="女" where readerName="王依然";
#【3】
select readerName, bookNo from tb_reader r, tb_borrow b where r.readerNo=b.readerNo and r.readerNo in (select readerNo from tb_borrow where borrowDate > '2014-01-01')
#【4】
create view v_borrow("读者编号","借书总册数") as select readerNO, count(readerNO) from tb_borrow group by readerNo;
#【5】
load data infile "backup.txt" into table tb_reader;问题2
请按要求完成以下操作。
- 创建一个名称为sp_book的存储过程, 要求根据输入的院系名称统计该院系读者的人均借阅图书的册数, 并返回 人均借阅图书的册数。
- 创建一个名称为tr book的触发器, 要求读者还书更新归还日期前判断是否超期, 对于已超期的读者, 需要将其读 者号、读者姓名、超期天数和超期次数等信息记录到超期记录表tb_expire中。
问题2代码
#【1】
DELIMITER $$
CREATE PROCEDURE sp_book( in dept VARCHAR(20), out bookAvg FLOAT)
BEGIN
DECLARE readerNum FLOAT;
DECLARE borrowNum FLOAT;
SELECT COUNT(*) INTO readerNum FROM tb_reader WHERE department=dept;
SELECT COUNT(*) INTO borrowNum FROM tb_borrow, tb_reader
WHERE tb_reader.readerNo=tb_borrow.readerNo AND department=dept GROUP BY dept;
IF (readerNum>0) THEN
SET bookAvg =borrowNum/readerNum;
ELSE
SET bookAvg =0;
END IF;
END $$
DELIMITER ;
#【2】
DELIMITER //
CREATE TRIGGER tr_book BEFORE update
ON tb_borrow FOR EACH ROW
BEGIN
DECLARE days INT;DECLARE times INT;DECLARE name VARCHAR(20);
SELECT datediff(New.returnDate,shouldDate) INTO days FROM tb_borrow WHERE readerNo=NEW.readerNo AND bookNo=NEW.bookNo;
IF ( days>0)THEN
BEGIN
SELECT COUNT(*) INTO times FROM tb_expire WHERE readerNo=NEW.readerNo;
IF (times!=0 ) THEN
UPDATE tb_expire SET expireDays=expireDays+days, expireTimes=expireTimes+times WHERE readerNo=NEW.readerNo;
ELSE
BEGIN
SELECT readerName INTO name FROM tb_reader WHERE readerNo=NEW.readerNo;
INSERT INTO tb_expire VALUES(NEW.readerNo, name, days, 1);
END;
END IF;
END;
END IF;
END //
DELIMITER ;
