23.05.16 모각코 7회차

  이번 모각코 7회차 모임에서 나는 <데이터 베이스> 과목의 DML : 관계 대수 연산과 CTE에 대해 학습했다.

 

• SQL DML : 관계 대수 연산자

1. 일반 집합 연산자

    ANSI/ISO SQL에서의 구현

        - Union 연산 : UNION / UNION ALL

        - Intersect 연산 : INTERSECTION

        - Difference 연산 : EXCEPT (Oracle은 MINUS)

        - Cartesian (Cross) product 연산 : CROSS JOIN

 

2. 순수 관계 연산자

    ANSI/ISO SQL 에서의 구현

        - Select 연산 : SELECT 문의 WHERE 절로 구현

        - Project 연산 : SELECT 문의 SELECT 절로 구현

        - Join 연산

            WHERE 절 조인 : SELECT 문의 WHERE 절에서 INNER JOIN 기능 제공

            FROM 절 조인 : SELECT 문의 FROM 절에서 다양한 join 기능 제공

                                           (INNER JOIN, NATURAL JOIN, OUTER JOIN, CROSS JOIN)

        - Divide 연산 : 제공하지 않음

 

• SQL DML : 집합 연산자

1. 집합 연산자

    - 목적 : 여러 개의 질의 결과를 하나로 결합함.

        - 서로 다른 테이블에서 얻은 유사한 형태의 결과를 하나로 합칠 때

        - 동일 테이블에서 서로 다른 질의를 수행하여 결과를 합칠 때

 

    - Union compatible의 제약조건 : 합집합, 교집합, 차집합 연산

        - SELECT 절의 칼럼 수가 동일하고

        - SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능해야 함.

           (반드시 동일한 데이터 타입일 필요는 없다. VARCHAR와 CHAR는 데이터 타입은 다르지만 같은 문자형이기 때문에 상호 호환 됨 -> 문제 없음)

 

    - 종류

        - UNION : 중복된 투플은 제거 (UNION ALL : 중복된 투플도 포함)

        - INTERSECT

        - EXCEPT (Oracle은 MINUS)

        - CROSS JOIN : 순수 관계 연산자에서 설명

 

    -> MySQL에서는 INTERSECT와 EXCEPT를 제공하지 않음

        - WHERE 절에서 AND / OR / NOT 그리고 IN / NOT IN과 같은 연산자를 이용해 투플을 필터링하는 투플 조건식을 작성하거나,

        - subquery 등을 사용하여 INTERSECT와 EXCEPT 연산의 효과를 만들 수 있음.

 

    - 일반 형식

   

SELECT			[ALL | DISTINCT] {{컬럼명 [[AS] 컬럼_별칭],} + | *}
FROM			테이블_리스트
[WHERE			투플_조건식]
[GROUP BY		컬럼명 [HAVING 그룹_조건식]]
{UNION [DISTINCT | ALL]} | INTERSECT | EXCEPT
SELECT			[ALL | DISTINCT] {{컬럼명 [[AS] 컬럼_별칭],} + | *}
FROM			테이블_리스트
[WHERE			투플_조건식]
[GROUP BY		컬럼명 [HAVING 그룹_조건식]]
[ORDER BY		{컬럼명 | 컬럼_별칭 | 컬럼_위치 [ASC | DESC],} +]
[LIMIT			[offset, ] row_count];

        - 두 검색문 (SELECT 문)의 결과는 union compatible 해야 함.

        - ORDER BY와 LIMIT는 union한 최종 결과에 대한 정렬 처리이므로, 가장 마지막 줄에 한 번만 기술함.

 

    --> SELECT 절은 ALL이 디폴트 값이나, UNION 절은 DISTINCT가 디폴트 값임

 

 

UNION 연산 (합집합)

    - UNION 연산은 OR 혹은 IN 연산으로 표현 가능

SELECT		TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM		PLAYER
WHERE		TEAM_ID = 'K02'
UNION
SELECT		TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM		PLAYER
WHERE		TEAM_ID = 'K07'
ORDER		BY 선수명;

     - IN 혹은 OR 연산자로 변환 가능

SELECT	TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM	PLAYER
WHERE	TEAM_ID = 'K02' OR TEAM_ID = 'K07'
ORDER	BY 선수명;

SELECT	TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM	PLAYER
WHERE	TEAM_ID IN ('K02', 'K07')
ORDER	BY 선수명;

 

    - UNION과 UNION ALL

        - UNION의 디폴트 값은 DISTINCT이므로 중복을 제거하여 검색한다. 그러나 UNION ALL 키워드를 주면 중복을 제거하지 않고 검색한다.

 

 

INTERSECT 연산 (교집합)

 

    - INTERSECT 연산은 AND 혹은 IN + 서브쿼리(subquery)로 표현 가능

        - MySQL은 INTERSECT 연산을 제공하지 않는다.

        - 소속이 K02 팀이면서 포지션이 GK인 선수들을 검색

SELECT	TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM	PLAYER
WHERE	TEAM_ID = 'K02' AND POSITION = 'GK'
ORDER	BY 1, 2, 3, 4, 5;

SELECT	TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM	PLAYER
WHERE	TEAM_ID = 'K02' AND
		PLAYER_ID IN (	SELECT	PLAYER_ID
					FROM	PLAYER
					WHERE	POSITION = 'GK' )
ORDER	BY 1, 2, 3, 4, 5;

 

 

EXCEPT 연산 (차집합)

 

    - EXCEPT 연산은 AND 혹은 NOT IN + subquery로 표현 가능

        - MySQL은 INTERSECT 연산을 제공하지 않는다.

        - 소속이 K02 팀이면서 포지션이 GK인 선수들을 검색

 

SELECT	TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM	PLAYER
WHERE	TEAM_ID = 'K02' AND POSITION <> 'MF'
ORDER	BY 1, 2, 3, 4, 5

SELECT	TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM	PLAYER
WHERE	TEAM_ID = 'K02' AND
		PLAYER_ID NOT IN (SELECT	PLAYER_ID
				FROM	PLAYER
				WHERE POSITION = 'MF')
ORDER	BY 1, 2, 3, 4, 5