SQL 기본 및 활용 - 2장 정리

🔍 2장 SQL 활용 🔍

---

 

 

 

 

📌순수 관계 연산자

​

SELECT : 릴레이션에 존재하는 튜플들 중 특정 조건 만족하는 경우 튜플들의 부분집합을 구하여 새로운 릴레이션을 만듦.

PROJECT : 주어진 릴레이션에서 속성 리스트에 제시된 속성 값만을 추출. 중복 X

JOIN : 공통 속성을 중심으로 2개의 릴레이션을 하나로 합침.

DIVIDE : X ⊃ Y인 2개의 릴레이션 R(X)와 S(Y)가 있을 때, R의 속성이 S의 속성값을 모두 가진 튜플에서 S가 가진 속성을 제외한 속성만을 추출

---

📌JOIN

​

- INNER JOIN : 교집합

- OUTER JOIN : 조건에 맞지 않는 데이터도 표시. FULL OUTER JOIN 합집합. 중복 데이터 X

👉 LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL OUTER JOIN

- CROSS JOIN​ : 한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인. 카티시안 곱 결과와 같음

​

테이블 사이의 JOIN이 조건이 없는 경우 카티시안 곱(Cartesian Product)이 발생함

​

USING 조건절을 이용한 JOIN에서는 JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없음.

# 일반 JOIN
SELECT A.ID, A.NAME, B.CHECK_YN
FROM TEST_TABLE1 A JOIN TEST_TABLE2 B USING ON A.ID = B.ID

# USING 절 이용
SELECT A.ID, A.NAME, B.CHECK_YN
FROM TEST_TABLE1 A JOIN TEST_TABLE2 B USING (ID)

SELF JOIN - 동일 테이블 사이의 조인. FROM절에 동일 테이블이 두 번 이상나타남. 동일 테이블 사이의 조인을 수행하면 테이블과 컬럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 Alias를 사용해야 함. 하나의 테이블에서 두 개의 컬럼이 연관 관계를 가지고 있는 경우 사용.

 

---

📌일반 집합연산자

집합 연산자	연산자의 의미
UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만듦.
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시. 단순히 결과만 합쳐 놓은 것. 일반적으로 여러 질의결과가 상호 배타적인 경우 많이 사용. 개별 SQL문의 결과가 서로 중복되지 않은 경우 UNION과 결과가 동일함
INTERSECT	여러개의 SQL문의 결과에 대한 교집합. 중복된 행은 하나의 행으로 만듦.
EXCEPT	앞으로 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합. 중복된 행은 하나의 행으로 만듦.(ORACLE에서는 MINUS를 사용)

일반 집합 연산자 SQL과 비교

 

• UNION - UNION
• INTERSECTION - INTERSECT
• DIFFERENCE - EXCEPT(ORACLE은 MINUS)
• PRODUCT - CROSS JOIN

---

ORDER BY

- 보통 ORDER BY 뒤에는 컬럼명이 오는데 숫자가 오는경우

 ORDER BY 1, 2;

숫자는 조회됐을 때 컬럼 순서를 의미함. 해당 컬럼 기준으로 정렬하겠다는 의미.

---

📌계층형 질의

​

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 계층형 질의 사용.

계층형 데이터 : 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터.

​

SQL Server에서 계층형 질의문 CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개함

.

WITH RECURSIVE CTE(ename,empno,mgr,lv) AS (
  SELECT ename, empno, mgr, 1lv FROM emp WHERE mgr IS NULL // 앵커 멤버
  UNION ALL
  SELECT e.ename, e.empno, e.mgr, 1+lv FROM emp e JOIN CTE ON CTE.empno = e.mgr // 재귀 멤버
)
select empno, mgr, ename, lv from CTE
order by lv;

 

SQL Server에서 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행함.

오라클의 계층형 질의문에서 WHERE절은 모든 전개를 진행한 후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 활용함.

계층형 질의 구문

​

START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문. 루트 데이터 지정. LEVEL값은 1

CONNET BY : 다음에 전개될 자식 데이터 지정.

PRIOR : CONNET BY절에 사용되며 현재 읽은 칼럼을 지정.

PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터 방향으로 전개하는 순방향 전개.

PRIOR 부모 = 자식 형태를 사용하면 반대로 자식 데이터에서 부모 데이터 방향으로 전개하는 역방향 전개

ORDER SIBLINGS BY : 형제 노드 사이에서 정렬 수행.

---

 

📌서브쿼리📌

​

반환되는 데이터의 형태에 따른 서브쿼리 분류

서브쿼리 종류	설명
Single Row 서브쿼리 (단일 행 서브쿼리)	- 서브쿼리의 실행 결과가 항상 1건 이하. - 단일 행 비교 연산자와 함께 사용됨. 👉 =, <, <=,>,>=,<>
Multi Row 서브쿼리 (다중 행 서브쿼리)	- 서브쿼리의 실행 결과가 여러 건인 서브쿼리 - 다중 행 비교 연산자와 함께 사용. 👉 IN, ALL, ANY, SOME, EXISTS
Multi Column 서브쿼리 (다중 열 서브쿼리)	- 서브쿼리의 실행 결과로 여러 컬럼을 반환. - 메인 쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있음. - 서브쿼리와 메인쿼리에서 비교하고자 하는컬럼 개수와 컬럼의 위치가 동일해야함. - SQL Server에서는 현재 지원하지 않는 기능

​

서브쿼리 사용시 주의사항

- 서브쿼리를 괄호로 감싸서 사용함

- 서브쿼리는 단일행 또는 복수행 비교연산자와 함께 사용가능함.

- 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야하고 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관없음.

- 서브쿼리에서는 ORDER BY를 사용하지 못함. ORDER BY절은 SELECT에서 오직 하나만 가능. 따라서 메인쿼리의 마지막 문장에 위치해야함.

​

 

스칼라서브쿼리, 인라인뷰, 서브쿼리 차이

● 스칼라 서브쿼리

- SELECT 절 에서 함수처럼 사용되는 쿼리문

- 하나의 레코드만 리턴 가능( 반환값 한개) 두개 이상의 레코드는 리턴 X

- 일치하는 데이터가 없으면 NULL반환 가능.

​

● 인라인뷰

- FROM 절에서 임시 공간에 테이블을 생성하여 사용하는 뷰와 비슷한 저장 형태

- FROM 절에 있는 서브쿼리가 인라인 뷰를 생성, Create 명령어로 만들지 않고 해당 라인에 직접 기술

- 효율적인 검색 기능

- 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있음.

- 인라인 뷰는 SQL문이 실행될 때만 입시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않음.

​

● 서브쿼리 : WHERE 조건 절에서 비교형태로 사용 되는 형태.

---

📌뷰사용의 장점📌

​

- 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도됨

- 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있음. 또한 해당 형태의 SQL문을 자주 사용할때 뷰를 이용하면 편리하게 사용 할 수 있음.

- 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있음.

---

📌CASE WHEN THEN - SQL의 조건문📌

# switch 문 방식
CASE 컬럼명|표현식
  WHEN 조건1 THEN 결과1
  WHEN 조건2 THEN 결과2
  WHEN 조건3 THEN 결과3
  ELSE 그 외 결과
END ;

---

📌GROUP BY

특정 컬럼을 기준으로 집계를 내는데 사용.

- ROLLUP : ROLL UP 계층 구조를 가진 SUB TOTAL을 생성하는 함수. 나열된 컬럼의 순서가 변경되면 수행결과도 변경.

ROLLUP(A,B) => (A,B) / (A,NULL) / (NULL,NULL)

- CUBE : 결합 가능한 모든 값에 대하여 다차원 집계 생성. 정렬 필요한 경우 ORDER BY절에 명시적으로 정렬 컬럼 표시되어야함.

- GROUPING SETS : 다양한 소계 집합을 만들수 있음. ROLLUP과 달리 평등한 관계이므로 파라미터의 순서가 바뀌어도 결과는 같음.

GROUPING SETS함수 역시 정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬 컬럼이 표시되어야함.

- GROUPING : 해당 컬럼의 값이 NULL이면 1, 값이 있으면 0 리턴.

​

ROLLUP( A, B ) == GROUPING SETS( ( A, B ),( A ) , ( ) )

CUBE( A, B ) == GROUPING SETS( ( A ), ( B ), ( A, B ( , ( ) )

 

CUBE , GROUPING SETS, ROLLUP 세가지 그룹 함수 모두 일반 그룹 함수로 동일한 결과 추출 가능

함수들에 의해 집계된 레코드에서 집계 대상 컬럼 이외의 GROUP 대상 컬럼의 값은 NULL을 반환.

​

HAVING 절 - GROUP BY절로 집계된 집계함수를 사용해 조건비교를 하는경우 사용. (WHERE절에서는 집계함수 사용 X)

 

---

📌윈도우함수

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수. 분석함수나 순위 함수로도 알려져있음.

순위함수 (RANK, DENSE_RANK, ROW_NUMBER)

RANK - 동일 값. 동일 순위 부여. 중간 순위 비움

DENSE_RANK - 동일 값 동일 순위 부여, 중간 순위비우지 않음

ROW_NUMBER - 동일 값에 대해서도 유일한 순서 부여

​

윈도우 함수에는 OVER 키워드가 필수적으로 포함됨.

Partition과 Group By 구문은 의미적으로 유사함. Partition 구문이 없으면 전체 집합을 하나의 Partition 으로 정의한 것과 동일

윈도우 함수 적용 범위는 Partition을 넘을 수 없음

​

- PARTITION BY절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있음

- ORDER BY 어떤 항목에 대해 순위를 지정할지에 대해 기술

​

LAG 함수를 이용해 파티션별 윈도우에서 이전 행의 값을 가져올 수 있음. LEAD 함수는 반대로 이후 행의 값을 가져옴.

(SQL Server에서는 지원하지 않는 함수.)

---

📌ROLE

많은 사용자에게 개별적으로 많은 권한을 부여하는 번거로움을 해소하기 위해 다양한 권한을 하나의 그룹으로 묶어놓은 논리적인 권한의 그룹.

GRANT - ROLE의 DBMS USER에게 부여하기위한 명령

REVOKE - ROLE 회수

---

📌PL/SQL특징

​

- BLOCK구조로 되어있어 각 기능별로 모듈화 가능

- 변수, 상수 등을 선언하여 SQL문장 간 값 교환

- IF,LOOP등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록함

- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있음

- Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있음

- 응용 프로그램의 성능을 향상시킴

- 여러 SQL문장을 BLOCK으로 묶고 한 번에 BLOCK전부를 서버로 보내기 때문에 통신량을 줄일 수 있음

​

PL/SQL 에서는 동적 SQL또는 DDL문장을 실행하는 경우 EXECUTE IMMEDIATE를 사용하여야함

---

📌Trigger

데이터베이스에 의해서 자동으로 호출되고 수행됨

- 특정 테이블에 대해서 INSERT, UPDATE, DELETE문이 수행되었을때 호출되도록 정의 할 수 있음

- 데이터 베이스에 로그인하는 작업에도 정의할 수 있음.

​

프로시저와 트리거의 차이점

프로시저	트리거
CREATE Procedure 문법사용	CREATE Trigger 문법사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 O	COMMIT, ROLLBACK 실행 X