数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql)
作者:mmseoamin日期:2023-12-13

如果对你有帮助,可以给卑微的博主留个赞、关注、收藏   (不是) 

(骗一下数据,说不定以后面试就过了,拜谢)

好像有读者说现在不要积分的资源也要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进行连接,连接成功后点击新建查询

数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第1张

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)),

add CONSTRAINT 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 int primary key auto_increment,

modify book_name varchar(30) not null,

modify book_price float not null,

modify book_state varchar(10) not null default '不可借',

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)

references opinions(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 null default '待还',

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_state CHECK(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 binary reader_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_message where 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 procedure select_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 where binary manager_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 where binary manager_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创建非聚集索引,其他索引由数据库引擎创建

                                完成后各表上的索引如下

数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第2张

各字段解析如下

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负责验证。

                                                        各用户授予权限如下

数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第3张

 数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第4张

数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第5张数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第6张 

(关于创建用户和授权可以参考以下代码

CREATE USER 'checker'@'localhost' IDENTIFIED BY 'checkerpassword';  #其他类似

...................

grant execute on procedure library_db.insert_opinion to 'reader'@'localhost';

........................)

(也可以使用navicat图形化的方式创建,百度一下)

9. 备份与恢复策略(可不做,但是做了的话比较完善)

采取海量+增量的方式备份数据。 每周进行一次海量备份,两次海量备份之间采用增量备份方式。由于mysql不直接提供增量备份方式,故采用日志记录的方式来实现增量备份。

备份流程如下:

1.在配置文件开启二进制日志功能,设置日志地址(根据),服务id  如图

       数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第7张

 注:backup 文件夹需要自己创建、mysql-bin是日志文件的命名方式,不用创建,结合下图看看

数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第8张

 数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第9张

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点 ,设置如下图

数据库课程设计-图书馆管理系统(2.数据库实现-基于mysql),第10张

注:每次进行恢复后,需要重新对存储过程进行授权

至此数据库实现完成。