当前文件路径：ClassSay/DB_51aspx/Sql.sql

		            
  1create database ClassSay
  2go
  3
  4use ClassSay
  5go
  6
  7create table ClassUserID
  8(
  9    UserID int identity(1,1) primary key,
 10    UserName varchar(20) unique,
 11    UserPassword varchar(20),
 12    UserQQ int,
 13    UserMsn varchar(20),
 14    UserAddress varchar(50),
 15    UserEmail varchar(20),
 16    UserTel varchar(20),
 17    UserYeah varchar(20),
 18    UserAge int,
 19    UserLable varchar(100),--个性签明
 20    UserImage varchar(100),--个性图片
 21    UserRoot char(1)
 22)
 23go
 24
 25create table GG
 26(
 27    userText text
 28)
 29go
 30
 31create proc seleGG
 32as
 33    select userText from GG
 34go
 35
 36insert GG
 37values('    大家有时间多回来瞧瞧，有事情大家招呼一声!')
 38go
 39
 40create proc UpdateGG
 41    @userText text
 42as
 43    update GG
 44    set userText = @userText
 45go
 46
 47
 48Create Table SumTable
 49(
 50    CountUser int
 51)
 52go
 53
 54insert dbo.SumTable
 55values(1)
 56go
 57
 58create proc UpdateSumUser
 59    @CountUser int
 60as
 61    update dbo.SumTable
 62    set CountUser = @CountUser
 63go
 64
 65create proc SeleUserSum
 66as
 67    select CountUser from SumTable
 68go
 69
 70create proc CreateUser
 71    @UserName varchar(20),
 72    @UserPassword varchar(20),
 73    @UserQQ int,
 74    @UserMsn varchar(20),
 75    @UserAddress varchar(50),
 76    @UserEmail varchar(20),
 77    @UserTel varchar(20),
 78    @UserYeah varchar(20),
 79    @UserAge int,
 80    @UserLable varchar(100),--个性签明
 81    @UserImage varchar(100)--个性图片
 82as
 83insert into ClassUserID(UserName,UserPassword,UserQQ,UserMsn,UserAddress,UserEmail,UserTel,UserYeah,UserAge,UserLable,UserImage,UserRoot)
 84values(@UserName,@UserPassword,@UserQQ,@UserMsn,@UserAddress,@UserEmail,@UserTel,@UserYeah,@UserAge,@UserLable,@UserImage,'5')
 85go
 86
 87exec CreateUser '51aspx','51aspx',3651624,'ikceo@hotmail.com','武汉市','inlk@sina.com','0','0','25','大家好','images/headpic/image24.gif'
 88go
 89
 90create proc SeleNameUser
 91    @UserName varchar(20)
 92as
 93    select UserName from dbo.ClassUserID
 94    where UserName = @UserName
 95go
 96
 97create proc NameUser
 98as
 99    select UserName from dbo.ClassUserID
100go
101
102create proc UpdateUser
103    @UserName varchar(20),
104    @UserPassword varchar(20),
105    @UserLable varchar(100),--个性签明
106    @UserImage varchar(100)--个性图片
107as
108    update ClassUserID
109    set UserPassword = @UserPassword,UserLable=@UserLable,UserImage=@UserImage
110    where UserName = @UserName
111go
112
113create proc seleUpdateUser
114    @UserName varchar(20)
115as
116    select UserName,UserPassword,UserLable,UserImage
117    from dbo.ClassUserID
118    where UserName = @UserName
119go
120
121--用户权限
122create proc UserProc
123    @UserName varchar(20),
124    @UserPassword varchar(20),
125    @UserRoot char(1) = '0' output
126as     
127     if exists (select * from  dbo.ClassUserID 
128    where @UserName = UserName and @UserPassword = UserPassword)
129        select @UserRoot = UserRoot from  dbo.ClassUserID 
130    where @UserName = UserName and @UserPassword = UserPassword
131    else
132        set @UserRoot = '0'
133go
134
135create table ClassText
136(
137    TextID int identity(1,1) primary key,
138    UserID int foreign key(UserID) references ClassUserID(UserID),
139    UserTtitle varchar(50),
140    UserText text,
141    TextBack int default 0,    --是否有回复
142    UserData datetime
143)
144go
145--多少条留言
146create proc CountText
147as
148    select count(*) from dbo.ClassText
149    
150go
151
152--回复表
153create table back
154(
155    BackID int identity(1,1) primary key,
156    TextID int references ClassText(TextID),    
157    UserName varchar(20),
158    BackBody text,
159    BackTime datetime
160)
161go
162
163create proc deleText
164    @TextID int
165as
166    delete dbo.back
167    where @TextID = TextID
168    delete dbo.ClassText
169    where @TextID = TextID
170go
171
172--回复留言
173create proc BackTable
174    @TextID int,
175    @UserName varchar(20),
176    @BackBody text    
177as
178    declare @BackTime datetime
179    set @BackTime = getdate()    
180
181    insert into back
182    values(@TextID,@UserName,@BackBody,@BackTime)
183
184    update dbo.ClassText
185    set TextBack = TextBack+1
186    where TextID = @TextID
187go
188create proc SeleBack
189    @TextID int
190as
191    select BackBody,UserName,BackTime from dbo.back
192    where @TextID = TextID
193go
194
195--选
196create proc TextSelect
197as
198    select TextID,UserTtitle,UserText,UserData,TextBack,UserName,UserLable,UserImage
199    from ClassText a,dbo.ClassUserID b
200    where a.UserID = b.UserID
201    
202go
203
204
205--插入
206create proc InsertText
207    @UserName varchar(20) ,
208    @UserTtitle varchar(50),
209    @UserText varchar(1000),
210    @UserData datetime
211as
212        if exists (select * from  dbo.ClassUserID where @UserName = UserName)
213    begin    
214        declare @UserID int
215        select @UserID = UserID from  dbo.ClassUserID where @UserName = UserName
216
217        insert into ClassText(UserID,UserTtitle,UserText,UserData)
218        values(@UserID,@UserTtitle,@UserText,@UserData)
219    end
220go
221
222
223create proc UpdateClassText
224    @TextID int,    
225    @UserText text,
226    @UserData datetime
227as
228    update dbo.ClassText
229    set UserText = @UserText,UserData = @UserData
230    where TextID = @TextID
231go
232
233--51aspx.com 2008-3-21