9장 데이터 조작 언어 (DML문)

 

■ 9장. 데이터 조작 언어 (DML문)

 

SQL 문의 종류

 

1. 1. QUERY 문   -> SELECT 문의 6가지 절, 조인, 서브쿼리, 함수
   2. DML 문 (Data Manipulation Language)   

 

  insert, update, delete, merge

 

3. 3. DDL 문 (Data Definition Language)

 

 create, alter , drop , truncate, rename

 

4. 4. TCL 문 (Transaction Control Language)

 

 commit, rollback, savepoint

 

5. 5. DLC 문 (Data Control Language)

grant, revoke

 

 

• • 9장 목차

• • insert
  • update
  • delete
  • merge
  • commit 과 rollback
  • 읽기 일관성과 lock
  • select .. for update 문

 

 

 

9.1 insert 문

insert into emp( empno, ename, sal)

values (2914,'JACK',4000);

 

문제260. 아래의 DATA를 입력하시오 !

 

사원번호 3282

사원이름 JANE

월급          4500

입사일      오늘날짜

부서번호 20

 

SELECT ENAME, HIREDATE

FROM EMP

WHERE HIREDATE = SYSDATE;

 

설명: 위에서 입력한 시분초와 현재의 시분초가 틀려서 데이터가 검색이 안되는것이다.

 

문제262. 위의 DATA를 ROLLBACK 하고 오늘 날짜를 다시 입력하는데

 시분초는 입력안되고 오늘 날짜만 입력되게 하시오!

 

 

 

insert into emp(empno, ename, sal, hiredate, deptno)

values (3282, 'JANE', 4500, TO_DATE('2017/03/07','RRRR/MM/DD'), 20);

 

 

문제263. 오늘 입사한 사원의 이름과 입사일을 출력하시오

 

select * from EMP

wheRE HIREDATE= to_DATE('2017/03/07', 'RRRR/MM/DD');

 

 

• • NULL 을 입력하는 방법

• • 명시적으로 입력하는 방법

1. -1. 1. NULL
       2. ''

• • 암시적으로 입력하는 방법

INSERT INTO EMP(EMPNO, ENAME, SAL)

VALUES (2911, NULL, 3400);

 

 

 

INSERT INTO EMP(EMPNO, ENAME, SAL)

VALUES (2945, '', 3500);

 

 

 

INSERT INTO EMP(EMPNO, ENAME, SAL)

VALUES (3924, '     ', 3500);

 

 

 

문제264. 이름이 NULL 이 아닌 사원들의 이름과 월급을 출력하는데 공백문자도 안나오게 하시오

 

SELECT * FROM EMP

WHERE TRIM(ENAME) IS NOT NULL;

 

 

6.2  UPDATE 문

 

UPDATE EMP

SET SAL = 8000

WHERE ENAME = 'SCOTT';

 

문제265. 직업이 SALESMAN 인 사원들의 커미션을 5000으로 변경하시오

 

UPDATE EMP

SET COMM = 5000

WHERE JOB = 'SALESMAN';

 

 

문제266. 월급이 3000이상인 사원들의 부서번호를 30번으로 변경하시오

 

UPDATE EMP

SET DEPTNO = 30

WHERE SAL >= 3000;

 

 

 

■ 9.3 delete 명령어

 

delete from emp

where ename='SCOTT';

 

문제267. 커미션이 null 인 사원들을 삭제 하시오

 

 

 delete from emp

where comm is null;

 

 

■ commit 문

• • 명시적 commit :

 commit 명령어를 직접 수행

 

• • 암시적 commit :

1. 1. 1. 정상 종료
      2. DDL 명령어 수행 (CREATE TABLE)
      3. DCL 명령어 수행

but 비정상 종료 == rollback

 

 

■ 서브쿼리를 사용한 DML 문

 

• • DELETE 문

 

 

 

문제268. SCOTT보다 많은 월급을 받는 사원들을 지우시오

 

DELETE FROM EMP

WHERE SAL > (SELECT SAL

FROM EMP

WHERE ENAME= 'SCOTT');

 

 

 

문제269. ALLEN보다 늦게 입사한 사원들을 삭제 하시오!

 

DELETE FROM EMP

WHERE HIREDATE > (SELECT HIREDATE

FROM EMP

WHERE ENAME = 'ALLEN');

 

 

 

 

문제270. JONES 보다 많은 월급을 받는 사원들의 직업을 SALESMAN으로 변경하시오

 

UPDATE  EMP

SET JOB='SALESMAN'

WHERE SAL > (SELECT SAL

FROM EMP

                WHERE ENAME='JONES');

 

문제271. JONES 보다 많은 월급을 받는 사원들의 직업을 KING의 직업으로 변경하시오

 

UPDATE  EMP

SET JOB= (SELECT JOB

FROM EMP

                WHERE ENAME='KING');

WHERE SAL > (SELECT SAL

FROM EMP

                WHERE ENAME='JONES');

 

 

• • INSERT 문

CREATE TABLE EMP20

AS

SELECT *

FROM EMP2

WHERE 1=2;

====> 조건이 FALSE 이기때문에 DATA는 못 가지고 오고 구조만 가져온다.

 

 

 

 

 

 

문제272. EMP2 테이블의 DATA를 EMP20테이블에 입력하시오

 

INSERT INTO EMP20

SELECT *

 FROM EMP2;

 

문제273. 아까 만들었던 DBLINK를 이용해서 짝꿍의 EMP TABLE을 내 DB에 EMP7으로 생성하시오

create public database link dblink7

connect to scott

identified by tiger

using '192.168.19.26:1521/xe';

 

 

CREATE TABLE EMP7

AS SELECT * FROM EMP WHERE 1=2;

 

 

INSERT INTO EMP7

SELECT *

 FROM EMP@DBLINK7;

 

 

 

(오늘의 마지막 문제2)  아래의 결과를 출력하시오

 

select deptno ,decode(grouping(job)+grouping(deptno),1,'부서토탈:',2,'전체토탈:',job) job,sum(sal)                   

  from emp

group by rollup(deptno,job);

 

 

 

 

 

 

■어제 배웠던 내용

• • 집합 연산자
  • union all, union , intersect, minus
  • 레포팅 함수

ROLLUP , GROUPING SETS , GROUPING , CUBE

• • DML 문장

 INSERT,UPDATE,DELETE, MERGE

 

• • SUBQUERY를 사용한 DML문

 DELETE,UPDATE, INSERT

 

 

문제274. 부서번호가 30번인 사원들의 커미션을

WARD의 커미션으로 변경하시오

 

UPDATE EMP

 SET COMM = (SELECT COMM

         FROM EMP

                WHERE ENAME = 'WARD')

 WHERE DEPTNO = 30;

 

문제275. EMP테이블에 SAL2라는 컬럼을 추가하시오.

 

ALTER TABLE EMP

ADD SAL2 NUMBER(10);

 

 

문제276. emp 테이블의 월급으로 SAL2의 값을 갱신하시오

UPDATE EMP

SET SAL2 = SAL;

 

문제277. EMP테이블에 LOC컬럼을 추가하시오.

ALTER TABLE EMP

ADD LOC VARCHAR2(10);

 

문제278. EMP 테이블에 추가한 LOC컬럼의 데이터를 해당사원의

부서위치로 값을 갱신하시오 (MERGE문을 사용해야한다.)

• • MERGE 문: INSERT, UPDATE, DELETE를 한번에 해버리겠다!

 

MERGE INTO EMP E

USING DEPT D

ON (D.DEPTNO = D.DEPTNO)

WHEN MATCHED THEN

UPDATE SET E.LOC = D.LOC;

속도가 아주 빨라요.

 

 

 

UPDATE EMP E

SET LOC = (SELECT LOC

FROM DETP D

WHERE E.DEPTNO = D.DEPTNO);

 

 

문제279. 이름과 부서위치와 부서번호를 출력하시오.

SELECT E.ENAME, D.DEPTNO, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO;

 

 

문제280. 사원테이블에 DNAME 컬럼을 추가하고 DEPT 테이블에 DNAME 컬럼 데이터로

값을 갱신하시오 (MERGE사용)

 

ALTER TABLE EMP

ADD DNAME VARCHAR2(10);

 

 

MERGE INTO EMP E

USING DEPT D

ON (D.DEPTNO = E.DEPTNO)

WHEN MATCHED THEN

UPDATE SET E.DNAME = D.DNAME;

 

ALTER TABLE EMP

DROP COLUMN LOC;

 

 

ALTER TABLE EMP

DROP COLUMN DNAME;

 

 

 

ALTER TABLE EMP

ADD GRADE NUMBER(10);

 

문제282. 사원이름, 월급, 급여등급(GRADE)을 출력하시오 .

 

SELECT E.ENAME,E.SAL,S.GRADE

FROM EMP E, SALGRADE S

WHRER E.SAL BETWEEN S.LOSAL AND S.HISAL;

 

문제283. 방금 EMP 테이블에 추가한 GRADE 컬럼의 데이터를 해당 사원의 등급으로 값을 갱신하시오.

 

MERGE INTO EMP  E

USING SALGRADE S

ON ( E.SAL BETWEEN LOSAL AND HISAL)

WHEN MATCHED THEN

UPDATE SET E.GRADE = S.GRADE;

 

문제284. 다시EMP 테이블에 GRADE컬럼을 DROP 하시오

 

ALTER TABLE EMP

DROP COLUMN GRADE;

문제285. 부서번호, 부서번호별 토탈 월급을 출력하시오

 

SELECT DEPTNO , SUM(SAL)

FROM EMP

GROUP BY DEPTNO

 

문제286. 부서테이블에 SUMSAL이라는 컬럼을 추가하시오

 

ALTER TABLE DEPT

ADD SUMSAL NUMBER(10);

 

문제287. 지금 추가한 SUMSAL 컬럼에 값을 갱신하는데 해당 부서번호의 토탈 월급으로 값을

갱신하시오

 

 

MERGE INTO DEPT D

USING (SELECT DEPTNO , SUM(SAL) E

FROM EMP

GROUP BY DEPTNO) E

ON ( E.DEPTNO = D.DEPTNO)

WHEN MATCHED THEN

UPDATE SET D.SUMSAL = E.E;

 

GROUP 함수는 컬럼 별칭을 써야한다.

 

 

설명:  1.  USING 절에 테이블 명을 쓰지 않고 서브 쿼리를 사용한다.

            2.  SUM(SAL)과 같은 그룹 함수를 USING 절 안에 사용했으면 컬럼 별칭을 사용해야한다.

 

 

 

문제288. 아래의 테이블을 생성하고 AVGAGE 컬럼의 값을 해당 통신사의

평균나이로 값을 생신하시오

 

CREATE TABLE TELECOM_AVG

(  TELECOM VARCHAR2(10),

AVGAGE NUMBER(10) ) ;

 

INSERT INTO TELECOM_AVG(TELECOM)

SELECT DISTINCT TELECOM

FROM EMP2;

 

 

MERGE INTO TELECOM_AVG A

USING (SELECT TELECOM, AVG(AGE) 평균나이

FROM EMP2

        GROUP BY TELECOM) T

ON ( T.TELECOM = A.TELECOM)

WHEN MATCHED THEN

UPDATE SET A.AVGAGE= T.평균나이;

 

• • 9장 목차

• • insert
  • update
  • delete
  • merge
  • commit 과 rollback
  • 읽기 일관성과 lock
  • select .. for update 문

 

9.6 읽기 일관성과 lock

 

• • 읽기 일관성이란?

 : 내가 접속한 세션에서 내가 변경한 데이터는 나만이 볼 수 있고

다른 세션에서는 볼 수 없다. 내가 커밋을 해야 다른 세션에서도

내가 변경한 데이터를 볼 수 있다.

 

 

커밋해줘야 다른 세션에서도 변경된 게 보이지!

 

 

 

• • LOCK?

: 내가 변경한 데이터를 누군가가 변경하려고 하면 그 누군가는

LOCK에 걸린다 (LOCK을 waiting 하는 상황이 발생한다)

 

 

01s P?-0042 OL> �̡� P?-0042 SQL> unknown un 0��n command command "CIS" "cl�� ��est �� lil estof lil SAL 0 ommit complete. 01_> UPDATE EMP 2 SET SAL = 7000 3 WHERE ENAME = vCOTT| , 7�� '/>

 

 

LOCK은 ROW(행) 단위로 걸린다.

 

 

 

문제289. 직업이 SALESMAN인 사원들의 커미션을8000으로 수정하시오

 

UPDATE EMP

SET COMM = 8000

WHERE JOB = 'SALESMAN';

 

 

문제290. 위와 같은 상태에서 다른 DOS창에서 이름이 TURNER 인 사원의  월급을 8000으로 수정하면

수정이 되겠는가? 앙데여!

 

문제291.(점심시간문제)

개발 (나) , 운영(짝꿍) 인 상태에서

나                                                                                                짝꿍

 

UPDATE EMP@DBLINK7

SET SAL = 0

WHERE ENAME='JONES';                              UPDATE EMP

SET SAL = 8000

WHERE ENAME='JONES';
 

 

 

9.7. SELECT .. FOR UPDATE 문

"LOCK은 SELECT 할 때는 LOCK을 걸지 않는다 SELECT 할 때 LOCK을 거는 문장이 SELECT FOR UPDATE

 

 

 

==> 5초 동안 LOCK을 건다.

 

 

 

문제291. 책 9-46쪽을 보고 직업이 SALESMAN인 사원들의 이름과 월급을 조회하는 쿼리문을 작성하는데

10초 동안 조회하는 행에 락을 걸게 쿼리를 작성하시오

 

SELECT ENAME, SAL

  FROM EMP

WHERE JOB = 'SALESMAN'

FOR UPDATE 10;