公司维护网站时常用的sql语句.net

--察看最近注册的企业会员信息  
select ecard.companyname,vip.* from vip
inner join enterprisecard ecard on ecard.vipid=vip.vipid where vip.vipid in(
select vipid from enterprisecard )
order by vip.regdate desc
--删除标题重复的纪录
delete bizinformation where bizinfoid not in(
select max(bizinfoid) from bizinformation group by titleinfo)
--删除内容重复的纪录
delete bizinformation where bizinfoid not in(
select max(bizinfoid) from bizinformation group by contentinfo)

--查看非法信息
select * from bizinformation where bizinfoid in(
select bizinfoid from bizinformation where titleinfo like '%转%车%')
--删除非法信息
delete from bizinformation where bizinfoid in(
select bizinfoid from bizinformation where titleinfo like '%转%车%')
-------
delete from bizinformation where bizinfoid in(
select bizinfoid from bizinformation where titleinfo like '%售%车%')
--****************************
select * from bizinformation where titleinfo like '%万转让%'
%代办%
%枪%
%丰胸%
%美容%仪器%
%供应%减肥%
%售%车%
%★%
%贷%
%办理%抵%押%
%美容%
%减肥%
--批量新增加银卡账号
declare @i int
set @i=314
while @i<=400
begin
 declare @x nvarchar(50)
 set @x='114su'
 set @x=@x+convert(nvarchar(50),@i)
 insert into vip (vipName,gender,pwd,isCompany,vipTypeId,regdate,validdate,templetNum)
values(@x,'男','e10adc3949ba59abbe56e057f20f883e',1,3,getdate(),dateadd(year,10,getdate()),1)
declare @vid int
set @vid=(select vipId from Vip where vipName =@x)

insert into EnterpriseCard
(
 vipId,
 categoryId,
 companyName,
 areaId,
 serviceRange
)
values
(@vid,26,'公司名称',13,'服务范围')
 set @i=@i+1
end

--针对旧帐号开通使用
1.检查该号，是否已经开通，如果查询结果为空，则可以开通。
select * from EnterpriseCard where vipid in(
select vipid from Vip where vipname='666371'
)
2.查看制定的会员的编号
select vipid from vip where vipname='666371'
3.开通：(替换编号)
insert into EnterpriseCard
(
 vipId,
 categoryId,
 companyName,
 areaId,
 serviceRange
)
values
(8982,26,'公司名称',13,'服务范围')
4.已开通

填写完成后激活即可在企业首页显示
//激活指定的会员
1.找到'666371',对应的编号
select vipid from vip where vipname='666371'
2.激活，
update vip set isActivation=1,regDate=getdate(),validdate=dateadd(year,2,getdate()) where vipid=8948

//修改指定的会员广告
update ad set adTitle='太原市洁馨家政服务中心',
     adContent='家庭保洁、清扫房间、擦玻璃、新居开荒、打蜡保养、清洗地毯、清洗油烟、刮家粉家、油漆、电工、清洗门头牌匾、月嫂陪护、陪读家教',
     phone='0351-3044768/7921922/8523659',
     nvaUrl='http://tyjxjz.sx114cn.cn/'
where autoId=21

 

 

//******************
select * from EnterpriseVipType  [查看会员类型]
//*************************
Delete Vip User
select * from enterprisecard where companyname like '%欣明%'
select * from vip where vipId=8501
select * from enterprisecard where vipid=8501
select * from BizInformation where promulgatorid=8510 --查找该会员发布的商讯
select * from dbo.CompanyNews where vipid=8510 ----查找该会员发布的企业动态
delete from vip where vipid=8501
delete from enterprisecard where cardid=3425

--查询最近1个月内注册的普通会员
select ec.companyName as '公司名称',ec.linkman as '联系人',ec.phone as '公司电话',ec.address as '详细地址',vip.realName as '注册名称',vip.gender as '性别',vip.phone as '注册电话',vip.mobilePhone as '移动电话' from vip
left join enterprisecard ec on ec.vipId=vip.vipId
where regdate>=dateadd(month,-1,getdate()) and vipTypeId=1
========================================================
查会员详细信息：
select * from EnterpriseCard where vipid='16563'
http://www.sx114cn.cn/Vip/moban01/vipVideo/bohanshengwu.flv

===================================
福布斯SQL注入清理：
update 表名 set 字段名=replace(convert(varchar(8000),字段名),'<script src=http://fsdfdssfsdfsdf></script>',' ')

 

 

 

批量删除

declare @delStr nvarchar(500)
set @delStr='<script src=http://3b3.org/c.js></script>' --替换关键字

 

set nocount on

declare @tableName nvarchar(100),@columnName nvarchar(100),@tbID int,@iRow int,@iResult int
declare @sql nvarchar(500)

set @iResult=0
declare cur cursor for
select name,id from sysobjects where xtype='U'

open cur
fetch next from cur into @tableName,@tbID

while @@fetch_status=0
begin
declare cur1 cursor for
        --xtype in (231,167,239,175) 为char,varchar,nchar,nvarchar类型
        select name from syscolumns where xtype in (231,167,239,175) and id=@tbID
open cur1
fetch next from cur1 into @columnName
while @@fetch_status=0
begin
      set @sql='update [' + @tableName + '] set ['+ @columnName +']= replace(convert(varchar(1000),['+@columnName+']),'''+@delStr+''','''') where ['+@columnName+'] like ''%'+@delStr+'%'''    
      exec sp_executesql @sql    
      set @iRow=@@rowcount
      set @iResult=@iResult+@iRow
      if @iRow>0
      begin
     print '表：'+@tableName+',列:'+@columnName+'被更新'+convert(varchar(10),@iRow)+'条记录;'
      end    
      fetch next from cur1 into @columnName

end
close cur1
deallocate cur1

declare cur2 cursor for
        --xtype in (99,35) 为ntext,text类型
        select name from syscolumns where xtype in (99,35) and id=@tbID
open cur2
fetch next from cur2 into @columnName
while @@fetch_status=0
begin
      set @sql='update [' + @tableName + '] set ['+ @columnName +']= replace(convert(nvarchar(1000),['+@columnName+']),'''+@delStr+''','''') where ['+@columnName+'] like ''%'+@delStr+'%'''    
      exec sp_executesql @sql    
      set @iRow=@@rowcount
      set @iResult=@iResult+@iRow
      if @iRow>0
      begin
     print '表：'+@tableName+',列:'+@columnName+'被更新'+convert(varchar(10),@iRow)+'条记录;'
      end    
      fetch next from cur2 into @columnName

end
close cur2
deallocate cur2

fetch next from cur into @tableName,@tbID
end
print '数据库共有'+convert(varchar(10),@iResult)+'条记录被更新!!!'

close cur
deallocate cur
set nocount off
============================
body{
filter:Gray;
}