테이블(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);
다른 예제
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와 마찬가지로 조건을 안달면 모든 데이터가 삭제된다.
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);인천 사는 사람과 부서가 개발부인 사람들의 목록을 합친다.