테이블(2) 데이터 조작 (추가, 수정, 삭제)

TRUNCATE

모든 데이터를 삭제한다. (구조는 삭제 안됨)

 

TRUNCATE TABLE emp1;

emp1은 테이블 이름이다.

 

 

INSERT

INSERT 후 COMMIT 또는 ROLLBACK으로 트랜잭션을 완료 해야 한다.

COMMIT : 트랜잭션 완료

ROLLBACK : 트랜잭션 취소

 

그러나 데이터 조작언어를 실행 후 DDL(CREATE, ALTER, DROP) 명령을 실행하면 자동 COMMIT 된다.

 

VS Code : 자동으로 커밋 COMMIT 되지 않도록 설정

SET AUTOCOMMIT OFF;

 

 

 

이제 실험을 위해 테이블 하나를 만들어 준다.

CREATE TABLE test1(
    num NUMBER PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    birth DATE NOT NULL,
    memo VARCHAR2(1000)
);

여기서 PRIMARY KEY는 테이블의 고유성을 나타내준다. (제약 조건)

하나의 테이블에는 하나의 기본키만 가질 수 있다.

즉 이 키를 가지고 구분하거나 여러 방면으로 사용할 수 있기 때문에 키는 주는게 좋다.

 

 

INSERT INTO test1 (num,name,birth,memo) VALUES (1234,'name','2000-11-11','this is memo');

INSERT INTO test1 (num,name,birth,memo) VALUES (1234,'name','2000-11-11','this is memo');
COMMIT;
SELECT * FROM test1;

확인을 해보면 정상적으로 데이터가 추가됨을 알 수 있다.

 

 

INSERT INTO test1 (num,name,birth,memo) VALUES (2222,'name2','2021-01-01', '');

작은 따옴표 두개 붙여놓으면 이것은 NULL이다.

 

 

순서 INSERT를 하고 COMMIT을 하면 ROLLBACK해도 데이터 안사라짐

그러나 COMMIT을 안한 데이터들은 모두 ROLLBACK 하면 사라짐

 

 

가상컬럼 (virtual column)

디스크에 저장 x

 

가상 컬럼의 예

 

CREATE TABLE test2(
    hak VARCHAR2(30) PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    kor NUMBER(3) NOT NULL,
    eng NUMBER(3) NOT NULL,
    mat NUMBER(3) NOT NULL,
    tot NUMBER(3) GENERATED ALWAYS AS (kor + eng + mat) VIRTUAL,
    ave NUMBER(3) GENERATED ALWAYS AS ((kor + eng + mat) / 3) VIRTUAL,
    reg_date DATE
);

tot와 ave가 가상컬럼의 예 이다.

 

이제 값을 넣어보자

 

INSERT INTO test2 (hak,name,kor,eng,mat,reg_date) VALUES ('111-111','name',100,90,80,SYSDATE);

결과 TOT랑 AVE가 자동으로 계산되어 나온다.

 

다른 예제

CREATE TABLE test3 (
    eno VARCHAR(30) PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    pay NUMBER(10) NOT NULL,
    tax NUMBER(10) GENERATED ALWAYS AS(
        TRUNC(
        CASE
            WHEN pay >= 3000000 THEN pay * 0.03
            WHEN pay >= 2000000 THEN pay * 0.02
            ELSE 0
        END , -1)
    ) VIRTUAL
);

 

 

 

서브쿼리(subquery)를 이용해서 다중 행 입력하기

 

CREATE TABLE emp1 AS SELECT empNo, name, dept, pos FROM emp WHERE 1 = 0;

--구조만 가져와서 테이블 생성

INSERT INTO emp1 SELECT empNo, name, dept, pos FROM emp WHERE dept = '개발부';

다중 행 입력 완료

 

 

INSERT ALL

두개 이상의 테이블에 여러 행을 추가

 

INSERT ALL
INTO emp1 (empNo, name,dept, pos) VALUES('1111','name111','dept11','pos111')
INTO emp2 (empNo, name,dept, pos) VALUES('2222','name222','dept22','pos222')
SELECT * FROM dual;

한줄만 표시되는 dual을 활용해서 각 테이블 마지막 행에다가 값을 넣을 수 있다.

 

예제

CREATE TABLE emp4 AS SELECT rrn, name, dept, pos FROM emp WHERE 1 = 0;
CREATE TABLE emp5 AS SELECT rrn, name, dept, pos FROM emp WHERE 1 = 0;

INSERT ALL
WHEN MOD(SUBSTR(rrn,8,1),2) = 1 THEN
    INTO emp4 VALUES(rrn,name,dept,pos)
ELSE
    INTO emp5 VALUES(rrn,name,dept,pos)
SELECT * FROM emp;

 

UPDATE

데이터를 수정

 

조심 : 조건을 안주면 모든 데이터가 다 바뀌어 버림 위험함

 

UPDATE SET WHERE로 기억할것

UPDATE emp_score SET com=90, excel=95  WHERE empNo = '1002';

empNo가 1002인 열의 값들을 변경

 

ROLLBACK으로 수정 취소 가능 커밋만 안하면 됨.

 

 

DELETE

데이터 삭제

 

UPDATE와 마찬가지로 조건을 안달면 모든 데이터가 삭제된다.

emp_score.sql

0.00MB

sql 업데이트를 해준다.

@c:\db\emp_score.sql

경로 잘 확인할 것.

 

CREATE TABLE emp_score1 AS SELECT * FROM emp_score;

값 그대로 가져온 테이블 만들고

 

DELETE FROM emp_score1;
--모든 데이터 제거 
--구조는 남는다.

테이블의 모든 값을 삭제할 때는 TRUNCATE가 더 빠름 (자동 커밋)

 

DELETE FROM emp_score1 WHERE empNo = '1001';

--특정 행만 삭제

 

실수로 삭제하거나 수정한 경우 복구하기

INSERT INTO emp (
              SELECT * FROM emp
              AS  OF  TIMESTAMP ( SYSTIMESTAMP - INTERVAL '30' MINUTE )
              WHERE city = '서울'
     );

30분 전의 emp 테이블로 삭제된 데이터 복구

 

 

 

MERGE

데이터 병합하기

 

CREATE TABLE emp1 AS SELECT empNo, name, city, dept, sal FROM emp WHERE city = '인천';

CREATE TABLE emp2 AS SELECT empNo, name, city, dept, sal FROM emp WHERE dept = '개발부';

MERGE 
INTO emp1 e1
USING emp2 e2
ON (e1.empNo = e2.empNo)
WHEN MATCHED THEN
    UPDATE SET e1.sal = e1.sal + e2.sal
WHEN NOT MATCHED THEN
    INSERT (e1.empNo, e1.name, e1.city, e1.dept, e1.sal) VALUES (e2.empNo, e2.name, e2.city, e2.dept, e2.sal);

인천 사는 사람과 부서가 개발부인 사람들의 목록을 합친다.