论坛bbsDB的T-sql 建立
创建数据库：bbsDB
USE master --使用master数据库
GO
/*---查询是否有bbsDB库,在系统的sysdatabases表中---*/
IF EXISTS (SELECT * FROM dbo.sysdatabases WHERE NAME='bbsDB')
DROP DATABASE bbsDB
GO
EXEC xp_cmdshell 'mkdir d:\project' --调用DOS命令创建d:\project目录
/*---开始创建数据库bbsDB---*/
CREATE DATABASE bbsDB
ON
(
/*---数据文件的具体描述---*/
NAME='bbsDB_data', --主数据文件的逻辑名
FILENAME='d:\project\bbsDB_data.mdf', --主数据文件的存放路径
SIZE=10MB, --主数据文件初始大小
FILEGROWTH=20% --主数据文件的增长率
)
LOG ON
(
/*---日志文件的具体描述,各参数含义同上---*/
NAME='bbsDB_log',
FILENAME='d:\project\bbsDB_log.ldf',
SIZE=1MB,
MAXSIZE=20MB,
FILEGROWTH=10%
)
GO
GO
/*---查询是否有bbsDB库,在系统的sysdatabases表中---*/
IF EXISTS (SELECT * FROM dbo.sysdatabases WHERE NAME='bbsDB')
DROP DATABASE bbsDB
GO
EXEC xp_cmdshell 'mkdir d:\project' --调用DOS命令创建d:\project目录
/*---开始创建数据库bbsDB---*/
CREATE DATABASE bbsDB
ON
(
/*---数据文件的具体描述---*/
NAME='bbsDB_data', --主数据文件的逻辑名
FILENAME='d:\project\bbsDB_data.mdf', --主数据文件的存放路径
SIZE=10MB, --主数据文件初始大小
FILEGROWTH=20% --主数据文件的增长率
)
LOG ON
(
/*---日志文件的具体描述,各参数含义同上---*/
NAME='bbsDB_log',
FILENAME='d:\project\bbsDB_log.ldf',
SIZE=1MB,
MAXSIZE=20MB,
FILEGROWTH=10%
)
GO
创建用户表bbsUsers
USE bbsDB
GO
/*---检查是否已存在表bbsUsers;查询bbsDB数据库中的系统表sysobjects---*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='bbsUsers')
DROP TABLE bbsUsers
GO
/*---创建用户表bbsUsers---*/
CREATE TABLE bbsUsers
(
UID INT IDENTITY(2,1) NOT NULL, --自动编号,标示列
Uname VARCHAR(15) NOT NULL, --昵称
Upassword VARCHAR(10), --密码
Uemail VARCHAR(20), --邮件
Usex BIT NOT NULL, --性别
Ubirthdat SMALLDATETIME, --生日
Uclass INT, --级别(几星级
Uremark 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 (888888) FOR Upassword --初始密码默认为6个8
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,Ubirthdat,Uremark,Ustate,Upoint)
VALUES ('可卡因','HYXS007','ss@HotMail','1978-7-9','我要去公安局自首',1,200)
INSERT INTO bbsUsers (Uname,Uemail,Ubirthdat,Uremark,Ustate,Upoint,Usex,Uclass)
VALUES ('心酸果冻','yy@hotmail','1987-11-29','善良的看风景沃尔',2,600,0,2)
INSERT INTO bbsUsers (Uname,Upassword,Uemail,Ubirthdat,Uremark,Ustate,Upoint,Uclass)
VALUES ('冬篱儿','fangdong','bb@sohu.com','1971-1-13','圣诞快乐',4,1200,3)
INSERT INTO bbsUsers (Uname,Upassword,Uemail,Ubirthdat,Uremark,Ustate,Upoint,Uclass)
VALUES ('Super','master','dd@p.com','1983-7-4','我是SuperMan',1,5000,5)
GO
--SELECT * FROM bbsUsers --查看测试数据是否正确
GO
GO
/*---检查是否已存在表bbsUsers;查询bbsDB数据库中的系统表sysobjects---*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='bbsUsers')
DROP TABLE bbsUsers
GO
/*---创建用户表bbsUsers---*/
CREATE TABLE bbsUsers
(
UID INT IDENTITY(2,1) NOT NULL, --自动编号,标示列
Uname VARCHAR(15) NOT NULL, --昵称
Upassword VARCHAR(10), --密码
Uemail VARCHAR(20), --邮件
Usex BIT NOT NULL, --性别
Ubirthdat SMALLDATETIME, --生日
Uclass INT, --级别(几星级
Uremark 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 (888888) FOR Upassword --初始密码默认为6个8
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,Ubirthdat,Uremark,Ustate,Upoint)
VALUES ('可卡因','HYXS007','ss@HotMail','1978-7-9','我要去公安局自首',1,200)
INSERT INTO bbsUsers (Uname,Uemail,Ubirthdat,Uremark,Ustate,Upoint,Usex,Uclass)
VALUES ('心酸果冻','yy@hotmail','1987-11-29','善良的看风景沃尔',2,600,0,2)
INSERT INTO bbsUsers (Uname,Upassword,Uemail,Ubirthdat,Uremark,Ustate,Upoint,Uclass)
VALUES ('冬篱儿','fangdong','bb@sohu.com','1971-1-13','圣诞快乐',4,1200,3)
INSERT INTO bbsUsers (Uname,Upassword,Uemail,Ubirthdat,Uremark,Ustate,Upoint,Uclass)
VALUES ('Super','master','dd@p.com','1983-7-4','我是SuperMan',1,5000,5)
GO
--SELECT * FROM bbsUsers --查看测试数据是否正确
GO
创建板块表bbsSection
USE bbsDB
GO
/*---查询是否有bbsSection表,在系统的sysdatabases表中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='bbsSection')
DROP TABLE bbsSection
GO
/*---创建板块表bbsSection,存放板块信息---*/
CREATE TABLE bbsSection
(
SID INT IDENTITY(1,1) NOT NULL, --板块编号,自动增长
Sname VARCHAR(32) NOT NULL, --板块名称
SmasterID INT NOT NULL, --版主的用户ID,外键;引用用户表bbsUsers的UID
Sprofile VARCHAR(200) NULL, --版面简介
SclickCount INT NULL, --点击率
StopicCount INT NULL --发帖数
)
GO
--SELECT * FROM bbsSection
ALTER TABLE bbsSection ADD CONSTRAINT PK_SID
PRIMARY KEY (SID) --设置主键 SID
ALTER TABLE bbsSection ADD CONSTRAINT FK_SmasterID
FOREIGN KEY (SmasterID) REFERENCES bbsUsers(UID) --设置外键,引用用户表bbsUsers的UID
ALTER TABLE bbsSection ADD CONSTRAINT DF_SclickCount
DEFAULT(0) FOR SclickCount --设置点击率默认为0
ALTER TABLE bbsSection ADD CONSTRAINT DF_StopicCount
DEFAULT(0) FOR StopicCount --设置发帖数默认为0
GO
INSERT INTO bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)
VALUES ('Java','3','包含框架,开源,非技术区,J2SE/基础类,J2EE
',500,1)
INSERT INTO bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)
VALUES ('.Net技术','5','包含c#,asp.net,Framework,web,services',800,1)
INSERT INTO bbsSection (Sname,SmasterID,Sprofile)
VALUES ('Linux/Unix社区','5','包含系统维护与使用区,程序开发区,内核及驱动程序')
--SELECT * FROM bbsSection
GO
GO
/*---查询是否有bbsSection表,在系统的sysdatabases表中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='bbsSection')
DROP TABLE bbsSection
GO
/*---创建板块表bbsSection,存放板块信息---*/
CREATE TABLE bbsSection
(
SID INT IDENTITY(1,1) NOT NULL, --板块编号,自动增长
Sname VARCHAR(32) NOT NULL, --板块名称
SmasterID INT NOT NULL, --版主的用户ID,外键;引用用户表bbsUsers的UID
Sprofile VARCHAR(200) NULL, --版面简介
SclickCount INT NULL, --点击率
StopicCount INT NULL --发帖数
)
GO
--SELECT * FROM bbsSection
ALTER TABLE bbsSection ADD CONSTRAINT PK_SID
PRIMARY KEY (SID) --设置主键 SID
ALTER TABLE bbsSection ADD CONSTRAINT FK_SmasterID
FOREIGN KEY (SmasterID) REFERENCES bbsUsers(UID) --设置外键,引用用户表bbsUsers的UID
ALTER TABLE bbsSection ADD CONSTRAINT DF_SclickCount
DEFAULT(0) FOR SclickCount --设置点击率默认为0
ALTER TABLE bbsSection ADD CONSTRAINT DF_StopicCount
DEFAULT(0) FOR StopicCount --设置发帖数默认为0
GO
INSERT INTO bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)
VALUES ('Java','3','包含框架,开源,非技术区,J2SE/基础类,J2EE
',500,1)INSERT INTO bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)
VALUES ('.Net技术','5','包含c#,asp.net,Framework,web,services
',800,1)INSERT INTO bbsSection (Sname,SmasterID,Sprofile)
VALUES ('Linux/Unix社区','5','包含系统维护与使用区,程序开发区,内核及驱动程序
')--SELECT * FROM bbsSection
GO
创建主贴表bbsTopic
USE bbsDB
GO
/*---查询是否有bbsTopic表,在系统的sysdatabases表中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='bbsTopic')
DROP TABLE bbsTopic
GO
/*---创建主贴表bbsTopic存放主贴内容---*/
CREATE TABLE bbsTopic
(
TID INT IDENTITY(1,1) NOT NULL, --帖子编号,自动增长
TsID INT NOT NULL, --板块编号:外键,引用bbsSection表的主键SID
TuID INT NOT NULL, --发帖人ID:外键,引用bbsUsers表的主键UID
TreplyCount INT NULL, --回复数量
Tface INT NULL, --发帖表情
Ttopic VARCHAR(20) NOT NULL, --标题
Tcontents VARCHAR(30) NOT NULL, --正文,必须大于6个字符
Ttime DATETIME NULL, --发帖时间
TclickCount INT NULL, --点击数
Tstate INT NOT NULL, --状态,例如是否被锁,是否为精华帖
TlastReply DATETIME NULL --最后回复时间,必须晚于发帖时间
)
GO
/*设置约束*/
ALTER TABLE bbsTopic ADD CONSTRAINT PK_TID
PRIMARY KEY (TID) --设置TID为主键标示
ALTER TABLE bbsTopic ADD CONSTRAINT FK_TsID
FOREIGN KEY(TsID) REFERENCES bbsSection(SID) --设置外键,引用bbsSection的SID
ALTER TABLE bbsTopic ADD CONSTRAINT FK_TuID
FOREIGN KEY(TuID) REFERENCES bbsUsers(UID) --设置外键,引用bbsUsers的UID
ALTER TABLE bbsTopic ADD CONSTRAINT DF_TreplyCount
DEFAULT(0) FOR TreplyCount --回复数量(TreplyCount)默认为0
ALTER TABLE bbsTopic ADD CONSTRAINT CK_Tcontents
CHECK(LEN(Tcontents)>6) --正文必须大于6个字符
ALTER TABLE bbsTopic ADD CONSTRAINT DF_Ttime
DEFAULT(getdate()) FOR Ttime --发帖时间默认为当前时间
ALTER TABLE bbsTopic ADD CONSTRAINT DF_TclickCount
DEFAULT(0) FOR TclickCount --点击数(TclickCount)默认为0
ALTER TABLE bbsTopic ADD CONSTRAINT DF_Tstate
DEFAULT(1) FOR Tstate --状态默认为1
ALTER TABLE bbsTopic ADD CONSTRAINT CK_TlastReply
CHECK(TlastReply>getDate()) --最后回复时间必须晚于发帖时间
GO
/*---插入测试数据---*/
INSERT INTO bbsTopic (TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate) VALUES
(1,3,2,1,'还是JPS中','JSP文件中读取',200,1)
INSERT INTO bbsTopic (TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate) VALUES
(2,2,0,2,'部署.net','项目包括,winserver',0,1)
GO
--SELECT * FROM bbsTopic
GO
/*---查询是否有bbsTopic表,在系统的sysdatabases表中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='bbsTopic')
DROP TABLE bbsTopic
GO
/*---创建主贴表bbsTopic存放主贴内容---*/
CREATE TABLE bbsTopic
(
TID INT IDENTITY(1,1) NOT NULL, --帖子编号,自动增长
TsID INT NOT NULL, --板块编号:外键,引用bbsSection表的主键SID
TuID INT NOT NULL, --发帖人ID:外键,引用bbsUsers表的主键UID
TreplyCount INT NULL, --回复数量
Tface INT NULL, --发帖表情
Ttopic VARCHAR(20) NOT NULL, --标题
Tcontents VARCHAR(30) NOT NULL, --正文,必须大于6个字符
Ttime DATETIME NULL, --发帖时间
TclickCount INT NULL, --点击数
Tstate INT NOT NULL, --状态,例如是否被锁,是否为精华帖
TlastReply DATETIME NULL --最后回复时间,必须晚于发帖时间
)
GO
/*设置约束*/
ALTER TABLE bbsTopic ADD CONSTRAINT PK_TID
PRIMARY KEY (TID) --设置TID为主键标示
ALTER TABLE bbsTopic ADD CONSTRAINT FK_TsID
FOREIGN KEY(TsID) REFERENCES bbsSection(SID) --设置外键,引用bbsSection的SID
ALTER TABLE bbsTopic ADD CONSTRAINT FK_TuID
FOREIGN KEY(TuID) REFERENCES bbsUsers(UID) --设置外键,引用bbsUsers的UID
ALTER TABLE bbsTopic ADD CONSTRAINT DF_TreplyCount
DEFAULT(0) FOR TreplyCount --回复数量(TreplyCount)默认为0
ALTER TABLE bbsTopic ADD CONSTRAINT CK_Tcontents
CHECK(LEN(Tcontents)>6) --正文必须大于6个字符
ALTER TABLE bbsTopic ADD CONSTRAINT DF_Ttime
DEFAULT(getdate()) FOR Ttime --发帖时间默认为当前时间
ALTER TABLE bbsTopic ADD CONSTRAINT DF_TclickCount
DEFAULT(0) FOR TclickCount --点击数(TclickCount)默认为0
ALTER TABLE bbsTopic ADD CONSTRAINT DF_Tstate
DEFAULT(1) FOR Tstate --状态默认为1
ALTER TABLE bbsTopic ADD CONSTRAINT CK_TlastReply
CHECK(TlastReply>getDate()) --最后回复时间必须晚于发帖时间
GO
/*---插入测试数据---*/
INSERT INTO bbsTopic (TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate) VALUES
(1,3,2,1,'还是JPS中
','JSP文件中读取',200,1)INSERT INTO bbsTopic (TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate) VALUES
(2,2,0,2,'部署.net','项目包括,winserver',0,1)
GO
--SELECT * FROM bbsTopic
创建跟帖表bbsReply
USE bbsDB
GO
/*---查询是否有bbsReply表,在系统的sysdatabases表中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='bbsReply')
DROP TABLE bbsReply
GO
/*---创建跟帖表bbsReply---*/
CREATE TABLE bbsReply
(
RID INT IDENTITY(1,1) NOT NULL, --自动编号,帖子编号
RtID INT NOT NULL, --主贴ID:外键,引用bbsTopic表的主键TID
RsID INT NOT NULL, --板块ID:外键,引用bbsSection表的主键SID
RuID INT NOT NULL, --回帖人:外键,引用bbsUsers表的主键UID
Rface INT NULL, --回帖表情
Rcontents VARCHAR(200) NOT NULL, --正文,必须大于6个字符
Rtime DATETIME NULL, --回帖时间
RclickCount INT NULL --点击数
)
GO
/*---创建约束---*/
ALTER TABLE bbsReply ADD CONSTRAINT PK_RID
PRIMARY KEY (RID)
ALTER TABLE bbsReply ADD CONSTRAINT FK_RtID
FOREIGN KEY (RtID) REFERENCES bbsTopic(TID)
ALTER TABLE bbsReply ADD CONSTRAINT FK_RsID
FOREIGN KEY (RsID) REFERENCES bbsSection(SID)
ALTER TABLE bbsReply ADD CONSTRAINT FK_RuID
FOREIGN KEY (RuID) 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
GO
INSERT INTO bbsReply (RtID,RsID,RuID,Rface,Rcontents,RclickCount) VALUES
(1,1,5,2,'JSP乱码问题该怎么解决最好,因为我发现这个问题好像在.',100)
INSERT INTO bbsReply (RtID,RsID,RuID,Rface,Rcontents,RclickCount) VALUES
(1,1,4,4,'转换jsp<%@ page language="java" import="java.">',200)
INSERT INTO bbsReply (RtID,RsID,RuID,Rface,Rcontents,RclickCount) VALUES
(2,2,2,3,'.net很经常,就像ppmm啊!',0)
GO
GO
/*---查询是否有bbsReply表,在系统的sysdatabases表中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='bbsReply')
DROP TABLE bbsReply
GO
/*---创建跟帖表bbsReply---*/
CREATE TABLE bbsReply
(
RID INT IDENTITY(1,1) NOT NULL, --自动编号,帖子编号
RtID INT NOT NULL, --主贴ID:外键,引用bbsTopic表的主键TID
RsID INT NOT NULL, --板块ID:外键,引用bbsSection表的主键SID
RuID INT NOT NULL, --回帖人:外键,引用bbsUsers表的主键UID
Rface INT NULL, --回帖表情
Rcontents VARCHAR(200) NOT NULL, --正文,必须大于6个字符
Rtime DATETIME NULL, --回帖时间
RclickCount INT NULL --点击数
)
GO
/*---创建约束---*/
ALTER TABLE bbsReply ADD CONSTRAINT PK_RID
PRIMARY KEY (RID)
ALTER TABLE bbsReply ADD CONSTRAINT FK_RtID
FOREIGN KEY (RtID) REFERENCES bbsTopic(TID)
ALTER TABLE bbsReply ADD CONSTRAINT FK_RsID
FOREIGN KEY (RsID) REFERENCES bbsSection(SID)
ALTER TABLE bbsReply ADD CONSTRAINT FK_RuID
FOREIGN KEY (RuID) 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
GO
INSERT INTO bbsReply (RtID,RsID,RuID,Rface,Rcontents,RclickCount) VALUES
(1,1,5,2,'JSP乱码问题该怎么解决最好,因为我发现这个问题好像在.',100)
INSERT INTO bbsReply (RtID,RsID,RuID,Rface,Rcontents,RclickCount) VALUES
(1,1,4,4,'转换jsp<%@ page language="java" import="java.">',200)
INSERT INTO bbsReply (RtID,RsID,RuID,Rface,Rcontents,RclickCount) VALUES
(2,2,2,3,'.net很经常,就像ppmm啊!',0)
GO
相关查找
USE bbsDB
GO
/*---测试各个表单中的数据---*/
SELECT * FROM bbsUsers --用户表
SELECT * FROM bbsTopic --主贴表
SELECT * FROM bbsReply --回帖表
SELECT * FROM bbsSection --板块表
/*创建版主帐号*/
EXEC sp_addlogin 'adminMaster','theMaster'
EXEC sp_grantdbaccess 'adminMaster','AM'
GRANT SELECT,DELETE ON bbsTopic TO AM
GRANT SELECT,DELETE ON bbsReply TO AM
GRANT UPDATE ON bbsUsers TO AM
/*---使用系统变量查询数据库系统情况---*/
PRINT 'SQL Server的版本:'+@@VERSION
PRINT '服务器的名称:'+@@SERVERNAME
UPDATE bbsUsers SET Upassword='1234' WHERE Uname='可卡因' --密码违反约束
PRINT '执行上条预计产生的错误号:'+CONVERT(varchar(5),@@ERROR)
GO
/*---使用变量和IF-ELSE语句,版主查看可卡因的情况---*/
SET NOCOUNT ON --不显示T-SQL预计影响的行数信息
PRINT ' ' --为了显示方便,打印一空行
PRINT '个人资料如下'
SELECT 昵称=Uname,等级=Uclass,个人说明=Uremark,积分=Upoint
FROM bbsUsers WHERE Uname='可卡因'
DECLARE @userID INT,@point INT --定义变量,用于存放用户编号值
SELECT @point=Upoint FROM bbsUsers WHERE Uname='可卡因'
SELECT @userID=UID FROM bbsUsers WHERE Uname='可卡因' --变量赋值
PRINT '可卡因发帖如下:'
SELECT 发帖时间=CONVERT(VARCHAR(10),Ttime,111), --111表示年/月/日格式转换
点击率=TclickCount,主题=Ttopic,内容=Tcontents FROM bbsTopic
WHERE TuID=@userID
PRINT '可卡因回帖如下:'
SELECT 回帖时间=CONVERT(VARCHAR(10),Rtime,111),点击率=RclickCount,
回帖内容=Rcontents FROM bbsReply WHERE RuID=@userID
IF(@point>30)
PRINT '可卡因的权限:有权发帖'
ELSE
PRINT '可卡因的权限:无权发帖'
/*进行提分加星操作*/
PRINT '开始提分,请稍后'
DECLARE @score INT, @avg INT --定义变量:提分值和平均分
SET @score=0
WHILE(1=1)
BEGIN
SELECT @avg=AVG(Upoint) FROM bbsUsers
IF(@avg>2000)
BREAK
ELSE
BEGIN
UPDATE bbsUsers SET Upoint=Upoint+50 WHERE Ustate<>4 --除了被封杀的用户外
SET @score=@score+50
END
END
print '提升分值:'+CONVERT(VARCHAR(8),@score)
/*---提升分值后,更新用户的对应等级(星级)---*/
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 3000 THEN 4
WHEN Upoint BETWEEN 3001 AND 4000 THEN 5
ELSE 6
END
PRINT '-----------------加分后的用户级别情况-------------------'
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
GO
/*---测试各个表单中的数据---*/
SELECT * FROM bbsUsers --用户表
SELECT * FROM bbsTopic --主贴表
SELECT * FROM bbsReply --回帖表
SELECT * FROM bbsSection --板块表
/*创建版主帐号*/
EXEC sp_addlogin 'adminMaster','theMaster'
EXEC sp_grantdbaccess 'adminMaster','AM'
GRANT SELECT,DELETE ON bbsTopic TO AM
GRANT SELECT,DELETE ON bbsReply TO AM
GRANT UPDATE ON bbsUsers TO AM
/*---使用系统变量查询数据库系统情况---*/
PRINT 'SQL Server的版本:'+@@VERSION
PRINT '服务器的名称:'+@@SERVERNAME
UPDATE bbsUsers SET Upassword='1234' WHERE Uname='可卡因' --密码违反约束
PRINT '执行上条预计产生的错误号:'+CONVERT(varchar(5),@@ERROR)
GO
/*---使用变量和IF-ELSE语句,版主查看可卡因的情况---*/
SET NOCOUNT ON --不显示T-SQL预计影响的行数信息
PRINT ' ' --为了显示方便,打印一空行
PRINT '个人资料如下'
SELECT 昵称=Uname,等级=Uclass,个人说明=Uremark,积分=Upoint
FROM bbsUsers WHERE Uname='可卡因'
DECLARE @userID INT,@point INT --定义变量,用于存放用户编号值
SELECT @point=Upoint FROM bbsUsers WHERE Uname='可卡因'
SELECT @userID=UID FROM bbsUsers WHERE Uname='可卡因' --变量赋值
PRINT '可卡因发帖如下:'
SELECT 发帖时间=CONVERT(VARCHAR(10),Ttime,111), --111表示年/月/日格式转换
点击率=TclickCount,主题=Ttopic,内容=Tcontents FROM bbsTopic
WHERE TuID=@userID
PRINT '可卡因回帖如下:'
SELECT 回帖时间=CONVERT(VARCHAR(10),Rtime,111),点击率=RclickCount,
回帖内容=Rcontents FROM bbsReply WHERE RuID=@userID
IF(@point>30)
PRINT '可卡因的权限:有权发帖'
ELSE
PRINT '可卡因的权限:无权发帖'
/*进行提分加星操作*/
PRINT '开始提分,请稍后
'DECLARE @score INT, @avg INT --定义变量:提分值和平均分
SET @score=0
WHILE(1=1)
BEGIN
SELECT @avg=AVG(Upoint) FROM bbsUsers
IF(@avg>2000)
BREAK
ELSE
BEGIN
UPDATE bbsUsers SET Upoint=Upoint+50 WHERE Ustate<>4 --除了被封杀的用户外
SET @score=@score+50
END
END
print '提升分值:'+CONVERT(VARCHAR(8),@score)
/*---提升分值后,更新用户的对应等级(星级)---*/
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 3000 THEN 4
WHEN Upoint BETWEEN 3001 AND 4000 THEN 5
ELSE 6
END
PRINT '-----------------加分后的用户级别情况-------------------'
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
附加
/*查找心酸果冻的发帖和回帖内容*/
DECLARE @name INT,@topic INT,@reply INT --定义变量name心酸果冻的UID topic发帖数,reply回帖数
SELECT @name=UID FROM bbsUsers WHERE Uname='心酸果冻' --给@name赋值 心酸果冻的uid
SELECT @topic = COUNT(TuID) FROM bbsTopic WHERE TuID=@name --查找@name的发帖数
IF (@topic>0) --大于0 也就是发过帖
BEGIN
SELECT 发帖时间=Ttime,点击率=TclickCount,主题=Ttopic,内容=Tcontents FROM bbsTopic WHERE TuID=@name --显示帖子内容
END
ELSE
PRINT '心酸果冻发帖数量为:0帖' --否则显示为0帖
SELECT @reply =COUNT(RuID) FROM bbsReply WHERE RuID=@name --查找@name的回帖数
IF(@reply>0) --大于0 也就是回过帖
BEGIN
SELECT 回帖时间=Rtime,点击率=RclickCount,回帖表情=Rface,回帖内容=Rcontents FROM bbsReply WHERE RuID=@name --显示回帖
END
ELSE
PRINT '心酸果冻回帖数量为:0帖' --否则显示为0帖
/*根据@topic+@reply的值计算功勋级别,并显示*/
PRINT '心酸果冻帖数总计:'+CONVERT(VARCHAR(10),@topic+@reply)+' '+'功勋级别:'+CASE
WHEN @topic+@reply<10 THEN '新手上路'
WHEN @topic+@reply BETWEEN 10 AND 20 THEN '侠客'
WHEN @topic+@reply BETWEEN 21 AND 30 THEN '骑士'
WHEN @topic+@reply BETWEEN 31 AND 40 THEN '精灵王'
WHEN @topic+@reply BETWEEN 41 AND 50 THEN '光明使者'
ELSE '法老'
END
GO
/*查找精华帖*/
PRINT '精华帖的信息如下'
DECLARE @tid INT ,@reply INT
SELECT TOP 1 @tid=TID,@reply=TreplyCount FROM bbsTopic ORDER BY TreplyCount DESC --返回回复最多的帖子ID
SELECT 发帖时间=CONVERT(VARCHAR(10),Ttime,111),点击率=TclickCount,
作者=(SELECT Uname FROM bbsUsers WHERE UID=TuID),
主题=Ttopic,内容=Tcontents
FROM bbsTopic WHERE TID=@tid
PRINT '回帖数:'+CONVERT(VARCHAR(10),@reply)+',如下所示'
SELECT 回帖时间=CONVERT(VARCHAR(10),Rtime,111),点击率=RclickCount,
回帖表情=CASE
WHEN Rface=1 THEN '^(oo)^猪头'
WHEN Rface=2 THEN '*小丑'
WHEN Rface=3 THEN '[:|]机器人'
WHEN Rface=4 THEN '(^o~o^)老人家'
ELSE '(:<吹水大王)'
END,
回帖内容=Rcontents FROM bbsReply WHERE RtID=@tid
/*论坛人气评估,论坛总点击率>1000,为"人气熊旺旺",否则就为"一般般"*/
IF (SELECT SUM(SclickCount) FROM bbsSection)>1000
PRINT '论坛人气熊旺旺!!!!!感谢大家支持!!!!!'
ELSE
PRINT '论坛人气一般般.大家要继续努力啊!!'
/*年度品牌板块:主贴量最多的板块*/
PRINT '年度品牌板块'
SELECT 板块名称=Sname,主帖数=StopicCount,简介=Sprofile FROM bbsSection WHERE StopicCount IN (SELECT MAX(StopicCount) FROM bbsSection)
/*年度倒胃板块:主贴量最少的板块*/
PRINT '年度倒胃板块'
SELECT 板块名称=Sname,主帖数=StopicCount,简介=Sprofile FROM bbsSection WHERE StopicCount IN (SELECT MIN(StopicCount) FROM bbsSection)
/*年度回帖人气最旺奖:回帖的点击率排名前2名*/
PRINT '年度回帖人气最旺前两名'
SELECT 昵称=Uname,星级=Uclass FROM bbsUsers WHERE UID IN (SELECT TOP 2 TuID FROM bbsTopic ORDER BY TclickCount DESC)
/*年度业绩最差版主:板块点击率低于500或主贴量等于0*/
PRINT '最差板块'
SELECT 板块名称=Sname,主贴数量=StopicCount,点击率=SclickCount FROM bbsSection WHERE SclickCount<=500 OR StopicCount=0
GO
/*用户发帖,相应板块主贴数+1 用户酌情加分,新帖+100,回帖+50.更新用户等级.显示新帖,所有用户等级重新排名*/
DECLARE @uid INT,@sid INT --定义两个变量 uid=用户ID tid=帖子ID
SELECT @uid=UID FROM bbsUsers WHERE Uname='心酸果冻' --获取用户ID
SELECT @sid=SID FROM bbsSection WHERE Sname='.Net技术' --获取板块ID
INSERT INTO bbsTopic (TsID,TuID,Ttopic,Tcontents)
VALUES (@sid,@uid,'什么是.NET啊','微软的广告超过半个北京城市') --插入新帖内容
UPDATE bbsSection SET StopicCount=StopicCount+1
IF EXISTS (SELECT * FROM bbsTopic WHERE Ttopic='什么是.NET啊') --判断是否有此帖,有此贴表示回帖+50否则+100
UPDATE bbsUsers SET Upoint=Upoint+50
ELSE
UPDATE bbsUsers SET Upoint=Upoint+100
/*更新用户积分*/
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 3000 THEN 4
WHEN Upoint BETWEEN 3001 AND 4000 THEN 5
ELSE 6
END
WHERE UID=@uid
/*显示主贴*/
SELECT 帖子标题=Ttopic,帖子内容=Tcontents,发帖人=Uname
FROM bbsTopic INNER JOIN bbsUsers ON bbsTopic.TuID=bbsUsers.UID
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
/*回帖后更新对应主贴信息,回复数量+1,点击率+1. 对应板块点击率+1 第一个回帖的+100 否则+50
更新用户相应等级
论坛发布主题和跟帖
更新用户星级
*/
GO
DECLARE @tid INT,@uid INT,@sid INT --tid 主贴ID,uid 用户id , sid 板块id
SELECT @tid=TID,@sid=TsID FROM bbsTopic WHERE Ttopic='什么是.NET啊' --获取tid和sid
SELECT @uid=UID FROM bbsUsers WHERE Uname='可卡因' --获取用户id
INSERT bbsReply (RtID,RsID,RuID,Rcontents) VALUES (@tid,@sid,@uid,'是微软力推的企业级信息共享平台') --插入数据
IF (SELECT TreplyCount FROM bbsTopic WHERE TID=@tid)=0 --回帖数=0 +100 否则+50
UPDATE bbsUsers SET Upoint=Upoint+100
ELSE
UPDATE bbsUsers SET Upoint=Upoint+50
/*重新统计用户等级*/
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 3000 THEN 4
WHEN Upoint BETWEEN 3001 AND 4000 THEN 5
ELSE 6
END
WHERE UID=@uid
UPDATE bbsTopic SET TreplyCount=TreplyCount+1, TclickCount=TclickCount+1 WHERE TID=@tid --更新回帖数和点击率
UPDATE bbsSection SET SclickCount=SclickCount+1 --更新板块点击率
/*显示主题和更帖*/
SELECT 主贴标题=Ttopic,主贴内容=Tcontents,更帖内容=Rcontents
FROM bbsTopic INNER JOIN bbsReply ON bbsTopic.TID=bbsReply.RtID
WHERE RtID=@tid
/*重新统计等级*/
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
DECLARE @name INT,@topic INT,@reply INT --定义变量name心酸果冻的UID topic发帖数,reply回帖数
SELECT @name=UID FROM bbsUsers WHERE Uname='心酸果冻' --给@name赋值 心酸果冻的uid
SELECT @topic = COUNT(TuID) FROM bbsTopic WHERE TuID=@name --查找@name的发帖数
IF (@topic>0) --大于0 也就是发过帖
BEGIN
SELECT 发帖时间=Ttime,点击率=TclickCount,主题=Ttopic,内容=Tcontents FROM bbsTopic WHERE TuID=@name --显示帖子内容
END
ELSE
PRINT '心酸果冻发帖数量为:0帖' --否则显示为0帖
SELECT @reply =COUNT(RuID) FROM bbsReply WHERE RuID=@name --查找@name的回帖数
IF(@reply>0) --大于0 也就是回过帖
BEGIN
SELECT 回帖时间=Rtime,点击率=RclickCount,回帖表情=Rface,回帖内容=Rcontents FROM bbsReply WHERE RuID=@name --显示回帖
END
ELSE
PRINT '心酸果冻回帖数量为:0帖' --否则显示为0帖
/*根据@topic+@reply的值计算功勋级别,并显示*/
PRINT '心酸果冻帖数总计:'+CONVERT(VARCHAR(10),@topic+@reply)+' '+'功勋级别:'+CASE
WHEN @topic+@reply<10 THEN '新手上路'
WHEN @topic+@reply BETWEEN 10 AND 20 THEN '侠客'
WHEN @topic+@reply BETWEEN 21 AND 30 THEN '骑士'
WHEN @topic+@reply BETWEEN 31 AND 40 THEN '精灵王'
WHEN @topic+@reply BETWEEN 41 AND 50 THEN '光明使者'
ELSE '法老'
END
GO
/*查找精华帖*/
PRINT '精华帖的信息如下'
DECLARE @tid INT ,@reply INT
SELECT TOP 1 @tid=TID,@reply=TreplyCount FROM bbsTopic ORDER BY TreplyCount DESC --返回回复最多的帖子ID
SELECT 发帖时间=CONVERT(VARCHAR(10),Ttime,111),点击率=TclickCount,
作者=(SELECT Uname FROM bbsUsers WHERE UID=TuID),
主题=Ttopic,内容=Tcontents
FROM bbsTopic WHERE TID=@tid
PRINT '回帖数:'+CONVERT(VARCHAR(10),@reply)+',如下所示'
SELECT 回帖时间=CONVERT(VARCHAR(10),Rtime,111),点击率=RclickCount,
回帖表情=CASE
WHEN Rface=1 THEN '^(oo)^猪头'
WHEN Rface=2 THEN '*小丑'
WHEN Rface=3 THEN '[:|]机器人'
WHEN Rface=4 THEN '(^o~o^)老人家'
ELSE '(:<吹水大王)'
END,
回帖内容=Rcontents FROM bbsReply WHERE RtID=@tid
/*论坛人气评估,论坛总点击率>1000,为"人气熊旺旺",否则就为"一般般"*/
IF (SELECT SUM(SclickCount) FROM bbsSection)>1000
PRINT '论坛人气熊旺旺!!!!!感谢大家支持!!!!!'
ELSE
PRINT '论坛人气一般般
.大家要继续努力啊!!'/*年度品牌板块:主贴量最多的板块*/
PRINT '年度品牌板块'
SELECT 板块名称=Sname,主帖数=StopicCount,简介=Sprofile FROM bbsSection WHERE StopicCount IN (SELECT MAX(StopicCount) FROM bbsSection)
/*年度倒胃板块:主贴量最少的板块*/
PRINT '年度倒胃板块'
SELECT 板块名称=Sname,主帖数=StopicCount,简介=Sprofile FROM bbsSection WHERE StopicCount IN (SELECT MIN(StopicCount) FROM bbsSection)
/*年度回帖人气最旺奖:回帖的点击率排名前2名*/
PRINT '年度回帖人气最旺前两名'
SELECT 昵称=Uname,星级=Uclass FROM bbsUsers WHERE UID IN (SELECT TOP 2 TuID FROM bbsTopic ORDER BY TclickCount DESC)
/*年度业绩最差版主:板块点击率低于500或主贴量等于0*/
PRINT '最差板块'
SELECT 板块名称=Sname,主贴数量=StopicCount,点击率=SclickCount FROM bbsSection WHERE SclickCount<=500 OR StopicCount=0
GO
/*用户发帖,相应板块主贴数+1 用户酌情加分,新帖+100,回帖+50.更新用户等级.显示新帖,所有用户等级重新排名*/
DECLARE @uid INT,@sid INT --定义两个变量 uid=用户ID tid=帖子ID
SELECT @uid=UID FROM bbsUsers WHERE Uname='心酸果冻' --获取用户ID
SELECT @sid=SID FROM bbsSection WHERE Sname='.Net技术' --获取板块ID
INSERT INTO bbsTopic (TsID,TuID,Ttopic,Tcontents)
VALUES (@sid,@uid,'什么是.NET啊','微软的广告超过半个北京城市') --插入新帖内容
UPDATE bbsSection SET StopicCount=StopicCount+1
IF EXISTS (SELECT * FROM bbsTopic WHERE Ttopic='什么是.NET啊') --判断是否有此帖,有此贴表示回帖+50否则+100
UPDATE bbsUsers SET Upoint=Upoint+50
ELSE
UPDATE bbsUsers SET Upoint=Upoint+100
/*更新用户积分*/
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 3000 THEN 4
WHEN Upoint BETWEEN 3001 AND 4000 THEN 5
ELSE 6
END
WHERE UID=@uid
/*显示主贴*/
SELECT 帖子标题=Ttopic,帖子内容=Tcontents,发帖人=Uname
FROM bbsTopic INNER JOIN bbsUsers ON bbsTopic.TuID=bbsUsers.UID
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
/*回帖后更新对应主贴信息,回复数量+1,点击率+1. 对应板块点击率+1 第一个回帖的+100 否则+50
更新用户相应等级
论坛发布主题和跟帖
更新用户星级
*/
GO
DECLARE @tid INT,@uid INT,@sid INT --tid 主贴ID,uid 用户id , sid 板块id
SELECT @tid=TID,@sid=TsID FROM bbsTopic WHERE Ttopic='什么是.NET啊' --获取tid和sid
SELECT @uid=UID FROM bbsUsers WHERE Uname='可卡因' --获取用户id
INSERT bbsReply (RtID,RsID,RuID,Rcontents) VALUES (@tid,@sid,@uid,'是微软力推的企业级信息共享平台') --插入数据
IF (SELECT TreplyCount FROM bbsTopic WHERE TID=@tid)=0 --回帖数=0 +100 否则+50
UPDATE bbsUsers SET Upoint=Upoint+100
ELSE
UPDATE bbsUsers SET Upoint=Upoint+50
/*重新统计用户等级*/
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 3000 THEN 4
WHEN Upoint BETWEEN 3001 AND 4000 THEN 5
ELSE 6
END
WHERE UID=@uid
UPDATE bbsTopic SET TreplyCount=TreplyCount+1, TclickCount=TclickCount+1 WHERE TID=@tid --更新回帖数和点击率
UPDATE bbsSection SET SclickCount=SclickCount+1 --更新板块点击率
/*显示主题和更帖*/
SELECT 主贴标题=Ttopic,主贴内容=Tcontents,更帖内容=Rcontents
FROM bbsTopic INNER JOIN bbsReply ON bbsTopic.TID=bbsReply.RtID
WHERE RtID=@tid
/*重新统计等级*/
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
来源：http://www.sh123.cn/bbs/viewthread.php?tid=6632
