当前文件路径：OnLineExam/DB_51aspx/MyOnLineExam.sql

		            
  1SET ANSI_NULLS ON
  2GO
  3SET QUOTED_IDENTIFIER ON
  4GO
  5IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
  6BEGIN
  7--51aspx.com
  8CREATE TABLE [dbo].[Users](
  9    [UserID] [varchar](20) NOT NULL,
 10    [UserName] [varchar](20) NOT NULL,
 11    [UserPwd] [varchar](20) NOT NULL,
 12    [UserPower] [int] NOT NULL,
 13 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
 14(
 15    [UserID] ASC
 16)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 17) ON [PRIMARY]
 18END
 19GO
 20SET ANSI_NULLS ON
 21GO
 22SET QUOTED_IDENTIFIER ON
 23GO
 24IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Score]') AND type in (N'U'))
 25BEGIN
 26CREATE TABLE [dbo].[Score](
 27    [ID] [int] IDENTITY(1,1) NOT NULL,
 28    [UserID] [varchar](20) NOT NULL,
 29    [PaperID] [int] NOT NULL,
 30    [Score] [int] NOT NULL,
 31    [ExamTime] [datetime] NOT NULL CONSTRAINT [DF_Score_ExamTime]  DEFAULT (getdate()),
 32 CONSTRAINT [PK_Score] PRIMARY KEY CLUSTERED 
 33(
 34    [ID] ASC
 35)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 36) ON [PRIMARY]
 37END
 38GO
 39SET ANSI_NULLS ON
 40GO
 41SET QUOTED_IDENTIFIER ON
 42GO
 43IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SingleProblem]') AND type in (N'U'))
 44BEGIN
 45CREATE TABLE [dbo].[SingleProblem](
 46    [ID] [int] IDENTITY(1,1) NOT NULL,
 47    [CourseID] [int] NOT NULL,
 48    [Title] [varchar](1000) NOT NULL,
 49    [AnswerA] [varchar](500) NOT NULL,
 50    [AnswerB] [varchar](500) NOT NULL,
 51    [AnswerC] [varchar](500) NOT NULL,
 52    [AnswerD] [varchar](500) NOT NULL,
 53    [Answer] [varchar](2) NOT NULL,
 54 CONSTRAINT [PK_SingleProblem] PRIMARY KEY CLUSTERED 
 55(
 56    [ID] ASC
 57)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 58) ON [PRIMARY]
 59END
 60GO
 61SET ANSI_NULLS ON
 62GO
 63SET QUOTED_IDENTIFIER ON
 64GO
 65IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Course]') AND type in (N'U'))
 66BEGIN
 67CREATE TABLE [dbo].[Course](
 68    [ID] [int] IDENTITY(1,1) NOT NULL,
 69    [Name] [varchar](200) NOT NULL,
 70 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED 
 71(
 72    [ID] ASC
 73)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 74) ON [PRIMARY]
 75END
 76GO
 77SET ANSI_NULLS ON
 78GO
 79SET QUOTED_IDENTIFIER ON
 80GO
 81IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JudgeProblem]') AND type in (N'U'))
 82BEGIN
 83CREATE TABLE [dbo].[JudgeProblem](
 84    [ID] [int] IDENTITY(1,1) NOT NULL,
 85    [CourseID] [int] NOT NULL,
 86    [Title] [varchar](1000) NOT NULL,
 87    [Answer] [bit] NOT NULL,
 88 CONSTRAINT [PK_JudgeProblem] PRIMARY KEY CLUSTERED 
 89(
 90    [ID] ASC
 91)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 92) ON [PRIMARY]
 93END
 94GO
 95SET ANSI_NULLS ON
 96GO
 97SET QUOTED_IDENTIFIER ON
 98GO
 99IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MultiProblem]') AND type in (N'U'))
100BEGIN
101CREATE TABLE [dbo].[MultiProblem](
102    [ID] [int] IDENTITY(1,1) NOT NULL,
103    [CourseID] [int] NOT NULL,
104    [Title] [varchar](1000) NOT NULL,
105    [AnswerA] [varchar](500) NOT NULL,
106    [AnswerB] [varchar](500) NOT NULL,
107    [AnswerC] [varchar](500) NOT NULL,
108    [AnswerD] [varchar](500) NOT NULL,
109    [Answer] [varchar](50) NOT NULL,
110 CONSTRAINT [PK_MultiProblem] PRIMARY KEY CLUSTERED 
111(
112    [ID] ASC
113)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
114) ON [PRIMARY]
115END
116GO
117SET ANSI_NULLS ON
118GO
119SET QUOTED_IDENTIFIER ON
120GO
121IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FillBlankProblem]') AND type in (N'U'))
122BEGIN
123CREATE TABLE [dbo].[FillBlankProblem](
124    [ID] [int] IDENTITY(1,1) NOT NULL,
125    [CourseID] [int] NOT NULL,
126    [FrontTitle] [varchar](500) NULL,
127    [BackTitle] [varchar](500) NULL,
128    [Answer] [varchar](200) NOT NULL,
129 CONSTRAINT [PK_FillBlankProblem] PRIMARY KEY CLUSTERED 
130(
131    [ID] ASC
132)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
133) ON [PRIMARY]
134END
135GO
136SET ANSI_NULLS ON
137GO
138SET QUOTED_IDENTIFIER ON
139GO
140IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Paper]') AND type in (N'U'))
141BEGIN
142CREATE TABLE [dbo].[Paper](
143    [PaperID] [int] IDENTITY(1,1) NOT NULL,
144    [CourseID] [int] NOT NULL,
145    [PaperName] [varchar](200) NOT NULL,
146    [PaperState] [bit] NOT NULL,
147 CONSTRAINT [PK_Paper] PRIMARY KEY CLUSTERED 
148(
149    [PaperID] ASC
150)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
151) ON [PRIMARY]
152END
153GO
154
155SET ANSI_NULLS ON
156GO
157
158SET QUOTED_IDENTIFIER ON
159GO
160
161CREATE TRIGGER [Tr_Paper_Insert] ON [dbo].[Paper] FOR INSERT AS SELECT @@IDENTITY
162GO
163SET ANSI_NULLS ON
164GO
165SET QUOTED_IDENTIFIER ON
166GO
167IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_PaperDetail]') AND type in (N'P', N'PC'))
168BEGIN
169EXEC dbo.sp_executesql @statement = N'
170
171
172CREATE PROCEDURE [dbo].[Proc_PaperDetail]
173        (@PaperID        [int],
174         @Type            [varchar](10))
175AS
176begin 
177    declare @sql nvarchar(1000)
178if @Type=''单选题''
179 begin    
180    set @sql=''select * from PaperDetail,SingleProblem where [Type]=''''单选题'''' and PaperDetail.TitleID=SingleProblem.ID and [PaperID]= ''+Cast(@PaperID AS varchar(10)) 
181    exec sp_executesql @sql        
182 end
183else if @Type=''多选题''
184 begin    
185    set @sql=''select * from PaperDetail,MultiProblem where [Type]=''''多选题'''' and PaperDetail.TitleID=MultiProblem.ID and [PaperID]='' + +Cast(@PaperID AS varchar(10))
186    exec sp_executesql @sql        
187 end
188else if @Type=''判断题''
189 begin    
190    set @sql=''select * from PaperDetail,JudgeProblem where [Type]=''''判断题'''' and PaperDetail.TitleID=JudgeProblem.ID and [PaperID]='' + +Cast(@PaperID AS varchar(10))
191    exec sp_executesql @sql        
192 end
193else
194 begin    
195    set @sql=''select * from PaperDetail,FillBlankProblem where [Type]=''''填空题'''' and PaperDetail.TitleID=FillBlankProblem.ID and [PaperID]='' + +Cast(@PaperID AS varchar(10))
196    exec sp_executesql @sql        
197 end
198end
199
200' 
201END
202GO
203SET ANSI_NULLS ON
204GO
205SET QUOTED_IDENTIFIER ON
206GO
207IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PaperDetail]') AND type in (N'U'))
208BEGIN
209CREATE TABLE [dbo].[PaperDetail](
210    [ID] [int] IDENTITY(1,1) NOT NULL,
211    [PaperID] [int] NOT NULL,
212    [Type] [varchar](10) NOT NULL,
213    [TitleID] [int] NOT NULL,
214    [Mark] [int] NOT NULL,
215 CONSTRAINT [PK_ExamPaper] PRIMARY KEY CLUSTERED 
216(
217    [ID] ASC
218)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
219) ON [PRIMARY]
220END
221GO
222SET ANSI_NULLS ON
223GO
224SET QUOTED_IDENTIFIER ON
225GO
226IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_UsersDetail]') AND type in (N'P', N'PC'))
227BEGIN
228EXEC dbo.sp_executesql @statement = N'
229
230create PROCEDURE [dbo].[Proc_UsersDetail]
231    @UserID    [varchar](50)
232AS SELECT     [dbo].[Users].[UserID], 
233        [dbo].[Users].[UserName],
234        [dbo].[Users].[UserPwd], 
235        [dbo].[Users].[UserPower]        
236        
237        
238FROM         [dbo].[Users]
239WHERE UserID=@UserID
240' 
241END
242GO
243SET ANSI_NULLS ON
244GO
245SET QUOTED_IDENTIFIER ON
246GO
247IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_ScoreList]') AND type in (N'P', N'PC'))
248BEGIN
249EXEC dbo.sp_executesql @statement = N'
250
251
252
253CREATE PROCEDURE [dbo].[Proc_ScoreList]
254AS SELECT     [dbo].[Users].[UserID],         
255        [dbo].[Users].[UserName],
256        [dbo].[Score].[ID],
257        [dbo].[Score].[Score],
258        [dbo].[Score].[ExamTime],
259        [dbo].[Paper].[PaperName]
260FROM         [dbo].[Users],[dbo].[Score],[dbo].[Paper]
261where Users.UserID=Score.UserID and Score.PaperID=Paper.PaperID
262' 
263END
264GO
265SET ANSI_NULLS ON
266GO
267SET QUOTED_IDENTIFIER ON
268GO
269IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_UsersAdd]') AND type in (N'P', N'PC'))
270BEGIN
271EXEC dbo.sp_executesql @statement = N'
272
273
274
275create PROCEDURE [dbo].[Proc_UsersAdd]
276    (@UserID         [varchar](20), 
277     @UserName         [varchar](20),
278     @UserPwd         [varchar](20),
279     @UserPower     [int])
280
281AS INSERT INTO [MyOnLineExam].[dbo].[Users] 
282     ([UserID], 
283     [UserName],
284     [UserPwd],
285     [UserPower]) 
286 
287VALUES 
288    ( @UserID,     
289     @UserName,
290     @UserPwd,
291     @UserPower)
292
293' 
294END
295GO
296SET ANSI_NULLS ON
297GO
298SET QUOTED_IDENTIFIER ON
299GO
300IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_UsersDelete]') AND type in (N'P', N'PC'))
301BEGIN
302EXEC dbo.sp_executesql @statement = N'
303
304create PROCEDURE [dbo].[Proc_UsersDelete]
305    (@UserID        [varchar](20))
306