站点导航：开发者在线 Builder.com.cn数据库后台数据库设计及实现

后台数据库设计及实现

开发者在线 Builder.com.cn 更新时间:2008-03-30作者：颠覆 来源:CSDN

本文关键词： 实现 设计 后台 数据库

创建数据库.sql

--调用master数据库
use master
go
--判断数据库bbsDB是否存在，如果存在，则删除
if exists(select * from sysdatabases where name ='bbsDB')
drop database bbsDB
--exec xp_cmdshell 'mkdir f:project'    --创建一个'f:project'
go
--创建bbsDB数据库
create database bbsDB         --主数据文件
on
(
name='bbsDB_data',        --主数据文件的逻辑名
filename='f:bsDBbsDB_data.mdf',        --主数据文件的物理名
size=10MB,            --主数据文件初始大小
filegrowth=20%            --主数据文件的增长率
)
LOG on                --日志文件
(
name='bbsDB_log',        --日志文件逻辑名
filename='f:bsDBbsDB_data.ldf',        --日志文件物理名
size=1MB,            --日志文件的初始大小
filegrowth=10%            --日志文件的增长率
)

创建表bbsReply(回帖表).sql 

--运行bbsDB数据库
use bbsDB
go
--检查表bbsReply是否存在，如果存在就删除
if exists(select * from sysobjects where name='bbsReply')
drop table bbsReply
--创建表bbsReply
create table bbsReply
(
RID int not null identity(1,1),
RTID int not null,
RSID int not null,
RUID int not null,
RFace int,
RContents varchar(200) not null,
RTime datetime not null,
RClickCount int not null,
)
--为表增加外键(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)
--增加默认值
alter table bbsReply add constraint DK_RTime default(getdate()) for RTime
--增加默认值
alter table bbsReply add constraint DK_RClickCount default(0) for RClickCount
--查看表数据
select * from bbsReply
--插入测试数据
insert into bbsReply (RTID,RSID,RUID,RFace,RContents,RTime,RClickCount)
values (1,1,5,2,'jsp乱码问题该怎么解决最好，因为我发现这个问题好象在好多地方都看见了',getdate(),100)
insert into bbsReply (RTID,RSID,RUID,RFace,RContents,RTime,RClickCount)
values (1,1,4,4,'转换jsp..',getdate(),200)
insert into bbsReply (RTID,RSID,RUID,RFace,RContents,RTime,RClickCount)
values (2,2,2,3,'.net很精彩，就像ppmm啊！',getdate(),200)

创建表bbsSection(版块表).sql

--使用数据库bbsDB
use bbsDB
go
--判断是否存在表bbsSection，如果存在就删除
if exists(select * from sysobjects where name='bbsSection')
drop table bbsSection
go
--创建表bbsSection
create table bbsSection
(
SID int identity(1,1) not null primary key,    --版块编号
Sname varchar(30) not null,            --版块名称
SmasterID int not null,                --版主ID,外键;引用用户bbsUsers的UID
Sprofile varchar(50),                --版面简介
SclickCount int,                --点击率
StopicCount int                    --发帖数
)
--为bbsSection创建外键，引用bbsUsers的UID
alter table bbsSection 
add constraint FK_Section_UID 
foreign key (SmasterID) references bbsUsers(UID)
--为bbsSection增加默认值
alter table bbsSection add constraint DF_SclickCount default(0) for SclickCount
alter table bbsSection add constraint DF_StopicCount default(0) for StopicCount

--插入数据
insert into bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount)
values('Java技术',3,'包含框架，开源，非技术区，J2SE',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,SclickCount,StopicCount)
values('Linux/Unix社区',5,'包含系统维护与使用区，程序开发区别',0,0)
select * from bbsSection

创建表bbsTopic(主帖表).sql

--使用bbsDB
use bbsDB
go
--判断是否存在表bbsTopic，如果存在就删除
if exists(select * from sysobjects where name='bbsTopic')
drop table bbsTopic
go
--创建表bbsTopic
create table bbsTopic
(
TID int identity(1,1) not null primary key,    --帖子编号
TSID int not null,                --版块编号；外键，引用bbsSection表的主键SID
TUID int not null,                --发帖人ID；外键，引用bbsUsers表的主键UID
TReplyCount int not null,            --回复数量
TFace int,                    --发帖表情
TTopic varchar(50) not null,            --标题
TContents varchar(200) not null,        --发帖内容
TTime datetime not null,            --发帖时间
TClickCount int not null,            --点击数
TState int not null,                --状态，例如是否被锁，是否为精华帖子
TLastReply datetime,                --最后回复时间
)
--把TSID设置为外键，引用bbsSection表的SID主键
alter table bbsTopic add constraint FK_Topic_SID foreign key(TSID) references bbsSection(SID)
--把TUID设置为外键，引用bbsUsers表的UID主键
alter table bbsTopic add constraint FK_Topic_UID foreign key(TUID) 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

--插入测试数据
insert into bbsTopic (TSID,TUID,TReplyCount,TFace,TTopic,TContents,TTime,TClickCount,TState,TLastReply)
values(1,3,2,1,'还是JSP中...','jsp文件中读取...',2005-08-01,200,1,2005-08-01)
insert into bbsTopic (TSID,TUID,TReplyCount,TFace,TTopic,TContents,TTime,TClickCount,TState,TLastReply)
values(2,2,0,2,'部署.net...','项目包括WinSe...',getdate(),200,1,getdate())
select * from bbsTopic

创建表bbsUsers(用户信息表).sql

--使用bbsDB数据库
use bbsDB
go
--判断是否存在表bbsUsers，如果存在就删除
if exists(select * from sysobjects where name='bbsUsers')
drop table bbsUsers
go
--创建表bbsUsers
create table bbsUsers
(
UID int identity(1,1) not null,        --自动编号,标识列
Uname varchar(15) not null,        --昵称
Upassword varchar(10),            --密码
Uemail varchar(50),            --邮件
Usex bit not null,            --性别
Uclass int,                --级别（几星级）
Uremark varchar(50),            --备注
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)    --把UID设为主键
--默认设置
alter table bbsUsers add constraint DF_Upassword  default (888888) for Upassword    --初试密码默认为6个8
alter table bbsUsers add constraint DF_Usexm default (1) for Usex    --性别默认为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_Uclass default (1) for Uclass    --默认级别为1（星级）
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,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('可卡因','HYXS007','SS@hotmail.com',1,'1','我要去公安局自首',getdate(),0,200)
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('心酸果冻','888888','lyzTTT@hotmail.com',0,'2','牵匹瘦马闯天下',getdate(),0,200)
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('冬篱儿','fangdong','bb@sohu.com',1,'3','爱迷失在天堂',getdate(),0,600)
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('Supper','master','dd@p.com',1,'6','BBS大斑竹',getdate(),0,5000)
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('可卡因','HYXS007','SS@hotmail.com',1,'1','我要去公安局自首',getdate(),0,200)
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('心酸果冻','888888','lyzTTT@hotmail.com',0,'2','牵匹瘦马闯天下',getdate(),0,200)
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('冬篱儿','fangdong','bb@sohu.com',1,'3','爱迷失在天堂',getdate(),0,600)
insert into bbsUsers(Uname,Upassword,Uemail,Usex,Uclass,Uremark,UregDate,Ustate,Upoint)
values('Supper','master','dd@p.com',1,'6','BBS大斑竹',getdate(),0,5000)

--查询bbsUsers
select * from bbsUsers

对数据库的操作.sql

 

--使用bbsDB
use bbsDB
go
--查询bbsDB内的表
select * from bbsUsers        --用户表
select * from bbsTopic        --主帖表
select * from bbsReply        --回帖表
select * from bbsSection    --版块表

/*1.使用系统变量，查询数据库系统情况*/
--查询SQL Server的版本号、服务器的名称、错误号等
print 'SQL Server的版本:'+ @@version
--查询服务器名称
print '服务器的名称:'+ @@servername
--修改会发生错误，因为Upassword限制必须大于6位,用@@error接受
update bbsUsers set Upassword='1234' where Uname='可卡因'
print convert(varchar(5),@@error)

/*2.网上有人举报可卡因涉嫌发表不合法言论，版主希望查看核实可卡因的发贴情况和权限*/
set nocount on    --不显示T-SQL语句影响的行数的信息
print ' '
print '个人资料如下:'            --查看可卡因个人资料
select 昵称=Uname,登记=Uclass,个人说明=Uremark,积分=Upoint
from bbsUsers where Uname='可卡因'
declare @userID int --定义变量，用于存放用户编号值
select @userID=UID from bbsUsers where Uname='可卡因'    --变量赋值
print '可卡因发帖如下：'            --查看可卡因发帖情况
select 发帖时间=convert(varchar(10),Ttime,111),点击率=TClickCount,
主题=TTopic,内容=TContents,状态=TState from bbsTopic where TUID=@userID
print '可卡因回帖如下:'            --查看可卡因回帖情况
select 回帖时间=convert(varchar(10),Rtime,111),点击率=RClickCount,
内容=Rcontents  from bbsReply where RuID=@userID
print '可卡因权限:'            --查看可卡因权限，如果积分大于30则可以发帖
declare @point int
select @point=Upoint from bbsUsers where Uname='可卡因'
if (@point>30)
    print '有权发贴'
else
    print '无权发帖'
go

/*3.循环反复提份(每次提分50),直到积分平均分达到2000以上*/
select * from bbsUsers
declare @avg int,@score
set @score=0
while(1=1)
    begin
        update bbsUsers set Upoint=Upoint+50 where Ustate<>4    --除了被封杀的帖子
        set @score=@score+50                    --记录提分值
        select @avg=avg(Upoint) from bbsUsers            --获取提分后的平均分
        if(@avg>2000)
            break
    end
print '提升分值:'+convert(varchar(8),@score)

/*4.给用户评星级:0～500评为1星级,500～1000评为2星级,1000～2000评为3星级,2000～4000评为4星级,
4000～5000评为5星级,5000以上评为6星级*/
--更新用户对应的等级
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
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

/*5.查询心酸果冻的发帖数和回帖数,如果发帖数>0,显示发帖数和具体的帖子信息,否则显示发帖数为:0帖;同理,回帖也如此.
最后显示帖子总计数量(发帖数+回帖数),并根据帖子总量显示功臣级别*/
select * from bbsTopic
select * from bbsUsers
select * from bbsReply
declare @uid int        --记录心酸果冻的的UID
declare @total int        --记录总帖子数
set @total=0
select @uid=UID from bbsUsers where Uname='心酸果冻'
print '心酸果冻发帖如下:'
select @total=@total+count(*) from bbsTopic where TUID=@uid
print '帖子如下:'
select convert(varchar(10),TTime,111),点击率=TClickCount,
内容=TContents from bbsTopic where TUID=@uid
print '心酸果冻回帖如下:'
select @total=@total+count(*) from bbsReply where RUID=@uid
print '帖子如下:'
select convert(varchar(10),RTime,111),点击率=RClickCount,
内容=RContents from bbsReply where RUID=@uid
print '心酸果冻帖数总计:'+convert(varchar(8),@total)
select 功臣级别=case
        when @total <10 then '新手上路'
        when @total between 10 and 20 then '侠客'
        when @total between 21 and 30 then '骑士'
        when @total between 31 and 40 then '精灵王'
        when @total between 41 and 50 then '光明使者'
        else '法老'
    end
go

/*6.选出精华帖(回帖量最多的帖子为精华帖子)*/
set nocount on
select * from bbsReply
select * from bbsTopic
select * from bbsUsers
select * from bbsSection
print '第一精华帖的信息如下'
declare @max int            --存储最多回帖数
declare @rtid int            --存储精华帖编号
declare @ttopic varchar(50)        --存储精华帖的标题
declare @name varchar(20)        --存储精华帖作者姓名
declare @uid int            --存储精华帖作者编号
select top 1 @max=count(*),@rtid=rtid from bbsReply group by RTID order by RTID
select @ttopic=TTopic,@uid=TUID from bbsTopic where TID=@rtid
select @name=Uname from bbsUsers where UID=@uid
select 发帖时间=convert(varchar(10),TTime,111),点击率=TClickCount,作者=@name,主题=@ttopic,内容=Tcontents
from bbsTopic where TID=@rtid
print '回帖数:'+convert(varchar(