libdb.sql

数据库文件先要导入

create database LibDB
ON 
( NAME = LibDB_dat,
   FILENAME = 'd:\LibDB.mdf'
)
LOG ON
( NAME = 'LibDB_log',
   FILENAME = 'd:\LibDB.ldf'
)

go

use LibDB

go

create table readers
(
RID int IDENTITY(100,1) PRIMARY KEY clustered(RID),
RName char(20),
RSex char(4),
RPassword char(20),
RCount int default 0,
E_mail char(50),
BZ nvarchar(255),
)

go


create table manager
(
MID int IDENTITY(100,1) PRIMARY KEY clustered(MID),
MName char(20),
MPassword char(20),
)


go




create table books
(
ISBN char(13) PRIMARY KEY clustered(ISBN) ,
type char(20),
BName char(20),
Author char(20),
Publisher nvarchar(40),
BCount char(20),
Price char(10),
BZ nvarchar(255),
)


go


create table binfo
(
ISBN char(13),
RID int,
BDate datetime,
BlDate datetime,
BZ char(200),
)

go


create table rinfo
(
ISBN char(13),
RID int ,
RDate datetime,
RlDate datetime,
BZ nvarchar(200),
)

go


ALTER TABLE Binfo
   ADD   CONSTRAINT F1  FOREIGN KEY (ISBN)           REFERENCES books (ISBN),
         CONSTRAINT F2  FOREIGN KEY (RID)           REFERENCES readers(RID) 
go

ALTER TABLE Rinfo
   ADD   CONSTRAINT F3  FOREIGN KEY (ISBN)           REFERENCES books (ISBN),
         CONSTRAINT F4  FOREIGN KEY (RID)           REFERENCES readers(RID)
go

INSERT INTO manager(MName,MPassword)
values('admin','123')