--察看最近注册的企业会员信息
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;
}