db.sql

JSP_博客网站系统,功能完整。学习JSP的好资料。开发时采用Eclipse+MyEclipse结合Tomcat部署。

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[H_BLOG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[H_BLOG]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[H_CATEGORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[H_CATEGORY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[H_IMAGE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[H_IMAGE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[H_REMARK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[H_REMARK]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[H_SPACELOG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[H_SPACELOG]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[H_SYSUSER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[H_SYSUSER]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[H_USER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[H_USER]
GO

CREATE TABLE [dbo].[H_BLOG] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[publishTime] [datetime] NOT NULL ,
	[content] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[userid] [int] NOT NULL ,
	[categoryid] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[H_CATEGORY] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[categoryName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[userid] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[H_IMAGE] (
	[id] [int] NOT NULL ,
	[imagePath] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[userid] [int] NOT NULL ,
	[isIcon] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[H_REMARK] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[nickName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[content] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[blogid] [int] NOT NULL ,
	[publishTime] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[H_SPACELOG] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[userid] [int] NOT NULL ,
	[spaceName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[accessTime] [datetime] NOT NULL ,
	[accessIp] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[H_SYSUSER] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[userName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[userPass] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[H_USER] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[userName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[userPass] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[nickName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[email] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[birthday] [datetime] NULL ,
	[city] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[regDate] [datetime] NOT NULL ,
	[updateTime] [datetime] NOT NULL ,
	[spaceName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[spaceHit] [int] NOT NULL ,
	[icon] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[isCommend] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[H_BLOG] WITH NOCHECK ADD 
	CONSTRAINT [PK_H_BLOG] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[H_CATEGORY] WITH NOCHECK ADD 
	CONSTRAINT [PK_H_CATEGORY] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[H_REMARK] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_REMARK] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[H_SPACELOG] WITH NOCHECK ADD 
	CONSTRAINT [PK_H_BLOGLOG] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[H_SYSUSER] WITH NOCHECK ADD 
	CONSTRAINT [PK_T_SYSUSER] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[H_USER] WITH NOCHECK ADD 
	CONSTRAINT [PK_H_USER] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[H_IMAGE] ADD 
	CONSTRAINT [DF_H_IMAGE_is] DEFAULT (0) FOR [isIcon]
GO

ALTER TABLE [dbo].[H_REMARK] ADD 
	CONSTRAINT [DF_T_REMARK_nickName] DEFAULT ('匆匆的过客') FOR [nickName]
GO

ALTER TABLE [dbo].[H_SYSUSER] ADD 
	CONSTRAINT [IX_H_SYSUSER] UNIQUE  NONCLUSTERED 
	(
		[userName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[H_USER] ADD 
	CONSTRAINT [DF_H_USER_spaceHit] DEFAULT (0) FOR [spaceHit],
	CONSTRAINT [DF_H_USER_isCommend] DEFAULT (0) FOR [isCommend],
	CONSTRAINT [IX_H_USER] UNIQUE  NONCLUSTERED 
	(
		[userName]
	)  ON [PRIMARY] 
GO