참고내용

ERDCloud

테이블 명세서

테이블 CREATE 및 INSERT문

• db에서 이전 6개월간 iot기록 때려넣어줌

DELIMITER $$

CREATE PROCEDURE test()
BEGIN
    DECLARE v_date DATE;
    DECLARE i INT;
    SET v_date = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

    WHILE v_date <= CURDATE() DO
        SET i = 1;
        WHILE i <= 10 DO 
            INSERT INTO iothistory (iot_id, iot_name, value_category, iot_value, iot_status, log_date)
            VALUES (
                'IOT1', 
                NULL, 
                NULL,
                ROUND(RAND() * 100, 2),
                NULL, 
                v_date 
            );
            SET i = i + 1;
        END WHILE;
        SET v_date = DATE_ADD(v_date, INTERVAL 1 MONTH);
    END WHILE;
END$$

DELIMITER ;

CALL test();

SELECT * FROM iothistory;

• 호식

drop table ghtlf;
create table ghtlf (
	ghtlfid int auto_increment primary key,
    room int,
    stday date,
    edday date,
    deposit int,
    dnjftp int,
    epty int,
	dlfma varchar(20),
    tel varchar(20),
    bname varchar(20)
);

INSERT INTO ghtlf (room, stday, edday, deposit, dnjftp, epty, dlfma, tel, bname)
VALUES 
(101, '2022-12-01', '2024-12-31', 5000, 521, 1, '이창민', '010-1654-3450', '창민치킨'),
(102, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL),
(103, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL),
(201, '2023-05-01', '2025-05-01', 4500, 490, 1, '정우성', '010-1234-5678', '정우피자'),
(202, '2024-08-01', '2026-08-01', 3000, 457, 1, '김호식', '010-5907-1832', '맘스터치'),
(203, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL),
(301, '2023-03-01', '2025-03-01', 3200, 400, 1, '박지성', '010-3333-4444', '지성커피'),
(302, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL),
(303, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL),
(304, '2024-12-03', '2027-12-03', 4000, 625, 1, '이말자', '010-9569-9780', '엄마몰래 PC방');

select * from ghtlf;