i can do ik!

NOT LIKE

특정 문자가 포함되어 있지 않는 데이터를 검색할 때 사용.

-- 특정 문자를 포함하지 않는 데이터 검색
SELECT * FROM [테이블명] WHERE [컬럼명] NOT LIKE '%특정 문자열%';

NOT LIKE 두개 이상 사용할 때

기본 검색 방식은 동일.

-- 특정 문자를 전부 포함하지 않는 결과
SELECT * FROM [테이블명] WHERE ([컬럼명] NOT LIKE '%특정문자1%' AND [컬럼명] NOT LIKE '%특정문자2%');

• MySQL/Maria DB를 사용하다 보면 한글 깨짐 현상이 종종 발생하게 된다.
• 영문이 아닌 한글만 깨지는 경우에는 보통 2가지 방법으로 해결이 가능.

my.ini 파일 소스 추가

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

해당 소스를 my.ini 파일에 추가 한다.

데이터베이스 기본 인코딩 셋 변경

alter database [데이터베이스명] default character set UTF8;

해당 데이터베이스 이름을 입력 하고 쿼리문을 실행 한다.

EXISTS

• 한 테이블이 다른 테이블과 외래키(FK)와 같은 관계가 있을 때 유용.
• 조건에 해당하는 ROW의 존재 유무와 이후 더 수행하지 않음.
• 일반적으로 SELECT절까지 가지 않기에 IN에 비해 속도나 성능면에서 더 좋음.
• 메인 쿼리 -> EXISTS 쿼리

※ IN절은 IN 쿼리 -> 메인 쿼리의 순서. 이 부분이 IN절과 EXISTS절의 가장 큰 차이

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

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

NOT EXISTS

NOT EXISTS는 EXISTS와 반대로 해당 조건을 제외하고 출력 할 때 사용.

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

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

EXPLAIN

SELECT문을 수행하는 방법에 대해 정보를 얻기 위한 수단.

EXPLAIN SELECT * FROM TABLE_NAME

EXPLAIN 결과

id: 1 
select_type: SIMPLE 
table: TABLE_NAME 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 1
Extra: NULL

• id : 쿼리 안에 있는 각 select 문에 대한 순차 식별자. 이순서대로 select문이 실행된다고 생각하면 된다.
• select_type : select 문의 유형.
  • SIMPLE : 서브쿼리나 'union'이 없는 가장 단순한 select문.
  • PRIMARY : 가장 바깥에 있는 select문.
  • DERIVED : from 문 안에있는 서브쿼리의 select문.
  • SUBQUERY : 가장 바깥의 select 문에 있는 서브쿼리.
  • DEPENDENT SUBQUERY : 기본적으로 SUBQUERY와 같은 유형, 가장 바깥 select문에 의존성을 가진 서브쿼리.
  • UNION : union 문의 두번째 select문.
  • DEPENDENT UNION : 바깥 쿼리에 의존성을 가진 union문의 두번째 select문.
• table : 참조되는 테이블.
• type : MySQL이 어떤식으로 테이블들을 조인하는지를 나타내는 항목. 이는 매우 중요한데, 이 타입을 분석함으로써 어떤 인덱스가 사용여부를 알 수 있고, 어떤식으로 쿼리가 튜닝되어야하는지에 대한 insight를 제공하기 때문.
  • system : 0개 또는 하나의 row를 가진 테이블.
  • const : 테이블에 조건을 만족하는 레코드가 하나일 때, 상수 취급
  • eq_ref : primary key나 unique not null column으로 생성된 인덱스를 사용해 조인을 하는 경우.  const 방식 다음으로 빠른 방법.
  • ref : 인덱스로 지정된 컬럼끼리의 '=' , '<=>' 와 같은 연산자를 통한 비교로 수행되는 조인이다
  • index_merge : 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후에 그 결과를 병합하는 처리 방식
  • unique_subquery : 오직 하나의 결과만을 반환하는 'IN'이 포함된 서브쿼리의 경우이다.
  • index_subquery : unique_subquery와 비슷하지만 여러개의 결과를 반환한다
  • range : 특정한 범위의 rows들을 매칭시키는데 인덱스가 사용된 경우이다. BETWEEN이나 IN, '>', '>=' 등이 사용될 때이다.
  • all : 조인시에 모든 테이블의 모든 row를 스캔하는경우이다. 물론 성능이 가장 좋지 않다.
• possible_keys : 테이블에서 row를 매핑시키기 위해 사용 가능한 (사용하지 않더라도) 키를 보여준다.  
• key : 실제 쿼리 실행에 사용된 key 목록. 이 항목에는 possible_keys 목록에 나타지 않은 인덱스도 포함 될 수 있다.
• ref : key column에 지정된 인덱스와 비교되는 column 또는 constants를 보여준다.
• rows : 결과 산출에 있어서 접근되는 record의 숫자. 조인문이나 서브쿼리 최적화에 있어서 중요한 항목.
• Extra : 실행계획에 있어서 부가적인 정보를 보여준다.
  • distinct : 조건을 만족하는 레코드를 찾았을 때 같은 조건을 만족하는 또 다른 레코드가 있는지 검사하지 않음.
  • not exist : left join 조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 검사하지 않는다.
  • range checked for each record : 최적의 인덱스가 없는 차선의 인덱스를 사용한다는 의미.
  • using filesort : mysql이 정렬을 빠르게 하기 위해 부가적인 일을 한다.
  • using index : select 할때 인덱스 파일만 사용
  • using temporary : 임시 테이블을 사용. order by 나 group by 할때 주로 사용
  • using where : 조건을 사용한다는 의미.

CONCAT()

CONCAT()은 둘 이상의 문자열이나 컬럼 값을 순서대로 합쳐서 반환해 주는 함수.

단, 합쳐지는 문자열이나 컬럼값이 NULL을 포함한다면 반환되는 값도 NULL.

SELECT CONCAT(문자열1, 문자열2 [, 문자열3 ...]) FROM 테이블명

CONCAT() 예제

SELECT CONCAT('AA','BBB',' ','CCC','DD') FROM TABLE_NAME;
SELECT CONCAT('999','.','111') FROM DUAL;
SELECT CONCAT('AA','BBB',NULL,'CCC','DD') FROM TABLE_NAME;

#AABBB CCCDD
#999.111
#NULL

CONCAT_WS()

CONCAT_WS()는 둘 이상의 문자열이나 컬럼 값을 반복되는 구분자를 넣고 순서대로 합쳐서 반환해 주는 함수.

CONCAT()와는 다르게 NULL을 포함하여도 NULL로 반환하지 않는다.

SELECT CONCAT_WS(구분자, 문자열1 [, 문자열2, 문자열3 ...]) FROM 테이블명

CONCAT_WS() 예제

SELECT CONCAT_WS(',','AA','BBB','CCC','DD') FROM DUAL;
SELECT CONCAT_WS(',','AA','BBB',NULL,'CCC','DD') FROM DUAL;
SELECT CONCAT_WS(',','111','222','333') FROM DUAL;

#AA,BBB,CCC,DD
#AA,BBB,CCC,DD
#111,222,333

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

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

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

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')