1. WITH 구문
WITH 구문은 서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문이다. WITH로 지정하는 서브쿼리에 별칭을 지정할 수 있으며, 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단한다.
주로 서브쿼리문의 재사용성을 높이기 위해 사용한다. 특정 조건이나 데이터를 반복적으로 조회해야할 때, WITH 구문을 통해서 저장해놓고 사용하면 편리하다.
예제 )
WITH W_EMP AS
(SELECT * FROM EMP WHERE DEPTNO = 30)
SELECT * FROM W_EMP;
2. DCL 문 : GRANT, REVOKE
DCL(Data Control Language)는 보통 잘 사용하지 않는다. 실무에서 사용하는 클라우드 서비스에서는 이를 한 단계 더 추상화한 형태로 제공한다. 그러나 그 동작원리를 알고 있어야하고, 혹시 사용해야할 경우가 있을 수 있으므로 기록한다.
GRANT
문법
privileges는 권한, object는 테이블명이라고 생각하면 되고, user는 database에 등록한 사용자 이름이다.
GRANT {privileges} ON {object} TO {user}
예시
GRANT SELECT, INSERT, UPDATE, DELETE ON EMP TO EMP01;
privileges 목록
| SELECT | 테이블을 조회하는 권한이다. |
| INSERT | 테이블에 로우를 삽입하는 권한이다. |
| UPDATE | 테이블에 로우를 갱신하는 권한이다. |
| DELETE | 테이블에 로우를 삭제하는 권한이다. |
| ALTER | 스키마 객체의 특성을 변경하는 권한이다. |
| INDEX | 테이블에 인덱스를 생성하는 권한이다. |
| REFERENCES | 테이블을 참조하는 제약조건을 생성하는 권한이다. |
이외에 ALL은 테이블에 대한 모든 권한을 부여한다.
WITH GRANT OPTION
권한을 부여할 수 있는 권한을 부여하는 옵션이다. GRANT문 맨 마지막에 작성하면 된다.
| WITH GRANT OPTION | -사용자에게 권한을 부여할 수 있는 권한을 부여 -A 사용자가 B에게 부여하고, B가 C에게 부여한 후 A가 B의 권한을 취소하면 "모든 권한이" 취소된다. |
| WITH ADMIN OPTION | -테이블에 대한 모든 권한을 부여 -A 사용자가 B에게 부여하고, B가 C에게 부여한 후 A가 B의 권한을 취소하면 "B의 권한만" 취소된다. |
REVOKE
사용자의 권한을 회수한다.
문법
REVOKE privileges ON {object} FROM {user}
3. SAVEPOINT
SAVEPOINT는 COMMIT, ROLLBACK과 같은 TCL(Transaction Control Lanugage) 중의 하나로, 트랜잭션을 작게 분리하여 부분적으로 롤백하는 등의 관리가 가능하도록 저장하는 것이다.
참조 0.의 예제
SAVEPOINT t1;
INSERT INTO EMP VALUES(10, 20);
SAVEPOINT t2;
INSERT INTO EMP VALUES(20, 30);
ROLLBACK TO t2;
COMMIT;
SELECT * FROM EMP;마지막 줄의 조회 결과는 EMP 테이블에 10, 20 값이 들어간 1개 행만 조회된다.
4. JOIN
EQUI 조인
EQUI 조인은 ISO 표준 SQL로는 INNER JOIN을 말한다. 두 개의 테이블 간에 일치하는 교집합 릴레이션을 기준으로 테이블들을 조인한다.
SELECT * FROM EMP, DEPT
WHERE EMP.DEPNO = DEPT.DEPTNO;
INTERSECT
INTERSECT는 두 개의 테이블에서 공통된 값을 조회한다. 두 개의 테이블에 공통된 값이 있는 컬럼을 지정하여 INTERSECT를 실행하면 공통으로 존재하는 값들이 나온다.
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;
OUTER JOIN
left outer join은 from 테이블을 기준, right outer join은 조인하는 테이블을 기준을 한다. 그림으로 이해하는게 쉽다.
left outer join : from 테이블인 음식 테이블을 기준으로, 가게 테이블에 null인 값이 있더라도 4개 행으로 표시된다.
right outer join : join 하는 가게 테이블 기준으로 4개 행으로 표시된다.
만약 각 상황에서 inner join을 실행하면 외래키 가게 코드가 null인 값은 join되지 않아서 각 4개행, 2개행이 표기된다.
CROSS JOIN
카테시안 곱을 발생시킨다. 14개 행이 있는 테이블과 4개행이 있는 테이블에 CROSS JOIN을 하면, 14 x 4 = 56개 행이 조회된다.
SELECT * FROM EMP CROSS JOIN DEPT;
참조
0. [도서] 영진닷컴 - SQL 개발자 이론서 + 기출문제
1. 꿈꾸는 개발자, DBA 커뮤니티 구루비 - Tibero4 기본강좌_특권 관리
http://www.gurubee.net/lecture/2092