패스트캠퍼스 데이터분석 부트캠프 9주차_SQL (2)

서브 쿼리

• 서브 쿼리 특징
  • 하나의 쿼리 내 포함된 또 하나의 쿼리를 의미
  • 서브 쿼리는 반드시 괄호 안에 있어야 함
  • SELECT, FROM, WHERE, HAVING, ORDER BY 절에 사용 가능
  • INSERT, UPDATE, DELETE 문에도 사용 가능
  • 서브쿼리에는 ; 을 붙이지 않아도 됨
• SELECT절의 서브 쿼리
  

  SELECT [컬럼 이름],
  ( SELECT [컬럼 이름]
  FROM [테이블 이름]
  WHERE 조건식 )
  
  FROM [테이블 이름]
  WHERE 조건식;

  • 스칼라 서브쿼리라고도 함
  • SELECT절의 서브 쿼리는 반드시 결과값이 하나의 값

• FROM절의 서브 쿼리
  

  SELECT [컬럼 이름]
  
  FROM ( SELECT [컬럼 이름]
  	FROM [테이블 이름]
  	WHERE 조건식 ) AS [테이블 별명]
  
  WHERE 조건식;

  • 인라인 뷰 서브쿼리라고도 함
  • FROM절의 서브 쿼리는 반드시 결과값이 하나의 테이블
  • 서브 쿼리로 만든 테이블은 반드시 별명을 가져야 함

• WHERE절의 서브 쿼리
  

  SELECT [컬럼 이름]
  
  FROM [테이블 이름]
  
  WHERE [컬럼 이름] [연산자] ( SELECT [컬럼 이름]
  				FROM [테이블 이름]
  				WHERE 조건식 );

  • 중첩 서브쿼리라고도 함
  • WHERE절의 서브 쿼리는 반드시 결과값이 하나의 컬럼 (EXISTS 제외)
  • 연산자와 함께 사용
    ■ ALL
       - < (작다) 와 사용 : [서브 쿼리]의 최솟값보다 작은 지 확인하는 연산자
       - > (크다) 와 사용 : [서브 쿼리]의 최댓값보다 큰 지 확인하는 연산자
    ■  ANY
       - < (작다) 와 사용 : [서브 쿼리]의 최댓값보다 작은 지 확인하는 연산자
       - > (크다) 와 사용 : [서브 쿼리]의 최솟값보다 큰 지 확인하는 연산자

 

 

상관 서브쿼리

 

부모 명령과 자식인 서브쿼리가 특정 관계를 맺는 것을 상관 서브쿼리라 부름 (=서브 쿼리가 외부 쿼리의 변수를 참조)

→ 독립적으로 사용할 수 있는 것은 상관 서브쿼리가 아님

• 예시
  

  # 각 고객에 대해 자신이 결제한 평균 금액보다 큰 결제를 한 경우의 결제 정보
  SELECT P.customer_id, P.amount, P.payment_date
  FROM payment P
  WHERE P.amount > (
  SELECT AVG(amount)
  FROM payment
  WHERE customer_id = P.customer_id	# 외부 쿼리 변수 참조
  );

   

 

집합 연산

• UNION
  

  # 합집합
  SELECT [컬럼 이름] FROM [테이블1 이름]
  UNION
  SELECT [컬럼 이름] FROM [테이블2 이름];

• UNION ALL
  

  # 중복 포함
  SELECT [컬럼 이름] FROM [테이블1 이름]
  UNION ALL
  SELECT [컬럼 이름] FROM [테이블2 이름];

• INTERSECT
  

  # 교집합
  SELECT [컬럼 이름] FROM [테이블1 이름]
  INTERSECT
  SELECT [컬럼 이름] FROM [테이블2 이름];

• EXCEPT
  

  # 차집합
  # 테이블1에는 있지만 테이블2에는 없는 값 반환
  SELECT [컬럼 이름] FROM [테이블1 이름]
  EXCEPT
  SELECT [컬럼 이름] FROM [테이블2 이름];

 

TCL, 트랜잭션 제어

 

트랜잭션은 하나 이상의 SQL 문을 포함하는 작업의 논리적 단위

트랜잭션은 단일 논리적 작업 단위로 수행되는 연산의 순서

논리적 작업 단위는 트랜잭션으로 간주되기 위해 원자성, 일관성, 고립성, 그리고 지속성의 네 가지 특성(ACID)을 가져야한다.

COMMIT과 ROLLBACK은 트랜잭션을 제어하는 SQL 명령어

• COMMIT : 현재 트랜잭션에서 만든 모든 변경 사항을 저장하는 데 사용된다. COMMIT문 바로 다음에
  새 트랜잭션이 시작된다.
  

  START TRANSACTION;	# 트랜잭션의 시작
  UPDATE orders
  SET status = 'Processed'
  WHERE order_id = 101;
  UPDATE orders
  SET status = 'Processed'
  WHERE order_id = 102;
  COMMIT;
  
  ## 마지막 COMMIT 명령 이후에 만든 모든 수정 사항을 저장한다.

• ROLLBACK : 현재 트랜잭션에서 만든 일부 또는 모든 변경 사항을 취소한다. 또한 현재 트랜잭션을 종료
  하며, 새로운 트랜잭션이 시작된다.
  

  START TRANSACTION;
  UPDATE rental
  SET return_date = NOW()
  WHERE rental_id = 3;
  UPDATE rental
  SET return_date = NOW()
  WHERE rental_id = 4;
  ROLLBACK;
  
  ## 두 개의 UPDATE 문은 실행되지만, ROLLBACK 문으로 인해 그 효과는 데이터베이스에 저장되지 않는다.

 

가상 테이블

• VIEW
  • 생성

    CREATE VIEW [가상 테이블 이름] AS
    SELECT [컬럼 이름]
    FROM [테이블 이름]
    WHERE 조건식;

  • 수정

    # 주로 이 코드를 사용
    CREATE OR REPLACE VIEW [가상 테이블 이름] AS
    SELECT [컬럼 이름]
    FROM [테이블 이름]
    WHERE 조건식;

  • 삭제
    

    DROP VIEW [가상 테이블 이름];

• WITH절
  

  WITH [가상 테이블 이름] AS (
  -- SQL 쿼리
  )
  -- CTE를 사용하는 메인 쿼리
  
  # 코드 예시
  WITH FilmInventory AS (
  SELECT DISTINCT film_id FROM inventory
  )
  SELECT f.film_id, f.title
  FROM film f
  JOIN FilmInventory fi ON f.film_id = fi.film_id;

  • VIEW와의 차이점
    ■  WITH절은 실행이 끝나면 가상테이블이 사라짐
    ■  VIEW는 실행이 끝나도 가상테이블이 사라지지 않고, 데이터베이스에 저장되어 있음