1if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_bob_comment_bob_article]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
  2ALTER TABLE [dbo].[bob_comment] DROP CONSTRAINT FK_bob_comment_bob_article
  3GO
  4
  5if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_bob_article_bob_class]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
  6ALTER TABLE [dbo].[bob_article] DROP CONSTRAINT FK_bob_article_bob_class
  7GO
  8
  9if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bob_article_view]') and OBJECTPROPERTY(id, N'IsView') = 1)
 10drop view [dbo].[bob_article_view]
 11GO
 12
 13if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bob_class_view]') and OBJECTPROPERTY(id, N'IsView') = 1)
 14drop view [dbo].[bob_class_view]
 15GO
 16
 17if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bob_admin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 18drop table [dbo].[bob_admin]
 19GO
 20
 21if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bob_article]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 22drop table [dbo].[bob_article]
 23GO
 24
 25if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bob_class]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 26drop table [dbo].[bob_class]
 27GO
 28
 29if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bob_comment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 30drop table [dbo].[bob_comment]
 31GO
 32
 33if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bob_pop]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 34drop table [dbo].[bob_pop]
 35GO
 36
 37CREATE TABLE [dbo].[bob_admin] (
 38    [admin_id] [int] IDENTITY (1, 1) NOT NULL ,
 39    [admin_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 40    [admin_pwd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
 41) ON [PRIMARY]
 42GO
 43
 44CREATE TABLE [dbo].[bob_article] (
 45    [aid] [int] IDENTITY (1, 1) NOT NULL ,
 46    [cid] [int] NOT NULL ,
 47    [title] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 48    [content] [text] COLLATE Chinese_PRC_CI_AS NOT NULL ,
 49    [posttime] [datetime] NULL 
 50) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 51GO
 52
 53CREATE TABLE [dbo].[bob_class] (
 54    [cid] [int] IDENTITY (1, 1) NOT NULL ,
 55    [cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 56    [description] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
 57) ON [PRIMARY]
 58GO
 59
 60CREATE TABLE [dbo].[bob_comment] (
 61    [comid] [int] IDENTITY (1, 1) NOT NULL ,
 62    [aid] [int] NOT NULL ,
 63    [comment] [text] COLLATE Chinese_PRC_CI_AS NOT NULL ,
 64    [cposttime] [datetime] NULL 
 65) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 66GO
 67
 68CREATE TABLE [dbo].[bob_pop] (
 69    [pid] [int] IDENTITY (1, 1) NOT NULL ,
 70    [pname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 71    [pmail] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 72    [pcontent] [text] COLLATE Chinese_PRC_CI_AS NOT NULL ,
 73    [posttime] [datetime] NULL ,
 74    [sex] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 75    [adminRev] [text] COLLATE Chinese_PRC_CI_AS NULL 
 76) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 77GO
 78
 79SET QUOTED_IDENTIFIER ON 
 80GO
 81SET ANSI_NULLS ON 
 82GO
 83
 84
 85
 86--分类信息，以及每个分类下的文章数
 87create view bob_class_view
 88as
 89select c.*,a.articlecount as articlecount from bob_class c
 90inner join (select c.cid,count(a.cid) articlecount from bob_class c
 91left join bob_article a on c.cid=a.cid group by c.cid) a on c.cid=a.cid
 92
 93GO
 94SET QUOTED_IDENTIFIER OFF 
 95GO
 96SET ANSI_NULLS ON 
 97GO
 98
 99SET QUOTED_IDENTIFIER ON 
100GO
101SET ANSI_NULLS ON 
102GO
103
104
105
106--文章所有--包括文章所在的分类名，和文章的评论数
107
108
109create view bob_article_view
110as
111select a.*,c.cname,cmt.countcomment from bob_article a
112inner join bob_class c on a.cid=c.cid 
113left join (select a.aid,count(c.comid) as countcomment from bob_article a 
114left join bob_comment c on a.aid=c.aid group by a.aid) cmt on a.aid=cmt.aid
115
116
117GO
118SET QUOTED_IDENTIFIER OFF 
119GO
120SET ANSI_NULLS ON 
121GO
122
123