데이터베이스 6강 :: 그룹질의, 중첩질의, 조인, 뷰

데이터베이스 6강을 보며 배운내용

 

 

4강, 5강에 이은 SQL 마지막 강의

좀 더 디테일한 데이터 검색을 배운다

궁금했던 JOIN 연산자도 나올 듯 싶다

 

 

정리(TL;DR)

SQL은 생각보다 직관적인 언어로 심지어 재밌었다
백엔드에 조금 더 호감이 생긴 시점

집계함수
SQL 집계함수를 통해 연산을 수행할 수 있다
마치 JavaScript의 Math 함수를 사용하는 느낌

그룹질의
같은 값을 가진 녀석들을 무리짓게 할 수 있는 녀석
집계함수의 COUNT와 함께 자주 사용될 것 같다

중첩질의
SELECT문을 실행하면 테이블을 출력하는데,
이 출력된 테이블을 고대로 FROM이나 WHERE에 사용할 수 있다
복잡한 구성처럼 보이겠지만 그만큼 편리하겠지

조인
내부조인: 조건을 만족하는 레코드만 보여준다
외부조인: 조건에 안맞는 레코드도 보여준다
자연조인: 동일한 컬럼이 있을 때 합치기 좋겠다. 두 테이블 합치는데 중복된 값은 한번만 쓰이니까
셀프조인: 자기 자신과 조인하니 구분하기 위해 별칭 필수(최하단 연습문제 확인)

뷰
원본 테이블 조작보다 안전하고, 관리가 쉬운 가상의 테이블
SELECT로 만든 테이블을
CREATE VIEW를 이용해서 가상의 테이블로 복사하는 느낌

 

 

집계 함수

통계 연산을 수행 가능

 

종류

• COUNT: 컬럼 개수
• SUM: 컬럼 값들 합
• AVG: 컬럽 평균
• MAX: 컬럼 최댓값
• MIN: 컬럼 최솟값

 

예시) 단과대학 몇 개인가?

SELECT COUNT(단과대학)
  FROM 학과

예시) 단과대학 중복없이 몇 개 인가?

SELECT COUNT(DISTINCT 단과대학)
  FROM 학과

예시) '단과대학수' 변경해서 출력(별칭)

SELECT COUNT(DISTINCT 단과대학) AS 단과대학수
  FROM 학과

 

 

그룹 질의

레코드를 그룹화하고 그룹에 대해 함수 적용하는 질의문

SELECT 질의
  GROUP BY 컬럼

SELECT 절에는 그룹에 포함된 컬럼, 그리고 집계 함수만 들어갈 수 있다

 

예시) 소속학과별 교수의 수 출력

SELECT 소속학과, COUNT(*) AS 교수수
  FROM 교수
  GROUP BY 소속학과

 

 

HAVING

집계 결과 레코드에 대한 조건 기술

GROUP BY와 같이 사용

 

WHERE vs HAVING
WHERE절은 레코드에 대한 조건 기술
HAVING절은 집계 결과 레코드에 대한 조건 기술

 

예시) 2개 이상 전공을 신청한 학생의 '학생번호'와 '신청 전공수' 출력

SELECT 학생번호, COUNT(*) as 신청_전공수
  FROM 전공
  GROUP BY 학생번호
  HAVING 신청_전공수 >= 2

 

 

중첩 질의

SELECT문 안에 SELECT문 사용

 

순서

내부 질의 => 외부 질의

순서가 항상 그런건 아니다

 

예시) FROM에 중첩 질의

SELECT 컬럼1, 컬럼2
  FROM(SELECT 컬럼1, 컬럼2
         FROM 테이블
         WHERE 조건)
  WHERE 조건

예시) WHERE에 중첩 질의

SELECT 컬럼1, 컬럼2
  FROM 테이블
  WHERE 컬럼 연산자(SELECT 컬럼 FROM 테이블 WHERE 조건)

예시) 학과별 교수 평균연봉 7000,0000 미만인 학과 중/ 가장 높은 평균 연봉 출력

1. 먼저 소속학과 그룹으로 평균연봉 보여주기

SELECT 소속학과, AVG(연봉) AS 평균연봉
  FROM 교수
  GROUP BY 소속학과

2. 위의 테이블을 테이블로 이용하기

SELECT MAX(d.평균연봉) AS 평균연봉
  FROM (SELECT 소속학과, AVG(연봉) AS 평균연봉
          FROM 교수
          GROUPT BY 소속학과) AS d
  WHERE d.평균연봉 < 70000000

예시) '컴퓨터과학과' 학생 중 수강신청 하지 않은 학생의 학생번호 출력

1. 컴퓨터과학과 학생번호 추출

SELECT 학생번호
  FROM 전공
  WHERE 학과이름 = '컴퓨터과학과'

2. NOT EXISTS 연산자 이용 학생번호가 있는지 확인

SELECT A.학생번호
  FROM 전공 AS A
  WHERE A.학과이름 = '컴퓨터과학과' AND
    NOT EXISTS(SELECT B.학생번호 
                 FROM 수강B
                 WHERE A.학생번호 = B.학생번호)

 

SQL의 백미는 조인이다
by unknown

조인

여러개의 테이블의 결과를 조합해서 하나의 테이블로 보여준다

 

종류

내부조인, 자연조인, 외부조인, 셀프조인

 

1. 내부조인

• 두 개 이상 테이블에서 조인 조건을 만족하는 레코드만 결합해서 출력하는 연산
• 조인 조건은 ON 절에 기록
• ANSI SQL 표준, Oracle사가 제안한 조인 형식 사용

형식

SELECT 컬럼1, 컬럼2
  FROM 테이블1 INNER JOIN 테이블2
  ON 조인조건1
  [선택]WHERE 조건

 

예시) 나이가 30세 이상인 학생의 '학생이름'과 '나이' 그리고 그 학생이 소유한 계좌의 '계좌번호', '잔액'을 출력

(ANSI SQL 표준)

SELECT 학생.학생이름, 학생.나이
       계좌.계좌번호, 계좌.잔액
       FROM 학생 INNER JOIN 계좌
       ON 학생.학생번호 = 계좌.학생번호
       WHERE 학생.나이 >= 30

 

(Oracle 표준)

SELECT 학생.학생이름, 학생.나이
       계좌.계좌번호, 계좌.잔액
       FROM 학생,계좌
       WHERE 학생.학생번호 = 계좌.학생번호
       AND 학생.나이 >= 30

오라클 문법이 더 편해보이네

 

2. 자연 조인

내부 조인과 비슷하다

동일한 이름의 컬럼에 대해 값이 같은 레코드를 결합

 

형식

SELECT 컬럼1, 컬럼2
  FROM 테이블1 NATURAL JOIN 테이블2
  [선택]WHERE 조건

 

3. 외부조인

조인조건에 맞지 않는 레코드도 결과에 포함

값이 없으면 Null로 대체

 

종류

왼쪽 외부 조인(left outer join): 왼쪽의 모든 레코드 포함

오른쪽 외부 조인(right outer join): 오른쪽의 모든 레코드 포함

완전 외부 조인(full outer join): 양쪽 모든 레코드 포함

 

4. 셀프조인

자기 자신과 조인

=> 같은 테이블을 사용하니 별칭이 의무적 사용

 

예시) 과목의 과목코드, 과목명 그리고 그 과목의 선수과목의 과목코드, 과목명 모두 출력

(선수과목이 없는 과목도 결과에 포함0

SELECT B.과목명, B.과목코드
       A.과목명 AS 선수과목명, A.과목코드 as 선수과목코드
   FROM 과목 AS A RIGHT OUTER JOIN 과목 AS B
   ON A.과목코드 = B.선수과목코드

 

 

뷰

뷰란 뭘까?

하나 이상의 테이블에서 유도된 가상의 테이블(Virtual Table)

 

뷰의 작점

• 데이터 독립성
  원본 테이블 구조가 바껴도 뷰를 이용한 작업은
  정의만 변경되고 응용 프로그램에 영향 없다
• 데이터 보안
  뷰를 통해서 테이블 보게해서 특정 컬럼에만 접근 허용할 수 있다
• 사용자 요구에 맞는 테이블 구조 제공
• 복잡한 질의문을 뷰로 단순화
• 데이터 무결성
  뷰 생성시 정한 규칙에 대해서만 수정 가능
  'WITH CHECK OPTION'

 

뷰의 생성

생성되는 뷰의 구조는 SELECT 문 결과로 결정

 

형식

CREATE VIEW 뷰이름 AS
    ( SELECT 컬럼1, 컬럼2
        FROM 테이블
        [선택]WHERE 조건 )
[WITH CHECK OPTION]

 

예시) 컴퓨터과학과 소속 학생정보, 학과이름, 이수학점을 출력하는 '컴퓨터과학과_학생' 뷰를 생성

CREATE VIEW 컴퓨터과학과_학생 AS
    (SELECT 학생.*, 전공.학과이름, 전공.이수학점
       FROM 학생 NATURAL JOIN 전공
       WHERE 전공.학과이름='컴퓨터과학과')

 

 

뷰의 수정

수정은 생성과 동일하게 새로운 SELECT 문 결과로 변경

 

형식

ALTER VIEW 뷰이름(컬럼1, 컬럼2) AS
    (SELECT 컬럼1, 컬럼2
       FROM 테이블
       [선택]WHERE 조건)

 

뷰의 삭제

DROP VIEW 뷰이름

 

 

조인 연습문제

여기 '강의' 테이블이 있다

선수 강의가 있는 강의의 강의이름, 강의번호, 그 강의에 대한 강의이름 출력하라

보아하니 본인을 한번더 불러야 되는 셀프조인을 이용

그리고 해당되는 것만 보여줄거니 외부조인은 아니다

 

테이블부터 만들어보자!

VARCHAR는 가변적으로 데이터 받는다고 하기에

CHAR대신 쫙 깔았다

CREATE TABLE 강의(
  강의번호 VARCHAR(6) NOT NULL,
  강의이름 VARCHAR(20) NOT NULL,
  이수구분 VARCHAR(2) NOT NULL,
  교수번호 VARCHAR(3) NOT NULL,
  선수강의 VARCHAR(6),
  PRIMARY KEY(강의번호)
);

INSERT INTO 강의 (강의번호, 강의이름, 이수구분, 교수번호, 선수강의) VALUES
  ('COM101', '인터넷과정보사회', '교양', 'C01', NULL),
  ('COM301', '데이터베이스', '전공', 'C01', 'COM101'),
  ('COM302', '데이터베이스설계및구현', '전공', 'C02', 'COM301'),
  ('BSN103', '경영학개론', '교양', 'B01', NULL),
  ('BSN302', '인적자원관리', '전공', 'B02', NULL);

 

다음은 SELECT 문

SELECT A.강의번호, A.강의이름, B.강의이름 AS 선수강의이름
  FROM 강의 A INNER JOIN 강의 B
  ON A.선수강의 = B.강의번호

 

직접 해본 결과

=> SQL Fiddle 답안 링크

 

 

참고링크

SQL Fiddle - SQL 온라인 에디터

=> http://sqlfiddle.com/