leasedbsql.sql~rf27443a1.tmp

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

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,
	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 not null 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),
@MID int,
@DID int
as
	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)
as
	select (select MemberName from MemberInfo where MemberID = MID) as MemberName,
	  (select DiscName from DiscInfo where DiscID = DID) as DiscName,
	  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+'%'
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),
@LID int,
@LevelName varchar(20)
as 
	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,
	(select LevelName from MemberLevel where LevelID = LID) as MemberLV,
	(select DiscCount from MemberLevel where LevelID = LID) as MaxCount 
	from MemberInfo 
	where MemberID 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