Java数据库编程技术 第四章习题
发布时间
阅读量：
阅读量
3 为读者‘张无忌’办理借阅《红楼梦》书籍的手续
BEGIN;
INSERT INTO book()
VALUES('6','红楼梦','author6','pubComp6','2018-08-02','100','40');
INSERT INTO reader()
VALUES('7','张无忌','0','CHINA');
INSERT INTO borrow()
VALUES('7','6','2018-08-02 16:50:54','2018-08-05 16:57:04',NULL);
UPDATE `book` SET `bCount`=`bCount`-1
WHERE `bName`='红楼梦';
UPDATE `reader` set `lendNum`=`lendNum`+1
WHERE `rName`='张无忌';
COMMIT;
4 读者 ‘刘冰冰’缴纳罚金归还图书手续
BEGIN;
INSERT INTO book()
VALUES('7','西游记','author7','pubComp7','2018-08-02','100','30');
INSERT INTO reader()
VALUES('8','刘冰冰','1','CHINA');
INSERT INTO borrow()
VALUES('8','7','2018-08-02 16:50:54','2018-08-05 16:50:54',NULL);
INSERT INTO penalty()
VALUES('8','7','2018-08-06 12:00:00','1','4.6元');
UPDATE `borrow` SET `returnDate`=NOW()
WHERE `rid`='8';
UPDATE `reader` SET `lendNum`=`lendNum`-1
WHERE `rName`='刘冰冰';
UPDATE `book` SET `bCount`=`bCount`+1
WHERE `bName`='西游记';
COMMIT;
5 查询视图
CREATE INDEX index_bName
on book(`bName`);
CREATE VIEW VIEW_admin
AS
SELECT book.bName,borrow.willDate,reader.rName
FROM borrow
INNER JOIN book ON book.bid=borrow.nif
INNER JOIN reader ON reader.rid=borrow.rid
WHERE willDate<DATE(NOW());
SELECT * FROM VIEW_admin
CREATE VIEW VIEW_reader
AS
SELECT book.bName,book.bCount,(book.bCount-COUNT(lendNum)) AS '可借阅数量' FROM borrow
INNER JOIN book ON book.bid=borrow.nif
INNER JOIN reader ON reader.rid=borrow.rid
GROUP BY book.bid
SELECT * FROM VIEW_reader
6 数据库备份，恢复何数据导出
mysqldump -u root -p libarary>C:\数据库备份\libarary_20180807.sql
CREATE DATABASE libarary;
USE libarary;
source C:\数据库备份\libarary_20180807.sql;
SELECT * FROM reader INTO OUTFILE 'C:\数据库备份\reader.txt';
SELECT * FROM book INTO OUTFILE 'C:\数据库备份\book.txt';
全部评论 (0)
还没有任何评论哟~