T-SQL从建库建表到添加约束添加数据和查询(注释不是很全)

最新推荐文章于 2020-05-11 20:34:59 发布

a617508321

最新推荐文章于 2020-05-11 20:34:59 发布

阅读量268

点赞数

文章标签：数据库 java 操作系统

原文链接：http://www.cnblogs.com/bq-blog/archive/2010/03/09/1681697.html

版权

EXEC sp_configure 'show advanced options',1;--打开某个服务
RECONFIGURE
EXEC SP_CONFIGURE 'XP_CMDSHELL',1;--设置为‘1’
RECONFIGURE
GO

use master
--检查是否存在bbsDB数据库：查询master数据库中的系统表sysdatabases--
IF EXISTS (SELECT * FROM sysdatabases where name='bbsDB')
DROP database bbsDB
EXEC XP_cmdshell'mkdir e:\project'--调用DOS命令创建文件夹，后续
/*----建库----*/
create database bbsDB
on
(
/*--数据库德具体描述--*/
name='bbsDB_data',--主数据库文件的逻辑名
filename='e:\project\dataDB_data.mdf',--主数据库文件的物理名称
size=10mb,--主数据库文件的初始大小
filegrowth=10%--主数据库文件的增长率
)
LOG ON
(
--日志文件的具体描述，个参数含义同上--
NAME="bbsDB_log",
filename='e:\project\dataDB_log.ldf',
size=3mb,
maxsize=20mb,
filegrowth=10%
)
GO
--*********************************************************
exec sp_configure 'show advanced options',1;--关闭这个服务
RECONFIGURE
EXEC SP_CONFIGURE 'XP_CMDSHELL',0;
RECONFIGURE
GO
-------------------------------------
--下面创建table
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(50) not null,  --昵称
Upassword varchar(15)not null, --密码
Uemail varchar(20) ,   --邮件
Ubirthday datetime null,  --生日
Usex bit NOT NULL,    --性别
Uclass INT ,     --级别（几星级）
Uremark varchar(20) ,   --备注
UregDate datetime not null,  --注册日期
Ustate int null,    --状态（是否禁言）
Upoint int null     --几分
)
GO
------------添加约束---------------
ALTER TABLE bbsUsers ADD CONSTRAINT PK_UID  --主键
PRIMARY KEY(UID)
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Upassword--初始密码默认为6个8
DEFAULT(888888) for Upassword
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Usex  --性别默认为男（1）
DEFAULT(1) FOR Usex
ALTER TABLE bbsUsers ADD CONSTRAINT DF_Uclass --默认级别为1星级
DEFAULT(1) FOR Uclass
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 --默认积分为20点
DEFAULT(20) FOR Upoint
ALTER TABLE bbsUsers ADD CONSTRAINT CK_Uemail --必须包好'@'字符
CHECK(Uemail like '%@%.%')
ALTER TABLE bbsUsers ADD CONSTRAINT CK_Upassword--密码至少6位
CHECK(LEN(Upassword)>=6)
GO
-----------测试表--------------
--插入测试数据
INSERT INTO bbsUsers(Uname,Upassword,Uemail,Ubirthday,Uremark,Upoint)
VALUES('王立勋','wanglixun','wang@163.com','1984-7-9','大家要好好学习',100)
INSERT INTO bbsUsers(Uname,Upassword,Uemail,Ubirthday,Uremark,Upoint)
VALUES('可卡因','HYXS007','SS@HotMail.com','1978-7-9','我要去公安局自首',200)
INSERT INTO bbsUsers(Uname,Upassword,Uemail,Ubirthday,Uremark,Upoint)
VALUES('心酸果冻','888888','yy@HotMail.com','1980-02-12','走遍天涯海角...',100)
INSERT INTO bbsUsers(Uname,Upassword,Uemail,Ubirthday,Uremark,Upoint)
VALUES('冬篱儿','fangdong','bb@sohu.com','1976-10-03','迷失在网络里...',100)
INSERT INTO bbsUsers(Uname,Upassword,Uemail,Ubirthday,Uremark,Upoint)
VALUES('Super','master','dd@p.com','1977-05-16','BBS大斑竹...',100)
delete from bbsUsers where Uname='王立勋'

--------查询结果----------
select * FROM bbsUsers   --查询结果
GO
-----------阶段四————创建表并添加约束-------------
USE bbsDB
IF EXISTS(SELECT * FROM sysobjects where name='bbsSection')
drop table bbsSection
-----创建bbsSection表------
CREATE TABLE bbsSection
(
SID INT IDENTITY(1,1)NOT NULL, --版块编号，自动增长
Sname VARCHAR(32) not null,  --版块名称
SmasterID INT NOT NULL,   --版主ID 引用用户表UID
Sprofile varchar(60) null,  --版面介绍
SclickCount int null,   --点击率
StopicCount int null   --发帖数
)
GO
-----------------为表bbsSection添加约束-----------------------
ALTER TABLE bbsSection ADD CONSTRAINT PK_SID  --设置主健
PRIMARY KEY(SID)
ALTER TABLE bbsSection ADD CONSTRAINT FK_SmasterID --设置外健引用用户表的UID
FOREIGN KEY(SmasterID)REFERENCES bbsUsers(UID)
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,'论坛java技术，包括J2EE,J2ME,J2SE.....',500,1)
INSERT INTO bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount)
VALUES('.NET技术',5,'论坛Web ServiceXML,NET Remotion,Duwami.....',800,1)
INSERT INTO bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount)
VALUES('11',3,'论坛java技术，包括J2EE,J2ME,J2SE.....',500,1)
INSERT INTO bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount)
VALUES('11',3,'论坛java技术，包括J2EE,J2ME,J2SE.....',500,1)
delete from bbsSection where Sname='11'
INSERT INTO bbsSection(Sname,SmasterID,Sprofile,SclickCount,StopicCount)
VALUES('Linux/Unix社区',5,'包含系统维护与使用区，程序开发区，内核及.....',500,1)
GO
----------------------查询bbsSection表是否成功---------------------------
select * from bbsSection

--------------------------创建bbsTopic主贴表------------------------------
USE bbsDB
IF EXISTS (SELECT * FROM sysobjects where name='bbsTopic')
DROP TABLE bbsTopic
CREATE TABLE bbsTopic
(
TID INT IDENTITY(1,1) NOT NULL,  --帖子编号，自动增长
TsID INT NOT NULL,     --版块编号外健，引用版块的SID
TuID INT NOT NULL,     --发帖人ID 引用用户表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

----------------------为bbsTopic表添加约束---------------------
ALTER TABLE bbsTopic ADD CONSTRAINT PK_TID  --设置主健
PRIMARY KEY(TID)
ALTER TABLE bbsTopic ADD CONSTRAINT FK_TsID  --设置外健引用bbsSection表中的SID
FOREIGN KEY(TsID) REFERENCES bbsSection(SID)
ALTER TABLE bbsTopic ADD CONSTRAINT FK_TuID  --设置外健引用bbsUsers表中的UID
FOREIGN KEY(TuID) REFERENCES bbsUsers(UID)
ALTER TABLE bbsTopic add constraint DF_TreplyCount --默认为0 回复数
DEFAULT(0) FOR TreplyCount
ALTER TABLE bbsTopic add constraint CK_Tcontents --正文必须大于6个字符
check(len(Tcontents)>6)
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
GO
-------------------向bbsTopic表中插入数据------------------------
insert into bbsTopic (TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate,TlastReply)
values(1,3,2,1,'还是jsp中的问题','jsp文件中读取....',200,1,2007-10-11)
insert into bbsTopic (TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,TclickCount,Tstate,TlastReply)
values(2,2,0,2,'部署.net中的问题','项目包括windows....',200,1,2007-10-12)
---------检查bbsTopic表是否建成-----------
select * from bbsTopic

----------------------创建bbs的Reply回帖表-----------------------------
USE bbsDB
IF EXISTS (SELECT * FROM sysobjects where name='bbsReply')
DROP TABLE bbsReply
CREATE TABLE bbsReply
(
RID INT IDENTITY(1,1) NOT NULL,  --自动编号，帖子编号
RtID INT NOT NULL,  --主贴ID外健引用主贴表中的TID
RsID INT NOT NULL,  --版块ID外健引用版块表中的SID
RuID INT NOT NULL,  --回帖人ID 外健引用用户标中的UID
Rface int null,   --回帖表请
Rcontents varchar(30) not null, --正文，必须大于6个字符
Rtime datetime null,  --回帖时间
RclickCount int null  --点击数
)
GO

----------------------添加bbsReply表的约束------------------------
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 --正文必须大于6个字符
CHECK(LEN(Rcontents)>6)
ALTER TABLE bbsReply ADD CONSTRAINT DF_Rtime --设置回帖时间必须是当天的时间
DEFAULT(getDate()) for Rtime
GO
----------------向表bbsReply中添加数据----------------------
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount)
values(1,1,5,2,'数据库连接池在......',100)
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount)
values(1,1,4,4,'public static Data......',200)
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount)
values(1,1,5,2,'***123412341234',100)
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount)
values(1,1,5,2,'***123412341234',100)
insert into bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount)
values(2,2,2,3,'帮测试人员架AS......',0)
delete from bbsReply where Rcontents='***123412341234'
-----------察看创建的bbsReply回帖表是否建成-------------
SELECT * FROM bbsReply
GO
-------------------------第三章---------------------------------------
----使用系统变量,查询数据库系统的情况----
use bbsDB
print 'SQL Serve的版本: '+ @@version
print '服务器的名称: '+@@servername
update bbsUsers set Upassword='1234' where Uname ='可卡因'--密码违反约束
print '执行上条语句产生的错误号是: '+convert(varchar(5),@@error)
GO
------------使用变量和if-else语句,版主察看"可卡因"的情况-------------
set nocount on --不显示T-SQL语句影响的fe行数信息
print ''  --为了显示方便,打印一行空行
print'个人资料如下:'
select 昵称=Uname,登记=Uclass,个人说明=Uremark,积分=Upoint
from bbsUsers where Uname='可卡因'
go
declare @point int --定义变量
DECLARE @userID INT --定义变量　用途与存放用户编号值　
select @userID=UID,@point=Upoint FROM bbsUsers where Uname='可卡因'--变量赋值
-------------------------------------------------------------------------
print'可卡因发帖如下:'
select 发帖时间=convert(varchar(10),Ttime,111), --111表示按年/月/日格式转换
点击率=TclickCount,主题=Ttopic,内容=Tcontents from bbsTopic
print '可卡因回帖如下: '
select 回帖时间=convert(varchar(10),Rtime,111),点击率=RclickCount,
回帖内容=Rcontents from bbsReply where RuID=@userID
IF(@point>30)
PRINT '可卡因的权限:有权发帖'
else
print '可卡因的权限:无权发帖'
go
GO
-------------------ASDF；ASDKJF；LKJAS；DFAS；F；SAKDJF；LKJAS；DLFJ；LKJ----------------
use bbsDB
set nocount on
print'开始提分,请稍候......'
declare @score int, @avg int --定义变量：提分值和平均分
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)
------提分后,更新用户的对应的登记(星级)--------
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
--------------------------------练习部分-----------------------------use bbsDB
set nocount on
declare @index int
select @index=StopicCount from bbsSection where SmasterId=3
if(@index>0)
begin
print '心酸果冻发帖数:'+convert(varchar(4),@index)+',帖子如下'
select 发贴时间=Ttime,点击率=TclickCount,主题=Ttopic,内容=Tcontents from bbsTopic where TuID=3
end
DECLARE @index2 INT
select @index2=TreplyCount from bbsTopic where TuID=3
print'心酸果冻回贴数：'+convert(varchar(4),@index2)
declare @sum int
select @sum=count(Ttopic) from bbsTopic where TuID=3
PRINT'心酸果冻贴数总计：'+convert(varchar(4),@sum)+'贴'+ ' 功臣级别: '+case
     when @sum<10 then '新手上路'
     when @sum between 10 and 20 then '侠客'
     WHEN @sum BETWEEN 21 AND 30 THEN '骑士'
     WHEN @sum between 31 and 40 then '精灵王'
     WHEN @sum between 41 and 50 then '光明使者'
     when @sum >50 then '法老'
     END
GO
--------------------作业部分---------------------
USE bbsDB
SET NOCOUNT ON
declare @index int
PRINT '第一精华贴的信息如下：'
select @index=TreplyCount from bbsTopic where TuID=3
select 发帖时间=convert(varchar(10),Ttime,121),点击率=TclickCount,作者=Uname,主题=Ttopic,内容=Tcontents from bbsTopic,bbsUsers where bbsUsers.UID=bbsTopic.TuID AND bbsTopic.TuID=3
print '回贴数:'+convert(varchar(4),@index)+'，如下所示：'
select 回贴时间=convert(varchar(10),Rtime,121),点击率=RclickCount,
          回复表情=case
           when Rface=1 then '^(oo)^猪头'
           when Rface=2 then '*:o)小丑'
           when Rface=3 then '[:|]机器人'
           when Rface=4 then '{^o~o^}老人家'
           when Rface=5 then '(:<)吹水大王'
           END
,回贴内容=Rcontents from bbsReply where RsID=1
GO
----------------------------------------------------
-----------------------------第四章部分------------------------------------------
use bbsDB
GO
SET 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 '年度回帖人气最ＩＮ的前两名获奖作者：'
Select 大名=Uname,星级=Uclass FROM bbsUsers
where UID IN (SELECT TOP 2 TuID FROM bbsTopic ORDER by TclickCount DESC)
--评选最差版主: 如果存在发帖量为0或低于500的版主,则评选最差的版主
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
------------------------------发贴部分-------------------------------
/*--发主贴
心酸果冻在.NET技术版块
怯怯地问：什么是.NET阿?
微软的.NET
--*/
use bbsDB
set NOCOUNT ON
DECLARE @userID VARCHAR(10),        @Sid int
select @userID=UID FROM bbsUsers where Uname='心酸果冻'--获取心酸果冻的id
select @sID=SID FROM bbsSection where Sname like'%.NET技术%'
--将'心酸果冻'的发帖插入主贴表
print 'DFDF   '+convert(varchar(8),@sID)
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 '什么是.Het啊?')
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
--对外发布"心酸果冻"的发帖(使用系统变量@@IDENTITY查看刚才插入的编号值)
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
-----------------------------------------回贴部分---------------------------------------
USE bbsDB
SET NOCOUNT ON
declare @userID INT,@SID INT,@TID INT, @woID INT
SELECT @userID=tUID,@SID=TsID,@TID=TID FROM bbsTopic where Ttopic like '%什么是.Net啊?%'
SELECT @woID=UID FROM bbsUsers where Uname='可卡因'
print '主贴人id: '+convert(varchar(4),@userID)+'   版块类型id: '+convert(varchar(4),@SID)+'   帖子id :'+convert(varchar(4),@TID)+'    回帖人id： '++convert(varchar(4),@woID)
--发出了回贴
INSERT INTO bbsReply(RtID,RsID,RuID,Rface,Rcontents,RclickCount)
values(@TID,@SID,@woID,1,'.Ner是微软力推的企业。。。',1)
--更新主贴信息
update bbsTopic set TreplyCount=TreplyCount+1,TclickCount=TclickCount+1,TlastReply=getdate() where TID=@TID
--版块更新点击率+1
update bbsSection set SclickCount=SclickCount+1 where SID=@SID
--用户回贴后将用户积分加加，如果是做主贴的第一回复人将加100 否则将加50
update bbsUsers set Upoint=case
     when (select TreplyCount from bbsTopic WHERE TID=@TID)<1 then Upoint+100
     else Upoint+50
    end
where UID=@woID
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
PRINT '主贴内容如下: '
SELECT 贴子编号=TID,标题=Ttopic,内容=Tcontents,发贴时间=Ttime,点击数=TclickCount from bbsTopic where tid=@TID
print '回贴内容如下: '
select 贴子编号=rid,回复表情=case
           when Rface=1 then '^(oo)^猪头'
           when Rface=2 then '*:o)小丑'
           when Rface=3 then '[:|]机器人'
           when Rface=4 then '{^o~o^}老人家'
           when Rface=5 then '(:<)吹水大王'
           END
,回贴内容=Rcontents,回贴时间=Rtime,点击数=RclickCount from bbsReply where RtID=@TID
--print '错误是号： '+convert(varchar(4),@@IDENTITY)
go
---------------第四章上机作业------------
use bbsDB
SET NOCOUNT ON
DECLARE @TID INT,@TsID INT,@TuID INT,@count int,@RuID INT--定义变量
--以下是取出不合法贴子的关联信息--
SELECT @TsID=TsID,@TID=TID,@TuID=TuID,@count=TreplyCount from bbsTopic where Ttopic like '%什么是.Net啊?%'
print '贴子版块id 是:'+convert(varchar(4),@tID)+'   贴子编号是:'+convert(varchar(4),@tID)+' 发贴人id: '+convert(varchar(4),@TuID)+' 点击数：　'+convert(varchar(4),@count)
SELECT @RuID=RuID FROM bbsReply where RtID=@tID--取出回贴人的ｉｄ
PRINT '回贴人ＩＤ：　'+convert(varchar(4),@RuID)
--先删除跟贴--
delete from bbsReply where RtID=@TID
--回贴人几分减去50--
update bbsUsers set Upoint=Upoint-50 where UID=@RuID
--删除主贴--
DELETE from bbsTopic where TID=@TID
--扣掉主贴人的积分100--
update bbsUsers set Upoint=Upoint-100 where UID=@RuID
--贴子版块主贴量-1
update bbsSection set StopicCount=StopicCount-1 where SID=@TsID
--察看问题的严重性如果回贴数量>10，负面影响较大，严重警告--
if (@count>10)
print'你这个...这个..这个问题很严重,现在给予你严重警告一次,如有再犯,将清除本论坛!'
else
print '给予一般警告,情节严重将开除本论坛!'
select 昵称=Uname,星级=case
      when Upoint=0 then ''
      when Upoint<500 then '★'
      when Upoint between 500 and 1000 then '★★'
      when Upoint between 1001 and 2000 then '★★★'
      when Upoint between 2001 and 4000 then '★★★★'
      when Upoint between 4001 and 5000 then '★★★★★'
      else '★★★★★★'
     end
,积分=Upoint from bbsUsers
go

转载于:https://www.cnblogs.com/bq-blog/archive/2010/03/09/1681697.html

a617508321

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
T-SQL从建库建表到添加约束添加数据和查询(注释不是很全)

EXEC sp_configure 'show advanced options',1;--打开某个服务RECONFIGUREEXEC SP_CONFIGURE 'XP_CMDSHELL',1;--设置为‘1’RECONFIGUREGOuse master--检查是否存在bbsDB数据库：查询master数据库中的系统表sysdatabases--IF EXISTS (SELECT ...
复制链接

扫一扫