数据库语言记录

一、查询一百张表的日志记录。用存储过程和游标实现

DECLARE @TableName varchar(32)
DECLARE @sql varchar(8000)
--set @sql='select * from user_table1'
DECLARE mycursor CURSOR FOR --定义游标
SELECT distinct(Table_Name) FROM  frank_test.INFORMATION_SCHEMA.Columns  --where Table_Name<>'user_table1'
OPEN mycursor; --打开游标。
FETCH next FROM mycursor INTO @TableName --去游标进行操作
WHILE @@FETCH_STATUS = 0
BEGIN
exec('SELECT * FROM ' +@TableName) -- 请注意 加号前后的 单引号的边上加空格
--exec('select * from user_table1 union all select * from ' +@tablename)
--set @sql=@sql+ ' union all select * from ' +@tablename
FETCH next FROM mycursor INTO @TableName
end
--exec(@sql)
CLOSE mycursor; --关闭游标
Deallocate mycursor--释放游标

二、SQL2005语言，搜索出的结果前加上序列好。

select ROW_NUMBER() over ( order by user_uname desc) as xunhao,*
from user_table1

 

三、循环向表中插入数据

declare @i int
set @i=1
while @i<1000000
begin
insert into info values('Justin'+str(@i),'深圳'+str(@i))
set @i=@i+1
end

DROP TABLE IF EXISTS `ratingvod`;
CREATE TABLE `ratingvod` (
  `cid` varchar(16) DEFAULT NULL,
  `uid` varchar(16) DEFAULT NULL,
  `rating` varchar(16) DEFAULT NULL,
  `feedback_time` varchar(16) DEFAULT NULL,
  `portal_id` varchar(255) DEFAULT NULL,
  `portal_type` varchar(16) DEFAULT NULL,
  `terminal_type` varchar(30) DEFAULT NULL,
  `terminal_os` varchar(30) DEFAULT NULL,
  `location` varchar(30) DEFAULT NULL,
  `token` varchar(30) DEFAULT NULL,
  `channel` varchar(30) DEFAULT NULL,
  `schedule_code` varchar(30) DEFAULT NULL,
  `adv_code` varchar(30) DEFAULT NULL
  -- PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- datetime int(12)

DELETE from ratingvod;

DROP PROCEDURE IF EXISTS dowhile;
create procedure dowhile()
 begin
  declare i int default 1;
declare j int default 1;
  start transaction;
  while i<101 DO
   while j<101 DO
    insert into ratingvod (cid,uid,rating,feedback_time,portal_id,portal_type,terminal_type,terminal_os,location,token,channel,schedule_code,adv_code)
   values(concat('vod',i),concat('User',i),i%5+1,'20120719172048','Portal_id1','Portal_type1','STB','Android','location1','unknow:unknow:unknow','CCTV1','scode0','acode0');
     SET j=j+1;  
END WHILE;
       SET i=i+1;
       SET j=1;
END WHILE;
  commit;
 end;

delimiter;

CALL dowhile();

-- SELECT count(cid) FROM ratingvod;
SELECT * FROM ratingvod;

 insert into tablefortest(a,b)values(1,2) ,(3,4) ,(5,6)

   若用户组织架构中，要先建组织，再每个组织添加等量用户？ 思考中……