USE Master
GO
IF EXISTS(Select * from sysdatabases where name='BlogDB')
DROP DATABASE BlogDB
GO
Create Database BlogDB
On
(
Name='BlogDB_MDF.mdf',
FileName='E:/Blog/DB/BlogDB_MDF.mdf',
Size=1 mb,
FileGrowth=10%
)
Log on
(
Name='BlogDB_LDF.ldf',
FileName='E:/Blog/DB/BlogDB_LDF.ldf',
Size=1 mb,
FileGrowth=10%
)
GO
Use BlogDB
GO
--用户表
CREATE TABLE Blogers
(
BID int PRIMARY KEY IDENTITY(1,1) NOT NULL, --用户ID
BLoginName varchar(16) UNIQUE NOT NULL, --登录名
BPassword varchar(150) NOT NULL, --密码
BAuthor varchar(50) , --用户名
BEmail varchar(60) , --电子邮箱
BTitle varchar(100) DEFAULT('我的博客') , --博客标题
BSubTitle varchar(200) DEFAULT('欢迎光临') , --博客副标题
BOriginalCount int DEFAULT(0) NOT NULL, --原创文章数
BReprintCount int DEFAULT(0) NOT NULL, --转载文章数
BCommentCount int DEFAULT(0) NOT NULL, --发表评论数
BRegisterTime datetime DEFAULT(GETDATE()) NOT NULL --注册日期
)
GO
--文章表
CREATE Table Articles
(
AID int PRIMARY KEY IDENTITY(1,1) NOT NULL, --文章ID
ABlogID int NOT NULL, --所属博客ID
ATypeID int NOT NULL, --类型ID
ACategoryID int NOT NULL, --类别ID
ATitle varchar(100) NOT NULL, --文章标题
AContent ntext NOT NULL, --文章内容
ASummary varchar(200), --摘要
APublishTime datetime DEFAULT(GETDATE()) NOT NULL, --发布时间
AFeedBackCount int DEFAULT(0) NOT NULL, --被评论次数
AClickCount int DEFAULT(0) NOT NULL --被点击次数
)
GO
--评论表
CREATE Table Comments
(
CID int PRIMARY KEY IDENTITY(1,1) NOT NULL, --评论ID
CArticleID int NOT NULL, --所评论文章的ID
CBlogID int NOT NULL, --评论者ID
CTitle nvarchar(100), --标题
CContent nvarchar(500) NOT NULL, --内容
CCommentTime datetime NOT NULL --评论时间
)
GO
--类别表
CREATE Table Categories
(
CID int PRIMARY KEY IDENTITY(1,1) NOT NULL, --类别ID
CName nvarchar(50) NOT NULL, --类别名
CBlogID int DEFAULT(-1) NOT NULL --所属博客
)
GO
Insert Into Categories (CName) Values('.Net')
Insert Into Categories (CName) Values('Java')
Insert Into Categories (CName) Values('数据库')
Insert Into Categories (CName) Values('软件工程')
Insert Into Categories (CName) Values('Web开发')
--类型表
CREATE Table Types
(
TID int PRIMARY KEY IDENTITY(1,1) NOT NULL, --类型ID
TName nvarchar(50) NOT NULL, --类型名
TImage nvarchar(50) NOT NULL, --图片路径
)
GO
Insert Into Types (TName) Values('原创')
Insert Into Types (TName) Values('转帖')
Alter Table Articles
Add Constraint FK_ABlogID FOREIGN KEY(ABlogID) REFERENCES Blogers(BID)
Alter Table Articles
Add Constraint FK_ACategoryID FOREIGN KEY(ACategoryID) REFERENCES Categories(CID)
Alter Table Articles
Add Constraint FK_ATypeID FOREIGN KEY(ATypeID) REFERENCES Types(TID)
Alter Table Comments
Add Constraint FK_CArticleID FOREIGN KEY(CArticleID) REFERENCES Articles(AID)
Alter Table Comments
Add Constraint FK_CBlogID FOREIGN KEY (CBlogID) REFERENCES Blogers(BID)
--Trigger
CREATE TRIGGER trig_AddArticleCount
ON Articles
FOR INSERT
AS
DECLARE @BID int
DECLARE @TID int
SELECT @BID=ABlogID,@TID=ATypeID FROM inserted
IF(@TID=1)
Update Blogers Set BOriginalCount=BOriginalCount+1 Where BID=@BID
IF(@TID=2)
Update Blogers Set BReprintCount=BReprintCount+1 Where BID=@BID
GO
CREATE TRIGGER trig_ReduceArticleCount
ON Articles
FOR DELETE
AS
DECLARE @BID int
DECLARE @TID int
SELECT @BID=ABlogID,@TID=ATypeID FROM deleted
IF(@TID=1)
Update Blogers Set BOriginalCount=BOriginalCount-1 Where BID=@BID
IF(@TID=2)
Update Blogers Set BReprintCount=BReprintCount-1 Where BID=@BID
GO
CREATE TRIGGER trig_AddComment
ON Comments
FOR INSERT
AS
DECLARE @ArticleID int
DECLARE @BlogID int
SELECT @ArticleID=CArticleID,@BlogID=CBlogID FROM inserted
Update Blogers Set BCommentCount=BCommentCount+1 Where BID=@BlogID
Update Articles Set AFeedBackCount=AFeedBackCount+1 Where AID=@ArticleID
GO
CREATE TRIGGER trig_ReduceComment
ON Comments
FOR INSERT
AS
DECLARE @ArticleID int
DECLARE @BlogID int
SELECT @ArticleID=CArticleID,@BlogID=CBlogID FROM deleted
Update Blogers Set BCommentCount=BCommentCount-1 Where BID=@BlogID
Update Articles Set AFeedBackCount=AFeedBackCount-1 Where AID=@ArticleID
GO