数据库课程设计----图书馆管理系统

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 �