Sqlserver建立測試資料

標籤：new   int   values   var   rom   com   val   key   ima   

USE Test --使用資料庫Test（如果沒有則需要建立一個）

----1.建立一個users表
create table users(
uId int primary key identity(1,1),
uName varchar(50) null,
uSex bit null default(1),
uPwd varchar(50) null,
uEmail varchar(100) null,
uPwdfindone nvarchar(50) null,
uPwdfindtwo nvarchar(50) null,
uPwdfindthree nvarchar(50) null,
uPwdfindanswer1 nvarchar(50) null,
uPwdfindanswer2 nvarchar(50) null,
uPwdfindanswer3 nvarchar(50) null,
uCity varchar(50) null,
uBirthyear int null,
uBirthmonth int null,
uBirthday int null,
uScore int null,
uRegistertime datetime null default(getdate()),
uRegisterIP varchar(50) null
)
GO

----2.迴圈向users表插入隨機資料
declare @s int --迴圈起點
declare @n int --年
declare @y int --月
declare @r int --日
declare @h int --時
declare @f int --分
declare @m int --秒
declare @mm int --毫秒
declare @t varchar(30) --註冊時間
declare @address varchar(10)--地址
declare @email varchar(20) --郵箱
declare @xb int --性別
declare @xm varchar(20) --姓名
declare @ma varchar(20) --密碼
declare @mq1 varchar(20) --密碼問題1
declare @mq2 varchar(20) --密碼問題2
declare @mq3 varchar(20) --密碼問題3
declare @mqa1 varchar(20) --密碼答案1
declare @mqa2 varchar(20) --密碼答案2
declare @mqa3 varchar(20) --密碼答案3
declare @bn int --出生年份
declare @by int --出生月
declare @br int --出生日
declare @fs int --分數
declare @ip1 int --ip地址第1段
declare @ip2 int --ip地址第2段
declare @ip3 int --ip地址第3段
declare @ip4 int --ip地址第4段
declare @ip varchar(50) --ip地址
set @s=1 --設定迴圈起點
while(@s<1000) --設定迴圈終點
begin
declare @id varchar(10)
set @id=CAST(@s as varchar(10))
begin --註冊時間
set @n=1990+abs(checksum(newid()))%(2014-1990+1)
set @y=1+abs(checksum(newid()))%(12-1+1)
set @r=1+abs(checksum(newid()))%(31-1+1)
if(@y=2)--當前月份是2月
begin
if((@n%4=0 and @n%100 <>0) or @n%400=0)--閏年判斷
set @r=1+abs(checksum(newid()))%(29-1+1)
else
set @r=1+abs(checksum(newid()))%(28-1+1)
end
else
begin
if(@y%4=0 or @y%6=0 or @y%9=0 or @y%11=0)--4、6、9、11月最多30天
set @r=1+abs(checksum(newid()))%(30-1+1)
end
declare @yy varchar(5)
set @yy=CAST(@y as varchar(5))
if(@y<10)
set @yy=‘0‘+CAST(@y as varchar(5))
declare @rr varchar(5)
set @rr=CAST(@r as varchar(5))
if(@r<10)
set @rr=‘0‘+CAST(@r as varchar(5))
set @h=1+abs(checksum(newid()))%(23-1+1)
set @f=1+abs(checksum(newid()))%(59-1+1)
declare @ff varchar(5)--分，用于格式化分鐘
set @ff=CAST(@f as varchar(5))
if(@f<10)
set @ff=‘0‘+CAST(@f as varchar(5))
set @m=1+abs(checksum(newid()))%(59-1+1)
declare @miao varchar(5)--秒，用于格式化秒鐘
set @miao=CAST(@m as varchar(5))
if(@m<10)
set @miao=‘0‘+CAST(@m as varchar(5))
set @mm=1+abs(checksum(newid()))%(999-1+1)
set @t=CAST(@n as varchar(5))+‘-‘[email protected]+‘-‘[email protected]+‘ ‘+CAST(@h as varchar(5))+‘:‘[email protected]+‘:‘[email protected]+‘:‘+CAST(@mm as varchar(5))
declare @time datetime
set @time=convert(datetime,@t)
end
begin --城市及郵箱
if(@s%2=0)
begin
set @xb=0
set @[email protected]+‘@qq.com‘
set @address=‘Guangzhou‘
end
else
begin
set @xb=1
set @[email protected]+‘@msn.com‘
set @address=‘Berlin‘
end
if(@s%11=0)
begin
set @[email protected]+‘@nas.com‘
set @address=‘Strasbourg‘
end
if(@s%22=0)
begin
set @[email protected]+‘@skyweb.com‘
set @address=‘Campinas‘
end
if(@s%33=0)
begin
set @[email protected]+‘@gly.com‘
set @address=‘Xiamen‘
end
if(@s%14=0)
begin
set @[email protected]+‘@bl.com‘
set @address=‘Dalian‘
end
if(@s%15=0)
begin
set @[email protected]+‘@hpjx.com‘
set @address=‘Hangzhou‘
end
if(@s%16=0)
begin
set @[email protected]+‘@rgm.com‘
set @address=‘Wuhan‘
end
if(@s%27=0)
begin
set @[email protected]+‘@tf.com‘
set @address=‘Chengdu‘
end
if(@s%58=0)
begin
set @[email protected]+‘@fj.com‘
set @address=‘Lijiang‘
end
if(@s%39=0)
begin
set @[email protected]+‘@px.com‘
set @address=‘Lyon‘
end
end
begin --姓名、性別、密碼問題、密碼答案等
set @xm=‘uname‘[email protected]
set @ma=‘upwd‘[email protected]
set @mq1=‘upwdfindone‘[email protected]
set @mq2=‘upwdfindtwo‘[email protected]
set @mq3=‘upwdfindthree‘[email protected]
set @mqa1=‘upwd1answer‘[email protected]
set @mqa2=‘upwd2answer‘[email protected]
set @mqa3=‘upwd3answer‘[email protected]
set @bn=1980+abs(checksum(newid()))%(1995-1980+1)
set @by=1+abs(checksum(newid()))%(12-1+1)
set @br=1+abs(checksum(newid()))%(31-1+1)
if(@by=2)--當前月份是2月
begin
if((@bn%4=0 and @bn%100 <>0) or @bn%400=0)--閏年判斷
set @br=1+abs(checksum(newid()))%(29-1+1)
else
set @br=1+abs(checksum(newid()))%(28-1+1)
end
else
begin
if(@by%4=0 or @by%6=0 or @by%9=0 or @by%11=0)--4、6、9、11月最多30天
set @br=1+abs(checksum(newid()))%(30-1+1)
end
set @fs=11+abs(checksum(newid()))%(150-11+1)
end
begin --ip地址
set @ip1=1+abs(checksum(newid()))%(254-1+1)
set @ip2=1+abs(checksum(newid()))%(254-1+1)
set @ip3=1+abs(checksum(newid()))%(254-1+1)
set @ip4=1+abs(checksum(newid()))%(254-1+1)
set @ip=CAST(@ip1 as varchar(5))+‘.‘+CAST(@ip2 as varchar(5))+‘.‘+CAST(@ip3 as varchar(5))+‘.‘+CAST(@ip4 as varchar(5))
end
begin --執行sql語句
insert into users(uName,uSex,uPwd,uEmail,uPwdfindone,uPwdfindtwo,uPwdfindthree,uPwdfindanswer1,uPwdfindanswer2,
uPwdfindanswer3,uCity,uBirthyear,uBirthmonth,uBirthday,uScore,uRegistertime,uRegisterIP)
values(
@xm,@xb,@ma,@email,@mq1,@mq2,@mq3,@mqa1,@mqa2,@mqa3,@address,@bn,@by,@br,@fs,@t,@ip
)
end
set @s+=1
end

----3.檢查資料新增是否執行成功
--select * from users

Sqlserver建立測試資料