数据库图书管理系统创建表

CREATE TABLE LibrarySelect
(LibRankn CHAR(30)PRIMARY KEY,
LibRankname CHAR(30)
);
CREATE TABLE BookList
(BLisbn CHAR(20)PRIMARY KEY,
BLName CHAR(40),
BLAuthor CHAR(30),
BLPublishunit CHAR(40),
BLPrice CHAR (30),
BLRankNumber CHAR(40)
FOREIGN KEY(BLRankNumber)REFERENCES LibrarySelect(LibRankn)

);
CREATE TABLE Book
(BNumber CHAR (40)PRIMARY KEY,
BIsbn CHAR (40),
BBorrowif CHAR (40),
BRemark CHAR (40)
);
CREATE TABLE Reader
(RMumber CHAR (30) PRIMARY KEY,
RName CHAR (30),
RUnits CHAR (30),
RSage CHAR (30),
RAdd CHAR (30),
RTeNum CHAR (30),
RIdNo CHAR (40)
);
CREATE TABLE Borrow
(BLendNum CHAR(40)PRIMARY KEY,
BLIBnum CHAR(40),
BBNum CHAR(30),
BLendDate CHAR(40),
BReturnDate CHAR (30),
BFineCNum CHAR(40),
BRemark CHAR(40)
FOREIGN KEY( BLIBnum)REFERENCES Reader(RMumber)将第一个与第二个表连接起来

);
CREATE TABLE FineClasNum
(FineNum CHAR(40)PRIMARY KEY,
FName CHAR(40),
FMoney CHAR(30)
);
CREATE TABLE ORDER1
(OBSNum CHAR(40)PRIMARY KEY,
OLendNum CHAR(40),
OBookNum CHAR(30),
ODate CHAR(40)
);


INSERT
INTO LibrarySelect
VALUES ('100','文学');


INSERT
INTO LibrarySelect
VALUES ('200','科技');


INSERT
INTO LibrarySelect
VALUES ('400','哲学');


INSERT
INTO BookList （BLisbn ,BLName ,BLAuthor ,BLPublishunit ,BLPrice ,BLRankNumber）
VALUES ('7040195836','数据库系统概论','王珊','高等教育出版社',39.00,200);

INSERT
INTO BookList （BLisbn ,BLName ,BLAuthor ,BLPublishunit ,BLPrice ,BLRankNumber）
VALUES ('9787508040110', '红楼梦', '曹雪芹', '人民出版社', 20.00, 100);


INSERT
INTO BookList （BLisbn ,BLName ,BLAuthor ,BLPublishunit ,BLPrice ,BLRankNumber）
VALUES ('9787506336239', '红楼梦', '曹雪芹', '作家出版社', 34.30 ,100);


INSERT
INTO BookList （BLisbn, BLName ,BLAuthor, BLPublishunit ,BLPrice ,BLRankNumber）
VALUES ('9787010073750', '心学之路', '张立文 ','人民出版社', 33.80 ,300);



INSERT
INTO Book( BNumber ,BIsbn ,BBorrowif )
VALUES ('2001231', '7040195836 ','否 ');


INSERT
INTO Book( BNumber ,BIsbn ,BBorrowif )
VALUES ('2001232', '7040195836 ','是');


INSERT
INTO Book( BNumber ,BIsbn ,BBorrowif )
VALUES ('1005050', '9787506336239', '否');


INSERT
INTO Book( BNumber ,BIsbn ,BBorrowif )
VALUES ('1005063', '9787508040110', '是');


INSERT
INTO Book( BNumber ,BIsbn ,BBorrowif)
VALUES ('3007071', '9787010073750', '是');



INSERT
INTO Reader( RMumber,RName ,RUnits ,RSage)
VALUES ('20051001', '王菲', '四川绵阳西科大计算机学院', '女');


INSERT
INTO Reader( RMumber,RName ,RUnits ,RSage)
VALUES ('20062001 ','张江', '四川绵阳中心医院', '男');


INSERT
INTO Reader( RMumber,RName ,RUnits ,RSage )
VALUES ('20061234', '郭敬明', '四川江油305', '男');


INSERT
INTO Reader( RMumber,RName ,RUnits ,RSage )
VALUES ('20071235 ','李晓明 ','四川成都工商银行', '

男');


INSERT
INTO Reader( RMumber,RName ,RUnits ,RSage )
VALUES ('20081237',' 赵鑫',' 四川广元广元中学',' 女');




INSERT
INTO Borrow(BLendNum ,BLIBnum ,BBNum ,BLendDate ,BReturnDate )
VALUES (1 ,'20081237', '3007071', '2010/09/19', '2010/09/20');


INSERT
INTO Borrow(BLendNum ,BLIBnum ,BBNum ,BLendDate ,BReturnDate ,BFineCNum )
VALUES (2 ,'20071235 ','1005063',' 2010/10/20', '2011/02/20','1');


INSERT
INTO Borrow(BLendNum ,BLIBnum ,BBNum ,BLendDate )
VALUES (3,'20071235',' 2001232', '2011/09/01');


INSERT
INTO Borrow(BLendNum ,BLIBnum ,BBNum ,BLendDate )
VALUES (4,'20061234',' 1005063 ','2011/9/20');


INSERT
INTO Borrow(BLendNum ,BLIBnum ,BBNum ,BLendDate )
VALUES (5, '20051001 ','3007071', '2011/9/10');



INSERT
INTO FineClasNum(FineNum ,FName,FMoney)
VALUES ('1',' 延期', '10');


INSERT
INTO FineClasNum(FineNum ,FName,FMoney)
VALUES ('2',' 损坏', '20');


INSERT
INTO FineClasNum(FineNum ,FName,FMoney)
VALUES ('3', '丢失', '50');


INSERT
INTO ORDER1(OBSNum ,OLendNum ,OBookNum ,ODate )
VALUES('1', '20081237 ','2001232', '2011/09/11');

INSERT
INTO ORDER1(OBSNum ,OLendNum ,OBookNum ,ODate )
VALUES('2', '20081237 ','1005063', '2011/09/12');


SELECT BLName BLisbn
FROM BookList,LibrarySelect
WHERE BLName = '红楼梦'AND LibrarySelect.LibRankn= BookList.BLRankNumber


SELECT RMumber
FROM Reader
WHERE RMumber = '20061234'

查询"王菲"的单位、所借图书的书名和借阅日期。

SELECT RName ,RUnits, BLendDate
FROM Reader ,Borrow
WHERE RName ='王菲'

查询各图书的罚款总数

SELECT FineClasNum
FROM FineNum

查询每类图书的册数和平均单价
SELECT BLPrice COUNT(BLisbn)
FROM BookList