Database 기본8 DDL(ALTER), DML(INSERT, UPDATE, DELETE)

----------- 2021-03-12
 
-- 서브쿼리 이용 테이블 만들기
 
-- 테이블 만들고 복사해오기
CREATE TABLE EMP_COPY
AS SELECT * FROM EMPLOYEE;
 
SELECT * FROM EMPLOYEE;
DROP TABLE EMP_COPY;
 
-- 복사시 데이터 없이 테이블만 생성하려면?
CREATE TABLE EMP_COPY
AS SELECT EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
    FROM EMPLOYEE WHERE 1=2;
SELECT * FROM EMP_COPY;
 
-- DML (Data Manipulation Language.....INSERT, UPDATE, DELETE)
-- 1. 지정한 컬럼에만 데이터 추가해보기 (지정안된건 NULL)
INSERT INTO DEPARTMENT(DEPT_ID, LOCATION_ID) -- 지정되지 않은 컬럼에 NOT NULL 설정되어 있으면 오류
VALUES ('A1', 'L3');
 
-- 모든 컬럼에 값 넣기
INSERT INTO DEPARTMENT
VALUES ('A2', '오라클개발부', 'L4'); -- NULL을 넣는것도 가능
 
-- INSERT문에 서브쿼리 이용해보기
CREATE TABLE INSERT_TEST
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE
    FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
    WHERE 1=2;
 
SELECT * FROM INSERT_TEST;
 
INSERT INTO INSERT_TEST
VALUES ('1', 'GOM', '개발팀');
 
-- 기존에 있는 데이터를 복사해서 넣어보기
INSERT INTO INSERT_TEST (SELECT EMP_ID, EMP_NAME, DEPT_TITLE
    FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
    WHERE DEPT_CODE='D5'
);
 
-- INSERT ALL : 2개 이상의 테이블에 한 SELECT문의 결괏값을 넣는다.
CREATE TABLE EMP_HIRE_DATE
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE
FROM EMPLOYEE WHERE 1=0;
CREATE TABLE EMP_MANAGE
AS SELECT EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE WHERE 1=0;
 
INSERT ALL
INTO EMP_HIRE_DATE VALUES(EMP_ID, EMP_NAME, HIRE_DATE)
INTO EMP_MANAGE VALUES(EMP_ID, EMP_NAME, MANAGER_ID)
SELECT EMP_ID, EMP_NAME, HIRE_DATE, MANAGER_ID
FROM EMPLOYEE;
 
SELECT*FROM EMP_HIRE_DATE;
 
-- INSERT ALL를 조건에 따라 분리해서 삽입해보기
CREATE TABLE EMP_OLD
AS SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE 1=0;
 
CREATE TABLE EMP_NEW
AS SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE WHERE 1=0;
 
SELECT*FROM EMP_OLD;
SELECT*FROM EMP_NEW;
 
INSERT ALL
WHEN '00/01/01'>HIRE_DATE THEN INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, SALARY, HIRE_DATE)
WHEN '00/01/01'<= HIRE_DATE THEN INTO EMP_NEW VALUES(EMP_ID, EMP_NAME, SALARY, HIRE_DATE)
SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE;
 
-- UPDATE : 컬럼값을 수정.. 테이블 전체 행 갯수는 변화가 없다.
CREATE TABLE EMP_COPY
AS SELECT * FROM EMPLOYEE;
DROP TABLE EMP_COPY;
SELECT*FROM EMP_COPY;
-- 'A1'부서의 부서명을 '자바개발부'로 변경.. UPDATE에서 WHERE 빼먹지 말기! 전부 바뀌어버린다..
UPDATE DEPT_COPY SET DEPT_TITLE='자바개발부' WHERE DEPT_ID='A1';
 
-- UPDATE 문에서 서브쿼리 사용해보기
-- 급여가 200만원 미만인 사원의 보너스를 0.4로 변경하기
UPDATE EMP_COPY SET BONUS=0.4 WHERE SALARY<2000000;
 
-- 방명수의 월급을 심봉선과 같게 변경하기
UPDATE EMP_COPY SET SALARY=(SELECT SALARY FROM EMP_COPY WHERE EMP_NAME='심봉선')
WHERE EMP_NAME = '방명수';
 
-- 방명수의 월급과 보너스를 전지연의 월급과 보너스와 같게 변경하기
UPDATE EMP_COPY SET (SALARY, BONUS)=(SELECT SALARY, BONUS FROM EMP_COPY WHERE EMP_NAME='전지연')
WHERE EMP_NAME='방명수';
 
-- 임시환의 직급을 과장, 부서를 해외영업3부로 변경하는 쿼리 작성
UPDATE EMP_COPY SET (JOB_CODE, DEPT_CODE)=(SELECT JOB_CODE, DEPT_CODE
    FROM JOB CROSS JOIN DEPARTMENT
    WHERE JOB_NAME='과장' AND DEPT_TITLE='해외영업3부')
-- SET JOB_CODE=(SELECT JOB_CODE FROM JOB WHERE JOB_NAME ='과장'),
--                     DEPT_CODE=(SELECT DEPT_ID FROM DEPARTMENT WHERE DEPT_TITLE='해외영업3부')
WHERE EMP_NAME='임시환';
 
-- DELETE : 조건에 맞는 ROW를 삭제하는 명령어
-- DELETE FROM 테이블 [WHERE 조건식] // WHERE절 생략시 전체 삭제
 
-- 차태연 삭제하기
DELETE FROM EMP_COPY WHERE EMP_NAME='차태연';
 
-- BONUS 안받는 사원 삭제
DELETE FROM EMP_COPY WHERE BONUS IS NULL;
 
-- TRUNCATE : 테이블 전체 행 삭제.. DELETE보다 빠르고 ROLLBACK으로 복구 불가능
 
-- MERGE : 구조가 같은 2개의 테이블을 1개의 테이블로 합치는 기능
-- MERGE INTO 기준이 될 테이블
-- USING 합칠테이블 ON (동일여부 판단할 기준컬럼)
-- WHEN [NOT]MATCHED THEN DML구문
MERGE INTO TBL_MERGE01
USING TBL_MERGE02 ON (TBL_MERGE01.ID=TBL_MERGE02.ID)
WHEN MATCHED THEN UPDATE TBL_MERGE01.NAME=TBL_MERGE02.NAME;
 
-- DDL (Dtat Definition Language) : 객체를 CREATE, ALTER(수정), DROP
 
-- ALTER : 테이블에 정의된 내용을 수정할 때 사용..
-- ㄴ 컬럼 및 제약조건의 삭제/추가, 컬럼의 자료형 변경, DEFAULT값 변경, 테이블-컬럼-제약조건 이름을 바꿀 수 있다.
 
CREATE TABLE TBL_USER_ALTER(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(20),
    USER_NAME VARCHAR2(20)
);
 
INSERT INTO TBL_USER_ALTER VALUES (1, 'ADMIN', '1234', '관리자');
INSERT INTO TBL_USER_ALTER VALUES (2, 'USER01', 'USER01', '유저일1');
 
-- 테이블에 데이터가 있는 상태에서 새 컬럼 추가
ALTER TABLE TBL_USER_ALTER ADD (ADDRESS VARCHAR2(100));
 
-- 새 컬럼 추가할 때 제약조건도 추가
ALTER TABLE TBL_USER_ALTER ADD (GENDER VARCHAR2(10) CHECK ( GENDER IN ('남', '여')));
 
-- 데이터가 있는 테이블에 컬럼을 추가할 때 주의.. NOT NULL 제약조건 설정 시 주의
ALTER TABLE TBL_USER_ALTER ADD(EMAIL VARCHAR2(50) DEFAULT 'NONE' NOT NULL );
 
-- 제약조건 추가
ALTER TABLE TBL_USER_ALTER ADD CONSTRAINT TBL_USER_ID_QU UNIQUE (USER_ID);
-- NOT NULL -> 추가가 안된다.. 컬럼 생성시 디폴트가 NULL 허용이라 기존 조건을 수정해야 함.
-- ALTER TABLE 테이블명 MODIFY 컬럼명 자료형 -> 컬럼 수정
-- ALTER TABLE 테이블명 MODIFY 컬럼명 CONSTRAINT 제약조건명 제약조건 -> 제약조건 수정
ALTER TABLE TBL_USER_ALTER MODIFY EMAIL CHAR(100);
 
-- ALTER TABLE 테이블명 DROP COLUMN 컬럼명 -> 컬럼 삭제하기
ALTER TABLE TBL_USER_ALTER DROP COLUMN EMAIL;
 
-- ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명 -> 제약조건 삭제
ALTER TABLE TBL_USER_ALTER DROP CONSTRAINT SYS_C007200;
 
-- ALTER TABLE 테이블명 RENAME COLUMN 수정전컬럼명 TO 수정후컬럼명 -> 컬럼명 수정
ALTER TABLE TBL_USER_ALTER RENAME COLUMN USER_ID TO USER_NICK;
ALTER TABLE TBL_USER_ALTER RENAME CONSTRAINT TBL_USER_ID_QU TO TBL_USER_NICK_QU;-- 제약조건명 수정