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（归还日期）。 请按要求完成以丁操作。

  1. 用 SQL语句删悇图书表中的字段: 入库时间。
  2. 用 SQL语句将姓多为"王依然”的读者性别改为“女"。设读者没有重名。
  3. 用SQL语句查询惜阅日期在2014-01-01以来的读者姓多、图书编号。
  4. 用SQL语句建立一个读者惜书总数的视图v_borrow, 要求字段显示为 “读者编号”和“借书总册数”。
  5. 在文件夹下有一个使用 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

请按要求完成以下操作。

  1. 创建一个名称为sp_book的存储过程, 要求根据输入的院系名称统计该院系读者的人均借阅图书的册数, 并返回 人均借阅图书的册数。
  2. 创建一个名称为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 ;