librarymanagement.sql

采用java+sql实现图书管理系统

CREATE DATABASE LibraryManagement
ON PRIMARY (NAME= LibraryManagement,
FILENAME='E:\LibraryManagement.mdf',
SIZE=4MB,
MAXSIZE=6MB,
FILEGROWTH=20%)

use LibraryManagement

CREATE TABLE userTable
(Userno int PRIMARY KEY,
UserName char(10),
Password char(10), 
Power char(20)) 

CREATE TABLE readers
(Readerno char(5) PRIMARY KEY, 
ReaderName char(20),
ReaderType char(5),
sex char(2),
Part char(20), 
Address char(40),
Telephone char(15),
Email char(20), 
Intime smalldatetime, 
Com char(2))

CREATE TABLE books
(Bookno int PRIMARY KEY, 
BookName char(20),
BookType char(20),
Press char(20), 
Author char(10), 
PressDate char(10),
Price char(10),
InDate smalldatetime, 
books_count char(20), 
borrowed_count char(20),
Com char(5))


CREATE TABLE bookBrowse
(Studentno int PRIMARY KEY,
StudentName char(10),
Type char(10),
Bookno int FOREIGN KEY(Bookno)REFERENCES books(Bookno),
BookName char(20), 
BorrowDate char(20), 
MustReturnDate char(20),
ReturnDate char(20),
Is_Returned char(10),
Com char(5)) 
          
           
INSERT into userTable values (3101,'烦烦','0000','系统管理员')
INSERT into userTable values (3103,'津津','0002','书籍管理员')
INSERT into userTable values (3105,'大大','0004','借阅管理员')
INSERT into userTable values (3201,'嘉嘉','0000','读者')
INSERT into userTable values (3202,'奇奇','0003','读者')
INSERT into userTable values (3203,'气气','0005','读者')
INSERT into userTable values (3204,'小小','0006','读者')
INSERT into userTable values (3205,'大大','0007','读者')
INSERT into userTable values (3206,'少少','0008','读者')
INSERT into userTable values (3207,'呵呵','0009','读者')
INSERT into userTable values (3208,'啃啃','0000','读者')
INSERT into userTable values (3209,'楠楠','0000','读者')

INSERT into readers values (3201,'嘉嘉','老师','女','广东工业大学','大学城','13828403500','[e-mail]','2009-8-02','')
INSERT into readers values (3202,'奇奇','学生','男','广东工业大学','大学城','13828403510','[e-mail]','2009-8-01','')
INSERT into readers values (3203,'气气','教授','女','广东工业大学','大学城','13828403501','[e-mail]','2009-8-03','')
INSERT into readers values (3204,'小小','学生','女','广东工业大学','大学城','13828403505','[e-mail]','2009-8-02','')
INSERT into readers values (3205,'大大','学生','男','广东工业大学','大学城','13828403507','[e-mail]','2009-8-04','')
INSERT into readers values (3206,'少少','老师','男','广东工业大学','大学城','13828403520','[e-mail]','2009-8-06','')
INSERT into readers values (3207,'呵呵','老师','女','广东工业大学','大学城','13828403560','[e-mail]','2009-8-05','')
INSERT into readers values (3208,'啃啃','老师','男','广东工业大学','大学城','13828403520','[e-mail]','2009-8-06','')
INSERT into readers values (3209,'楠楠','老师','女','广东工业大学','大学城','13828403560','[e-mail]','2009-8-05','')


INSERT into books values (207,'网络设备','计算机','清华大学出版社','甘刚','2005','28','2009-4-13','3','4','')
INSERT into books values (201,'java','计算机','北京大学出版社','小菊','2004','58','2009-4-13','5','3','')
INSERT into books values (202,'c++','计算机','清华大学出版社','小狗','2005','48','2009-4-13','10','4','')
INSERT into books values (203,'计算机网络','计算机','机械工业出版社','曾气','2005','21','2009-4-13','5','4','')
INSERT into books values (204,'单片机','计算机','人民邮电出版社','何小敏','2004','24','2009-4-13','5','4','丢失')
INSERT into books values (205,'普通话水平测试训练','语言','当代中国出版社','温家宝','2004','27','2009-4-13','5','3','损坏')
INSERT into books values (206,'邓小平理论','社会科学','高等教育出版社','邓小平','2005','38','2009-4-13','5','0','淘汰')
INSERT into books values (200,'c','计算机','清华大学出版社','小狗','2005','48','2009-4-13','10','4','')


INSERT into bookBrowse values (3201,'嘉嘉','老师',000,'网络设备','2005-04-04','2006-04-05','2005-09-02','否','')
INSERT into bookBrowse values (3202,'奇奇','学生',000,'网络设备','2005-04-09','2006-04-19','2005-10-01','否','')
INSERT into bookBrowse values (3203,'气气','教授',002,'c++','2005-09-04','2006-01-05','2005-12-01','否','')
INSERT into bookBrowse values (3206,'少少','老师',005,'普通话水平测试训练','2005-12-14','2006-02-05','2006-01-01','否','')
INSERT into bookBrowse values (3207,'呵呵','教授',006,'邓小平理论','2005-05-04','2006-04-15','2006-04-01','是','')