(7) SUBQUERY를 이용한 CREATE TABLE

: 서브쿼리를 이용해헛 테이블 복사(SELECT문 이용)
단, 컬럼명, 데이터타입, 값만 복사되고 제약조건은 NOT NULL만 복사됨

[표현법]
CREATE TABLE 테이블명
AS 서브쿼리;
: 해당 서브쿼리를 수행한 결과로 새로이 테이블을 생성하는 개념!

<예시>
1) EMPLOYEE 테이블을 복제한 새로운 테이블 생성(EMPLOYEE_COPY)

CREATE TABLE EMPLOYEE_COPY 
AS SELECT * 
   FROM EMPLOYEE
--> 컬럼들, 조회결과의 데이터값들, 제약조건 같은 경우 NOT NULL만 복사됨


2) EMPLOYEE 테이블에 있는 컬럼의 구조만 복사하고 싶음!! 데이터값 필요없음

CREATE TABLE EMPLOYEE_COPY2
AS SELECT * 
   FROM EMPLOYEE
   WHERE 1=0;

테이블 생성된 후 뒤늦게 제약조건 추가(ALTER TABLE)

: 테이블 생성된 후 제약조건 추가
[표현법]
ALTER TABLE 테이블명
제약조건(1~5 항목)

(1) PRIMARY KEY : ADD PRIMARY KEY(컬럼명);

<예시>
-- EMPLOYEE_COPY 테이블에 없는 PRIMARY KEY 제약조건 추가 EMP_ID 컬럼에
ALTER TABLE EMPLOYEE_COPY ADD PRIMARY KEY(EMP_ID);

(2) FOREIGN KEY : ADD FOREIGN KEY(컬럼명) REFERENCES 참조할 테이블명(참조할 컬럼명);

<예시>
-- EMPLOYEE 테이블에 DEPT_CODE 컬럼에 외래키 제약조건 추가 (DEPARTMENT의 DEPT_ID 참조)
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(DEPT_CODE) REFERENCES DEPARTMENT(DEPT_ID);

(3) UNIQUE : ADD UNIQUE(컬럼명);

(4) CHECK : ADD CHECK(컬럼에 대한 조건);

(5) NOT NULL : MODIFY 컬럼명 NOT NULL ( 또는 NULL);

1. DML(DATA MANIPULATION LANGUAGE)

: 데이터 조작언어

테이블에 새로운 데이터를 삽입(INSERT)하거나, 기존의 데이터를 수정(UPDATE)하거나, 삭제(DELETE)하는 구문

1) INSERT

: 테이블에 새로운 행을 추가하는 구문

[표현법]

(1) INSERT INTO 테이블명 VALUES (값, 값, 값, 값...)

: 해당 테이블에 모든 컬럼에 추가하고자 하는 값을 내가 직접 제시해서 한 행 INSERT 하고자 할 때

주의할 점: 컬럼 순번을 지켜서 VALUES에 값을 나열해야 됨!

<예시>
INSERT INTO EMPLOYEE
VALUES(900, '장채현', '980914-2333423', 'jang-cg@kh.or.kr', '01033432211'
       , 'D1', 'J7', 40000000, 0.2, 200, SYSDATE, NULL, DEFAULT);

[표현법]

(2) INSERT INTO 테이블명(컬럼명, 컬럼명, 컬럼명) VALUES (값, 값, 값...);

: 해당 테이블에 특정 컬럼만 선택해서 그 컬럼에 추가할 값만 제시하고자 할 때 사용

그래도 한 행 단위로 추가되기 때문에 선택 안 된 컬럼은 기본적으로 NULL값으로 들어감
(단, 기본값(DEFAULT)이 지정되어있다면 기본값이 들어감)

※ 주의할 점: NOT NULL 제약조건이 걸려있는 컬럼은 반드시 선택해서 직접 값 제시해야 됨
아무리 NOT NULL 제약조건이 걸려있는 컬럼이라고 해도 기본값이 지정되어있는 컬럼은 선택 안해도 됨

<예시>
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, DEPT_CODE, JOB_CODE, HIRE_DATE)
VALUES (901, '홍길동', '800203-1234567', 'D1', 'J2', SYSDATE); 

[표현법]

(3) INSERT INTO 테이블명 (서브쿼리);

: VALUES로 값 기입하는 것 대신에 서브쿼리로 조회한 결과값을 통째로 INSERT 하는 구문
(여러행 INSERT 가능)

<예시>
-- 먼저 테이블 만들고,
CREATE TABLE EMP_01(
    EMP_ID NUMBER,
    EMP_NAME VARCHAR2(30),
    DEPT_TITLE VARCHAR2(20)
);

--  전체사원 (부서배치가 안된 사원포함)들의 사번, 이름, 부서명을 조회한 결과를 EMP_01 테이블에 통째로 추가
INSERT INTO EMP_01
    (
        SELECT EMP_ID, EMP_NAME, DEPT_TITLE
        FROM EMPLOYEE
        LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)    
    );
    
 => EMP_01 테이블에 서브쿼리 내용을 통해 여러행(25행) 추가됨.

2)INSERT ALL

: 두개 이상의 테이블에 각각 INSERT할 때 사용
그 때 사용되는 서브쿼리가 동일할 경우

(1) INSERT ALL과 JOIN

<예시>
--> 우선 테이블 만들기

--> 첫번째 테이블 : 사번, 사원명, 직급명에 대해 보관할 테이블
CREATE TABLE EMP_JOB (
    EMP_ID NUMBER,
    EMP_NAME VARCHAR2(20),
    JOB_NAME VARCHAR2(20)
);


--> 두번째 테이블 : 사번, 사원명, 부서명에 대해 보관할 테이블
CREATE TABLE EMP_DEPT (
    EMP_ID NUMBER,
    EMP_NAME VARCHAR2(20),
    DEPT_TITLE VARCHAR2(20)
);

SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE SALARY <= 3000000;

-- EMP_JOB 테이블에 급여 300만원 이상인 사원들의 EMP_ID, EMP_NAME, JOB_NAME 삽입
-- EMP_DEPT 테이블에 급여 300만원 이상인 사원들의 EMP_ID, EMP_NAME, DEPT_TITLE 삽입

INSERT ALL
INTO EMP_JOB VALUES(EMP_ID, EMP_NAME, JOB_NAME)
INTO EMP_DEPT VALUES (EMP_ID, EMP_NAME, DEPT_TITLE)
    SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE
    FROM EMPLOYEE
    JOIN JOB USING (JOB_CODE)
    JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    WHERE SALARY <= 3000000;

[표현법]

(2) INSERT ALL과 WHEN THEN

<예시>
-- INSERT ALL 시 조건을 사용해서도 각 테이블에 값 INSERT 가능

-- 사번, 사원명, 입사일, 급여 (EMP_OLD)
CREATE TABLE EMP_OLD 
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
   FROM EMPLOYEE
   WHERE 1=0;

-- EMP_NEW
CREATE TABLE EMP_NEW 
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
   FROM EMPLOYEE
   WHERE 1=0;

SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE
-- WHERE HIRE_DATE < '2000/01/01'; -- > 2000년도 이전 입사자 => EMP_OLD 
WHERE HIRE_DATE >= '2000/01/01';  --> 2000년도 이후 입사자  => EMP_NEW

/*
    2) INSERT ALL
    WHEN 조건1 THEN 
        INTO 테이블명1 VALUES(컬럼명, 컬럼명...)
    WHEN 조건 2 THEN
        INTO 테이블명2 VALUES(컬럼명, 컬럼명...)    
    서브쿼리
*/

INSERT ALL 
WHEN HIRE_DATE < '2000/01/01' THEN
    INTO EMP_OLD VALUES (EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
WHEN HIRE_DATE >= '2000/01/01' THEN
    INTO EMP_NEW VALUES (EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY 
FROM EMPLOYEE;

3) UPDATE

: 테이블에 기록된 기존의 데이터를 수정하는 구문

[표현식]
UPDATE 테이블명
SET 컬럼명 = 바꿀 값
, 컬럼명 = 바꿀 값...
=> 여러개의 컬럼 값 동시변경 가능( , 로 나열해야됨! AND 아님!)
(WHERE 조건);

(1) 특정 조건을 갖춘 컬럼 수정

<예시>

-- 복사본 테이블 만든 후 작업하자!
CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;

-- DEPT_COPY 테이블에 D9부서의 부서명을 전략기획팀으로 수정
UPDATE DEPT_COPY
SET DEPT_TITLE = '전략기획팀' --> 전체 행의 모든 DEPT_TITLE값이 다 전략기획팀으로 변경되어버림
--> WHERE절(조건) 추가해야함 

UPDATE DEPT_COPY
SET DEPT_TITLE = '전략기획팀'
WHERE DEPT_ID = 'D9';

(2) 컬럼 내에 전체내용 수정

<예시>
-- 전체사원의 급여를 기존의 급여에 20프로 인상한 금액으로 변경

UPDATE EMP_SALARY
SET SALARY = SALARY * 1.2;

(3) 서브쿼리를 이용한 UPDATE

: 서브쿼리를 수행한 결과값으로 변경하겠다.

[표현법]
UPDATE 테이블명
SET 컬럼명 = (서브쿼리)
(WHERE 조건);

<단일행 서브쿼리>

-- EMP_SALARY 테이블에 강람보사원의 부서코드를 선동일 사원의 부서코드로 변경
UPDATE EMP_SALARY
SET DEPT_CODE = (SELECT DEPT_CODE
                 FROM EMP_SALARY
                 WHERE EMP_NAME = '선동일')
WHERE EMP_NAME = '홍길동';

<다중행 서브쿼리>
-- 방명수 사원의 급여와 보너스로 유재식 사원의 급여와 보너스를 같은 값으로 변경

UPDATE EMP_SALARY
SET(SALARY, BONUS) = (SELECT SALARY, BONUS
                      FROM EMP_SALARY
                      WHERE EMP_NAME ='방명수')
WHERE EMP_NAME = '유재식';

※ UPDATE시에도 변경할 값에 있어서 해당 컬럼에 대한 제약조건에 위배되면 안됨!

-- 노옹철 사원의 부서코드를 D0으로 변경
UPDATE EMPLOYEE
SET DEPT_CODE = 'D0'
WHERE EMP_NAME = '노옹철'; -> 외래키 제약조건에 위배

-- 사번이 200인 사원의 이름을 NULL로 변경
UPDATE EMPLOYEE
SET EMP_NAME = NULL
WHERE EMP_ID = 200; --> NOT NULL 제약조건에 위배

4) DELETE

: 테이블에 기록된 데이터를 삭제하는 구문

[표현법]
DELETE FROM 테이블명
(WHERE 조건); -> WHERE절 생략시 해당 테이블의 전체 행 삭제

• COMMIT; --> 모든 변경사항들 확정짓겠다. 픽스하겠다.
• ROLLBACK; --> 마지막 커밋시점으로 돌아감

※ 참고) 자식데이터가 존재하는 행은 삭제되지 않음.

5) TRUNCATE

: 조건없이 테이블의 전체행을 삭제할 때 쓰는 구문(절삭)

2. DDL(DATA DEFINITION LANGUAGE)

: 객체들을 새로이 생성(CREATE), 수정(ALTER), 삭제(DROP)하는 구문(데이터 정의언어)

1) ALTER

: 객체 구조를 수정하는 구문

<테이블 수정>
ALTER TABLE 테이블명 수정할내용;

-수정할 내용-

• (1) 컬럼 추가/수정/삭제
• (2) 제약조건 추가/삭제 -> 수정은 불가(수정하고자 한다면 삭제한 후 새로이 추가)
• (3) 테이블명/컬럼명/제약조건명 변경

(1) 컬럼 추가/수정/삭제 => DDL구문은 ROLLBACK 불가능

• (1)-1 컬럼추가(ADD) : ADD 추가할 컬럼명 데이터타입 (DEFAULT 기본값)

ALTER TABLE DEPT_COPY ADD CNAME VARCHAR2(20);
-> 새로운 컬럼 생성(값은 NULL값으로 채워짐) 
=> DEFAULT '한국'으로 하면 NULL값이 아닌 '한국'으로 채워짐

• (1)-2 컬럼수정(MODIFY)
  • 데이터타입 수정 : MODIFY 수정할 컬럼명 바꾸고자하는 데이터타입
  • DEFAULT값 수정 : MODIFY 수정할 컬럼명 DEFAULT 바꾸고자하는 기본값

<예시>
-- DEPT_ID 컬럼의 데이터타입을 CHAR(3)로 변경 (CHAR -> VARCHAR2 가능)
ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(3);

-- 데이터타입 자료형변경 불가 : ALTER TABLE DEPT_COPY MODIFY DEPT_ID NUMBER;
-- 데이터타입 줄이기 불가 : ALTER TABLE DEPT_COPY MODIFY DEPT_TITLE VARCHAR2(10);


ALTER TABLE DEPT_COPY
    MODIFY DEPT_TITLE VARCHAR2(40)
    MODIFY LOCATION_ID VARCHAR2(2)
    MODIFY LNAME DEFAULT '미국';

• (1)-3 컬럼삭제(DROP COLUMN)
  : DROP COLUMN 삭제하고자 하는 컬럼명;

• 특징
  1) DDL구문은 복구 불가(ROLLBACK 안됨)
  2) 테이블에 최소 한개의 컬럼은 존재해야함

<예시>
-- DEPT_COPY2로부터 DEPT_ID 컬럼 지우기
ALTER TABLE DEPT_COPY2 DROP COLUMN DEPT_ID;
ROLLBACK; --> DDL구문은 복구 불가능

ALTER TABLE DEPT_COPY2 DROP COLUMN DEPT_TITLE;
ALTER TABLE DEPT_COPY2 DROP COLUMN LOCATION_ID;
ALTER TABLE DEPT_COPY2 DROP COLUMN CNAME;
ALTER TABLE DEPT_COPY2 DROP COLUMN LNAME; --> 테이블에 최소 한개의 컬럼은 존재해야됨