BEGIN ;
SET autocommit=1;
#第三题
UPDATE book SET bCount=bCount-1
WHERE bName = "红楼梦";
UPDATE reader SET lendNum = lendNum+1
WHERE rName = "张无忌";
COMMIT
#第四题
INSERT INTO penalty(rid,bid,pData,pType,amount)VALUES(6,1,NOW(),1,4.6);
UPDATE boorrow SET returnData=NOW()
WHERE rid=6;
UPDATE reader SET lendNum=lendNum-1
WHERE rName="刘冰冰";
UPDATE book SET bCount=bCount+1
WHERE bName="西游记";
COMMIT;
ROLLBACK;
#第五题
CREATE VIEW Administrators AS
SELECT b.bName AS 图书名称,w.willData AS 归还日期,r.rName AS 读者姓名 FROM book b
INNER JOIN boorrow w ON w.nif = b.bid
INNER JOIN reader r ON r.rid= w.rid
SELECT * FROM Administrators
CREATE VIEW Readers AS
SELECT b.bName AS 图书名称,b.bCount AS 馆存量,(b.bCount-(SELECT COUNT(nif) FROM boorrow)) AS 可借阅的数量 FROM book b
CREATE INDEX Title_Index
ON book (bName)
SELECT * FROM Readers
#第六题
#备份数据
mysqldump -u root -p test > C:\backup\books.sql
enter PASSWORD:1234
#恢复数据
mysql -u root -p abc <C:\backup\books.sql
#导出文本信息
SELECT * FROM reader INTO OUTFILE 'D:\tb_reader.txt'
SELECT * FROM book INTO OUTFILE 'D:\tb_book.txt';