您目前尚未登陆，请选择【登陆】或【注册】
首页->行政办公->个人图书管理系统源码>>DB_51aspx/MyLibrary.sql>>源码在线查看
温馨提示：代码在线浏览功能只能做为源码浏览参考，如果想更进一步了解该代码请下载：个人图书管理系统源码
当前文件：文件类型 MyLibary/DB_51aspx/MyLibrary.sql，打开代码结构图
高亮显示
		            
---该语句仅仅适用于Sql2005下执行，Sql2000可以直接附件DB_51aspx下的数据库文件
CREATE DATABASE MyLibrary
GO

USE MyLibrary
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Power]') AND type in (N'U'))
BEGIN
CREATE TABLE [Power](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Power] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookDelete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_BookDelete]
	(@BookID	[int])
AS DELETE [MyLibrary].[dbo].[Book] 
WHERE 
	( [BookID]	 = @BookID)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_BookList]
AS SELECT 	*
FROM [dbo].[Book]
ORDER BY BookID' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookModify]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_BookModify]
	(@BookID	 		[int],
	 @BookName 			[varchar](50),
	 @BookISBN 			[varchar](50),	 
	 @BookAuthor 		[varchar](50),
	 @BookPublish 		[varchar](50),
	 @BookPublishDate 	[datetime],
	 @BookClassify 		[varchar](50),
	 @BookSubject 		[varchar](50),
	 @BookIndex 		[varchar](50),
	 @BookPrice 		[money],
	 @BookPageNum 		[int],
	 @BookSeries 		[varchar](50),
	 @BookDescription 	[text])

AS UPDATE [MyLibrary].[dbo].[Book] 

SET  [BookName]		=@BookName,
	 [BookISBN]		=@BookISBN,	 
	 [BookAuthor]	=@BookAuthor,
	 [BookPublish]	=@BookPublish,
	 [BookPublishDate]=@BookPublishDate,
	 [BookClassify]	=@BookClassify,
	 [BookSubject]	=@BookSubject,
	 [BookIndex]	=@BookIndex,
	 [BookPrice]	=@BookPrice,
	 [BookPageNum]	=@BookPageNum,
	 [BookSeries]	=@BookSeries,
	 [BookDescription]=@BookDescription
WHERE 
	( [BookID]	 = @BookID)
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookDetail]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_BookDetail]
	@BookID	[int]
AS SELECT 	*
FROM [dbo].[Book]
where BookID=@BookID' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Book]') AND type in (N'U'))
BEGIN
CREATE TABLE [Book](
	[BookID] [int] IDENTITY(1,1) NOT NULL,
	[BookName] [varchar](50) NOT NULL,
	[BookISBN] [varchar](50) NOT NULL,
	[BookAuthor] [varchar](50) NOT NULL,
	[BookPublish] [varchar](50) NOT NULL,
	[BookPublishDate] [datetime] NOT NULL,
	[BookClassify] [varchar](50) NOT NULL,
	[BookSubject] [varchar](50) NOT NULL,
	[BookIndex] [varchar](50) NOT NULL,
	[BookPrice] [money] NOT NULL,
	[BookPageNum] [int] NOT NULL,
	[BookSeries] [varchar](50) NULL,
	[BookDescription] [text] NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 
(
	[BookID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookConditionQuery]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [Proc_BookConditionQuery]
	(@Classify	 	[varchar](50),
	 @Type 			[varchar](10),
	 @Content 		[varchar](50),	 
	 @Mode 		    [int]
	 )
AS
begin
 declare @sql nvarchar(1000) --生成的sql语句

 
if @Classify=''所有书刊''
 begin
	if @Mode=1
		begin
			set @sql=''select * from Book where ''+@Type+'' like ''''''+@Content+''%''''''
			exec sp_executesql @sql		
		end

	else
		begin
			set @sql=''select * from Book where ''+@Type+'' like ''''%''+@Content+''%''''''
			exec sp_executesql @sql
		end
 end
else
 begin
	if @Mode=1
		begin
			set @sql=''select * from Book where [BookClassify]=''''''+@Classify+'''''' and ''+@Type+'' like ''''''+@Content+''%''''''
			exec sp_executesql @sql
		end
	else
		begin
			set @sql=''select * from Book where [BookClassify]=''''''+@Classify+'''''' and ''+@Type+'' like ''''%''+@Content+''%''''''
			exec sp_executesql @sql
		end
 end
end
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Borrow]') AND type in (N'U'))
BEGIN
CREATE TABLE [Borrow](
	[BorrowID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [varchar](50) NOT NULL,
	[BookID] [int] NOT NULL,
	[BorrowBeginDate] [datetime] NOT NULL,
	[BorrowEndDate] [datetime] NOT NULL,
	[BorrowState] [int] NOT NULL,
 CONSTRAINT [PK_Borrow] PRIMARY KEY CLUSTERED 
(
	[BorrowID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [Users](
	[UserID] [varchar](50) NOT NULL,
	[UserPassword] [varchar](50) NOT NULL,
	[UserPower] [int] NOT NULL,
	[UserName] [varchar](50) NOT NULL,
	[UserSex] [bit] NOT NULL,
	[UserDepart] [varchar](50) NOT NULL,
	[UserTelephone] [varchar](50) NULL,
	[UserEMail] [varchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_PowerList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

create PROCEDURE [Proc_PowerList]
AS SELECT 	[dbo].[Power].[ID], 
		[dbo].[Power].[Name]		
FROM 		[dbo].[Power]' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_UsersList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [Proc_UsersList]
AS SELECT 	[dbo].[Users].[UserID], 
		[dbo].[Power].[Name], 
		[dbo].[Users].[UserName], 
		[dbo].[Users].[UserSex], 
		[dbo].[Users].[UserDepart], 		 
		[dbo].[Users].[UserTelephone],
		[dbo].[Users].[UserEMail] 
FROM 		[dbo].[Users],[dbo].[Power]
where [dbo].[Users].[UserPower]=[dbo].[Power].[ID]
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BookAdd]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [Proc_BookAdd]
	(@BookName 			[varchar](50),
	 @BookISBN 			[varchar](50),	 
	 @BookAuthor 		[varchar](50),
	 @BookPublish 		[varchar](50),
	 @BookPublishDate 	[datetime],
	 @BookClassify 		[varchar](50),
	 @BookSubject 		[varchar](50),
	 @BookIndex 		[varchar](50),
	 @BookPrice 		[money],
	 @BookPageNum 		[int],
	 @BookSeries 		[varchar](50),
	 @BookDescription 	[text])

AS INSERT INTO [MyLibrary].[dbo].[Book] 
	 ([BookName],
	 [BookISBN],	 
	 [BookAuthor],
	 [BookPublish],
	 [BookPublishDate],
	 [BookClassify],
	 [BookSubject],
	 [BookIndex],
	 [BookPrice],
	 [BookPageNum],
	 [BookSeries],
	 [BookDescription]) 
 
VALUES 
	(@BookName,
	 @BookISBN,	 
	 @BookAuthor,
	 @BookPublish,
	 @BookPublishDate,
	 @BookClassify,
	 @BookSubject,
	 @BookIndex,
	 @BookPrice,
	 @BookPageNum,
	 @BookSeries,
	 @BookDescription)

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_ReaderCurrentBorrow]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [Proc_ReaderCurrentBorrow]
	@UserID VARCHAR(50)
AS SELECT 	[dbo].[Borrow].[UserID],		 
		[dbo].[Borrow].[BorrowBeginDate], 
		[dbo].[Borrow].[BorrowEndDate], 
		[dbo].[Borrow].[BorrowState],		 
		[dbo].[Book].[BookName],
		[dbo].[Book].[BookAuthor], 
		[dbo].[Book].[BookPublish],
		[dbo].[Book].[BookPublishDate]
FROM 	[dbo].[Borrow], 
		[dbo].[Book]
WHERE 	book.BookID=borrow.BookID 
		AND BorrowState=0  
		AND UserID=@UserID
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_ReaderBorrowHistory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_ReaderBorrowHistory]
	@UserID VARCHAR(50)
AS SELECT 	[dbo].[Borrow].[UserID], 
		[dbo].[Borrow].[BookID], 
		[dbo].[Borrow].[BorrowBeginDate], 
		[dbo].[Borrow].[BorrowEndDate], 
		[dbo].[Borrow].[BorrowState], 
		[dbo].[Book].[BookID], 
		[dbo].[Book].[BookName], 
		[dbo].[Book].[BookAuthor],
		[dbo].[Book].[BookPublish],
		[dbo].[Book].[BookPublishDate]
FROM 		[dbo].[Borrow], 
		[dbo].[Book]
WHERE 	Book.BookID=Borrow.BookID 
		AND BorrowState=1  
		AND UserID=@UserID' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_ReaderExpireCuiHuan]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create PROCEDURE [Proc_ReaderExpireCuiHuan]
	@UserID VARCHAR(50)
AS SELECT 	[dbo].[Borrow].[UserID], 		
		[dbo].[Borrow].[BorrowBeginDate], 
		[dbo].[Borrow].[BorrowEndDate], 
		[dbo].[Borrow].[BorrowState], 
		[dbo].[Book].[BookID], 
		[dbo].[Book].[BookName], 
		[dbo].[Book].[BookPublish]
FROM 	[dbo].[Borrow], 
		[dbo].[Book]
WHERE 	book.BookID=borrow.bookid 
		AND BorrowEndDate<GetDate() 
		AND BorrowState=0  
		AND UserID=@UserID' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_ReaderCurrentBorrowCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


create PROCEDURE [Proc_ReaderCurrentBorrowCount]
	@UserID VARCHAR(50)
AS SELECT 	booknum=Count(*)
FROM 	[dbo].[Borrow], 
		[dbo].[Book]
WHERE 	book.BookID=borrow.BookID 
		AND BorrowState=0  
		AND UserID=@UserID' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_BorrowAdd]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_BorrowAdd]
	(@UserID	 		[varchar](50),
	 @BookID	 		[int],
	 @BorrowBeginDate 	[datetime],
	 @BorrowEndDate 	[datetime],
	 @BorrowState 		[int])

AS INSERT INTO [MyLibrary].[dbo].[Borrow] 
	 ( [UserID],
	 [BookID],
	 [BorrowBeginDate],
	 [BorrowEndDate],
	 [BorrowState]) 
 
VALUES 
	( @UserID,
	 @BookID,
	 @BorrowBeginDate,
	 @BorrowEndDate,
	 @BorrowState)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_ReturnBook]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_ReturnBook]
	(@BookID	 	[int],	 
	 @BorrowState 	[int])

AS UPDATE [MyLibrary].[dbo].[Borrow] 

SET  [BorrowEndDate]=GetDate(),
[BorrowState]	 = @BorrowState

WHERE 
	( [BookID]	 = @BookID and BorrowState = 0)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_UsersAdd]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [Proc_UsersAdd]
	(@UserID	 	[varchar](50),
	 @UserPassword 	[varchar](50),
	 @UserPower 	[int],
	 @UserName 		[varchar](50),
	 @UserSex 		[bit],
	 @UserDepart 	[varchar](50),
	 @UserTelephone [varchar](50),
	 @UserEMail 	[varchar](50))

AS INSERT INTO [MyLibrary].[dbo].[Users] 
	 ([UserID],
	 [UserPassword],
	 [UserPower],
	 [UserName],
	 [UserSex],
	 [UserDepart],
	 [UserTelephone],
	 [UserEMail]) 
 
VALUES 
	( @UserID,
	 @UserPassword,
	 @UserPower,
	 @UserName,
	 @UserSex,
	 @UserDepart,
	 @UserTelephone,
	 @UserEMail)
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_UsersDelete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_UsersDelete]
	(@UserID		[varchar](50))
AS DELETE [MyLibrary].[dbo].[Users] 
WHERE 
	( [UserID]	 = @UserID)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_UsersDetail]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_UsersDetail]
	@UserID	[varchar](50)
AS SELECT 	[dbo].[Users].[UserID], 
		[dbo].[Users].[UserPassword], 
		[dbo].[Users].[UserPower],		
		[dbo].[Users].[UserName], 
		[dbo].[Users].[UserSex], 
		[dbo].[Users].[UserDepart], 
		[dbo].[Users].[UserTelephone], 
		[dbo].[Users].[UserEMail]
FROM 		[dbo].[Users]
WHERE UserID=@UserID' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_UsersModify]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_UsersModify]
	(@UserID	 	[varchar](50),
	 @UserPower 	[int],
	 @UserName 		[varchar](50),
	 @UserDepart 	[varchar](50),
	 @UserTelephone [varchar](50),
	 @UserEMail 	[varchar](50))

AS UPDATE [MyLibrary].[dbo].[Users] 

SET  [UserPower]	= @UserPower,
	 [UserName]		= @UserName,
	 [UserDepart]	= @UserDepart,
	 [UserTelephone]= @UserTelephone,
	 [UserEMail]	= @UserEMail 

WHERE 
	( [UserID]	 = @UserID)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_UsersPasswordModify]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [Proc_UsersPasswordModify]
	(@UserID	 	[varchar](50),
	 @UserPassword	[varchar](50))

AS UPDATE [MyLibrary].[dbo].[Users] 

SET  [UserPassword]	 = @UserPassword

WHERE 
	( [UserID]	 = @UserID)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Proc_UsersMessageModify]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


create PROCEDURE [Proc_UsersMessageModify]
	(@UserID	 	[varchar](50),
	 @UserTelephone	[varchar](50),
	 @UserEMail		[varchar](50))

AS UPDATE [MyLibrary].[dbo].[Users] 

SET  [UserTelephone]	 = @UserTelephone,
	 [UserEMail]		 = @UserEMail

WHERE 
	( [UserID]	 = @UserID)
' 
END
还没有找到您心仪的内容？请用.net源码大搜捕
代码片断 打包下载该项目完整源码：个人图书管理系统源码
51Aspx.com 版权所有 CopyRight © 2000-2008. 京ICP备06046876号