CREATE EVENT if not exists forme //forme라는 이벤트 없으면 만듦 ON SCHEDULE EVERY 1 second //1초마다 실행 STARTS CURRENT_TIMESTAMP //지금시간부터 시작~ DO INSERT INTO new_table (name) values ('hi'); //실행할 쿼리문~
SHOW EVENTS ; //이벤트 확인
이벤트 삭제
DROP EVENT IF EXISTs forme;
사실 해도 실행이 안됐었다.
알고보니 insert에서 막히는 것이었음
만든 table 에서 id가 not null로 빈값 못들어가는데 name만 자꾸 넣어주려니까 안되었던 것이다.
use test;
truncate sequence;
set @setTime = (select subtime(now(),'00:02:00'));
insert into sequence (name,deposit_date,deposit_amount,interest_amount) values ('이소윤',@setTime,10000000,0);
drop procedure if exists pro;
delimiter //
create procedure pro()
begin
while (select id from sequence order by id desc limit 1) < 10 do
INSERT INTO sequence (name,deposit_date,deposit_amount,interest_amount)
select
(select name from sequence order by id desc limit 1),
date_add((select deposit_date from sequence order by id desc limit 1),INTERVAL 1 hour),
((select deposit_amount from sequence order by id desc limit 1) + (select interest_amount from sequence order by id desc limit 1)),
(select deposit_amount from sequence where id=1)* 0.04 /12;
end while;
end //
delimiter ;
call pro();
select * from sequence;
use test;
truncate sequence;
set @setTime = (select subtime(now(),'00:02:00'));
insert into sequence (name,deposit_date,deposit_amount,interest_amount) values ('이소윤',@setTime,10000000,0);
drop procedure if exists pro;
delimiter //
create procedure pro()
begin
while (select MAX(id) from sequence) < 10 do
INSERT INTO sequence (name,deposit_date,deposit_amount,interest_amount)
select
(select MIN(name) from sequence),
date_add((select MAX(deposit_date) from sequence),INTERVAL 1 hour),
((select MIN(deposit_amount) from sequence) - (select MAX(interest_amount) from sequence)),
(select MAX(deposit_amount) from sequence)* 0.02;
end while;
end //
delimiter ;
call pro();
select * from sequence;
이거는 order by 랑 섞어서 쓴거.
use test;
truncate sequence;
set @setTime = (select subtime(now(),'00:02:00'));
insert into sequence (name,deposit_date,deposit_amount,interest_amount) values ('이소윤',@setTime,10000000,0);
drop procedure if exists pro;
delimiter //
create procedure pro()
begin
while (select MAX(id) from sequence) < 10 do
INSERT INTO sequence (name,deposit_date,deposit_amount,interest_amount)
select
(select MIN(name) from sequence),
date_add((select MAX(deposit_date) from sequence),INTERVAL 1 hour),
((select deposit_amount from sequence where deposit_amount order by id desc limit 1) - (select MAX(interest_amount) from sequence)),
(select deposit_amount from sequence where id =1)* 0.02;
end while;
end //
delimiter ;
call pro();
select * from sequence;
set @setTime = (select subtime(now(),'00:02:00'));
insert into sequence (name,deposit_date,deposit_amount,interest_amount) values ('이소윤',@setTime,'10000000','0');
drop procedure if exists pro; delimiter // create procedure pro() begin while (select id from sequence order by id desc limit 1) < 10 do
INSERT INTO sequence (name,deposit_date,deposit_amount,interest_amount) select (select name from sequence order by id desc limit 1), date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 hour), ((select deposit_amount from sequence where deposit_amount order by id desc limit 1) + (select interest_amount from sequence where interest_amount order by id desc limit 1)), (select deposit_amount from sequence where id='1')* 0.04 /12;
end while; end // delimiter ;
call pro();
select * from sequence;
아 다음행에 insert 추가 되는게
마지막 id가 9이니까
10까지 뜨는거구나~!!
그래도 deposit_amount의 마지막행 10000000을 구해서 이자액 33333을 구했으면
use test; truncate sequence; set @setTime = (select subtime(now(),'00:02:00')); insert into sequence (name,deposit_date,deposit_amount,interest_amount) values ('이소윤',@setTime,10000000,0);
drop procedure if exists pro; delimiter // create procedure pro() begin DECLARE n varchar(45) default "이소윤" ;
while (select id from sequence order by id desc limit 1) < 10 do INSERT INTO sequence (deposit_date) select (date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 hour));
update sequence set name = n, interest_amount = (select deposit_amount where deposit_amount order by id desc limit 1)* 0.04 /12;
end while;
end // delimiter ; call pro();
select * from test.sequence;
---
시간 증가만 insert해주고
나머지는 update로 넣어주려 했으나 잘안됨..
그래서 원래 한번에 다 insert한 걸로 수정함
중간에 null인거..
흠
그냥 id가 2인거 조건으로 넣어줌..
이러면 안될거같지만
use test;
truncate sequence;
set @setTime = (select subtime(now(),'00:02:00'));
insert into sequence (name,deposit_date,deposit_amount,interest_amount) values ('이소윤',@setTime,10000000,0);
drop procedure if exists pro;
delimiter //
create procedure pro()
begin
while (select id from sequence order by id desc limit 1) < 10 do
INSERT INTO sequence (name,deposit_date,deposit_amount,interest_amount)
select
(select name from sequence order by id desc limit 1),
date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 hour),
((select deposit_amount from sequence where deposit_amount order by id desc limit 1) + (select interest_amount from sequence where interest_amount order by id desc limit 1)),
(select deposit_amount from sequence where id='1')* 0.04 /12;
end while;
end //
delimiter ;
call pro();
update sequence
set deposit_amount = 10000000
where id = 2;
select * from sequence;
mysql구분 기호를 재정의하려면delimiter명령을 사용하십시오.//전체 정의를 단일 명령문으로 서버에 전달한 다음;프로시저를 호출하기 전에 로 복원할 수 있도록구분 기호가 변경됩니다 .이렇게 하면 프로시저 본문에 사용된 구분 기호가mysql자체;에서 해석되지 않고 서버로 전달될 수 있습니다."
INSERT INTO sequence (deposit_date,interest_amount) select date_add(now(),INTERVAL 1 MONTH),10000000* 0.04 /12;
숫자로 넣어서 하긴했는데 실제는 이렇게 하면 안될듯
재사용할 수 있게 변수지정해야되지 않을까
<4차시도>
INSERT INTO sequence (deposit_date,interest_amount) select date_add((select deposit_date from sequence where id='1'),INTERVAL 1 MONTH),(select deposit_amount from sequence where id='1')* 0.04 /12;
where id="1"
로 첫번째 행에 있는 값을 사용하게 했다. 금액은 괜찮은데
하지만 날짜는 이전행을 기준으로 한달씩 증가되어야한다.
<5차시도>
LAG함수쓰면 될거같은데 안되네....
아 된건가?
SELECT * FROM test.sequence; use test; insert into sequence (name,deposit_date,deposit_amount) values ('이소윤','20200509','10000000'); INSERT INTO sequence (deposit_date,interest_amount) select date_add(now(),INTERVAL 1 MONTH),10000000* 0.04 /12; select deposit_date, LAG(deposit_date) over(order by deposit_date) from sequence;
<6차시도>
흠...
select deposit_date from sequence order by id desc limit 1;
마지막행선택함.
이렇게 마지막 행의 날짜를 선택하고 거기에 한달씩 더해주면 되지 않을까?
select date_add(deposit_date,INTERVAL 1 MONTH) from sequence where deposit_date order by id desc limit 1;
오 됐다
한달 추가하겠다 마지막행으로 찾은 날짜에
ㅇㄴㄹㄴ
오됐다
INSERT INTO sequence (deposit_date,interest_amount) select date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 MONTH),(select deposit_amount from sequence where id='1')* 0.04 /12;
<종합해보면>
SELECT * FROM test.sequence; use test; insert into sequence (name,deposit_date,deposit_amount) values ('이소윤','20200509','10000000');
INSERT INTO sequence (deposit_date,interest_amount) select date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 MONTH),(select deposit_amount from sequence where id='1')* 0.04 /12;
<7차시도>
아 이름도 같이 들어가게끔
INSERT INTO sequence (name, deposit_date,interest_amount) select (select name from sequence order by id desc limit 1) ,date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 MONTH), (select deposit_amount from sequence where id='1')* 0.04 /12 ;
<결과>
use test;
insert into sequence (name,deposit_date,deposit_amount) values ('이소윤','20200509','10000000');
INSERT INTO sequence (name, deposit_date,interest_amount)
select (select name from sequence order by id desc limit 1) ,date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 MONTH),
(select deposit_amount from sequence where id='1')* 0.04 /12 ;
"blob의 경우 4GB의 이진 데이터를 저장할 수 있다고 합니다. 하지만 이건 DB에 직접 저장하는 것이 아니라 DB에는 Large Object의 위치 포인터만 저장하게 됩니다."
그말은 즉, 컴퓨터가 인식하는 모든 파일(이진 데이터)를 저장하는 타입이라고 한다.오 찾아보니까 볼 수 있긴하네
이렇게 확인할 수는 있구만
하지만 BLOB보다는 URL자체로 저장을 선호한다고 한다.
그 이유는데이터베이스 서버는 애플리케이션이 확장될 때 종종 성능병목 현상이 일어나는데 이미지와 함께 로드하면 더 큰 병목현상이 발생한다고 함.
Many web app designers don't store images in database BLOBS, but rather store them in a file system, and store their URLs in database strings. Why? Database servers often become a performance bottleneck when an application scales up. If you load them with images, they'll become even bigger bottlenecks.
그럼 string문자열로 바꿔줘야지~
하고 string찾는데 없어 아 맞다 VAR이지하고 보다가
VARCHAR과 CHAR의 차이가 뭔가 궁금해졌다
VARCHAR은 '가변길이'
실질적인 데이터와길이 정보도 같이 저장된다.
CHAR은 길이가 고정되어있어야한다. 남는 공간은 공백으로 채운다 공간낭비 발생!
VARCHAR is variable length, while CHAR is fixed length
