图书馆借还书管理数据库

type

status

date

slug

summary

category

icon

password

任务1：图书馆借还书管理数据库的数据定义

1、创建和管理数据库

（1）创建图书馆借还书管理数据库librarydb。

（2）显示当前 MySQL 中的数据库。

（3）使数据库librarydb 成为当前默认的数据库。

2、在数据库 librarydb 中创建与管理数据表

（1）创建读者类别表readertype，读者类别表 readertype 的结构如表1所示，要求同时创建主键。

表1 读者类别表readertype

字段名称	数据类型	允许空值	键	默认值	说明
typeno	CHAR(1)	否	主键	ㅤ	类别编号
typename	VARCHAR(50)	是	ㅤ	ㅤ	类别名称
max_quantity	TINYINT	是	ㅤ	ㅤ	最大可借数量
max_days	TINYINT	是	ㅤ	ㅤ	最多可借天数

（2）创建读者表 reader，读者表reader 的结构如表2所示，只要求同时创建主键，不必创建外键。其中读者编号对应的是学生的学号或教师的工号。

表2 读者表 reader

字段名称	数据类型	允许空值	键	默认值	说明
rid	CHAR(12)	否	主键	ㅤ	读者编号
rname	VARCHAR(50)	是	ㅤ	ㅤ	读者姓名
sex	CHAR(2)	是	ㅤ	ㅤ	性别
typeno	CHAR(1)	否	外键 readertype(typeno)	2	读者类别
dept	VARCHAR(50)	是	ㅤ	ㅤ	所属院系
tel	VARCHAR(50)	是	ㅤ	ㅤ	联系电话

（3）创建图书表book，图书表book 的结构如表3所示，要求同时创建主键。

表3 图书表book

字段名称	数据类型	允许空值	键	默认值	说明
bid	CHAR(8)	否	主键	ㅤ	图书编号
bname	VARCHAR(50)	是	ㅤ	ㅤ	书名
author	VARCHAR(50)	是	ㅤ	ㅤ	作者
publisher	VARCHAR(50)	是	ㅤ	ㅤ	出版社
publishdate	date	是	ㅤ	ㅤ	出版日期
price	float	是	ㅤ	ㅤ	定价
total	int	是	ㅤ	ㅤ	库存数量
position	VARCHAR(50)	是	ㅤ	ㅤ	存放位置

（4）创建借还书表borrow，借还书表borow 的结构如表 4所示，只要求同时创建主键，不必创建外键。

表4 借还书表borrow

字段名称	数据类型	允许空值	键	默认值	说明
rid	VARCHAR(12)	否	主键，外键reader(rid)	ㅤ	读者编号
bid	CHAR(8)	否	主键，外键book(bid)	ㅤ	图书编号
borrowtime	date	是	ㅤ	ㅤ	借书日期
returntime	date	是	ㅤ	ㅤ	换书日期

3、指定数据完整性约束

（1）为读者类别表 readertype 指定检查约束，即指定最大可借数量的范围为0~30本。

（2）为读者表 reader 指定外键约東，使其读者类别 typeno字段的值必须是读者类别表 readertype 中类别编号 typeno字段已经存在的值，并且要求当修改读者类别表 readertype 中的 typeno 字段的值时，读者表 reader 中的 typeno字段的值也要随之变化。

（3）为读者表 reader 指定检查约束，即指定性别sex 字段的值只能是“男”或“女”。

（4）修改读者表 reader 的默认值约束，使读者类别 typeno 字段的默认值为“1”。

（5）为图书表 book 指定检查约束，即指定定价 price 字段的值必须大于0。

（6）为借还书表 borrow 指定外键约束，使其读者编号 rid 字段的值必须是读者表 reader 中读者编号 rid 字段已经存在的值，并且要求当删除或修改读者表reader 中的读者编号 rid 字段的值时，如果借还书表borrow 中该读者还有相关记录，则不得删除或修改。

（7）为借还书表 borrow 指定外键约束，使其图书编号 bid 字段的值必须是图书表 book 中图书编号 bid字段已经存在的值，并且要求当删除图书表 book中的某个图书编号bid 时，如果借还书表borrow 中该图书还有相关记录，则同时删除。

任务2：图书馆借还书管理数据库的数据操作

1、向图书馆借书管理数据库librarydb中的各张表插入数据

（1）将表5所示的数据插入读者类别表readtype中。

表5 读者类别表readtype中的数据

（2）将表6所示的数据插入读者表reader中。

表6 读者表reader中的数据

（3）将表7所示的数据插入图书表book中。

表7 图书表book中的数据

（4）将表8所示的数据插入借书还书表borrow中。

表8 借书还书表borrow中的数据

（5）有两位新入职的老师申请办理图书馆业务，教师的信息如表9所示，请将他们的信息插入读者表reader中。

表9 新入职教师信息

（6）图书馆进了一本新书，新书信息如表10所示，请将该图书信息插入图书部book中。

表10 新书信息

2、修改表中的数据

（1）读者10631218于2022年7月5日借阅了一本图书01351006，请在borrow 表中添加该读者对该书的借还书数据，并在book表中将该图书的库存数量减1（假定该图书库存充足可借）。

（2）读者 10631218归还了借阅的图书01351006，还书日期为系统当前日期，请修改borrow 表中相应的借还书数据，并在book表中将该图书的库存数量加1。

3、刪除表中的数据

（1）读者10701274孟凯彦离职了，请将其从读者表 reader 中删除。

（2）读者 10631218 李亚明也离职了，请将其从读者表 reader 中删除，并思考不能删除的原因（外键约束）。

（3）由于图书01351006全部损毁，不再提供借阅，请从图书表book 中将该图书删除。删除该图书后，请查看borrow 表中与该图书相关的借还书数据是否还存在，并思考原因（外键约束）。

任务3：图书馆借还书管理数据库的数据查询

1、查询“人民邮电出版社”的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。

2、查询女性读者的相关信息，结果包含读者姓名、性别和所属院系。

3、查询 2017 年至 2019 年出版的相关图书信息，结果包含图书编号、书名、作者、出版日期和定价。

4、查询图书定价打7折后的图书编号、书名和打折后价格。

5、查询所有馆存图书的总类别数量和总库存数量。

6、查询借阅的书名称中包含“数据”的读者的借阅信息，结果包含读者姓名和书名。

7、查询读者为“教师”的借阅信息，结果包含读者姓名、书名和借阅日期。

8、查询尚未还书的相关读者信息，结果包含读者编号、读者姓名、书名和借阅日期。

9、查询每本图书的借阅次数，将结果按照借阅次数降序排列，结果包含图书编号、书名、借阅次数、作者和出版社。

10、查询每个院系的借阅次数，将结果按照借阅次数的降序排序，结果包含院系名称和借阅次数。

11、查询借阅了书名为“大数据技术基础”的图书但尚未还书的相关读者信息，结果包含读者编号、读者姓名、书名和借阅日期。

12、查询定价高于平均定价的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。

13、查询从未被读者借阅的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。

任务4：图书馆借还书管理数据库的视图

1、创建一个视图，包含图书表book中的图书编号bid、书名bname、作者author、出版社publisher 和定价price，然后利用该视图查询出“人民邮电出版社”的图书信息。

2、创建一个视图，包含读者表reader 中的读者姓名rname、性别sex和读者类别表readertype 中的类别名称typename，然后利用该视图查询出读者类别是“学生”的信息。

3、创建一个视图，包含尚未还书的读者编号rid、读者姓名rname、书名bname 和借书日期 borrowtime，然后利用该视图查询出所有尚未还书的读者信息。

4、创建一个视图，包含读者姓名rname 和书名bname，然后利用该视图查询出姓“李”的借阅了图书的读者信息，以及借阅图书的书名中包含“数据”的信息。

5、创建一个视图，包含读者表reader中的读者编号rid、读者姓名rname、性别sex 和读者类别编号typeno，然后向该视图中插入一条数据（55559999，李晓明，男，1）。

任务5：图书馆借还书管理数据库的索引

1、创建索引

（1）在图书表book的书名bname字段的前6个字符上创建一个升序的普通索引I_bname。

（2）在图书表book的图书编号bid字段上创建一个唯一索引，在放置位置position字段上创建一个普通索引，在定价price字段上创建一个普通索引且按照降序排列。

（3）在借还书表borrow的读者编号rid字段和图书编号bid字段上创建一个组合索引。

2、删除索引

（1）使用ALTER TABLE语句删除图书表book的定价price字段上创建的索引。

（2）使用DROP INDEX语句删除在图书表 book 中创建的索引 I_bname。

任务6：图书馆借还书管理数据库的编程

1、将图书表 book 中存放位置 position 字段分成楼层和区，查询结果包含图书编号、书名、作者、出版社、楼层和区的信息。

2、查询读者类别为学生且借阅超期（超过 60 天）读者的信息，结果包含读者姓名、书名、借阅日期和超期天数。（提示：结果与系统的当前日期相关，在不同的日期下执行会得到不同的结果。）

3、创建带输入参数的存储过程，输入书名，查询借阅了该书读者的信息，结果包含读者编号、读者姓名、书名和借阅日期，然后调用该存储过程查询借阅了图书“数学分析习题演练”的读者的信息。

4、创建带输入参数的存储过程，输入姓名，显示是否有逾期未还图书，学生的借阅天数超60天、教师的借阅天数超90天则为逾期读者，结果包含读者姓名、读者类别、书名、借阅日期和超期天数，然后调用存储过程查询“宋洪博”的图书逾期信息。

任务7：图书馆借还书管理数据库的事务控制

1、修改读者类别编号事务控制。要求在存储过程中定义事务，通过输入参数old_no和new_no修改读者类别表readertype中学生的类别编号typeno，将其由原来的old_no修改为新的new_no。为了保证数据的一致性，还需要同步修改读者表reader中相关的读者类别。只有这些修改操作都成功，才提交，否则回滚。分别模拟正常和异常情况下的修改结果。