- --百家姓资料库
- create table Table_Name ( id int,name nchar(1),groupid int)
- insert Table_Name values(1,'邹',1)
- insert Table_Name values(2,'钱',1)
- insert Table_Name values(3,'牛',1)
- insert Table_Name values(4,'何',1)
- insert Table_Name values(5,'夏',1)
- insert Table_Name values(6,'左',1)
- insert Table_Name values(7,'沙',1)
- insert Table_Name values(8,'龚',1)
- insert Table_Name values(9,'严',1)
- insert Table_Name values(0,'洪',1)
- insert Table_Name values(10,'尹',1)
- insert Table_Name values(11,'王',1)
- insert Table_Name values(12,'李',1)
- insert Table_Name values(13,'张',1)
- insert Table_Name values(14,'刘',1)
- insert Table_Name values(15,'陈',1)
- insert Table_Name values(16,'杨',1)
- insert Table_Name values(17,'黄',1)
- insert Table_Name values(18,'赵',1)
- insert Table_Name values(19,'周',1)
- insert Table_Name values(20,'吴',1)
- insert Table_Name values(21,'徐',1)
- insert Table_Name values(22,'孙',1)
- insert Table_Name values(23,'马',1)
- insert Table_Name values(24,'胡',1)
- insert Table_Name values(25,'郭',1)
- insert Table_Name values(26,'林',1)
- insert Table_Name values(27,'高',1)
- insert Table_Name values(28,'梁',1)
- insert Table_Name values(29,'郑',1)
- insert Table_Name values(30,'罗',1)
- insert Table_Name values(31,'宋',1)
- insert Table_Name values(32,'谢',1)
- insert Table_Name values(33,'唐',1)
- insert Table_Name values(34,'韩',1)
- insert Table_Name values(35,'曹',1)
- insert Table_Name values(36,'许',1)
- insert Table_Name values(37,'邓',1)
- insert Table_Name values(38,'萧',1)
- insert Table_Name values(39,'冯',1)
- insert Table_Name values(40,'曾',1)
- insert Table_Name values(41,'程',1)
- insert Table_Name values(42,'蔡',1)
- insert Table_Name values(43,'彭',1)
- insert Table_Name values(44,'潘',1)
- insert Table_Name values(45,'袁',1)
- insert Table_Name values(46,'于',1)
- insert Table_Name values(47,'董',1)
- insert Table_Name values(48,'余',1)
- insert Table_Name values(49,'苏',1)
- insert Table_Name values(50,'叶',1)
- insert Table_Name values(51,'吕',1)
- insert Table_Name values(52,'魏',1)
- insert Table_Name values(53,'蒋',1)
- insert Table_Name values(54,'田',1)
- insert Table_Name values(55,'杜',1)
- insert Table_Name values(56,'丁',1)
- insert Table_Name values(57,'沈',1)
- insert Table_Name values(58,'姜',1)
- insert Table_Name values(59,'范',1)
- insert Table_Name values(60,'江',1)
- insert Table_Name values(61,'傅',1)
- insert Table_Name values(62,'钟',1)
- insert Table_Name values(63,'卢',1)
- insert Table_Name values(64,'汪',1)
- insert Table_Name values(65,'戴',1)
- insert Table_Name values(66,'崔',1)
- insert Table_Name values(67,'任',1)
- insert Table_Name values(68,'陆',1)
- insert Table_Name values(69,'廖',1)
- insert Table_Name values(70,'姚',1)
- insert Table_Name values(71,'方',1)
- insert Table_Name values(72,'金',1)
- insert Table_Name values(73,'邱',1)
- insert Table_Name values(74,'贺',1)
- insert Table_Name values(75,'谭',1)
- insert Table_Name values(76,'韦',1)
- insert Table_Name values(77,'贾',1)
- insert Table_Name values(78,'石',1)
- insert Table_Name values(79,'熊',1)
- insert Table_Name values(80,'孟',1)
- insert Table_Name values(81,'秦',1)
- insert Table_Name values(82,'阎',1)
- insert Table_Name values(83,'薛',1)
- insert Table_Name values(84,'侯',1)
- insert Table_Name values(85,'雷',1)
- insert Table_Name values(86,'白',1)
- insert Table_Name values(87,'龙',1)
- insert Table_Name values(88,'段',1)
- insert Table_Name values(89,'郝',1)
- insert Table_Name values(90,'孔',1)
- insert Table_Name values(91,'邵',1)
- insert Table_Name values(92,'史',1)
- insert Table_Name values(93,'毛',1)
- insert Table_Name values(94,'常',1)
- insert Table_Name values(95,'万',1)
- insert Table_Name values(96,'顾',1)
- insert Table_Name values(97,'赖',1)
- insert Table_Name values(98,'武',1)
- insert Table_Name values(99,'康',1)
- insert Table_Name values(100,'普',1)
- insert Table_Name values(1,'晓',2)
- insert Table_Name values(2,'思',2)
- insert Table_Name values(3,'语',2)
- insert Table_Name values(4,'宝',2)
- insert Table_Name values(5,'芷',2)
- insert Table_Name values(6,'正',2)
- insert Table_Name values(7,'筱',2)
- insert Table_Name values(8,'宇',2)
- insert Table_Name values(9,'晗',2)
- insert Table_Name values(10,'尚',2)
- insert Table_Name values(11,'佳',2)
- insert Table_Name values(12,'萧',2)
- insert Table_Name values(1,'韵',3)
- insert Table_Name values(2,'涵',3)
- insert Table_Name values(3,'佳',3)
- insert Table_Name values(4,'彤',3)
- insert Table_Name values(5,'晴',3)
- insert Table_Name values(6,'晓',3)
- insert Table_Name values(7,'悦',3)
- insert Table_Name values(8,'香',3)
- insert Table_Name values(9,'贤',3)
- insert Table_Name values(10,'春',3)
- insert Table_Name values(11,'甜',3)
- insert Table_Name values(12,'若',3)
- insert Table_Name values(13,'静',3)
- insert Table_Name values(14,'琪',3)
- insert Table_Name values(15,'洁',3)
- insert Table_Name values(16,'羽',3)
- insert Table_Name values(17,'琳',3)
- insert Table_Name values(18,'菲',3)
- insert Table_Name values(19,'莎',3)
- insert Table_Name values(20,'顺',3)
- insert Table_Name values(21,'穗',3)
- insert Table_Name values(22,'颖',3)
- insert Table_Name values(23,'依',3)
- insert Table_Name values(24,'萌',3)
- insert Table_Name values(25,'颜',3)
- insert Table_Name values(26,'畅',3)
- insert Table_Name values(27,'彩',3)
- insert Table_Name values(28,'恩',3)
- insert Table_Name values(29,'萱',3)
- insert Table_Name values(30,'朵',3)
- insert Table_Name values(31,'雅',3)
- go
- --拼音函数
- create function f_GetPy(@str nvarchar(4000))
- returns nvarchar(4000)
- as
- begin
- declare @strlen int,@re nvarchar(4000)
- declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
- insert into @t(chr,letter)
- select '吖','A' union all select '八','B' union all
- select '嚓','C' union all select '咑','D' union all
- select '妸','E' union all select '发','F' union all
- select '旮','G' union all select '铪','H' union all
- select '丌','J' union all select '咔','K' union all
- select '垃','L' union all select '嘸','M' union all
- select '拏','N' union all select '噢','O' union all
- select '妑','P' union all select '七','Q' union all
- select '呥','R' union all select '仨','S' union all
- select '他','T' union all select '屲','W' union all
- select '夕','X' union all select '丫','Y' union all
- select '帀','Z'
- select @strlen=len(@str),@re=''
- while @strlen>0
- begin
- select top 1 @re=letter+@re,@strlen=@strlen-1
- from @t a where chr<=substring(@str,@strlen,1)
- order by chr desc
- if @@rowcount=0
- select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
- end
- return(@re)
- end
- go
- --表结构
- CREATE TABLE #t (id int IDENTITY(1,1) PRIMARY KEY,
- 姓名 NVARCHAR(20),邮件地址 NVARCHAR(20),登录次数 INT,注册时间 DATETIME,注册IP Nvarchar(20))
- GO
- DECLARE @I INT
- SET @I=1
- WHILE @I<=15
- BEGIN
- DECLARE @USERNAME NVARCHAR(20)
- SELECT @USERNAME=(select top 1 name from table_name where groupid=1
- order by newid())+(select top 1 name from table_name where groupid=2
- order by newid())+ (select top 1 name from table_name where groupid=3
- order by newid())
- INSERT INTO #t
- SELECT @USERNAME,
- LOWER(DBO.f_GetPy(@USERNAME))+LTRIM(abs(CHECKSUM(NEWID())%10000))+'@'+left(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(newid()),'-',''),'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),4)+'.com',
- abs(CHECKSUM(NEWID())%100),
- cast(dateadd(d,cast(rand()*3650 as int)*-1,getdate()) as smalldatetime),
- ltrim(cast(rand()*255+1 as int))+'.'+ltrim(cast(rand()*255+1 as int))+'.'+ltrim(cast(rand()*255+1 as int))+'.'+ltrim(cast(rand()*255+1 as int))
- SET @I=@I+1
- END
- --数据查看
- SELECT * FROM #t
- /*
- id 姓名 邮件地址 登录次数 注册时间 注册IP
- ----------- -------------------- -------------------- ----------- ----------------------- --------------------
- 1 袁晗朵 yhd8306@dcba.com 35 2007-07-18 17:31:00.000 31.127.248.68
- 2 武晗静 whj6025@dbca.com 6 2008-03-08 17:31:00.000 47.161.14.161
- 3 吴正穗 wzs789@fcbc.com 70 2004-05-05 17:31:00.000 136.72.124.16
- 4 刘宇贤 lyx8289@acdf.com 49 1999-06-15 17:31:00.000 30.217.62.72
- 5 左宝朵 zbd9535@aeac.com 48 2005-06-12 17:31:00.000 188.135.37.23
- 6 贾思若 jsr4194@ebdd.com 73 2003-12-20 17:31:00.000 249.103.98.103
- 7 吕宝颜 lby7149@abed.com 54 2003-07-29 17:31:00.000 192.50.128.159
- 8 任晗畅 rhc8396@cada.com 82 2006-05-24 17:31:00.000 170.138.222.36
- 9 武晗依 why8719@eaff.com 2 1999-02-14 17:31:00.000 86.223.34.211
- 10 萧晓琳 xxl3078@dffd.com 67 2004-01-31 17:31:00.000 227.219.230.235
- 11 武宝羽 wby9862@fbce.com 11 2005-08-22 17:31:00.000 102.67.36.242
- 12 谢正若 xzr8506@ccee.com 78 2003-02-15 17:31:00.000 167.254.13.201
- 13 王宇佳 wyj2036@cadf.com 24 2004-12-09 17:31:00.000 182.199.125.57
- 14 杜尚穗 dss9639@eefb.com 29 1999-03-04 17:31:00.000 161.57.234.111
- 15 汪宝琳 wbl4149@cfea.com 92 2003-05-26 17:31:00.000 89.7.183.95
- (15 行受影响)
- */
- --删除环境
- DROP TABLE #t
- GO
- DROP FUNCTION f_GetPy
- GO
- DROP TABLE Table_Name
- GO
生成注册信息资料库
最新推荐文章于 2023-09-04 20:05:39 发布