Yerim Shin

BE engineer

7 minute read

관계형 데이터베이스

데이터베이스(DB) vs 데이터베이스 관리시스템(DBMS)

  • DB는 어떤 자료구조를 사용하느냐에 따라 달라진다.
  • DB에는 계층형, 네트워크형, 관계형 데이터베이스가 있다.
    • 계층형 DB
      • 트리 자료구조에 데이터를 저장하고 관리하며 1:N 관계를 표현한다.
    • 네트워크형 DB
      • 오너와 멤버 형태로 데이터를 저장하며 N:M 관계 표현이 가능하다.
  • DBMS는 데이터베이스를 관리하기 위한 소프트웨어를 의미한다.

연산

집합연산

  • 합집합: 두 관계의 합
  • 교집합: 두 관계에 함께 존재하는 것
  • 차집합: 한 관계에만 존재하며 다른 관계에는 존재하지 않는 것
  • 곱집합: 각 관계에 존재하는 모든 데이터의 조합

관계연산

  • 선택연산: 관계에서 특정 조건에 해당하는 행만 조회
  • 투영연산: 관계에서 특정 조건에 맞는 속성만 조회
  • 결합연산: 여러 관계에서 공통된 속성을 통해 새로운 관계 생성
  • 나누기연산: 기준 관계에서 나누는 관계가 가지는 속성과 동일한 행만 추출하여 중복된 행 제거

DML, DDL, DCL, TCL

  • DML (Data Manipulation Language) : 데이터를 검색, 수정, 삽입, 삭제
    • SELECT, INSERT, UPDATE, DELETE, MERGE(INSERT + UPDATE)
  • DDL (Data Definition Language) : 데이터 테이블(셋)을 생성, 수정, 삭제
    • CREATE, ALTER, RENAME, DROP, TRUNCATE
    • 실행시 자동 커밋됨
  • DCL (Data user Control Language) : 데이터 사용자 혹은 객체 사용권한 관리
    • GRANT, REVOKE
    • REVOKE 로 권한을 회수하면, 권한을 취소당한 사용자가 WITH GRANT OPTION 써서 다른 사용자에게 부여했던 권한들도 모두 연쇄적으로 취소된다.
    • 여러가지 권한을 묶어 ROLE으로 관리할 수 있다.
  • TCL (Transaction Control Language) : 트랜잭션 관리 및 제어 명령어
    • COMMIT : 트랜잭션 완료
    • ROLLBACK : 최신 커밋까지 / 지정 savepoint까지 복귀
    • SAVEPOINT : 복귀 지점 지정
  • SQL에서 NULL 값은 반드시 IS (NOT) NULL으로 처리한다.
    • c.f ) NULL IS NULL : OK, 반드시 참인 문장
  • 오라클에서 기본 값을 null로 설정한 칼럼에 대해 ‘’ 을 INSERT하면 기본 값인 null 로 입력된다. SQL server에서는 ‘’ 입력 가능

트랜잭션

  • DB의 논리적인 연산 단위
  • 분리할 수 없는 한개 이상의 DB 조작을 의미함
  • 하나 이상의 SQL문을 가짐
  • 한 트랜잭션은 모두 적용되거나 적용되지 않음 (원자성)

SQL 실행 순서

  1. 파싱 Parsing
    • SQL 문법을 확인하고 구문을 분석하고, Library Cache에 저장한다.
  2. 실행 Execution
    • 옵티마이저가 생성한 실행 계획에 따라 SQL문을 실행한다.
  3. 인출 Fetch
    • 데이터를 읽어 전송한다.

SELECT문

  • 테이블에 저장된 데이터를 조회하기 위한 명령어로, 특정 행 또는 컬럼을 조회할 수 있다.

  • FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY 순서대로 실행된다.

  • DISTINCT
    • SELECT절의 컬럼에 위치해 중복을 제거한 결과를 출력한다.
  • Alias
    • 테이블 또는 컬럼 명이 길거나 함수를 적용하였을 때 이름을 변경한다. 
      SELECT 컬럼명 AS COL
FROM 테이블명 AS TAB;
  • ORDER BY
    • 데이터 조회 시 오름차순 또는 내림차순으로 정렬할 수 있다.
    • 가장 마지막에 수행되는 명령이다.
    • 메모리를 많이 사용하기 때문에 SQL이 느려질 수 있다.
SELECT 컬럼명
FROM 테이블명
ORDER BY 컬럼명 ASC/DESC;
  • WHERE
    • 원하는 행 또는 컬럼만 조회하기 위해 조건을 줄 때 사용한다.
  • 연산자
연산자 설명
= ~와 같다
< ~보다 작다
> ~보다 크다
!= / <> / NOT ~가 아니다
LIKE %문자% ‘문자’가 들어간 경우
BETWEEN A AND B A와 B 사이
IN LIST LIST 안에 있는 경우
IS NULL NULL인 경우

함수

  • NULL : 데이터 정의를 알 수 없는 값

  • 문자 함수

함수 설명 예시
LOWER/UPPER 영문의 소문자화 또는 대문자화 하는 함수 SELECT LOWER(컬럼명) AS 소문자 FROM 테이블명;
SELECT UPPER(컬럼명) AS 대문자 FROM 테이블명;
LENGTH 문자열의 길이를 반환하는 함수 SELECT LENGTH(컬럼명) AS 길이 FROM 테이블명;
SUBSTR 전체 문자열 중 START부터 END까지의 일부 문자열을 반환하는 함수 SELECT SUBSTR(컬럼명, START, END) AS 일부 FROM 테이블명;
INSTR 전체 문자열 중 특정 문자 ‘X’의 위치를 반환하는 함수 SELECT INSTR(컬럼명, 'X') AS 위치 FROM 테이블명;
LPAD/RPAD 문자열을 L길이가 되도록 왼쪽/오른쪽에 특정 문자 ‘X’를 채운 문자열을 반환하는 함수 SELECT LPAD(컬럼명, L, 'X') AS 문자열채움 FROM 테이블명;
SELECT RPAD(컬럼명, L, 'X') AS 문자열채움 FROM 테이블명;
TRIM/LTRIM/RTRIM 문자열에서 공백을 제거한 문자열을 반환하는 함수 SELECT TRIM(컬럼명) AS 공백제거 FROM 테이블명;
SELECT LTRIM(컬럼명) AS 공백제거 FROM 테이블명;
SELECT RTRIM(컬럼명) AS 공백제거 FROM 테이블명;
REPLACE 문자열에서 ‘OLD’문자를 ‘NEW’ 문자로 변환한 문자열을 반환하는 함수 SELECT REPLACE(컬럼명, OLD, NEW) AS 문자변환 FROM 테이블명;
  • 숫자 함수
함수 설명 예시
ROUND 숫자를 N번째 소수점 자리에서 반올림한 숫자를 반환하는 함수 SELECT ROUND(컬럼명, N) AS 반올림 FROM 테이블명;
TRUNCATE 숫자를 N번째 소수점 자리에서 버린 숫자를 반환하는 함수 SELECT TRUNCATE(컬럼명, N) AS 버림 FROM 테이블명;
CEIL/FLOOR 숫자를 올림/내림한 숫자를 반환하는 함수 SELECT CEIL(컬럼명) AS 올림 FROM 테이블명;
SELECT FLOOR(컬럼명) AS 내림 FROM 테이블명;
POWER 숫자를 N번 제곱한 숫자를 반환하는 함수 SELECT POWER(컬럼명, N) AS 제곱수 FROM 테이블명;
  • 날짜 함수
함수 설명 예시
SYSDATE / SYSTIMESTAMP 현재 시스템 날짜 및 시간을 반환해주는 함수 SELECT SYSDATE, SYSTIMESTAMP FROM 테이블명;
ADD_MONTHS / NEXT_DAY / LAST_DAY 특정 일/월 만큼 더한 날짜를 반환해주는 함수 SELECT ADD_MONTHS(SYSDATE, 6) AS 6개월후,
NEXT_DAY(SYSDATE, '월요일') AS 다음 월요일,
LAST_DAY(SYSDATE) AS 어제날짜 FROM 테이블명;
TO_CHAR 특정 날짜 및 시간 후의 날짜 및 시간을 반환해주는 함수 SELECT TO_CHAR(SYSDATE + 1/24/60/60, 'YYYY/MM/DD/HH24:MI:SS') AS 1초뒤,
TO_CHAR(SYSDATE + 1/24/60, 'YYYY/MM/DD/HH24:MI:SS') AS 1분뒤,
TO_CHAR(SYSDATE + 1/24, 'YYYY/MM/DD/HH24:MI:SS') AS 1시간뒤,
TO_CHAR(SYSDATE + 1, 'YYYY/MM/DD/HH24:MI:SS') AS 1일뒤 FROM 테이블명;
  • 1/24/60 = ORACLE 1분

GROUP BY

  • 집합 내에서 하나 혹은 여러 행을 그룹화한 후 집계 함수를 이용하여 계산하는 명령어이다.
  • HAVING절을 이용하여 GROUP BY가 적용된 상태에 조건을 추가할 수 있다.
  • alias 명이 사용 불가능하다.
  • 집계함수를 사용해 GROUP에 따른 집계를 낼 수 있다.
  • SUM, AVG, COUNT, MAX, MIN 등
    • COUNT : 컬럼명 대신 *을 사용할 경우 NULL을 포함한 행의 수를 반환한다.
    • STDDEV : 표준편차
    • VARIAN : 분산
  • null 값을 무시한다.

분기문

  • NVL
    • NULL인 값을 제거하기 위한 일반 함수
    • NULL 대신 특정 값(VALUE)으로 변환한다.
  • NVL2(COL, VALUE1, VALUE2)
    • NVL의 개선된 버전의 함수
    • NULL이 아닐 경우 VALUE1으로 NULL일 경우 VALUE2로 변환한다.
  • DECODE(COL, CONDI1, VALUE1, CONDI2, VALUE2, ...)
    • 한 컬럼에 여러 조건을 적용하여 변환하는 함수
    • 조건 1에 해당하는 경우 값1로 조건 2에 해당하는 경우 값2로 변환된다.
  • CASE WHEN
    • 여러 컬럼 및 조건으로 변환하는 경우 사용하는 함수
    • 하나의 컬럼 값만 기준이 되는 경우와 여러 컬럼이 기준이 되는 경우 문법을 달리해서 사용할 수 있다.
    • 나열된 순서대로 조건을 비교한다.
SELECT CASE COL1 WHEN A THEN 1 WHEN B THEN 2 AS 기준하나,
	   CASE WHEN COL1='A' THEN 1 WHEN COL1='B' THEN 2 AS 기준여러개
FROM 테이블명;

계층형 질의

  • CONNECT BY PRIOR 자식컬럼 = 부모컬럼 순방향 (프자부)
  • CONNECT BY 부모컬럼 = PRIOR 자식컬럼 순방향
  • Oracle : CONNECT BY 절 뿐만 아니라 SELECT, WHERE 절에서도 사용 가능

연산자

  • 우선순위 : 괄호, 비교연산자/SQL연산자, not, and, or

window 함수

  • 결과에 대한 함수 처리이기 때문에 결과 건수가 줄어들지는 않음
... OVER
	PARTITION BY -- 행을 분할
	ORDER BY     -- 행을 정렬
	ROWS / RANGE(WINDOWING)    -- 대상 행 지정
  • ROWS
    • PRECEDING : 이전 행
    • FOLLOWING : 다음 행
  • RANGE
    • UNBOUNDED : 무한한 이전/다음 행
    • CURRENT : 현재 행

예시 윈도우 함수

  • 순서, 순위
    • RANK()
      • 공동 등수 수여 후 다음 등수 제어
      • 1, 1, 3 …. 이런식으로 나오면 랭킹함수 쓴거임
    • DENSE_RANK()
      • 중복 등수 존재, 있어도 다음 연속하는 등수로 부여
      • 1, 2, 2, 3, …
    • ROW_NUMBER()
      • 중복 등수 없음. 연속 등수
  • 집계
    • SUM, AVG, COUNT, MAX, MIN
    • null 값 무시함
    • WHERE절에서 사용 불가능
  • 백분위, 비율
    • CIME_DIST :
    • RATIO_TO_REPORT
    • PERCENT_RANK
    • NTILE() : 인자 값으로 등분해 포함되는 구간값 출력

COUNT()

  • COUNT()함수
    • *, 1, 2, ... : NULL 포함 전체 행의 개수 카운트
    • 컬럼명 : NULL 미포함 전체 행의 개수 카운트
    • DISTINCT 컬럼명 : 컬럼명 중복없이 카운트
      • DISTINCT 컬럼명1, 컬럼명2 : 두가지 컬럼의 조합을 리턴하므로 주의

수직 결합 set operator

  • UNION() 합집합
  • INTERSECT() 교집합
  • MINUS - (ORACLE) / EXCEPT - (SQL-SERVER) 차집합
  1. 테이블과 테이블을 수직 결합하고자 할 때 사용한다
  2. 위치 기반 결합
  3. 중복치 제거 (DISTINCT한 결과만 출력)
    • UNION ALL -> 중복 인정
  4. 복수의 set operator 연산 처리 -> 위에서부터 아래로.

NULL 관련 함수

  • NVL(a, b) (오라클), ISNULL(a, b) (sql server)
    • null value 약자
    • a값이 NULL 인경우 b를 출력한다.
  • NULLIF(컬럼명1, 컬럼명2)
    • 두 컬럼 값이 같다면 NULL로 처리하는 함수
    • 같지 않다면 컬럼명1의 값을 반환한다.
    • NULL 값 간 비교시 NULL을 반환한다.
  • COALESCE(A, B, C, ...)
    • 인수 값 A, B, C, ... 순서대로 검사해 NULL값이 아닌 최초의 값 출력

GROUP 함수

  • 전체 합과 동시에 그룹별 소계를 함께 구하고 싶은 경우
  • ALIAS 명 사용 불가능

  • null도 집계에 포함함

  • ROLLUP : 하나씩 증가하는 형태
    • group by rollup(a) : 전체 합, a 소계
    • group by rollup(a, b) : 전체 합, a 소계, a와 b의 조합 소계 …

  • 다른 컬럼과 혼용시 주의

  • CUBE : 모든 가능한 조합 (시스템에 부하 갈 수 있음)
    • group by cube(a) : 전체 합, a 소계
    • group by cube(a, b) : 전체 합, a 소계, b 소계, a와 b 조합 소계
  • GROUPING SETS
    • group by grouping sets(a) : a 소계
    • group by grouping sets(a) : a 소계
    • 활용 예시
      • group by grouping sets(a, b) : a, b
      • group by grouping sets((a, b), a, ()) : rollup(a, b)와 동일
      • group by grouping sets(a, rollup(b)) : a, b, ()
  • GROUPING 칼럼명 WHEN 1 (O)
  • GROUPING 칼럼명 WHEN 0 (X)

서브쿼리

하나의 SQL문 내 포함된 또 다른 SQL

  • GROUP BY를 제외한 모든 곳에서 자유롭게 사용 가능
  • 단일 행 서브 쿼리
    • 다중행 서브쿼리에서 사용 불가
    • 서브 쿼리 결과가 1건 이하인 경우
    • 단일 행을 비교하는 비교 연산자
    • = , < , <=, >, >=, >, <> 등…
  • 다중 행 서브 쿼리
    • 단일행 서브쿼리에서 사용 가능
    • 서브 쿼리 결과가 여러 행인 경우 (2행 이상)
    • IN, ALL, ANY, SOME
    • EXISTS : 서브쿼리 내 조건 만족하는 개체가 존재하는가?
  • Un-Correlated (비연관) 서브쿼리
    • 서브 쿼리가 메인 쿼리 칼럼을 가지지 않음 (정적)
    • 단순히 메인쿼리에 간단한 값 제공이 목적임. 계산 해결…
    • 서브쿼리에서 메인쿼리 컬럼 사용 OK, 반대는 불가
  • Correlated (연관) 서브쿼리
    • 메인 쿼리 칼럼을 포함하는 서브쿼리
      • row에 따라 값 변동 (동적)
    • 메인쿼리가 먼저 수행된 후 서브 쿼리 조건 맞는지 확인하는 용도

표준 조인

  • INNER JOIN
    • 동일한 데이터의 값을 기준으로 실행되는 JOIN
  • NATURAL JOIN
    • 양 테이블 간 동일한 컬럼명을 기준으로 자동으로 실행되는 INNER JOIN
    • ON절 또는 USING절을 사용
  • CROSS JOIN
    • 결합하는 테이블 간 조건이 없을 때 . 모든 데이터 조합 리턴
  • OUTER JOIN
    • 기준이 되는 테이블은 다른 테이블에 값이 없어도 실행

출처

  • https://velog.io/@sy508011/SQLD-%EC%9A%94%EC%95%BD-%EC%A0%95%EB%A6%AC-2%EA%B3%BC%EB%AA%A9-1%EC%9E%A5-SQL-%EA%B8%B0%EB%B3%B8
  • https://youtube.com/playlist?list=PLJyu27XFq6X0Sa2622T9pan9WEQaUFFKg&si=aQVVNrMS5d5znQaS

Categories:

Updated:

Leave a comment

You may also enjoy