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

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)

);

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;

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 ('正常','丢失'));

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;

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

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

显示评注。