<1-1 데이터 모델링>

데이터 모델링의 특징

추상화. 단순화. 명확성

데이터 모델링의 단계

개념적 → 논리적 → 물리적

데이터 모델링의 관점(View)

데이터. 프로세스. 데이터&프로세스

ERD(Entity Relationship Model) 1.엔티티 도출 2.엔티티 배치 3.엔티티 관계 설정 4.관계명 서술 5.관계 참여도 6.관계 필수 여부 표현

데이터 모델링 고려사항

독립성(정규화). 고객 요구사항 표현. 데이터 품질 확보

3층 스키마

데이터 베이스 독립성 확보하기 위한 방법 → 데이터 복잡도 상승. 데이터 중복 X. 사용자 요구사항 변경에 따른 대응령 상승. 관리 및 유지보수 비용 감소

3층 스키마의 독립성

논리적 독립성. 물리적 독립성

3층 스미카의 구조

외부. 개념. 내부

엔티티

데이터 집합/ 개념. 사건. 장소 등의 명사

엔티티의 특징

식별자. 인스턴스 집합. 속성. 관계. 업무

엔티티 종류 -무형&유형: 유형. 개념. 사건 엔티티 -발생 시점: 기본(키엔티티). 중심(메인). 행위 엔티티

속성

엔티티가 가지는 항목/ 인스턴스 구성요소. 더 이상 분해 X

속성의 특징

하나의 값. 주식별자에게 함수적으로 종속됨

속성의 종류 -분해여부: 단일. 복합(여러개의 의미가 있는 것 ex.주소). 다중값(여러개의 값을 가질 수 있는 것 ex.상품리스트) -특성: 기본. 설계(데이터 모델링 과정에서 발생. 유일한 값에 부여 ex.상품코드). 파생(다른 속성에 의해서 만들어지는 속성 ex.합계 평균 등)

도메인

속성이 가질 수 있는 값의 범위 ex.성별은 여자와 남자

관계 -존재: 두 개의 엔티티 존재 여부의 관계가 있는 것 -행위: 두 개의 엔티티가 어떤 행위에 의한 관련성 있는 것

관계의 종류 -존재 관계: 엔티티간의 상태 -행위 관계: 엔티티간의 어떤 행위가 있는 것

관계차수

두개의 엔티티 간의 관계에 참여하는 수 -1대 1(완전 1대1/ 선택적 1대1) -1대N -1대 M(관계형 DB. M대 N 관계의 조인은 카테시안 곱이 발생 and M대 N 관계 → 1대N, N대 1로 해소해야 됨)

-필수적 관계(반드시 하나 있어야 됨. 표기법 ‘|’) -선택적 관계(없을수도 있는 관계. 표기법 ‘O’)

식별 관계&비식별 관계 -식별 관계: 강한 개체(어떤 다른 엔티티 의존X. 독립적으로 존재) ex.고객 엔티티의 기본키인 회원ID. 계좌 엔티티의 기본키의 하나로 공유하는 것 -비식별 관계: 강한 개체의 기본키. 다른 엔티티의 기본키X. 일반 칼럼으로 관계 가지는 것

주식별자(기본키. Primary Key)

최소성. 대표성. 유일성. 불변성

키의 종류 -기본키(Primary Key) -후보키(Candidate Key): 유일성&최소성 만족 -슈퍼키(Super Key): 유일성O. 최소성X -대체키(Alternate Key) -외래키(Foreign Key): 참조 무결성

식별자의 종류 -대표성: 주식별자/보조 -생성 여부: 내부/외부 -속성의 수: 단일/복합 -대체 여부: 본질. 인조(Sequence Number)

<1-2 데이터 모델과 성능>

정규화

데이터의 일관성. 최소한의 데이터 중복. 최대한의 데이터 유연성을 위한 방법 → 데이터 모델의 독립성을 확보하기 위한 방법

정규화 절차 -제1정규화: 속성의 원자성 확보. 기본키 설정 -제2정규화: 기본키 2개 이상 컬럼 경우에 발생. 부분 함수 종속성 제거 -제3정규화: 이행 함수 종속성(기본키 제외. 컬럼 간에 종속성 발생) 제거 -BCNF(Boyce-Code Normal Form): 복수의 후보키. 후보키들 복합 속성. 서로 중첩 → 후보키가 기본키 종속시키면 분해 -제4정규화: 여러 칼럼들. 하나의 컬럼 종속시키는 경우 → 분해하여 다중값 종속성 제거 -제5정규화: 조인에 의해서 종속이 발생. 분해

정규화의 문제점

테이블 분해 → 데이터 중복 제거 → 데이터 모델의 유연성 증가 but 데이터 조회 시 조인 유발. CPU&메모리 많이 사용

정규화를 사용한 성능 튜닝

조인으로 인한 성능 저하 문제 → 반정규화 통해 해결 but 반정규화는 데이터 중복 → 또 다른 문제점 발생

반정규화

DB 성능 향상 위해 데이터 중복 O. 조인 사용 떨어짐 == 성능 향상 방법. SELECT 속도 향상 but 데이터 모델 유연성 하락

반정규화 수행하는 경우 -정규화 충실하면 종속성. 활용성 상승 but 수행속도 하락 -다량의 범위. 자주 처리해야되는 경우 -특정 범위 데이터만 자주 처리할 때 -요약/집계 정보 자주 요구할 때

반정규화 절차 -대상 조사 및 검토: 데이터 처리 범위. 통계성 등 확인 및 대상 조사 -다른 방법 검토: 클러스터링. 뷰. 인덱스튜닝. 응용프로그램. 파티션 등… -반정규화 수행: 테이블. 속성. 관계 등을 반정규화

클러스터링

인덱스 정보 저장 할 때. 물리적으로 정렬해서 저장하는 방법 → 조회 시. 인접 블록 연속적으로 읽음 → 성능 향상

반정규화 기법 -계산된 칼럼 추가: 배치 프로그램 통해 총판매액. 평균잔고. 계좌평가 등 미리 계산하여 결과를 특정 칼럼에 추가 -테이블 수직분할: 1개의 테이블 → 2개 이상의 테이블로 분할 (칼럼 분할해서 new 테이블 만드는 것) -테이블 수평분할: 1개의 테이블 값 → 기준 정해 테이블 분할

파티션 기법

논리적 하나의 테이블. 파티션 사용해서 여러 개의 데이터 파일에 분산되어 저장 -Range Parition: 데이터 값의 범위를 기준 -List Parition: 특정한 값 지정 -Hash Parition: 특정한 값 지정 -Composite Parition: 범위&해시 복합작 사용. 파티션 수행

테이블 병합

1대 1 관계 테이블. 하나의 테이블로 병합해서 성능 향상 1대 N 관계 테이블. 병합 성능 향상 but 많은 양의 데이터 중복 발생 슈퍼 타입&서브 타입 관계 발생. 테이블 통합 성능 향상

Super type&Sub type

부모자식 관계 -배타적 관계: or -포괄적 관계: and

슈퍼타입 및 서브타입 변환 방법 -One ToOne Type: 슈퍼&서브. 개별 테이블별로 도출. 테이블 수 증가. 조인 발생 증가. 관리 Hard -Plus Type: 슈퍼&서브 타입 테이블로 도출. 조인 발생. 관리 Hard -Single Type: 슈퍼. 서브 하나의 테이블로 도출. 조인 성능 Good but 입출력 성능 X

분산 데이터베이스

물리적 떨어진 데이터베이스 + 네트워크 연결 = 사용자에게 단일 데이터베이스 이미지. 분산된 작업 처리

분산 데이터베이스 투명성 -분할 투명성: 사용자는 논리적 릴레이션. 여러 단편으로 분할되어 있는것. 인식할 필요X -위치 투명성: 사용자는 사용하려는 데이터 저장 장소 명시X. 데이터 어느 위치에 있더라도 동일한 명령 사용하여 데이터 접근 가능하게 -지역사상 투명성: 지역 DBMS&물적 데이터베이스 사이 사상 보장. 각 지역 시스템 이름과 무관하게 사용 가능 -중복 투명성: 데이터베이스 객체. 여러 시스템 중복되어 있어도. 고객과는 무관 - 데이터 일관성 유지 -장애 투명성: DB가 분산된 지역의 시스템 or 통신망 이상 - 데이터 무결성 보장 -병행 투명성: 많은 사용자. 응용 프로그램이 동시에 분산. 데이터베이스 트랜잭션 수행회로 Not Bad. 결과 이상 X

분산 데이터베이스 설계 방식 -상향식 설계 방식: 지역 스키마 작성 → 전역스키마 작성 -하향식 설계 방식: 전역 스키마 → 해당 지역 스키마 작성

분산 데이터베이스 장점 및 단점 -장점: 데이터베이스 신뢰성&가용성 증가. 병렬처리 빠른 응답. 시스템 용량 추가 쉬움 -단점: 관리 및 통제 Hard. 보안관리 Hard. 데이터 무결성 관리 Hard. DB설계 복잡→

<2-1 SQL 기본>

데이터베이스 종류 -계층형 데이터베이스: 트리(Tree)형태. 1대N 관계 -네트워크형 데이터베이스: 오너(Owner)&멤버(Member)형태. 1대 N + M대 N 표현 가능 -관계형: 릴레이션에 데이터 저장 및 관리. 릴레이션 사용하여 집합 연산&관계 연산 가능

DBMS(Database Management System)

데이터베이스 등을 관리하기 위한 소프트웨어 -종류: Oracle. MS-SQL. MySQL. Sybase 등… 모두 관계형 데이터베이스 지원

관계형 데이터베이스 집합 연산&관계 연산 집한 연산 -합집합(Union): 두 개의 릴레이션 → 하나로 합/ 중복된 행(튜플)은 한 번만 조회 가능 -차집합(Difference) -교집합(Intersection) -곱집합(Cartesian product)

관계연산 -선택 연산(Selection) -투영 연산(Projection): 릴레이션에서 조건에 맞는 속성만 조회 -결합 연산(Join) -나누기 연산(Division)

SQL의 종류 -DDL(Data Definition Language): 관계형 데이터베이스 구조 정의

CREATE. ALTER. DROP. RENAME

-DML(Date Manipulation Language): 테이블에서 데이터 입력. 수정. 삭제. 조회

INSERT. UPDATE. DELETE. SELECT

-DCL(Data Control Language): 데이터베이스 사용자에게 권하는 부여 및 회수

GRANT. REVOKE. TRUMCATE

-TCL(Transaction Control Language): 트랜잭션 제어 명령어

COMMIT. ROLLBACK. SAVEPOINT

트랜잭션의 특징 -원자성(Atomictiy): ALL or NOTHING -일관성(Consistency) -고립성(Isoaltion): 트랜잭션의 중간결과 다른 트랜잭션 접근 X -영속성(Durability)

SQL 실행 순서 -파싱(Parsing): 구문분석 -실행(Exection): 옵티마이저(Otimizer) 수립한 실행 계획에 따라 SQL 실행 -인출(Fetch): 데이터 읽어서 전송

테이블 관리 SQL문 -Create Table: 새로운 테이블 생성/ 기본키. 외래키. 제약사항 등 설정 -Alter Table: 생성된 테이블 변경/ 칼럼 추가 및 변경. 삭제 가능/ 기본키 설정 or 외래키 설정 가능 -Drop Table: 해당 테이블 삭제/ 테이블 구조 + 저장된 데이터 삭제

제약조건 사용

기본키. 외래키. 기본값. not null 등 테이블 생성할 때 지정 가능 -constraint: 기본키 + 기본키의 이름 지정 가능

comstraint p_name primary key(empno)

-외래키 지정

constrain f_name foreign key(emp_deptno) 
							referneces dept(dept_deptno)

CASCADE 사용

참조 관계(기본키와 외래키 관계) 있을 경우 데이터 자동 반영 -ON DELETE CASCADE 옵션: 자신이 참조하고 있는 테이블의 데이터 삭제 → 자동적으로 자신도 삭제되는 옵션/ 참조 무결성 준수할 수 있음

테이블 변경 -테이블변경(ALTER TABLE ~ RENAME TO)

ALTER TABLE EMP 
	RENAME TO NEW_EMP;

-칼럼 추가(ALTER TABLE ~ ADD)

ALTER TABLE EMP 
	ADD (age number(2) default 1);

-칼럼 변경 (ALTER TABLE ~ MODIFY)

ALTER TABLE EMP 
	MODIFY (ename varchar2(40) not null);

-칼럼 삭제(ALTER TABLE ~ DROR COLUMN)

ALTER TABLE EMP 
	DROP COLUM age;

-칼럼명 변경(ALTER TABLE ~ RENAME COLUMN ~ TO)

ALTER TABLE EMP 
	RENAME COLUMN name to new_ename;

테이블 삭제

DROP TABLE 

-EMP 테이블의 데이터 + 테이블 구조 모두 삭제

 DROP TABLE EMP

-위에 문항 + 참조된 제약사항까지도

DROP TABLE CASCADE CONSTRAINT;

뷰(View) 생성과 삭제 -뷰: 테이블로부터 유도된 가상 테이블

뷰의 특징 -참조한 테이블 변경되면 뷰도 변경 -뷰에 대한 입력. 수정. 삭제에는 제약 있음 -특정 칼럼만 조회 → 보안성 향상 -한번 생성된 뷰는 변경 X (변경원하면 삭제 후 재생성) -뷰의 조회 일반 테이블과 동일 (SELETE)

뷰 생성

CREATE VIEW T_EMP AS 
	SELETE * FROM EMP;

뷰 삭제

DROP VIEW T_EMP;

INSERT문 :테이블에 데이터 입력하는 DML문

INSERT INTO table (column1, column2, ...) VALUES(expression1, expression2, ...);

INSERT INTO table VALUES(expresstion1, expression2, ...);

Nologging

로그파일 기록 최소화시켜 입력 시 성능 향상시키는 방법/ Buffer Cache.메모리 영역 생략 후 기록

ALTER TABLE DEPT NOLOGGING;

UPDATE문

입력된 데이터 값 수정

UPDATE EMP 
		SET ENAME = '조조' 
	WHERE EMPNO = '100' 
→ EMP 테이블에서 EMPNO가 100번인 직원의 이름을 조조로 수정	

DELETE문

원하는 조건을 검색 후 해당되는 행 삭제

DELETE FROM EMP 
	WHERE EMPNO = 100;
→ EMP 테이블에서 EMPNO가 100번인 직원 삭제

테이블 모든 데이터 삭제 -DELETE FROM 테이블명: 테이블 모든 데이터 삭제 but 테이블 용량 감소 X -TRUNCATE TABLE 테이블명: 테이블 모든 데이터 삭제 and 테이블의 용량 초기화

SELETE문

테이블 입력된 데이터 조회

SELECT * 
	FROM EMP 
	WHERE 사원번호 = 1000;
→ 사원번호 1000번 직원의 모든 칼럼 조회

SELECT ENAME || '님' FROM EMP;
→ ENAME 칼럼 뒤에 '님' 문자 결합

Order by. 정렬

SELECT문 사용할 때 Order by 같이 사용

-ASC: 오름차순 -DESC: 내림차순

SELECT * FROM EMP 
	ORDER BY ENAME, SAL DESC;
→ ENAME 오름차순. SAL 내림차순

Index 정렬

정렬 사용하면 Oracle 데이터베이스에 부하 발생 ← 인덱스 사용하여 Order by 회피 피할 수 있음

SELECT /* INDEX_DESC(A) */ 
FROM EMP A;
→ INDEX 힌트(Hint)

Distinct

중복된 데이터 한 번만 조회

SELECT DEPTNO FROM EMP
ORDER BY DEPTNO;
→  중복 포함 결과값 출력

SELECT [DISTINCT] DEPTNO FROM EMP 
ORDER BY DEPTNO;
→ 중복 제외 결과값 출력

WHERE문 -비교 연산자: =. <. <=. >. >= -부정 비교 연산자: !=. ^=. <>. NOT 칼럼명=. NOT 칼럼명 > -논리 연산자: AND. OR. NOT -SQL 연산자: LIKE ‘%비교 문자열%’. BETWEEN A AND B. IN(list). IS NULL -부정 SQL 연산자: NOT BETWEEN A AND B. NOT IN (list). IS NOT NULL

Like문 -%: 어떤 문자를 포함한 모든 것을 조회/ 예) ‘조%’는 ‘조’로 시작하는 모든 문자를 조회 -_(underscore): 한 개인 단일 문자 의미

IN문

“OR”의 의미를 가지고 있어 하나의 조건만 만족해도 조회가 됨

예) JOB이 "CLEPK"이거나 "MANAGER"인 것을 조회할 때 
SELECT * FROM EMP 
	WHERE JOB IN ('CLEPK', 'MANAGER');

예) IN문에 두 개의 칼럼 지정
SELECT * FROM 
WHERE (JOB, ENAME) 
IN (('CLERK', 'test1'), ('MANAGER', 'test4'));

NULL 관련 함수 -NVL 함수: NULL이면 다른 값으로 바꾸는 함수/ NVL(MGR, 0) ← MGR 칼럼이 NULL이면 0으로 변경 -NVL2 함수: NVL 함수 + DECODE 함수/ NUL2(MGR, 1, 0) ← MGR 칼럼이 NULL이 아니면 1, NULL이면 0으로 반환 -NULLIF 함수: 두 개의 값이 같으면 NULL 아니면 첫 번째 값 반환/ NULLIF(exp1, exp2) 같으면 NULL, 아니면 exp1 반환 -COALESCE 함수: NULL이 아닌 최초의 인자 값 반환 / COALESCE(exp1, exp2, exp2, …) NULL이 아니면 exp1의 값 아니면 그 뒤의 값의 NULL 여부 판단하여 반환

GROUP BY문

테이블에서 소규모 행 그룹화하여 합계. 평균. 최댓값. 최소값 등을 계산 할 수 있음

-HAVING: 조건문 -ORDER BY: 정렬

SELECT DEPTNO, SUM(SAL) 
	FROM EMP 
GROUP BY DEPTNO HAVING SUM(SAL) > 10000;
→ GROUP BY 결과에서 급여합계가 10000이상만 조회

집계 함수 -COUNT(): 행 수 조회/ COUNT(*) NULL ← NULL값 포함한 모든 행 수/ COUNT(칼럼명) ← NULL 값 제외한 행 수 -SUM() -ANG() -MAX() 와 MIN() -STDDEV(): 표준편차 계산 -VARIAN(): 분산 계산

SLEECT문 실행 순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

명시적(Explicit)형변환. 암시적(Implicit)형변환

형변환? 두 개의 데이터의 데이터 타입(형)이 일치하도록 변환하는 것

-명시적 형변환: 개발자가 SQL을 사용할 때 형변환 함수 사용해야 함/ 인덱스 사용 O -암시적 형변환: 개발자가 형변환 하지 않은 경우 DBMS 자동으로 형변환 하는 것/ 인덱스 사용 X

형변환 함수 -TO_NUMBER(문자열): 문자열 숫자로 변환 -TO_CHAR(숫자 혹은 날짜, [FORMAT]): 숫자 혹은 날짜를 지정된 FORMAT 문자로 변환 -TO_DATE(문자열, FORMAT): 문자열 지정된 FORMAT의 날짜형으로 변환

DUAL 테이블

Oracle 데이터베이스에 의해서 자동으로 생성되는 테이블/ Oracle 데이터베이스의 모든 사용자가 사용할 수 있음

내장형 함수 -ASCII(문자): 문자 혹은 숫자 → ASCII 코드값 변환 -CHAR(ASCII 코드값): ASCII 코드 → 문자 변환 -SUBSTR(문자열, m, n): 문자열 m번째 위치부터 n개 자름 -CONCAT(문자열1, 문자열2): 문자열 1번과 문자열 2번 결합/ Oracle ‘||’. MS-SQL ‘+’ -LOWER(문자열): 영문자 소문자 변환 -UPPER(문자열): 영문자 대문자 변환 -LENGTH 혹은 LEN(문자열): 공백 포함 문자열 길이 -LTRIM(문자열, 지정문자): 왼쪽 지정된 문자 삭제/ 지정된 문자 생략하면 공백 삭제 -RTRIM(문자열, 지정문자): 오른쪽 지정된 문자 삭제/ 지정된 문자 생략하면 공백 삭제 -TRIM(문자열, 지정된 문자): 왼쪽 및 오른쪽에서 지정된 문자 삭제/ 지정된 문자 생략하면 공백 삭제

날짜형 함수 -SYSDATE: 오늘의 날짜를 날짜 타입으로 알려줌 -EXTRACT(‘YEAR’ | ‘MONTH’ | ‘DAY’ from dual): 날짜에서 년, 월, 일을 조회

숫자형 함수 -ABS(숫자): 절댓값 계산 -SIGN(숫자): 양수. 음수. 0 구별 -MOD(숫자): 나머지 계산/ % 사용가능 -CEIL/CEILNG(숫자): 숫자보다 크거나 같은 최소의 정수 -FLOOR(숫자): 숫자보다 작거나 같은 최대의 정수 -ROUND(숫자, m): 소수점 m 자리에서 반올림/ m의 기본값은 0 -TRUNC(숫자, m): 소수점 m 자리에서 절삭/ m의 기본값은 0

DECODE 와 CASE문 -DECODE: IF문 구현 가능. 특정 조건이 참이면 A 거짓이면 B로 응답

DECODE(EMPNO, 1000, 'TRUE', 'FALSE');

-CASE: IF~THEN~ELSE-END 프로그래밍 언어처럼 조건문 사용 가능/ 조건을 WHEN구. THEN 해당 조건 참이면 실행. 거짓이면 ELSE 실행

SELECT CASE
			WHEN EMPNO = 1000 THEN 'A'
			WHEN EMPNO = 1001 THEN 'B'
			ELSE 'C'
		END
	FROM EMP;          

ROWNUM 과 ROWID -ROWNUM: DRACLE 데이버베이스의 SELECT문 결과에 대해서 논리적인 일렬번호 부여/ 조회되는 행 수를 제한할 때 많이 사용 동일한 표현으로 SQL Server(TOP)/ MYSQL(LIMIT)

SELECT * FROM EMP 
WHERE ROWNUM <= 1 
→ 한 행을 조회

-ROWID: ORACLE 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값 SELECT ROWID, EMONO FROM EMP 와 같은 SELECT 문으로 확인 가능

ROWID의 구조 -오브젝트 번호(길이 1~6): 오브젝트별로 유일한 값 존재. 해당 오브젝트가 속해 있는 값 -상대 파일 번호(길이 7~9): 테이블스페이스에 속해 있는 데이터 파일에 대한 상대 파일번호 -블록 번호(길이 10~15): 데이터 파일 내부에서 어느 블록에 데이터 있는지 알려줌 -데이터 번호(길이 16~18): 데이터 블록 데이터가 저장되어 있는 순서 의미

WITH구문

서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문/ 서브쿼리 블록에 별칭 지정/ 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단

WHIT viewData AS
  (SELECT * FROM EMP 
  UNION ALL 
  SELECT * FROM EMP
  )
  SELECT * FROM viewData WHERE EMPNO=1000;

GRANT

데이터베이스 사용자에게 권한 부여

권한 -SELECT -INSERT -UPDATE -DELETE -REFERENCES -ALTER -INDEX -ALL

WITH GRANT OPTION -WITH GRAND OPTION: 특정 사용자에게 권한 부여할 수 있는 권한 부여/ 권한 취소하면 모든 권한 회수 -WITH ADMIN OPTION: 테이블에 대한 모든 권한 부여/ A사용자 권한만 회수

REVOKE

데이터베이스 사용자에게 부여된 권한 회수

-REVOKE privileges ON object FROM user;

COMMIT

INSERT. UPDATE. DELETE문으로 변경된 데이터를 데이스터베이스에 반영

ROLLBACK

데이터에 대한 변경 사용을 모두 취소하고 트랜잭션 종료/ 이전에 COMMIT한 곳까지만 복구

SAVEPOINT(저장점)

트랜잭션 작게 분할하여 관리하는 것/ SAVEPOINT를 사용하면 지정된 위치 이후의 트랜잭션만 ROLLBACK 할 수 있음

-SAVEPOINT 지정: SAVEPOINT / rollback to t2;

<2-2 SQL 활용>

EQUI(등가) 조인 (교집합)

조인의 가장 기본. 두 개의 테이블 간에 일치하는 것을 조인

SELETE * FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO  ← '='로 두 개의 테이블 연결
		AND EMP.ENAME LIKE '임%' ← 조인문에 추가 조건 및 정렬 가능
	OREDR BY ENAME;

INNER JOIN

ON문을 사용해서 테이블 연결

SELETE * FROM EMP INNER JOIN DEPT ← INNER JOIN구로 테이블 정의
			ON EMP.DEPTNO = DEPT.DEPTNO ← ON구를 사용해서 조인 조건 넣음
		AND EMP.ENAME LIKE '임%' ← 조인문에 추가 조건 및 정렬 가능
	OREDER BY ENAME

해시 조인(Hash Join) -먼저 선행 테이블 결정 → 주어진 조건(Where구)에 해당하는 행 선택 → 조인 키(Join Key) 기준. 해시 함수 사용 → 해시 테이블(메인 메모리에 생성) → 후행 테이블에서 주어진 조건 만족하는 행 찾기

-후행 테이블의 조인 키를 사용해서 해시 함수를 적용하여 해당 버킷 검색

INTERSECT 연산

두 개의 테이블에서 교집합 조회 → 두 개의 테이블에서 공통된 값을 조회

SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;

Non-EQUI(비등가) 조인

두 개의 테이블 간에 조인하는 경우 → 정확하게 일치하지 않는 것을 조인 -‘=’ 사용 하지 않고 ‘>’. ‘<’. ‘>=’. ‘<=’ 사용함

OUTER JOIN

두 개의 테이블 간에 교집합(EQUI JOIN) 조회 + 한쪽 테이블에만 있는 데이터도 포함시켜 조회 Oracle 데이터베이스에서는 OUTER JOIN을 할 때 “(+)” 기호를 사용해서 할 수 있음

SELETE * FROM DEPT, EMP
	WHERE EMP.DEPTNO (+)= DEPT.DEPTNO;

-LEFT OUTER JOIN

SELETE * FROM DEPT LEFT OUTER JOIN EMP
	ON EMP.DEPTNO = DEPT.DEPTNO;

-RIGHT OUTER JOIN

SELETE * FROM DEPT RIGHT OUTER JOIN EMP
	ON EMP.DEPTNO = DEPT.DEPTNO;

-FULL OUTER JOIN(LEFT + RIGHT)

CROSS JOIN

조건구 없이 2개의 테이블을 하나로 조인 ← 조인구 없어서 카테시안 곱 발생

SELECT * FROM EMP CROSS JOIN DEPT

EMP 테이블 14건 and DEPT 테이블 4건 = 총 조인 4 * 14 = 60

UNION(합집합)

두 개의 테이블 하나로 만드는 연산 → 중복된 데이터 제거하면서 테이블을 합침 → 정렬(SORT) 과정 발생 (주의사항) 두 개의 테이블의 칼럼 수. 칼럼의 데이터 형식 모두가 일치해야 됨

SELECT DEPTNO FROM EMP
UNION
SELECT DEPTNO FROM EMP;

UNION ALL

단순하게 테이블을 합친다 (중복 제거 or 정렬 하지 않음)

SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM EMP;

MINUS(차집합)

두 개의 테이블에서 차집합 조회 → 먼저 쓴 SELECT문에는 있고 뒤에 쓰는 SELECT문에는 없는 집합을 조회

SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;

DEPT(10, 20, 30, 40) EMP(10,20,30) → 40만 조회됨

계층형 조회(Connect by)

Oracle 데이터베이스에서 지원하는 것. 계층형으로 데이터 조회 가능

SELECT MAX(LEVEL) ← LEVEL에는 계층값을 가지고 있음(최대 계층 수 구할 수 있음)
FROM Limbest.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

Connect by: 트리(Tree) 형태의 구조로 질의를 수행하는 것
STARY WITH: 시작 조건 의미
CONNECT BY PRIOR: 조인 조건

→ 결과값 4(트리의 최대 깊이)

SELECT LEVEL, EMPNO, MGR, ENAME
FROM Limbest.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

LPAD함수: 계층형 조회 결과를 명확하게 볼 수 있다
SELECT LEVEL, LPAD(''. 4*(LEVEL -1)) || EMPNO, MGR, CONNECT_BY_ISLEAF ~
←4*LEVEL-1(LEVEL 값이 Root이면 1이 됨. 트리 형태를 보기 위해서 사용함)

CONNECT BY 키워드 -LEVEL: 검색 항목의 깊이. 계층 구조에서 가장 상위 레벨이 1 -CONNECT_BY_ROOT: 계층구조에서 가장 최상위 값 표시 -CONNECT_BY_ISLEAF: 계층구조에서 가장 최하위 표시 -SYS_CONNECT_BY_PATH: 계층구조의 전체 전개 경로 표시 -NOCYCLE: 순환구조가 발생지점까지만 전개 -CONNECT_BY_ISCYCLE: 순환구조 발생 지점 표시

Subquery

SELECT문에 다시 SELECT문을 사용하는 SQL

-인라인 뷰(View): FROM구에 SELECT문을 사용. 가상의 테이블을 만드는 효과를 얻을 수 있음

SELECT * 
	FROM (SELECT ROWNUM NUM, ENAME
				FROM EMP) a
	WHERE NUM < 5;

-스칼라 서브쿼리(Scala Subquery): SELECT문에 Subquery를 사용. 반드시 한 행과 한 컬럼만 반환하는 서브쿼리. 여러 행이 반환되면 오류 발생

SELECT ENAME AS "이름" SAL AS "급여",
			 (SELECT AVG(SAL)) ← 한 개의 행만 조회 되어야 함 
			 		FROM EMP) AS "평균급여"
	FROM EMP
WHERE EMPNO=1000;

-서브쿼리(Subquery): WHERE구에 SELECT문을 사용

SELECT *
	FROM EMP
	WHERE DEPTNO =
	(SELECT DEPTNO FROM DEPT
	 WHERE DEPTNO=10);

서브쿼리 종류(반환 행) -단일 행 서브쿼리(Signle row subquery): 결과는 반드시 한 행만 조회/ 비교 연산자(=, <, <=, >, >=, <>) 사용 -다중 행 서브쿼리(Multi row subquery): 결과는 여러 개의 행이 조회/ 다중 행 비교 연산자(IN, ANY, ALL, EXISTS) 사용

다중 행 비교 연산자 -IN(Subquery): Main query의 비교조건. Subquery 결과 중 하나만 동일하면 참 (OR조건) -ALL(Subquery): Main query와 Subquery의 결과가 모두 동일하면 참/ < ALL(최소값 반환)/ > ALL(최대값 반환) -ANY(Subquery): Main query의 비교조건. Subquery의 결과 중 하나 이상 동일하면 참/ < ANY(하나라도 크게 되면 참)/ > ANY(하나라도 작게 되면참) -EXIST(Subquery): Main query와 Subquery의 결과가 하나라도 존재하면 참

IN

반환되는 여러 개의 행 중에서 하나만 참이 되어도 참이 되는 연산

SELECT ENAME, DNAME, SAL
	FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
	AND EMP.EMPNO
	IN (SELECT EMPNO FROM EMP
			WHERE SAL >2000); ← 급여(SAL)가 2000원 보다 큰 사원번호 조회 후 EMP, EMPNO을 조회

ALL

메인쿼리와 서브쿼리의 결과가 모두 동일하면 참

SELECT *
		FROM EMP
	WHERE DEPTNO <= ALL (20, 30) ← DEPTNO가 20, 30보다 작거나 같은 것 조회

EXISTS

Subquery로 어떤 데이터 존재 여부 확인 → 결과로 참과 거짓이 반환

SELECT ENAME, DNAME, SAL
	FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
	AND EXIST (SELECT 1 FROM EMP
			WHERE SAL > 2000); ← 급여(SAL)가 2000원 보다 큰 사원이 있으면 TRUE 조회

연관(Correlated) Subquery

Subquery 내에서 Main Query 내의 칼럼을 사용하는 것을 의미

FROM EMP a
WHERE a.DEPTNO = 
	(SELECT DEPTNO FROM DEPT b
		WHERE b.DEPTNO = a. DEPTNO)

ROLLUP

GROUP BY의 칼럼에 대해서 Subtotal 생성/ GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라서 결과가 달라짐

SELECT DECODE(DEPTNO, NULL, '전체합계'), SUB(SAL) ← DEPTNO가 NULL이면 '전체합계'문자 출력
	FROM EMP
	GROUP BY ROLLUP(DEPTNO); ← ROLLUP을 사용하면 부서별 합계 및 전체합계가 계산

SELECT DEPTNO, JOB, SUM(SAL)
	FROM EMP
	GROUP BY ROLLUP(DEPT, JOB); ← 부서별, 직업별 합계를 구함. ROLLUP은 subtotal을 구함

GROUPING 함수

ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분하기 위해서 만들어진 함수

SELECT DEPTNO, GROUPING(DEPTNO), JOB, GROUPING(JOB), SUM(SAL)
	FROM EMP
	GROUP BY ROLLUP(DEPTNO, JOB)

SELECT DEPTNO, DECODE(GROUPING(DEPTNO), 1, '전체합계') TOT, JOB
	DECOED(GROUPING(JOB),1,'부서합계') T_DEPT, SUB(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)

GROUPING SETS 함수

GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계 생성 가능 and 칼럼의 순서와 관계없이 개별적으로 모두 처리

SELETE DEPTNO, JOB, SUM(SAL) 
FROM EMP
GROUP BY GROUPTING SETS(DEPTNO, JOB); ← DEPTNO와 JOB을 각각의 그룹으로 합계를 계산. DEPT와 JOB 순서 바꿔도 결과는 같음

CUBE 함수

제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산. 다차원 집계 제공 다양하게 데이터를 분석 → 조합할 수 있는 경우의 수가 모두 조합

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);