MySQL 활용 (기타)

(2017-12-04)
1. MySQL 활용 (기초)주석(comment) : #,  --,  /* */ 


2. MySQL 계정 및 db 추가

  ㅇ 사용자 이름 및 암호 생성
     - CREATE USER '사용자id'@localhost IDENTIFIED BY '비밀번호';

  ㅇ 특정 사용자에게 특정 db 권한을 줌
     - CREATE DATABASE IF NOT EXISTS `db명` ;
     - GRANT ALL PRIVILEGES ON `db명`.* TO '사용자id'@localhost ;

  ㅇ 특정 사용자에 특정 테이블에 대한 권한 부여
     - GRANT SELECT,UPDATE ON db명.테이블명 TO '사용자id'@'localhost' ;

  ㅇ 특정 사용자에 function 실행 권한 부여
     - GRANT EXECUTE ON FUNCTION `db명`.`함수명` TO '사용자id'@'localhost' ;

  ㅇ function,procedure 에 대한 dump 권한 부여
     - GRANT SELECT ON mysql.proc TO '사용자id@localhost';

  ㅇ 특정 사용자에게 설정된 grant 권한 보기
     - SHOW GRANTS FOR '사용자id'@'localhost' ;


3. 사례별 활용

  ㅇ 특정 값이 없으면 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;

  ㅇ 신규 비어있는 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

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

  ㅇ SELECT 결과 집합의 행 번호 매기기  : @rownum (세션 변수 활용)
     - SET @rownum:=0; SELECT (@rownum:=@rownum+1) ... ;


4. MySQL 함수문자열
     - 문자열 발췌 
        . SUBSTRING(문자열,시작위치,길이) : 시작위치까지 문자열 반환
        . SUBSTRING_INDEX(문자열,'구분자',몇번째위치) : 구분자 위치까지 문자열 반환
     - 문자열 대체 : REPLACE(문자열,대상문자열,대체문자열)
     - 문자열 위치 : POSITION(substr in str) = locate(substr,str)
     - leading zero 삽입 : LPAD(변수명,3,'0')
     - 특정 문자열(substr) 카운트
        . CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str,substr,''))

  ㅇ 형변환
     - (INT => STRING) CAST(num AS char(3))

  ㅇ 날짜,시간
     - 1개월 전 : DATE_SUB(NOW(), INTERVAL 1 MONTH)
     - 1개월 후 : DATE_ADD(NOW(), INTERVAL 1 MONTH)


5. 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 OUTER JOIN
         treetbl b ON a.id=b.parent GROUP BY a.id


6. MySQL Fulltext 검색색인 걸기
     - CREATE FULLTEXT INDEX idx_name ON tblname (colname);
     - ALTER TABLE tblname ADD FULLTEXT(col1, col2);

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


[MySQL] 1. MySQL 2. MySQL 서버 3. 저장 프로그램 4. 문자 세트 5. MySQL 활용 (기타) 6. MySQL 백업

 
        최근수정     참고문헌