19.09.30. (DML 데이터 조작문/INSERT/UPDATE/DELETE/트랜잭션과 TCL/COMMIT/ROLLBACK)

* 테이블 만들기

① CREATE TABLE 테이블명( 컬럼명 타입, 컬럼명 타입, ~~);

② CREATE TABLE 새테이블명 AS SELECT * FROM 기존테이블명 WHERE 조건; (기존 테이블 복사)

③ SQL DEVELOPER에서 직접 오른쪽 마우스 클릭 후, 새테이블을 생성할 수도 있다.

(똑같이 칼럼명 적고 타입도 똑같이 맞춰주기!!)

​

*DML 데이터 조작문*

(1)INSERT : 기존 데이터베이스에 새로운 데이터 삽입

 

단일행 삽입

INSERT INTO 테이블명 (EMPNO, ENAME)

VALUES ('1234','TIGER');

​

INSERT INTO t_student

VALUES ('20190930', '홍길동', '전기전자', 3, 'A','F','170, 50);

​

-전체가 아닌 원하는 곳에만 넣을 수 있는 경우, 정확하게 밝혀주기

INSERT INTO TO_STUDENT(STU_NO, STU_NAME, STU_DEPT, STU_GRADE)

VALUES ('20191001', '가나다', '기계',2);

 

INSERT INTO TO_STUDENT(STU_GENDER, STU_NO, STU_NAME, STU_HEIGHT)

VALUES ('F', '20191002','초콜릿', '170');

​

*컬럼명 생략 가능 (모든 컬럼에 자료를 입력하는 경우,테이블 생성할 때 컬럼 순서 그대로 입력됨)

​

-하나의 테이블에 복수행 삽입 : 서브쿼리 사용, 기존 테이블에 있던 여러 행을 복사해서 다른 테이블에 삽입

INSERT INTO 테이블명 (*복수행 삽입할 땐, VALUES 쓰지 않는다!!!)

SELECT ()

FROM 테이블 명

WHERE 조건;

​

ex)

INSERT INTO t_student

SELECT *

FROM student;

​

-여러 테이블에 복수행 삽입

INSERT ALL

INTO 테이블명 VALUES(컬럼명,컬럼명~)

INTO 테이블명 VALUES(컬럼명,컬럼명~)

SELECT 두개의 테이블 컬럼명 합해서 작성

FROM EMP

WHERE DEPTNO = 20;

​

-조건(WHEN) 추가하여 여러 테이블에 복수행 삽입

INSERT ALL

WHEN 조건 ~~~ THEN

INTO 테이블명 VALUES(컬럼명,컬럼명~)

WHEN 조건 ~~~ THEN

INTO 테이블명 VALUES(컬럼명,컬럼명~)

SELECT 두개의 테이블 컬럼명 합해서 작성

FROM EMP;

​

문1 T_EMP 테이블을 만들고, 'SMITH'를 제외한 모든 사원의 정보를 입력하시오.

​

CREATE TABLE T_EMP AS

SELECT *

FROM EMP

WHERE ENAME != 'SMITH';

​

문2 T_EMP 테이블에 아래 정보를 입력하시오.

​

이름 : SON

월급 : 20000

보너스 : 20%

사원번호 : 777

​

INSERT INTO T_EMP(ENAME,SAL,COMM,EMPNO)

VALUES('SON',20000,4000,777);

​

문3 T_EMP 테이블에 사원번호 9999, 사원이름 SIRENA를 입력하시오.

​

INSERT INTO T_EMP(EMPNO, ENAME)

VALUES(9999,'SIRENA');

​

(2) UPDATE : 테이블의 기존 내용을 수정하기 위해 사용

​

UPDATE 테이블명

SET 컬럼이름 = 변경값

WHERE 조건; (WHERE절을 사용하지 않을 경우 테이블에 있는 모든 행이 수정되기 때문에 신중히 판단할 것)

​

ex) SON의 SAL를 30000으로 변경하시오

​

UPDATE T_EMP

SET SAL = 30000

WHERE ENAME = 'SON';

​

문4 SON의 보너스를 10% 인상하시오. 그리고, 손흥민의 입사일을 오늘 날짜로 변경하시오.

​

UPDATE T_EMP

SET COMM = COMM * 1.1, HIREDATE = SYSDATE

WHERE EMPNO = 777;

​

문5 SON보다 적게 월급을 받는 사원들의 월급을 10% 인상하시오.

​

UPDATE T_EMP

SET SAL = SAL * 1.1

WHERE SAL < (SELECT SAL

FROM T_EMP

WHERE ENAME = 'SON');

​

문6 COMM이 NULL 값인 모든 사원의 COMM을 0으로 변경하시오.

​

UPDATE T_EMP

SET COMM = 0

WHERE COMM IS NULL;

​

(??)문7 월급 등급이 3인 직원의 월급을 15% 인상하여 저장하시오.

UPDATE T_EMP

SET SAL = SAL * 1.15

WHERE EMPNO IN (SELECT EMPNO

FROM SALGRADE, T_EMP

WHERE T_EMP.SAL BETWEEN LOSAL AND HISAL

AND GRADE = 3);

​

SELECT EMPNO

FROM SALGRADE, T_EMP

WHERE T_EMP.SAL BETWEEN LOSAL AND HISAL

AND GRADE = 3

​

​

문8 DALLAS에서 일하는 직원들의 COMM을 100씩 추가하시오.

UPDATE T_EMP

SET COMM = NVL(COMM,0) + 100

WHERE DEPTNO = (DALLS에서 일하는 부서번호);

​

SELECT DEPTNO

FROM DEPT

WHERE LOC = 'DALLS'

​

​

**문9 SCOTT의 상급자를 BLAKE로 변경하시오.

​

UPDATE T_EMP

SET MGR = (SELECT EMPNO

FROM T_EMP

WHERE ENAME = 'BLAKE')

WHERE EMPNO = (SELECT EMPNO

FROM T_EMP

WHERE ENAME = 'SCOTT');

​

SELECT EMPNO

FROM T_EMP

WHERE ENAME = 'BLAKE'

​

SELECT EMPNO

FROM T_EMP

WHERE ENAME = 'SCOTT'

​

(3) DELETE : 테이블에 저장되어 있는 데이터를 삭제할 때 사용

​

DELETE

FROM 테이블명

WHERE ~

​

ex)

DELETE

FROM T_EMP

WHERE EMPNO = 777;

​

문10 사원번호 7902번 사원 정보를 모두 삭제하시오.

​

DELETE

FROM T_EMP

WHERE EMPNO = 7902;

​

문11 평균급여보다 적게 받는 사원을 모두 삭제하시오.

​

DELETE

FROM T_EMP

WHERE SAL < (SELECT AVG(SAL)

FROM T_EMP);

 

​

문12 T_EMP 테이블의 데이터를 모두 삭제하시오.

​

DELETE FROM T_EMP;

​

*트랜잭션과 TCL* (데이터의 일관성을 유지하면서 안정적으로 데이터를 복구시키기 위한 목적)

트랜잭션 : 사용자에 의해 실행된 SQL문의 집합 (DML 명령어 = SELECT, INSERT,UPDATE,DELETE)

여러개의 명령어의 집합이 정상적으로 처리되면 정상 종료하고 (COMMIT),

여러개의 명력어의 집합이 하나라도 잘못되었다면 전체를 취소한다 (ROLLBACK).

​

(4)COMMIT : 모든 작업들을 정상적으로 처리하겠다고 확정하는 명령어

: 변경된 내용 모두 영구저장

: COMMIT 명령어를 수행하게 되면 하나의 트랜잭션 과정을 종료하는 것

​

(5)ROLLBACK : DELETE로 삭제했던 데이터 되돌리기

: ROLLBACK 명령어를 수행하게 되면 하나의 트랜잭션 과정을 종료하는 것

: 이전 COMMIT한 곳까지 복구

​

(6)MERGE : 데이터 삽입, 수정, 삭제 작업을 한번에 할 수 있다.

데이터를 겹치거나 안겹치는 경우 사용~

USING 절 테이블에 해당 행이 존재하는 경우 UPDATE(DELETE포함 할 수 있다)를 수행하고,

해당 행이 존재하지 않을 경우 INSERT를 수행한다.

대상 테이블에 대한 UPDATE / INSERT 조건은 ON절에 의해 결정된다.

​

(T_EMP 데이터 중 EMP 테이블 데이터가 있는 경우 사원번호 7369 사원의 이름을 KIM으로 변경하고,

데이터가 없는 경우 아래 값이 입력된다.)

MERGE INTO T_EMP

USING EMP

ON (EMP.EMPNO = T_EMP.EMPNO)

WHEN MATCHED THEN

UPDATE SET T_EMP.ENAME = 'KIM'

WHERE EMPNO = 7369

WHEN NOT MATCHED THEN

INSERT VALUES ('777','SON','','','','','','');

​

MERGE INTO T_EMP

USING EMP

ON (EMP.EMPNO = T_EMP.EMPNO)

WHEN MATCHED THEN

UPDATE SET T_EMP.COMM = 100

WHERE ENAME = 'CLERK'

WHEN NOT MATCHED THEN

INSERT VALUES ('777','SON','','','','','','');

​

문13 T_EMP에서 다음 정보를 입력하시오.

사원번호 : 1112

이름 : PARK

월급 : 2000

데이터가 존재하면 UPDATE하고 데이터가 존재하지 않으면 INSERT하시오.

CREATE TABLE S_EMP

AS SELECT *

FROM EMP

WHERE EMPNO = 7902;

​

MERGE INTO T_EMP

USING S_EMP

ON (S_EMP.EMPNO = T_EMP.EMPNO)

WHEN MATCHED THEN

UPDATE SET ENAME = 'PARK', SAL = 2000

WHEN NOT MATCHED THEN

INSERT VALUES (1112,'PARK','','','',2000,'','');

​

***문14 STUDENT 테이블에서 컴퓨터정보 1,2학년이면 몸무게를 10KG 씩 증가시키고,

컴퓨터정보 3학년 학생이면 몸무게 20K을 증가시켜 T_STUDENT 테이블에 저장하시오.

CREATE TABLE T_STUDENT

AS SELECT * FROM STUDENT;

​

MERGE INTO T_STUDENT A

USING (SELECT * FROM STUDENT WHERE STU_DEPT = '컴퓨터정보') B

ON (A.STU_NO = B.STU_NO)

WHEN MATCHED THEN

UPDATE SET A.STU_WEIGHT = B. STU_WEIGHT +

DECODE(B.STU_GRADE,

1,10,

2,10,

3,20)

WHEN NOT MATCHED THEN

INSERT (A.STU_NAME, A.STU_DEPT, A.STU_GRADE,

A.STU_CLASS, A.STU_GENDER, A.STU_HEIGHT,

A.STU_WEIGHT)

VALUES (B.STU_NAME, B.STU_DEPT, B.STU_GRADE,

B.STU_CLASS, B.STU_GENDER, STU_HEIGHT,

B.STU_WEIGHT + DECODE(B.STU_GRADE,

1,10,

2,10,

3,20));

​

​

문15 외국인 학생 테이블(FORE_STUDENT) 작성시에 오류가 발생하였다.

외국인 학생 MARK를 STUDENT 테이블에 추가합니다.

외국인 학생들의 클래스는 모두 A반으로 변경하고 추가된 MARK를 FORE_STUDENT에 저장하시오.

​

<오류 발생 명령문>

CREATE TABLE fore_student

as select * from student

where stu_grade = 1;

<MARK 정보> -> STUDENT 테이블에 추가 (INSERT)

STU_NO STU_NAME STU_GRADE

1111 MARK 1학년

​

create table fore_student

as select * from student;

​

merge into fore_student f

using (select * from student where stu_grade = 1) s

on (f.stu_no = s.stu_no)

when matched then

update set stu_class = 'A'

when not matched then

insert (f.stu_no,f.stu_name,f.stu_dept,f.stu_grade,f.stu_class,

f.stu_gender,f.stu_height,f.stu_weight)

values (s.stu_no,s.stu_name,s.stu_dept,s.stu_grade,s.stu_class,

s.stu_gender,s.stu_height,s.stu_weight);

​

insert into fore_student (stu_no, stu_name,stu_grade)

values(1111, 'MARK', 1);

​