【网学网提醒】：网学会员为广大网友收集整理了，sql数据库创建数据库、表、约束语法，希望对大家有所帮助！
/*上机2*/
/*阶段一*/
usemaster
go
/*检查*/
ifexists(select*fromsysdatabaseswherename='bbsDB')
dropdatabasebbsDB
go
/*创建数据库*/
createdatabasebbsDB
onprimary
(
/*创建数据文件*/
name='stuDB_data',--主数据名字
filename='d:\project\stuDB_data.mdf',--存储位置
size=10mb,--初始值大小
filegrowth=20%--增长率
)
logon
(
/*创建日志文件*/
name='stuDB_log',
filename='d:\project\stuDB_log.ldf',
size=3mb,
filegrowth=10%
)
go
/*阶段二*/
usebbsDB
go
/*检查*/
ifexists(select*fromsysobjectswherename='Users')
droptableUsers
go
/*创建用户表*/
createtableUsers
(
UIDintidentity(1,1)notnull,--用户编号，主键
UNamevarchar(15)notnull,--
UPasswordvarchar(20)notnull,--密码
UEmailvarchar(20),--邮箱
UBirthdaydatetime,--生日
USexbitnotnull,--性别
UClassint,--用户的等级
URemarkvarchar(20),--备注信息
UStegDatedatetimenotnull,--注册日期
UStateint,--状态
UPointint--用户积分
)
go
/*阶段3*/
altertableUsers
addconstraintpk_UIDprimarykey(UID),--主键约束
/*默认约束*/
constraintdf_UPassworddefault(888888)forUPassword,--密码
constraintdf_USexdefault(1)forUSex,--性别
constraintdf_UStegDatedefault(getdate())forUStegDate,--时间
constraintdf_UStatedefault(0)forUState,--用户状态
constraintdf_UClassdefault(1)forUClass,--用户级别
constraintdf_UPointdefault(20)forUPoint,--用户积分
/*检查约束*/
constraintck_UPasswordcheck(len(UPassword)>=6),--密码
constraintck_UEmailcheck(UEmaillike'%@%')--邮箱
/*阶段4*/
/*创建板块表*/
usebbsDB
go
/*检查*/
ifexists(select*fromsysobjectswherename='Section')
droptableSection
go
createtableSection
(
SIDintidentity(1,1)notnull,--板块编号
SNamevarchar(32)notnull,--板块名字
SMasterIDintnotnull,--版主用户
SProfilevarchar(500),--版面简介
SClickCountint,--点击率
STopicCountint--发贴数
)
go
/*添加约束*/
altertableSection
addconstraintpk_SIDprimarykey(SID),--主键约束
constraintfk_SMasterIDforeignkey(SMasterID)referencesUsers(UID),--外键约束
constraintdf_SClickCountdefault(0)forSClickCount,--点击率
constraintdf_STopicCountdefault(0)forSTopicCount--发贴率
go
/*创建主贴表*/
usebbsDB
go
ifexists(select*fromsysobjectswherename='Topic')
droptableTopic
go
createtableTopic
(
TIDintidentity(1,1)notnull,--帖子编号
TSIDintnotnull,--板块编号
TUIDintnotnull,--发贴人Id
TReplyCountint,--回复数量
TFaceint,--发贴表情
TTopicvarchar(20)notnull,--标题
TContentsvarchar(500)notnull,--正文
TTimedatetime,--发贴时间
TClickCountint,--点击率
TStateintnotnull,--状态
TLastReplydatetime,--最后
回复时间
)
go
/*添加约束*/
altertableTopic
addconstraintpk_TIDprimarykey(TID),--主键约束
constraintfk_TSIDforeignkey(TSID)referencesSection(SID),--外键
constraintfk_TUIDforeignkey(TUID)referencesUsers(UID),--外键
constraintdf_TReplyCountdefault(0)forTReplyCount,--默认回复数量
constraintck_TContentscheck(len(TContents)>=6),--默认正文必须大于6个字符
constraintdf_TTimedefault(getdate())forTTime,
constraintdf_TClickCountdefault(0)forTClickCount,--默认点击率为0
constraintdf_TStatedefault(1)forTState,--默认状态
constraintck_TLastReplycheck(TLastReply>TTime)--最后回复时间
/*创建跟贴表*/
usebbsDB
go
/*检查*/
ifexists(select*fromsysobjectswherename='Reply')
droptableReply
go
createtableReply
(
RIDintidentity(1,1)notnull,--帖子编号
RTIDintnotnull,--主贴ID
RSIDintnotnull,--板块ID
RUIDintnotnull,--回帖人ID
RFaceint,--回帖表情
RContentsvarchar(500)notnull,--正文
RTimedatetime,--回帖时间
RClickCountint--点击率
)
go
/*添加约束*/
altertableReply
addconstraintpk_RIDprimarykey(RID),--主键约束
constraintfk_RTIDforeignkey(RTID)referencesTopic(TID),--外键
constraintfk_RSIDforeignkey(RSID)referencesSection(SID),--外键
constraintfk_RUIDforeignkey(RUID)referencesUsers(UID),--外键
constraintdf_RContentscheck(len(RContents)>=6),--正文必须大于6个字符
constraintdf_RTimedefault(getdate())forRTime--默认当前时间
/*插入数据*/
insertintoUsers(UName,UPassword,UEmail,UBirthday,USex,UClass,URemark,UState,UPoint)
select'可卡因','HYXS007','ss@hotamil','1987-07-19',1,1,'我要去公园',1,600union
select'心酸果冻','888888','ss@hotamil','1980-02-12',0,2,'走遍天涯海角',2,2200union
select'东篱儿','fangdong','bb@hotamil','1976-10-03',1,3,'爱迷失在天堂',4,1200union
select'Super','master','dd@hotamil','1977-05-16',1,5,'BBS大斑竹',1,5000
insertintoSection(SName,SMasterID,SProfile,SClickCount,STopicCount)
select'java技术',2,'讨论JAVA相关技术，包括J2EE,J2SE,J2ME....',500,1union
select'.NET',4,'讨论WEBSERVICE/XML,NETREMOTING,DUWAMI...',800,1union
select'Linux/Unix社区',4,'包括系统维护与使用区，程序开发区，内......',0,0
insertintoTopic(TSID,TUID,TReplyCount,TFace,TTopic,TContents,TClickCount,TState,TLastReply)
select1,4,2,1,'还是isp中的问题','isp文件中读取.....',200,1,'2010-6-9'union
select2,3,0,2,'部署.net中的问题','项目包括windows',0,1,'2010-6-5'
insertintoReply(RTID,RSID,RUID,RFace,RContents,RClickCount)
select1,1,4,2,'数据库连接池在....',100union
select1,1,3,4,'publicstaticDate...',200union
select2,2,1,3,'帮测试人员架AS...',0
go
/*上机3*/
/*阶段1*/
/*显示系统信息*/
print'SQL_Server的
版本：'+@@version
print'服务器的名称：'+@@servername
print'错误号：'+convert(varchar(5),@@error)
go
setnocounton--不显示sql语句行数
print'个人资料如下'
select昵称=UName,等级=UClass,个人说明=URemark,积分=UPointfromUserswhereUname='可卡因'
declare@UsersIDint--定义用户编号
select@UsersID=UIDfromUserswhereUName='可卡因'
print'可卡因发帖如下：'
select发帖时间=convert(varchar(10),TTime,111),点击率=TClickCount,主题=TTopic,内容=TContentsfromTopicwhereTUID=@UsersID
print'可卡因回帖如下：'
select回帖时间=convert(varchar(10),RTime,111),点击率=RClickCount,回帖内容=RContentsfromReplywhereRUID=@UsersID
declare@pointint
if(@point>30)
print'可卡因的限权：有权发帖'
else
print'可卡因的限权：无权发帖'
go
/*阶段2*/
print'开始提分，请稍后......'
declare@numberint--提分值
declare@ageint--平均分
set@number=0
/*提分*/
while(1=1)
begin
updateUserssetUpoint=Upoint+50whereUstate<>4
set@number=@number+50
select@age=avg(Upoint)fromUsers
if(@age>2000)
break
end
print'提升分值：'+convert(varchar(8),@number)
go
/*提分后更新*/
updateUsers
setUClass=case
whenUpoint<500then1
whenUpointbetween500and1000then2
whenUpointbetween1001and2000then3
whenUpointbetween2001and3000then4
whenUpointbetween4001and5000then5
else6
end
print'------------------------------------------加分后的用户级别-------------------------------------'
select昵称=UName,星级=case
whenUClass=0then''
whenUClass=1then'★'
whenUClass=2then'★★'
whenUClass=3then'★★★'
whenUClass=4then'★★★★'
whenUClass=5then'★★★★★'
else'★★★★★★'
end
,积分=upointfromUsers
go
/*阶段3*/
declare@UsersIDint,@tcountint--发帖数
select@UsersID=UIDfromUserswhereUName='心酸果冻'
select@tcount=count(*)fromtopicwheretuid=@usersid
if@tcount>0
begin
print'心酸果冻发贴数：'+convert(varchar(2),@UsersID)
print'帖子如下：'
select发帖时间=convert(varchar(10),TTime,111),点击率=TClickCount,主题=TTopic,内容=TContentsfromTopicwhereTUID=@UsersID
end
else
print'心酸果冻发贴数：0'
declare@numberint--回帖数
select@number=count(*)fromReplywhereRUID=@usersid
if@number>0
begin
print'心酸果冻回帖数：'+convert(varchar(8),@number)
select发帖时间=convert(varchar(10),RTime,111),点击率=RClickCount,主题=RContentsfromReplywhereRuid=@usersID
end
else
print'心酸果冻回帖数：0'
declare@sumberint,@classvarchar(8)--帖数总计
set@sumber=@number+@tcount
select@class=case
when@sumber<10then'新手上路'
when@sumber>=10and@sumber<=20then'侠客'
when@sumber>20and@sumber<=30then'骑士'
when@sumber>30and@sumber<=40then'精灵王'
when@sumber>40and@sumber<=50then'光明使者'
else'法老'
end
print'心酸果冻贴数总计：'+(convert(varchar(8),@sumber))+'贴'+'功臣级别：'+@class
/*课后*/
print'第一精华帖的信息如下：'
declare@Usint
select@Us=(selecttop1tidfromtopicorderbyTReplyCountdesc)
declare@numbersint--回帖数最多
select@numbers=max(TReplyCount)fromTopic
select发帖时间=TTime,点击率=TClickCount,作者=(selectunamefromuserswhereuid=TUID),主题=TTopic,内容=TContentsfromTopicwheretid=@us
print'回复数：'+convert(varchar(10),@numbers)+'如下提示：'
select回帖时间=convert(varchar(10),RTime,111),点击率=RClickcount,回帖表情=RFace,回帖内容=RContentsfromReplywherertid=@us
selectmax(mm)from(selectmm=count(*)fromreplygroupbyrtid)asaa--没有回复字段时
/*上机4*/
/*阶段1*/
select*fromsection
print'-------------------各位大虾注意了，本论坛即将发布年度无记名评奖--------------------'
if(selectsum(sclickcount)fromsection)>1000
print'论坛人气年度评估：熊汪汪，大家辛苦了'
else
print'论坛人气年度评估：一般般'
print'年度品牌板块:'
select板块名称=SName,主帖数量=STopicCount,简介=sprofilefromsectionwhereStopicCount=(selectmax(StopicCount)fromsection)
print'年度倒胃板块：'
select板块名称=SName,主帖数量=STopicCount,简介=sprofilefromsectionwhereStopicCount=(selectmin(StopicCount)fromsection)
print'年度回帖人气最IN的前两名：'
select大名=uname,星级=uclassfromuserswhereuidin(selecttop2tuidfromtopicorderbytclickcountdesc)
ifexists(select*fromsectionwhereSTopicCount=0orsclickcount<=500)
begin
print'请下列版主加油哦！'
select板块名称=SName,主帖数量=STopicCount,点击率=sclickcountfromsectionwhereSTopicCount=0orsclickcount<=500
end
/*阶段2*/
--select*fromusers
/*插入*/
declare@sidint--板块id
insertintotopic(tsid,tuid,ttopic,tcontents)
select1,4,'什么是.Net啊?','微软的.Net广告超过半个北京城啊....'
select@sid=tsidfromtopicwheretuid=4
/*更新加分*/
updatesectionsetstopiccount=stopiccount+1wheresid=@sid
ifnotexists(select*fromtopicwherettopiclike'什么是.Net啊?')
updateuserssetupoint=upoint+100whereuid=(selectuidfromuserswhereuname='心酸果冻')
else
updateuserssetupoint=upoint+50whereuid=(selectuidfromuserswhereuname='心酸果冻')
updateusers
setuclass=case
whenupoint<500then1
whenupointbetween500and1000then2
whenupointbetween1001and2000then3
whenupointbetween2001and4000then4
whenupointbetween4001and5000then5
else6
end
whereuid=(selectuidfromuserswhereuname='心酸
果冻')
select发帖作者='心酸果冻',发帖时间=convert(varchar(10),ttime,111),主题=ttopic,内容=tcontentsfromtopicwheretid=@@identity
select昵称=uname,星级=case
whenuclass=0then''
whenuclass=1then'★'
whenuclass=2then'★★'
whenuclass=3then'★★★'
whenuclass=4then'★★★★'
whenuclass=5then'★★★★★'
else'★★★★★★'
end
,积分=upointfromusers
go
/*阶段3*/
select*fromreply--回帖
select*fromsection--板块
select*fromtopic--主帖
select*fromusers--用户表
--selectuidfromuserswhereuname='可卡因'
updatetopicsettreplycount=treplycount+1,tclickcount=tclickcount+1
wheretidin(selecttidfromtopicwherettopic='什么是.Net啊?')
updatesectionsetSClickCount=SClickCount+1
whereSIDin(selectrsidfromreplywherertid=(selecttidfromtopicwherettopic='什么是.Net啊?'))
ifexists(select*fromreplywherertid=(selecttidfromtopicwherettopic='什么是.Net啊?'))
begin
updateuserssetupoint=upoint+100
end
else
updateuserssetupoint=upoint+50
insertintoreply(RTID,RSID,RUID,RContents)
select3,1,4,'.Net是微软力推的企业级信息网络共享平台'
/*更新等级*/
updateusers
setuclass=case
whenupoint<500then1
whenupointbetween500and1000then2
whenupointbetween1001and2000then3
whenupointbetween2001and4000then4
whenupointbetween4001and5000then5
else6
end
whereuidin(selectuidfromuserswhereuname='可卡因')
select发帖作者='可卡因',发帖时间=convert(varchar(10),rtime,111),内容=rcontentsfromreplywhererid=@@identity
select昵称=uname,星级=case
whenuclass=0then''
whenuclass=1then'★'
whenuclass=2then'★★'
whenuclass=3then'★★★'
whenuclass=4then'★★★★'
whenuclass=5then'★★★★★'
else'★★★★★★'
end
,积分=upointfromusers
go
/*课后*/
select*fromreply--回帖
select*fromsection--板块
select*fromtopic--主帖
select*fromusers--用户表
declare@tuint,@tiint
select@tu=tuid,@ti=tidfromtopicwherettopic='什么是.Net啊?'
updateuserssetupoint=upoint-100whereuid=(selecttuidfromtopicwherettopic='什么是.Net啊?')
updateuserssetupoint=upoint-50whereuidin(selectruidfromreplywherertid=(selecttidfromtopicwherettopic='什么是.Net啊?'))
updatesectionsetSTopicCount=STopicCount-1wheresidin(selectrsidfromreplywherersid=(selecttsidfromtopicwherettopic='什么是.Net啊?'))
declare@tint
select@t=TReplyCountfromtopicwherettopic='什么是.Net啊?'
if(@tu>10)
print'严重警告'
else
print'一般警告'
deletefromreplywherertid=@ti
deletefromtopicwherettopic='什么是.Net啊?'
