Post

[KT AIVLE] SQL 3일차 - 심화학습

Posted
By ZeroMaster
15 min read

0. 개요

오늘은 집계함수, 순위함수와, 데이터 변경, 다중 테이블 조회 방법 마지막으로 뷰(View)에대해서 배워보았습니다.

다양한 집계 함수가 있으며 파이썬과 비교하며 학습 할 수 있었습니다.

그러면 정리 시작하도록 하겠습니다.


1. 명령어 정리

DDL

  • 테이블, 칼럼을 정의하는 명령어(수정, 삭제, 생성)
명령어내용
CREATE테이블 생성
ALTER테이블 구조 수정
DROP테이블 삭제
RENAME테이블 이름 변경
TRUNCATE테이블 초기화

DML

  • 내부 데이터를 조작 관리하는 명령어(조회, 추가, 변경, 삭제)
명령어내용
SELECT데이터 검색
INSERT데이터 추가
UPDATE데이터 수정
DELETE데이터 삭제

DCL

  • 데이터를 제어하는 언어
명령어내용
GRANT권한 부여
REVOKE권한 회수

TCL

  • 트랜잭션 제어하는 언어
명령어내용
COMMIT작업을 확정
ROLLBACK작업을 취소
SAVEPOINT특정 시점으로 되돌리기 설정


2. 데이터 집계

  • 집계 함수
    • SUM: 합계
    • AVG: 평균
    • MAX: 최대값
    • MIN: 최소값
    • COUNT: 개수
    • COUNT(*): 행 수 계산
  • 전체 집계
    • WHERE 조건에 맞는 행 집계 (조건 없으면 전체 집계)
    • 여러 함수(합계, 평균 등) 동시 사용 가능
      • 예: SUM(salary), AVG(salary)
  • 집계 함수와 NULL 값
    • NULL 값은 무시 (SUM, AVG 등 제외)
    • COUNT(*)는 NULL 포함
    • NULL 처리 방법:
      • *COUNT()*로 나눔: SUM(x) / COUNT()
      • NULL → 0 대체: IFNULL(x, 0)
  • GROUP BY

    • GROUP BY 사용:

      • 데이터를 그룹으로 묶어 집계 연산 수행.
      • 예: 지역별 구매 수량 합계, 성별 점수 평균, 부서별 인원 수 등.
  • ORDER BY 사용:
    • 집계 결과를 정렬할 때 사용.
    • 기본적으로 오름차순(ASC), 필요 시 내림차순(DESC) 지정 가능.
  • WHERE 절:
    • GROUP BY 이전의 데이터 필터링.
    • 집계 대상 행을 사전에 걸러냄.
  • HAVING 절:
    • GROUP BY 이후의 데이터 필터링.
    • 집계 결과를 기준으로 조건을 걸러냄.
  • 단순 GROUP BY 정렬
    SELECT dept_id, COUNT(*) AS emp_count
FROM employee
WHERE retire_date IS NULL
GROUP BY dept_id;
  • HAVING 절 활용
    SELECT dept_id, COUNT(*) AS emp_count
FROM employee
WHERE retire_date IS NULL
GROUP BY dept_id
HAVING COUNT(*) >= 3
ORDER BY emp_count DESC;


3. 순위함수

  • 순위 함수는 데이터를 특정 열 기준으로 정렬하고 순위 또는 번호를 부여함.
  • 사용 가능한 주요 순위 함수:
    • RANK()
    • DENSE_RANK()
    • ROW_NUMBER()
    • NTILE(n)
  • 이 함수들은 OVER 절을 사용하여 정렬 기준과 분할 기준을 지정함

1. RANK()

  • 특징: 동일한 값이 있으면 동일한 순위를 부여하며, 이후 순위는 건너뜀
  • 사용법: RANK() OVER (ORDER BY column_name DESC)

  • PARTITION BY 사용 가능: 특정 그룹별 순위를 부여할 수 있음

  • 예시: ```sql SELECT emp_name, salary, RANK() OVER (PARTITION BY gender ORDER BY salary DESC) AS rank FROM employee WHERE retire_date IS NULL;

2. DENSE_RANK()

  • 특징: 동일한 순위에 대해서 건너뛰는 숫자가 없음.

  • 사용법: DENSE_RANK() OVER (ORDER BY column_name DESC)

  • 예시:
    1
2
3
4

    SELECT emp_name, salary, 
      DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employee
WHERE retire_date IS NULL;

3. ROW_NUMBER()

  • 특징: 중복 값과 상관없이 일렬로 번호를 부여함.
  • 사용법: ROW_NUMBER() OVER (ORDER BY column_name)

  • PARTITION BY 사용 가능

  • 예시:
    1
2
3
4

    SELECT emp_name, salary, 
      ROW_NUMBER() OVER (ORDER BY emp_name ASC) AS row_num
FROM employee
WHERE retire_date IS NULL;

4. NTILE(n)

  • 특징: 데이터를 n개의 그룹으로 나누고 그룹 번호를 부여함.

  • 사용법: NTILE(n) OVER (ORDER BY column_name)

  • 예시:
    1
2
3
4

    SELECT emp_name, salary, 
      NTILE(3) OVER (ORDER BY salary DESC) AS group_num
FROM employee
WHERE retire_date IS NULL;

함수별 주요 차이점

| 함수 | 동일 값 처리 방식 | 순위/번호 형식 | |—————|——————————-|——————-| | RANK() | 동일 순위, 다음 순위 건너뜀 | 1, 2, 2, 4, 5 | | DENSE_RANK() | 동일 순위, 다음 순위 연속 | 1, 2, 2, 3, 4 | | ROW_NUMBER() | 중복 허용 없이 번호 부여 | 1, 2, 3, 4, 5 | | NTILE(n) | 그룹 번호 부여 | 1, 1, 2, 2, 3 |


4. 데이터 변경

DML문

개요

  • DML(Data Manipulation Language)은 데이터를 조작하는 언어로 데이터베이스 내용을 변경함.
    • 주요 명령어:
      • INSERT
      • UPDATE
      • DELETE
      • SELECT
  • 주의사항:
    • 잘못된 변경은 복구가 어렵기 때문에 항상 신중해야 함.
    • UPDATE와 DELETE 문에서 정확한 조건을 지정해야 함.
    • 작업 전 SELECT로 대상 데이터를 확인하는 습관을 가져야 함.

** 1. INSERT##

  • 특징
    • 테이블에 데이터를 삽입함.
    • 기본 구조:
      1
2

      INSERT INTO 테이블 (열1, 열2, …)
VALUES (값1, 값2, …);
  • 사용 예시
    • 열 이름 지정:
      1
2

      INSERT INTO department (dept_id, dept_name, unit_id, start_date)
VALUES ('PRD', '상품', 'A', '2022-10-01');
    • 열 이름 생략:
      1
2

      INSERT INTO department
VALUES ('DBA', 'DB관리', 'A', '2022-10-01');
    • 여러 행 삽입:
      1
2
3

      INSERT INTO department
VALUES ('PRD', '상품', 'A', '2022-10-01'),
    ('DBA', 'DB관리', 'A', '2022-10-01');
    • SELECT 결과 삽입:
      1
2

      INSERT INTO retired_employee
SELECT * FROM employee WHERE retire_date IS NOT NULL;

** 2. UPDATE##

  • 특징
    • 데이터 수정 시 사용됨.
    • 기본 구조:
      1
2
3

      UPDATE 테이블
SET 열1 = 값1, 열2 = 값2, …
WHERE 조건;
  • 사용 예시
    • 특정 조건에 맞는 데이터 수정:
      1
2
3

      UPDATE employee
SET phone = '010-1239-1239'
WHERE emp_id = 'S0001';

** 3. DELETE##

  • 특징
    • 데이터를 삭제할 때 사용됨.
    • 기본 구조:
      1
2

      DELETE FROM 테이블
WHERE 조건;
    • 사용 예시
    • 조건에 맞는 데이터 삭제:
      1
2

      DELETE FROM vacation
WHERE end_date <= '2017-12-31';
    • 테이블의 모든 데이터 삭제:
      1
2
3

      DELETE FROM vacation;
-- 또는
TRUNCATE TABLE vacation;


5. 다중 테이블 조회

JOIN의 필요성

  • 관계형 데이터베이스는 데이터를 여러 테이블로 나누어 저장함.
  • 테이블 간의 관계는 Primary Key(PK)와 Foreign Key(FK)를 통해 연결됨.
  • 하나의 테이블에서만 데이터 조회는 제한적이므로, 여러 테이블을 연결해 원하는 데이터를 조회해야 함.
  • 조인문이나 하위 쿼리를 사용하여 여러 테이블에서 데이터를 결함.

ERD를 참고해야 하는 이유

  • 관계 이해
    • ERD는 테이블 간 관계를 시각적으로 표현함.
    • 어떤 테이블이 연결되는지를 확인하여 조인할 테이블을 쉽게 식별할 수 있음.
  • 키 확인
    • 기본 키와 외래 키를 확인하여 조인 조건을 설정함.
  • 데이터 흐름 파악
    • 데이터의 흐름과 의존성을 명확히 보여줌.
  • 복잡성 감소
    • 복잡한 쿼리 구조를 시각적으로 이해하고 오류를 줄일 수 있음.

JOIN 문 작성 3단계

    1. 테이블 연결
      1
2
3

      SELECT emp_id, emp_name, dept_id
FROM employee
JOIN department ON employee.dept_id = department.dept_id;
    1. 테이블 별칭 지정
      1
2
3

      SELECT e.emp_id, e.emp_name, d.dept_name
FROM employee AS e
JOIN department AS d ON e.dept_id = d.dept_id;
    1. 테이블 별칭으로 조회
      1
2
3

      SELECT e.emp_id, e.emp_name, d.dept_name, e.phone
FROM employee AS e
JOIN department AS d ON e.dept_id = d.dept_id;

JOIN의 종류

    1. INNER JOIN
      • 특징: 양쪽 테이블에서 비교되는 값이 일치하는 행만 반환.
      • 구문:
        1
2
3

        SELECT e.emp_id, e.emp_name, v.month
FROM employee AS e
INNER JOIN vacation AS v ON e.emp_id = v.emp_id;
      • 예시: Desktop View
    1. OUTER JOIN
      • 특징: 일치하지 않는 데이터도 포함.
      • 종류: LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN.
      • 구문:
        1
2
3

        SELECT d.dept_name, u.unit_name
FROM department AS d
LEFT OUTER JOIN unit AS u ON d.unit_id = u.unit_id;
      • 예시: Desktop View
    1. CROSS JOIN
      • 특징: 두 테이블의 모든 조합을 생성.
      • 구문:
        1
2
3

        SELECT e.emp_name, v.month
FROM employee AS e
CROSS JOIN vacation AS v;
      • 예시: Desktop View
    1. 여러 테이블 조인
      • 목적: 테이블 간의 관계를 이해하고 데이터를 연결하여 조회.
      • 예시 쿼리:
        1
2
3
4
5
6
7

        SELECT e.emp_id, e.emp_name, d.dept_name, u.unit_name, v.begin_date, v.duration
FROM employee AS e
INNER JOIN department AS d ON e.dept_id = d.dept_id
LEFT OUTER JOIN unit AS u ON d.unit_id = u.unit_id
INNER JOIN vacation AS v ON e.emp_id = v.emp_id
WHERE v.begin_date BETWEEN '2021-01-01' AND '2021-03-31'
ORDER BY e.emp_id ASC;

하위 쿼리 (Sub-query)

  • 특징: 쿼리 안에 또 다른 쿼리를 포함.
  • 장점: JOIN 없이도 복잡한 조건을 간단히 작성 가능.
  • 예시 쿼리:
    • 최고 급여 직원 조회:
      1
2
3

      SELECT emp_id, emp_name, salary
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);
    • EXISTS 사용:
      1
2
3
4
5

      SELECT emp_id, emp_name, email
FROM employee AS e
WHERE EXISTS (
  SELECT * FROM vacation
  WHERE emp_id = e.emp_id
  • 하위 쿼리 사용 예시
    • 가장 먼저 입사한 직원 조회:
      1
2
3

      SELECT emp_id, emp_name, dept_id, phone, email, salary
FROM employee
WHERE hire_date = (SELECT MIN(hire_date) FROM employee);
    • 휴가를 간 적이 있는 정보시스템 부서 직원 조회:
      1
2
3

      SELECT emp_id, emp_name, dept_id, phone, email
FROM employee
WHERE dept_id = 'SYS' AND emp_id IN (SELECT emp_id FROM vacation);
    • 휴가를 간 적이 없는 정보시스템 부서 직원 조회:
      1
2
3

      SELECT emp_id, emp_name, dept_id, phone, email
FROM employee
WHERE dept_id = 'SYS' AND emp_id NOT IN (SELECT emp_id FROM vacation);


6. 뷰(View)

뷰 만들기

뷰(View)는 쿼리문을 기반으로 생성되며, 데이터베이스 내에 저장된 논리적 테이블입니다.

  • 복잡한 쿼리를 단순화하여 재사용할 수 있도록 돕습니다.
  • 뷰 생성 구문:
    1
2
3
4
5

    CREATE VIEW employee_info AS
SELECT e.emp_name, e.emp_id, e.gender, e.dept_id, d.dept_name, e.hire_date
FROM employee AS e
INNER JOIN department AS d ON e.dept_id = d.dept_id
WHERE e.retire_date IS NULL;
  • 뷰 조회:
    1

    SELECT * FROM employee_info;

뷰 변경과 삭제

  • ALTER VIEW로 변경:
    1
2
3

    ALTER VIEW employee_info AS
SELECT e.emp_name, e.emp_id, e.gender, e.dept_name, e.hire_date, e.email, e.phone
FROM employee AS e;
  • CREATE OR REPLACE VIEW로 변경:
    1
2
3

    CREATE OR REPLACE VIEW employee_info AS
SELECT e.emp_name, e.emp_id, e.gender, e.dept_name, e.hire_date, e.email, e.phone
FROM employee AS e;
  • 뷰 삭제:
    1

    DROP VIEW employee_info;

열 별칭 지정

  • AS 사용:
    1
2
3
4
5
6

    CREATE VIEW employee_info AS
SELECT e.emp_name AS 이름, e.emp_id AS 사번, e.gender AS 성별, 
      e.dept_id AS 부서코드, d.dept_name AS 부서이름, e.hire_date AS 입사일
FROM employee AS e
INNER JOIN department AS d ON e.dept_id = d.dept_id
WHERE e.retire_date IS NULL;
  • 뷰 선언 부분에서 지정:
    1
2
3
4
5

    CREATE VIEW employee_info(이름, 사번, 성별, 부서코드, 부서이름, 입사일) AS
SELECT e.emp_name, e.emp_id, e.gender, e.dept_id, d.dept_name, e.hire_date
FROM employee AS e
INNER JOIN department AS d ON e.dept_id = d.dept_id
WHERE e.retire_date IS NULL;

WITH CHECK OPTION(단, 뷰를 사용한 데이터 변경은 권고하지 않음 / 데이터를 보는 용도로만 사용)

  • 기능: 뷰를 통해 변경된 데이터가 뷰 조건을 벗어나지 않도록 강제합니다.
  • 예제:
    1
2
3
4
5
6

    CREATE OR REPLACE VIEW high_salary AS
SELECT e.emp_name, e.emp_id, e.dept_id, e.gender, e.hire_date, e.salary
FROM employee AS e
INNER JOIN department AS d ON e.dept_id = d.dept_id
WHERE e.salary >= 8000
WITH CHECK OPTION;
  • 데이터 변경 시 오류 발생:
    1
2
3
4

    UPDATE high_salary
SET salary = 7000
WHERE emp_id = 'S0002';
-- Error: CHECK OPTION failed


7. 후기

오늘로 3일동안 진행된 SQL이 마무리 되었습니다. 아는내용도 다시 복습하면서 공부 하도록 하겠습니다! 그럼 이만!

KT aivle school, SQL
KT aivle school
This post is licensed under CC BY 4.0 by the author.