i can do ik!

더미데이터를 삽입하는 프로시저 생성

다량의 데이터를 조회하는 쿼리의 속도 개선을 위해 더미데이터를 만들어야 했다.

검색 결과 프로시저를 생성하여 더미데이터를 삽입할 수 있었다.

DELIMITER $$
DROP PROCEDURE IF EXISTS loopInsert$$
 
CREATE PROCEDURE loopInsert()
BEGIN
    DECLARE i INT DEFAULT 1;
        
    WHILE i <= 10000 DO
        INSERT INTO FREQBANDINFO(fb_masterkey , fb_datetime, fb_freq , fb_th_level , fb_ch_level)
          VALUES('20211025112428205', now(), '2399000064', '-128.5', '-128.5');
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$

프로시저 실행

CALL loopInsert;

주의

멍청하게 1000만개를 한번에 넣었다가 한나절 걸렸다 천천히 테스트하면서 해볼것.

원인

외부에서 DB 접속 시도시 나는 에러.

보안상 root 계정에 아무나 접근 할 수 없기에 허용할 IP를 지정해야 한다.

모든 IP 허용

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '패스워드';

특정 IP 허용

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.254' IDENTIFIED BY '패스워드';

특정 IP 대역대 허용

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY '패스워드';

IP 허용 후 원상태로 복구

DELETE FROM mysql.user WHERE Host='%' AND User='아이디';
FLUSH PRIVILEGES;

서버 구축 시 실제로 일어났었으니 까먹지 말자.

DATE_FORMAT()

날짜를 지정한 형식으로 출력

-- 날짜를 형식으로 변환
DATE_FORMAT(날짜 , 형식)

형식

예제

-- 2022-01-15
DATE_FORMAT(NOW(),'%Y-%m-%d')

DATEDIFF

DATEDIFF는 문자 그대로 두 날짜 간의 차이를 DATE(일)로 가지고 온다.

-- 날짜1 - 날짜2
DATEDIFF(날짜1, 날짜2)

계산은 날짜1 - 날짜2로 진행되므로 순서에 유의.

TIMESTAMPDIFF

TIMESTAMPDIFF는 모든 시간 관련 차이를 계산.

-- 날짜2 - 날짜1
TIMESTAMPDIFF(단위, 날짜1, 날짜2);

계산은 DATEDIFF와 반대로, 날짜2 - 날짜1로 진행.

TIMESTAMPDIFF 단위

• SECOND - 초
• MINUTE - 분
• HOUR - 시
• DAY - 일
• WEEK - 주
• MONTH - 월
• QUARTER - 분기
• YEAR - 연

CASE

CASE문은 프로그래밍 언어 스위치(switch)문과 비슷하다.

단 다수의 조건에 하나의 반환 값은 불가하다.

WHEN, THEN과 함께 사용해야 한다.

WHEN, THEN의 갯수는 상관없다.

-- 조건에 따른 반환값
CASE
    WHEN '조건1'
    THEN '반환값1'
    WHEN '조건2'
    THEN '반환값2'
    ELSE '조건1, 조건2둘다 아닌 경우'
END

-- 비교문에 따른 반환값
CASE
    WHEN '비교문1'
    THEN '반환값1'
    WHEN '비교문2'
    THEN '반환값2'
    ELSE '비교문1, 비교문2 둘다 아닌 경우'
END

예제

-- 조건
CASE
    WHEN 'A' 
    THEN 'A입니다.'
    WHEN 'B'
    THEN 'B입니다.'
    ELSE 'A,B둘 다 아닙니다.'
END

-- 비교문
CASE
    WHEN 컬럼 LIKE '%A%' 
    THEN 'A를 포함 합니다'
    WHEN 컬럼 LIKE '%B%'
    THEN 'B를 포함 합니다.'
    ELSE 'A,B둘 다 포함하지 않습니다.'
END

IN

IN은 특정 값이 들어있을 경우에만 조회하려는 경우 사용.

값은 콤마( , )로 구분하여 괄호 내에 묶어 사용.

검색어 들은 OR 조건으로 검색 된다.

-- 검색어1 또는 검색어2인 값만 출력
WHERE [컬럼명] IN ('검색어1', '검색어2');

-- SELECT문의 조건만 출력
WHERE [컬럼명] IN (SELECT문);

NOT IN

NOT IN은 IN과 반대로 특정 값이 들어있지 않을 경우에만 조회하려는 경우 사용.

-- 검색어1 또는 검색어2가 아닌 값만 출력
WHERE [컬럼명] NOT IN ('검색어1', '검색어2');

-- SELECT문의 조건이 아닌 값만 출력
WHERE [컬럼명] NOT IN (SELECT문);

ORDER BY

데이터 조회할 때 ORDER BY를 사용하여 지정한 컬럼 기준으로 정렬한다.

오름차순(ASC), 내림차순(DESC) 두 가지 정렬 방법.

기본 정렬은 오름차순이다.

-- 오름차순 정렬
SELECT * FROM [테이블명] ORDER BY [컬럼명] ASC; -- ASC 생략가능

-- 내림차순 정렬
SELECT * FROM [테이블명] ORDER BY [컬럼명] DESC;

-- 다중 정렬
SELECT * FROM [테이블명] ORDER BY [컬럼명1] DESC, [컬럼명2] ASC;

컬럼 번호로 정렬이 가능하지만 사용하지 말자.

GROUP BY

특정 컬럼을 기준으로 그룹화할 때 사용.

데이터 통계 내기 위한 집계 함수(avg, sum...)를 사용하기 편리.

-- 컬럼 그룹화
SELECT [컬럼명] FROM [테이블명] GROUP BY [그룹화할 컬럼];

HAVING

특정 컬럼을 그룹화한 후 결과에 조건을 걸 때 사용.

WHERE - 그룹화 하기 전의 조건

HAVING - 그룹화 후의 조건

-- 컬럼 그룹화 후 조건 처리
SELECT [컬럼명] FROM [테이블명] GROUP BY [그룹화할 컬럼] HAVING [조건식];

-- 조건 처리 후 컬럼 그룹화 후 조건 처리
SELECT [컬럼명] FROM [테이블명] WHERE [조건식] GROUP BY [그룹화할 컬럼] HAVING [조건식];