미생

■ 행, 열 변환 작업

테이블의 행 데이터를 특정 기준으로 열 데이터로 변환하는 작업

■ 예시

과일 가게에서 판매하고 있는 과일은 [수박], [사과], [오렌지] 라고 특정할때, 날짜 별로 과일들의 수량을 계산하여 통계치를 구하려고 한다.

왼쪽은 과일이 판매될때 마다 작성한 데이터이고, 오른쪽은 구하고자 하는 결과 데이터이다. 

■ 작업

첫번째, 과일 가게 가계부 테이블를 생성한다.

CREATE TABLE `tbl_fruit_shop` (
  `pay_time` date NOT NULL COMMENT '구매 날짜',
  `fruit` varchar(50) NOT NULL COMMENT '과일',
  `cnt` int(11) NOT NULL COMMENT '수량'
)

두번째, 과일 가게 가계부 테이블를 조회했을 때 결과를 확인한다.

 * 결과 데이터는 [예시]의 왼쪽 데이터와 동일하다고 가정한다.

결과)

세번째, 각 행을 과일 별로 구분하여 데이터를 출력한다.

 * [CASE 문] 을 통해 행으로 구분되어 있던 데이터를 열로 수정

SELECT pay_time,
          CASE WHEN fruit='수박' THEN cnt END AS 수박,
          CASE WHEN fruit='사과' THEN cnt END AS 사과,
          CASE WHEN fruit='오렌지' THEN cnt END AS 오렌지
FROM tbl_fruit_shop
ORDER BY pay_time

결과)

네번째, 날짜 별로 과일들의 수량을 합산한다.

 * [SUM 함수] 와 [GROUP BY 문법]을 통해 데이터를 그룹화 함과 동시에 과일별 수량 합산

SELECT pay_time,
          SUM(CASE WHEN fruit='수박' THEN cnt END) AS 수박,
          SUM(CASE WHEN fruit='사과' THEN cnt END) AS 사과,
          SUM(CASE WHEN fruit='오렌지' THEN cnt END) AS 오렌지
FROM tbl_fruit_shop
GROUP BY pay_time
ORDER BY pay_time

결과)

다섯째, 날짜 별로 판매되지 않은 과일의 수량은 0으로 치환한다.

 * [COALESCE 함수]를 이용하여 NULL인 데이터는 0으로 치환

SELECT pay_time,
          COALESCE(SUM(CASE WHEN fruit='수박' THEN cnt END), 0) AS 수박,
          COALESCE(SUM(CASE WHEN fruit='사과' THEN cnt END), 0) AS 사과,
          COALESCE(SUM(CASE WHEN fruit='오렌지' THEN cnt END), 0) AS 오렌지
FROM tbl_fruit_shop
GROUP BY pay_time
ORDER BY pay_time

결과)

■ AUTO_INCREMENT 초기화

ALTER TABLE [테이블명] AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE [테이블명] SET [컬럼명] = @COUNT:=@COUNT+1;

■ 예시

인덱스는 1부터 시작하며 로우가 추가됨에 따라 1씩 증가하는 컬럼으로 수정

ALTER TABLE tbl_user AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE tbl_user SET user_id = @COUNT:=@COUNT+1;

주의 !! mysql 5.7 이하 미지원

■ WITH RECURSIVE 문

• 메모리 상에 가상의 테이블을 저장
• 재귀 쿼리를 이용하여 실제로 테이블을 생성하거나 데이터삽입(INSERT)을 하지 않아도 가상 테이블을 생성할 수 있다.

WITH RECURSIVE 테이블명 AS(
  SELECT 초기값 AS 컬럼별명1
  UNION ALL
  SELECT 컬럼별명1 계산식 FROM 테이블명 WHERE 제어문
 )

■ 예시

h(컬럼)이 초기값 1 부터 제어문에 합당하는 5까지의 데이터를 갖는 가상 테이블 생성

WITH RECURSIVE CTE AS(
  SELECT 1 AS h
  UNION ALL
  SELECT h+1 FROM CTE WHERE h<5
)

SELECT * FROM CTE;

※ 결과 확인

■ CASE 문

※ 사용법

• WHEN~THEN 절 : 컬럼명에 해당하는 데이터가 조건에 합당할경우 해당 조건에 결과를 반환한다.
• ELSE 절 : 상위 조건에 모두 합당하지 않을경우 ELSE 의 결과를 반환한다.

CASE 컬럼명 WHEN 조건1 THEN 결과1
                 WHEN 조건2 THEN 결과2
                                 :
                                 :
                 WHEN 조건3 THEN 결과3
                 ELSE 결과4
END

■ 예시

1) CASE_TEST 테이블 데이터 확인

2) TEST_COL 컬럼 CASE 문 처리

SELECT (CASE TEST_COL WHEN 'A' THEN 1
                      WHEN 'B' THEN 2
                      WHEN 'C' THEN 3
                      ELSE 4
                      END) AS TEST_COL
FROM CASE_TEST;

3) 결과 확인

CASE_TEST 테이블의 TEST_COL 컬럼의 데이터가 "A","B","C"가 아닌 데이터는 모두 4의 값으로 반환된다.

■ DATE_FORMAT 형식 종류

■ 예시

Datetime 형식의 (2020-07-20 13:44:41) 데이터를 2020-07-20 형태로 변환

■ 트랜잭션

트랜잭션의 사용은 어떠한 작업에 있어서 구간을 선정하고 해당 작업에 오류나 실수를 COMMIT을 실행하기 전까지 본 DB에 영향을 주지 않는것을 의미한다.

I. 자동 커밋(commit) 비활성화

설명) commit 을 실행하기 전까지의 작업을 본 서버에 영향을 주지 않는다.

• set autocommit = 0

II. 트랜잭션 시작

설명) start transaction 또는 begin 둘다 사용가능하며, 수정 작업 이후 rollback 을 실행하면 트랜잭션을 시작했던 작업으로 돌아오고 수정내용은 적용되지 않는다.

• 1) START TRANSACTION
• 2) BEGIN

III. 수정 처리

1) 수정 전 데이터 확인

2) 수정 처리

설명) author 가 '유은정' 인 레코드의 title을 '산' 으로 변경

3) 수정 후 데이터 확인

IV. ROLLBACK 실행

설명) start transaction 혹은 begin 실행했던 작업 위치로 돌아간다.

1) 수정 처리한 레코드 확인

설명) author 가 '유은정' 인 레코드의 title이 '산' 에서  '바다' 로 변경되었다. 즉, 수정 전으로 작업위치가 돌아갔다는걸 확인할 수 있다.

III. 재수정 처리

1) 수정 처리

설명) author 가 '유은정' 인 레코드의 title을 '산' 으로 변경

3) 수정 후 데이터 확인

설명) author 가 '유은정' 인 레코드의 title을 '산' 으로 변경될걸 확인

IV. COMMIT 실행

설명) 수정 작업 저장

1) 수정 된 데이터 확인

설명) author 가 '유은정' 인 레코드의 title을 '바다' 가 아닌 '산' 으로 변경 저장된걸 확인