如何在SQL Server中生成随机数据 [英] How to generate random data in SQL server
本文介绍了如何在SQL Server中生成随机数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想创建一个存储过程来在视频表中插入随机数据。我已经为UserProfile表生成了30,000条记录数据。
注意:用户名是视频表格中的FK元素。
CREATE TABLE UserProfile
(
用户名VARCHAR(45)NOT NULL,
UserPassword VARCHAR(45)NOT NULL,
电子邮件VARCHAR(45)NOT NULL,
FName VARCHAR (45)NOT NULL,
LNAME VARCHAR(45)NOT NULL,
生日DATE,
Genger VARCHAR(10)NOT NULL,
ZipCode INT,
图像VARCHAR (50),
主键(用户名)
);
GO
CREATE TABLE视频
(
VideoId INT NOT NULL默认值1000,
用户名VARCHAR(45)NOT NULL,
VideoName VARCHAR (160)NOT NULL,
UploadTime DATE,
TotalViews INT,
缩略图VARCHAR(100),
PRIMARY KEY(VideoId),
FOREIGN KEY (用户名)
参考UserProfile(用户名)
);
GO
解决方案
随机数据,即使在SQL
例如,从您的userprofile表中获取随机用户名。
BEGIN
- 从表中获取随机行
DECLARE @username VARCHAR(50)
SELECT @username = [用户名] FROM(
SELECT ROW_NUMBER()OVER(ORDER BY [Username])[row],[Username]
FROM [UserProfile]
)t
WHERE t.row = 1 +(SELECT CAST )* COUNT(*)as INT)FROM [UserProfile])
print(@username)
END
生成随机整数...
BEGIN
- 获取3到7之间的随机整数(3 + 5 - 1)
DECLARE @totalviews INT
SELECT @totalviews = CAST(RAND()* 5 + 3 as INT)
print totalviews)
END
生成随机varchar字符串
BEGIN
- 获取随机varchar ascii字符32到128
DECLARE @videoname VARCHAR(160)
DECLARE @length INT
SELECT @videoname =''
SET @length = CAST(RAND()* 160 as INT)
WHILE @length<> 0
BEGIN
SELECT @videoname = @videoname + CHAR(CAST(RAND()* 96 + 32 as INT))
SET @length = @length - 1
END
print(@videoname)
END
最后, p>
BEGIN
- 获取随机日期时间+/- 365天
DECLARE @uploadtime DATETIME
SET @uploadtime = GETDATE()+(365 * 2 * RAND() - 365)
print(@uploadtime)
END
I want to create a stored procedure to insert random data in 'Video' table. I have already generated 30,000 record data for UserProfile table.
Note: The username is FK element in Video table.
CREATE TABLE UserProfile
(
Username VARCHAR(45) NOT NULL ,
UserPassword VARCHAR(45) NOT NULL ,
Email VARCHAR(45) NOT NULL ,
FName VARCHAR(45) NOT NULL ,
LName VARCHAR(45) NOT NULL ,
Birthdate DATE ,
Genger VARCHAR(10) NOT NULL ,
ZipCode INT ,
Image VARCHAR(50) ,
PRIMARY KEY(Username)
);
GO
CREATE TABLE Video
(
VideoId INT NOT NULL DEFAULT 1000 ,
Username VARCHAR(45) NOT NULL ,
VideoName VARCHAR(160) NOT NULL ,
UploadTime DATE ,
TotalViews INT ,
Thumbnail VARCHAR(100) ,
PRIMARY KEY(VideoId),
FOREIGN KEY(Username)
REFERENCES UserProfile(Username)
);
GO
解决方案
It's not too difficult to generate random data, even in SQL
For example, to get a random username from your userprofile table.
BEGIN
-- get a random row from a table
DECLARE @username VARCHAR(50)
SELECT @username = [Username] FROM (
SELECT ROW_NUMBER() OVER(ORDER BY [Username]) [row], [Username]
FROM [UserProfile]
) t
WHERE t.row = 1 + (SELECT CAST(RAND() * COUNT(*) as INT) FROM [UserProfile])
print(@username)
END
To generate a random integer...
BEGIN
-- get a random integer between 3 and 7 (3 + 5 - 1)
DECLARE @totalviews INT
SELECT @totalviews = CAST(RAND() * 5 + 3 as INT)
print(@totalviews)
END
To generate a random varchar string
BEGIN
-- get a random varchar ascii char 32 to 128
DECLARE @videoname VARCHAR(160)
DECLARE @length INT
SELECT @videoname = ''
SET @length = CAST(RAND() * 160 as INT)
WHILE @length <> 0
BEGIN
SELECT @videoname = @videoname + CHAR(CAST(RAND() * 96 + 32 as INT))
SET @length = @length - 1
END
print(@videoname)
END
And finally, a random date
BEGIN
-- get a random datetime +/- 365 days
DECLARE @uploadtime DATETIME
SET @uploadtime = GETDATE() + (365 * 2 * RAND() - 365)
print(@uploadtime)
END
这篇关于如何在SQL Server中生成随机数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文