SQLD 개념정리 08. 핵심 요약

1과목 데이터 모델링의 이해

1장 데이터 모델링의 이해

1. 데이터 모델의 이해

모델링의 특징

• 추상화
• 단순화
• 명확화

모델링의 관점 : 데이터 관점, 프로세스 관점, 상관 관점

 

데이터 모델링이란

• 정보시스템을 구축하기 위한 데이터 관점의 업무 분석 기법
• 현실세계의 데이터에 대해 약속된 표기법에 의해 표현하는 과정
• 데이터베이스를 구축하기 위한 분석/설계의 과정

 

데이터 모델링의 중요성

• 파급효과
• 복잡한 정보 요구사항의 간결한 표현
• 데이터 품질

 

데이터 모델링 유의점

• 중복 : 중복 없어야함
• 비유연성 : 유연해야함
• 비일관성 : 일관성있어야함

 

데이터 모델링 단계 (개. 논. 물)

• 개념적 모델링 : 추상적, 요구사항 분석, 업무중심적이고 포괄적
• 논리적 모델링 : 가장 핵심, 정규화, key 속성 관계등 표현, 재사용성 높음
• 물리적 모델링 : 구체적, 하드웨어적, 실제 데이터베이스에 어떻게 표현할것인가

 

데이터 독립성의 필요성

• 유지보수 비용
• 데이터 복잡도
• 데이터 중복성
• 요구사항 대응

데이터 독립성 요소 (외. 개. 내) _ 논리적, 물리적

• 외부스키마 : 사용자 관점, 뷰 관점, 개개인의 사용자나 프로그래머
• 개념스키마 : 모든 사용자 관점 통합, 조직 전체 DB, 통합 관점
• 내부스키마 : 물리적 저장장치에 실제 저장
• 논리적 독립성 : 개념 스키마가 변경되어도 외부 스키마에 영향 X
• 물리적 독립성 : 내부 스키마가 변경되어도 개념 스키마에 영향 X, 저장장치의 구조 변경

 

ERD : 피터첸이 만든 데이터 모델 표기법

ERD 작성 순서 : 엔티티 그리기 → 엔티티 배치 → 엔티티간 관계 설정 → 관계명 기술 → 참여도 기술 → 필수여부 기술

• 가장 중요한 엔티티를 왼쪽 상단에
• 선택 여부는 ㅇ로 표현

2. 엔티티

엔티티 : 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합적인 것, 인스턴스의 집합

 

엔티티의 특징

• 반드시 해당 업무에서 필요하고 관리하고자 하는 정보여야함
• 유일한 식별자에 의해 식별가능
• 영속적으로 존재하는 인스턴스의 집합 → 인스턴스가 2개 이상
• 업무 프로세스에 의해 이용되어야함
• 반드시 속성이 있어야함
• 다른 엔티티와 최소 1개 이상의 관계가 있어야함

엔티티의 종류, 분류

• 유무형에 따른 분류
  • 유형 엔티티 : 물리적 형태 O, 안정적이고 지속적 사용 → ex) 사원, 물품, 강사
  • 개념 엔티티 : 물리적 형태 X, 관리해야할 개념적 정보 → ex) 조직, 보험상품
  • 사건 엔티티 : 업무를 수행함에 따라 발생되는 엔티티 → ex) 주문, 청구, 미납
• 발생시점에 따른 분류
  • 기본 엔티티 : 업무에 원래 존재하는 정보, 독립적, 부모 역할
  • 중심 엔티티 : 기본엔티티로부터 발생, 중심적 역할
  • 행위 엔티티 : 두개 이상의 부모엔티티로부터 발생
• 엔티티의 명명
  • 현업업무에서 사용하는 용어
  • 약어 X
  • 단수명사
  • 유일성
  • 생성의미대로 이름 부여

3. 속성

속성 : 업무에서 필요로 하는 인스턴스로 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위

• 한 개의 엔티티는 두 개 이상의 인스턴스의 집합
• 한 개의 엔티티는 두 개 이상의 속성을 가짐
• 한 개의 속성은 한 개의 속성값을 가짐

속성 분류

특성에 따른 분류

• 기본속성 : 업무로부터 추출한 모든 속성, 가장 일반적이고 많음
• 설계속성 : 업무상 필요한 데이터 이외에 데이터 모델링을 위해, 규칙화를 위해 새로 만든 속성
• 파생속성 : 계산된 값

엔티티 구성방식에 따른 분류

• PK 속성 / FK 속성 / 일반 속성 → PK, FK에 해당 안될시
• 복합 속성 / 단순 속성
• 다중값 속성 / 단일값 속성

속성의 명명

• 해당 업무에서 사용하는 이름 부여
• 서술식 속성명 사용 X
• 약어 사용 X
• 전체 데이터모델에서 유일성 확보

도메인 : 속성이 가질 수 있는 값의 범위

 

4. 관계

관계 분류

	존재에 의한 관계	행위에 의한 관계
UML 관계	연관관계 : 항상 이용하는 관계	의존관계 : 상대방 클래스의 행위에 의해 관계가 형성
소스코드	멤버변수	메소드에서 파라미터
클래스 다이어그램에서의 표현	실선	점선

 

ERD에서는 실선과 점선 구분 X

 

관계 표기법

• 관계명 → 애매한 동사를 피한다, 현재형
• 관계차수 : 1대1, 1대M 등..
• 관계선택사양 : 필수 / 선택

관계 체크사항

• 두 엔티티 사이에 관심있는 연관규칙이 존재하는가?
• 두 엔티티 사이에 정보의 조합이 발생되는가?
• 업무기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는가?
• 업무기술서, 장표에 관계연결을 가능하게 하는 동사가 있는가?

5. 식별자

식별자의 특징

• 유일성 : 주식별자에 의해 엔티티내의 모든 인스턴스들이 유일하게 구분되어야함
• 최소성 : 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수
• 불변성 : 주식별자의 값은 자주 변하지 않는 것이여야 함
• 존재성 : 주식별자가 지정이 되면 반드시 값이 들어와야함 → null X

식별자의 분류

대표성 여부	주식별자	각 튜플 구별가능한 구분자, 타 엔티티와 참조 관계 연결가능
	보조 식별자	각 튜플 구별가능한 구분자이지만 타 엔티티와 참조관계 연결 불가
생성 방식	내부식별자	엔티티 내부에서 스스로 만들어짐
	외부식별자	타 엔티티와의 관계를 통하여 타 엔티티로부터 받아오는 식별 ( A.사원번호(PK) 가 B.사원번호(FK) )
속성의 수	단일식별자	하나의 속성
	복합식별자	둘 이상의 속성
대체여부	본질식별자	실제 업무에 필요하여 만들어짐
	인조식별자	관계 구성을 위해 인위적으로 만들어짐

 

식별자 관계, 비식별자 관계

구분	식별자 관계	비식별자 관계
목적	강한 연결관계 표현	약한 연결관계 표현
자식 주식별자 관계	자식 주식별자의 구성에 포함	자식 일반속성에 포함
표기법	실선	점선
기타 고려사항	반드시 부모 엔티티에 종속 SQL 조인 최소화	약한 종속관계 자식 주식별자 구성 독립적 부모쪽의 관계 참여 선택적 부모없이 자식 생성가능 부모가 먼저 소멸할 경우에도 사용 가능

 

2장 데이터 모델과 SQL

1. 정규화

정규화 : 입력/수정/삭제/ 성능은 향상, 조회성능은 향상될수도 저하될수도

• 1차 : 모든 속성은 반드시 하나의 값을 가져야함 → 원자값
• 2차 : 엔티티의 일반속성은 주식별자 전체에 종속 → 완전 종속 = 부분 함수 종속 제거
• 3차 : 엔티티의 일반속성 간에는 서로 종속 X → 이행 종속 제거
• BCNF : 결정자 후보키가 아닌 종속 제거
• 후보키란 기본키에 속하는 키를 말함 → 즉, 기본키에 속하지 않는 속성이 다른 속성을 결정하면 안됨
• 4차 : 다치 종속 제거
• 5차 : 조인 종속 제거

반정규화 : 정규화된 엔티티, 속성, 관계에 대해 성능향상과 개발운영의 단순화를 위해 중복, 통합 분리 등을 수행하는 기법 → 조회시 성능 향상

 

2. NULL 속성의 이해

• null: 모르는 값 →  0이나 공백과는 다름
• not null, pk로 정의되지 않은 데이터 유형은 null 값 포함 가능
• null + 연산자 결과 → null 
• null 비교 결과 → unknown 또는 false
• NVL(oracle) = ISNULL(sql server) _같은 의미

 

2과목 SQL 기본 및 활용

1장 SQL 기본

1. 관계형 데이터베이스 개요

• DML 데이터 조작어 : select / insert / update / delete
• DDL 데이터 정의어 : create / alter / drop / rename / truncate
• DCL 데이터 제어어 : grant / revoke
• TCL 트랜잭션 제어어 : commit / rollback

2. SELECT 문

순서 : FROM → WHERE → GROUP BY → HAVING → ORDER BY

구문 : SELECT [ALL/DISTINCT] 컬럼 FROM 테이블

 

뒤에 나오는 WHERE 부터는 선택이나 SELECT, FROM은 필수

SELECT List에 서브쿼리 사용가능

 

3. 함수(FUNCTION)

 

문자형

• LOWER : 문자열을 소문자로
• UPPER : 문자열을 대문자로
• ASCII : 문자나 숫자의 ASCII 값 반환
• CHR : ASCII 값에 해당하는 문자 반환
• CONCAT : 문자열1, 2를 연결
• ex) CONCAT(‘RDBMS’,‘ SQL’) → ‘RDBMS SQL’
• || : 문자 연결  
  • e.g. A || 2 = A2
• SUBSTR : 문자열 중 m위치에서 n개의 문자 반환 → 이때 0부터시작아님 주의!
  • e.g. SUBSTR(‘SQL Expert’,5,3) → ‘Exp’
• LENGTH : 문자열 길이를 숫자 값으로 반환
• LTRIM : 왼쪽부터 확인해서 지정문자 나타나면 제거
• ex) LTRIM(‘xxxYYZZxYZ’,‘x’) → ‘YYZZxYZ’
• TRIM : 양 끝 확인해서 지정문자 나타나면 제거
• ex) (‘x’ FROM ‘xxYYZZxYZxx’) → ‘YYZZxYZ’
• REPLCAE(문자열, 찾는문자열, [변경할문자열]) : 변경할문자 미입력시 제거
• <> 비교 → is not 같은 느낌

숫자형

• SIGN(숫자) : 숫자가 양수면1 음수면-1 0이면 0 반환
• MOD(숫자1, 숫자2) : 숫자1을 숫자2로 나누어 나머지 반환 → 숫자2가 0이면 숫자1반환
• CEIL(숫자) : 올림
• FLOOR(숫자) : 내림
• ROUND(38.5235,3) -> 38.524 다음 자리에서 반올림
• TRUNC(38.5235,3) -> 38.523 해당자리까지 남기고 버림

날짜형

• SYSDATE : 현재날짜와 시각 출력
• EXTRACT : 날짜에서 데이터 출력
• TO_DATE(’날짜’, ‘YYYY.MM.DD HH24:MI:SS’) → 문자를 날짜로 변환.
  • 만약 MM까지만 있거나 하면 뒤는 자동으로 1일, 0시, 0분..이런식으로 바뀜
  • 1 = 하루, 1/24 = 1시간, 1/24/60 = 1분

집계함수

• COUNT
• SUM
• AVG
• MIN
• MAX
• VARIANCE 분산
• STDDEV 표준편차

NULL 함수 및 치환함수

• NVL(인수1, 인수2) : 인수1이 NULL일 경우 인수2반환, 아니면 인수1 → SQL Sever의 ISNULL과 동일
• NVL2(a,b,c) : a가 null이 아니면 b 맞으면 c 반환
• NULLIF(인수1, 인수2) : 인수1==인수2이면 NULL반환, 아니면 인수1
• COALESCE(인수1, 인수2, 인수3…) : NULL이 아닌 최초의 인수 반환
• CASE 구문 & WHEN 조건 THEN & ELSE 처리 : 별도의 ELSE가 없으면 NULL이 ELSE의 디폴트가됨

[CASE 구문 예시]

CASE WHEN 조건 = ‘a’ THEN ‘b’

== CASE 조건 WHEN ‘a’ THEN ‘b’ (단순표현, =만 있는 경우)

== DECODE (조건, ‘a’, ‘b’) : ELSE NULL 생략 → (값1, 리턴1, 값2, 리턴2…., ELSE값)

(CASE문에서는 alias 쓸수없음: SELECT 절 이전에 처리되는 부분에서는 alias 사용x)

 

4. WHERE 절

구성요소

• 컬럼명
• 비교연산자
• 문자, 숫자, 표현식
• 비교 컬럼명(JOIN 사용시)

특징

• ISNULL, IS NOT NULL 사용하여 NULL 비교
• 처리 우선순위 : AND > OR
• 집계함수 사용 x

5. ORDER BY 절

ORDER BY 절에 칼럼명 대신 alias 나 컬럼 순서 나타내는 정수도 사용 가능

오름차순(ASC) 기본값으로 적용. DESC 명시하여 내림차순 정렬 처리 가능.

 

c.f. Oracle에서는 NULL을 가장 큰 값으로 취급

SQL Server에서는 NULL을 가장 작은 값으로 취급

 

6. GROUP BY, HAVING 절

GROUP BY 절을 통해 소그룹별 기준을 정한 후, SLECT 절에 집계합수 사용

NULL값도 그룹화 가능.

GROUP BY 절에서는 SELECT 절과 달리 ALIAS 사용 불가

집계 함수는 WHERE 절에는 올 수 없음.

WHERE절이 GROUP BY보다 선행하여 실행

 

GROUP BY 절에 의해 그룹화된 데이터 중 HAVING 절 만족하는 내용만 출력

WHERE 조건절은 GROUP BY 의 HAVING 조건보다 선행하여 실행한다.

 

7. 조인 (JOIN)

두 개 이상의 테이블들을 연결 또는 결합하여 데이터 출력하는 것

일반적인 경우 PK나 FK 값의 연관에 의해 조인 성립 → 관계가 없어도 조인 성립하는 경우도 존재

DBMS 옵티마이저는 항상 2개씩 테이블 짝을 지어 조인함 → ex) A join B joind C는 에러

N개의 테이블로부터 원하는 데이터를 조회하려면 최소 N-1개의 조인 조건이 필요하다

• USING 조건절 : JOIN 컬럼에 대해서 ALIAS 나 테이블 이름 붙일 수 없음 → equi, natural등에서 사용

 

 

INNER JOIN (EQUI JOIN)

구문:  SELECT - FROM - WHERE 테이블1.컬럼1 = 테이블2.컬럼2;

→ ANSI/ISO SQL 표준 : SELECT FROM A. INNER JOIN B. ON A.컬럼 = B.컬럼

두 테이블의 중복되는 속성이 같은것만 남기는 조인 → 이때 속성이 두번나오게 되므로 하나를 제거하면 natural join

(내추럴은 중복속성 제거의미)

‘=’ 연산자 이외의 연산자 사용시 모두 NON EQUI JOIN

 

OUTER JOIN

LEFT / RIGHT / FULL

1. LEFT OUTER JOIN: 좌측 테이블 먼저 가져오고 우측 테이블에서는 조인된 데이터만 불러옴.

만약 우측테이블에 데이터가 없는 경우에는 NULL로 채운다.

2. RIGHT OUTER JOIN: LEFT OUTER JOIN의 반대로 실행

3. FULL OUTER JOIN: 모든 데이터를 읽어 조인하여 결과 생성

(INNER JOIN 가능한 것들 + 불가능한 것들은 NULL로 채운 값 _ 합집합)

 

NATURAL JOIN

두개 테이블에 공통인 행만 남기는것

NATURAL JOIN은 ON절 사용 불가

오라클에서는 JOIN, USING으로 NATURAL JOIN 가능 e.g. FROM A JOIN B USING (컬럼1);

 

[ORACLE, SQL Server 비교]

1. ORACLE

 1) 공백은 null로 인식. ' ' = null

 2) NVL

 3) 오라클에서는 OUTER JOIN을 (+) 기호로도 처리가능

→ ANSI 문장으로 변경하려면 left/right 명시와 on절을 추가해줘야함

 4) auto commit mode: off

 

예시 구문)

where A.ID = B.ID(+)  -- (+)붙은 반대쪽이 주대상이됨

AND B.이름(+) = 'K' -- (+)가 붙은 조건은 ON절에 포함

== (동일)

FROM A LEFT OUTER JOIN B

ON (A.ID = B.ID AND B.이름 = 'K')

 

2. SQL Server

 1) 공백 ' ' 은 null x

 2) ISNULL

 3) DML, DDL auto commit 기본값

 

DUAL 테이블

사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블

SELECT ~ FROM ~의 형식을 갖추기위한 일종의 더미 테이블

DUMMY라는 문자열 유형의 칼럼에 ‘x’라는 값이 들어있는 행을 1건 포함

 

SELECT 문장 실행 순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY (ALIAS 사용가능)

 

2장 SQL 활용

1. 서브쿼리

서브 쿼리 : 하나의 SQL문안에 포함되어 있는 또 다른 SQL문, 알려지지 않은 기준을 이용한 검색에 사용.

• 서브쿼리를 괄호로 감싸서 사용한다.
• 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수행 비교 연산자는 결과 건수와 상관없다.
• 서브쿼리에서 ORDER BY 사용 불가
• SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에서 사용 가능

스칼라 서브쿼리 : 컬럼을 반환하는 서브쿼리. 주로 SELECT에서 사용 → FROM절에는 올수없다.

인라인 뷰 (다이나믹 뷰) : FROM 절에서 사용되는 서브쿼리 → 메인쿼리에서 사용가능. ORDER BY를 사용 가능

 

중첩 서브 쿼리 → WHERE, HAVING

동작 방식에 따른 서브쿼리 분류

• 비연관 서브쿼리 : 서브쿼리가 메인쿼리 칼럼 안가짐. 메인쿼리에 값 제공 목적
• 연관 서브쿼리 : 서브쿼리가 메인쿼리 칼럼 가짐

반환 데이터에 따른 서브쿼리 종류

• 단일행 서브쿼리 : 실행결과 1건 이하
• → 단일 행 비교 연산자 : =,<,>,<> 등
• 다중행 서브쿼리 : 실행결과 여러 건
  • WHERE COL1 IN(’A’, ‘B’)는 COL1 = ‘A’ or COL1 = ‘B’를 의미함. 그래서 IN안에 null이 와도 무시됨
  • WHERE COL1 NOT IN(a, b) 은 col1 ≠ a and col1 ≠ b의미. 그래서 NULL 포함되면 항상 거짓
  • EXIST는 True / False 반환 → WHERE EXIST (서브쿼리)
  • WHERE COL1 = ALL (서브쿼리)
• → 다중 행 비교 연산자 : IN, ALL, ANY, SOME 등 → 단일 행 비교 연산자로도 사용가능
• 다중컬럼 서브쿼리 : 실행결과 컬럼 여러 개 → SQL Server에서는 X

뷰 View

뷰는 정의만 갖고있으며 실행 시점에 쿼리 재작성

실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다

 

장점

• 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다
• 편리성 : 복잡한 질의를 뷰로 생성하여 질의 단순하게 가능. 또한 해당 SQL 자주 사용시 뷰를 이용하면 편리
• 보안성 : 직원의 급여정보같이 숨기고 싶은 정보 빼고 뷰 생성 가능

2. 집합 연산자

UNION: 중복행 제거. 합집합

UNION ALL: 중복행 포함 모든 행 반환

INTERSECT: 교집합

EXCEPT (ORACLE: MINUS): 첫번째 SELECT 문의 결과에서 두번째 SELECT문을 뺀 차집합

 

3. 그룹 함수

ROLLUP: 소계와 총계 생성을 위한 함수. 컬럼 순서에 따라 결과값이 달라질 수 있음. (c.f. cube, grouping set은 순서상관 x)

SELECT COL1, SUM(COL2)
FROM T1
GROUP BY ROLLUP(COL1)
-- COL1 별 그룹핑 + 총계
GROUP BY ROLLUP(COL1, COL2)
-- COL1, COL2 별 그룹핑 + COL1별 그룹핑 + 총계
GROUP BY ROLLUP(COL1, COL2, COL3)
-- COL1, COL2, COL3 별 그룹핑 + COL1, COL2별 그룹핑 + COL1 별 그룹핑 + 총계
GROUP BY ROLLUP((COL1, COL2), COL3)
-- COL1, COL2, COL3 별 그룹핑 + COL1, COL2별 그룹핑 + 총계

 

CUBE: 모든 경우의 수에 대해 소계와 총계 생성. ROLLUP에 비해 시스템 부하 심함

SELECT COL1, SUM(COL2)
FROM T1
GROUP BY CUBE(COL1)
-- COL1 별 그룹핑 + 총계
GROUP BY ROLLUP(COL1, COL2)
-- COL1, COL2 별 그룹핑 + COL1별 그룹핑 + COL2별 그룹핑 + 총계
GROUP BY ROLLUP(COL1, COL2, COL3)
-- COL1, COL2, COL3 별 그룹핑 + COL1, COL2별 그룹핑 + COL2, COL3 + COL1, COL3 + COL1 + COL2 + COL3 + 총..
GROUP BY ROLLUP((COL1, COL2), COL3)
-- COL1, COL2, COL3 별 그룹핑 + COL1, COL2별 그룹핑 + COL3별 그룹핑 + 총계

 

GROUPING SETS: 각 대상 마다 그룹핑한 결과값 출력 (총계 x)

GROUPING SETS(COL1) -- GROUP BY COL1과 동일
GROUPING SETS(COL1, COL2) -- COL1 그룹 + COL2 그룹

GROUPING SETS(COL1, COL2, ())
GROUPING SETS(COL1, ROLLUP(COL2)) 
-- 위의 두개 동일
-- COL1 그룹 + COL2 그룹 + 총계

 

GROUPING 함수: 시행에 참여하지 않은(NULL) 값은 1, 그렇지 않은 경우는 0 으로 치환.

→ NULL로 되어있는 값들을 소계, 총계로 표시해주기 위한 방안 (CASE문 활용)

 

4. 윈도우 함수

순위 관련

• RANK : 동일한 값에 대해서는 동일한 순위를 부여 (1,2,2,4)
• DENSE_RANK : 동일한 순위를 하나의 등수로 간주 (1,2,2,3)
• ROW_NUMBER : 그냥 처음 ~ 마지막까지 고유한 순위 부여, 중복 X

행 순서 관련 - SQL Server 지원 X

• FIRST_VALUE : 파티션별 윈도우의 처음 값
• LAST_VALUE : 파티션별 윈도우의 마지막 값
• LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값
• LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값

비율 관련

• RATIO_TO_REPORT : 파티션 내 전체 SUM에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. >0, <=1
• PERCENT_RANK : 파티션별 윈도우에서 처음 값을 0, 마지막 값을 1로 하여 행의 순서별 백분율을 구한다. 값은 상관 X. 0>=,<=1
• CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다. >0, <=1. 이것도 값은 상관 X
• NTILE : (그룹을 나눠주기 위한 함수) 파티션별 전체 건수를 인수 값으로 N등분한 결과를 구할 수 있다. → 예를 들어 NTILE(5)이고 전체 행 수가 12개이면 1이 3개, 2가 3개, 3,4,5가 2개씩 배분.
  • 총 행의 수가 명확하게 나눠지지 않을 때 앞 그룹의 크기가 더 크게 분리

ROWS, RANGE

ROWS는 행 단위, RANGE는 집합(PARTITON) 단위라고 보면됨

RANGE BETWEEN start_point AND end_point

• CURRENT ROW 현재행
• UNBOUUNDED PRECEDING : 첫번째 행. (end point에서 사용불가)
• UNBOUUNDED FOLLOWING : 마지막 행. (start point에서 사용불가)

맨 처음 행과 맨 마지막 행을 설정하는 것.

*기본값: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

 

 

5. Top N 쿼리

ROWNUM: 번호를 매기는 내장함수.

현재 저장된 데이터를 그대로 두면서 각 행에 순차적인 번호를 부여

테이블 첫 행부터 차례대로 순회. 중간에 건너뛰는것 X

단, WHERE절에 ROWNUM 사용한 조건식이 FALSE가 되면 순회를 멈추고 결과를 반환함

 

e.g. SELECT ROWNUM, EMPNO, ENAME, SAL

FROM EMP

WHERE ROWNUM = 5;

→ 결과값은 아무것도 출력 되지 않는다.

ROWNUM은 1부터 순차적으로 전개되야 하는데, WHERE 조건에 ROWNUM = 5 라고 선언했기 때문. 

 

ORDER BY와 ROWNUM와 함께 사용할 때는 서브쿼리를 사용해야 함.

→ ORDER BY가 ROWNUM 실행 이후에 적용되기 때문에 순서가 뒤죽박죽 됨.

 

FETCH 절

출력될 행의 수를 제한하는 절

ORDER BY 절 뒤에 사용

[구문]

OFFSET: 건너뛸 행의 수 (필수 X)

N: 출력할 행의 수

FIRST: OFFSET 쓰지 않았을때 처음부터 N 행 출력 명령

NEXT: OFFSET 썼을때 제외한 행 다음부터 N개 행 출력

 

TOP N 쿼리

SQL Server 에서의 상위 N개 행 추출 문법

WITH TIES 사용하여 동순위 함께 출력 -> 출력 개수가 지정 등수보다 많을 수 있음

WITH TIES

 

 

6. 계층형 쿼리

계층형 쿼리: 테이블 내 두개의 컬럼 간에 계층 관계가 존재하는 경우 데이터를 조회하기 위해 사용

프. 자. 부. 순: prior 자식 = 부모 의 경우 순방향 전개

프. 부. 자. 역: prior 부모 = 자식 의 경우 역방향 전개

 

START WITH -- 계층 구조 전개의 시작 위치 지정. 루트(ROOT) 결정
CONNECT BY -- 다음에 전개될 자식 데이터 지정
PRIOR 
-- CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다. 
-- PRIOR 자식 = 부모 형태를 사용하면 (부모->자식) 방향으로 전개하는 순방향 전개를한다. 
-- PRIOR 부모 = 자식은 역방향
-- PK항목(not null, 유니크)과 부모항목(null가능)으로 구성됨
NOCYCLE -- 동일한 데이터가 전개되지 않음
ORDER SIBLINGS BY -- 형제 노드간의 정렬 수행 순서
WHERE -- 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

 

 

7. PIVOT 절과 UNPIVOT 절

[배경] 실제 업무에서 사용하는 가시성있는 테이블(보고서에 자주쓰는 테이블)과 집계함수 사용 가능한 테이블은 다르기 때문에 사용.

PIVOT: 행(row, 속성값)을 열(column, 헤더)로 바꾼다. 지정된 컬럼의 각 행 속성값들이 새로운 컬럼 헤더가 되고 이에 맞게 전체 속성값들이 재배치 → e.g. 각 직책이 부서별로 몇 명씩 있는지 etc 

 

[구문 예시]

SELECT *

FROM (SELECT E.JOB, D.DNAME

             FROM EMP E.DEPTNO = D.DEPTNO)

PIVOT (COUNT(*) FOR DNAME IN ('ACCOUNTING' AS ACCOUNTING,

                                                            'RESEARCH' AS RESEARCH,

                                                            'SALES' AS SALES));

 

*PIVOT 절은 내부적으로 첫번째 컬럼에 대하여 GROUP BY 연산을 포함하고 있음

각 속성값들이 PIVOT을 거치면 각각의 컬럼으로 지정된다.

 

UNPIVOT: PIVOT과 반대로 열을 행으로 바꾼다. 컬럼 헤더들이 한 컬럼의 각 행 속성값이 되고 이에 맞게 전체 속성값들이 재배치

 

[구문 예시]

SELECT 계절, 연도, 기온

FROM (SELECT * FROM 평균기온)

UNPIVOT (기온 FOR 연도 IN (Y2018 AS '2018년'

                                                Y2019 AS '2019년'

                                                Y2020 AS '2020년'

                                                Y2021 AS '2021년'

                                                Y2022 AS '2022년' ));

 

c.f. IN 연산자

id IN (1, 2); 

==

id = 1 OR id = 2;

 

 

8. 정규표현식 (Regular Expression)

- 문자열의 공통된 규칙을 보다 일반화 하여 표현하는 방법

 

메타문자: 문자가 가진 본래 의미가 아니라 문법적인 특별한 의미로 사

리터럴 문자: 문자 그대로의 의미를 가짐

 

정규표현식 정의: 메타, 리터럴 문자로 된 형식을 갖고 문자열의 특정 패턴을 찾아 내는 것

 

주요 메타 문자

[] : 대괄호 속 문자 중 하나와 일치

- : 연속 문자의 범위

[^] : 대괄호 속 문자들을 제외한 나머지 문자들중 하나와 일치

^ : 문자열 시작 지점

$ : 문자열 끝 지점

. : 임의의 한 문자 e.g. a.b: acb, a-b, a1b etc..

 

 

3장 관리 구문

1. DML

DELETE / DROP / TRUNCATE 차이점

DROP	TRUNCATE	DELETE
DDL	DDL	DML
Rollback X	Rollback X	Rollback O
Auto Commit	Auto Commit	사용자 commit
테이블이 사용했던 Storage 모두 Release	최초 테이블 생성시 할당된 Storage만 남기고 Release	데이터를 모두 삭제해도 Storage Release 안됨
데이터와 구조 동시 삭제	데이터만 초기화	데이터 일부 또는 전체 삭제
	UNDO 데이터생성X → 빠름	UNDO 데이터생성 → 느림

 

DISTINCT:  동일한 데이터 중복으로 처리하여 제거

• DISTINCT (A || B) : A와 B를 중복제거하여 붙여서 문자열로 만듬 → 합성 연산자

DELETE(MODIFY) 액션

• CASCADE : 연쇄
• RESTRICT(디폴트)
• NO ACTION : restrict랑 같음
• SET NULL : 참조하고 있는 테이블 삭제될시 값 Null로
• SET DEFAULT

INSERT 액션 : 자식테이블에 FK 입력시 부모테이블에 PK가 없는 경우

• AUTOMATIC : MASTER PK 생성 후 child 입력
• SET NULL : Child 키를 null로
• SET DEFAULT
• DEPENDENT : 부모테이블에 PK가 존재할때만 child 입력허용
• NO ACTION

2. TCL

COMMIT, ROLLBACK, SAVEPOINT

 

트랜잭션 : 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기위한 작업의 기본 단위

• Atomicity 원자성 : All or Nothing
• Consistency 일관성 : 트랜잭션 실행전에 DB 내용이 잘못되어있지않으면 실행후에도 잘못된 내용이 있으면 안됨
• Isolation 고립성 : 트랜잭션 실행 도중에 다른 트랜잭션 영향받으면 X
• Durability 영속성 : 트랜잭션이 성공하면 갱신한 내용이 데이터베이스에 영구 저장

3. DDL

CREATE, ALTER, DROP, TRUNCATE, RENAME

 

[제약조건의 종류]_ 참조 무결성

 

 

[기타 참고사항]

SQL 표준 데이터타입

• VARCHAR, CHAR, VARCHAR2 → TEXT는 없음
  • VARCHAR은 비교시 서로 길이가 다를경우 다른 내용으로 판단
  • CAHR은 길이가 다르면 짧은쪽에 공백 추가해서 비교
• DATE : SYSDATE, ‘20240812’ (O), 20240812 (X) → 숫자 입력 불가

테이블 생성 후 복제(CTAS) → 제약조건 중 NOT NULL만 가져올수있음

SQL : 구조적, 집합적, 선언적 → 절차적 X

NULLS LAST : 널값을 가장 큰것으로

count(*)는 null값도 센다 c.f. count(A.컬럼)의 경우 null 카운트 x