oracle sql (4)

/*
4일차

데이터 조작어(DML : Data Manipulation Language)

SQL : DBMS의 데이터를 관리하기 위해 설계된 특수목적의 프로그래밍 언어

SQL 문법의 종류
1. 데이터 정의 언어(DDL : Data Defintion Language)
    - 테이블과 컬럼을 정의하는 명령어
    - 테이블의 생성, 수정
    - CREATE, ALTER, DROP, RENAME, TRUNCATE
    
2. 데이터 조작 언어(DML : Data Manipulation Language)
    - 데이터베이스의 내부 데이터를 관리하기 위한 언어
    - 데이터를 조회, 추가, 변경, 삭제
    - SELECT, INSERT, UPDATE, DELETE
    
3. 데이터 제어 언어(DCL : Data Control Language)
    - 데이터를 관리 목적으로 보안, 무결성, 회복, 병행제어
    - GRANT, REVOKE
    
4. 트랜잭션 제어 언어 (TCL : Transaction Control Language)
    - DCL과 비슷, 데이터를 제어하는 언어가 아닌 트랜잭션을 사용
    - COMMIT, ROLLBACK, SAVEPOINT

*/

--  테이블 생성 : DDL -> create

CREATE TABLE dept_tmp
AS SELECT * FROM dept;

SELECT * FROM dept_tmp;

-- 테이블 삭제 : DDL -> drop

DROP TABLE dept_tmp;  -- 삭제 후 dept_tmp 다시 생성

SELECT * FROM dept_tmp;

/*
INSERT

- 테이블에 데이터를 추가 : DML -> insert
- insert의 기본 구조
    insert into 테이블명(컬럼명1, 컬럼명2,...컬럼명n)
    values (컬럼명1의 데이터, 컬럼명2의 데이터,...컬럼명n의 데이터)

*/

-- dept_tmp에 데이터 추가

INSERT INTO dept_tmp(deptno, dname, loc)
VALUES (50, 'DATABASE', 'SEOUL');

SELECT * FROM dept_tmp;

INSERT INTO dept_tmp
VALUES (60, 'NETWORK', 'BUSAN');

SELECT * FROM dept_tmp;

INSERT INTO dept_tmp(loc, dname, deptno)
VALUES ('DAEJEON', 'JAVA', 70);

SELECT * FROM dept_tmp;

-- 테이블에 NULL 입력
-- 명시적

INSERT INTO dept_tmp
VALUES (70, 'WEB', NULL);

DELETE dept_tmp WHERE dname = 'JAVA';

SELECT * FROM dept_tmp;

INSERT INTO dept_tmp
VALUES (80, 'MOBILE', '');

SELECT * FROM dept_tmp;

-- 암시적

INSERT INTO dept_tmp(deptno, loc)
VALUES (90, 'INCHEON');

SELECT * FROM dept_tmp;

-- 테이블에 날짜데이터 입력
-- 테이블 생성은 하되 행 생성 X -> 테이블의 구조 복사

CREATE TABLE emp_tmp
as SELECT * FROM emp WHERE 1 <> 1;  

SELECT * FROM emp_tmp;

DESC emp_tmp;

INSERT INTO emp_tmp
VALUES (9999, '홍길동', 'PRESIDENT', NULL, '2001/01/01', 5000, 1000, 10);

SELECT * FROM emp_tmp;

INSERT INTO emp_tmp
VALUES (1111, '성춘향', 'MANAGER', NULL, '2001-01-05', 4000, NULL, 20);

SELECT * FROM emp_tmp;
-- 날짜 형식 변경 : 도구 -> 데이터베이스 -> NLS -> 닐짜형식
-- YYYY-MM-DD HH24:MI:SS

-- 날짜 데이터 형식

INSERT INTO emp_tmp
VALUES (2111, '이순신', 'MANAGER', 9999, '07/01/2001', 4000, NULL, 20);

SELECT * FROM emp_tmp;

DELETE emp_tmp WHERE empno = 2111;

INSERT INTO emp_tmp
VALUES (2111, '이순신', 'MANAGER', 9999, TO_DATE('07/01/2001', 'DD/MM/YYYY'), 4000, NULL, 20);

SELECT * FROM emp_tmp;

INSERT INTO emp_tmp
VALUES (3111, '심청이', 'MANAGER', 9999, SYSDATE, 4000, NULL, 30);

SELECT * FROM emp_tmp;

-- 서브 쿼리를 이용한 여러 데이터 입력
-- 서브쿼리 이용한 insert문의 주의점!!
-- values 사용X
-- 테이블과 서브쿼리의 컬럼의 갯수가 일치
-- 테이블과 서브쿼리의 컬럼의 자료형 일치

INSERT INTO emp_tmp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    SELECT E.empno, E.ename, E.job, E.mgr, E.hiredate, E.sal, E.comm, E.deptno
    FROM emp E, salgrade S
    WHERE E.sal BETWEEN S.losal AND S.hisal
    AND S.grade = 1;

SELECT * FROM emp_tmp;

/*
데이터 수정
- UPDATE
- update 기본 구조
    UPDATE 변경할 테이블
    SET 변경할 컬럼1 = 값1, 변경할 컬럼2 = 값2, ... 변경할 컬럼n = 값n
    WHERE 데이터를 변경할 대상의 조건(필수);

*/

CREATE TABLE dept_tmp2
AS SELECT * FROM dept;

SELECT * FROM dept_tmp2;


-- 데이터 전체 수정

UPDATE dept_tmp2 SET loc = 'SEOUL';

SELECT * FROM dept_tmp2;

-- 수정 내용을 되돌리기 : TCL -> ROLLBACK

ROLLBACK;

SELECT * FROM dept_tmp2;

-- 데이터 일부만 수정

UPDATE dept_tmp2 SET dname = 'DATABASE', loc = 'SEOUL'
WHERE deptno = 40;

SELECT * FROM dept_tmp2;

-- 수정 내용 적용하기(완료) : TCL -> COMMIT

COMMIT;

SELECT * FROM dept_tmp2;

ROLLBACK;

SELECT * FROM dept_tmp2;


-- 서브쿼리를 이용한 수정

UPDATE dept_tmp2 SET (dname, loc) = (SELECT dname, loc
                                     FROM dept
                                     WHERE deptno = 40)
WHERE deptno = 40;

SELECT * FROM dept_tmp2;

-- 서브쿼리를 이용하여 일부분 수정

UPDATE dept_tmp2 SET dname = (SELECT dname
                              FROM dept
                              WHERE deptno = 40),
                     loc = (SELECT loc
                            FROM dept
                            WHERE deptno = 30)          
WHERE deptno = 40;

SELECT * FROM dept_tmp2;

-- where 절에 서브쿼리를 사용

UPDATE dept_tmp2 SET loc = 'SEOUL' WHERE deptno = (SELECT deptno
                                                   FROM dept_tmp2
                                                   WHERE dname = 'OPERATIONS');
SELECT * FROM dept_tmp2;                                                   
                                                


/*
데이터 삭제
- DELETE
- 기본 구조
    DELETE 테이블명
    WHERE 삭제 데이터의 조건;

*/

-- 데이터 일부 삭제

CREATE TABLE emp_tmp2
AS SELECT * FROM emp;

SELECT * FROM emp_tmp2;

-- where절 사용

DELETE emp_tmp2
WHERE job = 'MANAGER';

SELECT * FROM emp_tmp2;

-- where 절에 서브쿼리를 사용
DELETE FROM emp_tmp2 WHERE empno IN (SELECT E.empno
                                     FROM emp_tmp2 E, salgrade S
                                     WHERE E.sal BETWEEN S.losal AND S.hisal
                                     AND S.grade = 3
                                     AND deptno = 30);
SELECT * FROM emp_tmp2;

-- 데이터 전체를 삭제

DELETE emp_tmp2;

SELECT * FROM emp_tmp2;

/*
데이터 정의어 : DDL(Data Definition Language)
- 주의!!! : 조작어에서는 COMMIT 또는 ROLLBACK 반영시 적용, 정의어에서는 실행과 동시에 COMMIT 됨
- 객체(테이블, 뷰, 인덱스, 스키마, 도메인)를 생성(CREATE), 변경(ALTER), 삭제(DROP)
- 생성(CREATE) : 객체(테이블, 뷰, 인덱스, 스키마, 도메인)
- 변경(ALTER) : 테이블에 대한 정의를 변경하는데 사용
- 삭제(DROP) : 객체(테이블, 뷰, 인덱스, 스키마, 도메인)

*/

/*
- 테이블 생성 : create
- 기본 구조
    create table 소유계정.테이블명 (
        컬럼명1 자료형; 
        컬럼명2 자료형;
        ...
        컬럼명n 자료형;   
    )
   
*/

CREATE TABLE emp_ddl(
    empno number(4),
    enmae varchar2(10),
    job varchar2(9),
    mgr number(4),
    hairedate DATE,
    sal number(7, 2),
    comm number(7, 2),
    deptno number(2)
);

DESC emp_ddl;

-- 기존 테이블 컬럼구조와 데이터 복사

CREATE TABLE dept_ddl
AS SELECT * FROM dept;

DESC dept;
SELECT * FROM dept;

-- 기존 테이블 컬럼구조와 데이터 일부만 복사

CREATE TABLE emp_ddl_30
AS SELECT * FROM emp WHERE deptno = 30;

SELECT * FROM emp_ddl_30;

-- 기존 테이블 컬럼구조만 복사

CREATE TABLE empdept_ddl
AS SELECT E.empno, E.ename, E.job, E.mgr, E.hiredate, E.sal, E.comm, 
          D.deptno, D.dname, D.loc FROM emp E, dept D WHERE 1 <> 1;
          
SELECT * FROM empdept_ddl;

/*
테이블 변경 : alter

- 변경 키워드
    1. ADD : 데이터 컬럼 추가
    2. RENAME : 컬럼명 변경
    3. MODIFY : 자료형을 변경
    4. DROP : 특정 컬럼 삭제

*/

CREATE TABLE emp_alter
AS SELECT * FROM emp;

--    1. ADD

ALTER TABLE emp_alter
ADD hp varchar2(20);

SELECT * FROM emp_alter;

--    2. RENAME

ALTER TABLE emp_alter
RENAME COLUMN hp TO TEL;

SELECT * FROM emp_alter;

--    3. MODIFY

DESC emp_alter;

ALTER TABLE emp_alter
MODIFY empno number(5);

DESC emp_alter;

--    4. DROP

ALTER TABLE emp_alter
DROP COLUMN tel;

SELECT * FROM emp_alter;


-- 테이블의 데이터 삭제 : truncate -> ROLLBACK 불가

CREATE TABLE emp_trunc
AS SELECT * FROM emp;

SELECT * FROM emp_trunc;

DELETE emp_trunc;

SELECT * FROM emp_trunc;

ROLLBACK;

SELECT * FROM emp_trunc;

TRUNCATE TABLE emp_trunc;

SELECT * FROM emp_trunc;

ROLLBACK;

SELECT * FROM emp_trunc;

-- 테이블 삭제 : DROP

DROP TABLE emp_trunc;

DESC emp_trunc;

/*
객체

인덱스
- select 성능을 향상시키기 위해 사용하는 물리적 저장 구조


*/
-- 오라클에서 자동으로 생성된 인덱스
-- scott 계정이 소유한 인덱스 정보 

SELECT * FROM user_indexes;

-- scott 계정이 소유한 인덱스 컬럼 정보 

SELECT * FROM user_ind_columns;


/*
인덱스 생성
-- 사용자가 직접 인덱스를 생성
-- 기본 구조
    create index 인덱스이름
    ON 테이블명(컬럼명1 ASC 또는 DESC,
               컬럼명2 ASC 또는 DESC,
               ....
               컬럼명n ASC 또는 DESC,);
    
*/

-- emp 테이블에 sal 컬럼의 인덱스 생성

SELECT * FROM emp;

CREATE INDEX idx_emp_sal
ON emp(sal);

-- 생성된 인덱스 확인

SELECT * FROM user_ind_columns;

SELECT * FROM emp WHERE sal > '0';


-- 인덱스 삭제

DROP INDEX idx_emp_sal;

SELECT * FROM user_ind_columns;



-- index 사용 예제
-- 테이블 생성
CREATE TABLE emp3 (
no NUMBER,
name VARCHAR2(10),
salary NUMBER);

-- 데이터 삽입
INSERT INTO emp3 VALUES (1, '강호동', 200);
INSERT INTO emp3 VALUES (2, '우재석', 300);
INSERT INTO emp3 VALUES (3, '이경규', 100);
INSERT INTO emp3 VALUES (4, '김구라', 150);
INSERT INTO emp3 VALUES (5, '박나래', 100);
INSERT INTO emp3 VALUES (6, '양세찬', 250);

-- 데이터 조회
SELECT * FROM emp3;

-- emp3 테이블의 name컬럼을 사용하는 idx_name 인덱스 생성

CREATE INDEX idx_name ON emp3(name);

-- emp3 테이블의 인덱스 컬럼 조회

SELECT * FROM user_ind_columns;

-- 인덱스 테이블명은 반드시 대문자로 조회

SELECT * FROM user_ind_columns WHERE table_name = 'EMP3'; 

-- 테이블 조회

select * from emp3;

-- 인덱스 사용하지 않고 조회

SELECT * FROM emp3 order by name;

-- 인덱스 사용

SELECT * from emp3 where name > '0';


/*
뷰(가상 테이블)
- 테이블처럼 사용하는 것
- 쿼리(select)를 저장한 객체
- 실제로 테이블 존재하지 않음
- 이유
    1. 보안관리
        - 보안등급에 다라 컬럼의 범위를 정하고 권한을 부여
        - 연산결과만 제공하고 싶을 때 : 수식이나 알고리즘을 숨길 때
        - 원본 스키마를 숨길 때
    2. 사용 편리
        - 검색 조건이 많은 쿼리를 단순화
        - JOIN, 서브쿼리 단순화


- 생성
    create view 뷰명
    as 쿼리문;
*/

/*
-- view 생성 권한 : grant 

cmd

C:\Users\bigdata>sqlplus system/system

SQL> grant create view to scott;

Grant succeeded.

SQL>

*/

-- 뷰 생성

CREATE VIEW emp_v
AS SELECT * FROM emp WHERE deptno = 20;

-- 뷰 확인

SELECT * FROM user_views;

-- 뷰 조회

SELECT * FROM emp_v;

-- 뷰 수정 : ALTER 사용 X

CREATE OR REPLACE VIEW emp_v
AS
SELECT ename, job, deptno
FROM emp
WHERE deptno = 30;

/*

- 읽기 전용 뷰

CREATE VIEW 뷰명
AS
쿼리문
SELECT ename, job, deptno
FROM emp
WHERE deptno = 30
WITH READ ONLY;

*/

-- 뷰 삭제

DROP VIEW emp_v;

SELECT * FROM user_views;

-- 사용예

-- 서브쿼리

SELECT * FROM (SELECT empno, ename, job, deptno
               FROM emp
               WHERE deptno = 20);

-- 뷰 생성

CREATE VIEW emp_20
AS SELECT empno, ename, job, deptno FROM emp WHERE deptno = 20;

SELECT * FROM emp_20;

-- 뷰 삭제

DROP VIEW emp_20;

SELECT * FROM user_views;

-- 뷰 수정 : ALTER 사용 X

CREATE OR REPLACE VIEW emp_20
AS
SELECT ename, job, sal
FROM emp
WHERE sal >= 2500;

SELECT * FROM emp_20;

/*
인라인 뷰
- SQL문에서 일회성으로 만들어 사용하는 뷰
- select 문에서 사용되는 서브쿼리
- with절의 select 문

*/

-- rownum 사용 : pseudo column(의사컬럼)

SELECT ROWNUM, E.* FROM emp E;

-- sal 기준 정렬 : rownum도 같이 정렬이 된다(rownum앞의 숫자와 비교)

SELECT ROWNUM, E.* FROM emp E ORDER BY sal DESC;

-- 인라인뷰를 서브쿼리에 사용 : 정렬 결과를 순번을 부여

SELECT ROWNUM, E.* FROM (SELECT * 
                         FROM emp E
                         ORDER BY sal DESC)E;
                         
                         
-- 인라인뷰를 with절에 사용 

WITH E AS (SELECT * FROM emp ORDER BY sal DESC)
SELECT ROWNUM, E.* FROM E;

-- 인라인뷰로 top-n 출력(서브쿼리 이용)

SELECT ROWNUM, E.* FROM (SELECT * 
                         FROM emp E
                         ORDER BY sal DESC)E
                   WHERE ROWNUM <= 5;


-- 인라인뷰로 top-n 출력(with이용)

WITH E AS (SELECT * FROM emp ORDER BY sal DESC)
SELECT ROWNUM, E.* FROM E
                   WHERE ROWNUM <= 5;
                   
/*
시퀀스
- 규칙에 따른 순번 생성
- 생성
    create sequence 시퀀스명
    [increment by n]  -- 번호의 증가값
    [start with n]    -- 시작값
    [maxvalue n | nomaxvalue n]    -- 최대값
    [minvalue n | nominvalue n]    -- 최소값
    [cycle | nocycle]    -- 다시 시작
    [cache | nocache]    -- 생성할 번호 미리 지정
*/         

-- dept 구조 복사 -> dept_seq

CREATE TABLE dept_seq
AS SELECT * 
   FROM dept
   WHERE 1 <> 1;
   
SELECT * FROM dept_seq;

-- 시퀀스 생성

CREATE SEQUENCE seq_dept_seq
INCREMENT BY 10
START WITH 10
MAXVALUE 30
MINVALUE 0
NOCYCLE
CACHE 2;

SELECT * FROM user_sequences;
   
-- 시퀀스 사용

-- insert : nextval 사용

INSERT INTO dept_seq (deptno, dname, loc)
VALUES (seq_dept_seq.NEXTVAL, 'DATABASE', 'SEOUL');
INSERT INTO dept_seq (deptno, dname, loc)
VALUES (seq_dept_seq.NEXTVAL, 'WEB', 'INCEON');
INSERT INTO dept_seq (deptno, dname, loc)
VALUES (seq_dept_seq.NEXTVAL, 'MOILE', 'DAEJEON');

SELECT * FROM dept_seq;

-- 마지막 생성 시퀀스 확인

SELECT seq_dept_seq.CURRVAL FROM dual;

/*
- 시퀀스 수정
    - 생성
    alter sequence 시퀀스명
    [increment by n]  -- 번호의 증가값
    [maxvalue n | nomaxvalue n]    -- 최대값
    [minvalue n | nominvalue n]    -- 최소값
    [cycle | nocycle]    -- 다시 시작
    [cache | nocache]    -- 생성할 번호 미리 지정

*/

-- 수정

alter SEQUENCE seq_dept_seq
INCREMENT BY 3
MAXVALUE 99
CYCLE;
            
SELECT * FROM user_sequences;

-- 시퀀스 삭제

DROP SEQUENCE seq_dept_seq;

SELECT * FROM user_sequences;

/*
트랜잭션
- 하나의 단위로 데이터를 처리
- COMMIT : 트랜잭션 반영
- ROLLBACK : 트랜잭션 취소

- insert 문 참조

*/

/*
세션
- 활동 시간이나 기간
- DB 접속시간 ~ 종료까지의 시간

- 주의할 점!!!
    1. 읽기 일관성 : DB는 여러곳에서 동시 다발적으로 접속 -> 데이터를 관리, 사용
    2. 수정중인 데이터 잡근 잠금 : lock
        2-1 : 일부 데이터를 수정, 삭제 할 경우 해당 데이터만 lock상태로 처리 
              -> 해당 데이터를 제외한 다른 행의 데이터만 사용
              -> COMMIT 이후에 해당 데이터를 사용
        2-2 : 전체 데이터를 수정, 삭제 할 경우 테이블 전체 행에 대해 lock상태로 처리 
              -> 모든 데이터 사용 X
              -> COMMIT 이후에 해당 데이터를 사용
        2-3 : 모든 lock 상태라 할지라도 insert는 가능

*/