방통대 다니고 있는데 졸업하려면 자격증을 하나 꼭 따야했고, 마침 회사의 기획자친구와 백엔드친구들과 함께 스터디를 할 수 있는 기회가 생겨 SQLD 개발자를 따 보기로 했다. 이때가 6월 말. 시험은 9월 초여서 대충 2달 반 정도 남은 셈이 되었다.
교재는 아래의 것으로 했다. 업무관련한 책은 회사에서 구입해주는데 원래는 다 보고 회사에 비치하는 것이 규칙이지만 이사님한테 말씀드려서 우리끼리 자격증 스터디 하기로 했다니까 합격하고 책거리 하면 봐 주겠다고ㅋㅋㅋ 하셔서 회사에 신청해서 구입했다.
이기적 SQL 개발자 이론서 + 기출문제 (2023년용)
이 책으로 하기로 한 이유는... 그냥 대충 목차 봤더니 제일 끌려서였다. 원래 노랑이가 국룰이라고들 하는데 어쩐지 우리는 이것을 산 것이었다... 동영상 강의 포함이라고 했지만... 보지 않았죠?! 그래서 사실 퀄리티가 어땠는지는 모르겠다.
공부하던 중에 알게 됐는데 사람들이 졸업을 드럽게 못했던지, 방통대에서 졸업인정시험 관련한 걸 없애버리기로 했더라고... 지난 5월에 공지가 나고 2024학년도 2월 졸업 대상자들부터 그 면제를 해당받게 되었는데(= 나) 그래서 중간에 갑자기 솜사탕 씻은 너구리가 되어 버리고 말았다... 이거... 따도 필요없다고요? 안 따도 졸업이 된다고요? 굳이 딸 필요는 없고 오로지 온전히 스스로의 의지로 해 내야 한다고요? 갑자기 눈 앞이 깜깜해졌다...
백 번을 생각해도, 같이 공부하는 백엔드 친구들이야 당연히 한 번에 붙을 거고 심지어 나는 다음 회차 시험일이 친한 친구 결혼식 날이라서 그 때엔 죽어도 못 보고 그 다음 회차에나 볼 수 있었다. 그럼 시험일이 1월일텐데... 이걸 반 년을 공부하는 사람이 될 수는 없는 것이었음!!! 누구는 막!!! 일주일 공부하고 붙는다는데!!! 그래서 시험 10일 정도를 앞두고 노랑이 책도 사서 봤다.
처음에는 친구들이랑 같이 공부하면서 첫번째 책 이상한 부분이 많다고 짜증을 많이 냈는데 노랑이를 사 보고서 깨달았다. ㅇ ㅏ, 이론이라도 적혀있는 것이 양반이었구나!
노랑이는 완전 실전문제 타입이어서 문제 옆에 그 문제와 관련되는 이론이 적혀 있는 거지 이걸 처음부터 정리를 했다면 너무 힘들었을 것 같았다. 결국에는 두 권을 샀어야 하는 것 같음... 또는 다른 친구들처럼 첫 번째 책으로 그냥 버티거나.
그리고 인터넷에서 기출문제 찾아보면서 보니까 실전문제 정리를 잘 해서 올려주신 분도 있었다.
Study with yuna(SQLD 최신 기출 있는 블로그)
실전으로 시험보고 나서 생각하는 건데, 노랑이에 있는 문제들이 확실히 몇 개 정도는 아주 똑같이 나오기도 했다. 근데 그건 전체적으로 따지면 미미한 수준이고 무엇보다 진짜 어려운 문제들이 아니라 좀 간단한 문제들인 편이 많아서 오히려 기초공부를 했다면 문제가 같아서 맞는 게 아니라 그냥 공부를 열심히 해서 맞을 수 있을 것 같았다. 뭐든 다... 기초가 중요하다 결국...
회사는 MySQL을 쓰는데 SQLD 시험은 Oracle 기준이어서 Oracle에만 있는 메소드들은 내가 이래저래 해 봐야지 이해할 수 있을 것 같아서 오라클을 깔고 모르는 문제는 테이블부터 만들어서 내가 쳐보면서 공부했다.
설치하는 거야... 쳐보면 나오니까 생략하고 컴퓨터를 껐다 켜면 이게 다시 연결해주어야 하더라고! 이걸 몰라서 몇 번은 시간을 엄청 썼다. 그래서 적어놓기
- 컴퓨터 껐다 켜면
- docker 실행
- 터미널 창 켜서 `docker ps --all` 입력
- 여기서 오라클 돌아가고 있는지 보기
CONTAINER ID : 컨테이너 아이디
IMAGE NAMES : jaspeen/oracle-xe-11g
COMMAND : "/entrypoint.sh"
CREATED : 예전에 만들어짐(7 days ago 이런 식으로)
STATUS : Exited 인지 Up인지 확인
PORTS : 0.0.0.0:1521->1521/tcp, 0.0.0.0:8080->8080/tcp oracle
- 상태확인해보고
- 끊겨있으면 `docker restart CONTAINER ID`
- 다시 접속됐는지 확인 후
- DBeaver 가서 연결해보기
이건 내가 공부하면서 직접 정리한 부분이다. 그래봤자 어차피 교재 짜깁기지만...
그래도 이거 통으로 외우고 시험장 들어가면... 떨어지지는...? 않을 수도...? (저도 물론 좋은 점수를 받지는 못했습니다)
개념적 모델링
- 추상적 수준이 가장 높음
- 엔티티의 속성 정의, 개념적 ERD(Entity Relationship Diagram) 작성
- 개체와 개체들 간의 관계에서 ER다이어그램을 만드는 과정
논리적 모델링
- 릴레이션 정의
- 정규화를 수행하여 재사용성을 높인다
- 논리적 시스템으로 구축하고자 하는 업무에 대해 Key, 속성, 관계 등을 정확하게 표현하며 재사용성이 높다.
- ER다이어그램을 사용하여 관계 스키마 모델을 만드는 과정
물리적 모델링
- 성능, 보안, 가용성을 고려하여 데이터베이스 실제 구축
- 관계 스키마 모델의 물리적 구조를 정의하고 구현하는 과정
데이터 모델링을 할 때 정규화를 정확하게 수행
데이터베이스 용량산정 수행
데이터베이스에 발생되는 트랜잭션 유형 파악
용량과 트랜잭션의 유형에 따라 반정규화 수행
이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 수행
성능관점에서 데이터 모델 검증
데이터모델링 관점
데이터 모델링 시 유의점
데이터베이스
장점
-지역 자치성, 점증적 시스템 용량 확장
-신뢰성과 가용성
-효용성과 융통성
-빠른 응답속도와 통신비용 절감
-데이터의 가용성과 신뢰성 증가
-시스템 규모의 적절한 조절
-각 지역 사용자의 요구 수용 증대
단점
-소프트웨어 개발 비용
-오류의 잠재성 증대
-처리비용의 증대
-설계, 관리의 복잡성과 비용
-불규칙한 응답 소고
-통제의 어려움
-데이터의 무결성에 대한 위협
분산 DB 논리적으로 같은 시스템, 물리적 분산, 데이터 무결성 해침
분할 투명성 분할돼서 여러군데 저장
위치 투명성 저장장소 명시 불필요, 데이터베이스의 실제 위치를 알 필요없이 단지 데이터베이스의 논리적인 명칭으로 액세스 할 수 있다.
지역사상 투명성 지역 DBMS와 물리적 DB 사이 Mapping 보장
중복 투명성 데이터가 여러 곳에 중복되어 있더라도 사용자는 마치 하나의 데이터만 존재하는 것처럼 사용 가능하다.
장애 투명성 트랜잭션, DBMS, 네트워크, 컴퓨터 장애에도 트랜잭션을 정확하게 처리한다.
병행 투명성 다수의 트랜잭션이 동시에 실현되더라도 그 결과는 영향을 받지 않는다.
데이터 무결성을 보장하기 위한 방법
ERD(Entity Relationship Diagram) 작성절차
3층 스키마
외부 (사용자) 응용 프로그램이 접근하는 데이터베이스
뷰 단계 여러 개의 사용자 관점으로 구성 되어 있으며,
각 개인의 입장에서 필요로 하는 데이터베이스의 논리적 구조를 정의한 것
개념 (설계자) 통합 데이터베이스 구조
내부 (개발자) 물리적 저장 구조
엔티티(Entity)
엔티티 종류
속성
기본 속성
- 사원이름, 직책이름, 고용일자 등 가장 일반적인 속성
- 비즈니스 프로세스에서 도출되는 본래의 속성이다.
- 회원ID, 이름, 계좌번호, 주문일자 등이 있다.
설계 속성
- 업무상 필요한 데이터 외에 업무를 규칙화 하기 위해 속성을 새로 만들거나 변형하여 정의하는 속성
- 데이터 모델링 과정에서 발생되는 속성이다.
- 유일한 값을 부여한다.
- 상품코드, 지점코드 등이 있다.
파생 속성
- 다른 속성에 영향을 받아 발생하는 속성, 계산된 값들이 여기에 해당 (예: 총 결제금액 등)
- 다른 속성에 의해서 만들어지는 속성이다.
- 합계, 평균 등이 있다.
도메인
관계
|
로 선택적 관계는 o
로 표시O = or (있을수도 있고 or 없을수도 있다.)
관계표기법
ERD 표기법 중 IE 표기법은 관계의 1:N 관계에서 N쪽에 새발을 표시하고 선택, 필수 참여관계에서 선택 참여(or)에 O, 필수 참여에 | 로 표시한다.
카디널리티
식별관계
비식별관계
식별자 관계 | 비식별자 관계 |
---|---|
독립적으로 존재할 수 있는 엔티티를 “강한 개체(Strong Entity)”라고 함 | 약한 개체(Weak Entity) |
부모 엔티티로부터 속성을 받아 자식엔티티의 주식별자로 사용하는 경우 | 부모 엔티티로부터 속성을 받았지만 자식엔티티의 주식별자로 사용하지 않고, 일반적인 속성(즉, 일반 속성 외부식별자)으로 사용하는 경우 |
식별자 관계에서 Null값이 오면 안되므로 반드시 부모엔티티가 생성되어야 자기 자신의 엔티티가 생성됨 | 약한 종속 관계로 자식 주식별자 구성을 독립적으로 구성 |
부모로부터 받은 속성을 자식엔티티가 모두 사용하고 그것만을 주식별자로 사용한다면 부모엔티티와 자식엔티티의 1:1 관계 | 상속받은 주식별자속성을 타 엔티티에 차단 필요 |
부모로부터 받은 속성 이외에, 다른 부모로 부터 받은 속성을 포함하거나 스스로 가지고 있는 속성과 함께 주식별자로 구성되는 경우는 1:M 관계 | 부모쪽의 관계참여가 선택관계 |
식별자 관계로만 관계를 구성하면 조인조건의 개수가 많아지기 때문에, 개발자 복잡성과 오류가능성을 유발시킬수 있는 요인이 될 수 있다. | 비식별자 관계로만 관계를 구성하면 많은 조인이 걸리게 되고, 그에 따라 복잡성이 증가하고 성능이 저하되는 문제가 생길 수 있다. |
실선으로 표현 | 점선으로 표현 |
식별자 분류 | 식별자 | 설명 |
---|---|---|
대표성 여부 | 주식별자 | 엔터티 내에서 각 행을 구분할 수 있는 구분자이며, 타 엔터티와 참조관계를 연결할 수 있는 식별자 (ex. 사원번호, 고객번호) |
보조식별자 | 엔터티 내에서 각 행을 구분할 수 있는 구분자이나 대표성을 가지지 못해 참조관계 연결을 못함(ex. 주민등록번호) | |
스스로 생성여부 | 내부식별자 | 엔터티 내부에서 스스로 만들어지는 식별자(ex. 고객번호) |
외부식별자 | 타 엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자(ex. 주문엔터티의 고객번호) | |
속성의 수 | 단일식별자 | 하나의 속성으로 구성된 식별자(ex. 고객엔터티의 고객번호 ) |
복합식별자 | 둘 이상의 속성으로 구성된 식별자(ex. 주문상세엔터티의 주문번호+상세순번) | |
대체여부 | 본질식별자 | 업무(비즈니스)에 의해 만들어지는 식별자(ex. 고객번호) |
인조식별자 | 업무적으로 만들어지지는 않지만 원조식별자가 복잡한 구성을 가지고 있기 때문에 인위적으로 만든 식별자(ex. 주문엔터티의 주문번호(고객번호+주문번호+순번)) |
기본키 Primary Key
후보키 Candidate Key 유일성, 최소성
슈퍼키 Super Key 유일성
대체키 Alternate Key 후보키 중 기본키를 선정하고 남은 키
외래키 Foreign Key 참조무결성(referential integrity)을 확인하기 위해 사용되는 키
정규화 Normalization
정규화 과정
제1정규화 - 속성의 원자성 확보
- 기본키 설정
제2정규화 - 기본키가 2개 이상의 속성으로 이루어진 경우 부분함수 종속성을 제거
- 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속된 상태
제3정규화 - 기본키를 제외한 칼럼 간의 종속성을 제거
- 이행함수 종속성을 제거
BCNF - 복수의 후보키가 있고, 후보키들이 복합속성이어야 하며, 서로 중첩되어야 함
제4정규화 - 여러 컬럼들이 하나의 칼럼을 종속시키는 경우 분해하여 다중값 속성을 제거
제5정규화 - 조인에 의해서 종속성 발생되는 경우 분해
데이터베이스의 성능향상을 위하여 데이터의 중복을 허용하고 조인을 줄이는 데이터베이스 성능 향상 방법
조회(SELECT)의 속도를 향상시키지만 데이터 모델의 유연성은 낮아짐
데이터 모델의 유연성은 새로운 모델 생성, 기존 모델 변경 등의 변화를 최소화하면서 변경된 업무 요건이나 신규 업무를 빠르고 정확하게 반영할 수 있는 성질을 말한다.
유연한 모델의 특징은 업무 요건이 변경되더라도 애플리케이션만 조금 손보면 된다는 것이다. CRM, ERP 같은 패키지 솔류션은 대부분 추상화 수준을 높여 유연성을 극대화한 모델로 구현되어 있다.
데이터 모델 유연성의 핵심은 데이터를 (속성 수준이 아닌) 행 수준으로 저장하는 데 있다. 컬럼 추가(=구조 변경) 없이 새로운 인스턴스(값)을 추가해서 업무 변경에 대응하는 것이다. 이렇게 값을 추가하는 방식으로 전환함으로써 구조 변경을 최소화하는 것이다.
변환방법 | 설명 |
---|---|
One To One Type | 슈퍼타입과 서브타입을 개별 테이블로 도출한다. 테이블의 수가 많아서 조인이 많이 발생하고 관리가 어렵다. |
Plus Type | 슈퍼타입과 서브타입 테이블로 도출한다. 조인이 발생하고 관리가 어렵다. |
Single Type | 슈퍼타입과 서브타입을 하나의 테이블로 도출하는 것이다. 조인성능이 좋고 관리가 편리하지만, IO 성능이 나쁘다. |
SELECT · 릴레이션에 존재하는 튜플 중에서 선택 조건을 만족하는 튜플의 부분집합을 구하여 새로운 릴레이션을 만듦
· 릴레이션의 행(가로)에 해당하는 튜플을 구하는 것이므로 수평 연산이라고도 함
PROJECT · 주어진 릴레이션에서 속성 List에 제시된 Attribute만을 추출하는 연산
· 릴레이션의 열(세로)에 해당하는 Attribute를 추출하는 것이므로 수직 연산자라고도 함
JOIN · 공통 속성을 중심으로 2개의 릴레이션을 하나로 합쳐서 새로운 릴레이션을 만드는 연산
· 조인 조건이 '='일 때 동일한 속성이 두 번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 자연(NATURAL) 조인이라고 함
DIVISION · X ⊃ Y인 2개의 릴레이션에서 R(X)와 S(Y)가 있을 때, R의 속성이 S의 속성값을 모두 가진 튜플에서 S가 가진 속성을 제외한 속성만을 구하는 연산
이상현상
정규화를 하지 않아서 발생하는 현상(Anomaly)
삽입 이상현상, 갱신 이상현상, 삭제 이상현상이 있다.
옵티마이저는 SQL의 실행계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다.
DBMS Database Management System
Row = Tuple
Column = Field = Attribute
SQL Structured Query Language
DDL Data Definition Language
DML Data Manipulate Language
DCL Data Control Language
SQL> CREATE ROLE manager;
SQL> GRANT CREATE SESSION, CREATE TABLE TO manager;
SQL> GRANT manager TO scott, test;
// 여기서는 scott, test 라는 유저에 부여함.`TCL Transaction Control Language
같은 이름의 SAVEPOINT가 저장될 경우 나중에 저장된 SAVEPOINT 로 ROLLBACK(복원) 함.
oracle 의 경우 기본 값이 auto commit off 로 ddl 이 일어날 경우 묵시적 commit 이 됨 (설정 불가), sql server 의 경우 기본 값이 auto commit on 으로 false 가 될 경우 ddl 도 묵시적 commit 이 되지 않음
Merge
테이블 복사
[오라클]
<테이블 복사할 때>
CREATE TABLE 새로만들테이블명 AS
SELECT * FROM 복사할테이블명 [WHERE 절]
<테이블 구조만 복사할 때>
CREATE TABLE 새로만들테이블명 AS
SELECT * FROM 복사할테이블명 WHERE 1=2 [where에다가 참이 아닌 조건을 넣어줌]
<테이블은 이미 생성되어 있고 데이터만 복사할 때>
INSERT INTO 복사할테이블명 SELECT * FROM 복사할테이블명 [WHERE 절]
<테이블 이름 변경>
ALTER TABLE 구테이블명 RENAME TO 신테이블명
[SQL server]
<테이블 복사할 때>
SELECT * INTO 새로만들테이블명 FROM 복사할테이블명
원자성 Atomicity (all or nothing) 트랜잭션에서 연산들이 모두 성공하거나 모두 실패 해야함. (계좌이체)
일관성 Consistency 트랜잭션 실행 전 DB 내용이 잘못 되지 않으면 실행 후도 잘못 되지 않아야 함
고립성 Isolation 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
영속성 Durability 트랜잭션이 성공적으로 수행되면 DB의 내용은 영구적으로 저장된다.
SQL 실행순서
ON DELETE CASCADE (oracle에만 있고 mySQL에는 없음)
DROP TABLE CASCADE CONSTRAINT
CREATE VIEW
ORDER BY 는 성능저하가 발생
SELECT 구문에 사용되지 않은 컬럼도 OERDER BY 구문에서 사용할 수 있다
ORACLE 은 NULL 을 가장 큰 값으로 취급하여 ORDER BY 시 맨 뒤로 정렬되고 SQL SERVER 반대로 가장 앞으로 정렬한다.
부정비교 연산자 “같지 않은 것”
!=
^=
<>
연산자 우선순위
1 산술 연산자(*, /, +, -)
2 연결 연산자 (||)
3 비교 연산자(<, >, <=, =>, <>, =)
4 IS NULL, LIKE, IN
5 BETWEEN
6 NOT 연산자
7 AND 연산자
8 OR 연산자
NVL - NULL이면 다른 값으로 바꾸는 함수이다.
- "NVL(MGR, 0)"은 MGR칼럼이 NULL이면 0으로 바꾼다.
NVL2 - NVL함수와 DECODE를 하나로 만든 것이다.
- "NVL2(MGR, 1, 0)"은 MGR칼럼이 NULL이 아니면 1, NULL이면 0을 반환한다.
NULLIF - 두 개의 값이 같으면 NULL을 같지 않으면 첫 번째 값을 반환한다.- "NULLIF(exp1, exp2)"은 exp1과 exp2가 같으면 NULL을, 같지 않으면 exp1을 반환한다.
COALESCE - "COALESCE(mgr, 1)"은 mgr이 NULL이 아니면 1을 반환한다.
- exp1이 NOT NULL이면 exp1, exp2가 NOT NULL이면 exp2… 계속
- 그러니까 제일 처음으로 NOT NULL이 아닌 값을 반환
ORDER BY [정렬하고자 하는 컬럼명] [DESC/ASC] NULLS LAST
비교할 때 NULL과는 비교될 수 없다.
NOT IN문 서브쿼리의 결과 중에 NULL이 포함되는 경우 데이터가 출력되지 않는다.
IN 문은 OR 조건, NOT IN 문은 AND 조건
NULL은 논리적으로 비교할 수 없는 연산이기 때문에 원하는 데이터를 추출 하기 위해서는 조회 컬럼에 IS NOT NULL조건을 주어 NULL인 데이터를 빼고 조회하면 된다.
COUNT(*) : NULL을 포함해서 계산
COUNT(column) : NULL을 제외하고 계산
SELECT문 실행순서
SYSDATE
SIGN : 양수, 음수 구별
TRUNC : 소수점 m자리에서 절삭
DECODE(exp1, ‘TRUE’, ‘FALSE’)
WITH : subquery를 사용해서 임시테이블이나 뷰처럼 사용할 수 있음
RANK() : 중복된 순위 건너뛰고 순위 부여 (1, 2, 2, 4 …)
DENSE_RANK() : 중복된 순위 다음에는 바로 다음 순위를 부여 (1, 2, 2, 3 … )
ROWNUM은 논리적인 숫자이므로 "ROWNUM = 2" 와 같이 조회하면 찾을 수 없다.
LAG ****: 이전 행의 값을 리턴
LEAD ****: 다음 행의 값을 리턴
LAG(expr [,offset] [,default]) OVER([partition_by_clause] order_by_clause)
LEAD(expr [,offset] [,default]) OVER([partition_by_clause] order_by_clause)
LAG 함수 : 이전 행의 값을 리턴
LEAD 함수 : 다음 행의 값을 리턴
expr : 대상 컬럼명
offset : 값을 가져올 행의 위치 기본값은 1, 생략가능
default : 값이 없을 경우 기본값, 생략가능
partition_by_clause : 그룹 컬럼명, 생략가능
order_by_clause : 정렬 컬럼명, 필수
CREATE TABLE test33_45
(col1 varchar2(10), col2 varchar2(10), col3 integer);
INSERT ALL
INTO test33_45 VALUES('A', '가', 1)
INTO test33_45 VALUES('A', '가', 5)
INTO test33_45 VALUES('A', '다', 10)
INTO test33_45 VALUES('B', '가', 20)
INTO test33_45 VALUES('B', '나', 30)
INTO test33_45 VALUES('B', '나', 100)
INTO test33_45 VALUES('C', '다', 50)
SELECT * FROM dual;
SELECT col1, col2, col3, ntile(3) OVER (ORDER BY col3) AS ntile_2
FROM test33_45;
COL1|COL2|COL3|NTILE_2|
----+----+----+-------+
A |가 | 1| 1|
A |가 | 5| 1|
A |다 | 10| 1|
B |가 | 20| 2|
B |나 | 30| 2|
C |다 | 50| 3|
B |나 | 100| 3|
SELECT col1, col2, col3, ntile(3) OVER (PARTITION BY col1 ORDER BY col3) AS ntile_2
FROM test33_45;
COL1|COL2|COL3|NTILE_2|
----+----+----+-------+
A |가 | 1| 1|
A |가 | 5| 2|
A |다 | 10| 3|
B |가 | 20| 1|
B |나 | 30| 2|
B |나 | 100| 3|
C |다 | 50| 1|
SELECT col1, col2, col3, ntile(3) OVER (PARTITION BY col2 ORDER BY col3) AS ntile_2
FROM test33_45;
COL1|COL2|COL3|NTILE_2|
----+----+----+-------+
A |가 | 1| 1|
A |가 | 5| 2|
B |가 | 20| 3|
B |나 | 30| 1|
B |나 | 100| 2|
A |다 | 10| 1|
C |다 | 50| 2|
LPAD(123, 5, '0') → 결과: 00123
LPAD(123, 8, '0') → 결과: 00000123
[SQLD_33_47]
COL1
--------
2020-2-1
[SQL]
SELECT EXTRACT(YEAR FROM SYSDATE),
LPAD(EXTRACT(month from sysdate), 2, 0)
FROM SQLD_33_47;
ROWNUM: Oracle 에서 사용
TOP: MySQL에서 사용
TOP WITH TIES로 쓰면 동일한 데이터가 있을 때 함께 조회됨
SQL server (EXCEPT) / Oracle (MINUS)
SYSDATE
SELECT TO_CHAR(TO_DATE('2015.01.10 10', 'YYYY.MM.DD HH24')
+1/24/(60/10),'YYYY.MM.DD HH24:MI:SS')
FROM DUAL;
TO_CHAR...
-------------------
2015.01.10 10:10:00
SELECT TO_DATE('2015.01.10 10', 'YYYY.MM.DD HH24')
FROM dual;
TO_DATE('2015.01.1010','YYYY.MM.DDHH24')|
----------------------------------------+
2015-01-10 10:00:00.000|
1/24 : 1시간 (1일을 24로 나누기)
1/24/60 : 1분 (1일 / 24시간 = 1시간 / 60 = 1분)
1/24/6 : 10분
1.5/24 : 1시간 30분
1.5/24/6 : 15분
1/24/60/6 : 10초
"롤업을 하면 총계가 나온다"
롤업은 괄호 안의 항목들을 오른쪽부터 하나씩 지워나가면서 집계한다.
예를 들면 ROLLUP(a, b) 는
1. (a, b) 합계
2. (a) 합계
3. () 합계
이렇게 3가지 형태의 집계 결과가 나오게 된다.
ROLLUP(a), a 를 보면
1. (a), a
2. (), a
이렇게 두가지 형태의 집계 결과가 나오게 된다.
a 가 두번 나오나 한번 나오나 다 같은 a 기준 집계 결론은 a 기준 집계 두번 한 결과
ROLLUP : 인수 순서중요(계층적), Order by 로 정렬
CUBE : 모든값에 다차원집계 생성 / 시스템에 많은 부하
GroupingSets : 인수 순서무관 (평등)
PL/SQL
Cursor 선언
DECLARE CURSOR <커서명> IS SELECT <컬럼명> FROM <테이블명>
Cursor Open
OPEN <커서명>
FETCH
FETCH <커서명> INTO 변수1, 변수2...
Cursor Close
[ PL/SQL의 특징 ]
인덱스
B-Tree 인덱스
계층형 질의
CONNECT_BY_LEAF 전개 과정에서 해당 데이터가 리프 데이터면 1, 아니면 0을 반환
CONNECT_BY_ISCYCLE 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로 존재하면 1, 그렇지 않으면 0을 반환.
여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 뜻함.
SYS_CONNECT_BY_PATH 하위 레벨의 칼럼까지 모두 표시(구분자 지정 가능)
CONNECT BY PRIOR 자식 = 부모 (부모 → 자식) (순 ↓ 방향)
CONNECT BY PRIOR 부모 = 자식 (자식 → 부모) (역 ↑ 방향)
CONNECT BY 자식 = PRIOR 부모 (자식 → 부모) (역 ↑ 방향)
CONNECT BY 부모 = PRIOR 자식 (부모 → 자식) (순 ↓ 방향)
START WITH 계층구조 전개의 시작 위치를 지정하는 구문, 루트 데이터를 지칭
CONNECT BY 다음에 전개될 자식 데이터를 지정, 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야함(JOIN)
PRIOR CONNECT BY 절에 사용되며 현재 읽은 칼럼을 지정
PRIOR 부모 부모형태를 사용하면 계층구조에서 부모 -> 자식 방향으로 순방향 전개
PRIOR 자식 자식 형태를 사용하면 자식 -> 부모 방향으로 역방향 전개
NOCYCLE 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개중에 다시 나타나는 경우 CYCLE이 생성. CYCLE이 발생한 데이터는 런타임 오류를 방생시켜 NOCYCLE 구문을 통해 CYCLE이 발생하는 경우 이후 데이터 전개를 방지
ORDER SIBLINGS BY 형제노드(동일 LEVEL) 사이 데이터 정렬
WHERE 모든 전개를 수행한 뒤 지정조건을 통해 데이터 필터링
프로시저 | 트리거 |
---|---|
CREATE PROCEDURE 문법 사용 | CREATE TRIGGER 문법 사용 |
생성하면 소스코드와 실행코드가 생성됨 | 생성하면 소스코드와 실행 코드가 생성됨 |
EXECUTE 명령어로 실행 | 생성 후 자동 실행 |
COMMIT, ROLLBACK 실행 가능 | COMMIT, ROLLBACK 실행 불가 |
서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다.
질의 결과에 서브쿼리 컬럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리 등을 사용해야 한다.
서브쿼리 사용시 주의사항1. 서브쿼리를 괄호로 감싸서 사용한다.2. 서브쿼리는 단일행(Single row) 또는 복수행 (Multiple row)비교 연산자와 함께 사용 가능하다. 단일행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관없다.
1번은 서브쿼리 결과가 여러 개의 행이 리턴 되므로 오류가 발생하게 된다 ( = 단일행 연산자로 서브쿼리의 결과가 반드시 하나만 리턴 되어야 함)
SELECT A.EMPNO, A.ENAME
FROM EMP A
WHERE A.EMPNO = (SELECT 1 FROM
EMP_T B WHERE A.EMPNO = B.EMPNO);
메인쿼리의 값을 서브쿼리에서 주입을 받아서 비교를 하는것으로 상호연관 서브쿼리(CORRELATED SUB QUERY) 이다.
서브쿼리에 *(A.EMPNO 값을 매번 가져와서 대입을 해야하므로 성능이 매우 좋지않다.)
서브쿼리에서는 정렬을 수행하기 위해서 내부에 ORDER BY를 사용하지 못한다.
메인 쿼리를 작성할 때 서브쿼리에 있는 칼럼을 자유롭게 사용할 수 없다.
여러 개의 행을 되돌리는 서브쿼리는 다중행 연산자를 사용해야 한다.
EXIST는 TRUE와 FALSE만 되돌린다.
SELECT
스칼라 서브쿼리 FROM
인라인 *뷰
FROM
인라인 *뷰 서브쿼리가 FROM 절에 사용되면 동적으로 생성된 테이블인 것처럼 사용할 수 있습니다.
인라인 뷰는 SQL 문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않습니다.
FROM
인라인 *뷰 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해 사용합니다.
중첩 반복 조인(Nested Loop Join)
- 좁은 범위에 유리
- 유리순차적으로 처리하며, Random Access 위주
- 후행(Driven) 테이블에는 조인을 위한 인덱스가 생성되어 있어야 함
- 실행속도 = 선행 테이블 사이즈 * 후행 테이블 접근횟수
색인된 중첩 반복 조인, 단일 반복 조인(Single Loop Join)
- 후행(Driven) 테이블의 조인 속성에 인덱스가 존재할 경우 사용
- 선행 테이블의 각 레코드들에 대하여 후행 테이블의 인덱스 접근 구조를 사용하여 직접 검색 후 조인하는 방식
정렬 합병 조인(Sort Merge Join)
- Sort Merge 조인은 해당 테이블의 인덱스가 없을때 수행이 된다.
- 테이블을 정렬(Sort) 한 후에 정렬된 테이블을 병합(Merge) 하면서 조인을 실행한다.
- 조인 연결고리의 비교 연산자가 범위 연산( >, < )인 경우 Nested Loop 조인보다 유리
- 두 결과집합의 크기가 차이가 많이 나는 경우에는 비효율적
해시 조인(Hash Join)
- 해시(Hash)함수를 사용하여 두 테이블의 자료를 결합하는 조인 방식
- Nested Loop 조인과 Sort Merge 조인의 문제점을 해결
- 대용량 데이터 처리는 상당히 큰 hash area를 필요로 함으로, 메모리의 지나친 사용으로 오버헤드 발생 가능성
Nested Loop Join Sort Merge Join Hash Join
랜덤 액세스 등가, 비등가 조인 가능 등가조인만 가능
대용량 sort 작업 유리 조인키 기준 정렬 대량 작업 유리, 함수처리
가) 선행 테이블에서 주어진 조건을 만족하는 레코드를 필터링한다.
나) 선행 테이블의 조인 키를 기준으로 해시 함수를 적용하여 해시 테이블을 생성한다.
다) 1번, 2번 작업을 선행 테이블에서 조건을 만족하는 모든 행을 수행한다.
라) 후행 테이블에서 주어진 조건을 만족하는 레코드를 필터링한다.
마) 후행 테이블의 조인 키를 기준으로 해시 함수를 적용하여 선행 테이블에서 해시 함수 반환값과 같은 값을 반환하는 해당 버킷을 찾는다.
특징
NATURAL JOIN
CROSS JOIN 상호 조인이라고도 불리며, 한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인시키는 기능을 한다.
이러한 CROSS JOIN을 카테시안 곱 (Cartesian Product)라고도 한다.
NATURAL JOIN - 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인을 간단히 표현하는 방법이다.
- NATURAL JOIN은 두 테이블의 동일한 이름을 가지는 칼럼이 모두 조인된다.
- 동일한 칼럼을 내부적으로 찾게 되므로 테이블 별칭(Alias)을 주면 오류가 발생한다.
턱걸이지만... 구래도 합격~!