day8

📖 MERGE : 병합

데이터가 없으면 저장하고, 있으면 업데이트 해라!

MERGE INTO 테이블명(최종저장할 대상)  --전체 직원
USING 가져올데이터테이블명           --계열사 직원
ON ( 합치는 조건 )
WHEN MATCHED THEN --부합하는게 있다 -> 수정
    UPDATE SET 컬럼명 = 값
    WHERE
WHEN NOT MATCHED THEN --부합하는게 없다 -> 삽입
    INSERT VALUES ( 컬럼명 );

emp_b1 (계열사1)
emp_b2 (계열사2)
emp_all (최종 집계 전체 직원 목록)

🎈 테이블 생성 - CREATE

CREATE TABLE emp_b1
(
    empno NUMBER(3),
    name VARCHAR2(16),
    pay NUMBER(10)
);

CREATE TABLE emp_b2
(
    empno NUMBER(3),
    name VARCHAR2(16),
    pay NUMBER(10)
);

CREATE TABLE emp_all
(
    empno NUMBER(3),
    name VARCHAR2(16),
    type NUMBER(1),    --1 계열사, 2 계열사
    pay NUMBER(10)
);

🎈 테이블 삽입 - INSERT

--emp_b1
INSERT INTO emp_b1 VALUES (1, '1이름', 100);
INSERT INTO emp_b1 VALUES (2, '2이름', 200);
INSERT INTO emp_b1 VALUES (3, '3이름', 400);

--emp_b2
INSERT INTO emp_b2 VALUES (4, '4이름', 400);
INSERT INTO emp_b2 VALUES (5, '5이름', 500);
INSERT INTO emp_b2 VALUES (6, '6이름', 1000);

🎈 emb_b1 과 emp_b2를 emp_all에 병합하기!

--emp_b1 병합
    MERGE INTO emp_all ea --전체 직원
    USING emp_b1 e1       --계열사 직원
    ON ( ea.empno = e1.empno ) --합치는 조건
    WHEN MATCHED THEN --부합하는게 있다 -> 수정
        UPDATE SET ea.pay = e1.pay
        --WHERE
    WHEN NOT MATCHED THEN --부합하는게 없다 -> 삽입
        INSERT VALUES ( e1.empno, e1.name, 1, e1.pay );

--emp_b2 병합
    MERGE INTO emp_all ea --전체 직원
    USING emp_b2 e2       --계열사 직원
    ON ( ea.empno = e2.empno ) --합치는 조건
    WHEN MATCHED THEN --부합하는게 있다 -> 수정
        UPDATE SET ea.pay = e2.pay
        --WHERE
    WHEN NOT MATCHED THEN --부합하는게 없다 -> 삽입
        INSERT VALUES ( e2.empno, e2.name, 2, e2.pay );

💻

📖 키 제약조건

NOT NULL : 널 안됨. 값이 무조건 존재 / NULL X
UNIQUE : 유일해야한다. 중복값 저장 XXXX / 중복 X
CHECT : 해당 조건에 맞는 값만 허용 ex) 점수 NUMBER(3) 999 0~100

PRIMARY KEY : PK 기본키설정! (NOT NULL + UNIQUE), 테이블에 1개만 설정!
FOREIGN KEY : FK 외래키설정! 다른 테이블 컬럼을 참조

📌 test001 테이블 생성 -> UNIQUE, NOT NULL

CREATE TABLE test001
(
    no NUMBER(3) UNIQUE,
    id VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL
);

--데이터 삽입
INSERT INTO test001
VALUES (1, 'myid1', null); --생성할때 name NOT NULL! -> null 저장불가

INSERT INTO test001
VALUES (1, 'myid1', 'name111');

INSERT INTO test001
VALUES (1, 'myid1', 'name111'); --no 1 이 unique 근데 위에 이미 만들어진 상태에서 1을 또 넣으려고 하면 -> 고유 무결성 제약조건 위배

INSERT INTO test001
VALUES (2, 'myid1', 'name111');

INSERT INTO test001
VALUES (null, 'myid1', 'name111');

==> name은 NOT NULL 로 생성했기 때문에 null로 저장X
==> no는 UNIQUE 그렇기 때문에 1이 이미 삽입 되어있다면 다음에 1을 다시 삽입하면 오류!

💻

📌 test002 테이블 생성 -> PRIMARY KEY, UNIQUE, NOT NULL

CREATE TABLE test002
(
    no NUMBER(3) PRIMARY KEY, --테이블에는 하나의 기본키만!
    id VARCHAR2(30) UNIQUE NOT NULL,
    name VARCHAR2(30) NOT NULL --동명이인이 있을수도 있으니까 기본키X
);

INSERT INTO test002
VALUES (1, 'myid1', null); --생성할때 name NOT NULL! -> null 저장불가

INSERT INTO test002
VALUES (1, 'myid1', 'name111'); --기본키 중복X

INSERT INTO test002
VALUES (null, 'myid1', 'name111'); --기본키 NULLX, id 중복X

INSERT INTO test002
VALUES (3, 'myid222', 'name111');

==> name은 NOT NULL 로 생성했기 때문에 null로 저장X
==> no는 기본키! 그래서 중복x
==> no는 기본키! 그래서 nullx , id는 UNIQUE라 중복X

📌 test003 테이블 생성 -> PRIMARY KEY, UNIQUE, NOT NULL, CHECK

CREATE TABLE test003
(
    no NUMBER(3) CONSTRAINT test003_no_pk PRIMARY KEY, --SYS_C007501 이런식으로 뜨는게 아니라 test003_no_pk 이렇게 뜸
    id VARCHAR2(30) 
        CONSTRAINT test003_id_uk UNIQUE 
        CONSTRAINT test003_id_nn NOT NULL,
    name VARCHAR2(30) 
        CONSTRAINT test003_name_nn NOT NULL,
    score NUMBER(3) -- 0~100
        CONSTRAINT test003_score_ck CHECK (score BETWEEN 0 AND 100)
);

SELECT * FROM test003;

INSERT INTO test003
VALUES (1, 'id1', 'name1', 50); --두번 넣을수는 없어 기본키는 중복X

INSERT INTO test003
VALUES (2, 'id2', 'name1', 50);

INSERT INTO test003
VALUES (3, 'id3', 'name1', 100); 

==> CONSTRAINT test003_no_pk : 얘로 지정 안해주면 CONSTRAINT name은 SYS_C007501 이런식으로 저장되는게 아니라 test003_no_pk로 저장된다!

==> 그리고 CHECK 로 score에 들어오는 값의 범위 지정!

📌 test004 테이블 생성 -> PRIMARY KEY, UNIQUE, NOT NULL / 여러개의 기본키 지정!

CREATE TABLE test004
(
    no NUMBER(3) PRIMARY KEY, --테이블에는 하나의 기본키만! --no도 PK
    id VARCHAR2(30) PRIMARY KEY,                       --id도 PK
    name VARCHAR2(30) NOT NULL --동명이인이 있을수도 있으니까 기본키X
);

--두개 조합 PK VS 테이블에는 한개의 PK만 설정가능!


--no + id 결합해서 PK 설정 하고싶으면?
CREATE TABLE test004
(
    no NUMBER(3),     --no도 PK
    id VARCHAR2(30),  --id도 PK                     
    name VARCHAR2(30) NOT NULL, --동명이인이 있을수도 있으니까 기본키X
    
    --no + id 결합해서 PK 설정
    CONSTRAINT test004_pk PRIMARY KEY (no, id)
);

==> 테이블에는 하나의 기본키만 지정할 수 있다!
==> 근데 여러 컬럼을 결합해서 기본키로 지정하고 싶다면?
CONSTRAINT test004_pk PRIMARY KEY (no, id) 이런식으로 밑에서 따로 이름 test004_pk 설정해주고 no, id 두개를 동시에 기본키로 지정해준다~!

📌 외래키 설정 -> 좀번거로워서 잘 사용안한다!

--mystu myprof
CREATE TABLE mystu
(
    stuno NUMBER(4) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    profno NUMBER(4) CONSTRAINT mystu_fk REFERENCES myprof(profno)
--  profno NUMBER(4) CONSTRAINT mystu_fk REFERENCES myprof(profno) ON DELETE CASCADE; -> 참조값이 지워질때 같이 지워지는 => 연쇄삭제
--  profno NUMBER(4) CONSTRAINT mystu_fk REFERENCES myprof(profno) ON DELETE SET NULL; -> 참조값이 지워질때 NULL로 세팅 => NULL로 바뀜
);

CREATE TABLE myprof
(
    profno NUMBER(4) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL
);

INSERT INTO myprof VALUES (1, '교수1');
INSERT INTO myprof VALUES (2, '교수2');
INSERT INTO myprof VALUES (3, '교수3');

SELECT * FROM myprof;
COMMIT;

INSERT INTO mystu VALUES (101, '학생1', null);
INSERT INTO mystu VALUES (102, '학생2', 1);
INSERT INTO mystu VALUES (103, '학생3', 3);
INSERT INTO mystu VALUES (104, '학생4', 8); --myprof prof 1,2,3만! 외래키 설정한 컬럼에 존재하는 값만 추가 가능!
INSERT INTO mystu VALUES (104, '학생4', 2); --myprof prof에 1,2,3있으니까 1,2,3만 들어갈수있어!

SELECT * FROM mystu;
COMMIT;

SELECT *
FROM mystu s, myprof p
WHERE s.profno = p.profno(+); --stu에 있는게 다나오는 아우터조인

--3교수가 퇴사 -> 3교수 데이터를 삭제
DELETE FROM myprof 
WHERE profno = 3; --외부에서 참조하고 있는 데이터가 있으면, 삭제 불가! -> 지우는 순간 mystu prof 3이 조건 위배!
                  --그래서 mystu prof 3을 아예 없애야 myprof prof 3을 지울 수 있음
                  --근데 생성할때 CASCADE 걸어두면 myprof에서 지우면 mystu에서도 싹 지워짐!

DELETE FROM mystu
WHERE profno = 3; --그래서 mystu prof 3을 아예 없애야 myprof prof 3을 지울 수 있음

==> myprof 테이블의 profno를 외래키로 사용하고 싶으면 profno NUMBER(4) CONSTRAINT mystu_fk REFERENCES myprof(profno) 로 참조하는 외래키로 지정!
=> 근데 조건을 더 걸수 있다! ON DELETE CASCADE;는 참조값을 지울때 같이 지워지는 연쇄삭제효과, ON DELETE SET NULL;는 참조값이 지워질때 mystu는 null로 바뀌는 효과

==> INSERT INTO mystu VALUES (104, '학생4', 8); 를 할 수없다! myprof prof에는 1,2,3만 존재! 외래키 설정한 컬럼에 존재하는 값만 추가 가능!

==> myprof에서 prof 3을 삭제 하고 싶다면 외부에서 참조하고 있는 데이터가 없어야 삭제가 가능하다!
=> 그래서 mystu에서 prof 3을 아예 다없애야 myprof에서 prof 3을 삭제 가능
🚨 근데 !!!!! 생성하는 과정에서 CASCADE를 걸어놨다면 myprof에서 지워도 mystu에서도 싹 지워짐!