Day059

MERGE

여러 테이블의 데이터를 합치는 병합을 의미한다.

MERGE INTO test_member_total tmt
USING test_member tm
ON (tmt.no = tm.no)
WHEN MATCHED THEN
    UPDATE SET tmt.name = tm.name
WHEN NOT MATCHED THEN
    INSERT VALUES (tm.no, tm.name);

Constraint(제약조건)

p388, 제약조건 옵션

INDEX(인덱스)

---

2차 프로젝트 관련

1. 깃, 깃허브
   커밋,,, 각자 병합 후 커밋을 해야 이력이 남는다.

2. DB테이블 최소 5개 이상

---

--CREATE TABLE
--INSERT, UPDATE, DELETE
--트랜잭션 (COMMIT, ROLLBACK)

--MERGE

CREATE TABLE test_member
(
    no NUMBER(4),
    name VARCHAR2(16)
);

CREATE TABLE test_member2
(
    no NUMBER(4),
    name VARCHAR2(16)
);

CREATE TABLE test_member_total
(
    no NUMBER(4),
    name VARCHAR2(16)
);

SELECT * FROM test_member;
SELECT * FROM test_member2;
SELECT * FROM test_member_total;

INSERT INTO test_member VALUES (1, '가사람');
INSERT INTO test_member VALUES (2, '나사람');
INSERT INTO test_member VALUES (3, '다사람');

INSERT INTO test_member VALUES (4, '라사람');

INSERT INTO test_member2 VALUES (5, '마사람');
INSERT INTO test_member2 VALUES (6, '바사람');
INSERT INTO test_member2 VALUES (7, '사사람');

INSERT INTO test_member_total VALUES (7, '다른사람');

UPDATE test_member SET name = '다인간'
WHERE no = 3;

UPDATE test_member SET name = '새이름'
WHERE no = 1;

MERGE INTO test_member_total tmt
USING test_member tm
ON (tmt.no = tm.no)
WHEN MATCHED THEN
    UPDATE SET tmt.name = tm.name
WHEN NOT MATCHED THEN
    INSERT VALUES (tm.no, tm.name);

MERGE INTO test_member_total tmt
USING test_member2 tm
ON (tmt.no = tm.no)
WHEN MATCHED THEN
    UPDATE SET tmt.name = tm.name
WHEN NOT MATCHED THEN
INSERT VALUES (tm.no, tm.name);

INSERT INTO test_member_total
SELECT no, name FROM test_member;

SELECT no, name 
FROM test_member tm
WHERE tm.no NOT IN (SELECT no
                    FROM test_member_total);

SELECT *
--DELETE 
FROM test_member_total
WHERE no IN (
    SELECT tmt.no
    FROM test_member_total tmt, test_member tm
    WHERE tmt.no = tm.no
     AND tmt.name != tm.name
);

INSERT INTO test_member_total
SELECT no, name FROM test_member2;

SELECT *
--DELETE 
FROM test_member_total;

--연습문제
--1. dept2 테이블에 아래와 같은 내용으로 새로운 부서 정보를 입력
SELECT * FROM dept2;
INSERT INTO dept2 VALUES (9010, 'temp_10', '1006', 'temp_area');
-- 2. 특정 컬럼만 입력
INSERT INTO dept2(dcode, dname, pdept)
    --VALUES (9020, 'temp_20', '1006');
    VALUES (9020, 'temp_20', '1006',
            (SELECT dcode
             FROM dept2
             WHERE dname = 'Business Department') );
    
--3. 테이블 생성과 동시에 데이터를 입력
SELECT * FROM professor4;    
CREATE TABLE professor4
AS 
SELECT profno, name, pay FROM professor
WHERE profno <= 3000;

--이미 생성된 테이블에 데이터를 입력
INSERT INTO professor4
SELECT profno, name, pay FROM professor
WHERE profno <= 3000;

-- 4.
UPDATE professor
SET bonus = 100
--SELECT * FROM professor
WHERE name = 'Sharon Stone';


SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TEST002';

--테이블 생성과 동시에 기본키 설정
CREATE TABLE test001
(
    no number(4) PRIMARY KEY,
    name varchar2(24)
);
SELECT * FROM test001;

ALTER TABLE test001
MODIFY (name CONSTRAINT test001_name_nn NOT NULL);
INSERT INTO test001 values (3, null);

--제약조건 삭제
ALTER TABLE test001
DROP CONSTRAINT test001_name_nn;

delete
from test001
where no = 2;
--중복되는 값이 이미 들어 있으면 오류가 난다.
ALTER TABLE test001
ADD CONSTRAINT test001_name_uk UNIQUE(name);
INSERT INTO test001 values (3, '다른이름');

--두개의 컬럼을 동시에 기본키로 설정
CREATE TABLE test002
(
    no number(4),
    name varchar2(24),
    id varchar(16),
    CONSTRAINTS test002_pk PRIMARY KEY (no, name)
);

CREATE TABLE temp_member
(
    id NUMBER(4) PRIMARY KEY,
    name VARCHAR2(16)
);

INSERT INTO temp_member VALUES(1, '유승아');
INSERT INTO temp_member VALUES(2, '김성훈');
INSERT INTO temp_member VALUES(3, '조승연');
INSERT INTO temp_member VALUES(4, '서지희');
INSERT INTO temp_member VALUES(5, '이하린');
INSERT INTO temp_member VALUES(6, '이호진');

CREATE TABLE temp_team
(
    id NUMBER(4),
    name VARCHAR2(32),
    member_id NUMBER(4) REFERENCES temp_member(id)
    --데이터를 삭제할 때 참조하는 데이터에 대한 옵션
    --ON DELETE CASCADE
    --ON DELETE SET NULL
);

INSERT INTO temp_team VALUES (1, '1조', 1);
INSERT INTO temp_team VALUES (1, '1조', 2);
--참조하는 테이블에 4라는 값이 없기때문에 오류가 난다.
--INSERT INTO temp_team VALUES (1, '1조', 4);
INSERT INTO temp_team VALUES (1, '1조', 3);

SELECT * FROM temp_member;
SELECT * FROM temp_team;

--멤버 삭제
SELECT *
--DELETE
FROM temp_member
WHERE id = 1;

INSERT INTO temp_team VALUES (2, '2조', 4);
INSERT INTO temp_team VALUES (2, '2조', 5);
INSERT INTO temp_team VALUES (2, '2조', 6);

SELECT *
FROM temp_member
WHERE id IN (SELECT * FROM temp_team
             WHERE id = 2);
             

SELECT * FROM professor
WHERE profno = 4005;
WHERE name = 'Meg Ryan';             
--데이터가 50만개라고 가정.
--문자보다 숫자로 조회하는 경우가 더 빠르다.
--인덱스가 걸려 있는 컬럼으로 조회하는 경우 조회 속도가 더 빠르다.
--테이블 생성할 때 PK를 설정하면 자동으로 인덱스가 생성된다.