数据库编程第四章课后作业

原创于 2018-08-07 20:43:15 发布 · 3.2k 阅读

CC 4.0 BY-SA版权

本文介绍了一个图书管理系统的数据库操作流程，包括更新书籍数量、记录罚款信息、创建视图以简化查询过程、备份与恢复数据库、以及导出文本信息等实用功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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