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

凛_Lin~~

已于 2024-02-27 08:27:37 修改

阅读量2.1w

点赞数 86

文章标签：数据库 mysql 课程设计

于 2023-01-11 16:22:06 首次发布

本文链接：https://blog.csdn.net/ass133755/article/details/128645404

版权

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

好像有读者说现在不要积分的资源也要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博客

安装好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)),

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;

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创建非聚集索引,其他索引由数据库引擎创建

完成后各表上的索引如下

各字段解析如下

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

各用户授予权限如下