Post

SQL | Structed Query Language

Posted
By 탱
72 min read

KT 에이블스쿨 6기 SQL에 진행한 강의 내용 정리한 글입니다.

SQL(Structed Query Language)

데이터베이스에서 데이터를 조회하거나 처리(입력, 수정, 삭제)할 때 사용하는 구문

데이터 기본 조회

단순조회

  • SELECT문이 가장 기본이 되면서 가장 많이 사용하는 쿼리문
  • 대부분의 SELECT문은 FROM절을 사용해 테이블에서 데이터를 조회
    • SELECT문에 FROM절 없이도 사용 가능
  • 특정값, 계산식 또는 함수 결과를 조회 가능
  • AS를 사용하여 표시되는 열의 별칭 지정 가능
  • SELECT절에 조회할 열 이름이나 수식 등을 보고자 하는 순서로 나열
  • FROM절에 조회 대상 테이블, 뷰 또는 테이블 형태를 반환하는 함수나 하위 쿼리문을 입력
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 'Hello SQL World' 문자열을 출력
SELECT 'Hello SQL World';

-- 'Hello SQL World' 문자열을 출력하고, 컬럼 이름을 'Start'로 지정
SELECT 'Hello SQL World' AS Start;

-- 10과 20을 더한 결과를 출력하고, 컬럼 이름을 'Result'로 지정
SELECT 10 + 20 AS Result;

-- 현재 날짜를 출력하고, 컬럼 이름을 'Today'로 지정
SELECT CURDATE() AS Today;

-- 현재 날짜를 시스템 변수 @Today에 저장
SET @Today = CURDATE();

-- 시스템 변수 @Today의 값을 출력
SELECT @Today;

조회 대상 열 지정

  • 열 이름 대신 *를 입력하고 조건을 입력하지 않으면 테이블의 모든 행의 모든 열을 조회
  • 모든 열을 의미하는 *는 ALL이라고 읽음
  • 표시되는 열 순서는 테이블을 만들 때 지정한 열 순서와 동일
1
2
3
4

-- employee 테이블의 모든 행과 모든 열을 조회
SELECT * FROM employee;
-- department 테이블의 모든 행과 모든 열을 조회
SELECT * FROM department;
  • 열 이름을 입력하고 조건을 입력하지 않으면 모든 행의 지정한 열을 조회
  • 보고자 하는 순서대로 열 이름을 정확히 입력
  • 원하는 순서로 나열하면 되니 테이블에 정의한 열 순서는 조회에서 의미가 없음
1
2
3
4

-- employee 테이블에서 emp_id와 emp_name 열을 해당 순서대로 조회
SELECT emp_id, emp_name FROM employee;
-- employee 테이블에서 emp_name과 emp_id 열을 해당 순서대로 조회
SELECT emp_name, emp_id FROM employee;

조건 대상 조건 지정

  • 열 이름 대신 *를 입력하고 조건을 입력하면 조건에 맞는 행의 모든 열을 조회
1
2
3
4
5

-- employee 테이블에서 emp_id가 'S0001'인 행의 모든 열을 조회
SELECT * FROM employee WHERE emp_id = 'S0001';
-- vacation 테이블에서 emp_id가 'S0002'인 행의 모든 열을 조회
SELECT * FROM vacation WHERE emp_id = 'S0002';
SELECT * FROM vacation WHERE emp_id = 'S0002';
  • 열 이름을 입력하고 조건을 입력하면 조건에 맞는 행의 지정한 열을 조회
  • 가장 적절한 조회 형태
  • 조건을 잘 못 지정한 경우
    • 오류 대신 의도하지 않는 결과를 얻게 됨
    • 조회된 결과에 대한 정합성을 항상 확인하는 습관 필요
1
2
3
4

-- employee 테이블에서 emp_id가 'S0001'인 행의 emp_id와 emp_name 열을 조회
SELECT emp_id, emp_name FROM employee WHERE emp_id = 'S0001';
-- department 테이블에서 dept_id가 'SYS'인 행의 dept_id와 dept_name 열을 조회
SELECT dept_id, dept_name FROM department WHERE dept_id = 'SYS';

비교연산자

  • 크기를 비교할 때 사용하는 연산자이며 대부분 WHERE절에서 조건을 정의할 때 사용
  • 일반적으로 숫자의 크기를 비교하지만 문자와 날짜도 크기를 비교 가능
  • 같지 않다, 다르다는 <>, != 연산자를 사용
1
2
3
4
5
6
7
8

-- employee 테이블에서 dept_id가 'SYS'인 행의 모든 열을 조회
SELECT * FROM employee WHERE dept_id = 'SYS';
-- employee 테이블에서 salary가 7000보다 큰 행의 모든 열을 조회
SELECT * FROM employee WHERE salary > 7000;
-- employee 테이블에서 hire_date가 '2019-02-28' 이전인 행의 모든 열을 조회
SELECT * FROM employee WHERE hire_date < '2019-02-28';
-- employee 테이블에서 dept_id가 'SYS'가 아닌 행의 모든 열을 조회
SELECT * FROM employee WHERE dept_id <> 'SYS';

문자열 조건 지정

  • 문자열 데이터는 숫자나 날짜 데이터와는 달리 다양한 조건의 검색이 요구
  • LIKE 연산자로 문자 패턴을 비교해 원하는 데이터를 조회 가능
1
2
3
4
5
6
7

-- employee 테이블에서 emp_name에 '국'이 포함된 모든 행의 모든 열을 조회
SELECT * FROM employee WHERE emp_name LIKE '%국%';
-- employee 테이블에서 emp_name이 '국'으로 끝나는 모든 행의 모든 열을 조회
SELECT * FROM employee WHERE emp_name LIKE '%국';
-- employee 테이블에서 email이 4자리 문자로 시작하고 '@'를 포함한 이메일 주소를 가진 모든 행의 모든 열을 조회
-- _하나가 글자 하나를 의미
SELECT * FROM employee WHERE email LIKE '____@%';

논리연산자

  • WHERE 절에서 여러 개의 조건을 연결하는 연산자
  • AND 연산자는 모든 조건을 만족하는 행을 반환
    • 조건을 만족할수록 결과 행의 수 감소
    • 성능 향상 가능성 존재
  • OR 연산자는 하나 이상의 조건을 만족하는 행을 반환
    • 조건을 만족할수록 결과 행의 수 증가
    • 성능 저하 가능성 존재
  • NOT 연산자는 특정 조건에 해당하지 않는 데이터를 필터링
    • NOT LIKE, NOT IN, NOT BETWEEN, IS NOT NULL 등
  • 여러 조건을 나열할 경우에는 괄호를 사용해 조건을 명확히 식별할 수 있도록 해야 함
1
2
3
4
5
6

-- employee 테이블에서 salary가 5000보다 크고 hire_date가 '2020-12-31' 이전인 행의 모든 열을 조회
SELECT * FROM employee WHERE salary > 5000 AND hire_date <= '2020-12-31';
-- employee 테이블에서 dept_id가 'SYS' 또는 'MKT'인 행의 모든 열을 조회
SELECT * FROM employee WHERE dept_id = 'SYS' OR dept_id = 'MKT';
-- employee 테이블에서 phone 값이 '010'으로 시작하지 않는 행의 모든 열을 조회
SELECT * FROM employee WHERE phone NOT LIKE '010%';

범위 조건과 리스트 조건

  • WHERE 절에 조건을 간결하게 입력할 수 있는 방법
    • 가독성 향상
    • 조건에 이름을 반복해서 입력하는 번거로움 해결
1
2
3
4

-- employee 테이블에서 salary가 5000 이상 8000 이하인 행의 모든 열을 조회
SELECT * FROM employee WHERE salary BETWEEN 5000 AND 8000;
-- employee 테이블에서 dept_id가 'SYS', 'MKT', 'HRD' 중 하나인 행의 모든 열을 조회
SELECT * FROM employee WHERE dept_id IN ('SYS', 'MKT', 'HRD');
  • NOT 연산자를 사용해 조건에 대한 부정을 쉽게 지정 가능
1
2
3
4

-- employee 테이블에서 salary가 5000 미만 또는 8000 초과인 행의 모든 열을 조회
SELECT * FROM employee WHERE salary NOT BETWEEN 5000 AND 8000;
-- employee 테이블에서 dept_id가 'SYS', 'MKT', 'HRD'가 아닌 행의 모든 열을 조회
SELECT * FROM employee WHERE dept_id NOT IN ('SYS', 'MKT', 'HRD');

NULL 값 비교

  • NULL 값은 0도 아니고 공백도 아닌 알 수 없는 값
    • Unknown Value
  • NULL값과 문자열 ‘NULL’은 완전히 다름
  • IS NULL, IS NOT NULL을 사용해 NULL 값을 식별 가능
1
2
3
4
5
6
7
8
9
10
11

-- employee 테이블에서 eng_name이 문자열 'NULL'인 행의 모든 열을 조회
SELECT * FROM employee WHERE eng_name = 'NULL';

-- employee 테이블에서 eng_name이 NULL인 행의 모든 열을 조회
SELECT * FROM employee WHERE eng_name IS NULL;

-- employee 테이블에서 retire_date가 NULL인 행의 모든 열을 조회
SELECT * FROM employee WHERE retire_date IS NULL;

-- employee 테이블에서 retire_date가 NULL이 아닌 행의 모든 열을 조회
SELECT * FROM employee WHERE retire_date IS NOT NULL;

IFNULL함수

  • IFNULL()함수를 사용해 NULL값 대신 다른 값을 표시 가능
  • DBMS마다 다른 함수 사용
    • MySQL: IFNULL()
    • MSSQL: ISNULL()
    • ORACLE: NVL()
  • IFNULL() 함수 대신 표준 함수인 COALESCE() 함수를 사용하기를 권고
1
2
3
4
5
6
7
8
9
10

-- employee 테이블에서 retire_date가 NULL인 행 중 emp_name, emp_id, eng_name(Null일 경우 빈 문자열로 대체) 등을 조회
SELECT emp_name, emp_id, IFNULL(eng_name, '') AS nick_name,
 gender, dept_id, hire_date
 FROM employee
 WHERE retire_date IS NULL;
-- COALESCE() 함수 사용
SELECT emp_name, emp_id, COALESCE(eng_name, '') AS nick_name,
 gender, dept_id, hire_date
 FROM employee
 WHERE retire_date IS NULL;

자동 형 변환

  • 문자 데이터가 연산에 사용되면 자동으로 숫자로 변환(자동 형 변환)
  • 숫자로 변환되지 못하는 문자는 0으로 변환
1
2
3
4

SELECT '10' + '20'; -- 30
SELECT 10 + '20'; -- 30
SELECT 10 + '20AX'; -- 30
SELECT 10 + 'LX20'; -- 10
  • 문자열 데이터를 + 연산자로 결합 불가능
    • CONCAT 함수를 사용하여 결합
  • 자동 형 변환은 DBMS에 따라 다르게 수행됨을 유의
    • MS SQL은 +를 사용해 문자열 연결이 가능
    • ORACLE은 ||를 사용해 문자열 연결이 가능
1
2
3

SELECT CONCAT ('10', '20'); -- 1020
SELECT CONCAT (10, '20'); -- 1020
SELECT CONCAT (10, 20); -- 1020

데이터 결합

  • CONCAT() 함수를 사용해 데이터 결합
  • 결합되는 값에 NULL값이 포함되면 결합 결과가 NULL이 됨
1
2
3
4
5

-- 'emp_name'과 'emp_id'를 결합하여 새로운 열 'emp_name'을 생성
-- 결합된 값은 'emp_name'과 'emp_id'를 괄호로 묶은 형태로 표시됨
SELECT CONCAT(emp_name, '(', emp_id, ')') AS emp_name,
       dept_id, gender, hire_date, email
FROM employee;

데이터 정렬

  • ORDER BY절을 사용해 정렬된 결과를 표시 가능
    • ASC: 오름차순
      • 기본값으로 정렬 방식이 지정되지 않으면 오름차순으로 정렬
    • DESC: 내림차순
  • 숫자, 날짜형 데이터 정렬이 가능
  • 복합 정렬은 ,로 구분해서 정렬 방식을 지정
1
2
3
4
5
6

-- 'employee' 테이블에서 모든 열을 조회하고, 'salary' 열을 기준으로 내림차순 정렬
SELECT * FROM employee ORDER BY salary DESC;

-- 'employee' 테이블에서 모든 열을 조회하고, 먼저 'dept_id'를 기준으로 오름차순 정렬
-- 만약 'dept_id'가 동일한 값이 있으면, 그 안에서 'emp_id'를 기준으로 내림차순 정렬
SELECT * FROM employee ORDER BY dept_id ASC, emp_id DESC;

CASE문

  • CASE문을 사용하면 쿼리문 안에서 조건에 따른 처리(값 연산, 변환 등) 수행 가능
1
2
3
4
5
6
7

-- 'employee' 테이블에서 'emp_name', 'emp_id', 'gender', 'hire_date', 'retire_date', 'salary' 열을 조회
-- gender 컬럼의 값을 'M'일 경우 '남자', 'F'일 경우 '여자'로 변환하고, 그 외의 경우에는 빈 문자열을 반환
SELECT emp_name, emp_id,
 CASE WHEN gender = 'M' THEN '남자'
 WHEN gender = 'F' THEN '여자'
 ELSE '' END AS gender, hire_date, retire_date, salary
FROM employee;

IF 함수

  • 조건이 참이면 값1, 거짓이면 값2 선택
  • CASE문처럼 여러 조건에 따라 분기하여 처리하려면 IF함수를 중첩해서 사용
    • 여러 조건에 따른 처리는 CASE문, 단 한 번의 조건 비교로 참과 거짓에 따른 처리는 IF 함수 사용
1
2
3
4
5
6

-- 'employee' 테이블에서 'emp_name', 'emp_id', 'gender', 'hire_date' 컬럼을 조회
-- gender가 'M'이면 '남자', 'F'이면 '여자', 그 외에는 빈 문자열을 반환
SELECT emp_name, emp_id,
       IF(gender = 'M', '남자', IF(gender = 'F', '여자', '')) AS gender,
       hire_date
FROM employee;

데이터베이스 모델링을 위한 요구 분석

요구 분석 중요성

  • 효율적인 데이터베이스 설계
    • 정확한 요구 분석은 데이터베이스의 구조를 최적화하여 성능을 향상
  • 비즈니스 요구 충족
    • 비즈니스 요구사항을 충족시키는 데이터베이스 시스템을 구현 가능
  • 비용 절감
    • 초기 단계에서 요구사항을 명확히 함으로써 설계 변경으로 인한 추가 비용 감소 가능
  • 프로젝트 성공률 증가
    • 명확하고 구체적인 요구사항은 프로젝트의 성공 확률 증가

요구 분석 단계

  1. 요구사항 수집
    • 인터뷰: 사용자, 관리자를 포함한 이해관계자들과의 인터뷰를 통해 요구사항 수집
    • 설문조사: 설문지를 통해 조직의 데이터 요구사항을 정량적으로 수집
    • 워크샵 및 브레인스토밍: 이해관계자들이 한자리에 모여 아이디어를 공유하고 요구사항 도출
    • 문서검토: 기존의 문서, 보고서, 시스템 매뉴얼 등을 검토하여 요구사항 파악
  2. 요구사항 분석
    • 요구사항 분류: 수집된 요구사항을 기능적 요구사항과 비기능적 요구사항으로 분류
    • 요구사항 명세화: 요구사항을 명확하고 상세하게 문서화
    • 우선순위 설정: 요구사항의 중요도와 긴급도에 따라 우선순위를 설정
  3. 요구사항 검증
    • 요구사항 검토: 이해관계자들과 함께 요구사항을 검토하여 정확성과 완전성을 확인
    • 프로토타이핑: 시뮬레이션이나 프로토타입을 만들어 요구사항
    • 피드백 수집: 이해관계자들로부터 피드백을 받아 요구사항을 수정 및 보완

요구 분석 시 고려사항

  • 명확성(Clarity)
    • 요구사항은 이해하기 쉽게 명확하게 기술
    • 애매모호한 표현은 피하고 구체적이고 측정 가능한 표현 사용
  • 일관성(Consistency)
    • 요구사항 간에 모순이 없어야 하며 서로 충돌하지 않도록 작성
    • 시스템의 다른 부분들과 일관성을 유지
  • 완전성(Completeness)
    • 요구사항은 시스템의 모든 측면을 포괄해야 하며 특정 기능이나 요구사항이 빠지지 않도록 작성
    • 누락된 부분이 없이 모든 관련 사항이 포함되어야 함
  • 추적 가능성(Traceability)
    • 각 요구사항은 그 출처를 명확히 할 수 있어야 함
    • 시스템 설계 및 구현 단계에서 추적할 수 있도록 작성
  • 검증 가능성(Verifiability)
    • 요구사항은 검증 가능해야 함
    • 테스트나 검토를 통해 확인 가능

데이터베이스 모델링

데이터베이스 모델링

개념적 데이터 모델링

  • 비즈니스 요구사항을 반영한 고수준의 데이터 모델로 주로 ERD(Entity-Relationship Diagram)를 사용하여 표현
  • 데이터베이스의 물리적 구현에 독립적인 모델로, 사용자가 이해하기 쉬운 방식으로 데이터를 표현
  • 데이터의 구조와 관계를 시각적으로 표현하며 데이터베이스 설계의 기초를 마련하고 시스템 요구사항을 이해 및 반영

개념적 모델링 주요 구성 요소

  • 엔티티(Entity)
    • 실세계의 객체나 개념을 나타내며 데이터베이스에 저장되는 주요 객체(ex. 고객, 주문, 제품)
  • 속성(Attribute)
    • 엔티티의 특성을 나타내는 데이터 요소 (ex. 고객의 이름, 주소, 전화번호)
    • 단순 속성: 더 이상 분해할 수 없는 속성 (ex. 이름)
    • 복합 속성: 여러 하위 속성으로 구성된 속성 (ex. 주소)
    • 다중 값 속성: 여러 값을 가질 수 있는 속성 (ex. 전화번호)
    • 유도 속성: 다른 속성의 값에 의해 계산된 속성 (ex. 총금액)
  • 관계(Relationship)
    • 엔티티 간 연결성 (ex. 고객이 주문을 한다, 주문이 제품을 포함한다)
    • 1:1 관계: 한 엔티티가 다른 엔티티 하나의 연결성 (ex. 한 사람이 하나의 여권)
    • 1:N 관계: 한 엔티티가 여러 엔티티와 연결성 (ex. 한 고객이 여러 주문)
    • N:M 관계: 여러 엔티티가 다른 엔티티와 연결성 (ex. 여러 학생이 여러 강의)
    • 필수(Mandatory): 모든 엔티티 간 관계에 참여해야 함 (ex. 모든 주문은 반드시 고객에 의해 만들어져야 함)
    • 선택적(Optional): 일부 엔티티는 관계에 참여하지 않아도 됨 (ex. 고객은 주문을 할 수도 있고 하지 않을 수도 있음)
  • 식별자(Identifier)
    • 엔티티를 구별하게 실행하는 하나 또는 그 이상의 속성 (ex. 고객 엔티티의 고객 ID, 주문 엔티티의 주문 ID)
  • 식별자 종류
    • 기본 식별자 (Primary Identifier): 각 인스턴스를 구별하게 실행하는 데 사용되는 주요 속성 (ex. 고객 엔티티의 고객 ID, 주문 엔티티 주문 ID)
    • 대체 식별자 (Alternative Identifier): 기본 식별자를 대신하여 각 인스턴스를 구별하게 실행할 수 있는 속성 (ex. 이메일 주소가 고객 엔티티 데이터의 식별자로 사용)
    • 복합 식별자 (Composite Identifier): 두 이상의 속성을 조합하여 각 인스턴스를 구별하게 실행하는 속성 (ex. 고객 이름과 고객 주소를 조합하여 식별)
    • 자연 식별자 (Natural Identifier): 현실 세계에서 고유한 값을 갖는 속성 (ex. 주민등록번호, ISBN 등)
    • 인조 식별자(Surrogate Identifier): 시스템에 의해 생성된 고유값을 식별자로 사용하는 경우 (ex. 자동 증가 숫자, GUID)

개념적 데이터 모델링 단계

  1. 요구사항 분석
    • 시스템의 요구사항을 수집하고 분석하여 데이터 모델링의 기초 자료를 마련
    • 이해관계자 인터뷰, 설문조사 등을 통해 데이터 요구사항을 수집
  2. 엔티티 도출
    • 비즈니스 영역에서 필요한 주요 엔티티를 식별하고 엔티티 후보 목록을 작성한 후 검토 및 확정
  3. 속성 정의
    • 각 엔티티에 필요한 속성을 정의하며 엔티티 속성 목록을 작성하고 속성의 데이터 유형을 결정
  4. 관계 설정
    • 엔티티간의 관계를 정의하고 관계의 유형(일대일, 일대다, 다대다)을 명확히 설정
    • 관계의 기수성(Cardinality) 및 선택성(Optionality)을 결정
  5. ER 다이어그램 작성
    • 엔티티, 속성, 관계를 포함하는 ERD를 작성하여 주요 엔티티 및 관계를 시각적으로 표현
      • 엔티티(Entity): 사각형으로 표현
      • 속성(Attribute): 타원형으로 표현
      • 관계(Relationship): 마름모로 표현
      • 관계선: 선으로 표현
      • 식별자: 밑줄로 표현
  6. 검증 및 피드백
    • 작성된 모델을 검토하고 이해관계자들로부터 피드백을 받아 수정하면서 모델의 일관성, 완전성, 정확성을 검토

개념적 모델링 장점

  • 이해용이성
    • 기술적인 세부사항 없이 데이터 구조를 시각적으로 표현하여 기술적 배경이 없는 사용자도 쉽게 이해 가능
    • 엔티티, 속성, 관계 등을 다이어그램으로 표현하여 데이터 구조를 직관적으로 파악 가능
  • 의사소통의 원활화
    • 개발자, 데이터베이스 설계자, 비즈니스 애널리스트, 사용자 등 다양한 이해관계자가 공통된 이해를 가질 수 있음
    • 비즈니스 요구사항을 명확하게 모델링하여 데이터베이스 설계에 정확하게 반영 가능
  • 유연성 및 확장성
    • 데이터베이스 구현 전에 모델을 수정하거나 확장하기가 상대적으로 쉬움
    • 새로운 엔티티나 속성, 관계를 추가하기가 용이하며 기존 모델을 쉽게 확장할 수 있음
  • 데이터 품질 향상
    • 개념적 데이터 모델링을 통해 데이터의 정확성과 일관성 향상
    • 비즈니스 규칙과 제약 조건을 데이터 모델에 포함시켜 데이터의 품질 향상
  • 재사용성
    • 동일하거나 유사한 데이터 구조가 필요한 다른 프로젝트나 시스템에서 개념적 데이터 모델을 재사용 가능

논리적 데이터 모델링

  • 개념적 데이터 모델을 바탕으로 데이터베이스 시스템의 논리적 구조를 정의하는 과정
  • 데이터베이스 시스템의 논리적 구조에는 테이블, 열, 데이터 유형, 관계, 제약 조건 등을 포함
  • 데이터의 정확성, 일관성, 무결성을 보장하는 논리적 구조를 설계하여 데이터베이스 시스템의 효율적인 구현을 지원

논리적 모델링 주요 구성 요소

  • 엔티티(Entity)
    • 테이블로 변환되며 데이터베이스에서 저장되는 주요 객체를 나타냄
  • 속성(Attribute)
    • 열로 변환되며 테이블 내에서 저장되는 데이터 요소를 나타냄
  • 관계(Relationship)
    • 외래키로 변환되며 테이블 간의 연관성을 정의
  • 식별자(Identifier)
    • 기본키로 변환되며 테이블 내에서 각 행을 식별하는데 사용
  • 제약조건(Constraints)
    • 데이터의 정확성과 무결성을 유지하기 위한 규칙을 정의

논리적 데이터 모델링 단계

  1. 개념적 모델 검토
    • 개념적 데이터 모델에서 도출된 엔티티, 속성, 관계를 검토
  2. 엔티티를 테이블로 변환
    • 각 엔티티를 데이터베이스 테이블로 변환
  3. 속성을 열로 변환
    • 각 속성을 테이블의 열로 변환하고 데이터 유형을 정의
  4. 식별자 정의
    • 각 테이블의 기본키를 정의하여 테이블 내 각 행을 고유하게 식별
  5. 관계 설정
    • 테이블 간의 외래키를 설정하여 관계를 정의
  6. 제약 조건 정의
    • 데이터 무결성을 유지하기 위해 제약 조건을 정의
    • 고유성 제약, 무결성 제약, 참조 무결성 제약 등
  7. 정규화(Normalization)
    • 데이터를 중복없이 효율적으로 저장하기 위해 정규화를 수행
    • 정규화는 데이터베이스 설계의 품질을 높이고 데이터의 일관성을 유지

논리적 모델링의 장점

  • 데이터 정확성 및 무결성 보장
    • 제약 조건과 키를 통해 데이터의 정확성과 무결성을 유지할 수 있음
  • 효율적인 데이터 저장
    • 정규화를 통해 데이터 중복을 최소화하고 저장공간을 효율적으로 사용
  • 데이터베이스 구현의 기초 제공
    • 논리적 데이터 모델은 물리적 데이터 모델링과 데이터베이스 구현의 기초가 됨
  • 비즈니스 규칙 반영
    • 데이터베이스 설계에 비즈니스 규칙을 명확히 반영하여 시스템 요구사항을 충족

정규화(Normalization) 필요성

  • 데이터 중복 감소
    • 데이터가 여러 테이블에 분산되어 저장됨으로써 중복되는 데이터를 최소화함
      • 저장 공간 절약, 데이터 일관성 유지
  • 데이터 무결성 유지
    • 데이터의 일관성과 정확성 보장
    • 중복 데이터가 줄어들어 데이터의 수정, 삭제, 삽입 시 무결성이 유지
  • 데이터 이상(Anomaly) 방지
    • 추가 이상(Insertion Anomaly): 데이터 삽입 시 불필요한 중복 데이터를 피할 수 있음
    • 변경 이상(Update Anomaly): 데이터 갱신 시 모든 중복 데이터를 일관되게 수정할 수 있음
    • 삭제 이상(Deletion Anomaly): 데이터 삭제 시 중요한 정보가 의도치 않게 손실되지 않음
  • 데이터베이스 설계의 효율성
    • 쿼리 성능을 향상시키고 데이터 검색 및 조작을 더 효율적으로 수행 가능
    • 각 테이블이 명확하게 정의되고 특정한 역할을 수행하도록 함으로써 데이터베이스 설계를 쉽게 이해 가능
  • 데이터베이스 유지보수 용이성
    • 데이터베이스 구조가 명확하여 유지보수가 용이해짐
    • 새로운 요구사항에 따른 구조 변경도 쉽게 할 수 있음
  • 확장성 및 유연성
    • 새로운 데이터 요구사항이 발생할 때 쉽게 확장 가능
    • 새로운 엔티티나 속성을 추가하기에 용이함

1차 정규형(1NF)

  • 테이블이 정규화의 첫번째 단계에 부합하는 것을 의미하며 다음과 같은 조건을 충족해야 함
    • 원자성(Atomicity): 모든 속성 값이 더 이상 분해할 수 없는 원자값이어야 함
    • 중복 행 제거: 모든 행이 고유해야 함
    • 컬럼 순서가 무의미: 컬럼의 순서가 데이터베이스 설계에 영향을 주지 않아야 함

2차 정규형(2NF)

  • 1차 정규형을 만족하고 기본키가 아닌 모든 속성이 기본키의 모든 부분 집합에 완전히 종속되어야 함
    • 부분적 종속성 제거: 기본키의 일부분에만 종속적인 속성을 제거
    • 2NF는 복합 키를 가진 테이블에서만 의미가 있음

3차 정규형(3NF)

  • 2차 정규형을 만족하고 비 기본키 속성이 아닌 다른 비 기본키 속성에 이행적 종속을 갖지 않아야 함
    • 이행적 종속성 제거: 비 기본키 속성이 다른 비 기본키 속성에 종속되지 않도록 함

물리적 데이터 모델링

  • 논리적 데이터 모델을 기반으로 실제 데이터베이스 시스템에 맞게 물리적 구조를 설계하는 과정
  • 물리적 구조에는 데이터베이스 테이블, 인덱스, 파티션, 저장소 매개변수 등을 포함
  • 데이터베이스의 성능, 보안, 저장효율성을 최적화하여 데이터의 효율적인 저장과 접근을 보장

물리적 모델링 주요 구성 요소

  • 테이블(Table)
    • 데이터를 저장하는 기본 단위로 논리적 모델의 엔티티가 물리적 모델의 테이블로 변환됨
  • 열(Column)
    • 테이블 내의 데이터 항목을 정의하며 논리적 모델의 속성이 물리적 모델의 열로 변환됨
  • 데이터 유형(Data Type)
    • 각 열의 데이터 형식을 정의함 (ex. 정수형, 문자형, 날짜형 등)
  • 인덱스(Index)
    • 데이터 검색을 빠르게 하기 위한 구조이며 특정 열에 대해 인덱스를 생성하여 검색 성능을 향상시킴
  • 키(Keys)
    • 기본키(Primary Key), 외래키(Foreign Key)등 데이터의 무결성을 유지하기 위한 제약조건을 정의
  • 뷰(View)
    • 하나 이상의 테이블에서 데이터를 가져와 가상 테이블을 생성함
  • 저장소 매개변수
    • 테이블 및 인덱스의 저장소 특성을 정의함 (ex. 테이블 스페이스, 블록 크기 등)

물리적 모델링 단계

  1. 논리적 모델 검토
    • 논리적 데이터 모델에서 도출된 테이블, 열, 관계를 검토
  2. 테이블 설계
    • 논리적 모델의 엔티티를 물리적 모델의 테이블로 변환하고 각 테이블의 열과 데이터 유형을 정의
  3. 키 및 제약 조건 정의
    • 각 테이블의 기본키와 외래키를 정의하고 데이터 무결성을 유지하기 위한 제약 조건을 설정
  4. 인덱스 설계
    • 검색 성능을 최적화하기 위해 인덱스를 설계
  5. 뷰 설계
    • 필요한 경우 데이터 접근을 용이하게 하기 위해 뷰를 생성
  6. 저장소 매개변수 설정
    • 데이터베이스의 성능과 저장 효율성을 최적화하기 위해 저장소 매개변수를 설정
  7. 보안 설정
    • 데이터 접근 권한 및 보안 규칙을 정의

물리적 데이터 모델링 고려사항

  • 성능 최적화
    • 데이터베이스의 성능을 향상시키기 위해 인덱스, 파티션, 클러스터링 등을 고려
  • 저장 효율성
    • 저장 공간을 효율적으로 사용하기 위해 데이터 압축, 테이블 스페이스 관리 등을 고려
  • 보안
    • 데이터 접근 권한을 설정하고 민감한 데이터를 보호하기 위한 보안 조치를 취함
  • 백업 및 복구
    • 데이터 손실에 대배해 백업 전략과 복구 절차를 마련
  • 확장성
    • 데이터베이스가 증가하는 데이터와 사용자 수를 처리할 수 있도록 확장 가능성을 고려

역정규화(Denormalization)

정규화된 데이터베이스를 의도적으로 비정규화하여 데이터 중복을 허용하는 방식으로 데이터베이스 성능을 향상시키는 과정
데이터 액세스 속도를 높이고, 복잡한 쿼리의 응답 시간을 줄이기 위해 데이터 중복과 저장 공간의 증가를 허용함

  • 역정규화 필요성
    • 읽기 작업이 많은 데이터베이스에서 쿼리 성능을 최적화하기 위해 사용
    • 복잡한 조인(Join) 연산을 줄이고, 단일 테이블에서 데이터를 빠르게 조회할 수 있도록 함
    • 데이터를 중복 저장하여 복잡한 쿼리 구조를 단순화
  • 역정규화 장점
    • 데이터를 중복 저장함으로써 읽기 성능이 향상됨
    • 조인 연산을 줄이고, 데이터 조회 속도를 높임
    • 잡한 쿼리를 단순화하여 유지보수와 관리가 용이함
  • 역정규화 단점
    • 데이터 중복으로 인해 저장 공간이 증가
    • 데이터 중복으로 인해 데이터 일관성을 유지하기 어려워질 수 있음
    • 데이터 중복으로 인해 추가, 수정, 삭제 작업이 복잡해지고, 데이터 일관성을 유지하기 위한 추가 작업이 필요

역정규화 기법

  • 중복 컬럼 추가
    • 다른 테이블에 있는 속성을 중복 저장하여 조인 없이 조회할 수 있도록 함
  • 중복 테이블 생성
    • 데이터 조회 성능을 높이기 위해 자주 사용되는 데이터를 별도의 테이블에 중복 저장
  • 집계 테이블 사용
    • 자주 사용되는 집계 결과를 미리 계산하여 저장하는 테이블을 생성
  • 파티셔닝(Partitioning)
    • 큰 테이블을 여러 작은 테이블로 분할하여 성능을 최적화
  • 인덱스 추가
    • 특정 열에 대한 인덱스를 추가하여 데이터 조회 성능을 향상

테이블 디자인 시 고려사항

  • 데이터 유형 선정
    • 각 열의 데이터 유형을 신중하게 선택하여 저장공간을 최적화하고 성능을 향상시킴
    • 불필요하게 큰 데이터 유형을 사용하지 않도록 주의
  • 기본키 설정
    • 기본키는 각 행을 고유하게 식별할 수 있도록 설정 필요
    • 기본키는 가능한 한 단순하고 변경되지 않는 값을 사용함
      • 자동 증가열을 사용하면 편리
  • 외래키 설정
    • 외래키를 사용하여 테이블 간의 관계를 정의하고 참조 무결성을 유지
    • 부모-자식 관계를 명확히 정의하여 데이터 무결성을 유지
  • 인덱스 설계
    • 자주 조회되는 열에 대해 인덱스를 생성하여 검색 성능을 향상시킴
    • 인덱스는 검색 성능을 높이는 반면, 삽입과 갱신 성능에 영향을 줄 수 있으므로 적절하게 균형을 맞춤
    • 다중 열 인덱스는 여러 열을 동시에 검색할 때 유용함
  • 데이터 무결성 제약 조건
    • 필수 입력 항목은 NULL을 허용하지 않도록 설정
    • 중복을 허용하지 않아야 하는 열에 대해 UNIQUE 제약 조건을 설정
    • 열 값의 범위나 조건을 지정하기 위해 CHECK 제약 조건을 사용함
    • 특정 열에 대해 기본값을 설정하여 데이터 입력 오류를 줄임
  • 정규화와 역정규화
    • 데이터 중복을 최소화하고 데이터 무결성을 유지하기 위해 1차, 2차, 3차 정규형을 만족하도록 설계
    • 쿼리 성능을 최적화하기 위해 필요한 경우 역정규화를 고려함
      • 데이터 중복을 허용하여 읽기 성능을 향상시킬 수 있음
  • 파티셔닝(Partitioning)
    • 대용량 테이블을 여러 작은 테이블로 분할하여 성능을 최적화함
  • 데이터 보안 및 접근 제어
    • 각 테이블에 대해 접근 권한을 적절하게 설정하여 데이터 보안을 유지하고, 필요시 암호화하여 중요 데이터를 보호
  • 저장소 매개변수 설정
    • 테이블 및 인덱스의 저장소 특성을 정의하여 저장 공간을 최적화함
      • 테이블 스페이스, 블록 크기 등을 설정
    • 데이터베이스 파일의 물리적 배치를 최적화하여 성능을 향상시킴

추상화 계층 사용

추상화 계층 = 뷰, 프로시저, 함수

  • 응용 프로그램이 테이블에 직접 접근하면
    • 테이블 구조를 변경하고자 할 경우 응용 프로그램 수정이 필요
    • 이로 인해 테이블 구조 변경에 대한 부담이 가중됨
    • 제대로 모델링을 하고 싶어도 할 수 없는 상황이 됨
  • 추상화 계층을 사용하면
    • 응용 프로그램이 테이블을 직접 접근하지 않도록 함
    • 응용 프로그램이 테이블 대신 뷰, 프로시저, 함수를 사용하도록 함
    • 테이블 구조를 변경하고자 할 경우 관련 뷰, 프로시저, 함수만 수정하면 됨
    • 프로시저의 경우 성능상의 이점을 얻을 수 있음
    • 불필요한 데이터를 보이지 않게 함으로써 보안이 강화되는 효과가 있음

데이터 집계

집계 함수

  • 합(SUM), 평균값(AVG), 최댓값(MAX), 최솟값(MIN), 개수(COUNT)를 구함
    • 합과 평균값은 숫자에 대해서만 구할 수 있음
  • 최댓값, 최솟값, 개수는 숫자는 물론 문자와 날짜에 대해서도 사용 가능
    • 문자는 사전 순에 따라 최댓값, 최솟값 결정
      • 날짜의 최솟값 → 가장 빠른(오래된) 날짜
      • 날짜의 최댓값 → 가장 최근 날짜
  • 행 수를 구할 때는 COUNT(*)를 사용함
1
2
3

-- employee 테이블에서 현재 퇴직하지 않은 직원 수를 계산하는 쿼리
SELECT COUNT(*) AS emp_count
 FROM employee WHERE retire_date IS NULL;

전체 집계

  • WHERE 절의 조건을 만족하는 모든 행(조건이 없으면 전체 행)에 대한 집계 수행
  • 전체에 대한 집계이므로 출력되는 값이 한 행
  • 합계, 평균, 최솟값, 최댓값, 개수 등을 개별적으로 또는 함께 집계할 수 있음
1
2
3

-- 시스템부서 직원들의 급여 한 집계 
SELECT SUM(salary) AS tot_salary
    FROM employee WHERE dept_id = 'SYS';

집계함수와 NULL값

  • 집계 함수는 NULL 값을 무시함 (없는 값으로 간주)
    • 의도하지 않은 결과를 얻을 수 있음
      • SUM(): NULL 값을 제외한 나머지 값의 합을 구함
      • AVG(): NULL 값을 제외한 나머지 값의 합을 NULL 값을 제외한 값의 개수로 나눔
      • MIN(), MAX(): NULL 값을 제외한 값 중에서 가장 작은/큰 값
      • COUNT(): NULL 값을 제외한 값의 개수
  • COUNT(*)만이 특정 열을 기준으로 하지 않으므로 NULL 값이 무시되지 않음
  • NULL 값이 있는 열에 대한 집계 시, 특히 AVG() 함수 사용 시 주의가 필요
  • NULL 값을 무시하고 집계가 수행되기를 원하는 경우도 있을 것임
  • 그렇지 않은 경우는 NULL 값을 0으로 대체해 집계를 수행해야 함
1
2
3
4
5
6
7
8
9

-- 퇴직하지 않은 직원의 평균 급여를 계산 (NULL 값은 무시됨)
SELECT AVG(salary) AS avg_salary
    FROM employee WHERE retire_date IS NULL;
-- 퇴직하지 않은 직원의 평균 급여를 계산 (NULL 값을 포함해 전체 행 기준으로 계산)
SELECT SUM(salary) / COUNT(*) AS avg_salary
    FROM employee WHERE retire_date IS NULL;
-- NULL 값을 0으로 대체하여 평균 급여를 계산
SELECT AVG(IFNULL(salary, 0)) AS avg_salary
    FROM employee WHERE retire_date IS NULL;

그룹별 집계와 조건

  • GROUP BY를 사용해 그룹별 집계를 수행
  • 집계 결과에 대한 정렬이 필요한 경우 ORDER BY 절을 사용함
  • 기본적으로 집계 기준 열을 기준으로 오름차순 정렬이 됨
1
2
3
4
5

-- 부서별로 retire_date가 NULL인, 즉 퇴직하지 않은 직원 수를 계산하는 쿼리
SELECT dept_id, COUNT(*) AS emp_count
    FROM employee
    WHERE retire_date IS NULL
    GROUP BY dept_id;
  • WHERE 절 조건 → GROUP BY 하기 전 조건 → 집계 대상 행 필터링
  • HAVING 절 조건 → GROUP BY 한 후 조건 → 집계 후 표시할 행 필터링
  • HAVING 없는 GROUP BY는 존재하지만, GROUP BY 없는 HAVING은 존재하지 않음
1
2
3
4
5
6
7

-- 부서별로 퇴직하지 않은 직원 수를 계산하고, 직원 수가 3명 이상인 부서만 출력하는 쿼리
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;

순위 함수

  • 특정 열을 기준으로 데이터를 정렬하고 이 결과에 순위나 번호를 부여함
  • RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE() 함수 모두 OVER 절을 필수로 함
  • OVER 절 안에 정렬 기준과 분할 기준을 지정하며 분할 기준은 생략할 수 있음
1
2
3
4

-- 직원 테이블에서 퇴직하지 않은 직원들의 성별별 급여 순위를 계산하는 쿼리
SELECT emp_name, emp_id, gender, dept_id, hire_date, salary,
        RANK() OVER(PARTITION BY gender ORDER BY salary DESC) AS rnk
    FROM employee WHERE retire_date IS NULL;

RANK

  • 정렬된 데이터에서 현재 행의 순위를 반환함
    • 만일 같은 값이 여러 개 있으면 같은 순위를 부여하고, 다음 순위는 중복된 순위 개수만큼 건너뛰고 부여
  • OVER 절 안에 ORDER BY 문으로 순위를 정하기 위한 정렬 기준을 지정함
  • 최종 출력 결과에 새로운 정렬 기준을 주고 싶으면 쿼리문 마지막에 ORDER BY 문을 추가함
1
2
3
4

-- 직원 테이블에서 퇴직하지 않은 직원들의 급여 순위를 계산하는 쿼리
SELECT emp_name, emp_id, gender, dept_id, hire_date, salary,
        RANK() OVER(ORDER BY salary DESC) AS rnk
    FROM employee WHERE retire_date IS NULL AND salary IS NOT NULL
  • PARTITION BY문을 사용하면 데이터를 원하는 기준으로 그룹화하여 각 그룹 내에서 순위를 계산함
  • 최종 출력 결과는 구분 기준(PARTITION BY) + 정렬 기준(ORDER BY)에 의해 정렬되어 표시됨
1
2
3
4

-- 직원 테이블에서 퇴직하지 않은 직원들의 성별별 급여 순위를 계산하는 쿼리
SELECT emp_name, emp_id, gender, dept_id, hire_date, salary,
        RANK() OVER(PARTITION BY gender ORDER BY salary DESC) AS rnk
    FROM employee WHERE retire_date IS NULL AND salary IS NOT NULL;

DENSE_RANK

  • RANK() 함수와 사용 방법이 같으며 표시되는 순위에 누락된 숫자가 없음이 다름
  • 같은 순위가 있어도 이어지는 순위는 건너지 않고 부여함
1
2
3
4

-- 직원 테이블에서 퇴직하지 않은 직원들의 급여 순위를 계산하는 쿼리 (순위에 중복 없음)
SELECT emp_name, emp_id, gender, dept_id, hire_date, salary,
        DENSE_RANK() OVER(ORDER BY salary DESC) AS rnk
    FROM employee WHERE retire_date IS NULL AND salary IS NOT NULL;

ROW_NUMBER

  • 정렬된 결과에 일련의 번호를 부여함
  • PARTITION BY를 사용하지 않는 한 같은 번호는 부여되지 않음
1
2
3
4

-- 직원 테이블에서 퇴직하지 않은 직원들의 이름 기준으로 고유 번호를 부여하는 쿼리
SELECT ROW_NUMBER() OVER(ORDER BY emp_name ASC) AS num,
        emp_name, emp_id, gender, dept_id, hire_date, salary
    FROM employee WHERE retire_date IS NULL AND salary IS NOT NULL;

NTILE

  • 몇 개의 그룹으로 나누어 그 그룹마다 번호를 부여함
  • 그룹의 개수를 지정해야 하므로 다른 함수와는 달리 NTILE(n) 형태로 함수안에 숫자 지정
1
2
3
4

-- 직원 테이블에서 퇴직하지 않은 직원들의 급여를 기준으로 3개의 그룹으로 나누는 쿼리
SELECT emp_name, emp_id, gender, dept_id, hire_date, salary,
        NTILE(3) OVER(ORDER BY salary DESC) AS grp
    FROM employee WHERE retire_date IS NULL AND salary IS NOT NULL;

데이터 변경

  • DML(Data Manipulation Language): INSERT, UPDATE, DELETE, SELECT
  • DDL(Data Definition Language): CREATE, ALTER, DROP
  • DCL(Data Control Language): GRANT, REVOKE, DENY

DML문

  • 데이터 조작 언어 = 데이터를 변경하는 쿼리문
  • INSERT, UPDATE, DELETE, SELECT문이 DML문에 포함됨
  • 잘못 변경된 것에 대한 즉각적인 복구가 어려울 수 있음
    • 우선 SELECT문으로 변경 대상을 확인하고 또 확인하는 습관 필요
    • UPDATE문과 DELETE문의 경우 정확한 조건을 지정해야 함
    • 필요한 경우 DML 문을 /* 주석처리 */ 해서 실수로 수행되지 않게 방지

INSERT문

  • 모든 열이 원래 순서로 나열될 경우 열 이름 생략이 가능
    • 가독성 향상을 위해 생략하지 않기를 권고
1
2
3
4
5
6
7
8
9
10

-- 열 이름 지정
INSERT INTO department(dept_id, dept_name, unit_id, start_date)
    VALUES('PRD', '상품', 'A', '2022-10-01');
-- 열 이름 생략
INSERT INTO department
    VALUES('DBA', 'DB관리', 'A', '2022-10-01');
-- 콤마로 구분해 여러 행을 동시에 추가
INSERT INTO department
    VALUES ('PRD', '상품', 'A', '2022-10-01'),
    ('DBA', 'DB관리', 'A', '2022-10-01');
  • SELECT 문 수행 결과를 테이블에 INSERT할 수 있음
  • SELECT 문 결과와 같은 개수의 열이 테이블에 있어야 함
    • 대상 열을 별도로 지정하지 않은 경우
  • 추가될 값과 입력 받을 열의 데이터 형식이 서로 같거나 호환되어야 함
1
2
3
4
5
6
7

-- 퇴직한 직원 정보를 retired_employee 테이블로 삽입하는 쿼리
INSERT INTO retired_employee
    SELECT * FROM employee WHERE retired_date IS NOT NULL;
-- 퇴직한 직원의 특정 열(emp_id, emp_name, hire_date, retire_date)을 retired_employee 테이블로 삽입하는 쿼리
INSERT INTO retired_employee
    SELECT emp_id, emp_name, hire_date, retire_date
        FROM employee WHERE retired_date IS NOT NULL;

UPDATE문

  • 조건에 맞는 행 값을 변경
1
2
3
4

-- 특정 직원의 전화번호를 업데이트하는 쿼리
UPDATE employee
    SET phone = '010-1239-1239'
    WHERE emp_id = 'S0001';

DELETE문

  • 조건에 맞는 행 제거
1
2
3

-- 2017년 12월 31일 이전에 종료된 휴가 정보를 삭제하는 쿼리
DELETE FROM vacation
    WHERE end_date <= '2017-12-31';
  • 테이블 모든 행 제거
    • 모든 행을 지울 때는 TRUNCATE TABLE문 사용을 권고
1
2
3
4

-- vacation 테이블의 모든 데이터를 삭제하는 쿼리 (행 단위 삭제)
DELETE FROM vacation; -- 방법 1
-- vacation 테이블의 모든 데이터를 빠르게 삭제하는 쿼리 (테이블 초기화)
TRUNCATE TABLE vacation; -- 방법 2

다중 테이블 조회

JOIN의 필요성

  • 관계형 데이터베이스는 여러 테이블로 데이터가 나뉘어 존재하는 형태
  • 대부분의 테이블들이 PK, FK로 관계를 설정해 연결된 상태
  • 하나의 테이블에서만 데이터를 조회하는 쿼리는 한계에 부딪히게 됨
  • 결국 의미 있는 결과를 얻기 위해서 여러 테이블에서 데이터를 조회해야 함
  • 조인문과 하위 쿼리를 사용해 여러 테이블을 연결해 조회할 수 있음

ERD를 참고해야 하는 이유

  • 관계 이해
    • ERD는 테이블 간의 관계를 시각적으로 표현함
    • 어떤 테이블이 어떻게 연결되어 있는지를 쉽게 파악 가능
      • 조인할 테이블을 식별하는 데 도움이 됨
  • 키 확인
    • ERD를 통해 각 테이블의 기본 키와 외래 키를 확인할 수 있음
      • 조인 조건을 설정하는 데 필수적인 정보임
  • 데이터 흐름 파악
    • ERD는 데이터 흐름과 의존성을 명확히 보여줌
      • 올바른 순서와 방식으로 테이블을 조인할 수 있음
  • 복잡성 감소
    • ERD를 참조하면 복잡한 쿼리 구조를 시각적으로 이해할 수 있음
    • 조인문을 작성하는 과정에서 발생할 수 있는 오류를 줄일 수 있음

JOIN문 작성 3단계

  • 테이블 연결
1
2
3
4

-- 직원 정보와 부서 정보를 조인하여 조회하는 쿼리 (테이블 연결)
SELECT emp_id, emp_name, employee.dept_id,department.dept_name, phone, email
    FROM employee
    JOIN department ON employee.dept_id = department.dept_id;
  • 테이블 별칭 지정 → 쿼리문이 간결
1
2
3
4

-- 직원 정보와 부서 정보를 조인하여 조회하는 쿼리 (테이블에 별칭 사용)
SELECT emp_id, emp_name, e.dept_id, d.dept_name, phone, email
    FROM employee AS e
    JOIN department AS d ON e.dept_id = d.dept_id;
  • 테이블 별칭 채우기 → 쿼리문 이해에 도움을 줌
1
2
3
4

-- 직원 정보와 부서 정보를 조인하여 조회하는 쿼리 (테이블에 별칭 사용)
SELECT emp_id, emp_name, employee.dept_id,department.dept_name, phone, email
    FROM employee
    JOIN department ON employee.dept_id = department.dept_id;

INNER JOIN

  • 가장 일반적인 JOIN 문 형태
  • 양쪽 테이블에서 비교되는 값이 일치하는 행만 가져옴
  • 일반적으로 PK와 FK가 ON 절에서 서로 비교됨
1
2
3
4
5
6

-- 2020년에 입사하고 퇴직하지 않은 직원의 정보를 부서 정보와 함께 조회하는 쿼리
SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name, e.phone
    FROM employee AS e
    INNER JOIN department AS d ON e.dept_id = d.dept_id
    WHERE e.hire_date BETWEEN '2020-01-01' AND '2020-12-31'
        AND e.retire_date IS NULL;

OUTER JOIN

  • 비교되는 값이 일치하지 않는 행도 기준 테이블에서 가져옴
  • LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 구분
    • 단, MySQL은 FULL OUTER JOIN이 없음
1
2
3
4

-- 부서 정보와 해당 단위(unit) 정보를 조회하는 쿼리 (LEFT OUTER JOIN 사용)
SELECT d.dept_id, d.dept_name, d.unit_id, u.unit_name
    FROM department AS d
    LEFT OUTER JOIN unit AS u ON d.unit_id = u.unit_id;

CROSS JOIN

  • 일반적인 비즈니스 응용프로그램에서 사용되지 않음
  • ON 절이 없어 모든 경우의 수 만큼 결과 행을 얻음
  • 대량의 테스트 데이터를 만드는 목적으로 많이 사용됨

여러 테이블 조인

  • 테이블간의 관계를 알고 작성하면 쉽게 작성 가능
1
2
3
4
5
6
7
8

-- 2021년 1월 1일부터 3월 31일까지 휴가를 간 직원들의 정보를 조회하는 쿼리
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 문으로 작성해서 같은 결과를 얻을 수 있음
  • JOIN 문보다 작성하기가 쉬움
1
2
3
4

-- 직원 테이블에서 가장 높은 급여를 받는 직원의 정보를 조회하는 쿼리
SELECT emp_id, emp_name, salary
    FROM employee
    WHERE salary = (SELECT MAX(salary) FROM employee);
  • 일반적인 하위 쿼리에 비해 상관 하위 쿼리를 이해하기가 다소 어려움
1
2
3
4
5
6

-- 휴가 기록이 있는 직원들의 정보를 조회하는 쿼리
SELECT emp_id, emp_name, email
    FROM employee AS e
    WHERE EXISTS (SELECT *
                        FROM vacation
                        WHERE emp_id = e.emp_id);

뷰(Views)

뷰 만들기

  • 뷰는 쿼리문을 가지고 서버에 존재하는 개체
    • 뷰를 조회하면 뷰가 가지고 있는 쿼리가 실행되어 결과 반환
  • 우선 뷰가 보여줄 데이터에 대한 SELECT 문을 작성 (뷰를 만드는 99.9%의 작업)
  • 작성한 SELECT 문을 CREATE VIEW 문을 사용해 뷰로 선언함
  • 뷰를 사용해 테이블 구조와 쿼리의 복잡성을 숨길 수 있음 → 사용자 관점의 데이터 제공
  • 뷰가 보여주는 데이터만 보게 하여 예민한 데이터에 대한 접근을 차단할 수 있음 → 보안 강화
1
2
3
4
5
6
7

-- 직원 정보와 부서 정보를 포함하는 뷰를 생성하는 쿼리
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 방법1. ALTER VIEW문으로 변경
-- 기존 뷰(employee_info)를 수정하여 새로운 열(email, phone)을 포함하는 쿼리
ALTER VIEW employee_info
AS
SELECT e.emp_name, e.emp_id, e.gender, d.dept_name, e.hire_date, e.email, e.phone
    FROM employee AS e ~~(생략)~~
-- 방법2. CREATE OR REPLACE VIEW 문으로 생성 또는 변경 (단, MS SQL은 CREATE OR ALTER문 사용)
-- 뷰(employee_info)를 생성하거나 기존 뷰를 대체하여 새로운 열(email, phone)을 포함하는 쿼리
CREATE OR REPLACE VIEW employee_info
AS
SELECT e.emp_name, e.emp_id, e.gender, d.dept_name, e.hire_date, e.email, e.phone
    FROM employee AS e ~~(생략)~~
-- 불필요한 뷰는 DROP VIEW 문으로 제거 
-- employee_info 뷰를 삭제
DROP VIEW employee_info;

열 별칭 지정

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 방법1. AS를 사용해 별칭 지정
-- 직원 정보와 부서 정보를 포함한 뷰(employee_info)를 생성하는 쿼리
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;

-- 방법2. 뷰 이름 선언 부분에서 별칭 지정
-- 직원 정보와 부서 정보를 포함한 뷰(employee_info)를 생성하는 쿼리
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
7
8
9
10
11
12
13
14
15
16

-- 고액 연봉(8000 이상)을 받는 직원 정보를 포함한 뷰(high_salary)를 생성하거나 기존 뷰를 대체하는 쿼리
CREATE OR REPLACE VIEW high_salary
AS
SELECT e.emp_name, e.emp_id, d.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;

-- 고액 연봉(high_salary) 뷰를 통해 직원 급여를 7000으로 업데이트하는 쿼리
UPDATE high_salary
    SET salary = 7000
    WHERE emp_id = 'S0002';
/*
Error Code: 1369. CHECK OPTION failed 'hrdb2019.high_salary' 0.016 sec
*/
Bootcamp, KT Aivle School
Bootcamp KT Aivle School SQL
This post is licensed under CC BY 4.0 by the author.