SQL 다루기(3) : VIEW, INDEX, SEQUENCE, DB사용자 계정 다루기

0. OBJECT : TABLE, VIEW , INDEX, SEQUENCE

• 데이터를 저장하거나 참조(Reference하는 데 사용되는 데이터 구조
• OBJECT 생성 방법 : CREATE [OBJECT종류] [OBJECT명]

1. OBJECT - VIEW

• 물리적 테이블에 근거한 논리적 가상 테이블⇒ 일종의 SUBQUERY 로 생각해도 편할듯
• ⇒ 사실상 SELECT 문에 이름을 붙여놓은 것, 이 때문에 메모리 용량을 잡아먹지 않는다.
• USER_VIEWS 로 확인가능
• DML 모두 가능 : SELECT , UPDAET, CREATE, DELETE, DROP 가능
• UPDATE 시에는 ALTER 대신 다음 명령어를 사용한다.
  • CREATE OR REPLACE VIEW ~
• INSERT INTO [VIEW명] VALUES ( ~~ ) : VIEW에 INSERT 하면 기존 TABLE 에도 반영됨. 반대로 TABLE 에 INSERT 해도 VIEW 에 그대로 반영됨 ⇒ VIEW - TABLE 은 결국 SUBQUERY 로 동작하기 때문에 VIEW 에 INSERT 하면 TABLE 에 INSERT 가 된다.

## VIEW 확인 ##
SQL> SELECT VIEW_NAME, TEXT
  2  FROM USER_VIEWS;

VIEW_NAME
------------------------------
TEXT
--------------------------------------------------------------------------------
EMPVW30
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO = 30

## VIEW 원리 ##
SQL> SELECT * FROM EMPVW30;
QUERY 가 실행되는 순간 아래처럼 SUBQUERY 로 동작하여 결과를 가져오게 된다.
SQL> SELECT * FROM
  2  (SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 30);

SQL> CREATE OR REPLACE VIEW EMPVW30
  2  AS
  3  SELECT EMPNO, ENAME, COMM, DEPTNO
  4  FROM EMP
  5  WHERE DEPTNO = 30;

## VIEW INSERT ##
SQL> INSERT INTO EMPVW30 VALUES ( 1111, 'KING', 2000, 30);

하면 결국 아래처럼 실행되서 기존 TABLE 에도 넣어진다
SELECT * FROM (INSERT INTO EMPVW30 VALUES (9999, 'TEST', NULL, 30))

1) VIEW 의 종류

SIMPLE VIEW COMPLEX VIEW

AS 뒤 SELECT 문	단순한 문장	복잡한 문장 - DISTINCT, JOIN, GROUP BY, GROUP FUNCTION 등등
DML	항상 DML 이 잘 실행됨	DML 일부 혹은 전부가 수정이 안 될수도 있음

• VIEW를 잘 사용하면 처음부터 해당 데이터 값을 갖는 테이블이 있었던 것처럼 만들 수 있음
• COMPLEX VIEW 로 만들어진 VIEW 는 추후 DML 이 동작하지 않을 수 있다.
  • 예를 들어 아래처럼 DEPT_SUMSAL 인 VIEW 에 데이터를 추가한다고 하면 저장이 되지 않는다. 이는 SUMSAL 의 값이 SUM(SAL) 로 잡혀있기 때문에 DB 에서는 합계가 50000인 것은 알지만 몇 개의 행이 더해져서 - SUM - 50000인 것이지 모르기 때문에 INSERT 되지 않는다.

## 기존 TABLE ##
SQL> SELECT DEPTNO, SUM(SAL) FROM EMP
  2  GROUP BY DEPTNO
  3  ORDER BY DEPTNO;

## VIEW 만들기 - COMPLEX VIEW ##
SQL> CREATE VIEW DEPTNO_SUMSAL
  2  AS
  3  SELECT DEPTNO, SUM(SAL) SUMSAL
  4  FROM EMP
  5  GROUP BY DEPTNO
  6  ORDER BY DEPTNO;

SQL> SELECT * FROM DEPT_SUMSAL;

    DEPTNO     SUMSAL
---------- ----------
        10       8750
        20      10875
        30       9400

#########
SQL> INSERT INTO DEPT_SUMSAL VALUES ( 50, 50000);
INSERT INTO DEPT_SUMSAL VALUES ( 50, 50000)
*
1행에 오류:
ORA-01733: 가상 열은 사용할 수 없습니다

2. ROWNUM ⇒ STOPKEY

• ROWNUM 과 ROWID 는 DB 에서 임의적으로 지정해주는 테이블 행의 번호와 테이블 별 특별한 PRIMARY KEY 라고 생각하면 된다
• 내부 처리과정을 테이블로 출력한다 생각하면 됨
• ROWNUM 은 IF 문처럼 조건을 만족하지 않으면 중단됨 ⇒ IF(ROWNUM = N) == FALSE 면 그냥 중단
  • 만약 ROWNUM = 3 이라고 지정해두면 맨 첫 번째 ROWNUM 은 1이기 때문에 항상 거짓이고 더이상 실행되지 않고 중단된다.

ROWNUM 활용한 N번째로 큰 값 뽑아내기

1. ROWNUM 확인, SAL 내림차순 정렬
2. ROWNUM 확인, SAL 내림차순 정렬 을 VIEW로 생성
3. 생성된 VIEW 에서 다시 ROWNUM 을 확인하고 이를 통해 상위 N 번째까지를 가져올 수 있음

SQL> SELECT ROWNUM, ROWID, DEPTNO
  2  FROM EMP;

    ROWNUM ROWID                  DEPTNO
---------- ------------------ ----------
         1 AAAR3sAAEAAAACXAAA         20
         2 AAAR3sAAEAAAACXAAB         30

## ROWNUM 확인, SAL 내림차순 정렬 ##
SQL> SELECT ROWNUM, EMPNO, ENAME, SAL
  2  FROM EMP
  3  ORDER BY SAL DESC;

    ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         9       7839 KING             5000
        13       7902 FORD             3000

## ROWNUM 확인, SAL 내림차순 정렬 을 VIEW로 생성 ##
SQL> CREATE VIEW E1
  2  AS
  3  SELECT EMPNO, ENAME, SAL
  4  FROM EMP
  5  ORDER BY SAL DESC;

뷰가 생성되었습니다.

SQL> SELECT * FROM E1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5000
      7902 FORD             3000

## 생성된 VIEW 에서 다시 ROWNUM 을 확인하고 이를 통해 상위 N 번째까지를 가져올 수 있음 ##

SQL> SELECT EMPNO, ENAME, SAL, ROWNUM FROM E1
  2  WHERE ROWNUM <= 3;

     EMPNO ENAME             SAL     ROWNUM
---------- ---------- ---------- ----------
      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          3

## 4위만 ##
조회불가!
SQL> SELECT EMPNO, ENAME, SAL, ROWNUM FROM E1
  2  WHERE ROWNUM = 4;

선택된 레코드가 없습니다.

ROWNUM 을 활용한 N 보다크고 M 보다작은 값 - SAL 기준 4위에서 7위까지의 값 -

1. SAL 을 기준으로 정렬
2. 1)에서 정렬된 내용을 VIEW 로 생성 이때 ROWNUM 을 넣어서 ROWNUM 기준 1 ~ 7 까지만 뽑아냄 ⇒ ROWNUM ≤ 7
3. 1. 에서 뽑아낸 1 ~ 7 까지의 값을 갖고 다시 ROWNUM 을 붙여서 이번에는 ROWNUM ≥ 4 까지만 뽑아냄

⇒ 결과적으로 4 ≤ ROWNUM ≤ 7 이되고 해당 값들만 뽑아내짐

이때 VIEW 를 그때그때 만들어도 되고, FROM 절에 SUBQUERY 를 넣어서 INLINE-VIEW 로 만들어도 상관없음 → 대신 쿼리가 길어진다ㅠ

SQL> SELECT ROWNUM, RN, EMPNO, ENAME, SAL
  2  FROM ( SELECT ROWNUM RN, EMPNO, ENAME, SAL
  3             FROM ( SELECT EMPNO, ENAME, SAL
  4                     FROM EMP
  5                     ORDER BY SAL DESC)
  6             WHERE ROWNUM <=7 )
  7  WHERE RN >=4;

ROWID

• 테이블 별 ROW 하나 별 - 값 별 - 주소 값 ⇒ 일종의 주소값 같은 느낌으로 고유한 값을 같는다.

 

3. OBJECT - INDEX

• 인덱스- INDEX - 란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다
• ⇒ 책의 목차 기능처럼 검색 시 전체를 확인하는게 아니라 인덱스 - 목차 - 를 기준으로 먼저 확인한 후 검색한다.
• [CREATE OR DROP] INDEX [인덱스명] ON 테이블명(컬럼명)
  • CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME)
  • DROP INDEX IDX_EMP_ENAME
• 인덱스는 테이블을 빠른 검색을 위해 사용하며 보통 자주 검색되는 컬럼의 VALUE 을 ROWID 기준으로 만들어진다.
• INDEX 가 자동으로 생성되는 경우 : PK(PRIMARY KEY), UK(UNIQUE) ⇒ 이는 PK, UK 가 붙은 값들은 결국 DML 할때마다 중복한 값이 있는지 검색하고 확인하기 때문에 DB에서 자동으로 INDEX 를 만들어두어 검색을 빠르게 한다
• 사용자가 만든 INDEX 는 NONUNIQUE 로 지정된다.

SQL> CREATE INDEX IDX_EMP_ENAME
  2  ON EMP(ENAME);

SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
  2  FROM USER_INDEXES;

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
EMP1_EMPNO_PK                  NORMAL                      EMP1                           UNIQUE
PK_EMP                         NORMAL                      EMP                            UNIQUE
IDX_EMP_ENAME                  NORMAL                      EMP                            NONUNIQUE
SYS_C0011117                   NORMAL                      DEPT1                          UNIQUE
SYS_C0011118                   NORMAL                      DEPT1                          UNIQUE
DEPT1_LOC_UK                   NORMAL                      DEPT1                          UNIQUE

 

4. OBJECT - SEQUENCE

• 숫자 발생기 ⇒ 쉽게 생각하면 다른 언어의 FOR 문
• 주로 INSERT 문에서 사용 ⇒ AUTO_INCREMENT 와 비슷함. 즉 10 까지 찍고, 11을 삭제하고 다시 값을 추가하면 11부터 연속된 값으로 추가되는 것이 아니라 12 부터 시작한다
• 사용 명령어
  • CREATE SEQUENCE [시퀸스명]
  • START WITH [시작번호]
  • INCREMENT BY [증가량]
  • NOCACHE OR CACHE [숫자] : 다음 숫자값만큼을 메모리에 저장해두고 다음에 불러옴
  • NOCYCLE OR CYCLE : 순환 없음 ⇒ 순환하면 최댓값 다음 → 최솟값으로
  • MINVALUE [최솟값]
  • MAXVALUE [최댓값]
• [SEQUENCE명].NEXTVAL 해서 다음값을 가져올 수 있다
• DROP SEQUENCE [시퀸스명]

SQL> CREATE SEQUENCE DEPT_DEPTNO
  2  START WITH 1
  3  INCREMENT BY 1
  4  NOCACHE
  5  NOCYCLE
  6  MINVALUE 1
  7  MAXVALUE 100;

## NOCYCLE ##
SQL> SELECT DEPT_DEPTNO.NEXTVAL, EMPNO FROM EMP;
1행에 오류:
ORA-08004: 시퀀스 DEPT_DEPTNO.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다

## CYCLE ##
SQL> SELECT DEPT_DEPTNO.NEXTVAL, EMPNO FROM EMP;
NEXTVAL      EMPNO
---------- ----------
        98       7782
        99       7788
       100       7839
         1       7844

## CACCH ##
SQL> SELECT * FROM USER_SEQUENCES;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
EMP_EMPNO_SEQ                           1 1.0000E+28            1 N N         20          41

실제 마지막으로 출력된 LAST_NUM 은 30 이다. 즉 20개씩 이미 가져와있다는 것을 확인 가능
				29       7902
        30       7934

 

5. DB 사용자 계정 다루기 : DDL

• 프롬프트에서 사용자 계정명 확인하기 : set sqlprompt "_user>”
• 유저 생성 후에 작업을 하기 위해서는 ‘권한’이 필요함 ⇒ privilege
  • system : db 전체에 영향, 로그인, 테이블 조회 등
    • GRANT, REVOKE
  • object : 해당 object 에 한에서만 영향
    • SELECT, DELETE, UPDATE, CREATE
  • 권한 부여는 ROOT 계정 - 오라클은 SYSTEM 인듯? - 으로만 가능!!
  • 여러 권한을 한번에 부여, 회수 할때는 , - 콘마- 로 구분함

유저 생성

• CREATE USER [유저명] IDENTIFIED BY [유저패스워드]

유저 삭제

• DROP USER [유저명]
• 만일 해당 유저가 단 하나라도 OBJECT 가 있다면 해당 유저는 삭제할 수 없다. 그래도 삭제하려면 CASCADE 옵션을 사용해야한다.
  • DROP USER [유저명] CASCADE

유저 변경

• 유저 패스워드 변경
  • ALTER USER [유저명] IDENTIFIED BY [비밀번호]
• 유저 잠금
  • ALTER USER [유저명] ACCOUNT LOCK / UNLOCK

권한 부여

• GRANT CONNECT TO [유저명] ⇒ 로그인 권한, SYSTEM
• GRANT RESOURCE TO [유저명] ⇒ 객체 생성, 수정, 삭제, SYSTEM
• GRANT [OBJECT 권한] ON [OBJECT명] TO [유저명] ⇒ OBJECT

권한 회수

• 권한 준 사람만 회수 가능
• REVOKE [권한] ON [OBJECT명] FROM [유저명] ⇒ OBJECT

다른 사용자의 테이블 조회

• A 계정에서 B 계정의 테이블을 조회하려하면 아래처럼 명령어를 사용해야함
• SELECT NAME FROM B.[테이블명];

SYSTEM>CREATE USER USER1
  2  IDENTIFIED BY tiger;

## user1 에게 resource 권한 부여 => 객체 생성, 수정, 삭제##

SYSTEM>GRANT RESOURCE TO USER1;

권한이 부여되었습니다.

SYSTEM>conn user1/tiger;
연결되었습니다.
USER1>create table t1
  2  (id number);

테이블이 생성되었습니다.

## SELECT 권한 부여
SCOTT>GRANT SELECT ON EMP TO USER1;

## 권한 회수
SYSTEM>REVOKE CONNECT, RESOURCE FROM USER1;

## 계정생성, 권한부여 한번에
SYSTEM>CREATE USER USER2 IDENTIFIED BY tiger;
SYSTEM>grant connect to user2;
USER2>UPDATE SCOTT.DEPT SET LOC='SEOUL' WHERE DEPTNO = 40;

 

6. 스키마

• 스키마란 - 데이터베이스의 구조와 제약조건에 관해 전반적인 명세를 기술한 것
• ⇒ **개체의 특성을 나타내는 속성(Attribute)**과 속성들의 집합으로 이루어진 개체(Entity), 개체 사이에 존재하는 관계(Relation)에 대한 정의와 이들이 유지해야 할 제약조건들을 기술한 것
• 오라클의 스키마는 사용자명