MySQL 활용 (기타)

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


2. MySQL 계정 및 db 추가

  ㅇ 사용자 및 db 를 새로이 만들고 권한을 줌
     - CREATE USER '사용자id'@localhost IDENTIFIED BY '비밀번호';
     - 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 백업  
  1.   기술공통
  2.   기초과학
  3.   파동/광학/음향
  4.   방송/멀티미디어/정보이론
  5.   전자/전기/제어
  6.   통신/네트워킹
  7.   정보기술(IT)
        1. 정보기술
    1.   전산기초
    2.   컴퓨터구조
    3.   프로그래밍
    4.   데이터베이스
          1. DB
      1.   데이터베이스 기초
      2.   관계형데이터베이스
            1. 관계형 데이터베이스
            2. 관계DB 주요 용어
            3. 관계(relation)
            4. 튜플
            5. 관계 대수
        1.   SQL
        2.   MySQL
          1.   1. MySQL
              2. MySQL 서버
              3. 저장 프로그램
              4. 문자 세트
              5. MySQL 활용 (기타)
              6. MySQL 백업
      3.   DB 모델링/설계
      4.   회복,병행제어
      5.   데이터웨어하우스
      6.   데이터베이스(기타일반)
    5.   소프트웨어 공학
    6.   운영체제
    7.   정보보호/보안
    8.   IT 기타기술
  8.   기계/재료/공업일반
  9.   표준/계측/품질
  10.   기술경영

 
        최근수정     모바일웹     참고문헌