leasedbsql.sql

个人版音像管理系统,可以扩展,内含数据库,支持多数据库使用

use master 
if exists(select * from sysdatabases where name='LeaseDB')
  drop database leaseDB
GO

create database leaseDB
on primary
(
	name='leaseDB_mdf',
	filename='d:\leaseDB.mdf'
)
log on
(
	name='leaseDB_ldf',
	filename='d:\leaseDB.ldf'
)
GO

use leaseDB
if exists(select * from sysObjects where name='DiscInfo')
  drop table discInfo

create table discInfo
(
	DiscID int primary key identity(1,1),
	DiscName varchar(30) not null,
	Author varchar(20),
	Director varchar(20),
	SimpleInfo varchar(200),
	TID int not null,
	DiscState int not null check(DIscState = 0 or DiscState = 1) default(1)
)
GO

if exists(select * from sysobjects where name='DiscType')
  drop table DiscType

create table DiscType
(
	TypeID int primary key identity(1,1),
	TypeName varchar(20) not null unique
)
GO

if exists(select * from sysobjects where name='MemberInfo')
  drop table MemberInfo

create table MemberInfo
(
	MemberID varchar(10) primary key,
	MemberName varchar(20) not null,
	MemberPhone varchar(15),
	MemberAddress varchar(50),
	MemberState int not null check(MemberState = 0 or MemberState = 1) default(1),
	LID int not null
)
GO

if exists(select * from sysObjects where name='MemberLevel')
  drop table MemberLevel

create table MemberLevel
(
	LevelID int primary key identity(1,1),
	LevelName varchar(20) not null unique,
	DiscCount int not null check(DiscCount = 3 or DiscCount = 5)
)
GO

if exists(select * from sysObjects where name = 'LeaseInfo')
  drop table LeaseInfo

create table LeaseInfo
(
	LeaseID int primary key identity(1,1),
	MID varchar(10) not null,
	DID int not null,
	LeaseDate datetime not null default(getdate()),
	ReturnDate datetime default(getdate())
)
GO

alter table DiscInfo
add constraint FK_TID foreign key(TID) references DiscType(TypeID)

alter table MemberInfo
add constraint FK_LID foreign key(LID) references MemberLevel(LevelID)

alter table LeaseInfo
add constraint FK_DID foreign key(DID) references DiscInfo(DiscID)

alter table LeaseInfo
add constraint FK_MID foreign key(MID) references MemberInfo(MemberID)

alter table LeaseInfo
add constraint CK_RE_LE check(returnDate > LeaseDate)







--存储过程
--leaseInfo 表
if exists(select * from sysObjects where name = 'proc_insert_LeaseInfo')
  drop proc proc_insert_LeaseInfo
GO

create proc proc_insert_LeaseInfo
@MemberName varchar(20),
@DiscName varchar(30)
as
declare @MID varchar(10),@DID int
	select @MID=MemberID from MemberInfo where MemberName = @MemberName
	select @DID=DiscID from DiscInfo where DiscName = @DiscName
	insert into LeaseInfo values(@MID,@DID,default,null)
GO


if exists (select * from sysObjects where name= 'proc_select_LeaseInfo')
  drop proc proc_select_LeaseInfo
GO
create proc proc_select_LeaseInfo
@where varchar(50),
@dateWhere varchar(4)
as
if @dateWhere = 'All'
(
	select LeaseID,
		(select MemberName from MemberInfo where MemberID = MID) as MemberName,
		(select LevelName from MemberLevel where LevelID = 
			(select LID from MemberInfo where MemberID = MID)) as LeaseMemberLV,
		(select DiscName from DiscInfo where DiscID = DID) as LeaseDiscName,
		(select DiscState from DiscInfo where DiscID = DID) as LeaseDiscState,
		LeaseDate,ReturnDate 
	from LeaseInfo 
	where MID in (select MemberID from MemberInfo where MemberName like '%'+@where+'%') 
		or MID like '%'+@where+'%'
		or DID in (select DiscID from DiscInfo where DiscName like '%'+@where+'%') 
		or DID like '%'+@where+'%'
)
else if @dateWhere = 'Borrowed'
(
	select LeaseID,
		(select MemberName from MemberInfo where MemberID = MID) as MemberName,
		(select LevelName from MemberLevel where LevelID = 
			(select LID from MemberInfo where MemberID = MID)) as LeaseMemberLV,
		(select DiscName from DiscInfo where DiscID = DID) as LeaseDiscName,
		(select DiscState from DiscInfo where DiscID = DID) as LeaseDiscState,
		LeaseDate,ReturnDate 
	from LeaseInfo 
	where (MID in (select MemberID from MemberInfo where MemberName like '%'+@where+'%') 
		or MID like '%'+@where+'%'
		or DID in (select DiscID from DiscInfo where DiscName like '%'+@where+'%') 
		or DID like '%'+@where+'%')
		and ReturnDate is null
)
else if @dateWhere = 'Returned'
(
		select LeaseID,
		(select MemberName from MemberInfo where MemberID = MID) as MemberName,
		(select LevelName from MemberLevel where LevelID = 
			(select LID from MemberInfo where MemberID = MID)) as LeaseMemberLV,
		(select DiscName from DiscInfo where DiscID = DID) as LeaseDiscName,
		(select DiscState from DiscInfo where DiscID = DID) as LeaseDiscState,
		LeaseDate,ReturnDate 
	from LeaseInfo 
	where (MID in (select MemberID from MemberInfo where MemberName like '%'+@where+'%') 
		or MID like '%'+@where+'%'
		or DID in (select DiscID from DiscInfo where DiscName like '%'+@where+'%') 
		or DID like '%'+@where+'%')
		and ReturnDate is not null
)
GO

if exists(select * from sysobjects where name = 'proc_update_LeaseInfo')
  drop proc proc_update_LeaseInfo
GO
create proc proc_update_LeaseInfo
@LeaseID int
as
	update LeaseInfo set ReturnDate = default where LeaseID = @LeaseID
GO

--MemberInfo表
if exists(select * from sysObjects where name='proc_insert_MemberInfo')
  drop proc proc_insert_MemberInfo
Go
create proc proc_insert_MemberInfo
@MemberID varchar(10),
@MemberName varchar(20),
@MemberPhone varchar(15),
@MemberAddress varchar(50),
@LevelName varchar(20)

as 
declare @LID int
	select @LID = LevelID from MemberLevel where LevelName = @LevelName
	insert into MemberInfo values(@MemberID,@MemberName,@MemberPhone,@MemberAddress,default,@LID)
GO

if exists(select * from sysobjects where name='proc_select_MemberInfo')
  drop proc proc_select_MemberInfo
GO
create proc proc_select_MemberInfo
@where varchar(50)
as
	select MemberId,MemberName,MemberPhone,MemberAddress,MemberState,
	(select LevelName from MemberLevel where LevelID = LID) as MemberLV,
	(select DiscCount from MemberLevel where LevelID = LID) as MaxCount,
	(select count(MID) from LeaseInfo where MID = MemberID and ReturnDate is null) as DiscCount
	from MemberInfo 
	where MemberID like '%'+@where+'%' 
		or MemberID in 
			(select MID from LeaseInfo where ReturnDate is null and DID in 
				(select DiscID from DiscInfo where DiscName like '%'+@where+'%'))
		or MemberName like '%'+@where+'%'
		or MemberPhone like '%'+@where+'%' 
		or MemberAddress like '%'+@where+'%'
		or LID in (select LevelID from MemberLevel where LevelName like '%'+@where+'%')
GO

if exists(select * from sysobjects where name='proc_update_MemberInfo')
  drop proc proc_update_MemberInfo
GO
create proc proc_update_MemberInfo
@MemberID varchar(10),
@MemberName varchar(20),
@MemberPhone varchar(15),
@MemberAddress varchar(50),
@MemberState int,
@LevelName varchar(20)
as
	update MemberInfo 
	set memberName = @MemberName, memberPhone = @MemberPhone,
		memberAddress = @MemberAddress,memberState = @MemberState,
		LID = (select LevelID from MemberLevel where LevelName = @LevelName)
	where MemberID = @MemberID
Go

--DiscInfo表
if exists(select * from sysobjects where name='proc_insert_DiscInfo')
  drop proc proc_insert_DiscInfo
GO
create proc proc_insert_DiscInfo
@DiscName varchar(30),
@Author varchar(20),
@Director varchar(20),
@SimpleInfo varchar(200),
@TypeName varchar(20)
as
declare @TID int
	select @TID = TypeID from DiscType where TypeName = @TypeName

	insert into DiscInfo 
	values (@DiscName,@Author,@Director,@SimpleInfo,@TID,default)
GO

if exists(select * from sysobjects where name='proc_select_DiscInfo')
  drop proc proc_select_DiscInfo
GO
create proc proc_select_DiscInfo
@where varchar(50)
as 
	select DiscID,DiscName,Author,Director,SimpleInfo,
			(select TypeName from DiscType where TypeID = TID) as DiscTP,
			DiscState 
	from DiscInfo 
	where convert(char(10),DiscID) like '%'+@where+'%' or DiscName like '%'+@where+'%'
		or Author like '%'+@where+'%' or Director like '%'+@where+'%'
		or SimpleInfo like '%'+@where+'%' 
		or TID in (select TypeID from DiscType where TypeName like '%'+@where+'%')
GO

if exists(select * from sysObjects where name='proc_update_DiscInfo')
  drop proc proc_update_DiscInfo
GO
create proc proc_update_DiscInfo
@DiscName varchar(30),
@Author varchar(20),
@Director varchar(20),
@SimpleInfo varchar(200),
@TypeName varchar(20),
@DiscState int,
@DiscID int
as
	Update DiscInfo 
	set discName = @DiscName,Author =  @Author,
		Director = @Director,SimpleInfo =  @SimpleInfo,
		TID = (select TypeID from DiscType where TypeName = @TypeName),
		DiscState = @DiscState 
	where DiscID = @DiscID
GO

--触发器
--LeaseInfo表
if exists(select * from sysobjects where name = 'trig_insert_LeaseInfo')
  drop trigger trig_insert_LeaseInfo
GO
create trigger trig_insert_LeaseInfo
on LeaseInfo
for insert
as
declare @DiscID int,@DiscState int
	select @DiscID = DID from inserted
	select @DiscState = DiscState from DiscInfo where DiscID = @DiscID
if (@DiscState <> 1)
	rollback transaction
	update DiscInfo set DiscState = 0 where DiscID = @DiscID
if @@error <> 0 
	rollback transaction

if exists(select * from sysObjects where name = 'trig_update_LeaseInfo')
  drop trigger trig_update_LeaseInfo
GO
create trigger trig_update_LeaseInfo
on LeaseInfo
for update
as 
declare @DiscID int,@ReturnDate datetime
	select @DiscID = DID,@ReturnDate = ReturnDate from updated
if(@ReturnDate != null)
	update DiscInfo set DiscState = 1 where DiscID = @DiscID
else
	rollback transaction
if @@error <> 0 
	rollback transaction
GO


--测试数据
--MemberLevel
insert into MemberLevel
values ('普通会员',3) 
insert into MemberLevel
values ('高级会员',5)

--MemberInfo
Insert into MemberInfo
values ('S0001','梁涛','13900000000','这里那里',default,2)

Insert into MemberInfo
values ('S0002','王为','13900000001','这里那里(不在一起)',default,1)


--DiscType
insert into DiscType
values ('动作片')

insert into DiscType
values ('喜剧片')

insert into DiscType
values ('爱情片')

insert into DiscType
values ('战争片')

insert into DiscType
values ('科幻片')

insert into DiscType
values ('恐怖片')

--DiscInfo
insert into DiscInfo
values ('大话西游','周星星','周星星','粉搞笑,粉搞笑',2,default)

insert into DiscInfo
values ('断背山','不详','不详','の,不想说什么',6,default)

--LeaseInfo
insert into LeaseInfo
values ('S0002',2,default,null)



select * from LeaseInfo
select * from MemberInfo

select * from MemberLevel