DB_51aspx/SQLQuery1.sql

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

create database SHBookMIS
go
use SHBookMIS
create table UserInfo
(
UserID varchar(10) primary key,
UserPwd varchar(20) not null default'111111',
UserRight varchar not null default'1'
)
go
insert into UserInfo(UserID) values('zhang')
insert into UserInfo values('admin','admin','0')
create table BookType
(
TypeID varchar(10) primary key,
TypeName varchar(20) not null
)
go
insert into BookType(TypeID,TypeName) values('T001','武侠')
insert into BookType(TypeID,Typename) values('T002','言情')
insert into BookType(TypeID,Typename) values('T003','灵异')
insert into BookType(TypeID,Typename) values('T004','科幻')
insert into BookType(TypeID,Typename) values('T005','推理')
create table BookInfo
(
BookID varchar(10) primary key,
BookName varchar(50) not null,
TypeID varchar(10) foreign key references BookType(TypeID),
Author varchar(20) not null,
HireByDay smallmoney not null,
Deposit smallmoney not null,
Times int default'0',
State varchar default'0'
)
go
insert into BookInfo(BookID,BookName,TypeID,Author,HireByDay,Deposit) values('B001','绝代双骄','T001','古龙',1,50)
insert into BookInfo(BookID,BookName,TypeID,Author,HireByDay,Deposit) values('B002','多情剑客无情剑','T001','古龙',1,50)
insert into BookInfo(BookID,BookName,TypeID,Author,HireByDay,Deposit) values('B003','天涯明月刀','T001','古龙',1,50)
insert into BookInfo(BookID,BookName,TypeID,Author,HireByDay,Deposit) values('B004','雪山飞狐','T001','金庸',1,50)
insert into BookInfo(BookID,BookName,TypeID,Author,HireByDay,Deposit) values('B005','风声','T003','麦家',1,50)
insert into BookInfo(BookID,BookName,TypeID,Author,HireByDay,Deposit) values('B006','盗墓之王','T003','飞天',1,50)
insert into BookInfo(BookID,BookName,TypeID,Author,HireByDay,Deposit) values('B007','末世天启','T004','火炬之光',1,50)
insert into BookInfo(BookID,BookName,TypeID,Author,HireByDay,Deposit) values('B008','重启家园','T004','九头怪猫',1,50)
create table BorrowRecord
(
	RecordID int identity,
	BookID varchar(10) foreign key references BookInfo(BookID),
	LendDate smalldatetime,
	ReturnDate smalldatetime,
	Hire smallmoney  default 0.0, --应收租金
	Pawn varchar,		--抵押物 0:身份证，1:现金
	IDCard varchar(18),	--上个字段为0时
	UserID varchar(10) foreign key references UserInfo(UserID),	--经手人
	State varchar(10) default '未还' 	--是否已还/未还
)
create table MemberInfo
(
  MemberID varchar(20),--自动增长 M001,M002,M003,...
  MemberName varchar(30),
  MemberSex char(2),