/*創(chuàng)建書的類型名稱與代號(hào)的對(duì)應(yīng)表*/
drop table wantlend;
drop table yjbook;
drop table book;
drop table kinds;
create table kinds(kind integer primary key,
                   name char(26));
insert into kinds
values(1,'經(jīng)典');
insert into kinds
values(2,'哲學(xué)');
insert into kinds
values(3,'法律');
insert into kinds
values(4,'軍事');
insert into kinds
values(5,'經(jīng)濟(jì)');
insert into kinds
values(7,'文化教育');
insert into kinds
values(8,'語言文字');
insert into kinds
values(9,'文學(xué)');
insert into kinds
values(10,'美術(shù)設(shè)計(jì)');
insert into kinds
values(11,'歷史地理');
insert into kinds
values(12,'工商管理');
insert into kinds
values(13,'小兒讀物');
insert into kinds
values(14,'視聽藝術(shù)');
insert into kinds
values(15,'自然科學(xué)');
insert into kinds
values(16,'生活?yuàn)蕵?#039;);
insert into kinds
values(17,'趣味閱讀');
insert into kinds
values(18,'醫(yī)學(xué)');
insert into kinds
values(19,'農(nóng)林牧漁');
insert into kinds
values(20,'工業(yè)技術(shù)');
insert into kinds
values(21,'計(jì)算機(jī)');
insert into kinds
values(22,'圖片掛歷');
insert into kinds
values(23,'進(jìn)品圖書');
insert into kinds
values(24,'綜合');
/*書本信息*/

create table book(
  book_NO char(30)  primary key,/* 圖書編號(hào)*/
  book_name char(30) not null,
  book_zname char(20),  
  book_publish char(26),
  enter_num integer, 
  book_jg  money,
  enter_date datetime,
  Book_leave integer,
  kind integer,
foreign key (kind) references kinds(kind)); 
insert into book
values('7-900084-17-7 9787900084170','photoshop6.0','斐紅義','中科多脈管體電子',5,27,'2003-10-1',5,10);
insert into book
values('7-900084-17-7 9787900084678','pb8.0','power','石油大學(xué)pb',5,27,'2003-10-1',5,5);
insert into book
values('7-900084-17-7 9787900085688','asp','asp','石油大學(xué)asp',5,29,'2003-10-1',5,11);
insert into book
values('7-900084-17-7 9757900184678','java','power','石油大學(xué)java',5,28,'2003-10-1',5,1);
insert into book
values('7-900084-17-7 9787901084678','mx','power','石油大學(xué)mx',5,32,'2003-10-1',5,11);
insert into book
values('7-900084-17-7 9787910084678','autoware','ware','石油大學(xué)ware',5,26,'2003-10-1',5,7);
insert into book
values('7-900084-17-7 9788900084678','english','english','石油大學(xué)english',5,25,'2003-10-1',5,9);
insert into book
values('7-922084-17-7 9787900084678','sd','power','石油大學(xué)pb',5,27,'2003-10-1',5,5);
insert into book
values('7-922084-17-7 9787900085688','addd','asp','石油大學(xué)asp',5,29,'2003-10-1',5,11);
insert into book
values('7-922084-17-7 9757900184678','jggg','power','石油大學(xué)java',5,28,'2003-10-1',5,8);
insert into book
values('7-922084-17-7 9787901084678','mmm','power','石油大學(xué)mx',5,32,'2003-10-1',5,11);
insert into book
values('7-9022084-17-7 9787910084678','pkh','ware','石油大學(xué)ware',5,26,'2003-10-1',5,7);
insert into book
values('7-902284-17-7 9788900084678','yhh','en3glish','石油大學(xué)english',5,25,'2003-10-1',5,1);
insert into book
values('7-111084-17-7 9787900084678','sd','po3wer','石油大學(xué)pb',5,27,'2003-10-1',5,5);
insert into book
values('7-111084-17-7 9787900085688','addd','as3p','石油大學(xué)asp',5,29,'2003-10-1',5,11);
insert into book
values('7-111084-17-7 9757900184678','jggg','p3ower','石油大學(xué)java',5,28,'2003-10-1',5,12);
insert into book
values('7-111084-17-7 9787901084678','mmm','po3wer','石油大學(xué)mx',5,32,'2003-10-1',5,13);
insert into book
values('7-9111084-17-7 9787910084678','pkh','wa3re','石油大學(xué)ware',5,26,'2003-10-1',5,7);
insert into book
values('7-11184-17-7 9788900084678','yhh','engli3sh','石油大學(xué)english',5,25,'2003-10-1',5,1);
insert into book
values('7-922084-17-7 978789a4678','sd','powe5r','石油大學(xué)pb',5,27,'2003-10-1',5,5);
insert into book
values('7-922084-17-7 9787900085777','addd','as5p','石油大學(xué)asp',5,29,'2003-10-1',5,15);
insert into book
values('7-922084-17-7 97579001847777','jggg','powe5r','石油大學(xué)java',5,28,'2003-10-1',5,16);
insert into book
values('7-922084-17-7 9787901087777','mmm','powe5r','石油大學(xué)mx',5,32,'2003-10-1',5,11);
insert into book
values('7-9022084-17-7 9787910087777','pkh','war5e','石油大學(xué)ware',5,26,'2003-10-1',5,7);


drop table thushuchuli;
create table thushuchuli( /*圖書損失處理*/
ChuLiyichi datetime,
name_no char(10),
book_no char(30),
book_destorykind char(20),
fakuanmoney money);

drop table pop;
create table pop/*圖書流行指數(shù)*/
(book_no char(30) primary key,
point   integer);

/*讀者的資料*/
drop table money_list;
drop table readpass;
drop table money;
drop table readname;
CREATE TABLE readname(
	name_no char(10) primary key,
	name_name char(8),
	name_class char(20));
insert into readname
values('0001','0001','01');
insert into readname
values('0002','0002','00');

CREATE TABLE money( 
	name_no char(10) primary key,
	name_name char(8),
	name_arrearage money,
foreign key (name_no) references readname(name_no));


/*讀者帳號(hào)*/
create table readpass(
    name_name char(10) primary key,
    name_pass char(8) not null,
    sex char(2),
    department char(12),
    telephone char(13), 
    grade char(10) not null, 
foreign key (name_name) references readname(name_no));
insert into readpass
values('0001','0001','男','計(jì)機(jī)算','58467695','讀者');
insert into readpass
values('0002','0002','男','計(jì)機(jī)算','5844565','管理員');

/*讀者的欠款*/


/*money_list*/

create table money_list
(name_no char(10) primary key,
name_arrearage money,
foreign key(name_no) references money(name_no)
);


/*已借書的情況表*/
CREATE TABLE yjbook(     /*讀者借書情況*/
	name_no char(10) primary key,/*讀者*/
	book_no1 char(30),
        book1 char(30),
	date1 datetime,
book_no2 char(30),
	book2 char (30),
	date2 datetime,
book_no3 char(30),
	book3 char(30),
	date3 datetime,
book_no4 char(30),
	book4 char(30),
	date4 datetime,
book_no5 char(30),
	book5 char(30),
	date5 datetime
foreign key (name_no) references readname(name_no),
foreign key (book_no1) references book(book_no),
foreign key (book_no2) references book(book_no),
foreign key (book_no3) references book(book_no),
foreign key (book_no4) references book(book_no),
foreign key (book_no5) references book(book_no));

create table wantlend(   /*已選擇想借的書*/
name_no char(10) primary key,
book_NO1 char(30) ,/* 圖書編號(hào)*/
  book_name1 char(26) ,/*書名*/
book_NO2 char(30) ,
  book_name2 char(26),
book_NO3 char(30) ,
  book_name3 char(26) ,
book_NO4 char(30) ,
  book_name4 char(26),
book_NO5 char(30) ,
  book_name5 char(26),
foreign key (name_NO) references readname(name_no));
/*入庫(kù)圖書詳細(xì)信息表*/
drop table book_in;
create table book_in(
  book_NO char(30)  not null,/* 圖書編號(hào)*/
  book_name char(30) not null,
  book_zname char(20),  
  book_publish char(26),
  enter_num integer, 
  book_jg  money,
  enter_date datetime primary key,
  Book_leave integer,
  kind integer); 
/*保存刪除圖書的表*/
drop table book_leave_stay;
create table book_leave_stay(
  book_NO char(30)  primary key,/* 圖書編號(hào)*/
  book_name char(30) not null,
  book_zname char(20),  
  book_publish char(26),
  book_leave integer, 
  book_jg  money,
  enter_date datetime,
  kind integer,
  detail char(50));

drop table yjlist;
/*yjlist*/
create table yjlist
(id char(1)primary key,
name_no char(10),
name_name char(8),
book_no char(30),
book char(30),
datej datetime
);