MySQL 基础教程[7]

曾经终败给现在 2023-10-04 23:24 71阅读 0赞

#### 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 ;