6일차 : DDL(Data Definition Language)

오늘 내용 중 중요한 부분 : PK와 FK 제약조건, 설정방법, 왜 걔네들이 필요한지 보기, +DROP 테이블, 오라클 휴지통

PRIMARY KEY

: 중복된 컬럼값 저장을 방지하기 위한 제약조건(이 점은 UNIQUE와 동일, 나머지는 다름)

• 컬럼 수준의 제약조건 또는 테이블 수준의 제약조건으로 설정 가능
• PRIMARY KEY 제약조건은 테이블 하나의 컬럼에만 설정하며 NULL을 허용하지 않는다 - UNIQUE와 차이점
• 테이블의 행을 식별할 수 있는 고유값을 저장하기 위한 컬럼에 PRIMARY KEY 제약조건 설정

MGR1 테이블 생성 : 사원번호(숫자형-PRIMARY KEY:PK),사원이름(문자형),입사일(날짜형) - 컬럼 수준의 제약조건으로 설정

CREATE TABLE MGR1(NO NUMBER(4) CONSTRAINT MGR1_NO_PK PRIMARY KEY,NAME VARCHAR2(20),STARTDATE DATE);
DESC MGR1;

--제약조건 확인
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='MGR1';

질의 결과 - 테이블 생성 - DESC MGR1 - 제약조건 확인

MGR1 테이블에 행 삽입

INSERT INTO MGR1 VALUES(1000,'홍길동',SYSDATE);

--에러 : PRIMARY KEY 제약조건이 설정된 컬럼에 기존행의 컬럼값과 같은 값을 전달할 경우 에러 발생
INSERT INTO MGR1 VALUES(1000,'임꺽정',SYSDATE); 

--에러 : PRIMARY KEY 제약조건이 설정된 컬럼에 NULL을 전달할 경우 에러 발생
INSERT INTO MGR1 VALUESVALUES(NULL,'임꺽정',SYSDATE); 

SELECT * FROM MGR1;
COMMIT;

질의 결과

MGR2 테이블 생성 : 사원번호(숫자형-PRIMARY KEY:PK),사원이름(문자형),입사일(날짜형) - 테이블 수준의 제약조건으로 설정

CREATE TABLE MGR2(NO NUMBER(4),NAME VARCHAR2(20),STARTDATE DATE,CONSTRAINT MGR2_NO_PK PRIMARY KEY(NO));
DESC MGR2;

--제약조건 확인 
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='MGR2';

질의 결과 - 테이블 생성 - DESC MGR2 - 제약조건 확인

FOREIGN KEY

: 부모 테이블에 저장된 행의 컬럼값을 참조하여 자식 테이블의 컬럼에 잘못된 값이 저장되는 것을 방지하는 제약조건

• INNERJOIN하려면 조인조건 맞아야하고 그럴때 FOREIGN KEY 설정
• 테이블 수준의 제약조건만 설정 확인 가능
• 부모 테이블의 PRIMARY KEY 제약조건이 설정된 컬럼을 참조하여 자식 테이블의 컬럼에 FORGIEN KEY 제약조건을 설정

SUBJECT1 테이블 생성, TRAINEE1 테이블 생성

--SUBJECT1 테이블 생성 : 과목코드(숫자형-PRIMARY KEY), 과목명(문자형) 부모 테이블 - 
CREATE TABLE SUBJECT1(SNO NUMBER(2) CONSTRAINT SUBJECT1_SNO_PK PRIMARY KEY,SNAME VARCHAR2(20));

--SUBJECT1 테이블에 행 삽입 
INSERT INTO SUBJECT1 VALUES(10,'JAVA');
INSERT INTO SUBJECT1 VALUES(20,'JSP');
INSERT INTO SUBJECT1 VALUES(30,'SPRING');
SELECT * FROM SUBJECT1;
COMMIT;

--TRAINEE1 테이블 생성 : 수강생번호(숫자형-PRIMARY KEY),수강생이름(문자형),수강과목코드(숫자형)
CREATE TABLE TRAINEE1(TNO NUMBER(4) CONSTRAINT TRAINEE1_TNO_PK PRIMARY KEY,TNAME VARCHAR2(20),SCODE NUMBER(2));

--TRAINEE1 테이블에 행 삽입
INSERT INTO TRAINEE1 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE1 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE1 VALUES(3000,'전우치',30);
INSERT INTO TRAINEE1 VALUES(4000,'일지매',40);
SELECT * FROM TRAINEE1;
COMMIT;

질의 결과 - SUBJECT1 테이블, TRAINEE1 테이블

INNER JOIN, OUTER JOIN 검색결과 차이

TRAINEE1 테이블과 SUBJECT1 테이블에서 모든 수강생의 수강생 번호,수강생이름,수강과목명 검색

--INNER JOIN인 경우 조인조건이 맞는 행만 결합하여 검색 - 조인조건이 맞지 않는 수강생 미검색 
-->>검색 오류 : 데이터 무결성을 위반 - 컬럼에 참조할 수 없는 컬럼값이 저장 
SELECT TNO,TNAME,SNAME FROM TRAINEE1 JOIN SUBJECT1 ON SCODE=SNO; --40번 과목을 듣는 일지매, 40번 과목은 없다 
--OUTER JOIN인 경우 조인조건이 맞는 행뿐만 아니라 조인조건이 맞지 않는 행도 검색 
SELECT TNO,TNAME,SNAME FROM TRAINEE1 LEFT JOIN SUBJECT1 ON SCODE=SNO;

질의 결과 - INNER JOIN, OUTER JOIN

FORGIEN KEY:FK

NEW 부모 테이블
SUBJECT2 테이블 생성 : 과목코드(숫자형-PRIMARY KEY), 과목명(문자형)

CREATE TABLE SUBJECT2(SNO NUMBER(2) CONSTRAINT SUBJECT2_SNO_PK PRIMARY KEY,SNAME VARCHAR2(20));

--SUBJECT2 테이블에 행 삽입 
INSERT INTO SUBJECT2 VALUES(10,'JAVA');
INSERT INTO SUBJECT2 VALUES(20,'JSP');
INSERT INTO SUBJECT2 VALUES(30,'SPRING');
SELECT * FROM SUBJECT2;
COMMIT;

자식 테이블
TRAINEE3 테이블 생성 : 수강생번호(숫자형-PRIMARY KEY),수강생이름(문자형),수강과목코드(숫자형-FORGIEN KEY:FK)

• FOREIGN KEY 제약조건을 설정할 때 ON DELETE CASCADE 또는 ON DELETE SET NULL 기능 추가
  1) ON DELETE CASCADE : 부모 테이블의 행을 삭제 할 경우 자식 테이블에 참조 컬럼값이 저장된 행도 같이 삭제하는 기능
  2) ON DELETE SET NULL : 부모 테이블의 행을 삭제 할 경우 자식 테이블에 참조 컬럼값을 NULL로 변경하는 기능

CREATE TABLE TRAINEE3(TNO NUMBER(4) CONSTRAINT TRAINEE3_TNO_PK PRIMARY KEY,TNAME VARCHAR2(20),SCODE NUMBER(2)
    ,CONSTRAINT TRAINEE3_SCODE_FK FOREIGN KEY(SCODE) REFERENCES SUBJECT2(SNO) ON DELETE CASCADE);
    
--TRAINEE3 테이블에 행 삽입
INSERT INTO TRAINEE3 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE3 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE3 VALUES(3000,'전우치',30);
SELECT * FROM TRAINEE3;
COMMIT;

--SUBJECT2 테이블에서 과목코드가 10인 과목정보 삭제
DELETE FROM SUBJECT2 WHERE SNO=10;
SELECT * FROM SUBJECT2;
--TRAINEE3 테이블에 저장된 수강생 중 수강과목코드가 10이 저장된 모든 수강생 정보 삭제 확인
SELECT * FROM TRAINEE3;

서브쿼리를 이용하여 테이블 생성

: 기존 테이블을 이용하여 새로운 테이블 생성 - 행 복사
형식)CREATE TABLE 테이블명[(컬럼명,컬럼명,...)] AS SELECT 검색대상,검색대상,... FROM 테이블명 [WHERE 조건식]

• 서브쿼리의 검색결과와 동일한 속성의 테이블을 생성하며 검색행을 생성된 테이블에 삽입
• 생성 테이블의 컬럼명이 생략된 경우 서브쿼리의 검색대상을 컬럼명으로 사용
• 기존 테이블과 속성은 같지만 생성 테이블에는 제약조건 미설정

--EMP 테이블에서 모든 사원 정보를 검색하여 EMP2 테이블을 생성하고 검색행을 복사하여 삽입 
CREATE TABLE EMP2 AS SELECT * FROM EMP;

--EMP 테이블과 EMP2 테이블의 속성 비교 - 두 테이블의 속성 동일
DESC EMP;
DESC EMP2;

--EMP 테이블과 EMP2 테이블의 제약조건 비교 - EMP 테이블에는 제약조건이 설정되어 있지만 EMP2 테이블에는 제약조건 미설정 
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP';
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP2';

-EMP 테이블에서 모든 사원의 사원번호,사원이름,급여를 검색하여 EMP3 테이블을 생성하고 검색행을 복사하여 삽입
CREATE TABLE EMP3 AS SELECT EMPNO,ENAME,SAL FROM EMP;

--EMP3 테이블의 속성 확인 및 저장행 검색
DESC EMP3;
SELECT * FROM EMP3;

--EMP 테이블에서 급여가 2000 이상인 사원의 사원번호,사원이름,급여를 검색하여 EMP4 테이블을 생성하고 검색행을 복사하여 삽입
CREATE TABLE EMP4 AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>=2000;

--EMP4 테이블의 속성 확인 및 저장행 검색 
DESC EMP4;
SELECT * FROM EMP4;

--EMP 테이블에서 급여가 2000 이상인 사원의 사원번호,사원이름,급여를 검색하여 EMP5 테이블을 생성하고 검색행을 복사하여 삽입 
-- >> EMP5 테이블의 컬럼명을 각각 사원번호 NO,사원이름 NAME,급여 PAY로 설정 - 검색대상의 갯수와 컬럼의 갯수가 동일
CREATE TABLE EMP5(NO,NAME,PAY) AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>=2000;

--EMP5 테이블의 속성 확인 및 저장행 검색 
DESC EMP5;
SELECT * FROM EMP5;

--EMP 테이블과 동일한 속성의 EMP6 테이블 생성 - 기존 테이블의 행이 복사되지 않도록 작성 
CREATE TABLE EMP6 AS SELECT * FROM EMP WHERE 0=1; --조건식을 무조건 거짓이 되도록 설정 

--EMP6 테이블의 속성 확인 및 저장행 검색 
DESC EMP6;
SELECT * FROM EMP6;

테이블 삭제 : 테이블에 저장된 모든 행이 같이 삭제

형식) DROP TABLE 테이블명
삭제되어서 롤백 사용할 수 없ㄷ음

--테이블 목록 확인 
SELECT TABLE_NAME FROM TABS;

--USER1 테이블 삭제 
DROP TABLE USER1;
SELECT TABLE_NAME FROM TABS WHERE TABLE_NAME='USER1';
SELECT * FROM USER1;

질의 결과

오라클 휴지통

오라클에서는 테이블을 삭제할 경우 테이블과 테이블 관련 객체를 휴지통(RECYCLEBIN)으로 이동
-> 삭제 테이블과 테이블 관련 INDEX 객체 복구 가능

--테이블 목록 확인 - USER_TABLETS(TABS)
SELECT TABLE_NAME FROM TABS;

--테이블 목록 확인 - TAB 뷰 이용
--TNAME 컬럼에 BIN으로 시작되는 테이블은 오라클 휴지통에 접종하는 삭제 테이블
SELECT * FROM TAB;

--오라클 휴지통의 객체 목록 확인
SHOW RECYCLEBIN;

--오라클 휴지통에 존재하는 삭제 테이블 복구 - 테이블 관련 INDEX 객체도 같이 복구 
--형식)FLASHBACK TABEL 테이블명 TO BEFORE DROP;

--USER1 삭제 테이블 복구 - 테이블에 저장된 행도 같이 복구
FLASHBACK TABLE USER1 TO BEFORE DROP;
SELECT TABLE_NAME FROM TABS WHERE TABLE_NAME='USER1';
SELECT * FROM USER1;

--오라클 휴지통의 객체 목록 확인
SHOW RECYCLEBIN;

--USER1,USER2,USER3,USER4 테이블 한꺼번에 삭제
SELECT TABLE_NAME FROM TABS WHERE TABLE NAME_LIKE 'USER%';
DROP TABLE USER1;
DROP TABLE USER2;
DROP TABLE USER3;
DROP TABLE USER4;
SELECT TABLE_NAME FROM TABS WHERE TABLE NAME_LIKE 'USER%';
SHOW RECYCLEBIN;

오라클 휴지통의 삭제 테이블 제거 - 테이블이 종속된 INDEX 객체도 같이 제거

--형식)PURGE TABLE 테이블명
PURGE TABLE USER4;
SHOW RECYCLEBIN;

--오라클 휴지통의 모든 삭제 테이블 제거 
PURGE RECYCLEBIN;
SHOW RECYCLEBIN;

--오라클 휴지통에서 삭제 테이블 제거
PURGE TABLE MGR1;
SHOW RECYCLEBIN;

MGR2 테이블 삭제 - 오라클 휴지통을 이용하지 않고 삭제 처리

형식)DROP TABLE 테이블명 PURGE

DROP TABLE MGR2 PURGE;
SHOW RECYCLEBIN;