* SQL
QUERY : SELECT
DML : INSERT, UPDATE, DELETE
TCL : COMMIT, ROLLBACK > 트랜잭션을 제어할 목적으로 만들어진 언어
DDL : CREATE, ALTER, DROP, RENAME, TRUNCATE> 데이터 구조 정의
DCL : GRANT, REVOKE
[ VIEW와 테이블 ]
* 테이블에 대한 정보 보는 법
SELECT TABLE_NAME
2 FROM USER_TABLES;
↓
SELECT * FROM TAB;
* 제약조건 확인
DESC USER_CONSTRAINTS
> 필요한 걸 USER 뒤에 이름만 바꾸면 됨
* VIEW의 생성
- 형식: CREATE VIEW 이름
- 먼저, VIEW 생성 권한 주기
> 관리자 계정으로 다시 로그인
CONNECT SYSTEM/ORACLE
> 권한 주기 : GRANT 권한 TO 사용자명:
GRANT CREATE VIEW TO SCOTT;
> 다시 원래 계정으로 로그인
CONNECT scott/tiger
- 생성
create view emps
2 as
3 select EMPNO, ENAME, SAL, DEPTNO
4 FROM EMP
5 WHERE DEPTNO = 10;
* VIEW 정보 보기
DESC USER_VIEWS
* TABLE과 VIEW 차이
- TABLE : 데이터 저장소 > 물리적으로 데이터를 저장하는 저장소
- VIEW : 물리적 테이블에 근거한 논리적 가상테이블
> 테이블의 일부분만 볼 수 있음
> 물리적인 저장 장소를 갖지 않아도 테이블을 가진 것처럼 사용할 수 있음
- 따라서, VIEW를 만들면 테이블을 복사하지 않고도 테이블의 일부분을 SELECT 명령만으로 사용할 수 있음
SQL> SELECT * FROM EMPS;
위를 실행하면 아래와 같은 과정이 내부적으로 진행됨
SQL> SELECT *
2 FROM (SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO = 10);
> VIEW는 SELECT 명령 글자만 저장하여 테이블이 있는 것처럼 사용
* 테이블 복사와 VIEW
- 테이블 복사
CREATE TABLE CDEPT
2 AS
3 SELECT * FROM DEPT;
> 복사할 때까진 똑같지만, 복사한 이후에 DML이 발생하면 복사된 테이블에는 변경사항이 반영되지 않음
- VIEW
> 뷰는 아래와 같은 과정으로, 복사한 것과 똑같이 보이게 됨
CREATE VIEW CDEPT
2 AS
3 SELECT * FROM DEPT;
|
++ 휴지통 거치지 않고 바로 삭제
SQL> DROP TABLE CDEPT PURGE; |
* VIEW와 DML
- VIEW에 INSERT
> VIEW에 저장이 되는 것이 아니라, VIEW를 통해 원본 테이블에 저장 되는 것
INSERT INTO EMPS
2 VALUES (1111, 'HONG', 3000, 10);
>> VIEW를 통해 DML이 가능함
* VIEW의 수정 : CREATE OR REPLACE
CREATE OR REPLACE VIEW EMPS
2 AS
3 SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
4 FROM EMP
5 WHERE DEPTNO = 30;
> 아래와 같이, 확인해보면 수정된 것을 알 수 있음
SELECT VIEW_NAME, TEXT
2 FROM USER_VIEWS;
* 월급을 많이 받는 순으로 3명만 출력 > 사번, 이름, 급여 :: ROWNUM
SELECT EMPNO, ENAME, SAL
2 FROM E1
3 WHERE ROWNUM <=3;
- FROM 절에서 사용되는 SUBQUERY : 마치 뷰처럼 동작 <INLINE-VIEW>
SELECT EMPNO, ENAME, SAL
2 FROM (SELECT EMPNO, ENAME, SAL
3 FROM EMP
4 ORDER BY SAL DESC)
5 WHERE ROWNUM <= 3;
* 월급이 적은 순으로 3명만 출력 > 사번, 이름, 급여
SELECT EMPNO, ENAME, SAL
2 FROM (SELECT EMPNO, ENAME, SAL
3 FROM EMP
4 ORDER BY SAL ASC)
5 WHERE ROWNUM <= 3;
* ROWNUM
- 일반 컬럼과는 쓰임새가 다름
> 절반은 컬럼이자, 절반은 의사열
- 아래와 같은 식은 가능하지만, WHERE ROWNUM = 2인 경우에는 불가능함
SELECT ROWNUM, EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE ROWNUM = 1;
> ROWNUM은 조건을 만족할 때 까지만 수행하고 중단하는 구조로 되어 있음
그래서 보통 무엇보다 작다, 크다, =1 까지만 가능 (1행이 2가 아니면 중단하기 때문..)
* INDEX
> 목적 : 빠른 검색을 위해서
> 컬럼의 값 : ROWID를 별도의 저장장소에 저장..
- 인덱스가 없이 진행하는 과정
SELECT ROWID, ENAME, SAL, JOB, DEPTNO
2 FROM EMP;
> NEXT
SELECT ROWID, ENAME, SAL, JOB, DEPTNO
2 FROM EMP
3 WHERE ROWID='AAASRGAAEAAAAJ0AAH';
>> 이처럼 ROWID 찾아서 해당되는 데이터 찾음
* INDEX 만드는 명령 : CREATE INDEX 이름
CREATE INDEX IDX_EMP_ENAME
2 ON EMP(ENAME);
> 인덱스 생성 후 실행
SELECT EMPNO, ENAME, SAL
2 FROM EMP
3 WHERE ENAME = 'SMITH';
>> 자료를 찾는 과정이 빨라짐(데이터가 커야 큰 차이가 느껴짐..)
* INDEX 확인
DESC USER_INDEXES
>>
COL INDEX_NAME FORMAT A20
COL TABLE_NAME FORMAT A10
SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
2 FROM USER_INDEXES
3 WHERE TABLE_NAME IN ('EMP', 'DEPT');
* INDEX UNIQUENESS
- INDEX 자동 생성 : PK, UK <UNIQUE>
> INSERT 추가될 때 기존에 이런 값이 있었는지 검색(무결성 유지를 위해)
- INDEX 수동 생성 : 그 외, 필요한 인덱스를 직접 생성 <NONUNIQUE>
+ INDEX를 지정해주면 좋은 컬럼이란? : 자주 검색되는 컬럼
> JOIN CONDITION
* INDEX 삭제 : DROP INDEX 이름
DROP INDEX IDX_EMP_ENAME;
* JOB 컬럼으로 검색하는 일이 많다.. 인덱스 IDX_EMP_JOB 만들기
CREATE INDEX IDX_EMP_JOB
2 ON EMP(JOB);
>> 생성
아래의 SELECT문과 같은 데이터가 보임(JOB은!)
SELECT EMPNO, ENAME, JOB
2 FROM EMP
3 WHERE JOB = ‘MANAGER';
COL INDEX_NAME FORMAT A20
COL TABLE_NAME FORMAT A10
SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'EMP';
>> DATA DICTIONARY 확인
* SEQUENCE : 자동 번호 발생기
> 사용 용도의 99.9%가 INSERT문에 쓰임
- APPLICATION이 DB에 데이터를 INSERT할 때 PK의 값으로 사용할 목적으로 생성하는 OBJECT
- RDBMS : 관계형 데이터베이스
> 하나의 TABLE에는 반드시 하나의 PK가 존재해야 함
> 게시판에 사람들이 글을 쓰면 글들을 구분할 수 있어야 함 : 게시물 번호
> 요즘은 게시물 번호를 사용자들에게 노출시키진 않지만 관리할 때 사용
* SEQUENCE 생성 : CREATE SEQUENCE 이름
CREATE SEQUENCE DEPT_DEPTNO_SEQ
2 START WITH 1
3 INCREMENT BY 1
4 NOCACHE
5 NOCYCLE;
- 해석
CREATE SEQUENCE 시퀀스명
START WITH 1 -- > 시작 값 1
INCREMENT BY 1 -- > 1씩 증가
MINVALUE 1 -- > 최소값
MAXVALUE 100 -- > 최대값 100
CYCLE | NOCYCLE ; 순환사용가능
NOCACHE | CACHE 20 한번에 미리 생성해 놓을 번호의 수
- 아래 식처럼 INSERT문을 실행할 때마다 번호를 하나씩 생성해서 실행함
INSERT INTO DEPT
2 VALUES (DEPT_DEPTNO_SEQ.NEXTVAL, 'AAA', 'BBB');
* SEQUENCE 확인
DESC USER_SEQUENCES
OR
SELECT * FROM USER_SEQUENCES;
>> SEQUENCE는 한 개만 만들어 놓고, 여러 테이블에 사용될 수 있음!
* ORACLE 계정
Scott/tiger -- > END USER : 최종 사용자
System/ORACLE -- > 집사 정도..
Sys/ORACLE -- > 진짜 주인 계정.. 성주 정도..
* 권한 <DCL> : GRANT, REVOKE
- 시스템 권한
> GRANT 권한 TO 사용자;
> REVOKE 권한 FROM 사용자
- OBJECT 권한
> GRANT 권한 ON OBJECT TO 사용자;
> REVOKE 권한 ON OBJECT FROM 사용자;
* USER(계정) 만들기 : CREATE USER 이름 IDENTIFIED BY 비번;
CONN SYSTEM/ORACLE
↓
CREATE USER USER1
2 IDENTIFIED BY tiger;
↓
CONNECT USER1/tiger
결과 : ORACLE에 연결되어 있지 않습니다!! > USER1로 로그인 안됨
>> 왜??
Privilege
System : sys
Object : 오브젝트 생성자 > 오브젝트 권한은 오브젝트 생성자에게 있음
>> 따라서, 아래의 방법으로 USER에 DB에 엑세스될 권한을 줘야 함..
* USER에 권한 부여 : GRANT 권한명 TO 사용자명;
GRANT CONNECT TO USER1;
> CONNECT :: DB에 엑세스 할 수 있는 권한
> RESOURCE : 개체를 생성할 수 있는 권한
| ++ 사용중인 USER 이름으로 ‘SQL>’ 수정 SET SQLPROMPT "_USER>" |
* USER OBJECT 권한 부여 GRANT SELECT ON 오브젝트명 TO 사용자명;
> 다른 사용자의 테이블 열기
- 스키마 관련.. 간략하게 말해서 테이블명 앞에는 사용자 이름이 와야 함
> 다른 사람이 만든 테이블에 접근하려면 테이블명 앞에 사용자 이름이 와야!
- USER1에 로그인 된 상태에서,
SELECT * FROM SCOTT.EMP; >> 오류 발생
- 해결하기 위해서는, SCOTT으로 가서 권한 줘야 함
CONN scott/tiger >> SCOTT으로 로그인
- GRANT SELECT ON 오브젝트명 TO 사용자명;
SCOTT>GRANT SELECT ON EMP TO USER1;
- 다시 로그인해서 테이블 확인
SCOTT>CONN USER1/tiger
↓
SELECT * FROM SCOTT.EMP; >> 해결!
* SMITH 급여 2000으로 변경 : USER1로 로그인된 상태에서 시작
CONNECT scott/tiger
> 연결
GRANT UPDATE ON EMP TO USER1;
> 권한 부여
CONN user1/tiger > 다시 user1 로그인
UPDATE SCOTT.EMP
2 SET SAL = 2000
3 WHERE ENAME = 'SMITH';
> USER1에서 업데이트
SELECT ENAME, SAL
2 FROM SCOTT.EMP
3 WHERE ENAME = 'SMITH';
> 확인해 보면 UPDATE 성공한 결과 나옴
* UPDATE 권한 부여 : GRANT UPDATE ON 테이블명 TO 사용자명
UPDATE SCOTT.EMP
2 SET SAL = 100;
>> 오류 발생
GRANT UPDATE ON EMP TO USER1;
>> 권한 부여
* OBJECT 권한 회수 : REVOKE 권한 FROM 사용자명;
CONNECT scott/tiger
> 연결되었습니다.
- OBJECT 권한 회수 : REVOKE 권한 FROM 사용자;
REVOKE SELECT, UPDATE ON EMP FROM USER1;
> 권한이 취소되었습니다.
* USER1 권한 회수 : REVOKE 권한 FROM 사용자명;
REVOKE CONNECT, RESOURCE FROM USER1;
* 비밀번호 변경 > 관리자 및 자기 자신 가능
alter user user1
2 identified by lion;
> 비밀번호 변경
conn user1/lion
> 연결되었습니다.
* 테이블 생성
CREATE TABLE T1
2 (ID NUMBER);
* USER 삭제 > 관리자만 가능 : DROP USER 사용자명 CASCADE;
- 사용자를 지우면 사용자와 그 사용자의 모든 테이블이 사라짐
그래서 정말 지울거니??? 해서 CASCADE 옵션으로 지워야 함 > 확실히 지운다는 뜻
DROP USER UESR1 CASCADE;
> 사용자가 삭제되었습니다.
* SCOTT 계정 잠구는 법
IF SCOTT 퇴사,
DROP USER SCOTT CASCADE; > 10년째 일하던 모든 테이블이 날아가버림
- 비밀번호 변경하지 않아도 접속 불가능하게 할 수 있음
ALTER USER SCOTT
2 ACCOUNT LOCK;
> 이렇게 하면, CONN scott/tiger 했을 때 ‘the account is locked’
> 맞는 비밀번호를 넣어도 아예 접근 자체를 막아버림
> 권한이 있는 사람은 SCOTT의 테이블 접근 가능
* 잠긴 계정 풀기
ALTER USER SCOTT
2 IDENTIFIED BY tiger
3 ACCOUNT UNLOCK;
> 비밀번호 다시 tiger로 변경하고, 계정 UNLOCK
++ 응용
1. 테스트 전용 계정을 생성 한다. ( ID: TEST_ACCOUNT PW: TIGER )
CREATE USER TEST_ACCOUNT
2 IDENTIFIED BY tiger;
2. DB에 LOGIN 가능하게 권한을 부여 한다.
GRANT CONNECT TO TEST_ACCOUNT;
3-6번은 TEST_ACCOUNT 가 실행하는 명령 ( 권한이 필요) >>>> EMP테이블 권한을 TEST_ACCOUNT에 주고.. 풀기
CONN scott/tiger
GRANT SELECT ON EMP TO TEST_ACCOUNT;
GRANT SELECT ON SALGRADE TO TEST_ACCOUNT;
CONN TEST_ACCOUNT/tiger
3. 사원이름과 직업, 급여, 급여등급 을 출력하라?
SELECT E.JOB, E.SAL, S.GRADE
2 FROM SCOTT.EMP E, SCOTT.SALGRADE S
3 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
4. 20 번 부서의 사원들과 같은 월급을 받는 사원들의 이름과 월급과 부서번호를 출력하라
SELECT ENAME, DEPTNO
2 FROM SCOTT.EMP
3 WHERE SAL IN (SELECT SAL FROM SCOTT.EMP WHERE DEPTNO = 20);
5. 이름에 T 를 포함하고 있는 사원들과 동일한 부서에서 근무하고 있는 사원의 사원 번호와 이름을 출력하라
SELECT EMPNO, ENAME
2 FROM SCOTT.EMP
3 WHERE DEPTNO IN (SELECT DEPTNO FROM SCOTT.EMP WHERE ENAME LIKE '%T%');
6. BLAKE 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라
SELECT C.ENAME, C.HIREDATE
2 FROM SCOTT.EMP E, SCOTT.EMP C
3 WHERE C.HIREDATE > E.HIREDATE
4 AND E.ENAME = 'BLAKE';
7. TEST_ACCOUNT 계정에 테이블을 생성하고 이 테이블에 SELECT, UPDATE 할 수 있는 권한을 SCOTT에 부여 한다.
CONN SYSTEM/ORACLE
GRANT CREATE TABLE TO TEST_ACCOUNT;
CONN TEST_ACCOUNT/tiger
CREATE TABLE USERDATA
2 (NO NUMBER(2),
3 MSG VARCHAR2(20));
GRANT SELECT, UPDATE ON TEST_ACCOUNT.USERDATA TO scott;
(예시)
SQL> DESC USERDATA
이름 널? 유형
----------------------------------------------------------------------------------- -------- ---------------------------
NO NUMBER(2)
MSG VARCHAR2(20)
8. SCOTT계정에서 EMP 테이블을 복사한 COPY_EMP5 생성하라( 단 이미 copy_emp5 가 있다면 삭제후 다시 생성하세요 )
CONN scott/tiger
CREATE TABLE COPY_EMP5
2 AS
3 SELECT * FROM EMP;
9. COPY_EMP5 테이블 10번 부서사원들의 사번 이름 급여 부서번호만 볼수 있는 CEMP5 뷰를 생성하시오.
CONN SYSTEM/ORACLE
GRANT CREATE VIEW TO SCOTT;
CONNECT scott/tiger
CREATE VIEW CEMP5
2 AS
3 SELECT EMPNO, ENAME, SAL, DEPTNO
4 FROM EMP
5 WHERE DEPTNO = 10;
10. 이 VIEW를 SELECT할수 있는 권한을 TEST_ACCOUNT에 부여하시오.
GRANT SELECT ON SCOTT.CEMP5 TO TEST_ACCOUNT;
11. TEST_ACCOUNT에서 CEMP5를 통해서 데이터 접근이 가능한지 SELECT 해보세요.
SELECT * FROM SCOTT.CEMP5;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 CLARK 2450 10
7839 KING 5000 10
1111 HONG 3000 10
12. COPY_EMP5 테이블에서 JOB 컬럼에 IDX_CEMP5_ENAME 이라는 인덱스를 생성하세요
CONN scott/tiger
CREATE INDEX IDX_CEMP5_JOB
2 ON COPY_EMP5(JOB);
13. 아래의 결과가 볼수 있게 데이터 딕셔너리를 조회하세요
SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'COPY_EMP5';
(예시)
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES
--------------- ---------- ------------- ---------
IDX_CEMP5_ENAME NORMAL COPY_EMP5 NONUNIQUE
14. 인덱스가 사용되도록 SELECT문을 완성하세요
SELECT ENAME, JOB
2 FROM COPY_EMP5
3 WHERE JOB = 'MANAGER';
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
15. IDX_CEMP5_ENAME INDEX를 삭제하세요
DROP INDEX IDX_CEMP5_JOB;