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 --版块表
--------------------
/*---检查是否已存在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 --版块表
--------------------
解决方案 »
- 觉得自己MYSQL还不错的进来看看
- 求教高手
- ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_6f81_0.MYI'; try to r
- 兄弟我是新手上路,请求各位帮助
- 在繁体WINXP下,客户端用ODBC为何连不上MYSQL服务器啊
- mysqldump错误!
- PostgreSQL的Function返回Select的结果集如何做?谢谢!
- delphi中的mysql的连接问题
- mybatis配置时出现的问题
- 求 mysql 语句 删除重复的数据 保留某列最大值的数据
- 联合查询把记录为零的数据也查出来
- 有一个问题,就是表中列太多会有什么问题?
WHERE UID IN (SELECT TOP 2 TuID FROM bbsTopic ORDER BY TclickCount DESC)
消息 207,级别 16,状态 1,第 2 行
列名 'TuID' 无效