创建存储过程生成模拟数据1——搭建数据分析平台

使用MySQL数据库

1.建表

CREATE TABLE `airinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomId` int(11) NOT NULL,
  `date` date DEFAULT NULL,
  `temperature` int(11) DEFAULT NULL,
  `humidity` int(11) DEFAULT NULL,
  `PM2.5` int(11) DEFAULT NULL,
  `CO2` int(11) DEFAULT NULL,
  `SO2` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.创建存储过程生成ID

DELIMITER ;; 
CREATE PROCEDURE insert1()
BEGIN 
DECLARE a INT DEFAULT 1; 
WHILE (a <= 1800) DO 
    -- repeat 
    insert into airinfo(id) values(a); 
    SET a = a + 1; 
END WHILE; 
commit; 
END;; 

CALL insert1();

3.创建存储过程生成roomID

DELIMITER ;; 
CREATE PROCEDURE insert2()

BEGIN 
DECLARE a INT DEFAULT 1; 
WHILE (a <= 300) DO 
	update airdata.airinfo set roomID = 101 where id = a ;
	SET a = a + 1; 
END WHILE; 
WHILE (a <= 600) DO 
	update airdata.airinfo set roomID = 102 where id = a ;
	SET a = a + 1; 
END WHILE;
WHILE (a <= 900) DO 
	update airdata.airinfo set roomID = 201 where id = a ;
	SET a = a + 1; 
END WHILE;
WHILE (a <= 1200) DO 
	update airdata.airinfo set roomID = 202 where id = a ;
	SET a = a + 1; 
END WHILE;
WHILE (a <= 1500) DO 
	update airdata.airinfo set roomID = 301 where id = a ;
	SET a = a + 1; 
END WHILE;
WHILE (a <= 1800) DO 
	update airdata.airinfo set roomID = 302 where id = a ;
	SET a = a + 1; 
END WHILE;
END;; 
CALL insert2();

4.创建存储过程生成date

DELIMITER ;; 
create PROCEDURE insert3()  
BEGIN  
DECLARE a INT DEFAULT 1;   
DECLARE nowdate date DEFAULT '20170101';  


set nowdate = DATE_FORMAT('20170101','%Y%m%d');  
WHILE a < 301  
DO  
update airdata.airinfo set date = nowdate where id = a ;
SET a = a + 1;   
set nowdate = DATE_ADD(nowdate,INTERVAL 1 DAY);  
END WHILE; 


set nowdate = DATE_FORMAT('20170101','%Y%m%d');  
WHILE a < 601   
DO  
update airdata.airinfo set date = nowdate where id = a ;
SET a = a + 1;   
set nowdate = DATE_ADD(nowdate,INTERVAL 1 DAY);  
END WHILE; 


set nowdate = DATE_FORMAT('20170101','%Y%m%d');  
WHILE a < 901   
DO  
update airdata.airinfo set date = nowdate where id = a ;
SET a = a + 1;   
set nowdate = DATE_ADD(nowdate,INTERVAL 1 DAY);  
END WHILE;  


set nowdate = DATE_FORMAT('20170101','%Y%m%d');  
WHILE a < 1201   
DO  
update airdata.airinfo set date = nowdate where id = a ;
SET a = a + 1;   
set nowdate = DATE_ADD(nowdate,INTERVAL 1 DAY);  
END WHILE;  


set nowdate = DATE_FORMAT('20170101','%Y%m%d');  
WHILE a < 1501   
DO  
update airdata.airinfo set date = nowdate where id = a ;
SET a = a + 1;   
set nowdate = DATE_ADD(nowdate,INTERVAL 1 DAY);  
END WHILE;  


set nowdate = DATE_FORMAT('20170101','%Y%m%d');  
WHILE a < 1801   
DO  
update airdata.airinfo set date = nowdate where id = a ;
SET a = a + 1;   
set nowdate = DATE_ADD(nowdate,INTERVAL 1 DAY);  
END WHILE;  
  
END;;

CALL insert3();