MySQL 활용 (기타)

1. MySQL 활용 (기초)

  ㅇ 주석(comment) : #,  --,  /* */ 

  ㅇ 백틱(`) : SQL 명령어와 테이블명,스키마명,칼럼명 등과 구분시켜 가독성을 높임
      例) SELECT * FROM `select`


2. 사례별 활용  :  일반

  ㅇ 특정 값이 없으면 INSERT 하고, 있으면 UPDATE 하기
     -  INSERT ~ ON DUPLICATE UPDATE ~

     - 例) INSERT id=t_id,email=t_email ON DUPLICATE UPDATE id=t_id,email=t_email
        . 특정 t_id가 없으면, t_id 및 t_email을 함께 INSERT 하고, 
        . 특정 t_id가 있으면, t_email로 UPDATE 함

  ㅇ 중복값 찾기
     - SELECT t1, count(*) as num FROM test GROUP BY t1 HAVING num>1;

  ㅇ 조건_1에 맞게 select한 후, 조건_2에 맞게 update 하기
     - UPDATE tbl1 a LEFT JOIN (SELECT col1,col2 FROM tbl2 WHERE 조건_!) b
              ON a.col1=b.col2 SET 대입절 WHERE 조건_2 

  ㅇ NULL 비교 조건
     - column IS NULL 또는 column IS NOT NULL 로써 가능
     - column = NULL 은 비교를 위한 올바른 문법이 아니고, 단지 대입문 임

  ㅇ LIMIT
     - SELECT * FROM tblname LIMIT 시작점,개수 


3. 사례별 활용  :  번호(순서)

  ㅇ 비어있는 id 번호 찾기
     - SELECT a.id + 1 as available FROM tblname a LEFT JOIN tblname b ON b.id = (a.id + 1)
              WHERE b.id IS NULL ORDER BY a.id LIMIT 0,1

  ㅇ SELECT 결과 집합의 행 번호 매기기  : @rownum (세션 변수 활용)
     - ①  SET @rownum:=0; SELECT (@rownum:=@rownum+1) AS num, ... FROM 테이블명 ... ;
     - ②  SELECT (@row_number:=@row_number + 1) AS num, ... 
                        FROM (SELECT @row_number:=0) AS t, 테이블명 ...


4. MySQL에서, 트리 구조 구현

  ㅇ 조건
     - (자기자신 ID : id, 부모 ID : pid, 루트 ID : 0)

  ㅇ 특정 노드로부터 모든 부모 노드 찾기
     - SELECT문에 의한 방법
        . SELECT @r AS _id, (SELECT @r:=pid FROM temp_table WHERE id=_id LIMIT 1) AS _pid
          FROM (SELECT @r:=특정노드ID) vars, (SELECT * FROM temp_table) h 
          WHERE @r > 0

  ㅇ 직계 자식 노드 수
     - SELECT a.id, a.title, COUNT(b.id) as childCount FROM treetbl a LEFT JOIN
           treetbl b ON a.id=b.parent GROUP BY a.id

  ㅇ 직계 자식 노드 수를 child에 업데이트
     - UPDATE treetbl a LEFT JOIN (SELECT parent,count(*) as cnt FROM treetbl GROUP BY parent) b
           ON a.id=b.parent SET a.child=IFNULL(b.cnt,0)


6. MySQL Fulltext 검색

  ㅇ 색인 걸기
     - CREATE FULLTEXT INDEX idx_name ON tblname (colname);
     - ALTER TABLE tblname ADD FULLTEXT(col1, col2);

  ㅇ 색인 재구성
     - REPAIR TABLE tblname QUICK;