sql.sql

三层班级留言本源码 用VS2005写的,对于其中没有太多的技术含量, 但是还是蛮有参考价值的. 后台数据库的读写采用三层架构,没有专业的后台管理程序,所有操作都是同一个页面上进行. 如果

create database ClassSay
go

use ClassSay
go

create table ClassUserID
(
	UserID int identity(1,1) primary key,
	UserName varchar(20) unique,
	UserPassword varchar(20),
	UserQQ int,
	UserMsn varchar(20),
	UserAddress varchar(50),
	UserEmail varchar(20),
	UserTel varchar(20),
	UserYeah varchar(20),
	UserAge int,
	UserLable varchar(100),--个性签明
	UserImage varchar(100),--个性图片
	UserRoot char(1)
)
go

create table GG
(
	userText text
)
go

create proc seleGG
as
	select userText from GG
go

insert GG
values('    大家有时间多回来瞧瞧，有事情大家招呼一声!')
go

create proc UpdateGG
	@userText text
as
	update GG
	set userText = @userText
go


Create Table SumTable
(
	CountUser int
)
go

insert dbo.SumTable
values(1)
go

create proc UpdateSumUser
	@CountUser int
as
	update dbo.SumTable
	set CountUser = @CountUser
go

create proc SeleUserSum
as
	select CountUser from SumTable
go

create proc CreateUser
	@UserName varchar(20),
	@UserPassword varchar(20),
	@UserQQ int,
	@UserMsn varchar(20),
	@UserAddress varchar(50),
	@UserEmail varchar(20),
	@UserTel varchar(20),
	@UserYeah varchar(20),
	@UserAge int,
	@UserLable varchar(100),--个性签明
	@UserImage varchar(100)--个性图片
as
insert into ClassUserID(UserName,UserPassword,UserQQ,UserMsn,UserAddress,UserEmail,UserTel,UserYeah,UserAge,UserLable,UserImage,UserRoot)
values(@UserName,@UserPassword,@UserQQ,@UserMsn,@UserAddress,@UserEmail,@UserTel,@UserYeah,@UserAge,@UserLable,@UserImage,'5')
go

exec CreateUser '51aspx','51aspx',3651624,'[e-mail]','武汉市','[e-mail]','0','0','25','大家好','images/headpic/image24.gif'
go

create proc SeleNameUser
	@UserName varchar(20)
as
	select UserName from dbo.ClassUserID
	where UserName = @UserName
go

create proc NameUser
as
	select UserName from dbo.ClassUserID
go

create proc UpdateUser
	@UserName varchar(20),
	@UserPassword varchar(20),
	@UserLable varchar(100),--个性签明
	@UserImage varchar(100)--个性图片
as
	update ClassUserID
	set UserPassword = @UserPassword,UserLable=@UserLable,UserImage=@UserImage
	where UserName = @UserName
go

create proc seleUpdateUser
	@UserName varchar(20)
as
	select UserName,UserPassword,UserLable,UserImage
	from dbo.ClassUserID
	where UserName = @UserName
go

--用户权限
create proc UserProc
	@UserName varchar(20),
	@UserPassword varchar(20),
	@UserRoot char(1) = '0' output
as	 
 	if exists (select * from  dbo.ClassUserID 
	where @UserName = UserName and @UserPassword = UserPassword)
		select @UserRoot = UserRoot from  dbo.ClassUserID 
	where @UserName = UserName and @UserPassword = UserPassword
	else
		set @UserRoot = '0'
go

create table ClassText
(
	TextID int identity(1,1) primary key,
	UserID int foreign key(UserID) references ClassUserID(UserID),
	UserTtitle varchar(50),
	UserText text,
	TextBack int default 0,	--是否有回复
	UserData datetime
)
go
--多少条留言
create proc CountText
as
	select count(*) from dbo.ClassText
	
go

--回复表
create table back
(
	BackID int identity(1,1) primary key,
	TextID int references ClassText(TextID),	
	UserName varchar(20),
	BackBody text,
	BackTime datetime
)
go

create proc deleText
	@TextID int
as
	delete dbo.back
	where @TextID = TextID
	delete dbo.ClassText
	where @TextID = TextID
go

--回复留言
create proc BackTable
	@TextID int,
	@UserName varchar(20),
	@BackBody text	
as
	declare @BackTime datetime
	set @BackTime = getdate()	

	insert into back
	values(@TextID,@UserName,@BackBody,@BackTime)

	update dbo.ClassText
	set TextBack = TextBack+1
	where TextID = @TextID
go
create proc SeleBack
	@TextID int
as
	select BackBody,UserName,BackTime from dbo.back
	where @TextID = TextID
go

--选
create proc TextSelect
as
	select TextID,UserTtitle,UserText,UserData,TextBack,UserName,UserLable,UserImage
	from ClassText a,dbo.ClassUserID b
	where a.UserID = b.UserID
	
go


--插入
create proc InsertText
	@UserName varchar(20) ,
	@UserTtitle varchar(50),
	@UserText varchar(1000),
	@UserData datetime
as
        if exists (select * from  dbo.ClassUserID where @UserName = UserName)
	begin	
		declare @UserID int
		select @UserID = UserID from  dbo.ClassUserID where @UserName = UserName

		insert into ClassText(UserID,UserTtitle,UserText,UserData)
		values(@UserID,@UserTtitle,@UserText,@UserData)
	end
go


create proc UpdateClassText
	@TextID int,	
	@UserText text,
	@UserData datetime
as
	update dbo.ClassText
	set UserText = @UserText,UserData = @UserData
	where TextID = @TextID
go

--51aspx.com 2008-3-21