如果对你有帮助，可以给卑微的博主留个赞、关注、收藏 (不是)
(骗一下数据，说不定以后面试就过了，拜谢)
数据库选用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;
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负责验证。
各用户授予权限如下
（关于创建用户和授权可以参考以下代码
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点 ，设置如下图
注:每次进行恢复后，需要重新对存储过程进行授权
至此数据库实现完成。
版权归原作者 凛_Lin~~ 所有， 如有侵权，请联系我们删除。