[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 { 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) |
GROUP BY 에 조건절을 사용할려면 HAVING을 사용해야한다.
|
SELECT DEPTNO.SUM(SAL) |
* 직업별(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"