关于BBS数据库的设计
一个简单的论坛系统
1：包含下列信息：
2：每天论坛访问量300万左右，更新帖子10万左右。
请给出数据库表结构设计，并结合范式简要说明设计思路。
一. 发帖主题和回复信息存放在一张表，并在这个表中增加user_name字段 对数据库的操作而言，检索数据的性能基本不会对数据造成很大的影响（精确查找的情况下），而对表与表之间的连接却会产生巨大的影响， 特别在有巨量数据的表之间；因此对问题的定位基本可以确定：在显示和检索数据时，尽量减少数据库的连接以及表与表之间的连接；
引用
1: user:用户基本信息表
字段有：user_id,user_name,email,homepage,tel,add...
2: forum_item:主题和回复混合表
字段有：id,parent_id,user_id,user_name,title,content,....
parent_id=0或者null表示是主题，否则=n表示是id=n那条帖子的回复 UserName字段是冗余的，因此在用户修改UserName的时候就会产生同步数据的问题，这个需要程序来进行弥补
二. 主题表和主题回复分开保存
引用
1: user:用户基本信息表
字段有：user_id,user_name,email,homepage,tel,add...
2: forum_topic:主题表
字段有：id,user_id,title,content,....
3: forum_topic_back:主题回复表
字段有：id,topic_id,user_id,title,content,....
三. 主题表的内容单独设计成一个表
引用
1: user:用户基本信息表
字段有：user_id,user_name,email,homepage,tel,add...
2: forum_topic:主题表
字段有：id,user_id,title,....
3: forum_topic_content:主题内容表
字段有：id,topic_id,content
4: forum_topic_back:主题回复表
字段有：id,topic_id,user_id,title,content,....
四.用户信息分2个表保存，并对相关表进行分表处理
引用
1: 简单用户表 tb_user:
id , username
2: 用户详细信息表 tb_userinfo
id,userid , email , homepage , phone , address ...
3: 论坛主题表 tb_bbs
id , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime
4: 论坛内容标 tb_bbs_content (此表可按照bbsid进行分表存储) id,bbsid , content;
5: 论坛回复表 tb_bbs_reply (此表可按照bbsid进行分表存储)
id , bbsid , userid , content , replytime , ip
五.增加一个主题缓存表，取每个区的前面100条记录
引用
1: 简单用户表 tb_user:
字段有：id , username
2: 用户详细信息表 tb_userinfo
字段有：id,userid , email , homepage , phone , address ...
3: 论坛主题表 tb_bbs
字段有：id , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime
4: 论坛内容标 tb_bbs_content (此表可按照bbsid进行分表存储) 字段有：id,bbsid , content;
5: 论坛回复表 tb_bbs_reply (此表可按照bbsid进行分表存储)
字段有：id , bbsid , userid , content , replytime , ip
6: 主题缓存表 tb_bbs_cache
字段有：id , userid , title , ip , repleycount , replyuserid , createtime , lastreplytime
------------------------------------------------------------------------------
下面是针对上面的方案展开的讨论：
1：方案一表面上看起来好像少查了一张表，但由于冗余，因为帖子数量极大，会占用大量的空间。这种数据量大，但是对实时和数据绝对安全性要求较低的应用，大量使用缓存的话可以极大提高处理能力。
2：方案一你这么设计的话，索引怎么建比较好呢，还有就是会不会造成这个表过热，还有…… 我觉得像论坛这样的系统，使用缓存可以大大降低数据库的负载
3：大家的意思是分成主题表、回复表等多个表？ 还是合成一个表然后做物理分区？ 哪种更好呢？
4：再这么高插入更新的频率下 索引就有些不实用了，创建索引会降低插入更新的速度而且访问量这么大的情况下，索引不建议采用
5：就这样的一个论坛，实时在更新、发帖、回帖。我觉得在数据库上建立索引不太好，但是如果不建立索引如何来提高查询等方面性能呢？
6：都是分布式数据库了。放在多个表中，直接关联一点都没问题。重要是横向切分
7：认同分表，分库，缓存的做法
引用
问题分析:
每天论坛访问量300万左右，更新帖子10万左右。
1. 读写比例在30:1左右, 应向读取效率方面倾斜. 索引建立需参考常用读取的主关键字.
2. 每月数据在10W*30=300W. 可按月分表
3. 每年帖子在300W*12=3600W, 推算数据不会小于30T. 可按年分库
结构:
用户信息：独立表，userid主键
发帖、回帖：按月表存储，帖子唯一ID主键，日期索引。
帖子内容明细：按月存储，帖子唯一ID主键
8：拿一张500万的表来说事
引用
更新的时候如果没有索引的话
更新时间大概需要30秒左右 指的是全表更新~~
而查询某单行记录 却需要10秒左右~~
而加入索引的话
更新时间差不多慢了一倍有余
而查询记录则缩减到毫秒级~~
快了百倍有余~~
孰重孰轻 自己选
9：自己的一点经验:
引用
1.分表存储;
2.建立索引;SQL按所以查询的速度还是很快的;
3.避免整表扫描;先读取主题,在按照主题ID读取回复;再按照用户ID读取用户;而不要使用关联;
4.使用缓存;
10：需要分3张表，且建立索引。。。
理由如下：
引用
1：建立3张表可以避免冗余数据，维护起来方便。。。
2：每天论坛访问量300万左右，可见主要的压力来自于查询，sql查询的效率在于避免全表扫描，可见建立索引是必须的。。。
3：关于创建索引会降低插入更新的速度这个问题是不存在的。。。 因为，索引之所以会降低更新的速度的速度，是因为在更新完对应字段后还需要更新对应字段的索引。
4：看到更新帖子10万左右，这句话是说，我们可能对发帖标题，发帖内容，回复标题，回复内容这4个字段做更新。。。需要注意的是，这四个字段并不是用来建立表连接的字段，为了优化查询速度我们不会在这四个字段上建立索引，所以从这道题目出发，我们建立的索引不会影响更新帖子的性能。。。
所以，我认为最后的答案是建立3张表，在连接用到的字段上建立索引。。。
11：
引用
兩個表然後建一個視圖是否可行呢？
视图也是很慢的。
12：每天就更新10万个帖子，每天访问那么多，肯定是不能把所有的主贴放在一个表里，大表分小表，建立常用字段的索引，然后配置缓存。级联关系最好不
要配置，等需要的时候再查询。
13：虽然题目中没有说明，但实际应用中，查阅帖子通常只会分页显示，而一页最多也就显示几十个帖子，那么实际上只要SQL语句构造得好，T_USER表其实只是跟一个只有几十行结果集的的子查询进行连接，应该基本不用担心出现性能问题。
而且实际上，一个万行级的表简单关联百万行级的表（其实镇魂歌数量级在我看来其实也算不上很大的表），在数据库方面完全有很多优化方式，甚至可以通过提高硬件配置来改善性能，实在没有很大必要进行结构上的冗余。一旦结构有冗余，为了保证数据一致性，往往你还要消耗更多的资源，反而得不偿失。
14：分表有垂直和水平分表
引用
1：无论你拿多少记录(甚至是1条),如果两个大表关联都可能会产生非常大的中间值,如果你排序(排序字段没有用到索引),你都可能导致数据库采用各种各样的方式来计算。
2：索引会导致插入、更新记录很慢,大家都是知道的。
3：水平分表可以解决这个问题,只要你能保证每个表只存适合的记录数(例如100W一个表) (水平分区也可以解决IO的一些问题)
4：还有就读写分离,master是写,slave是读 (再加上cache,一般问题都还好了)
上面都是比较大的工作量,最好是保证你的数据库设计是合理的(范式是第一步,然后考虑反范式),基本上也能满足很多问题了。
15：方案四 把内容与其它信息分开的好处就是可以让每个表的文件最小化,对数据库操作压力会减小,操作速度会快,还可以搭配缓存,把内容根据情况进行缓存,可以尽量很少访问表数据。
引用
1：对于上述分表方式也可以适用于分库操作,这样就降低了数据库单库的压力,把压力分散到各个机器
2：我的做法就是尽量避免表关联
3：再就是对于sql语句尽量都保证索引有效,不能索引的sql,尽量采用能索引的高效方式解决
16：外围的方案：
引用
1 读方面，生成静态页，或者缓存最新最热的帖子。
2 写方面，估计主要是INSERT吧，这个可以异步操作的。所有的写贴操作放到一个队列然后批量执行插入数据库操作。
17：方案四比较靠谱，再加上定期转储，海量的cache，大型论坛就此搞定。
18：我觉得应该还是使用3张表比较合适。
引用
1：业务上说,很可能主贴跟回复贴拥有不同的扩展,比如附件什么的,都放在一张表里面,假如主贴跟回复存在个性需求,怎么办？无限加字段么？
2：主贴跟回复在同一张表里,会增大锁表的几率。
3：索引的确会降低表更新的速度,但是带来的查询效率提升也是很可观的,因此我觉得,索引不能不用,但是要少用。
4：建立表时,确实可以通过楼上某位仁兄回复所言,用水平分表的方式,其实原理就是用先算再查嘛。
5：在前端表现上,可以使用ajax等方式,分步骤取数据,比如主贴的内容先取出来,然后再逐步加载回复信息等。
19：提高速度的关键:
引用
1.建立索引并在查询时充分利用;
2.避免使用关联,这样避免整表扫描;使用关联不如多次使用主键查询来的快;
3.一些处理的功能尽可能放到内存中来做,比如组织主题和回复;
4.使用静态页面也是个不错的做法;
20：方案三是延续了hibernate二级缓存的思想， 对于经常更新的数据都设计成单独表，这样可以最大程度的利用hibernate缓存
21：没有fast=true的设置,有人说or比in 好,exists比in 好,索引比全表扫描好,分区能提高查询效率,但是分区要降低插入效率
我要说的是,没有fast=true的选项, 如果能找到一步,或者几步公式化的方法能提高效率,那么优化器自己就会做了,根本不用用户担心。
假设 or比in好,数据库优化器把in语法和or语法走的执行计划一样就可以了,何必折磨用户呢。
说点实际的,很多人张嘴就说,SQL优化就是避免全表扫描,不知道大家有没有了解过索引查找的原理.索引查找数据,有两步要做,第一步是索引中快速查询,索引里只存储了对应表数据的rowid, 所以还有第二步,根据rowid去得到全部的数据, 所以需要一次磁盘i/o, 不要小看磁盘I/O,通过索引查询出的结果比较多的时候,磁盘i/o的时间是非常大的,这个时候比全表扫描慢得多, 实际
上,oracle 10g基于成本的优化器(CBO),选择性不高的索引,优化器根本不会使用,而自动采用全表扫描的方式来做.
22：这个量级的bbs我设计过,当时是这样做的(方案五)：
引用
共四个表：
1. 用户表
2. 主题表(包含最后回复信息,最后回复人,最后回复id等)
3. 回复表
4. 主题缓存表(这个取每个区的前面100条记录),一般来说负载最大的就是主题的第一页,所以缓存表是个小表。
共3台app集群,1台web,2台oracle一主一备,运行下来速度还是可接受的。
23：不建议进行表的设计冗余，感觉就想重复代码一样，有坏味道
引用
1：缓存常用的页面和数据
2：读写表或库分开(基于垂直分隔)
3：数据库可以进行垂直分隔(字段分到多个表中)，再进行水平分隔（数据分到多个表中）
4：论坛功能可以进行分隔，不同的服务器负责不同的功能,如图片服务器，web服务器,邮件服务器等
总之，就是要细化分工
24：支持方案三的设计
读取的操作：
引用
1：显示帖子列表界面,如果主贴内容放在forum_topic表,那么这就是冗余的,假设都要获取100个帖子,一行的数据长度越大,数据库需要扫描的数据块就越多,性能也越差。
2：在打开一个帖子时,读操作通过索引关联到两张表（forum_topic和forum_topic_content）性能消耗对整个数据库来说不多。
写帖子的操作：
引用
发表帖子,对标题表和内容表分别作一个插入
更新非索引列不会引起索引更新:
引用
只要被索引的列（例如回复表的标题ID）不被频繁更新,即使索引所在地行的其它列被频繁update,索引也不会被更新从而产生性能消耗,一张表一天30万次的索引更新,因它引起的性能消耗小到即使数据库安装在奔腾3单核CPU下都能轻松承担下来, 为什么会有人对索引有这么大的误解呢？。对一个论坛(或者绝大部分的系统)来说,检索(SELECT)数据耗费的系统资源远远高于更新数据
(INSERT/UPDATE)本身,而索引是专门为检索数据服务的,难道就为了节省更新数据的小小的性能消耗,付出检索100条数据时需要数据库扫描几千万上亿条数据进行数据匹配的代价？如果是这样的话,即使是有32核顶级CPU的数据库作并行查询都未必顶得住。
做数据库设计,还是多了解数据库的原理才好。
25：数据库切分是必须的。
引用
1：垂直切分：用户表、用户信息表、主题表、主题内容表、回复表 2：水平切分：主题1、主题2、主题3、...、主题n
3：缓存：缓存路由表
4：再配合数据库读写分离和集群吧
另：其实论坛修改标题、内容的概率是很小的。大部分都是新增
第二篇：BBS论坛数据库设计代码
BBS论坛数据库设计代码
use master
go
----------------------------------------新建数据bbsDB的库
-------------------------------------------------------
--判断数据库是否存在使用exists关键字，在用sysdatabases关键字查询-- if exists(select * from sysdatabases where name = 'bbsDB')
--如果数据库存在就删除此数据库用drop（删除）database（数据库对象）bbsDB（数据库名）--
drop database bbsDB
--删除后在添加一个数据库create（添加）database（数据库对象）bbsDB（数据库名）on--
create database bbsDB
on
(
name = 'bbsDB_MDF', --数据库主文件名--
filename = 'F:\SQL\第四阶段\bbsDB_MDF.mdf', --数据库存储的路径-- size = 10mb, --数据库初使大小--
filegrowth = 10% --增长变化--
)
--设置日志文件--
log on
(
name = 'bbsDB_LDF', --日志文件名--
filename = 'F:\SQL\第四阶段\bbsDB_LDF.ldf', --日志文件存储路径-- size = 1mb, --日志文件初使大小--
maxsize = 20mb, --日志文件最大的可储存--
filegrowth = 10% --增长变化--
)
go
---------------------------------------以上信息为新建的数据库
-------------------------------------------------
use bbsDB
go
---------------------------------------新建bbsUsers表(用户
表)-------------------------------------------------
--判断bbsDB库是否有表bbsUser的存在exists关键字+sysobjects判断当前数据库表--
if exists(select * from sysobjects where name='bbsUsers')
--如果表存在就删除此表--
drop table bbsUser
--新建bbsUser表--
create table bbsUsers
(
UID int identity(1,1) not null, --用户编号,自动增长列，不允许空--
Uname varchar(50) not null, --用户名,不允许空--
Upassword varchar(50) not null, --密码,不允许空--
Uemail varchar(50), --邮箱--
Usex char(2) not null, --性别,不允许空--
Uclass int, --用户等级--
Uremark varchar(50), --备注信息--
UregDate datetime not null, --注册日期,不允许空--
Ustate int, --状态，是否在线等--
Upoint int, --用户的积分--
)
go
--------------------------------------为用户表添加各种约束
--------------------------------------------------------
--为bbsUser表添加约束，add constraint关键字PK_UID约束名，primary key（主键）设置UID字段为主键--
alter table bbsUsers
add constraint PK_UID primary key(UID)
--为Upassword添加约束，check(Upassword length>=6)字段长度大于等于6位，default设置默认值为888888--
alter table bbsUsers
add constraint CK_Upassword check(len(Upassword) >= 6)
alter table bbsUsers
add constraint DF_Upassword default('888888') for Upassword
--为Uemail字段添加约束，必须包含@号--
alter table bbsUsers
add constraint CK_Uemail check(Uemail like )
--为User字段添加约束，设置默认值为男，并且值只能是男或女--
alter table bbsUsers
add constraint DF_Usex default('男') for Usex
alter table bbsUsers
add constraint CK_Usex check(Usex='男' or Usex='女')
--为Uclass字段添加约束，默认值为1--
alter table bbsUsers
add constraint DF_Uclass default(1) for Uclass
--为UregDate字段添加约束，默认值为当前日期--
alter table bbsUsers
add constraint DF_UregDate default(getDate()) for UregDate
--为Ustate添加约束，默认值为0--
alter table bbsUsers
add constraint DF_Ustate default(0) for Ustate
--为Upoint字段添加约束，默认值为20--
alter table bbsUsers
add constraint DF_Upoint default(20) for Upoint
-----------------------------------以上信息为以建立的bbsUser（用户表）及各种约束-----------------------------------
go
-----------------------------------新建bbsSection表（版块表）
-----------------------------------------------------
--判断数据库内是否有表bbsSection的存在--
if exists(select * from sysobjects where name = 'bbsSection')
--如果有此表执行删除--
drop table bbsSection
--新建bbsSection表,及个字段--
create table bbsSection
(
SID int identity(1,1) not null, --版块编号，自动增长列--
Sname varchar(50) not null, --版块名称，不允许空--
SmasterID int not null, --版主ID--
Sprofile varchar(50), --版面简介--
SclickCount int, --点击率--
StopicCount int --发贴数--
)
-----------------------------------为表bbsSection（版块表）添加约束
-----------------------------------------------
go
--将SID字段设置为主键--
alter table bbsSection
add constraint PK_SID primary key(SID)
--为SmasterID设置外键，他的主键是bbsUsers表的UID字段foreign key(外键) references 主键表(主键表字段)--
alter table bbsSection
add constraint FK_SmasterID foreign key(SmasterID) references bbsUsers (UID)
--为SclickCount字段添加默认值为0--
alter table bbsSection
add constraint DF_SclickCount default(0) for SclickCount
--为字段StopicCount设置默认值为0--
alter table bbsSection
add constraint DF_StopicCount default(0) for StopicCount
---------------------------------以上为表bbsSection（版块表）建立及添加相应约束-------------------------------------
----------------------------------------新建bbsTopic表（主贴表）
---------------------------------------------------
go
--判断数据库中是否有bbsTopic表的存在--
if exists(select * from sysobjects where name = 'bbsTopic')
--删除此表--
drop table bbsTopic
--新建bbaTopic表及个字段--
create table bbsTopic
(
TID int identity(1,1) not null, --帖子编号--
TsID int not null, --版块编号--
TuID int not null, --发贴人ID--
TreplyCount int, --回复数量--
Tface int, --发贴表情--
Ttopic varchar(50) not null, --标题--
Tcontents varchar(50) not null, --正文--
Ttime datetime, --发贴时间--
TclickCount int, --点击数--
Tstate int not null, --状态--
TlastReply datetime --最后回复时间--
)
----------------------------------------为bbsTopic(主贴表)表个字段添加约束-----------------------------------------
go
--将TID字段设置为主键--
alter table bbsTopic
add constraint PK_TID primary key(TID)
--将TsID设置为外键，引用bbsSection表的主键SID字段-- alter table bbsTopic
add constraint FK_TsID foreign key(TsID) references bbsSection (SID) --将TuID字段设置为外键盘，引用bbsUsers表的主键UID字段-- alter table bbsTopic
add constraint FK_TuID foreign key(TuID) references bbsUsers (UID) --设置TreplyCount字段的默认值为0--
alter table bbsTopic
add constraint DF_TreplyCount default(0) for TreplyCount
--为Tcontents字段添加约束，他的值必须大于等于6位--
alter table bbsTopic
add constraint CK_Tcontents check(len(Tcontents) >= 6)
--为Ttime字段添加默认值，为当前日期--
alter table bbsTopic
add constraint DF_Ttime default(getDate()) for Ttime
--设置TclickCount字段默认值为0--
alter table bbsTopic
add constraint DF_TclickCount default(0) for TclickCount
--设置Tstate字段默认值为1--
alter table bbsTopic
add constraint DF_Tstate default(1) for Tstate
--为TlastReply字段添加约束，最后回复时间必须要晚于发贴时间-- alter table bbsTopic
add constraint CK_TlastReply check(TlastReply > Ttime)
-----------------------------------------以上为bbsTopic（主贴表）表建立及建立个字段---------------------------------
---------------------------------------------新建表bbsReply（回帖表）及个字段--------------------------------------
go
--判断数据库中是否有bbsReply（回帖表）的存在--
if exists(select * from sysobjects where name = 'bbsReply')
--删除bbsReply表--
drop table bbsReply
--创建bbsReply（回帖）表及个字段--
create table bbsReply
(
RID int identity(1,1) not null, --帖子编号--
RtID int not null, --主贴ID--
RsID int not null, --版块ID--
RuID int not null, --回帖人ID--
Rface int, --回帖表情--
Rcontents varchar(50) not null, --正文--
Rtime datetime, --回帖时间--
RclickCount int --点击数--
)
-----------------------------------------为bbsReply（回帖）表个字段添加约束----------------------------------------
go
--设置RID字段为主键--
alter table bbsReply
add constraint PK_RID primary key(RID)
--设置RtID字段为外键引用bbsTopic表的主键TID字段--
alter table bbsReply
add constraint FK_RtID foreign key(RtID) references bbsTopic(TID) --设置RsID为外键引用bbsSection表的主键SID--
alter table bbsReply
add constraint FK_RsID foreign key(RsID) references bbsSection(SID) --设置RuID为外键引用bbsUsers表的主键UID字段--
alter table bbsReply
add constraint FK_RuID foreign key(RuID) references bbsUsers(UID) --设置Rcontents字段必须大于6位--
alter table bbsReply
add constraint DF_Rcontents check(len(Rcontents) >= 6)
--设置Rtime字段默认值为当前日期--
alter table bbsReply
add constraint DF_Rtime default(getDate()) for Rtime
------------------------------------------------以上为创建bbsReply（回帖）表的内容---------------------------------
------------------------------------------------------向表格中插入数据
-----------------------------------------
go
--向bbsUsers（用户表插入数据）--
insert into
bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('天天','123456','zhong@163.com','女',2,'我们的家乡在希望的田野上',3,100)
insert into
bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('可卡因','HYXS007','ss@HotMail.com','女',1,'我要去公安局自首',1,200) insert into
bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('心酸果冻','888888','yy@HotMail.com','男',2,'牵匹瘦马闯天涯',2,600)
insert into
bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('冬篱儿','fangdong','bb@sohu.com','女',3,'爱迷失在天堂',4,1200) insert into
bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,Ustate,Upoint) values('Supper','master','dd@p.com','女',5,'BBS大斑竹',1,500)
--向bbsSection（版块表插入数据）--
insert into bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount) values('Java技术核心',3,'包含框架，开源',500,1)
insert into bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount) values('.Net技术',5,'包含C#，ASP',800,1)
insert into bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount) values('Linux/Unix社区',5,'包含系统维护运行',0,0)
--向bbsTopic（主贴表）插入数据--
insert into
bbsTopic(TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate,TlastReply)
values(1,3,3,1,'还是JAVA','JAVA文件如何读取',200,1,'2008-8-1')
insert into
bbsTopic(TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate,TlastReply)
values(2,2,1,2,'.NET文件部署','项目包含、WINFROM',0,1,'2007-8-5')
--向bbsReply（跟贴表）插入数据--
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(1,1,5,2,'JAVA中乱码问题怎么解决好？',100)
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(1,1,4,4,'你好你好我们我们',200)
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(1,1,4,4,'呵呵哈哈嘿嘿西西',200)
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(2,1,4,4,'JSP文件转换',200)
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount) values(2,1,4,4,'啊大安定大安定',200)
---------------------------------------------------以上为各表格插入测试数据
---------------------------------------
---------------------------------------------------------查询数据库情况
-------------------------------------------
print 'SQL Server的版本是：
print 'SQL Server的服务器名称是：
-----------------------------------------------------------------------------------------------------------------
---------------------------------------------------系统变量查询上一条语句错误--------------------------------------
update bbsUsers set Upassword = '1234' where Uname = '天天'
print '执行上条语句产生错误: '+convert(varchar(5),@@error) --输出上一条语句的错误信息--
-----------------------------------------------------------------------------------------------------------------
---------------------------------------------------查询用户天天的信息
---------------------------------------------
set nocount on --不打印影响的信息--
--查询个人信息--
print '天天个人信息如下'
select Uname,Uremark,Upoint from bbsUsers where Uname = '天天' --用变量存储ID查询信息--
declare @id int --声明变量--
select @id = UID from bbsUsers where Uname = '天天' --将bbsUsers表中的UID字段赋值给id变量--
print '发贴情况如下'
select
convert(varchar(10),Ttime,111),TclickCount,Ttopic,Tcontents,TclickCount,Tcontents from bbsTopic where TuID = @id
print '回贴情况如下'
select convert(varchar(10),Rtime,111),RclickCount,Rcontents from bbsReply
where RuID = @id
if(@id > 30) --if判断--
print '有权发贴' --条件为真输出--
else --否则--
print '无权发贴' --输出--
-----------------------------------------------------------------------------------------------------------------
----------------------------------------查询心酸果冻用户发贴回帖显示级别
-------------------------------------------
--声明变量用于存储用户ID--
declare @uNameID int
--声明变量用于存储用户发贴数量--
declare @count int
--声明变量用于存储用户回帖数量--
declare @count1 int
--将心酸果冻用户的ID号存到变量@uNameID中--
select @uNameID = UID from bbsUsers where Uname = '心酸果冻' --用count统计函数将用户的发贴数量存到变量@count中--
select @count = count(*) from bbsTopic where TuID = @uNameID --输出--
print '心酸果冻发贴数量为： ' + convert(varchar(10),@count)
print ''
print '具体内容如下： '
--显示信息,判断用户发贴数量大于0时显示帖子的信息--
if(@count > 0)
select Ttime as 发贴时间,TclickCount as 点击率,Ttopic as 主题,Tcontents as 内容 from bbsTopic where TuID = @uNameID
--用count统计函数将用户的回贴数量存到变量@count1中--
select @count1 = count(*) from bbsReply where RuID = @uNameID --输出--
print '心酸果冻的回帖数量为: '+convert(varchar(10),@count1)
--显示信息，用户回帖数量大于0时显示帖子信息--
if(@count1 > 0)
select Rtime as 回帖时间,RclickCount as 点击数量,Rcontents as 回帖内容 from bbsReply where RuID = @uNameID
--判断用户的等级--
if((@count + @count1) < 10)
print '心酸果冻贴数总计为: '+convert(varchar(10),@count + @count1)+' 功臣级别为: 新手上路'
if(((@count + @count1) >= 10) and ((@count + @count1) < 20))
print '心酸果冻贴数总计为: '+convert(varchar(10),@count + @count1)+' 功臣级别为: 侠客'
if(((@count + @count1) >= 20) and ((@count + @count1) < 30))
print '心酸果冻贴数总计为: '+convert(varchar(10),@count + @count1)+' 功臣级别为: 骑士'
if(((@count + @count1) >= 30) and ((@count + @count1) < 40))
print '心酸果冻贴数总计为: '+convert(varchar(10),@count + @count1)+' 功臣级别为: 精灵王'
if(((@count + @count1) >= 40) and ((@count + @count1) < 50))
print '心酸果冻贴数总计为: '+convert(varchar(10),@count + @count1)+' 功臣级别为: 光明使者'
if((@count + @count1) >= 50)
print '心酸果冻贴数总计为: '+convert(varchar(10),@count + @count1)+' 功臣级别为: 法老'
------------------------------------------------------------------------------------------------------------------
----------------------------------------回帖最多的为精华贴
---------------------------------------------------------
--声明整形变量用于存储主贴编号,和作者编号--
declare @tidid int
declare @zhuzheid int
declare @huifu int
--将主贴标号存到@tidid变量中--
select top 1 @tidid = TID from bbsTopic order by TreplyCount desc --将用户编号存到@zhuzheid变量中--
select @zhuzheid = TuID from bbsTopic where TID = @tidid
--输出--
print '第一精华贴的信息如下：'
--查出回贴最多的主贴，（用户表与主贴表两表连查）--
select Ttime as 发贴时间,TclickCount as 点击率,Uname as 作者,Ttopic as 主题,Tcontents as 内容 from bbsTopic
inner join bbsUsers
on bbsUsers.UID = bbsTopic.TuID
where bbsUsers.UID = @zhuzheid and bbsTopic.TID = @tidid
--为了美观输出空行--
print ''
--利用@tidid（主贴编号）变量查出回复数量存到@huifu变量中--
select @huifu = TreplyCount from bbsTopic where TID = @tidid
--输出--
print '回帖数量'+convert(varchar(10),@huifu)+',如下所示：'
--查询回帖信息--
select Rtime as 回帖时间,RclickCount as 点击率,回帖表情 = case
--case多分之语句判断回帖表情--
when Rface = 1 then '~(00)~猪头'
when Rface = 2 then '*:0)小丑'
when Rface = 3 then '[:|]机器人'
when Rface = 4 then '{~0~0~}老人家'
when Rface = 5 then '(:<)吹水大王'
end
,Rcontents as 回帖内容 from bbsReply where RtID = @tidid
-----------------------------------------------------------------------------------------------------------------
/*实现：1、论坛人气，点击率>1000为“人气熊旺旺”，否则为“一般般”
2、年度品牌版块：主贴量最多的版块
3、年度倒胃版块：主贴量最少的版块
4、年度回帖人气最旺奖：回帖的点击率排名前2名
5、年度最差斑竹：版块点击率低于500或主贴等于
0-------------------------------------------------*/
--1、论坛人气，点击率>1000为“人气熊旺旺”，否则为“一般般”--
print '---->>>>各位大虾注意了，本论坛即将发布年度奖项<<<<----'
if(select sum(SclickCount) from bbsSection) > 1000
print '论坛人气评估: 人气熊旺旺大家辛苦了'
else
print '论坛人气评估: 一般般,大家加油了'
--2、年度品牌版块：主贴量最多的版块--
print '年度最佳版块'
select Sname as 版块名称,StopicCount as 主贴数量,Sprofile as 简介 from bbsSection
where StopicCount = (select max(StopicCount) from bbsSection)
--3、年度倒胃版块：主贴量最少的版块--
print '年度倒胃版块'
select Sname as 版块名称,StopicCount as 主贴数量,Sprofile as 简介 from bbsSection
where StopicCount = (select min(StopicCount) from bbsSection)
--4、年度回帖人气最旺奖：回帖的点击率排名前2名--
print '年度回帖人气最旺的前两位'
select Uname as 大名,Uclass as 星级 from bbsUsers where uid in( select top 2 TuID from bbsTopic order by TclickCount desc)
--5、年度最差斑竹：版块点击率低于500或主贴等于0--
if exists(select * from bbsSection where StopicCount = 0 or SclickCount <= 500)
begin
print '请以下斑竹加油哦'
select Sname as 版块名称,StopicCount as 主贴数量,SclickCount as 点击率 from bbsSection
where StopicCount = 0 or SclickCount <= 500
end
-----------------------------------------------------------------------------------------------------------------
*-------------------------------------------用户发主贴
---------------------------------------------------------*/
--声明变量存放用户ID和版块ID--
declare @id int
declare @bankuai int
--将发贴人心酸果冻的ID存到变量@id中--
select @id = UID from bbsUsers where Uname = '心酸果冻'
--将版块编号放入@bankuai变量中--
select @bankuai = SID from bbsSection where Sname like '%.Net%' --将发贴内容插入到主贴表--
insert into bbsTopic(TsID,TuID,Tface,Ttopic,Tcontents)
values(@bankuai,@id,3,'什么是.Net啊？','我靠！微软的.Net广告超过半个北京城啊....')
--更新主贴表，.Net技术版块主贴数加1--
update bbsSection set StopicCount = StopicCount + 1 where SID = @bankuai --更新用户积分，如果是新主题则积分加100否则加50分--
if not exists(select * from bbsTopic where Ttopic like '什么是.Net啊？' and TuID <> @id)
update bbsUsers set Upoint = Upoint + 100 where UID = @id
else
update bbsUsers set Upoint = Upoint + 50 where UID = @id
--更新积分后更新级别--
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
--所有用户等级从新排名--
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
------------------------------------------------------------------------------------------------------------------
/*----------------------跟贴----------------------------------------------------------------*/ --声明变量用于存放回帖人编号及主贴ID--
declare @GTid int
declare @ZTid int
declare @BKid int
--获得可卡因的编号--
select @GTid = UID from bbsUsers where Uname = '可卡因'
--获得主贴的编号--
select @ZTid = TID from bbsTopic where Ttopic like '%什么是.Net%' --获得版块编号--
select @BKid = SID from bbsSection where Sname like '%.Net%'
--插入回帖内容--
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents)
values(@ZTid,@BKid,@GTid,2,'.NET是微软极力推崇的企业级信息网络共享平台.')
--回帖后主贴回复数量加1，点击率加1--
update bbsTopic set TreplyCount = TreplyCount + 1,TclickCount = TclickCount + 1 where TID = @ZTid
--回帖后相应的版块点击率加1--
update bbsSection set SclickCount = SclickCount + 1 where SID = @BKid
--回帖后给用户加分，如果是第一个回帖加100分，否则加50分--
if ((select top 1 RuID from bbsReply where RtID = @ZTid order by Rtime) = @GTid)
update bbsUsers set Upoint = Upoint + 100 where UID = @GTid
else
update bbsUsers set Upoint = Upoint + 50 where UID = @GTid
--更新用户相应的信息--
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
--发布主贴和跟贴--
select 主贴作者 = '心酸果冻',主贴发布时间 = convert(varchar(10),Ttime,111),主题 = Ttopic,内容 = Tcontents
from bbsTopic where TID = @@identity
select 回帖作者 = '可卡因',回帖时间 = convert(varchar(10),Rtime,111),回帖内容 = Rcontents
from bbsReply where RID = @@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
-----------------------------------------------------------------------------------------------------------------
/*------------------------------------------删除帖子
-----------------------------------------------------------*/
--声明变量用于存放回帖人编号及主贴ID--
declare @GTid int
declare @BZid int
declare @ZTid int
declare @BKid int
--获得斑竹心酸果冻的编号--
select @BZid = UID from bbsUsers where Uname = '心酸果冻'
--获得主贴的编号--
select @ZTid = TID from bbsTopic where Ttopic like '%什么是.Net%' --获得所有回帖人的编号--
select @GTid = RuID from bbsReply where RtID = @ZTid
--获得版块编号--
select @BKid = SID from bbsSection where Sname like '%.Net%' --被删帖的斑竹心酸果冻减去积分100分--
update bbsUsers set Upoint = Upoint - 100 where UID = @BZid --回贴的帖主均减积分50分--
update bbsUsers set Upoint = Upoint - 50 where UID = @GTid
--帖子所在版块的主贴量减1--
update bbsSection set StopicCount = StopicCount - 1 where SID = @BKid --如果回帖数量大于10给予严重警告，否则给予警告--
if((select TreplyCount from bbsTopic where TID = @ZTid) > 10) print '影响很坏，给予严重警告一次'
else
print '给予警告一次'
--开始删除，先删除跟贴在删除主贴--
delete from bbsReply where RtID = @ZTid
delete from bbsTopic where TID = @ZTid
--重新排名--
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
--重新星级排名--
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
-----------------------------------------------------------------------------------------------------------------
select * from bbsUsers
select * from bbsSection
select * from bbsTopic
select * from bbsReply