10주차 ~ 12주차
10주차
<COMMIT과 ROLLBACK을 사용할 때의 차이>
1. DML의 데이터 삽입, 수정, 삭제 이후 COMMIT or ROLLBACK 전 데이터 상태
- 데이터베이스에 최종적으로 적용된 상태가 아니라 메모리 영역에만 적용된 상태이기 때문에 변경 전 상태로 복구 가능합니다.
- SELECT 구문을 통해 삽입, 수정, 삭제의 결과를 조회 할 수 있습니다.
- 작업자는 가능
- 작업자외 다른 사람은 불가능
- 변경된 행은 Locking 설정으로 인해 다른 사용자가 변경할 수 없음
2. DML의 데이터 삽입, 수정, 삭제 이후 COMMIT한 상황
- 데이터의 삽입, 수정, 삭제 내역이 데이터베이스에 완전하게 반영됩니다.
- COMMIT 실행 시점 이후에는 해당 구문의 실행으로 인한 변화로 이전 데이터는 완전히 삭제합니다.
- 작업자 뿐만 아니라 모든 사람은 SELECT 구문을 통해 동일한 결과를 얻을 수 있습니다.
- 변경된 행의 Locking이 모두 풀리고 모든 사용자는 특정한 행에 대한 조작이 가능합니다.
<SAVEPOINT>
- 트랜잭션 중간에 마치 게임을 저장하듯 'SAVEPOINT'라는 것을 설정할 수 있음
<트랜잭션의 병렬처리시 문제점>
- Dirty Read: 다른 c트랜잭션에 의해 수정이 되었지만 아직 커밋되지 않은 데이터를 읽는 경우
- Non-Repeatable Read: 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
- Phantom Read: 같은 쿼리를 두 번 수행했는데, 중간에 다른 트랜잭션으로 인해 값이 추가가 되어 적용이 된다면 첫 번째 쿼리에서 없던 유령(Phantom) 데이터가 두 번째 쿼리 결과로 나타나는 문제점이 발생
<DCL: 오라클 기본 유저>
- SYS: DBA ROLE을 부여받은 최상위 유저 → DB생성과 제거 가능 (default PW: CHANGE_ON_INSTALL)
- SYSTEM: DBA ROLE을 부여받은 유저 → DB생성과 제거 불가능 (default PW: MANAGER)
<Oracle 유저 생성 및 권한 부여 과정>
--1. Oracle SQL Plus 사용
CONN SCOTT/TIGER;
--2. ERROR 발생
CREATE USER PJS IDENTIFIED BY KOREA7;
--3. 유저 생성 권한 부여
CONN SYSTEM/비밀번호;
GRANT CREATE USER TO SCOTT;
--4. 정상 동작
CONN SCOTT/TIGER;
CREATE USER PJS IDENTIFIED BY KOREA7;
--5. ERROR 발생
CONN PJS/KOREA7;
--6. 로그인 권한 부여
CONN SYSTEM/비밀번호;
GRANT CREATE SESSION TO PJS;
--7. 정상 동작
CONN PJS/KOREA7;
--8. ERROR 발생
CREATE TABLE MENU (MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));
--9. 테이블 생성 권한 부여
CONN SYSTEM/비밀번호;
GRANT CREATE TABLE TO PJS;
--10. 정상 동작
CONN PJS/KOREA7;
CREATE TABLE MENU (MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));
<SQL Server 유저 생성 및 권한 부여 과정>
--1. 유저 매핑
CREATE LOGIN PJS WITH PASSWORD='KOREA7', DEFAULT_DATABASE=AdventureWorks
--2. 데이터베이스로 이동하여 유저 생성
USE ADVENTUREWORKS;
GO CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo;
--3. ERROR 발생
CREATE TABLE MENU (MENU_SEQ INT NOT NULL, TITLE VARCHAR(10));
--4. 권한 부여
GRANT CREATE TABLE TO PJS;
--5. 스키마에 권한 부여
GRANT Control ON SCHEMA::dbo TO PJS
--6. 정상 동작
CREATE TABLE MENU (MENU_SEQ INT NOT NULL, TITLE VARCHAR(10));
<시스템 권한: 기본 권한>
- system_privilege: 부여할 시스템 권한의 이름
- role: 부여할 데이터베이스 역할의 이름
- user, role: 부여할 사용자 이름과 다른 데이터 베이스 역할 이름
- PUBLIC: 시스템권한, 또는 데이터베이스 역할을 모든 사용자에게 부여 가능
- WITH ADMIN OPTION: 권한을 부여 받은 사용자도 부여 받은 권한을 다른 사용자 또는 역할로 부여 가능
<시스템 권한: 객체 권한>
- object_privilege: 부여할 객체권한의 이름
- object: 객체명
- user, role: 부여할 사용자 이름과 다른 데이터 베이스 역할 이름
- PUBLIC
- 객체권한, 또는 데이터베이스 역할을 모든 사용자에게 부여 가능
- PUBLIC으로 권한을 부여하면 회수할 때도 PUBLIC으로 설정
- WITH GRANT OPTION: 권한을 부여 받은 사용자도 부여 받은 권한을 다른 사용자 또는 역할로 부여 가능
<시스템 권한: 객체 권한의 회수>
- CASCADE CONSTRAINTS: 참조 객체 권한에서 사용 된 참조 무결성 제한을 같이 삭제 할 수 있습니다.
- WITH GRANT OPTION: 객체 권한을 부여한 사용자의 객체 권한을 철회하면, 권한을 부여받은 사용자가 부여한 객체 권한 또한 같이 철회되는 종속철회가 발생합니다.
<ROLE>
- ROLE을 이용하면 권한 부여와 회수를 쉽게 할 수 있음
CONN SYSTEM/비밀번호;
-- ① ROLE의 생성합니다.
CREATE ROLE LOGIN_TABLE;
-- ② ROLE에 권한 부여합니다.
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;
-- ③ ROLE을 사용자 또는 ROLE에게 부여합니다.
GRANT LOGIN_TABLE TO CHELSEA;
<오라클 DBMS에서 제공하는 ROLE 정리>
1. ROLE: CONNECT (접속)
- 부여권한: CREATE SESSION
2. ROLE: RESOURCE (객체 생성)
- 부여권한
- CREATE CLUSTER
- CREATE PROCEDURE
- CREATE TYPE
- CREATE SEQUENCE
- CREATE TRIGGER
- CREATE OPERATOR
- CREATE TABLE
- CREATE INDEXTYPE
11주차
<Non EQUI JOIN>
- 두 개의 테이블 간에 칼럼들이 서로 정확하게 일치하는 것이 아닌 특정 범위 내에 있는 경우
SELECT e.ename '사원명', e.sal '급여', s.grade '급여등급'
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;SELECT COUNT(*) AS CNT
FROM emp_tbl a, rule_tbl b
WHERE a.ename LIKE b.rule;
<일반 집합 연산자>
- UNION: 공통 교집합의 중복을 없애기 위한 사전 작업으로 시스템의 부하를 주는 정렬 작업이 발생
- UNION ALL: 공통 집합을 중복해서 그대로 보여줌 (효율적)
- INTERSECTION: 두 집합의 공통된 값을 추출
- DIFFERENCE: 공통집합을 제외한 부분을 출력 (Oracle: MINUS, ANSI 표준: EXCEPT)
- PRODUCT: 곱집합, JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합
<순수 관계 연산자>
- SELECT: SELECT 문에서는 WHERE 절로 표현 가능, 특정 행(row)에 대한 부분 집합
- PROJECT: 특정 열(col)에 대한 부분 집합
- JOIN: 공통 속성을 중심으로 두 개의 테이블을 하나로 합침
- DIVIDE: 현재는 SQL문으로 구현되어 있지 않은 연산자
<대표적인 JOIN 방법 6가지>
- INNER JOIN
- OUTER JOIN
- NATURAL JOIN: 동일한 이름을 갖는 모든 칼럼에 대해 INNER JOIN 수행 (Oracle만 지원)
- USING 조건절: NATURAL JOIN에 FROM 절에서 USING 조건을 이용하면 같은 이름을 가진 칼럼에서 원하는 칼럼만 선택적으로 INNER JOIN 가능 (Oracle만 지원)
- ON 조건절
- CROSS JOIN: PRODUCT의 개념으로 테이블 간의 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합
<NATURAL JOIN>
SELECT 칼럼1, 칼럼2, ..
FROM 테이블1 NATURAL JOIN 테이블2;
<USING 조건절>
SELECT 칼럼1, 칼럼2, .. -- JOIN 컬럼에 대해 Alias 사용 불가
FROM 테이블1 JOIN 테이블2
USING (EXPR); -- 괄호 없으면 에러 발생
<CROSS JOIN>
SELECT ename, dname
FROM emp CROSS JOIN dept
ORDER BY ename;
<계층형 질의>
- 계층형 데이터는 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 의미
- 계층형 데이터를 조회하기 위해서는 계층형 질의를 사용해야 함
<계층형 질의: 용어 정리>
- LEVEL - 각 데이터의 계층
- NODE - 각각의 데이터
- ROOT (NODE) - 최상위 노드
- Parent Node - 노드의 상위 노드
- Child Node - 노드의 하위 노드
- Leaf Node - 하위 노드가 없는 노드
<Oracle 계층형 질의 구문>
- SELECT
- FROM
- WHERE
- START WITH
- 계층 구조 전개의 시작 위치를 지정하는 구문
- 어디서부터 계층 질의를 시작하는지 루트 데이터(루트 노드 행)를 설정하는 구문
- CONNECT BY [NOCYCLE]
- 전개되어질 자식 데이터를 지정하는 구문 (연결 고리)
- 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야 함
- NOCYCLE
- 이미 나타났던 동일한 데이터가 전개 중에 다시 나타나는 경우 --> CYCLE
- 사이클이 발생한 이후의 데이터는 전개하지 않게 할 수 있음
- [PRIOR] A AND B
- CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정 가능
- `PRIOR 자식 = 부모` 형태로 사용
- 부모 데이터에서 자식 데이터 (부모 → 자식) 방향으로 작성하면 순방향
- `PRIOR 사원 = 관리자`
- 자식 데이터에서 부모 데이터 (자식 → 부모) 방향으로 작성하면 역방향
- `PRIOR 관리자 = 사원`
→ 순환 구조를 만든다 == 계층(LEVEL)을 만듦
→ 이전의(prior) 컬럼 A가 = 현재의 컬럼 B와 같으면 이전에 가지고 있던 LEVEL에 1을 더해서 하위 LEVEL로 지정
- 계층형 질의에서 문제가 나온다면 🤔 `START WITH` & `PRIOR` 부분을 해석하는게 핵심!
- OREDER SIBLINGS BY: 형제 노드(동일한 LEVEL) 사이에서 정렬을 수행하는 구문
<계층형 질의를 사용할 때 가상 칼럼(Pseudo Column)을 제공>
- LEVEL
- 루트 데이터이면 1이 값으로 정해지고 그 하위 데이터이면 2가 값으로 정해짐
- 하위 데이터로 내려갈수록 1씩 증가
- CONNECT_BY_ISLEAF
- 전개 과정에서 해당 데이터가 각 트리 경로의 마지막 값이라면 1이되고 그렇지 않다면 0이 출력
- CONNECT_BY_ISCYCLE
- 전개 과정에서 자식 데이터를 확인
- 그 자식 데이터를 전개했을 때 다시 부모 데이터가 정해져 있다면 무한 반복으로 전개가 됨. 이렇게 무한 반복이 되는 경우 값이 1, 그렇지 않으면 값이 0
- 무한 반복을 방지하기 위한 NOCYCLE 옵션을 사용했을 때만 사용 가능
SELECT COL,...[LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE]
FROM TB1
WHERE condition AND condition
START WITH condition
CONNECT BY [NOCYCLE] condition AND condition ...
[ORDER SIBLINGS BY COL1, COL2, ...]
12주차
<집합 연산자>
- SELECT를 통해 얻은 결과 간의 집합 연산을 수행하는 연산자
<집합 연산자의 종류>
- UNION (합집합 중복 허용 x)
- SQL문 결과에 대한 합집합
- 모든 중복된 행을 하나의 행으로 만들어서 합집합의 결과를 반환
- 중복 배제하기 위한 정렬 연산이 있어 시스템의 부하가 있음
- UNION ALL (합집합 - 중복 허용 o)
- SQL문 결과에 대한 합집합
- 중복된 행도 그대로 포함하여 합집합 결과를 반환하는 연산자
- 주로 여러 쿼리문의 결과가 상호 배타적일 때 많이 사용
- INTERSECT (INTERSECTION)
- SQL문 결과에 대한 교집합
- 중복을 허용하지 않고 하나의 행을 만들어 줌
- MINUS(EXCEPT)
- SQL문 결과에 대한 차집합
- 특정한 SQL문 결과에서 다른 SQL문 결과를 뺀 값
- 중복을 허용하지 않고 하나의 행으로 만들어 줌
<서브쿼리>
- 하나의 SQL 문안에 포함되어 있는 또 다른 SQL 문
<서브쿼리 사용 시 주의사항>
1. 서브쿼리는 소괄호`()`로 감싸서 사용
2. 서브쿼리는 단일행 또는 복수행 비교 연산자와 함께 사용 가능
- 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 함
- 복수 행 비교 연산자는 서브쿼리의 결과 건수와 관련이 없습니다. 1건도 가능하고 여러 건도 가능
3. 서브쿼리에서는 ORDER BY를 사용 불가능
- ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 메인쿼리의 마지막 문장에 위치해야 함
<뷰>
- 데이터베이스에서 저장된 정보를 좀 더 편리하게 보여주기 위해 사용되는 가상의 테이블
- 이미 존재하는 데이터 테이블을 사용하여 원하는 정보를 필터링하거나 정리하여 새로운 "가상" 테이블을 만드는 것
<뷰: CREATE VIEW 문을 통해서 생성>
CREATE VIEW V_DEPT_EMP AS
SELECT E.EMPNO,
E.ENAME,
E.JOB,
E.SAL,
D.DNAME
FROM DEPT D,
EMP E
WHERE D.DEPTNO = E.DEPTNO;
<뷰: 이미 존재하는 뷰를 참조해서도 생성 >
CREATE VIEW V_DEPT_EMP_FILTER AS
SELECT ENAME,
JOB
FROM V_DEPT_EMP
WHERE EMPNO IN (7698, 7788);
<뷰: 뷰를 사용하여 데이터를 조회>
SELECT ENAME,
JOB
FROM V_DEPT_EMP
WHERE EMPNO IN (7698, 7788);
<뷰: 뷰를 제거하기 위해서는 DROP VIEW 문을 사용>
DROP VIEW V_DEPT_EMP;
DROP VIEW V_DEPT_EMP_FILTER;