ORACLE_07_이론정리_제약조건의 추가

    제약조건 추가하기
        형식 1 ] 

  컬럼이름    데이터타입(길이)  
                CONSTRAINT  제약조건이름  제약조건    PRIMARY KEY
                CONSTRAINT  제약조건이름  REFERENCES 테이블이름(컬럼이름)
                CONSTRAINT  제약조건이름  UNIQUE
                CONSTRAINT  제약조건이름  NOT NULL
                CONSTRAINT  제약조건이름  CHECK(컬럼이름 IN(데이터1, 데이터2,.....))

                
형식 2 ]         

  컬럼이름    데이터타입(길이),
            컬럼이름    데이터타입(길이),
            ...
            컬럼이름    데이터타입(길이),
            CONSTRAINT  제약조건이름  PRIMARY KEY(컬럼이름),
            CONSTRAINT  제약조건이름  UNIQUE(컬럼이름),
            CONSTRAINT  제약조건이름  FOREIGN KEY(컬럼이름) REFERENCES 테이블이름(컬럼이름),
            CONSTRAINT  제약조건이름  CHECK(컬럼이름 IN(데이터1, 데이터2,.....))

            주의 ] 컬럼이 만들어진 이후에는 NOT NULL 제약조건은 추가하지 못한다.
                <== 제약조건을 추가하지 않고 테이블을 만들게 되면 컬럼들은 NULL 데이터를 허용하는 컬럼으로 만들어진다.
                    따라서 NOT NULL 제약조건은 이런 컬럼의 속성을 수정해야 한다.
                    
      형식 3 ] 제약조건없이 테이블을 만드는 경우 테이블 수정명령으로 제약조건을 추가하는 방법

 ALTER TABLE 테이블이름
        ADD CONSTRAINT  제약조건이름  제약조건(컬럼이름);

    여러테이블을 생성할 때 생성하는 순서는 참조해주는 테이블부터 생성해야 한다.

--아바타테이블
CREATE TABLE avatar(
    ano NUMBER(2),
    aname VARCHAR2(15 CHAR),
    oriname VARCHAR2(50 CHAR),
    savename VARCHAR2(50 CHAR),
    dir VARCHAR2(100 CHAR),
    len NUMBER,
    adate DATE DEFAULT sysdate,
    isShow CHAR(1) DEFAULT 'Y',
    CONSTRAINT AVT_NO_PK PRIMARY KEY(ano),
    CONSTRAINT AVT_SNAME_UK UNIQUE(savename),
    CONSTRAINT AVT_SHOW_CK  CHECK(isShow IN ('Y','N')));


--NOT NULL 제약조건 수정
ALTER TABLE avatar
MODIFY  aname
    CONSTRAINT  AVT_NAME_NN NOT NULL;    

ALTER TABLE avatar
MODIFY  oriname
    CONSTRAINT  AVT_ONAME_NN NOT NULL;

ALTER TABLE avatar
MODIFY  sname
    CONSTRAINT  AVT_SNAME_NN NOT NULL;

ALTER TABLE avatar
MODIFY  dir
    CONSTRAINT  AVT_DIR_NN NOT NULL;

ALTER TABLE avatar
MODIFY  adate
    CONSTRAINT  AVT_date_NN NOT NULL;
    
ALTER TABLE avatar
MODIFY  isShow
    CONSTRAINT  AVT_isShow_NN NOT NULL;


--회원테이블 추가
CREATE TABLE MEMBER(
    mno NUMBER(4)
        CONSTRAINT MB_NO_PK PRIMARY KEY,
    name VARCHAR2(20 CHAR)
        CONSTRAINT MB_NAME_NN NOT NULL,
    id  VARCHAR2(15 CHAR)
        CONSTRAINT MB_ID_UK UNIQUE
        CONSTRAINT MB_ID_NN NOT NULL,
    pw  VARCHAR2(15 CHAR)
        CONSTRAINT MB_PW_NN NOT NULL,
    mail VARCHAR2(50 CHAR)
        CONSTRAINT MB_MAIL_UK UNIQUE
        CONSTRAINT MB_MAIL_NN NOT NULL,
    tel VARCHAR2(13 CHAR)
        CONSTRAINT MB_TEL_UK UNIQUE
        CONSTRAINT MB_TEL_NN NOT NULL, 
    avt NUMBER(2) DEFAULT 10
        CONSTRAINT MB_AVT_FK REFERENCES avatar(ano)
        CONSTRAINT MB_ANO_NN NOT NULL,
    gen CHAR(1)
        CONSTRAINT MB_GEN_CK CHECK(gen IN ('F','M'))
        CONSTRAINT MB_GEN_NN NOT NULL,
    joindate DATE DEFAULT sysdate
        CONSTRAINT MB_DATE_NN NOT NULL,
    isShow CHAR(1) DEFAULT 'Y'
        CONSTRAINT MB_SHOW_CK  CHECK(isShow IN('Y','N'))
        CONSTRAINT MB_SHOW_NN  NOT NULL
 );

    등록된 제약조건 확인하는 방법 : 오라클이 테이블을 이용해서 관리한다.
                                                     이 테이블 이름이 USER_CONSTRAINTS 이다.
                                                      따라서 이 테이블의 내용을 확인하면 등록된 제약조건을 확인할 수 있다.
          참고 ] CONSTRAINT_TYPE
                P   PRIMARY KEY
                R   FOREIGN KEY
                U   UNIQUE
                C   NOT NULL, CHECK

---

    제약조건 삭제하는 방법
    형식 ] 
        ALTER TABLE 테이블이름
        DROP CONSTRAINT 제약조건이름;
        
    참고 ] 기본키(PRIMARY KEY)의 경우는 제약조건 이름을 몰라도 삭제할 수 있다.
          기본키는 테이블에 오직 한개만 만들어지기 때문이다.
          
          형식 ] ALTER TABLE 테이블이름
                DROP PRIMARY KEY; (기본키 제약조건만 삭제)

DESC USER_CONSTRAINTS;

--아바타테이블의 제약조건 조회
SELECT
    constraint_name 제약조건이름, constraint_type 제약조건, 
    table_name 테이블이름
FROM
    user_constraints
WHERE
    table_name IN('AVATAR','MEMBER')   
;

--회원테이블 기본키 제약조건 삭제
ALTER TABLE MEMBER
DROP PRIMARY KEY;

--기본키 추가
ALTER TABLE MEMBER
ADD CONSTRAINT MB_NO_PK PRIMARY KEY(mno);


--TMP 테이블 생성
CREATE TABLE tmp(
    no NUMBER(4)
);

    테이블 수정하기
    1) 필드 추가하기
        형식 ] 
            ALTER TABLE 테이블이름
            ADD (
                필드이름    데이터타입(길이)
                    CONSTRAINT  제약조건이름   제약조건
            );
            
    2) 필드 이름 변경하기
        형식 ] ALTER TABLE 테이블이름
              RENAME COLUMN 필드이름 TO 바뀔이름;
    
    3) 필드 길이 변경하기
        형식 ] 
            ALTER TABLE 테이블이름
            MODIFY 필드이름 데이터타입(길이);
            
        참고 ] 
            DEFAULT 값 추가
            ALTER TABLE 테이블이름
            MODIFY 필드이름 DEFAULT 데이터;
            
       ★참고 ] 길이변경은 현재길이보다 늘이는 것은 가능하지만 줄이는 것은 불가능 하다.
               이미 입력되어있는 데이터가 수정된 길이를 넘어설 수 있기 때문에
            
     4) 필드 삭제하기
        형식 ] 
            ALTER TABLE 테이블이름
            DROP COLUMN 필드이름;

---

    테이블 이름 변경하기
    형식 ] ALTER TABLE 테이블이름
          RENAME TO 변경될테이블이름;

---

    
    테이블 삭제하기
        참고 ] 테이블 내의 모든 데이터도 같이 삭제된다.
        형식 1 ]  DROP TABLE 테이블이름; (<- 휴지통에 넣는 작업)
        형식 2 ]  DROP TABLE 테이블이름 purge;
                  ==> 휴지통에 넣지 말고 완전 삭제하세요 
        참고 ] DML 명령은 복구가 가능하지만 DDL 명령은 복구가 원칙적으로 불가능하다.
              10g 부터 휴지통 개념을 추가해서 삭제된 데이터를 휴지통에 보관하도록 하고 있다.
        
        휴지통관리 ] 
            1. 휴지통에 있는 모든 데이터를 완전 지우기 : purge recyclebin;                
            2. 휴지통에 있는 특정 테이블만 완전 삭제 : purge table 테이블이름;                
            3. 휴지통 확인하기 : show recyclebin;
            4. 휴지통 테이블 복구 : flashback table 테이블이름 to before drop;

--TMP테이블에 NAME 필드 추가
ALTER TABLE tmp
ADD(
    name VARCHAR2(10 char)
        CONSTRAINT TMP_NAME_NN NOT NULL
        
);

 

--TMP테이블의 NO 필드에 기본키제약조건 추가
ALTER TABLE TMP
ADD CONSTRAINT TMP_NO_PK PRIMARY KEY(NO);

--NO를 TNO로 변경하자
ALTER TABLE tmp
RENAME COLUMN no TO tno;

--휴지통확인
show recyclebin;

 

---

    TRUNCATE 명령 : DML 명령 중 DELETE과 같이 테이블 안에 있는 모든 데이터를 삭제하는 명령
    형식 ] TRUNCATE TABLE 테이블이름;
    참고 ] DELETE 명령은 DML 소속이고(복구:ROLLBACK 가능)
           TRUNCATE 명령은 DDL 소속 명령이므로 복구 불가

    무결성 체크 : 데이터베이스는 프로그램 등 전산에서 작업할 때 필요한 데이터를 제공해주는 보조프로그램이다.
                 따라서 데이터베이스가 가진 데이터는 항상 완벽한 데이터여야 한다.
                 그런데 데이터를 입력하는 것은 사람의 몫이고 따라서 완벽한 데이터를 보장할 수 없게 된다.
                 각각의 테이블에 들어가서는 안될 데이터나 빠지면 안되는 데이터등을 미리 결정해놓고 
                 데이터를 입력하는 사람이 잘못 입력하면 그 데이터는 아예 입력되지 못하도록 방지하는 역할을 하는 기능
                 한마디로 정리하자면 이상데이터가 입력되는 것을 방지하는 기능
                 따라서 이 기능은 반드시 필요한 기능은 아니다. 실수를 미연에 방지할 수 있도록 하는 기능. 

    테이블을 생성하는 명령으로 서브질의의 결과를 이용해서 테이블을 만드는 방법도 있다.

    CREATE TABLE 테이블이름
    AS  서브질의;

    ==> 이렇게 복사하게 되면 모든 테이블의 구조는 복사할 수 있지만, not null 제약조건을 제외한 다른 모든 

           제약조건은  복사해오지 않는다.
      
    이 때 복사할 테이블의 구조만 복사하고 싶은 경우

CREATE TABLE 테이블이름
        AS SELECT   *   FROM 테이블이름 WHERE 1=2;

--MEMBER 테이블 복사해서 MBMB02 테이블을 만들어보자.
CREATE TABLE MEMB02
AS 
    SELECT * FROM MEMBER;

 

--제약조건은 복사가 안되기 때문에 기재해야함.
ALTER TABLE MEMB02
ADD CONSTRAINT MB02_NO_PK PRIMARY KEY(mno);

--SCOTT.EMP
SELECT  
    ename, job, hiredate, deptno
FROM    
    emp
WHERE
    ename = 'SMITH' --데이터 비교작업 후 조건절이 true 이면 조회될 내용으로 추가 
;

SELECT
     ename, job, hiredate, deptno
FROM
    emp
WHERE
    1=2; 
    -- 한행씩 꺼내는데 저 식은 언제나 거짓. 어떤 데이터가 있더라도 모든 데이터를 조회에 포함시키지 않는다.

 

--INSERT 명령에 서브질의를 사용할 수 있다.
/*
    형식 ] INSERT INTO 테이블이름
             서브질의
            ;
*/

--EMP 테이블중 부서번호가 10번인 사람의 데이터만 복사해라

INSERT INTO EMP2
(
        SELECT  *
        FROM    EMP
        WHERE DEPTNO = 10
);

---

    DCL(Data Control Language) 
    트랜젝션 처리 : 교과서적인 의미로는 오라클이 처리(실행)하는 명령 단위
    ex ) 테이블 만들 ‹š create 명령을 타이핑 하고 엔터키를 누르면 -> 그 명령을 실행하게 되는데 이것을 

          "트랜젝션이 실행되었다" 라고 표현한다.
         데이터베이스에 적용시킴(트렌젝션이 처리 되었다)
         위와 같이 대부분의 명령은 엔터키를 누른 순간 명령이 실행되고 그것은 곧 트렌젝션이 실행된 것이므로
         결국 오라클은 명령 한줄이 곧 하나의 트랜젝션이 된다.
         
         그런데 DML 명령 만큼은 트랜젝션 단위가 달라진다.
         DML 명령 : INSERT, UPDATE, DELETE
         -==> DML 명령은 곧장 실행되는 것이 아니고 버퍼장소(임시 기억장소)에 그 명령을 모아만 놓는다.
              결구 트랜젝션이 실행되지 않는다.
              따라서 DML 명령은 강제로 트랜젝션 실행 명령을 내려야 한다.
              이 ‹š 트렌젝션은 한번만 일어나게 된다.
              
              왜? DML 명령은 데이터를 변경하는 명령이다.
                데이터베이스에서 가장 중요한 개념은 무결성인데 이런 곳에 DML 명령이 한순간 트랜젝션 처리가 된다면
                데이터의 무결성이 깨질 수 있다.
            
            
            이런 문제점을 해결하기 위한 목적으로 트랜젝션 방식을 변경해 놓았다.
        
            버퍼에 모아놓은 명령을 트랜젝션 처리하는 방법
            - 자동 트랜젝션 처리
                1. SQLplusfmf 정상적으로 종료하는 순간 트랜젝션 처리가 일어난다.
                    ==> exit  명령으로 세선을 정상적으로 닫는 트랜젝션 처리가 다르게 일어난다.
                                   
                2.DDL 명령 이나 DCL 명령이 내려지는 순간
            - 수동 트랜젝션 처리
                3. commit 이라고 강제로 명령을 내리는 순간 
              참고 ] 버퍼에 모아놓은 명령이 실행되지 않는 순간, 트랜젝션 차례가 되지 못하고 버려지는 경우
                      - 자동
                        1.정전드ㅇ에 의해서 시스템이 셧다운 되는 순간
                        2.SQL PLUS를 비정상종료할때
                   - 수동 
                        3. ROLLBACK이라고 명령을 내리는 순간
                        
            
        ★결론적으로  DMP 명령을 내린 후 다시 검토해서 완멱한 명령이라고 판단되면    
          COMMIT 이라고 명령해서 트랜젝션을 발싱시키고
          만약 검토과정중 오류가 발생한다면
          ROLLGBACK 라는 명령으로 잘못된 명령을 취소하도록 한다.
    
        책갈피 만들기
        SAVEPOINT 적당한 이름, 

        이것을 사용해서 ROLLBACK 적당한 이름
     

 ROLLBACK TO SAVEPOINT 이름; (SAVEPOINT 선언하는것)
        SAVEPOINT A;
        DML(1)
        DML(2)
        DML(3)
       
        SAVEPOINT B;
        DML (4) 
        DML (5)
        DML (6)
        
       SAVEPOINT C;
        DML (7) 
        DML (8)
        DML (9)
        
    ROLLBACK TO C ; <= 7~9만 취소
    ROLLBACK TO B ; 4,5,6,[7,8,9] 가 취소
    ROLLBACK TO A ; 1,2,3,[4,5,6,7,8,9]가 취소

    참고 ] 트랜젝션이 처리되면 SAVEPOINT는 자동 파괴된다.

ALTER TABLE emp2 
ADD CONSTRAINT  emp2_NO_PK PRIMARY KEY (EMPNO);

---

    뷰(VIEW) : 교과서적인 의미로 질의명령의 결과를 하나의 창문으로 바라볼 수 있는 창문
        예 ] SELECT*fROM EMP;
            ==> 이렇게 질의명령을 실행하면 결과가 발생하는데
                그 결과 중에서 일부분만 볼 수 있는 창문을 만들어서 사용하는 것.
        
        테이블과 유사하지만 테이블과 다른점은 물리적으로 데이터베이스에 필드들이 만들어져 있지 않다는 것이다.
        
    ==> 자주사용하는 복잡한 질의명령을 따로 저장해놓고 이 질의 명령의 결과를 손쉽게 처리할 수 있도록 하는 것.

        뷰의 종류
            1. 단순뷰 : 하나의 테이블만 이용해서 만들어진 뷰
                       DML 명령이(INSERT, UPDATE, DELETE) 원칙적으로 가능하다.
            2. 복합뷰 : 두개 이상의 테이블을 이용해서 (조인해서) 만들어진 뷰를 의미
                       DML 명령(INSERT , UPDATE, DELETE)이 불가능하다.
                ==> 그냥 보기만 하세요...(SELECT 질의명령밖에 못씀

---

    참고 ] 원칙적으로 사용자계정은 관리자가 허락한 일만 할 수 있다.
           그런데 뷰를 만드는 권한은 아직 부여되어 있지 않다.
           따라서 권한을 부여하고 뷰를 만들어야 한다.
           
           권한 부여하는 방법 - 관리자계정에서 (SYSTEM)
            GRANT 권한이름 , 권한이름,....TO 계정이름;

---

    뷰를 만드는 방법
    형식 1 ] 
        CREATE [OR REPLACE] VIEW 뷰이름
        AS 서브질의;
        서브질의의 결과로 뷰를 만들겠다.
        
        참고 : 뷰를 확인하는 방법은 오라클에서는 뷰를 관리하는 테이블이 존재하고 그 테이블에서 관리한다.
              그 테이블이름이 USER_VIEW 이다.
              
    형식 2 ] 
        DML 명령으로 데이터를 변경할때 변경된 데이터는 기본테이블만 변경이 되므로 
        뷰 입장에서 보면 그 데이터를 실제로 사용 못할 수 있다.
        
        CREATE [OR REPLACE] VIEW 뷰이름
        AS
            질의명령
        WITH CHECK OPTION;
        
     형식 3 ] VIEW를 이용해서 데이터를 변경하면 VIEW 를 사용하는 데이터만 변경 가능하다.
              이것은 원본테이블의 입장에서 문제가 발생할 수 있다.
              뷰를 통해서 데이터를 수정하지 못하도록 방지하는 형식이다.
              CREATE OR REPLACE VIEW 뷰이름
              AS 
                질의명령
              WITH READ ONLY;   <--이 뷰는 읽기전용 뷰. 수정이나 삭제 불가.

---

    참고 ] 원래 뷰는 기본테이블이 있고 그것을 바라보는 창문을 만드는 개념이다.(테이블 내용이 바뀌면 바뀐 내용을 보게 됨)
           그런데 기본테이블이 없어도 VIEW를 만들 수 있다.
           형식 ] CREATE OR REPLACE FORCE VIEW 뷰이름
                  AS 
                    질의명령
                  .....
                  ;
            참고 ] 진짜로 테이블이 없이 뷰가 작동되는 것은 아니다.
                  테이블은 필요한데
                  이 명령을 내리는 순간만 없는 경우에 급할때 사용하는 방법
                  나중에 테이블이 만들어지면 그때 데이터를 불러오게 된다.

---

    VIEW 삭제
    형식 ] 

--형식
DROP VIEW 뷰이름;

--관리자계정(SYSTEM)에서 작업
GRANT CREATE VIEW TO SCOTT; --CREATE VIEW (VIEW를 만들 수 있는 권한)을 SCOTT에게 준다.

 

CREATE VIEW dnosal
AS 
    SELECT  DEPTNO DNO, MAX(SAL) MAX, MIN(SAL) MIN, SUM(SAL) SUM, COUNT(*) CNT, AVG(SAL) AVG
    FROM   EMP
    GROUP BY                    --복합뷰
        DEPTNO
;

문제 ] 사원들의 사원이름, 부서번호, 부서최대급여, 부서원수를 조회하세요.

SELECT ENAME 사원이름, DEPTNO 부서번호, MAX 부서최대급여, CNT 부서원수
FROM    EMP,DNOSAL
WHERE 
    DEPTNO = DNO
;

--USER_VIEWS 구조확인
DESC USER_VIEWS;

SELECT *
FROM USER_VIEWS WHERE VIEW_NAME='DNOSAL';

문제  ] EMP 테이블의 사원들의 사원번호, 사원이름, 부서번호, 부서이름, 부서위치를 조회하는 뷰를 만들어보자.

           (뷰이름 : EMP_DNO)

CREATE OR REPLACE VIEW EMP_DNO
AS 
    SELECT EMPNO EMPNO, ENAME ENAME, DNAME DNAME, E.DEPTNO DNO, LOC LOC
    FROM EMP E, DEPT D
    WHERE E.DEPTNO = D.DEPTNO;

--데이터추가 (두 테이블을 조인해서 만든 복합뷰이므로 DML 명령은 사용할 수 없다.
INSERT INTO
    EMP_DNO
VALUES(
    8000, 'JENNIE',40,'OPERATIONS','BOSTON'
);

    문제2 ] 10번부서 사원들의 사원번호, 사원이름, 급여, 커미션 을 조회하는 뷰(DNO10을 만드세요)

CREATE OR REPLACE VIEW DNO10
AS SELECT EMPNO, ENAME, SAL, COMM
    FROM EMP
    WHERE DEPTNO=10;

DESC DNO10;


--제니사원이 입사했습니다.
INSERT INTO 
    DNO10
VALUES(
    8000,'제니',7000,10000
);

DESC DNO10;

ROLLBACK;

--EMP 테이블을 복사해서 EMPLOYEE 테이블을 만드세요

CREATE TABLE EMPLOYEE
AS SELECT *
    FROM EMP;
    
    
--기본키 제약조건과 참조키 제약조건을 추가하세요
ALTER TABLE EMPLOYEE
ADD CONSTRAINT EMPLY_NO_PK PRIMARY KEY(EMPNO);

ALTER TABLE EMPLOYEE
ADD CONSTRAINT EMPLY_DNO_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);

문제3 ]   EMPLOYEE 테이블의 10번부서 사원들의 사원번호, 사원이름, 급여, 커미션을 조회하는 뷰(DNO10)을 만드세요
               뷰를 만드는 조건으로 사용된느 컬럼의 데이터는 수정하지 못하도록 하세요

CREATE OR REPLACE VIEW DNO10
AS
    SELECT  EMPNO, ENAME, SAL, COMM, DEPTNO
    FROM   EMPLOYEE
    WHERE DEPTNO = 10
WITH CHECK OPTION;

--DNO10 조회
SELECT * FROM DNO10;

--CLARK의 커미션을 500으로 인상하세요
UPDATE 
    DNO10
SET
    COMM = NVL(COMM+ 500,500)
WHERE ENAME = 'CLARK';

-- KING의 부서번호를 40번으로 수정하세요

UPDATE
    EMPLOYEE
SET DEPTNO = 40
WHERE ENAME = 'KING';
-- 게시판 테이블(BOARD)의 글번호, 작성자 번호, 글제목, 작성일, 클릭수를 조회하는 뷰를 작성하세요
CREATE OR REPLACE FORCE VIEW BRDLIST
AS 
    SELECT BNO,BMNO,TITLE,WDATE,CLICK
    FROM BOARD
;    

--BRDLIST 삭제
DROP VIEW BRDLIST;

ROLLBACK;