SQLSE猎豹CS6VE福睿斯数据库晋级脚本图像和文字步骤

只能远程协助的方式。我特意做了一个脚本,用电话指导客户在SSMS里执行一下脚本就可以了

–删除主键

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

1.0的数据库跟1.1的数据库的区别是1.1的数据库里的其中一个[CT_OuterCard]表比1.0的多了6个字段,其他所有表都一样

alter table 表名 drop constraint 主键名
–添加主键
alter table 表名 add constraint 主键名 primary key(字段名1,字段名2……)
–添加非聚集索引的主键
alter table 表名 add constraint 主键名 primary key
NONCLUSTERED(字段名1,字段名2……)

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

还有存储过程增加了很多,其他都没有改变

新建表: 
create table [表名] 

[自动编号字段] int IDENTITY (1,1) PRIMARY KEY , 
[字段1] nVarChar(50) default \’默认值\’ null , 
[字段2] ntext null , 
[字段3] datetime, 
[字段4] money null , 
[字段5] int default 0, 
[字段6] Decimal (12,4) default 0, 
[字段7] image null , 
)

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

首先,先在公司的服务器数据库上生成存储过程脚本,数据库是1.1版本的,下面的图片里没有说明的,都是默认设置,下一步即可

删除表: 
Drop table [表名]

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 ‘年度回帖人气最IN的前两名获奖作者:’
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)%3C1 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–取出回贴人的id
PRINT ‘回贴人ID: ’+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

当然,如果你的数据库里有自定义函数的话,也可以勾选函数,如果我们的数据库没有函数,所以。。。

DECLARE curItems CURSOR
FOR select [name] from sysobjects where xtype=’U’
FOR READ ONLY
OPEN curItems
DECLARE @n  NVARCHAR(100),@m NVARCHAR(100)
FETCH FROM curItems INTO @n
WHILE @@FETCH_STATUS=0
BEGIN
 set @m=@n
    exec(‘Drop Table ‘ + @m)
FETCH NEXT FROM curItems INTO 
@n
END
CLOSE  curItems
DEALLOCATE  curItems

保存到新建查询窗口

插入数据: 
INSERT INTO [表名] (字段1,字段2) VALUES (100,\’51WINDOWS.NET\’)

这一步做完了,然后编写下面的SQL脚本

删除数据: 
DELETE FROM [表名] WHERE [字段名]>100

复制代码
代码如下:–升级GPOS1.0到GPOS1.1数据库的升级脚本 2013-7-4USE
[GPOSDB]GO——————删除所有存储过程———————select
* from sys.procedures

更新数据: 
UPDATE [表名] SET [字段1] = 200,[字段2] = \’51WINDOWS.NET\’
WHERE [字段三] = \’HAIWA\’

declare @sql varchar(4000)set @sql=”select @sql=@sql+’drop proc
‘+name+’; ‘ from sys.procedures–print @sqlexec(@sql)

新增字段: 
ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL

——————————–在[CT_OuterCard]表添加6个字段——————————-ALTER
TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransCurrCount] INT NOT
NULL CONSTRAINT [DF_CT_OuterCard_I_LimitTransCurrCount] DEFAULT
((0))ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransType]
[int] CONSTRAINT [DF_CT_OuterCard_I_LimitTransType] DEFAULT
((0))ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransTotal]
[decimal](18, 2) NOT NULL CONSTRAINT
[DF_CT_OuterCard_DE_LimitTransTotal] DEFAULT ((0))ALTER TABLE
[dbo].[CT_OuterCard] ADD [DE_LimitTransCurrTotal]
[decimal](18, 2) NOT NULL CONSTRAINT
[DF_CT_OuterCard_DE_LimitTransCurrTotal] DEFAULT ((0))ALTER TABLE
[dbo].[CT_OuterCard] ADD [I_LimitCarNo] [int] NOT NULL
CONSTRAINT [DF_CT_OuterCard_I_LimitCarNo] DEFAULT ((0))ALTER TABLE
[dbo].[CT_OuterCard] ADD [D_LimitDate] [datetime] NOT NULL
CONSTRAINT [DF_CT_OuterCard_D_LimitDate] DEFAULT
(getdate())—————————————————————————————————————-把刚才在新建查询窗口里生成的存储过程脚本粘贴到下面—————————创建GPOS1.1的所有存储过程———————————————USE
[GPOSDB]GO/****** 对象: StoredProcedure
[dbo].[Report_GreaserSaleStat] 脚本日期: 07/04/2013 13:27:09
******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO

删除字段: 
ALTER TABLE [表名] DROP COLUMN [字段名]