基于MySQL+JSP开发的书籍管理系统，小型图书馆

create database if not exists library; use library; drop database library drop table if exists book; #book_info 图书信息book create table book ( book_id varchar(30) NOT NULL, #图书ID name varchar(200) NOT NULL, #图书名 author varchar(200) NOT NULL, #作者 publish varchar(40) NOT NULL, #出版商 ISBN varchar(80) NOT NULL, #图书编码 price float NOT NULL, #价格 number int(10) unsigned, #图书总数 PRIMARY KEY (book_id) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; insert into book values('2012112','Java程序设计','java','人民邮电','124224','36.5','3'); insert into book values('2012113','C语言程序设计','C','北京大学','325231','38.6','4'); insert into book values('2012114','软件项目管理','soft','清华大学','543646','36.5','3'); insert into book values('2012115','大学语文','chinese','高等教育','124214','39.5','2'); insert into book values('2012116','高等数学','math','高等教育','354353','42.5','3'); insert into book values('2012117','大学英语','english','新东方','124244','29.5','3'); insert into book values('2012118','PHP+MySQL程序设计','PHP','清华大学','124924','36.5','4'); insert into book values('2012119','python学习手册','python','北邮','124274','36.5','5'); insert into book values('2012130','程序设计语言原理','program','北京大学','123424','36.5','4'); insert into book values('2012131','计算机网络','network','机械工业','124724','36.5','2'); insert into book values('2012132','计算机组成原理','computer','电子工业','123424','36.5','2'); drop table if exists book_state; #图书状态 create table book_state ( book_id varchar(30) NOT NULL, #图书状态 number int(10) unsigned NOT NULL default '0', #图书总数 leftnum int(10) unsigned NOT NULL default '0', #图书剩余数 PRIMARY KEY (book_id), FOREIGN KEY (book_id) REFERENCES book(book_id) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; insert into book_state values('2012112','3','3'); insert into book_state values('2012113','4','4'); insert into book_state values('2012114','3','3'); insert into book_state values('2012115','2','2'); insert into book_state values('2012116','3','3'); insert into book_state values('2012117','3','3'); insert into book_state values('2012118','4','4'); insert into book_state values('2012119','5','5'); insert into book_state values('2012130','4','4'); insert into book_state values('2012131','2','2'); insert into book_state values('2012132','2','2'); drop table if exists admin; #admin 用户表 create table admin ( user_id int(10) NOT NULL, #读者ID password varchar(25) NOT NULL, #密码 count int AUTO_INCREMENT, PRIMARY KEY (count) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; ALTER TABLE admin AUTO_INCREMENT=0; insert into admin(user_id,password) values(10000,'admin'); drop table if exists borrower; #borrower_info读者信息 borrower create table borrower ( id varchar(15) NOT NULL, #读者ID name varchar(12) NOT NULL, #读者姓名 gender char(1) NOT NULL, #读者性别 address varchar(40) NOT NULL, #地址 telcode char(12) NOT NULL, #电话 zipcode char(6) NOT NULL default '100000', #邮编 PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; drop table if exists lend_list; #图书借出表 create table lend_list ( book_id varchar(30) NOT NULL, #图书ID borrower_id varchar(15) NOT NULL, #读者ID lend_date date NOT NULL, #借出时间 back_date date default NULL, #归还时间 PRIMARY KEY (book_id,borrower_id,back_date), FOREIGN KEY (book_id) REFERENCES book(book_id), FOREIGN KEY (borrower_id) REFERENCES borrower(id) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; alter table lend_list modify back_date datetime;