Verdana

오라클을 사용할때는 어렵게 구현 했던것을 MySQL을 사용하면서 아주 쉽게 구현이 가능했다.

+-----------+----------+
| colHeader | value    |
+-----------+----------+
| Header1   | value 1  |
+-----------+----------+
| Header2   | value 2  |
+-----------+----------+
| Header3   | value 3  |
+-----------+----------+

+-----------+-----------+-----------+
| Header1   | header2   | Header3   |
+-----------+-----------+-----------+
| Value 1   | value 2   | Value 3   |
+-----------+-----------+-----------+

SELECT t1.value AS `Header1`,
       t2.value AS `Header2`,
       t3.value AS `Header3`
  FROM (SELECT * FROM table1 WHERE colHeader='Header1') t1
  JOIN (SELECT * FROM table1 WHERE colHeader='Header2') t2
  JOIN (SELECT * FROM table1 WHERE colHeader='Header3') t3;

SELECT  
  ID,
  GROUP_CONCAT(if(colHeader = 'Header 1', value, NULL)) AS 'Header 1', 
  GROUP_CONCAT(if(colHeader = 'Header 2', value, NULL)) AS 'Header 2', 
  GROUP_CONCAT(if(colHeader = 'Header 3', value, NULL)) AS 'Header 3' 
FROM myTable
GROUP BY ID;

[출처] MySQL (or MariaDB) 에서 row 데이터를 column 으로 변경하기.|작성자 동쪽새

MySQL에서 사용되는 CASE WHEN 구문에 대해서 알아보겠습니다. CASE WHEN 구문은 프로그래밍 switch문과 굉장히 흡사한 구조입니다.

기존 SELECT 구문으로 출력한 모습입니다. 여기서 조건을 넣어 각각 다른 값을 출력할 수 있도록 해보겠습니다.

CASE WHEN 을 사용한 쿼리문입니다. 맨 위부터 천천히 살펴보면 일단 SELECT 구문으로 name 필드와 iq 필드를 출력하도록 설정했습니다.

여기서 name은 기존 출력하던 방식 그대로 출력하고 iq부분은 CASE WHEN 구문을 이용하여 조건을 넣었습니다.

만약 iq가 100을 넘는다면 기존 iq부분의 데이터를 THEN구문을 이용하여 천재라는 문자열로 변경하여 출력합니다. 

그리고 iq가 100이라면 iq부분의 데이터를 보통이라는 문자열로 변경합니다.

ELSE 구문은 위의 조건식 외 모든 데이터에 모두 적용됩니다. 그래서 위 조건식 외 모든 데이터는 바부라는 문자열로 변경됩니다.

CASE WHEN 구문 마지막에는 END 라는 구문을 써줘야합니다. (CASE = 시작, END = 끝)

마지막으로 iq 필드명을 AS를 이용하여 수준으로 변경하여 출력합니다.

출처 : http://kb.globalsoft.co.kr/web/web_view.php?notice_no=200

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX({컬럼명}, 'x', 2), 'x', -1) AS sHeight FROM {테이블명} -- {1024x512} 형식의 데이터를 x로 구분하여 두번째값 추출

ex) SELECT SUBSTRING_INDEX('www.mytestpage.info','.',2);  

RESULT -> www.mytestpage

SELECT SUBSTRING_INDEX('www.mytestpage.info','.',-2);   

RESULT -> mytestpage.info

SELECT UNIX_TIMESTAMP(CURDATE()); -- 오늘 시작일

SELECT UNIX_TIMESTAMP(CURDATE()) + 86399; -- 오늘 종료일

SELECT UNIX_TIMESTAMP(DATE_SUB( CURDATE() , INTERVAL 6 DAY)); -- 6일전 날짜

SELECT UNIX_TIMESTAMP(DATE_SUB( CURDATE() , INTERVAL 1 DAY)); -- 1일전 날짜

SELECT UNIX_TIMESTAMP(CONCAT( YEAR(CURDATE()), '-', MONTH(CURDATE()), '-01' )); -- 이번달 첫날 

SELECT UNIX_TIMESTAMP(LAST_DAY(CURDATE())); -- 이번달 마지막 날짜 

SELECT CURDATE(); -- 오늘 날짜 YYYY-mm-dd

SELECT DAY(CURDATE()) - 1; -- 오늘을 제외한 이번달 날짜

SELECT YEAR(CURDATE()); -- 이번년

SELECT MONTH(CURDATE()); -- 이번달

SELECT DAYOFMONTH(CURDATE()); -- 오늘

SELECT HOUR(NOW()); -- 시간

SELECT MINUTE(NOW()); -- 분

SELECT SECOND(NOW()); -- 초

SELECT UNIX_TIMESTAMP(CURDATE())-1

SELECT DATE_FORMAT(NOW() , '%Y-%m-%d %H:%i:%s')

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());

- 마스터, 슬레이브 Replication(동기화) 상태 보는 법

show master status -- 마스터에서

show slave status -- 슬레이브에서 (쉘에서 볼땐 show slave status \G; 로 하면 보기좋게 보임)

Master_Log_File

Read_Master_Log_Pos

SLAVE DB에서 확인해서

Slave_IO_Running와 Slave_SQL_Running이 Yes이고,

Read_Master_Log_Pos와 Exec_Master_Log_Pos 값이 일치하면 정상적으로 복제중이다.

==============================================================================

보통 리플리케이션이 깨졌을땐

슬레이브서버에서 stop slave 후

방법1. 

  에러부분을 수정

방법2.

  SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

처리 후 START SLAVE 하면 됨

- SLAVE DB 추가 및 이전 시 Read_Master_Log_Pos 틀릴경우 맞춰주는 법

슬레이브 서버에서

stop slave; # 슬레이브 서버를 멈춘 뒤

show slave status; # 현재 마스터의 쿼리실행내역을 확인

`Master_Log_File`

`Read_Master_Log_Pos`

# 서버이전 완료 후 슬레이브 서버에서 해당 쿼리 입력

CHANGE MASTER TO

MASTER_HOST = 'xxx.xxx.xxx.xxx'

,MASTER_PORT = 3306

,MASTER_USER = 'repl_user'

,MASTER_PASSWORD = 'NICE!COPYcat#00'

,MASTER_LOG_FILE = '마스터DB의 binary파일'

,MASTER_LOG_POS = 576375776;

START SLAVE;

UPDATE `table` SET `uid` = CASE
    WHEN id = 1 THEN 2952
    WHEN id = 2 THEN 4925
    WHEN id = 3 THEN 1592
    ELSE `uid`
    END
WHERE id  in (1,2,3)

쿼리 한번에 여러ROW의 값을 UPDATE 할 수 있다