第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;'