[DB]SQLD_2

SQL 종류

- SQL은 데이터 정의, 데이터 조작, 데이터 제어 등의 기능 지원

Standard	Explanation
DDL(Data Definition Language)	- 관계형 데이터베이스 구조를 정의 - CREATE, ALTER, DROP, RENAME문
DML(Data Manipulation Language)	- 테이블에서 데이터를 입력, 수정, 삭제, 조회 - INSERT, UPDATE, DELETE, SELECT문
DCL(Data Control Language)	- 데이터베이스 사용자에게 권한을 부여하거나 회수 - GRANT, REVOKE, TRUNCATE문
TCL(Transaction Control Language)	- 트랜잭션을 제어하는 명령어 - COMMIT, ROLLBACK, SAVEPOINT문

 

SQL 문의 실행 순서

- 개발자가 작성한 DDL, DML, DCL 문은 3단계에 걸쳐서 진행

Execution	Explanation
Parsing	- SQL문의 문법 확인 및 구문분석 - 구문분석한 SQL문은 Library Cache에 저장
Execution	- Optimizer가 수립한 실행 계획에 따라 SQL을 실행
Fetch	- 데이터를 읽고 전송

 

CASCADE 옵션

- 테이블을 생성할 때 CASCADE 옵션을 사용할 수 있음

- CASCADE 옵션 : 참조 관계(기본키와 외래키 관계)가 있을 경우 참조되는 데이터를 자동으로 반영

# DEPT 테이블 생성 및 데이터 입력

create table DEPT
(
	deptno varchar2(40) primary key,
    deptname varchar2(20)
);
insert into DEPT values ('1000', '인사팀');
insert into DEPT values ('1001', '총무팀');

# EMP 테이블 생성 후 데이터 입력

create table EMP (
	empno number(10),
    ename varchar2(20),
    sal number(10, 2) default 0,
    deptno varchar2(4) not null,
    createtable date default sysdate,
    constraint e_pk primary key (empno),
    constraint d_fk foreign key (deptno)
    	references dept (deptno)
        ON DELETE CASCADE
);
insert into EMP values (100, '임베스트', 1000, '1000', sysdate);
insert into EMP values (101, '을지문덕', 2000, '1001', sysdate);

delete from DEPT where deptno = '1000';
select * from EMP;

- 이렇게 되면 EMP 테이블에서 deptno가 1000에 대응하는 '임베스트' 데이터 삭제

- ON DELETE CASCADE : 자신이 참조하고 있는 테이블의 데이터가 삭제되면 자동으로 대응하는 자신의 테이블 데이터도 삭제 

- DROP TABLE에서 CASCADE CONSTRAINT : 해당 테이블의 데이터를 외래키로 참조한 슬레이브 테이블과 관련된 제약사항도 삭제할 때 사용

 

View

- 테이블로부터 유도된 가상 테이블

- 실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만을 조회

- 테이블 딕셔너리에 SQL문 형태로 저장하되 실행 시에 참조

 

Nologging

- 데이터베이스에 데이터를 입력하면 로그파일에 정보를 기록

- Check Point라는 이벤트 발생 시 로그파일의 데이터를 데이터 파일에 저장

- Nologging : Buffer Cache라는 메모리 영역을 생략하고 기록해 로그파일의 기록을 최소화시켜서 입력 시 성능을 향상

 

테이블 용량이 초기화되지 않는다?

- Oracle 데이터베이스는 저장공간을 할당할 때 Extent 단위로 할당

- 테이블에 데이터가 입력되면 Extent에 저장

- 만약 Extent의 크기가 MAX_INSTENT를 넘어서게 되면 용량 초과 오류 발생

- DELETE문은 삭제 여부만 표시하고 용량은 초기화되지 않음

 

/*+ INDEX_DESC(A) */ : 테이블에 생성된 인덱스를 내림차순으로 읽게 함

인덱스 칼럼에 형변환을 수행하면 인덱스를 사용하지 못한다.

- 인덱스 : 데이터를 빠르게 조회하기 위해서 인덱스 키를 기준으로 정렬해 놓은 데이터

- 인덱스는 변형 발생 시 인덱스를 사용할 수 없으며, 인덱스 칼럼에 형변환 발생 시 인덱스 사용 불가

[Oracle DB에서 제공하는 문자열 함수]

FUNCTION	EXPLANATION
ASCII(문자)	문자 혹은 숫자를 ASCII 코드값으로 변환
CHAR(ASCII 코드값)	ASCII 코드값을 문자로 변환
SUBSTR(문자열, m, n)	문자열에서 m번째 위치부터 n개를 자른다
CONCAT(문자열1, 문자열2)	문자열 1번과 문자열 2번 결합 Oracle은 'II', MS-SQL은 '+' 사용 가능
LOWER(문자열)	영문자를 소문자로 변환
UPPER(문자열)	영문자를 대문자로 변환
LENGTH 혹은 LEN(문자열)	공백 포함 문자열의 길이를 알려준다
LTRIM(문자열, 지정문자)	왼쪽에서 지정된 문자를 삭제 지정된 문자를 생략하면 공백 삭제
RTRIM(문자열, 지정문자)	오른쪽에서 지정된 문자를 삭제 지정된 문자를 생략하면 공백 삭제
TRIM(문자열, 지정문자)	왼쪽 및 오른쪽에서 지정된 문자 삭제 지정된 문자 생략하면 공백 삭제

DECODE

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

- EMPNO와 1000을 비교해서 같으면 True, 다르면 False 출력

CASE

CASE[expression]
	WHEN condition_1	THEN result_1
    WHEN condition_2	THEN result_2
    
    ...
    
    WHEN condition_n	THEN result_n
    ELSE result
END

 

EXISTS

SELECT ENAME, DNAME, SAL
	FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
	AND EXISTS (SELECT 1 FROM EMP
    	WHERE SAL > 2000);
# SAL이 2000보다 큰 행이 있다면 TRUE가 조회된다.

 

SCALA- Subquery : 반드시 한 행과 한 칼럼만 반환하는 서브쿼리- 여러 행이 반환되면 오류 발생

SELECT ENAME AS "이름"
	SAL AS "급여",
    (SELECT AVG(SAL)
    	FROM EMP
     ) AS "평균급여"
  FROM EMP
 WHERE EMPNO=1000;

 

Correlated

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

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

 

ROLLUP

SELECT DECODE(DEPTNO, NULL, '전체합격', DEPTNO),
		SUM(SAL)
   FROM EMP
   GROUP BY ROLLUP(DEPTNO);

- DEPTNO에 대해서 GROUP BY로 급여합계를 계한하고 부서별 전체합계를 추가해서 계산

- ROLLUP : DEPTNO에 대해서 기존 GROUP BY와는 다르게 부서별 전체합계를 계산

- DECODE문을 전체합계 조회할 때 전체합계라는 문자를 출력하기 위해서 사용. DECODE문을 사용해서 DEPTNO가 NULL과 같으면 전체합계라는 문자를 출력하고 그렇지 않으면 부서번호를 출력한다.