第4章 课后作业

第3题

set autocommit=0;

BEGIN;

INSERT INTO borrow 
VALUES(
(SELECT rid FROM reader WHERE rName = '张无忌'),
(SELECT bid FROM book WHERE bName = '红楼梦'),
DATE(NOW()),
'2018-10-10',
NULL);

UPDATE reader SET lendNum = lendNum+1 WHERE rName = '张无忌';

UPDATE book SET bCount = bCount - 1 WHERE bName = '红楼梦';

COMMIT;

第4题

BEGIN;

INSERT penalty VALUES(
    (SELECT rid FROM reader WHERE rName = '刘冰冰'),
    (SELECT bid FROM book WHERE bName = '西游记'),
    DATE(NOW()),
    (SELECT pNo FROM type WHERE pType = '延期'),
    '4.6'
);

UPDATE borrow 
SET returnDate = DATE(NOW()) 
WHERE rid = (SELECT rid FROM reader WHERE rName = '刘冰冰')
AND nif = (SELECT bid FROM book WHERE bName = '西游记');

UPDATE reader
SET lendNum=lendNum - 1
WHERE rName = '刘冰冰';

UPDATE book
SET bCount = bCount + 1
WHERE bName = '西游记';

COMMIT;

第5题

CREATE INDEX bName_index ON book(bName);

CREATE VIEW v_master 
    AS 
SELECT (SELECT bName FROM book WHERE book.bid = borrow.nif)AS '图书名称',
willDate AS '应归还日期',
(SELECT rName FROM reader WHERE reader.rid = borrow.rid) AS '读者姓名',
returnDate AS '实际归还日期'
FROM borrow WHERE willDate<DATE(NOW());

SELECT * FROM v_master;

CREATE VIEW v_reader
    AS
SELECT bName AS '图书名称',bCount as '馆存数',(bCount-(SELECT COUNT(nif) FROM borrow WHERE borrow.nif=book.bid))AS '可借阅数量' FROM book

SELECT * FROM v_reader

第6题

mysqldump -u root -p library > D:\library_20180807.sql

CREATE DATABASE libraryDB;
USE libraryDB;
source D:\library_20180807.sql;

SELECT * FROM reader INTO OUTFILE 'd:/reader.txt';

SELECT * FROM book INTO OUTFILE 'd:/book.txt;'