You Nicholas

가고 가고 또 가면 알게되고, 행하고 행하고 또 행하게 되면 이루게 된다.

4 분 소요

트랜잭션

  • 간단히 “데이터베이스의 상태를 변화시키는 작업의 모음”
  • 통상적으로 정보의 교환이나 데이터베이스 갱신 등 일련의 작업들에 대한 연속처리단위
  • 데이터베이스의 무결성이 보장되는 상태에서 요청된 작업을 완수하기 위한 작업의 기본 단위

COMMIT

트랜잭션은 확정 신호를 알려줘야 데이터베이스에 반영된다.

  • 예시
    • “A가 B에게 100만원을 입금해야한다” 라는 트랜잭션을 실행한 뒤 확정 신호를 보내야 데이터베이스에 반영이 된다.
    • 확정 신호를 보내지 않는다면 데이터베이스의 내용에 변화가 이루어지지 않는다.

예시에서의 확정 신호를 commit 이라고 한다.

CREATE TABLE user (
	id varchar(10) primary Key,
	name varchar(10)
);

INSERT INTO user VALUES ('id1', 'user1');
INSERT INTO user VALUES ('id2', 'user2');

COMMIT; # Auto-Commit이 해제되어있는 경우

ㅂ

확정 신호를 하지 않았음에도 테이블이 생성되고 데이터가 INSERT, UPDATE, DELETE 되는 것을 확인된다면 AUTO-COMMIT 기능이 켜져있기 때문이다.

ROLLBACK

COMMIT과의 반대되는 개념

앞으로 변경될 작업에 대한 내용을 취소한다는 의미로, 트랜잭션 수행 중에 지금까지 수행한 내용을 모두 취소한다.

INSERT INTO user VALUES ('id3', 'user3');
ROLLBACK; # id3은 데이터베이스에 입력이 안된다.
INSERT INTO user VALUES ('id4', 'user4');
COMMIT;

ACID

ACID (Atomicity, Consistency, Isolation, Durability)

각 단어는 데이터베이스 내에서 일어나는 하나의 트랜잭션 (transaction) 의 안전성을 보장하기 위해 필요한 성질

  1. Atomicity (원자성)
    • 하나의 트랜잭션을 구성하는 작업들은 전부 성공하거나 전부 실패해야 된다. 부분적으로 실행이 되면 안되는 성질
    • 예시
    1. 계좌 A 에서 돈을 꺼낸다.
    2. 계좌 B 에 돈을 넣는다.
    3. ⇒ 1과정만 수행하거나, 2과정만 수행한다면 문제가 발생한다. 돈을 꺼내는 것은 성공하나 돈을 넣는 것은 실패하게 되면서 전부 실패로 돌아가는 것이 Atomicity이다.

Consistency

데이터베이스 상태 일관성에 대한 성질

하나의 트랜잭션 이전과 이후 데이터베이스 상태는 이전과 같이 유호해야 한다는 뜻

데이터베이스의 제약이나 규칙에 의거한 데이터베이스이어야 한다.

  • 예시

      CREATE TABLE user (
  	id varchar(10) primary Key,
  	name varchar(10)
  );
  # 다음과 같은 트랜잭션은 해당 성질을 위반한다.
  1. id가 없는 새로운 고객을 추가하는 쿼리
  2. 기존 user의 value만 삭제하는 쿼리
  # 각 데이터베이스의 유효한 상태는 다를수가 있지만 상태에 대한 일관성을 변하지 않아야 한다.
  # 위 예시에서는 '이름이 있어야 한다' 라는 제약을 위한하고 있다.
  # 따라서 한 트랜잭션 이후에 데이터베이스의 상태가 일관되지 않아진다는 점이다.

Isolation

  1. 고립성 (isolation)
    • 하나의 트랜잭션이 다른 트랜잭션과 독립되어야 한다드 뜻
    • 실제로 동시에 여러 개의 트랜잭션들이 수행될 때에 각 트랜잭션은 고립되어 있어 연속으로 실행된 것과 동일한 결과를 나타낸다.
    • 예시
    1. 해당 계좌로부터 계좌 B 로 육천원의 계좌 이체를 하는 트랜잭션
    2. 해당 계좌로부터 계좌 C 로 육천원의 계좌 이체를 하는 트랜잭션
    3. ⇒ 동시에 하는 경우 연속으로 계좌 B 에 먼저 보낸 뒤 계좌 C 에 보내는 결과와 동일해야 한다는 뜻

Durability

  1. 지속성 (durability)
    • 하나의 트랜잭션이 성공적으로 수행되었다면 해당 트랜잭션에 대한 로그가 남고 런타임 오류나 시스템 오류가 발생해도 해당 기록은 영구적이어야 한다는 뜻
    • 예시

    은행에서 계좌이체를 성공적으로 한 뒤에 해당 은행 데이터베이스에 오류가 발생해 종료가 되어도 계좌이체 내역은 남아야 한다.

SQL More

  • GROUP BY

      SELECT * FROM customers; # customers 테이블로부터 데이터 조회
    
  SELECT State, COUNT(*) # state 기반으로 그룹화
  FROM customers
  GROUP BY State;

  • HAVING

      SELECT State, COUNT(*)
  FROM customers
  GROUP BY State
  HAVING COUNT(*) >= 3 # GROUP BY로 조회된 결과에 대한 필터
  # WHERE와 HAVING의 적용하는 방식 차이
  # HAVING은 그룹화된 결과에 대한 필터
  # WHERE는 그룹화하기 전에 조회되는 필터

  • COUNT()

      SELECT *, COUNT(*) FROM customers # 집계하기, 몇 개인지 값을 리턴한다.
  GROUP BY State;
  # 각 그룹의 첫번째 레코드와 각 그룹에 대한 집계를 리턴한다
    
  SELECT State, COUNT(*) FROM customers
  GROUP BY State;

  • SUM()

      SELECT InvoiceId, SUM(UnitPrice) # 조회된 값들에 대한 합을 구해 리턴한다.
  FROM invoice_items
  GROUP BY InvoiceId;
  # invoice_items 라는 테이블에서 InvoiceId 필드를 기준으로 그룹화 후
  # UnitPrice 필드에 대한 값들에 합을 구한다.

  • AVG()

      SELECT TrackId, AVG(UnitPrice) # 평균값을 구해주는 함수
  FROM invoice_items
  GROUP BY TrackId;
  # 각 TrackId의 평균 UnitPrice를 구하고 있다.

  • MAX(), MIN()

      SELECT CustomerId, MIN(Total) # 최소값을 구하는 함수 MAX는 최대값
  FROM invoices
  GROUP BY CustomerId
  # CustomerId로 그룹화한 뒤에 각 고객의 최소값들을 보여주고 있는 쿼리이다.
  • SELECT 실행 순서
    • FROM
    • WHERE
    • GROUP BY
    • HAVING
    • SELECT
    • ORDER BY
      SELECT CustomoerId, AVG(Total)
  FROM invoices
  WHERE CustomerId >= 10
  GROUP BY CustomerId
  HAVING SUM(Total) >= 30
  ORDER BY 2
    
  # 위의 쿼리문을 시행하게 되었을 때 동작하는 순서
  1. FROM invoices : invoices 테이블에 접근
  2. WHERE CustomerId >= 10 : CustomerId 필드가 10 이상인 레코드들 조회
  3. GROUP BY CustomerId: CustomerId 를 기준으로 그룹화
  4. HAVING SUM(Total) >= 30 : Total 필드들의 값들의 합이 30 이상인 결과 필터
  5. SELECT CustomerId, AVG(Total) : 조회된 결과에서 CustomerId 필드와 Total 필드의 평균값을 가져오기
  6. ORDER BY 2 : AVG(Total) 필드를 기준으로 오룸차순 정렬

  • CASE 사용하기

      SELECT CASE # CASE는 IF문과 같은 기능
  			WHEN CustomerId <= 25 THEN 'GROUP 1'
  			WHEN CustomerId <= 50 THEN 'GROUP 2'
  			ELSE 'GROUP 3'
  		END
  	FROM customers
    
  # GROUP 1, GROUP 2, GROUP3 으로 나누기
  # 25이하인 경우 1, 26-50은 2, 그 외는 3으로 분류

  • SUBQUERY

      SELECT customers.LastName , # 쿼리문을 작성할 때 다른 쿼리문을 포함하는 것
  # 즉, 실행되는 쿼리에 중첩으로 위치해 정보를 전달
  	   (SELECT COUNT(*) FROM invoices WHERE customers.CustomerId = invoices.CustomerId) AS InvoiceCount
  FROM   customers;
    
  # 위의 서브쿼리를 Join으로 변경한 것
  SELECT c.LastName , COUNT(*) AS invoceCount
  FROM customers c
  JOIN invoices i 
  ON   c.CustomerId == i.CustomerId
  GROUP BY c.CustomerId;

  • IN, NOT IN

      SELECT * # IN은 특정한 값들이 서브쿼리에 있는지 확인하는 것
  FROM customers
  WHERE CustomerId IN (SELECT invoices.CustomerId FROM invoices WHERE invoices.Total < 1 );
  • FROM
SELECT *
FROM ( # 조회된 결과를 하나의 테이블이나 조회할 대상으로 지정해 사용
	SELECT CustomerId
	FROM customers
	WHERE CustomerId < 10
	)

참고