GO
/*---检查是否已存在bbsDB数据库:查询master数据库中的系统表sysdatabases---*/
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'bbsDB')
DROP DATABASE bbsDB
GOEXEC xp_cmdshell 'mkdir D:\project'  --调用DOS命令创建文件夹/*-----建库--------*/
CREATE DATABASE bbsDB
 ON 
 (
  /*----数据文件的具体描述--*/
  NAME = 'bbsDB_data', --主数据文件的逻辑名
  FILENAME = 'D:\project\bbsDB_data.mdf' , --主数据文件的物理名
  SIZE = 10 MB,  --主数据文件初始大小
  FILEGROWTH = 20%   --主数据文件的增长率
 ) 
 LOG ON 
 (
  /*----日志文件的具体描述,各参数含义同上--*/
  NAME = 'bbsDB_log', 
  FILENAME = 'D:\project\bbsDB_log.ldf' ,
  SIZE = 1MB, 
  MAXSIZE = 20MB,
  FILEGROWTH = 10%
 )
GO/*-----建用户表--------*/
USE bbsDB
GO
/*---检查是否已存在表bbsUsers:查询bbsDB数据库中的系统表sysobjects---*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='bbsUsers')
  DROP TABLE bbsUsers
GO
CREATE TABLE bbsUsers 
(
   UID INT IDENTITY (1,1) NOT NULL , --自动编号,标识列
   Uname VARCHAR(15) NOT NULL ,      --呢称
   Upassword VARCHAR (10)  ,         --密码
   Uemail VARCHAR (20)  ,            --邮件
   Ubirthday DATETIME  ,             --生日
   Usex BIT NOT NULL ,               --性别
   Uclass INT   ,                    --级别(几星级)
   Ure VARCHAR (20) ,           --备注
   UregDate DATETIME NOT NULL ,      --注册日期
   Ustate INT NULL ,                 --状态(是否禁言等)
   Upoint INT NULL                   --积分(点数)

GO
SELECT * FROM bbsUsers
GO/*--------添加约束-------*/
ALTER TABLE bbsUsers ADD CONSTRAINT PK_UID PRIMARY KEY(UID) --主键
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Upassword  DEFAULT (8888) FOR Upassword --初始密码密码为8888
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Usex DEFAULT (1) FOR Usex  --性别默认为男(1)
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Uclass DEFAULT (1) FOR Uclass --级别默认为1星级
ALTER TABLE bbsUsers ADD CONSTRAINT DF_UregDate DEFAULT (getDate( )) FOR UregDate --注册日期默认为当前日期
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Ustate DEFAULT (0) FOR Ustate --状态默认为离线
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Upoint DEFAULT (20) FOR Upoint --默认积分20点
ALTER TABLE bbsUsers ADD CONSTRAINT CK_Uemail CHECK (Uemail LIKE '%@%') --必须包含'@'字符
ALTER TABLE bbsUsers ADD CONSTRAINT CK_Upassword CHECK (LEN(Upassword) >= 6) --至少6位
GO--插入测试数据
INSERT INTO bbsUsers (Uname,Upassword,Uemail,Ubirthday,Ure,Upoint) 
   VALUES ('火云邪神','hxy123','[email protected]', '1988-7-9','快…',20000)
GOselect * from bbsUsers
=======================================
/*新建bbsSection(版块表)表*/
if exists(select * from sysobjects where name = 'bbsSection')
  drop table bbsSection
GO
create table bbsSection
(
SID INT IDENTITY (1, 1) NOT NULL ,
Sname VARCHAR (32) NOT NULL ,
UID INT NOT NULL ,
Sprofile VARCHAR (255) NULL ,
SclickCount INT NULL ,
StopicCount INT NULL 
)
/*bbsSection表约束*/
alter table bbsSection add constraint PK_SID primary key (SID)  --主键约束
alter table bbsSection add constraint DF_SclickCount default(0) for SclickCount --默认约束
alter table bbsSection add constraint DF_StopicCount default(0) for StopicCount --默认约束
alter table bbsSection add constraint FK_SmasterID foreign key (UID) references bbsUsers(UID)=============================================================
/*新建bbsTopic(主贴表)表*/
if exists(select * from sysobjects where name = 'bbsTopic')
  drop table bbsTopic
GO
create table bbsTopic
(
TID INT IDENTITY (1, 1) NOT NULL , --帖子编号
SID INT NOT NULL ,  --版块编号
UID INT NOT NULL ,  --发帖人
TreplyCount INT NULL ,  ---回复数量
Tface INT NULL ,        --发帖表情
Ttopic VARCHAR(20) NOT NULL ,   --标题
Tcontents VARCHAR(30) NOT NULL , --正文
Ttime DATETIME NULL,       --发帖时间
TclickCount INT NULL ,     --点击数
Tstate INT NOT NULL,       --状态
TlastReply DATETIME NULL 
)
/*建bbsTopic约束*/
alter table bbsTopic add constraint PK_TID primary key (TID)  --主键约束
alter table bbsTopic add constraint FK_TsID foreign key (SID) references bbsSection(SID)  --外键约束
alter table bbsTopic add constraint FK_TuID foreign key (UID) references bbsUsers (UID)    --外键约束
alter table bbsTopic add constraint DF_TreplyCount default(0) for TreplyCount   --默认约束
alter table bbsTopic add constraint DF_Ttime default(getDate()) for Ttime          --默认约束
alter table bbsTopic add constraint DF_TclickCount default(0) for TclickCount   --默认约束
alter table bbsTopic add constraint DF_Tstate default(1) for Tstate             --默认约束
alter table bbsTopic add constraint CK_Tcontents check (LEN(Tcontents)>6)       --检查约束
alter table bbsTopic add constraint CK_TlastReply check (TlastReply>Ttime AND TlastReply <= getdate()) --检查约束
alter table bbsTopic add constraint CK_Ttime check (Ttime<=getDate())           ==============================================================
/*建表 bbsReply((回贴表)*/
if exists(select * from sysobjects where name = 'bbsReply')
  drop table bbsReply
GO
create table bbsReply
(
RID INT IDENTITY (1, 1) NOT NULL ,
TID INT NOT NULL ,
SID INT NOT NULL ,
UID INT NOT NULL ,
Rface INT NULL,
Rcontents VARCHAR(30) NOT NULL ,
Rtime DATETIME NULL ,
RclickCount INT NULL 
)
/*建表bbsReply 约束*/
alter table bbsReply add constraint PK_RID primary key (RID)
alter table bbsReply add constraint FK_RtID foreign key (TID) references bbsTopic (TID)
alter table bbsReply add constraint FK_RsID foreign key (SID) references bbsSection (SID)
alter table bbsReply add constraint FK_RuID foreign key (UID) references bbsUsers (UID)
alter table bbsReply add constraint CK_Rcontents check (len(Rcontents)>6)
alter table bbsReply add constraint DF_Rtime default(getdate()) for Rtime=================================================================
USE bbsDB
GOSET NOCOUNT ON
print '--->>>>>>>>>>>>各位大虾注意了,本论坛即将发布年度无记名评奖<<<<<<<<<<<<<<<<<<<<--'
--评估总体人气:如果论坛总点击率>1000,人气较旺
IF (SELECT SUM(SclickCount) FROM bbsSection)>1000 
  print '论坛人气年度评估:熊旺旺,大家辛苦了!'
ELSE
  print '论坛人气年度评估:一般般,大家加油啊!'
--评选品牌版块和倒胃版块:根据主帖数量评估
print '年度品牌版块:'
SELECT 版块名称=Sname,主帖数量=StopicCount,简介=Sprofile FROM bbsSection
   WHERE StopicCount=(SELECT MAX(StopicCount) FROM bbsSection)print '年度倒胃版块:'
SELECT 版块名称=Sname,主帖数量=StopicCount,简介=Sprofile FROM bbsSection
   WHERE StopicCount=(SELECT MIN(StopicCount) FROM bbsSection)--评选回贴人气最旺的前两名作者
print '年度回贴人气最IN的前两名获奖作者:'
SELECT 大名=Uname,星级=Uclass FROM bbsUsers 
  WHERE UID IN (SELECT TOP 2 TuID FROM bbsTopic ORDER BY TclickCount DESC)--评选最差版主:如果存在发帖量为0的板块,列出对应的版块和版主信息
IF EXISTS (SELECT * FROM bbsSection WHERE StopicCount=0 OR SclickCount<=500)
  BEGIN 
    print '请下列版块的斑竹加加油哦!'
    SELECT 版块名称=Sname,主帖数量=StopicCount,点击率=SclickCount FROM bbsSection
       WHERE StopicCount=0 OR SclickCount<=500
 END
GO------------------------阶段2----------------
/*--发主贴:
心酸果冻在.Net技术版块发贴:
怯怯的问:什么是.Net啊?
微软的.Net广告超过半个北京城啊
--*/
DECLARE @userID varchar(10),@sID INT --定义变量存放用户编号和版块编号
SELECT @userID=UID FROM bbsUsers WHERE Uname='心酸果冻' --获取心酸果冻的用户编号
SELECT @sID=SID FROM bbsSection WHERE Sname LIKE '%.Net技术%' ----获取.Net版块的编号
--插入主贴表
INSERT INTO bbsTopic (TsID,TuID,Tface,Ttopic,Tcontents)
  VALUES(@sID,@userID,3,'什么是.Net啊?','微软的.Net广告超过半个北京城啊')
--更新版块表:.Net技术版块主贴数+1
UPDATE bbsSection SET StopicCount=StopicCount+1 WHERE SID=@sID
--更新用户的积分:如果是新主题,则积分增加100,否则增加50
IF NOT EXISTS (SELECT * FROM bbsTopic
      WHERE Ttopic LIKE '什么是.Net啊?' AND TuID<>@userID)
   UPDATE bbsUsers SET Upoint=Upoint+100 WHERE UID=@userID
ELSE
   UPDATE bbsUsers SET Upoint=Upoint+50 WHERE UID=@userID
---更新用户的积分后,更新相应的级别
UPDATE bbsUsers 
  SET Uclass=CASE
                WHEN Upoint <500 THEN 1
                WHEN Upoint BETWEEN 500 AND 1000 THEN 2
                WHEN Upoint BETWEEN 1001 AND 2000 THEN 3
                WHEN Upoint BETWEEN 2001 AND 4000 THEN 4
                WHEN Upoint BETWEEN 4001 AND 5000 THEN 5
                ELSE 6
             END
   WHERE UID=@userID
--对外公告心酸果冻的发贴
SELECT 发贴作者='心酸果冻',发贴时间=convert(varchar(10),Ttime,111), 
    主题=Ttopic,内容=Tcontents FROM bbsTopic WHERE TID=@@IDENTITY
--显示目前的最新排名
SELECT 昵称=Uname,星级=CASE
                         WHEN Uclass=0 THEN ' '
                         WHEN Uclass=1 THEN '★'
                         WHEN Uclass=2 THEN '★★'
                         WHEN Uclass=3 THEN '★★★'
                         WHEN Uclass=4 THEN '★★★★'
                         WHEN Uclass=5 THEN '★★★★★'
                         ELSE               '★★★★★★'
                      END
   ,积分=Upoint FROM bbsUsers
GO
-------------------------------------------------------------------
SELECT * FROM BBSUsers  --用户表
SELECT * FROM bbsTopic    --主贴表
SELECT * FROM bbsReply    --回帖表
SELECT * FROM bbsSection  --版块表
--------------------