当前文件路径：MyLibary/DB_51aspx/MyLibrary.sql 
1
2---该语句仅仅适用于Sql2005下执行，Sql2000可以直接附件DB_51aspx下的数据库文件
3CREATE DATABASE MyLibrary
4GO
5
6USE MyLibrary
7GO
8
9SET ANSI_NULLS ON
10GO
11SET QUOTED_IDENTIFIER ON
12GO
13IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Power]') AND type in (N'U'))
14BEGIN
15CREATE TABLE [Power](
16 [ID] [int] NOT NULL,
17 [Name] [varchar](50) NOT NULL,
18 CONSTRAINT [PK_Power] PRIMARY KEY CLUSTERED
19(
20 [ID] ASC
21)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
22) ON [PRIMARY]
23END
24GO
25SET ANSI_NULLS ON
26GO
27SET QUOTED_IDENTIFIER ON
28GO
29IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookDelete]') AND type in (N'P', N'PC'))
30BEGIN
31EXEC dbo.sp_executesql @statement = N'
32create PROCEDURE [Proc_BookDelete]
33 (@BookID [int])
34AS DELETE [MyLibrary].[dbo].[Book]
35WHERE
36 ( [BookID] = @BookID)'
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'[Proc_BookList]') AND type in (N'P', N'PC'))
44BEGIN
45EXEC dbo.sp_executesql @statement = N'
46create PROCEDURE [Proc_BookList]
47AS SELECT *
48FROM [dbo].[Book]
49ORDER BY BookID'
50END
51GO
52SET ANSI_NULLS ON
53GO
54SET QUOTED_IDENTIFIER ON
55GO
56IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookModify]') AND type in (N'P', N'PC'))
57BEGIN
58EXEC dbo.sp_executesql @statement = N'
59create PROCEDURE [Proc_BookModify]
60 (@BookID [int],
61 @BookName [varchar](50),
62 @BookISBN [varchar](50),
63 @BookAuthor [varchar](50),
64 @BookPublish [varchar](50),
65 @BookPublishDate [datetime],
66 @BookClassify [varchar](50),
67 @BookSubject [varchar](50),
68 @BookIndex [varchar](50),
69 @BookPrice [money],
70 @BookPageNum [int],
71 @BookSeries [varchar](50),
72 @BookDescription [text])
73
74AS UPDATE [MyLibrary].[dbo].[Book]
75
76SET [BookName] =@BookName,
77 [BookISBN] =@BookISBN,
78 [BookAuthor] =@BookAuthor,
79 [BookPublish] =@BookPublish,
80 [BookPublishDate]=@BookPublishDate,
81 [BookClassify] =@BookClassify,
82 [BookSubject] =@BookSubject,
83 [BookIndex] =@BookIndex,
84 [BookPrice] =@BookPrice,
85 [BookPageNum] =@BookPageNum,
86 [BookSeries] =@BookSeries,
87 [BookDescription]=@BookDescription
88WHERE
89 ( [BookID] = @BookID)
90'
91END
92GO
93SET ANSI_NULLS ON
94GO
95SET QUOTED_IDENTIFIER ON
96GO
97IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookDetail]') AND type in (N'P', N'PC'))
98BEGIN
99EXEC dbo.sp_executesql @statement = N'
100create PROCEDURE [Proc_BookDetail]
101 @BookID [int]
102AS SELECT *
103FROM [dbo].[Book]
104where BookID=@BookID'
105END
106GO
107SET ANSI_NULLS ON
108GO
109SET QUOTED_IDENTIFIER ON
110GO
111IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Book]') AND type in (N'U'))
112BEGIN
113CREATE TABLE [Book](
114 [BookID] [int] IDENTITY(1,1) NOT NULL,
115 [BookName] [varchar](50) NOT NULL,
116 [BookISBN] [varchar](50) NOT NULL,
117 [BookAuthor] [varchar](50) NOT NULL,
118 [BookPublish] [varchar](50) NOT NULL,
119 [BookPublishDate] [datetime] NOT NULL,
120 [BookClassify] [varchar](50) NOT NULL,
121 [BookSubject] [varchar](50) NOT NULL,
122 [BookIndex] [varchar](50) NOT NULL,
123 [BookPrice] [money] NOT NULL,
124 [BookPageNum] [int] NOT NULL,
125 [BookSeries] [varchar](50) NULL,
126 [BookDescription] [text] NULL,
127 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED
128(
129 [BookID] ASC
130)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
131) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
132END
133GO
134SET ANSI_NULLS ON
135GO
136SET QUOTED_IDENTIFIER ON
137GO
138IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookConditionQuery]') AND type in (N'P', N'PC'))
139BEGIN
140EXEC dbo.sp_executesql @statement = N'
141
142CREATE PROCEDURE [Proc_BookConditionQuery]
143 (@Classify [varchar](50),
144 @Type [varchar](10),
145 @Content [varchar](50),
146 @Mode [int]
147 )
148AS
149begin
150 declare @sql nvarchar(1000) --生成的sql语句
151
152
153if @Classify=''所有书刊''
154 begin
155 if @Mode=1
156 begin
157 set @sql=''select * from Book where ''+@Type+'' like ''''''+@Content+''%''''''
158 exec sp_executesql @sql
159 end
160
161 else
162 begin
163 set @sql=''select * from Book where ''+@Type+'' like ''''%''+@Content+''%''''''
164 exec sp_executesql @sql
165 end
166 end
167else
168 begin
169 if @Mode=1
170 begin
171 set @sql=''select * from Book where [BookClassify]=''''''+@Classify+'''''' and ''+@Type+'' like ''''''+@Content+''%''''''
172 exec sp_executesql @sql
173 end
174 else
175 begin
176 set @sql=''select * from Book where [BookClassify]=''''''+@Classify+'''''' and ''+@Type+'' like ''''%''+@Content+''%''''''
177 exec sp_executesql @sql
178 end
179 end
180end
181'
182END
183GO
184SET ANSI_NULLS ON
185GO
186SET QUOTED_IDENTIFIER ON
187GO
188IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Borrow]') AND type in (N'U'))
189BEGIN
190CREATE TABLE [Borrow](
191 [BorrowID] [int] IDENTITY(1,1) NOT NULL,
192 [UserID] [varchar](50) NOT NULL,
193 [BookID] [int] NOT NULL,
194 [BorrowBeginDate] [datetime] NOT NULL,
195 [BorrowEndDate] [datetime] NOT NULL,
196 [BorrowState] [int] NOT NULL,
197 CONSTRAINT [PK_Borrow] PRIMARY KEY CLUSTERED
198(
199 [BorrowID] ASC
200)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
201) ON [PRIMARY]
202END
203GO
204SET ANSI_NULLS ON
205GO
206SET QUOTED_IDENTIFIER ON
207GO
208IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Users]') AND type in (N'U'))
209BEGIN
210CREATE TABLE [Users](
211 [UserID] [varchar](50) NOT NULL,
212 [UserPassword] [varchar](50) NOT NULL,
213 [UserPower] [int] NOT NULL,
214 [UserName] [varchar](50) NOT NULL,
215 [UserSex] [bit] NOT NULL,
216 [UserDepart] [varchar](50) NOT NULL,
217 [UserTelephone] [varchar](50) NULL,
218 [UserEMail] [varchar](50) NULL,
219 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
220(
221 [UserID] ASC
222)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
223) ON [PRIMARY]
224END
225GO
226SET ANSI_NULLS ON
227GO
228SET QUOTED_IDENTIFIER ON
229GO
230IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_PowerList]') AND type in (N'P', N'PC'))
231BEGIN
232EXEC dbo.sp_executesql @statement = N'
233
234create PROCEDURE [Proc_PowerList]
235AS SELECT [dbo].[Power].[ID],
236 [dbo].[Power].[Name]
237FROM [dbo].[Power]'
238END
239GO
240SET ANSI_NULLS ON
241GO
242SET QUOTED_IDENTIFIER ON
243GO
244IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_UsersList]') AND type in (N'P', N'PC'))
245BEGIN
246EXEC dbo.sp_executesql @statement = N'
247
248CREATE PROCEDURE [Proc_UsersList]
249AS SELECT [dbo].[Users].[UserID],
250 [dbo].[Power].[Name],
251 [dbo].[Users].[UserName],
252 [dbo].[Users].[UserSex],
253 [dbo].[Users].[UserDepart],
254 [dbo].[Users].[UserTelephone],
255 [dbo].[Users].[UserEMail]
256FROM [dbo].[Users],[dbo].[Power]
257where [dbo].[Users].[UserPower]=[dbo].[Power].[ID]
258'
259END
260GO
261SET ANSI_NULLS ON
262GO
263SET QUOTED_IDENTIFIER ON
264GO
265IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookAdd]') AND type in (N'P', N'PC'))
266BEGIN
267EXEC dbo.sp_executesql @statement = N'
268
269CREATE PROCEDURE [Proc_BookAdd]
270 (@BookName [varchar](50),
271 @BookISBN [varchar](50),
272 @BookAuthor [varchar](50),
273 @BookPublish [varchar](50),
274 @BookPublishDate [datetime],
275 @BookClassify [varchar](50),
276 @BookSubject [varchar](50),
277 @BookIndex [varchar](50),
278 @BookPrice [money],
279 @BookPageNum [int],
280 @BookSeries [varchar](50),
281 @BookDescription [text])
282
283AS INSERT INTO [MyLibrary].[dbo].[Book]
284 ([BookName],
285 [BookISBN],
286 [BookAuthor],
287 [BookPublish],
288 [BookPublishDate],
289 [BookClassify],
290 [BookSubject],
291 [BookIndex],
292 [BookPrice],
293 [BookPageNum],
294 [BookSeries],
295 [BookDescription])
296
297VALUES
298 (@BookName,
299 @BookISBN,
300 @BookAuthor,
301 @BookPublish,
302 @BookPublishDate,
303 @BookClassify,
304 @BookSubject,
305 @BookIndex,
306 @BookPrice,
307 @BookPageNum,
308 @BookSeries,
309 @BookDescription)
310
311'
312END
313GO
314SET ANSI_NULLS ON
315GO
316SET QUOTED_IDENTIFIER ON
317GO
318IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_ReaderCurrentBorrow]') AND type in (N'P', N'PC'))
319BEGIN
320EXEC dbo.sp_executesql @statement = N'
321
322CREATE PROCEDURE [Proc_ReaderCurrentBorrow]
323 @UserID VARCHAR(50)
324AS SELECT [dbo].[Borrow].[UserID],
325 [dbo].[Borrow].[BorrowBeginDate],
326 [dbo].[Borrow].[BorrowEndDate],
327 [dbo].[Borrow].[BorrowState],
328 [dbo].[Book].[BookName],
329 [dbo].[Book].[BookAuthor],
330 [dbo].[Book].[BookPublish],
331 [dbo].[Book].[BookPublishDate]
332FROM [dbo].[Borrow],
333 [dbo].[Book]
334WHERE book.BookID=borrow.BookID
335 AND BorrowState=0
336 AND UserID=@UserID
337'
338END
339GO
340SET ANSI_NULLS ON
341GO
342SET QUOTED_IDENTIFIER ON
343GO
344IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_ReaderBorrowHistory]') AND type in (N'P', N'PC'))
345BEGIN
346EXEC dbo.sp_executesql @statement = N'
347create PROCEDURE [Proc_ReaderBorrowHistory]
348 @UserID VARCHAR(50)
349AS SELECT [dbo].[Borrow].[UserID],
350 [dbo].[Borrow].[BookID],
351 [dbo].[Borrow].[BorrowBeginDate],
352 [dbo].[Borrow].[BorrowEndDate],
353 [dbo].[Borrow].[BorrowState],
354 [dbo].[Book].[BookID],
355 [dbo].[Book].[BookName],
356 [dbo].[Book].[BookAuthor],
357 [dbo].[Book].[BookPublish],
358 [dbo].[Book].[BookPublishDate]
359FROM [dbo].[Borrow],
360 [dbo].[Book]
361WHERE Book.BookID=Borrow.BookID
362 AND BorrowState=1
363 AND UserID=@UserID'
364END
365GO
366SET ANSI_NULLS ON
367GO
368SET QUOTED_IDENTIFIER ON
369GO
370IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_ReaderExpireCuiHuan]') AND type in (N'P', N'PC'))
371BEGIN
372EXEC dbo.sp_executesql @statement = N'
373Create PROCEDURE [Proc_ReaderExpireCuiHuan]
374 @UserID VARCHAR(50)
375AS SELECT [dbo].[Borrow].[UserID],
376 [dbo].[Borrow].[BorrowBeginDate],
377 [dbo].[Borrow].[BorrowEndDate],
378 [dbo].[Borrow].[BorrowState],
379 [dbo].[Book].[BookID],
380 [dbo].[Book].[BookName],
381 [dbo].[Book].[BookPublish]
382FROM [dbo].[Borrow],
383 [dbo].[Book]
384WHERE book.BookID=borrow.bookid
385 AND BorrowEndDate<GetDate()
386 AND BorrowState=0
387 AND UserID=@UserID'
388END
389GO
390SET ANSI_NULLS ON
391GO
