SQL DB SQLD

DB - SQLD 정리

SQLD

Posted by Yan on August 27, 2021

SQLD 시험범위 정리

DML, DCL, DDL

DML : 데이터 조작어

  • SELECT, INSERT, DELETE, UPDATE
  • 비절차적 데이터 조작어(DML)는 사용자가 무슨 데이터를 원하는지를 명세하는 언어다.
  • 호스트 프로그램 속에 삽입되어 사용되는 DML명령어들은 데이터 부속어Data Sub Language라고도 한다.

DDL : 데이터 정의어

  • 스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 제거할 때 사용된다
  • CREATE, ALTER, DROP, RENAME

ALTER

  • ORACLE
    • ALTER TABLE 테이블명 MODIFY 칼럼명 데이터 유형;
  • SQL SERVER
    • ALTER TALBE 테이블명 ALTER 칼럼명 데이터유형;
불필요한 칼럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

테이블 이름 변경

RENAME 테이블명 TO 변경 후 테이블명;

DCL : 데이터 제어어

  • GRANT, REVOKE

트랜잭션의 특성

  1. 원자성
  • 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.
  1. 일관성
  • 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.
  1. 고립성
  • 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다.
  1. 지속성
  • 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제점

  • Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
  • Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타는 현상

NULL의 특성

  • NULL값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다.
  • 테이블을 생성할 때 NOT NULL또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 NULL값을 포함할 수 있다.
  • NULL값을 포함하는 연산의 경우 결과 값도 NULL값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인 것과 같다.
  • 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL함수를 사용한다. NULL값의 대상이 숫자유형 데이터인 경우는 주로 0으로, 문자유형 데이터인 경우는 X같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.

단일행 NULL관련 함수의 종류

NVL(표현식1, 표현식2) / ISNULL(표현식 1, 표현식 2)

  • 표현식1의 결과값이 NULL이면 표현식2의 값을 출력
  • 단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 한다.
  • NULL관련 가장 많이 사용되는 함수

NULLIF(표현식1, 표현식2)

  • 표현식1이 표현식2와 같으면 NULL
  • 같지 않으면 표현식1을 리턴

COALESCE(표현식1, 표현식2, …)

  • 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다.
  • 모든 표현식이 NULL이라면 NULL을 리턴

ORDER BY절의 특징

  • 기본적인 정렬 순서는 오름차순 ASC이다
  • 숫자형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력된다
  • 날짜형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력된다
  • ORACLE에서는 NULL값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에 가장 마지막에, 내림차순으로 정렬했을 경우는 가장 먼저 위치한다
  • SQL SERVER에서는 NULL값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우에 가장 먼저, 내림차순으로 정렬했을 경우는 가장 마지막에 위치한다

JOIN

  • 일반적으로 JOIN은 PK와 FK값의 연관성에 의해 성립된다. 하지만 어떤 경우에는 PK, FK관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능하다
  • EQUI JOIN은 JOIN에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용되는 방법이다
  • EQUI JOIN은 “=”연산자에 의해서만 수행되며, 그 외의 비교 연산자를 사용하는 경우에는 모두 NON EQUI JOIN이다
  • 대부분 NON EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있다

집합 연산자

두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용

  1. SELECT절의 칼럼 수가 동일하고
  2. SELECT절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환할 때 사용

일반 집합 연산자

  1. UNION : 합집합 (중복 행은 1개로 처리)
  2. UNION ALL : 합집합 (중복 행도 표시)
  3. INTERSECT(INTERSECTION) : 교집합
  4. EXCEPT, MINUS(DIFFERENCE) : 차집합
  5. CROSS JOIN(PRODUCE) : 곱집합

순수 관계 연산자

관계형 DB를 새롭게 구현

  1. SELECT -> WHERE
  2. PROJECT -> SELECT
  3. NATURAL JOIN -> 다양한 JOIN
  4. DIVIDE

FROM절 JOIN형태

  1. INNER JOIN
  2. NATURAL JOIN
  3. USING 조건절
  4. ON 조건절
  5. CROSS JOIN
  6. OUTER JOIN

INNER JOIN

  • JOIN 조건에서 동일한 값이 있는 행만 변환
  • UNING 또는 ON절을 필수적으로 사용

NATURAL JOIN

  • 두 테이블 간 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN수행
  • NATURAL JOIN이 명시되면 추가로 USING, ON, WHERE절에서 JOIN 조건을 정의할 수 없음
  • SQL SERVER에서는 지원하지 않음

USING 조건절

  • 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN 가능
  • JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없음
  • SQL SERVER에서는 지원하지 않음

ON 조건절

  • 칼럼 명이 다르더라도 JOIN조건을 사용할 수 있음
  • ALIAS나 테이블명을 반드시 사용

윈도우 함수

1
2
3
4
5

SELECT WINDOW_FUNCTION(ARGUMENTS)
       OVER ( [PARTITION BY 컬럼]
              [ORDER BY 절]
              [WINDOWING 절])
       FROM 테이블명;
  • WINDOW_FUNCTION(ARGUMENTS) : 함수에 따라 0~N개의 인수 지정
  • PARTITION BY 절 : 전체 집합을 기준에 따라 소그룹으로 나눌 수 있다
  • ORDER BY 절 : 순위를 지정할 항목을 기술
  • WINDOWING 절 : WINDOWING 절은 함수의 대상이 된느 행 기준의 범위를 강력하게 지정
    • ROWS : 물리적인 결과 행의 수
    • RANGE : 논리적인 값에 의한 범위
    • 둘 중 하나를 선택해서 사용
  • RANK : 중복 건너뛰기. 특정 항목에 대한 순위를 구하는데, 동일한 값에 대해서는 동일한 순위 부여 
    SELECT JOB, ENAME, SAL,
     RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
     RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
     FROM EMP;
  • DENSE RANK : 중복을 건너뛰지 않음. 동일한 순위를 하나의 등수로 간주함
  • ROW_NUMBER : RANK나 DENSE_RANK 함수가 동일한 값에 대해 동일한 순위를 부여하는 것에 반해, 동일한 값이라도 고유한 순위를 부여
  • PERCENT_RANK : 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구함.
    • 결과값은 0 < X <= 1의 범위를 가짐
  • LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있음
  • LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있음

계층형 질의

  • 테이블에 계층형 데이터가 존재하는 경우 데이터가 존재하는 경우 데이터를 조회하기 위한 과정
  • START WITH : 계층구조 전개의 시작위치 지정
  • CONNECT BY : 다음에 전개될 자식 데이터 지정
  • PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.
    • PRIOR 자식 = 부모형태를 사용 : 계층구조에서 부모 데이터 -> 자식데이터 방향 전개
    • PRIOR 자식 데이터 = 부모 데이터 : 부모데이터 = PRIOR 자식데이터와 동일한 의미
    • 부모에서 자식으로 가는 경우 순방향
  • NOCYCLE : 동일한 데이터가 전개되지 않음
  • ORDER SIBLINGS BY : 형제 노드간의 정렬 수행
  • WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 필터링
  • LEVEL : 루트 데이터 1, 하위데이터 2, 리프 데이터까지 1씩 증가
  • CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터 -> 1, 아니면 0
  • CONNECT_BY_ISCYCLE : 해당 데이터가 조상 데이터 -> 1, 아니면 0

절차형 PL/SQL

  • EXCEPTION : 생략 가능
  • PROCEDURE
  • TRIGGER : COMMIT 롤백이 안 된다
  • USER DEFINED FUNCTION : 값이 반드시 나온다

저장모듈

PL/SQL 문장을 DB서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램.
독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램

PL/SQL특징

  • BLOCK 구조로 되어 있어 각 기능별로 모듈화 가능
  • 변수, 상수 등을 선언하여 SQL문장 간 값을 교환
  • IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다
  • DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다
  • PL/SQL은 ORACLE에 내장되어 있으므로 호환성이 좋다
  • 응용 프로그램의 성능을 향상시킨다
  • BLOCK단위로 처리되어 통신량을 줄일 수 있다
CATALOG
    FEATURED TAGS
    Udemy java CS SQL DB Spring algorithm 디자인패턴 Gof effective Java class spring Ubuntu 이것이 자바다 자바의 정석 TDD object 데이터 OS gradle javascript quick_sort stream 객체지향 AWS Algorithm Khan_academy MSA NodeJS Nomad_Coders OOP cloud controller design_pattern domain frontend gof heap_sort lambda node