2022_04_27_DB_5일

INSERT ,UPDATE , DELETE , MERGE ,CTAS

--INSERT의 첫번째 방법(모든 컬럼 데이터를 한번에 지정)
INSERT INTO departments VALUES(280,'개발자',null,1700);

--INSERT의 두번째 방법(직접 컬럼을 지정하고 저장)
INSERT INTO departments(department_id,department_name,location_id)
VALUES(280,'개발자',1700);

--INSERT (서브쿼리)
INSERT INTO managers
(SELECT employee_id,first_name,job_id,hire_date FROM employees);

 

UPDATE emps SET salary=30000; --테이블 전체
SELECT * FROM emps;

UPDATE emps SET phone_number='515.123.4566',manager_id=102
WHERE employee_id=100;

--UPDATE (서브쿼리)
UPDATE emps   
SET (job_id, salary , manager_id )=
(SELECT job_id,salary,manager_id FROM emps WHERE employee_id=100)
WHERE employee_id=101;

 

--DELETE 
DELETE FROM emps WHERE employee_id=103;

--DELETE(서브쿼리)
DELETE FROM emps
WHERE department_id=(SELECT department_id FROM depts WHERE department_id=100);

 

CTAS : 사본 테이블 복사 

CTAS
--데이터 없이 구조만 복사 
CREATE TABLE emps_it AS (SELECT * FROM employees WHERE 1=2 );

--데이터 ,구조 복사  WHERE 없으면 default는 TURE 
CREATE TABLE emps_it AS (SELECT * FROM employees WHERE 1=1 );

 

MERGE :한  테이블에 해당하는 데이터가 있다면 UPDATE를 , 없으면 INSER로 처리

oracle 10 버전 이후  DELETE 기능 추가 -- UPDATE 이후에 작성해야 한다


MERGE INTO emps_it a --(머지를 할 타겟 테이블을 작성)a를 기준으로 b를 합침
USING --병합 시킬 데이터 
    (SELECT * FROM employees WHERE job_id='IT_PROG') b --조인 구문
    ON --병합 시킬 데이터의 연결 조건 
    (a.employee_id =b.employee_id) --조인 조건과 유사
WHEN MATCHED THEN -- 조건에 일치할 경우 타겟 테이블에 이렇게 실행해라.
    UPDATE SET 
        a.phone_number=b.phone_number,
        a.hire_date=b.hire_date,
        a.salary=b.salary,
        a.commission_pct=b.commission_pct,
        a.manager_id=b.manager_id,
        a.department_id=b.department_id
        /*
        oracle 10버전 이후 DELETE를 사용 할 수 있는데 ,
        DELETE만 단독으로 사용 불가 -->UPDATE 이후에 DELETE 작성이 가능
        UPDATE된 대상을 DELETE하도록 설계 되어 있기 때문에
        삭제할 대상 컬럼들을 동일한 값으로 일단 UPDATE를 진행하고
        DELETE의 WHERE절에 아까 지정한 동일한 값을 지정해서 삭제합니다.
        */
    DELETE    --어차피 DELETE할거면 UPDATE SET에서 위 처럼하지말고,
            --간단히 값만 변경해서 삭제하는게 더 낫다
        WHERE a.employee_id=b.employee_id 
        
        
        
WHEN NOT MATCHED THEN --조건에 일치하지 않는 경우 타겟 테이블에 실행
    INSERT /*속성(컬럼) : 다 안넣을거면 컬럼명 작성*/  
        VALUES(b.employee_id,b.first_name, b.last_name,b.email,
        b.phone_number,b.hire_date,b.job_id,b.salary,
        b.commission_pct,b.manager_id,b.department_id);

 

트랜잭션(Transaction) : 논리적인 작업의 단위 / 분리되어서 안될 작업의 단위 — ROLLBACK , COMMIT , SAVEPOINT(—oracle만)

 

 

--오토커밋 상태 확인 :default는 OFF
SHOW AUTOCOMMIT;

--오토커밋 온
SET AUTOCOMMIT ON; 

--오토커밋 오프
SET AUTOCOMMIT OFF;

SELECT* FROM emps;

INSERT INTO emps(employee_id,last_name,email,hire_date,job_id)
VALUES(300,'kim','abc@naver.com',sysdate,1800);

--보류중인 모든 데이터 변경사항을 취소(폐기), 직전 커밋 단계로 회귀 (돌아가기)및 트랜잭션 종료
ROLLBACK;

--세이브포인트 생성.
--롤백할 포인트(지점)를 직접 이름을 붙여서 지정.
--ANSI 표준 먼법이 아니기 때문에 그렇게 권장하지는 않음.
SAVEPOINT insert_kim;

INSERT INTO emps(employee_id,last_name,email,hire_date,job_id)
VALUES(301,'park','park@naver.com',sysdate,1800);

ROLLBACK TO SAVEPOINT insert_kim;


--보류중인 모든 데이터 변경사항을 영구적으로 적용하면서 트랜잭션을 종료
--커밋한 이후에는 어떤 방법을 사용하더라도 되돌릴 수 없습니다.
COMMIT;

-- DDL ,DCL: ROLLBACK,COMMIT의 대상이 아님 --기본적으로 AUTOCOMMIT이기 때문
-- DML : ROLLBACK,COMM의 적용 대상임

 

DDL :  CREATE  , ALTER ,DROP , TRUNCATE 

--NUMBER(2) ->정수를 2자리까지 저장할 수 있는 숫자형 타입.
--NUMBER(5,2) ->정수부,실수부를 합친 총 자리수 5자리, 소수점 2자리 표현
--NUMBER -->괄호를 생략할 시 (38,0)으로 자동 지정 됩니다 ==정수 38자리
--VARCHAR2(byte) ->괄호 안에 들어올 문자열의 최대 길이를 지정(4000byte까지)
--DATE -> BC4712년 1월1일~AD 9999년 12월 31일까지 지정가능 
--DATE 시,분,초 지원 가능 (보이지 않을 뿐)


CREATE TABLE dept2(
    dept_no NUMBER(2), --2자리수
    dept_name VARCHAR2(14),--14byte
    loca VARCHAR2(15),  --15byte
    dept_date DATE , --날짜 타입 : DATE 
    dept_bonus NUMBER(10) --10자리수
);

DESC dept2;
SELECT * FROM dept2;

--NUMBER 타입에 들어가는 자리수를 확인
INSERT INTO dept2 VALUES(10,'영업','서울',sysdate,2000000); --OK
INSERT INTO dept2 VALUES(10,'영업','서울',sysdate,20000000000); --XX

--컬럼 추가 
ALTER TABLE dept2
ADD (dept_count NUMBER(3));

--열 이름 변경
 ALTER TABLE dept2
 RENAME COLUMN dept_count TO emp_count;
 
 --열 속성 수정
 ALTER TABLE dept2
 MODIFY (emp_count NUMBER(4));
 
 DESC dept2;
 
 --열 삭제
 ALTER TABLE dept2
 DROP COLUMN emp_count;
 
 --테이블 이름 변경
 ALTER TABLE dept2
 RENAME TO dept3;
 
 DESC dept3;
 
 --테이블 삭제 (구조는 남겨두고 내부 데이터만 모두 삭제)
 TRUNCATE TABLE dept3;
 
 SELECT * FROM dept3;
 
 --테이블과 데이터를 삭제
 DROP TABLE dept3;