如果对你有帮助,可以给卑微的博主留个赞、关注、收藏 (不是)
(骗一下数据,说不定以后面试就过了,拜谢)
好像有读者说现在不要积分的资源也要vip才能下,如果下不了可以留邮箱到评论区或者私聊,我也把资源放到github了,地址如下:
https://github.com/goLSX/library_manager_system
如果对你有帮助不妨点个star 拜谢
数据库选用mysql 8.0.25 64位, 配合使用navicat
mysql可以在主页下载安装包,或者网上自己搜索
数据库安装配置可以参考网上的教程,有很多人都做了教程,例如这篇博文Mysql8.0.17压缩包安装——超详细简单教程_singular港的博客-CSDN博客
navicat的安装可参考这篇博文
Navicat16安装教程-图文详解_victor_王泽华的博客-CSDN博客
目录
1. 新建数据库 (模式)
2. 基本表的创建
3. 视图的创建
4. 完整性的创建
5. 触发器创建
6. 存储过程的创建
7. 索引创建
8. 数据库权限管理
9. 备份与恢复策略
安装好navicat和mysql后
先启动mysql服务,然后使用navicat进行连接,连接成功后点击新建查询
1. 新建数据库 (模式)
例如创建数据库名字叫library_db ,之后我们新建的表、存储过程等内容都属于这个数据库(模式)
create database library_db;use library_db;
2. 基本表的创建
readers表 CREATE TABLE readers ( reader_name varchar(20) , password varchar(35) , name varchar(10) , id_num varchar(20) , phone_num varchar(15) ); |
managers表 create table managers( manager_name varchar(20), password varchar(35), name varchar(10), id_num varchar(20), phone_num varchar(15), entry_time date, work_position varchar(20), state varchar(5) ); |
opinions表 create table opinions( opinion_rec_num int, reader_name varchar(20), opinion varchar(100), submit_time date, state varchar(10) ); |
books表 create table books( book_num int, book_name varchar(30), book_price float, book_state varchar(10), book_position varchar(30) ); |
opinion_results表 create table opinion_results( opinion_rec_num int, result varchar(100), transactor Varchar(20), finish_time date ); |
borrows表 create table borrows( borrow_rec_num int, reader_name varchar(20), book_num int, borrow_time date, transactor varchar(20), borrow_state varchar(10), borrow_duration smallint ); |
returns表 create table returns( borrow_rec_num int, return_time date, transactor varchar(20), fee float, kind varchar(5) ); |
3. 视图的创建
create view reader_message as select reader_name,name,id_num,(substring(now(),1,4)-substring(id_num,7,4))- (substring(id_num,11,4)-date_format(now(),’%m%d’)>0) as age,phone_num from readers; |
create view manager_message as select manager_name,name,id_num,(substring(now(),1,4)-substring(id_num,7,4))- (substring(id_num,11,4)-date_format(now(),’%m%d’)>0) as age, phone_num,entry_time,work_position,state from managers; |
create view opinion_result_message as select opinions.opinion_rec_num as opinion_rec_num, reader_name,opinion,submit_time,state,result,transactor,finish_time from (opinions left outer join opinion_results on opinions.opinion_rec_num = opinion_results.opinion_rec_num); |
create view return_message as select borrows.borrow_rec_num as borrow_rec_num,reader_name, borrows.book_num as book_num,book_name,borrow_time, borrows.transactor as borrow_transactor,borrow_state,return_time, borrow_duration,returns.transactor as return_transactor,fee,kind from borrows left outer join books on borrows.book_num = books.book_num left join returns on borrows.borrow_rec_num = returns.borrow_rec_num; |
create view book_message as select book_num,book_name,book_price,book_state,book_position from books; |
4. 完整性的创建
(主键、外键、check、非空、唯一、用户定义完整性、自增)
readers表 alter table readers modify reader_name varchar(20) primary key, modify password varchar(35) not null, modify name varchar(10) not null, modify id_num varchar(20) unique, add CONSTRAINT readers_check_id CHECK ((char_length(id_num) = 15) or (char_length(id_num) = 18)), addCONSTRAINT readers_check_phone CHECK ((phone_num is null) or (char_length(phone_num) = 11)); managers表 alter table managers modify manager_name varchar(20) primary key, modify password varchar(35) not null, modify name varchar(10) not null, modify id_num varchar(20) unique, modify entry_time date not null, modify work_position varchar(20) not null, modify state varchar(5) default ‘正常’, add CONSTRAINT managers_check_id CHECK ((char_length(id_num) = 15) or (char_length(id_num) = 18)), add CONSTRAINT managers_check_phone CHECK ((phone_num is null) or (char_length(phone_num) = 11)), add CONSTRAINT managers_check_state CHECK(state=’正常’ or state=’注销’); |
books表 alter table books modify book_num intprimary keyauto_increment, modify book_name varchar(30) not null, modify book_price float not null, modify book_state varchar(10) not nulldefault ‘不可借’, add CONSTRAINT books_check_price CHECK (book_price > 0), add CONSTRAINT books_check_state CHECK(book_state=’可借’ or book_state=’不可借’); |
opinions表 alter table opinions modify opinion_rec_num int primary key auto_increment, modify opinion varchar(100) not null, modify submit_time date not null, modify state varchar(10) default ‘待处理’, add CONSTRAINT opinions_fkey_reader foreign key(reader_name) references readers(reader_name), add CONSTRAINT opinions_check_state CHECK(state=’待处理’ or state=’处理完成’); |
opinion_results表 alter table opinion_results modify opinion_rec_num int primary key, modify result varchar(100) not null, modify finish_time date not null, add CONSTRAINT opinion_results_fkey_rec_num foreign key(opinion_rec_num) referencesopinions(opinion_rec_num), add CONSTRAINT opinion_results_fkey_transactor foreign key(transactor) references managers(manager_name); |
borrows表 alter table borrows modify borrow_rec_num int primary key auto_increment, modify borrow_time date not null, modify borrow_state varchar(10) not nulldefault ‘待还’, modify borrow_duration smallint not null default 30, add CONSTRAINT borrows_fkey_reader foreign key(reader_name) references readers(reader_name), add CONSTRAINT borrows_fkey_book foreign key(book_num) references books(book_num), add CONSTRAINT borrows_fkey_transactor foreign key(transactor) references managers(manager_name), add CONSTRAINT borrows_check_stateCHECK(borrow_state in (‘待还’,’已还’)); |
returns表 alter table returns modify borrow_rec_num int primary key , modify return_time date not null, modify kind varchar(5) default ‘正常’, add CONSTRAINT returns_fkey_rec_num foreign key(borrow_rec_num) references borrows(borrow_rec_num), add CONSTRAINT returns_fkey_transactor foreign key(transactor) references managers(manager_name), add CONSTRAINT returns_check_kind CHECK(kind in (‘正常’,’丢失’)); |
5. 触发器创建
create trigger insert_opinion_results after insert on opinion_results for each row update opinions set state=’处理完成’ where new.opinion_rec_num = opinions.opinion_rec_num; |
create trigger insert_borrows after insert on borrows for each row update books set book_state=’不可借’ where new.book_num = books.book_num; |
create trigger insert_books before insert on books for each row if new.book_position is not null then set new.book_state=’可借’; end if; |
create trigger update_books before update on books for each row if old.book_position is null and new.book_position is not null then set new.book_state=’可借’; end if; |
6. 存储过程的创建
create procedure insert_reader (in reader_name_in varchar(20), in password varchar(35) , in name varchar(10) , in id_num varchar(20) , in phone_num varchar(15)) begin insert into readers values(reader_name_in,password,name,id_num,phone_num); end |
create procedure insert_manager (in manager_name_in varchar(20), in password_in varchar(35) , in name_in varchar(10) , in id_num_in varchar(20) , in phone_num_in varchar(15), in entry_time_in date , in work_position_in varchar(20)) begin insert into managers (manager_name,password,name,id_num,phone_num,entry_time, work_position) values (manager_name_in,password_in,name_in,id_num_in,phone_num_in,entry_time_in,work_position_in); end |
create procedure insert_opinion (in reader_name_in varchar(20), in opinion_in varchar(100), in submit_time_in date) begin insert into opinions(reader_name,opinion,submit_time) values (reader_name_in,opinion_in,submit_time_in); end |
create procedure insert_book (in book_name_in varchar(30), in book_price_in float, in book_position_in varchar(30)) begin insert into book_message(book_name,book_price,book_position) values (book_name_in,book_price_in,book_position_in); end |
create procedure insert_opinion_result (in opinion_rec_num_in int, in result_in varchar(100), in transactor_in int, in finish_time_in date) begin insert into opinion_results(opinion_rec_num,result,transactor,finish_time) values (opinion_rec_num_in,result_in,transactor_in,finish_time_in); end |
create procedure insert_borrow (in reader_name_in varchar(20), in book_num_in int, in borrow_time_in date, in transactor_in varchar(20), out result varchar(10)) begin select exists(select * from reader_message where binaryreader_name = reader_name_in) into result; if result = ‘0’ then set result = ‘读者不存在’; select result; commit; end if; select exists(select * from book_message where book_num = book_num_in) into result; if result = ‘0’ then set result = ‘图书不存在’; select result; commit; end if; insert into borrows(reader_name,book_num,borrow_time,transactor) values (reader_name_in,book_num_in,borrow_time_in,transactor_in); select ‘成功’; end |
create procedure insert_return (in borrow_rec_num_in int, in return_time_in date, in transactor_in varchar(20), in kind_in varchar(5), out fee_out float) begin declare price float; DECLARE latetime SMALLINT ; declare fee_out float default null; declare book_num_temp int; declare borrow_time_temp date; declare borrow_duration_temp smallint; SELECT book_num ,borrow_time ,borrow_duration INTO book_num_temp,borrow_time_temp,borrow_duration_temp FROM borrows WHERE borrow_rec_num = borrow_rec_num_in ; SELECT book_price into price FROM books WHERE book_num = book_num_temp; SET latetime = datediff(return_time_in,borrow_time_temp)- borrow_duration_temp; UPDATE borrows SET borrow_state = ‘已还’ WHERE borrow_rec_num = borrow_rec_num_in; IF kind_in = ‘正常’ THEN UPDATE books SET book_state = ‘可借’ WHERE book_num = book_num_temp; IF latetime > 0 THEN SET fee_out = 0.1 * latetime; END IF; ELSE UPDATE books SET book_position = NULL WHERE book_num = book_num_temp; set fee_out = price; end if; insert into returns(borrow_rec_num,return_time,transactor,fee,kind) values (borrow_rec_num_in,return_time_in,transactor_in,fee_out,kind_in); select fee_out; end |
create procedure select_reader_message(in reader_name_in varchar(20)) begin select * from reader_messagewhere binary reader_name = reader_name_in; end |
create procedure select_manager_message(in manager_name_in varchar(20)) begin select * from manager_message where binary manager_name = manager_name_in; end |
CREATE procedureselect_book_message(in book_name_in varchar(30)) begin select * from book_message where book_name like CONCAT(‘%’,book_name_in,’%’); end |
CREATE PROCEDURE select_book_by_num(in book_num_in int) begin select * from book_message where book_num = book_num_in; end |
CREATE PROCEDURE select_opinion(in opinion_rec_num_in int) begin select opinion,state from opinion_result_message where opinion_rec_num = opinion_rec_num_in; end |
create procedure select_opinion_result_message(in reader_name_in varchar(20)) begin select * from opinion_result_message where binary reader_name = reader_name_in order by submit_time desc limit 10; end |
create procedure select_pending_opinion() begin select * from opinions where state = ‘待处理’ order by submit_time limit 10; end |
create procedure select_return_message(in reader_name_in varchar(20)) begin select * from return_message where binary reader_name = reader_name_in order by borrow_time desc limit 10; end |
CREATE PROCEDURE select_borrow_by_booknum(in book_num_in int) begin select * from return_message where borrow_state = ‘待还’ and book_num = book_num_in limit 1; end |
create procedure update_reader_message (in reader_name_in varchar(20), in name_in varchar(10), in id_num_in varchar(20), in phone_num_in varchar(15)) begin update reader_message set name = name_in , id_num = id_num_in , phone_num = phone_num_in where binary reader_name = reader_name_in; end |
create procedure update_reader_password (in password_in varchar(35)) begin update readers set password = password_in where binary reader_name = reader_name_in; end |
CREATE PROCEDURE update_manager_message(in manager_name_in varchar(20), in name_in varchar(10), in id_num_in varchar(20), in phone_num_in varchar(15)) begin update manager_message set name = name_in , id_num = id_num_in , phone_num = phone_num_in where binary manager_name = manager_name_in; end |
CREATE PROCEDURE update_manager_password (in manager_name_in varchar(20), in password_in varchar(35)) begin update managers set password = password_in wherebinarymanager_name = manager_name_in; end |
CREATE PROCEDURE update_manager_work (in manager_name_in varchar(20), in work_position_in varchar(20), in state_in varchar(5)) begin update managers set work_position = work_position_in ,state = state_in where binary manager_name = manager_name_in; end |
CREATE PROCEDURE update_book (in book_num_in int, in book_name_in varchar(30), in book_price_in float, in book_state_in varchar(10), in book_position_in varchar(30)) begin update book_message set book_name = book_name_in ,book_price = book_price_in, book_state = book_state_in,book_position = book_position_in where book_num = book_num_in; end |
CREATE PROCEDURE delete_book (in book_num_in int, out result varchar(10)) begin select exists(select * from book_message where book_num = book_num_in) into result; if result = ‘0’ then set result = ‘图书不存在’; select result; commit; end if; update book_message set book_state = ‘不可借’,book_position = null where book_num = book_num_in; select ‘成功’; end |
CREATE PROCEDURE delete_manager (in manager_name_in varchar(30)) begin update managers set state = ‘注销’ where binary manager_name = manager_name_in; end |
CREATE PROCEDURE extend_time (in book_num_in int , out result varchar(5)) begin declare latetime smallint; declare result varchar(5) default ‘成功’; declare borrow_rec_num_in int; set borrow_rec_num_in = (select borrow_rec_num from borrows where borrow_state = ‘待还’ and book_num = book_num_in); select CURDATE() – borrow_time – borrow_duration into latetime from borrows where borrow_rec_num = borrow_rec_num_in; if latetime > 0 then set result = ‘超期’; else update borrows set borrow_duration = borrow_duration + 15 where borrow_rec_num = borrow_rec_num_in; end if; select result; end |
create procedure check_reader (in reader_name_in varchar(20), in password_in varchar(35), out result varchar(5)) begin if password_in = (select password from readers where binary reader_name = reader_name_in) then set result = ‘正确’; else set result = ‘错误’; end if; select result; end |
create procedure check_manager (in manager_name_in varchar(20), in password_in varchar(35), out result varchar(5)) begin set result = ‘错误’; if password_in = (select password from managers where binary manager_name = manager_name_in) then set result = ‘正确’; if ‘注销’ = (select state from managers where binary manager_name = manager_name_in) then set result = ‘注销’; end if; end if; select result; end |
create procedure check_manager_sigh_up (in manager_name_in varchar(20), in id_num_in varchar(20) , out result varchar(10)) begin select exists(select * from managers wherebinarymanager_name = manager_name_in) into result; if result = ‘1’ then set result = ‘用户名已存在’; select result; commit; end if; select exists(select * from managers where id_num = id_num_in) into result; if result = ‘1’ then set result = ‘身份证号已被注册’; end if; if result = ‘0’ then select ‘OK’; else select result; end if; end |
create procedure check_reader_sigh_up (in reader_name_in varchar(20), in id_num_in varchar(20) , out result varchar(10)) begin select exists(select * from readers where binary reader_name = reader_name_in) into result; if result = ‘1’ then set result = ‘用户名已存在’; select result; commit; end if; select exists(select * from readers where id_num = id_num_in) into result; if result = ‘1’ then set result = ‘身份证号已被注册’; end if; if result = ‘0’ then select ‘OK’; else select result; end if; end |
7. 索引创建
mysql创建表时默认引擎InnoDB,如果表有主键,会建立主键聚集索引,如果一个字段有unique约束,会建立unique索引,如果一个字段有外键约束,会建立外键索引。在此基础上我们只多建立一个索引,books表的book_name字段建立非聚集索引。
create index book_name_index on books(book_name);
给books表的book_name创建非聚集索引,其他索引由数据库引擎创建
完成后各表上的索引如下
各字段解析如下
Table | 表示创建索引的数据表名。 |
Non_unique | 表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为 0。 |
Key_name | 表示索引的名称。 |
Seq_in_index | 表示该列在索引中的位置,如果索引是单列的,则该列的值为 1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序。 |
Column_name | 表示定义索引的列字段。 |
Collation | 表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。 |
Cardinality | 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。 |
Sub_part | 表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为 NULL。 |
Packed | 指示关键字如何被压缩。若没有被压缩,值为 NULL。 |
Null | 用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO。 |
Index_type | 显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。 |
Comment | 显示评注。 |
8. 数据库权限管理
使用严格的权限管理,只有允许的操作才能进行;有三类用户使用数据库,创建reader,manager,sys_manager。每个用户只授权某些函数的执行,不允许修改,也不允许权限传递,更不能直接接触到表,这样保证数据库的安全性和独立性(只通过函数接口访问)。
除了这三个用户外,实现时还使用一个checker用户,这个用户负责在我们没有登录成功前,连接数据库进行用户检查,检查通过后,建立用户连接进行数据库操作(reader/manager/sys_manager), 同时checker连接释放,另外读者用户注册也需要checker帮助传递读者信息到数据库完成注册,manager的注册有sys_manager负责验证。
各用户授予权限如下
(关于创建用户和授权可以参考以下代码
CREATE USER ‘checker’@’localhost’ IDENTIFIED BY ‘checkerpassword’;#其他类似
……………….
grant execute on procedure library_db.insert_opinion to ‘reader’@’localhost’;
……………………)
(也可以使用navicat图形化的方式创建,百度一下)
9. 备份与恢复策略(可不做,但是做了的话比较完善)
采取海量+增量的方式备份数据。 每周进行一次海量备份,两次海量备份之间采用增量备份方式。由于mysql不直接提供增量备份方式,故采用日志记录的方式来实现增量备份。
备份流程如下:
1.在配置文件开启二进制日志功能,设置日志地址(根据),服务id 如图
注:backup 文件夹需要自己创建、mysql-bin是日志文件的命名方式,不用创建,结合下图看看
2.先进行海量备份如
mysqldump -u root -p -R library_db> C:\Users\Administrator\Desktop\library_db.sql
或者使用navicat图形化进行备份
3.之后mysql会记录执行日志,保存到我们指定的地址,命名是mysql-bin.00000x 这样我们就可以通过海量备份加日志的方式恢复数据。(从mysql-bin.000001开始计数)
示例恢复如下:
1.先恢复海量备份
mysql -u root -p < C:\Users\Administrator\Desktop\library_db.sql
2.再利用日志逐个循环恢复增量备份 (到日志目录文件夹下执行恢复命令)
mysqlbinlog –no-defaults mysql-bin.000001 | mysql -uroot -p+密码
(如mysqlbinlog –no-defaults mysql-bin.000001 | mysql -uroot -p123456)
mysqlbinlog –no-defaults mysql-bin.000002| mysql -uroot -p+密码
………
实际运行时备份方式采用navicat创建一个批处理作业,指定执行全量备份,然后重置日志,(从000001开始计数),执行时间为每周日凌晨2点 ,设置如下图
注:每次进行恢复后,需要重新对存储过程进行授权
至此数据库实现完成。