declare @a table(id int,ziduan1 varchar(50),ziduan2 varchar(50)) insert @a(id,ziduan1,ziduan2) values(1,'abc','yuxi') insert @a(id,ziduan1,ziduan2) values(2,'[email protected]','haha') insert @a(id,ziduan1,ziduan2) values(3,'jsuehgja','haha') insert @a(id,ziduan1,ziduan2) values(4,'school@pieadf','haha') insert @a(id,ziduan1,ziduan2) values(5,'school@fufufuf','haha') select * from @aselect id from @a where ziduan1 like 'school@%' update @a set ziduan2=convert(varchar(50),rand()*10) where id in (select id from @a where ziduan1 like 'school@%')select * from @a
--建立测试环境 Create Table 表(id varchar(10),ziduan1 varchar(30),ziduan2 varchar(10)) --插入数据 insert into 表 select '1','abc','yuxi' union select '2','[email protected]','haha' union select '3','jsuehgja','haha' union select '4','school@pieadf','haha' union select '5','school@fufufuf','haha' go create view randchar as select rand() as randchar --测试语句 gocreate function GenStr() returns varchar(8) begin declare @f varchar(8) declare @i integer declare @l integer set @f='' set @i=0 select @l=round(randchar*4,0)+4 from randchar
while @i<=@l begin select @f=@f+ char(round(randchar*60,0)+64) from randchar set @i=@i+1 end
return(@f) end go update 表 set ziduan2=dbo.GenStr() where ziduan1 like 'school@%' select * from 表
--删除测试环境 Drop Table 表 Drop function GenStr drop view randchar
declare @biao table(ziduan1 nvarchar(30),ziduan2 int) insert into @biao select 'school@111',0 union all select 'test@111',0 union all select 'school@112',0select * from @biao update @biao set ziduan2=cast(ceiling(rand(checksum(newid()))*5)+3 as int) where ziduan1 like 'school@%'select * from @biao
declare @a table(id int,ziduan1 varchar(50),ziduan2 varchar(50))
insert @a(id,ziduan1,ziduan2)
values(1,'abc','yuxi')
insert @a(id,ziduan1,ziduan2)
values(2,'[email protected]','haha')
insert @a(id,ziduan1,ziduan2)
values(3,'jsuehgja','haha')
insert @a(id,ziduan1,ziduan2)
values(4,'school@pieadf','haha')
insert @a(id,ziduan1,ziduan2)
values(5,'school@fufufuf','haha')
select * from @aselect id from @a where ziduan1 like 'school@%'
update @a set ziduan2=convert(varchar(50),rand()*10) where id in
(select id from @a where ziduan1 like 'school@%')select * from @a
Create Table 表(id varchar(10),ziduan1 varchar(30),ziduan2 varchar(10))
--插入数据
insert into 表
select '1','abc','yuxi' union
select '2','[email protected]','haha' union
select '3','jsuehgja','haha' union
select '4','school@pieadf','haha' union
select '5','school@fufufuf','haha'
go
create view randchar
as
select rand() as randchar
--测试语句
gocreate function GenStr()
returns varchar(8)
begin
declare @f varchar(8)
declare @i integer
declare @l integer
set @f=''
set @i=0
select @l=round(randchar*4,0)+4 from randchar
while @i<=@l
begin
select @f=@f+ char(round(randchar*60,0)+64) from randchar
set @i=@i+1
end
return(@f)
end
go update 表 set ziduan2=dbo.GenStr()
where ziduan1 like 'school@%'
select * from 表
--删除测试环境
Drop Table 表
Drop function GenStr
drop view randchar
insert into @biao
select 'school@111',0 union all
select 'test@111',0 union all select 'school@112',0select * from @biao update @biao
set ziduan2=cast(ceiling(rand(checksum(newid()))*5)+3 as int)
where ziduan1 like 'school@%'select * from @biao