Lecture 15 - SQL(5)

※Review
DML
    insert
    update
    delete
    merge

    truncate

트랜잭션(Transaction)
접속사용자
    테이블
        - 1명 => 여러명이 동시에 한테이블에 접속할 경우도 있다.
    테이블
        - <-  시간 -> delete            사람 A
                  -> update               사람 B - 업데이트 하려보니 자료가 사라짐....
=> 한사람이 동작을 하고 있으면 다른 사람은 하지마!! 라는 개념으로 만들어 진게 트랜잭션(Transaction)이다. 처음 사용자가 LOCK을 걸고 다하면 LOCK을 푼다. 허나 이 기능은 DML 문만 가능하다.
클라이언트 <-> 서버 : 세션 - 아이디가 달라야 생기는 것은 아니다. 아이디가 같아도 접속 방법이 다르면 다른 세션이다.

서로 같은 아이디로 로그인 하였으나 왼쪽과 오른쪽 결과가 다르다 이것이 트랜잭션이다

rollback을 하면 모든 변경사항을 취소한다.

commit을 하면 우측에도 마침내 적용이 된 것을 확인할 수 있다.

좌측에서 먼저 업데이트 후 우측에서도 같은 업데이트를 진행하면 우측은 멈춘다(Lock걸림) - 껏다 켜도

insert는 잘 된다.

우측 락이 걸린 상태에서 좌측 rollback을 해서 락을 푸는 순간 우측 진행이 된다.

Commit

Rollback

읽기 일관성

데이터 
    수정
    삽입
    삭제
    수정
    삽입
    삭제
    commit; (All) - 부분 Commit이란 개념 없다. 전부다 commit되던가 전부다 취소되던가 한다.
    rollback; (부분) - 부분 rollback이 가능하다. savepoint

Savepoint

save point a -> save point b -> rollback to a -> rollback to b는 안됨!!

88p
1. 부서 테이블에 부서코드가 99, 부서명은 '관리과', 위치는 '대구'인 행을 입력하시오
2. 부서 테이블에 99번 부서의 부서명을 '회계과'로 변경하시오
3. 부서 테이블의 99번 부서 행을 삭제하시오
4. 트랜잭션이 자동 커밋 되는 시점을 모두 고르시오
    a. DDL 문장의 실행
    b. DCL 문장의 실행
    c. 시스템 오류
    d. SQL * Plus의 정상 종료
5. 현재 진행 중인 트랜잭션을 종료하고 모든 데이터 변경사항을 취호사는 명령어는 (              )이며, 데이터의 변경사항을 데이터베이스에 영구히 반영하는 명령어는 (              )이다.

뷰(View)의 작성
    sql(select) 문장 - 별명
    view - dml(X)

뷰를 만드려고 하였으나 권한이 없다

권한 부여한다

비로서 뷰 생성 가능

 create view empvu10 
 as select empno, ename, sal 
 from emp 
 where deptno=10;

->  select * from (select empno, ename, sal 
                        from emp 
                        where deptno=10);

와 같이 서브큐리를 이용한 select 문구와 같은 내용이다.
위와 같이 사용한 서브큐리를 '인라인 뷰'라고 한다.

114p 집계 함수나 조인 문장이 포함된 복합 뷰 생성하는 방법

( ) 절을 view로 설정하여 위와 같이 작성하여 확인 가능하다

view는 alter가 없지만 대신 replace가 있고 drop은 가능하다.

Top-N 분석

rownum이라는 수도 클래스(없는 컬럼)가 뒤죽박죽으로 출력됨

서브쿼리를 이용하여 정렬된 데이터르 출력

Top-N 쿼리를 이용하여 최상 5위까지 출력

실무에서 작업을 할 때 select * from 테이블(X) - 데이터가 백만개가 될수 도 있기 때문에
1. desc 테이블 구조
2. count 데이터 갯수
3. 5개 이내의 데이터만 출력
     Top-N 쿼리를 쓴다.

118p 
1. 부서명과 사원명을 출력하는 뷰 DNAME_ENAME_VU를 작성하시오.
2. 사원명과 사수명을 출력하는 뷰 WORKER_MANAGER_VU를 작성하시오
3. 사원 테이블에서 사번, 사원명, 입사일을 입사일이 늦은 사원 순으로 정렬하시오
4. 사원 테이블에서 사번, 사원명, 입사일을 입사일이 늦은 사원 5명을 출력하시오
5. 사원 테이블에서 사번, 사원명, 입사일을 입사일이 6번째로 늦은 사원부터 10번재 사원까지 출력하시오.

데이터베이스 객체

순서를 매기고 싶은 값들에 sequence를 쓴다. (서류 번호 자동 증가 같은거)

10씩 증가하다가 100에 도달하고난 이후에는 에러난다.

인덱스
기존 정렬방식이 아닌 b*tree(balance tree)를 사용한다. - 중간값 정렬 방식
검색은 select

인덱스 생성

인덱스 확인

함수 기반 인덱스

인덱스 삭제

동의어

125p
1. 초기값 1, 중분 1, 최소값 1, 최대값 9999인 TEST_SEQ를 생성하시오. 단, 시퀀스 번호는 순환되지 않으며, 시퀀스 번호 추출시 한번에 10개씩 메모리에 캐시하도록 한다
2. 사원 테이블의 외래키인 부서코드 컬럼에 인덱스를 작성하시오
3. 사원 테이블의 사원명 컬럼과 업무 컬럼에 결합 인덱스를 작성하시오
4. 부서 테이블에 대한 동의어 D를 생성하시오
5. 사원 테이블에 대한 공용 동의어 E를 생성하시오

사용자 접근 제어

사용자가 된다고 모든 권한을 사용할 수 있는 것은 아니다

create session 권한 부여

create table 권한 부여

table 공간을 사용할 권한 부여

톰을 락 한 순간 인서트는 된다

exit로 나와서 다시 톰을 로그인 했을때 톰이 락 되었다

톰의 락을 푼거

톰의 비밀번호를 바꾼후 우측에서 기존 비번 시도시 로그인x 바꾼 비번으로 시도시 로그인 O

권한
    시스템 권한                           - 관리자
    스키마(사용자) - 객체            - 객체 생성자(소유자)
=> 다른 사용자에게 부여

좌측(Scott)에서 우측(Tom)으로 권한을 주게 되어 우측에서 열람이 가능

좌측(Scott)에서 Revoke(권한 회수) 하여 우측(Tom)에서 열람 불가능

좌측(Scott)에서 우측(Tom)으로 insert 권한은 주지 않아 우측(Tom)에서 insert 하지 못함

좌측(Scott)에서 우측(Tom)으로 insert 권한을 주어 비로서 insert가 가능

grant update(loc)은 loc만 변경 가능한 권한을 준다는 뜻이다. (부분적 허용)

부여된 권한의 확인

좌측(Scott-grantor, owner), 우측(Tom-grantee)의 권한 조회

롤(Role)

권한의 집합 - 롤을 사용하여 권한의 부여, 회수 작업을 단순화 할 수 있다.

connect, resource, tablespace 권한을 tiger에게 주었고 이는 모든 권한을 사용가능하다.

데이터베이스 링크

 

로컬 조회

상대방(Remote) 조회

원격(Remote)와 로컬과 join 가능 - 데이터 백업시 사용 emp@link1 e(원격) 와 dept d(로컬)

133P
1. 계정이 KIM, 암호가 LION인 사용자 계정을 작성하시오
2. KIM에게 CREATE TABLE과 CREATE SESSION 권한을 부여하시오
3. KIM에게 SCOTT의 DEPT, EMP 테이블의 SELECT 권한을 부여하시오
4. KIM에게 SCOTT의 EMP 테이블에 SAL, COMM 컬럼을 UPDATE 할 수 있는 권한을 부여하시오
5. KIM에게 부여된 EMP 테이블의 UPDATE 권한을 회수하시오