[SQLD]. SQL 기본

계층형 데이터베이스

트리(Tree) 형태의 자료구조에 데이터를 저장하고 관리한다. 1대 N 관계를 표현한다.

 

네트워크 데이터베이스

오너(Owner)와 멤버(Member) 형태로 데이터를 저장한다. 1대N과 함께 M대N 표현도 가능하다.

 

관계형 데이터베이스

릴레이션에 데이터를 저장하고 관리한다.

릴레이션을 사용해서 집합 연산과 관계 연산을 할 수 있다.

 

집합연산

합집합( Union )	두 개의 릴레이션을 하나로 합치는 것 중복된 행은 한 번만 조회된다.
차집합 (Difference)	본래 릴레이션에는 존재하고 다른 릴레이션에는 존재하지 않는 것을 조회한다.
교집합 (Intersection)	두 개의 릴레이션 간에 공통된 것을 조회한다.
곱집합 (Cartesian product)	각 릴레이션에 존재하는 모든 데이터를 조합하여 연산한다.

관계연산

선택 연산(Selection)	릴레이션에서 조건에 맞는 행(튜플)만을 조회한다.
투영 연산(Projection)	릴레이션에서 조건에 맞는 속성만을 조회한다.
결합 연산(Join)	여러 릴레이션의 공통된 속성을 사용해서 새로운 릴레이션을 만들어 낸다.
나누기 연산(Division)	나누는 릴레이션의 속성을 삭제한 후 중복된 행을 제거하는 연산

---

기본키(Primary Key)

유일성, 최소성 , Not Null 을 만족하면서 테이블을 대표하는 것이다.

 

행 : Row , Tutle

열 : Column , Atrribute

 

외래키(Foreign Key)

다른 테이블의 기본키를 참조(조인)하는 칼럼이다.

외래키는 관계 연산 중에서 결합 연산(Join)을 하기 위해서 사용한다.

---

SQL의 종류

종류	설명
DDL(Data Definition Language)	구조를 정의하는 언어 CREATE , ALTER , DROP , RENAME
DML(Data Manipulation Language)	테이블에서 데이터를 입력, 수정 , 삭제 , 조회한다. INSERT , UPDATE , DELETE , SELECT
DCL (Data Control Language)	사용자에게 권한을 부여하거나 회수한다. GRANT , REVOKE , TRUMCATE
TCL (Transcation Control Language)	트랜잭션을 제어하는 명령어이다,. COMMIT , ROLLBACK , SAVEPOINT

트랜잭션의 특성

원자성(Atomicity)	트랜잭션은 데이터베이스 연산의 전부가 실행되거나 전혀 실행되지 않아야 한다. (ALL OR NOTHING) 즉, 트랜잭션의 처리가 완전히 끝나지 않았을 경우는 실행되지 않은 상태와 같아야 한다.
일관성 ( Consistency )	트랜잭션 실행 결과로 데이터베이스의 상태가 모순되지 않아야 한다. 트랜잭션 실행 후에도 일관성이 유지되어야 한다.
고립성 ( Isolation )	트랜잭션 실행 중에 생성하는 연산의 중간에 다른 트랜잭션이 접근할 수 없다, 즉, 부분적인 실행 결과를 다른 트랜잭션이 볼 수 없다.
영속성 ( Durability )	트랜잭션이 그 실행을 성공적으로 완려하면 그 결과는 영구적으로 보장이 되어야 한다.

SQL 문 실행 순서

파싱(Parsing)

- SQL문의 문법을 확인하고 구문분석한다.

- 구문분석한 SQL문은 Library Cache에 저장한다.

 

실행(Excute)

- 옵티마이저(Optimizer)가 수립한 실행 계획에 따라 SQL을 실행한다.

 

인출(Fetch)

- 데이터를 읽어서 전송한다.

---

DDL (Data Definition Language)

Create Table EMP {      empno number(10) primary key,      ename varchar2(20),      sal number(6) }

테이블 구조 확인

DESC EMP;

 

제약조건 종류

기본키 (Primary key) , 외래키 (Foreign key) , 기본값(default) , not null 

 

외래키 지정

constraint deptfk foreign Key(deptno)              references dept(deptno)

deptfk : 외래키 이름

deptno : EMP 테이블의 deptno

deptno : DEPT 테이블의 deptno 칼럼

 

사원테이블이 부서 테이블의 deptno를 참조한 것

 

CASCADE

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

 

ON DELETE CASCASDE 옵션은 자신이 참조하고 있는 테이블의 데이터가 삭제되면 자동으로 자신도 삭제되는 옵션

참조무결성을 준수할 수 있다.

---

ALTER TABLE 문

테이블명 변경 : ALTER TABLE ~ RENAME TO 

칼럼추가 : ALTER TABLE ~ ADD

칼럼 변경 : ALTER TABLE ~ MODIFY

칼럼 삭제 : ALTER TABLE ~ DROP COLUMN

칼럼명 변경 : ALTER TABLE ~ RENAME COLUMN ~ TO

 

테이블 삭제

DROP TABLE EMP;

 

DROP TABLE EMP CASCADE CONSTRAINT;

참조된 제약사항까지도 모두 삭제

---

뷰(View) 생성과 삭제

• 뷰란 테이블로부터 유도된 가상의 테이블이다.
• 실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만 조회할 수 있게 한다.
• 뷰는 데이터 딕셔너리(Data Dictionary)에 SQL문 형태로 저장하되 실행 시에 참조된다.

뷰의 특징

• 참조한 테이블이 변경되면 뷰도 변경된다.
• 뷰의 검색은 참조한 테이블과 동일하게 할 수 있지만, 뷰에 대한 입력 , 수정 , 삭제에는 제약이 있다.
• 특정 칼럼만 조회시켜서 보안성을 향상시킨다.
• 한번 생성된 뷰는 변경할 수 없고 ALTER 문을 사용해서 뷰를 변경할 수 없다.

CREATE VIEW T_EMP AS   SELECT * FROM EMP;

뷰의 장점과 단점

장점	단점
특정 칼럼만 조회할 수 있기 때문에 보안 기능이 있다. 데이터 관리가 간단하다. SELECT문이 간단해진다. 하나의 테이블에 여러 개의 뷰를 생성할 수 있다.	뷰는 독자적인 인덱스를 만들 수 없다. 삽입 , 수정, 삭제 연산이 제약된다. 데이터 구조를 변경할 수는 없다.

---

DML(Data Mainpulation Language)

INSERT 문

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

 

Nologging 사용

로그파일의 기록을 최소화시켜서 입력 시 성능을 향상시키는 방법

---

UPDATE 문

UPDATE EMP         SET ENAME = '조조' WHERE EMPNO = 100;

UPDATE 문에 조건문을 입력하지 않으면 모든 데이터가 수정되니까 조심

데이터를 수행할 때 조건절에서 검색되는 행 수만큼 수정( EMPNO 가 100인 사람이 2명이면 둘다 '조조'로 수정됨 )

---

DELETE 문

DELETE FROM EMP         WHERE EMPNO = 100;

조건문을 입력하지 않으면 모든 데이터가 삭제됨.

 

TRUNCATE

DELETE 문을 사용하면 테이블의 모든 데이터가 삭제되지만 테이블의 용량은 감소하지 않는다.

이럴 경우 TRUNCATE TABLE EMP; 를 실행시켜야한다.

 

이럴경우 테이블의 모든 데이터를 삭제하고, 삭제되면 테이블의 용량은 초기화된다.

---

SELECT 문

SELECT ENAME || '님' FROM EMP;

ENAME 칼럼 뒤에 '님'이라는 문자를 결합한다.

 

ORDER BY 

오름차순(default , ASC; )  , 내림차순 ( DESC; )

시점 : ORDER BY 가 정렬을 하는 시점은 모든 실행이 끝난 후이다.

정렬은 데이터베이스 메모리를 많이 사용하게 됨으로 성능 저하가 발생

메모리 내부에 할당된 SORT_AREA_SIZE가 너무 작으면 성능 저하가 발생한다.

 

INDEX를 이용하면 정렬을 회피할 수 있다.

Primary Key는 자동 오름차순

힌트 : /*+ INDEX_DESC(A) */

인덱스를 기준으로 내림차순정렬  A = alias

 

DISTINCT : 중복된 데이터를 한 번만 조회

SELECT DISTINCT DEPINO FROM EMP ORDER BY DEPINO;

Alias : 테이블명이나 칼럼명이 너무 길어서 간략하게 할 때 사용.

---

WHERE 문

같지 않은 것을 조회하는 비교연산자

!= , ^= , <> , NOT 칼럼명 = 

 

LIKE 절

"a%b"  -  ab , axb , aonb

"T%" - Tiger , Toast , Tuck

"a_b" - axb , aob , agb

"%est%" - 중간에 'est'가 있는 모든 것

 

IN 문

IN문은 'OR' 의 의미를 가지고 있어서 하나의 조건만 만족해도 조회가 된다.

SELECT * FROM EMP WHERE (JOB , ENAME ) IN (('CLERK','test1'),('MANAGER','Test4));

 

NULL 함수	설명
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	NULL이 아닌 최소의 인자 값을 반환한다. COALESCE(exp1,exp2,exp3,...) 은 exp1이 NULL이 아니면 exp1의 값을, 그렇지 않으면 그 뒤의 값의 NULL 여부를 판단하여 값을 반환한다.

COALESCE

	COMM	SAL	COAL
1000	NULL	800	800
1001	300	1600	300

1000 인덱스는 첫 번째 값이 NULL이니까 두 번째 800을 대입

1001 인덱스는 첫 번째 값이 300 이니까 첫 번째 300을 대입

---

GROUP BY 문

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

SELECT DEPTNO.SUM(SAL) FROM EMP GROUP BY DEPTNO;

 

GROUP BY 에 조건절을 사용할려면 HAVING을 사용해야한다.

SELECT DEPTNO.SUM(SAL) FROM EMP GROUP BY DEPTNO;

* 직업별(JOB) 급여합계 중에 급여(SAL)합계가 1000 이상인 직업 조회

---

SELECT 문의 실행 순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

암시적 형변환 ( 자동으로 시스템이 형변환 )

SELECT * FROM EMP   WHERE EMPNO = '100';

위의 처럼 할 경우 자동으로 '100'을 숫자형으로 바꾸는 것이 아니라

EMPNO 칼럼을 문자형 칼럼으로 다 바꿔버린다. 그래서 인덱싱이 불가능해짐.

 

DUAL 테이블

Oracle 데이터 베이스에 의해서 자동으로 생성되는 테이블

사용자가 임시로 사용할 수 있는 테이블

 

TRIM() : 공백제거

SYSDATE() : 오늘 날짜 구하기

EXTRACT() : 연도만

SIGN(숫자) : 음수와 양수를 구분

CEIL / CEILING(숫자) : 숫자보다 크거나 같은 최소의 정수를 반환 3.4 => 4

FLOOR(숫자) : 숫자보다 작거나 같은 최대의 정수를 돌려준다. 3.4 => 3

TRUNC(숫자 , m) : 소숫점 m 자리에서 절삭한다.

 

DECODE 문 = if 문과 비슷

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

* EMPNO 와 1000을 비교해서 같으면 'TRUE'를 출력하고 다르면 'FALSE"를 출력한다.

 

CASE 문 

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

C언어의 Swich문과 비슷

---

ROWNUM

조회되는 행 수를 제한할 때 많이 사용된다.

화면에 데이터를 출력할 때 부여되는 논리적 순번

 

* Inline View

  SELECT문에서 FROM절에 사용되는 서브쿼리를 의미한다.

  ROWNUM의 값을 1,2,3,4,5,.... 같이 순차적으로 증가하는 ROWNUM 데이터를 얻고 싶을 때 인라인뷰를 사용하는 것 이다.

  FROM 절에 SELECT 문을 사용하면 그 것은 Inline View 이다.

SELECT * FROM ( SELECT ROWNUM list , ENAME FROM EMP ) WHERE list <=5;

위와 같이 5건의 행을 조회하기 위해서는 인라인 뷰를 사용하고 ROWNUM에 별칭을 사용해야 한다.

 

ROWID

ROWID 는 ORACLE 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값이다.

데이터가 어떤 데이터 파일 , 어느 블록에 저장되어 있는 지 알 수 있다.

구조	길이	설명
오브젝트 번호	1~6	오브젝트(Object) 별로 유일한 값을 가지고 있으며, 해당 오브젝트가 속해 있는 값이다.
상대 파일 번호	7~9	테이블 스페이스에 속해 있는 데이터 파일에 대한 상대 파일번호이다.
블록 번호	10~15	데이터 파일 내부에서 어느 블록에 데이터가 있는지 알려준다.
데이터 번호	16~18	데이터 블록에 데이터가 저장되어 있는 순서를 의미한다.

 

ex)   AAAGHSAABAAALLBAAA

---

WITH 구문

서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문

옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단한다.

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

 

DCL(Data Control Language)

GRANT문은 데이터베이스 사용자에게 권한을 부여한다.

데이터베이스 사용을 위해서는 권한이 필요하며 연결 , 입력, 수정 , 삭제 조회를 할 수 있다.

GRANT privileges(권한) ON object(테이블) TO user(사용자);

privileges

SELECT

INSERT

UPDATE

DELETE

REFERENCES

ALTER

INDEX

ALL

 

WITH GRANT OPTION

특정 사용자에게 권할을 부여할 수 있는 권한을 부여한다.

권한을 A 사용자가 B에 부여하고 B가 다시 C에게 부여한 후에 권한을 취소(Revoke)하면 모든 권한이 취소된다.

A->B->C

 

WITH ADMIN OPTION

테이블에 대한 모든 권한을 부여한다.

권한을 A 사용자가 B에 부여하고 B가 다시 C에게 부여한 후에 권한을 취소하면 B사용자 권한만 취소된다.

A->B

B->C

REVOKE : 권한회수

REVOKE privileges(권한) ON object(테이블) TO user(사용자);

 

TCL ( Transcation Control Language )

COMMIT

• INSERT , UPDATE , DELETE 문으로 변경한 데이터를 데이터베이스에 반영한다.
• 변경 전 이전 데이터는 잃어버린다.
• COMMIT 이 완료되면 ROCK이 해제된다. ( 평상시에 내가 사용할 때 다른 누구가 사용하지 못하게 LOCK )
• COMMIT을 실행하면 하나의 트랜잭션 과정을 종료한다.

 

Oracle 데이터 베이스는 암시적 트랜잭션 관리를 한다.

트랜잭션 종료는 사용자가 COMMIT 혹은 ROLLBACK으로 처리해야한다.

 

ROLLBACK

변경사항을 적용하지 않고 초기화 ( 이전에 COMMIT한 곳까지 만)

 

SAVEPOINT

이전 COMMIT 지점이 아닌 설정한 지점으로 이동

트랜잭션을 작게 분할하여 관리 지정된 위치 이후의 트랜잭션만 ROLLBACK 할 수 있다.

ROLLBACK TO <SAVEPOINT명>

SAVEPOINT와 관계없이 ROLLBACK할거면 그냥 "ROLLBACK"