newtable.txt

图书馆管理系统的作业

/*创建书的类型名称与代号的对应表*/
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,'经典');
insert into kinds
values(2,'哲学');
insert into kinds
values(3,'法律');
insert into kinds
values(4,'军事');
insert into kinds
values(5,'经济');
insert into kinds
values(7,'文化教育');
insert into kinds
values(8,'语言文字');
insert into kinds
values(9,'文学');
insert into kinds
values(10,'美术设计');
insert into kinds
values(11,'历史地理');
insert into kinds
values(12,'工商管理');
insert into kinds
values(13,'小儿读物');
insert into kinds
values(14,'视听艺术');
insert into kinds
values(15,'自然科学');
insert into kinds
values(16,'生活娱乐');
insert into kinds
values(17,'趣味阅读');
insert into kinds
values(18,'医学');
insert into kinds
values(19,'农林牧渔');
insert into kinds
values(20,'工业技术');
insert into kinds
values(21,'计算机');
insert into kinds
values(22,'图片挂历');
insert into kinds
values(23,'进品图书');
insert into kinds
values(24,'综合');
/*书本信息*/

create table book(
  book_NO char(30)  primary key,/* 图书编号*/
  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','石油大学pb',5,27,'2003-10-1',5,5);
insert into book
values('7-900084-17-7 9787900085688','asp','asp','石油大学asp',5,29,'2003-10-1',5,11);
insert into book
values('7-900084-17-7 9757900184678','java','power','石油大学java',5,28,'2003-10-1',5,1);
insert into book
values('7-900084-17-7 9787901084678','mx','power','石油大学mx',5,32,'2003-10-1',5,11);
insert into book
values('7-900084-17-7 9787910084678','autoware','ware','石油大学ware',5,26,'2003-10-1',5,7);
insert into book
values('7-900084-17-7 9788900084678','english','english','石油大学english',5,25,'2003-10-1',5,9);
insert into book
values('7-922084-17-7 9787900084678','sd','power','石油大学pb',5,27,'2003-10-1',5,5);
insert into book
values('7-922084-17-7 9787900085688','addd','asp','石油大学asp',5,29,'2003-10-1',5,11);
insert into book
values('7-922084-17-7 9757900184678','jggg','power','石油大学java',5,28,'2003-10-1',5,8);
insert into book
values('7-922084-17-7 9787901084678','mmm','power','石油大学mx',5,32,'2003-10-1',5,11);
insert into book
values('7-9022084-17-7 9787910084678','pkh','ware','石油大学ware',5,26,'2003-10-1',5,7);
insert into book
values('7-902284-17-7 9788900084678','yhh','en3glish','石油大学english',5,25,'2003-10-1',5,1);
insert into book
values('7-111084-17-7 9787900084678','sd','po3wer','石油大学pb',5,27,'2003-10-1',5,5);
insert into book
values('7-111084-17-7 9787900085688','addd','as3p','石油大学asp',5,29,'2003-10-1',5,11);
insert into book
values('7-111084-17-7 9757900184678','jggg','p3ower','石油大学java',5,28,'2003-10-1',5,12);
insert into book
values('7-111084-17-7 9787901084678','mmm','po3wer','石油大学mx',5,32,'2003-10-1',5,13);
insert into book
values('7-9111084-17-7 9787910084678','pkh','wa3re','石油大学ware',5,26,'2003-10-1',5,7);
insert into book
values('7-11184-17-7 9788900084678','yhh','engli3sh','石油大学english',5,25,'2003-10-1',5,1);
insert into book
values('7-922084-17-7 978789a4678','sd','powe5r','石油大学pb',5,27,'2003-10-1',5,5);
insert into book
values('7-922084-17-7 9787900085777','addd','as5p','石油大学asp',5,29,'2003-10-1',5,15);
insert into book
values('7-922084-17-7 97579001847777','jggg','powe5r','石油大学java',5,28,'2003-10-1',5,16);
insert into book
values('7-922084-17-7 9787901087777','mmm','powe5r','石油大学mx',5,32,'2003-10-1',5,11);
insert into book
values('7-9022084-17-7 9787910087777','pkh','war5e','石油大学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/*图书流行指数*/
(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));


/*读者帐号*/
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','男','计机算','58467695','读者');
insert into readpass
values('0002','0002','男','计机算','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) ,/* 图书编号*/
  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));
/*入库图书详细信息表*/
drop table book_in;
create table book_in(
  book_NO char(30)  not null,/* 图书编号*/
  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,/* 图书编号*/
  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
);