230706 Oracle SQL 9 [DML문, 데이터 입력, 수정, 삭제, 복구, 저장]

권주희·2023년 7월 6일

복습

SQLP주관식 문제) (select 절의 서브쿼리)

이름, 월급, 직업이 SALESMAN 인 사원들의 토탈월급,
직업이 SALESMAN 인 사원들의 최대월급,
직업이 SALESMAN 인 사원들의 최소월급 을 출력하시오
튜닝전 :

select ename, sal,
               (select sum(sal) from emp where job='SALESMAN' ) 토탈월급,
               (select max(sal) from emp where job='SALESMAN' ) 최대월급,
               (select min(sal) from emp where job='SALESMAN' ) 최소월급,
  from emp;

메인 쿼리의 컬럼이 서브쿼리에 들어가져 있으면? -> 상호관련 서브쿼리
select 절의 서브쿼리를 용어로 하면 ? -> 스칼라 서브쿼리

튜닝후 : ? 스스로 알아내보시오 -_-

다른 학생들이 풀이한것

select ename, sal,
sum(decode(job, 'SALESMAN', sal)) over () 토탈월급,
max(decode(job, 'SALESMAN', sal)) over () 최대월급,
min(decode(job, 'SALESMAN', sal)) over () 최소월급
from emp;

select ename,sal,
      substr(급여,1,4)as 합계, 
      substr(급여,5,4)AS 최대급여,
      substr(급여,9,4)AS 최소월급
from(
     select ename,sal,(select sum(sal)||max(sal)||min(sal) from emp where job='SALESMAN' )AS 급여
FROM EMP
     );

rpad, lpad (pad) 를 써서 공백을 10자리씩 채워주는게 좋을듯 (현업 데이터는 숫자길이가 더 길 가능성이 있어서!)
,

SELECT e.ename, e.sal, s.최대월급, s.최소월급, s.토탈월급
FROM emp e, (
             SELECT max(sal) 최대월급, min(sal) 최소월급, sum(sal) 토탈월급
              FROM emp
              WHERE job = 'SALESMAN') s;

078. 데이터 입력하기 (INSERT) (DML문)

SQL의 종류

Query : select 문의 6가지 절
DML : (Data Manipulation Language)
-> insert, update, delete, merge, select
DDL : (Data Definition Language)
-> create, alter, drop, truncate, rename
DCL : (Data Control Language)
-> grant, revoke
TCL : (Transction Control Language)
-> commit, rollback, savepoint
DDL, DCL이 DBA의 영역이다.

insert 문

예제) 사원 테이블에 아래의 데이터를 입력하시오

사원번호 : 9321
사원이름 : JANE
월급 : 4000
입사일 : 오늘 날짜

 insert into emp (empno, ename, sal, hiredate)
   values (9321, 'JANE', 4000, to_date('2023/07/06','RRRR/MM/DD') );

날짜 넣을때 to_date('지정날짜', 'RRRR/MM/DD')
입력되는 컬럼 외의 다른 값은 null 값으로 입력됨
숫자를 입력할 때는 양쪽에 싱글 쿼테이션 마크를 안둘러줘도 되는데,
문자와 날짜를 입력할 때는 양쪽에 싱클 쿼테이션 마크를 둘러줘야 한다.
emp 다음에 괄호를 열고 컬럼들을 기술했으면 values 다음에 해당하는 값을 기술해줘야 합니다.
만약 아래와 같이 테이블이름 다음 괄호를 열고 컬럼을 안썼으면, values 다음에 다 써줘야합니다. (emp 테이블의 모든 컬럼)

 insert into emp 
  values (9321, 'JANE', 'dba', 7788, to_date('2023/07/06','RRRR/MM/DD'), 4000, 300, 10) ;

문제 431. 이름이 JANE 인 사원의 이름과 입사일을 출력하시오
select ename, hiredate
 from emp
 where ename ='JANE';

문제 432. 부서테이블에 아래의 데이터를 입력하시오
 부서번호 : 50
 부서명 : data team
 부서위치 : seoul
insert into dept (deptno, dname, loc)
 values (50, 'data team', 'seoul' ) ;

rollback

rollback; 하면 commit 하지 않은 작업들 취소된다.
-> 그동안 작업했던 데이터 입력, 수정, 삭제 작업을 취소하는 명령어

문제 433. 아래의 데이터를 우리반 테이블에 입력하시오

학생번호 : 31
학생이름 : 김인호
나이 : 30
생일 : 1994/11/04
주소 : 서울시 강남구 
통신사 : sk

insert into emp17 (empno, ename, age, birth, address, telecom)
 values (31,'김인호', 30, to_date('1994/11/04' ,'RRRR/MM/DD'), '서울시 강남구', 'sk');

null 값을 입력하는 방법 2가지

명시적 입력: 1) insert into emp (empno, ename, sal)
values ( 7234, null, 4000) ;
-> null 이라고 직접 입력하는것
2) insert into emp (empno, ename, sal)
values ( 7456, '', 5000) ;
-> '' 싱글 쿼테이션 마크를 두개 연속 붙여서 입력
(mysql에서는 ''는 공백으로 입력됨)
암시적 입력: insert into emp (empno, ename, sal)
values (3933, 'aaa', 5000) ;
-> 입력하는 컬럼 외에 나머지 컬럼들은 다 null 으로 자동으로 입력됨

문제 434. 이름이 null인 사원들의 이름과 월급을 출력하시오
select ename, sal
 from emp
 where ename is null;

문제 435. 아래와 같이 이름에 공백을 입력하시오
insert into emp(empno,ename,sal)
 values (4956, '   ', 6000) ;

문제 436. 이름이 null이 아닌 사원들의 이름과 월급을 출력하시오
select ename, sal
 from emp
 where ename is not null;

문제 437. 위의 결과를 다시 출력하는데 이름이 공백인 사원도 제외하고 출력하시오
select ename, sal
 from emp
 where ename is not null
  and ename != '   ';

공백 개수를 정확히 모를 수도 있자너
공백 개수를 몇개를 넣던지 관계 없이 출력될 수 있게 하는 방법

trim (공백 제거)

select ename, sal
from emp
where trim(ename) is not null ;

trim 을 사용하게 되면, 공백이 제거가 되면서 공백들어간 데이터가 null로 변경된다.
rtrim : 오른쪽 공백 제거 (실수로 오른쪽에 스페이스바가 눌렸거나 공백이 생겨버린 악성 SQL 제외하는 방법)
좌변을 가공하면 sql 검색성능이 느려진다. (trim 사용 등)
```
select ename, sal
 from emp
 where rtrim(ename)='JACK ';
```
좌변 가공하지 않고 튜닝하는 방법은 나중에 index에서 배울 것

데이터 입력 (insert) 정리

insert 하고 나서 commit 안했으면 바로 rollback 이 가능하다.
insert 할 때 값에 양쪽에 공백이 포함되지 않도록 주의해야한다.

문제 438. (MySQL)
mysql 의 emp 테이블에 아래의 데이터를 입력하시오
사원번호 : 5969
사원이름 : JACK
월급 : 3000
부서번호 : 20
insert into emp(empno,ename,sal,deptno)
 values (5969,'JACK', 3000, 20);

MySQL의 autocommit

rollback; 했더니 삭제되지 않았음!!
MySQL은 자동 commit 기능이 기본값으로 활성화 되어있다.

-> 오라클처럼 쓰고 싶다면 자동 commit 기능 꺼야함

※ 자동커밋기능 켜져있는지 알아보는 방법

select @@autocommit;
숫자가 1로 되어있으면 자동commit 기능 켜져 있는 거임 (0은 꺼진거)

-> MySQL을 사용하기전에는 반드시 autocommit 기능이 활성화 되었는지 확인하고 시작하기

※ 자동커밋기능 끄는방법

set autocommit=FALSE;

지금 접속한 세션 (session) 에서만 유효, 껐다 키면 다시 돌아감!
database level 로 하려면 dba가 설정해주어야함 (db관리 수업때 배움)

문제 439.
아래의 데이터를 emp 테이블에 입력하시오
사원번호 : 5543
사원이름 : JONE
월급 : 3999
입사일 : 2023/07/06
Oracle )
insert into emp (empno, ename, sal, hiredate)
  values (5543, 'JONE', 3999, to_date('2023/07/06','RRRR/MM/DD') );
MySQL )
insert into emp (empno, ename, sal, hiredate)
  values (5543, 'JONE', 3999, date_format('2023/07/06','%Y/%m/%d') );
위와 같이 날짜를 입력해야 db의 날짜형식에 상관없이 날짜를 입력할 수 있습니다.

문제 440. (MySQL)
아래의 null 값을 mySQL의 emp 테이블에 입력하시오
사원번호 : 3945
사원이름 : null값
월급 : 3000
insert into emp(empno, ename, sal)
 values (3945, null, 3000) ;
insert into emp(empno, ename, sal)
values (3945, '', 3000) ;
null이라고 써있지 않고 빈칸으로 되었음
=mySQL은 '' null로 입력되지 않고 공백으로 들어감!!

079. 데이터 수정하기 (UPDATE)

예제. 이름이 SCOTT인 사원의 월급을 5600으로 수정하시오

update  emp
 set sal = 5600
 where ename ='SCOTT';

update 문

update (테이블)
set (업데이트 할 컬럼)
where ___ (선택)

문제 441. 직업이 SALESMAN 인 사원들의 커미션을 9000 으로 수정하시오
update emp
 set comm = 9000
 where job ='SALESMAN';

문제 442.
월급이 2000 이상인 사원들의 부서번호를 10번으로 변경하시오
update emp
 set deptno = 10
 where sal >= 2000;

문제 443.
사원테이블의 월급을 모두 0으로 변경하시오
update emp
 set sal = 0

문제 444.
ALLEN 의 직업을 null 값으로 변경하시오
update emp
 set job = null
 where ename = 'ALLEN';

문제 445. (MySQL)
직업이 SALESMAN 인 사원들의 월급을 8000으로 변경하시오
update emp
 set sal = 8000
 where job='SALESMAN';

mysql 에서 rollback 할때 0 rows affected 으로 뜬다! (걱정 노)

문제 446. (MySQL)
사원테이블에서 커미션이 null인 사원들의 부서번호를 90번으로 수정하시오
update emp
 set deptno = 90
 where comm is null;
null 로 바꿀때는 is 나 is not

▣ [7월 6일 점심시간 문제]
이름, 월급, 부서번호, 직업,
자기가 속한 부서번호의 평균월급,
자기가 속한 직업의 평균월급을 출력하시오

자기가 속한 쓸 때! select 절에 스칼라서브쿼리

select ename, sal, deptno, job,
      ( select avg(sal)
        from emp s
        where e.deptno = s.deptno ) as 부서평균 ,
      ( select avg(sal)
        from emp s
        where e.deptno = s.deptno) as 직업평균
 from emp e;

080. 데이터 삭제하기 (DELETE, TRUNCATE, DROP)

delete/truncate/drop 비교

DML 은 커밋 안했으면 ROLLBACK 이 가능
DDL 은 오라클에서도 자동커밋이 됨

DML -> delete
ddl -> truncate, drop

truncate 가 가장 안되는게 많아서, 신중하게 사용해야함 (저장 구조를 남긴다는 장점이 있음)

예제1. delete 문으로 emp 테이블을 지우시오

delete  from emp;

데이터만 지워진거지, 저장 구조 (테이블, 컬럼) 와 저장 공간 (땅덩어리) 은 그대로 있음

예제2. emp 테이블을 delete로 지우고 commit 하시오

delete from emp;
 commit;

✅ DELETE 'from' 없이도 결과값 출력가능함

테이블 복구하기

15분 안에 복구해야함
예제3. 타임머신 기능으로 복구 시도하라

emp 테이블을 flashback 이 가능한 상태로 변경
```
alter table emp enable row movement
```
현재 시간에서 5분전으로 emp테이블을 되돌린다.

 flashback table emp to timestamp
   ( systimestamp - interval '5' minute) ;

flashback 이 가능한 database 의 설정 시간을 확인하시오
```
show parameter undo
```
undo_retention : 3600 (초) = 60분
(확실하게 60분을 보장하지는 않지만 최대한 60분을 보장을 해줍니다.)

문제 447. 직업이 SALESMAN 인 사원들을 삭제하시오
delete from emp
 where job='SALESMAN';

문제 448. 월급이 2400 이상인 사원들을 지우시오
delete from emp
 where sal >= 2400;

문제 449. (MySQL) autocommit 기능을 해제하는 것을 database 레벨로 지정하시오
답: edit -> preference -> new connection auto commit 을 해제한다

MySQL 에서 autocommit 기능 해제 (database 레벨로 지정)

문제 450. (MySQL)
emp 테이블을 모두 delete 하시오
delete from emp;

truncate

문제 451. emp 테이블을 truncate 하시오
truncate table emp;
truncate 는 rollback 안된다! 구조만 남아있고 데이터는 다 삭제

문제 452. 지금 truncate 한 emp 테이블의 데이터를 flashback 으로 복구하시오
alter table emp enable row movement;
flashback table emp to timestamp
 (systimestamp - interval '5' minute);
truncate 는 flashback 도 불가능

문제 453. emp_backup 으로 emp 테이블을 복구하시오
insert into emp
 select *
  from emp_backup;

※ truncate 명령어를 수행하기 전에 반드시
create table as select문으로 백업을 받고 수행하라!

create table emp_backup2
 as
 select *
 from emp;

문제 454. (MySQL)
MySQL에 있는 emp 테이블을 truncate 하시오 (백업먼저)
truncate table emp;
mysql 에서도 rollback 불가넝~

insert into emp
 select *
 from emp_backup2;

drop

문제 455. emp 테이블을 drop 하시오
drop table emp;
drop은 구조까지 아예 지우는거라서, 모든게 안되지만 flashback 만 된다.

문제 456. 휴지통속에 emp 테이블이 있는지 확인하시오
select *
 from user_recyclebin
 order by droptime desc;
휴지통속에 삭제된 테이블 보는 방법 'from user_recyclebin'
order by 사용해서 정렬도 가능

문제 457. 휴지통속에 있는 emp 테이블을 복구하시오
flashback table emp to before drop;

문제 458. (MySQL)
mysql의 emp 테이블을 drop 하시오 (백업먼저)
mysqp은 orecle 과 같은 flashback 이 없다.

문제 459. backup 받아놓은 emp_backup2 으로 emp 테이블을 생성하시오
 create table emp
  as
 select * from emp_backup2;

문제 450. c##scott 유저가 가지고 있는 테이블 리스트를 확인하시오
select table_name
 from user_tables;

테이블 생성

문제 451. 지금 현재 가지고 있는 테이블 리스트의 정보를 담는 테이블을 다음과 같이 생성하시오
create table table_list_0706 
 as
 select *
 from user_tables;

테이블 drop 하고 복구하기

문제 452.
짝꿍하고 자리를 바꿔서 짝꿍의 테이블중 2개를 drop 하고 다시 자리로 돌아오시오

문제 453.
짝꿍이 drop 한 테이블이 뭔지 확인하시오
select table_name from table_list_0706
minus
select table_name from user_tables;
혹은
select original_name, droptime
from user_recyclebin
order by droptime desc;

문제 454. 짝꿍이 drop 한 테이블을 복구합니다
flashback table emp to before drop;

flashback table dept to before drop;

문제 455. 휴지통을 비웁니다
purge recyclebin;

※ 디스크 공간 확보를 합니다. dba는 항상 db에 여유공간을 모니터링 해야한다. 공간에 data가 꽉차면 db 에 작업이 안됩니다.

081. 데이터 저장 및 취소하기 (COMMIT, ROLLBACK)

commit : 현재까지 작업한 db의 변경작업을 영구히 저장하겠다.
rollback : 현재까지 작업한 db의 변경작업을 취소하겠다.

오라클 같은 계정 두개 열어놓고 해도 commit 안하면 반대편 창껄로 적용 안된다!
commit 하면 둘다 적용된다!

문제 456.
A세션에서 (왼쪽 창 오라클) emp 테이블의 월급을 모두 0으로 변경하시오
update emp
 set sal=0;
commit;

문제 457.
A 세션에서 (왼쪽창 오라클) flashback table 로 emp 테이블을 10분전으로 복구하시오
alter table emp enable row movement;

flashback table emp to timestamp
 (systimestamp - interval '10' minute);
타임머신 기능은 commit 안해도 바로 양쪽에 반영됨 (flashback 되면서 자동 커밋되었다)

암시적 commit

DDL 명령어 수행했을때

-> delete 한다음에 다른 DDL 명령어 수행하면 자동 commit 됨
( create, alter, drop, truncate, rename )
DCL 명령어 수행했을때
( grant, revoke )
정상종료 (exit)
-> 모르고 삭제했는데 exit 누르고 나가버리면,, commit 된거임~!

암시적 rollback

db가 비정상적으로 종료되었을때
-> 컴퓨터 전원 그냥 꺼버렸을때,, 인터넷 끊겼거나?

명령프롬포트에서 C:\Users\itwill>sqlplus "/as sysdba" (오라클 최고 권한으로 접속)

shutdown abort -> ORACLE 인스턴스가 종료되었습니다. (down 시키기)
startup -> 데이터베이스가 열렸습니다. (up 시키기)
강제로 다시 돌려짐..(?)
shutdown abort 같은 경우가 생기면, 암시적으로 자동 rollback 됨

문제 458.
아래의 DML 작업중 DB에 반영된 작업은 무엇인가?
 1) insert into emp...
 2) insert into emp...
 3) update emp ...
 4) update emp ...
 5) commit;
 6) insert into emp...
 7) update emp ...
 8) rollback;
답 : 1-4번까지 db에 반영되었다.
5번 commit까지는 rollback 불가능 6,7번만 취소 (rollback) 되었다.

SAVEPOINT

savepoint : 나중에 rollback 할 지점을 표시하는 것

문제 459.
savepoint 기능을 다음과 같이 테스트 하시오
insert into dept values( 60, 'aaa', 'bbb' ) ;
savepoint a;
insert into dept values ( 70, 'bbb', 'ccc' ) ;
savepoint b;
insert into dept values ( 80, 'ccc', 'ddd' ) ;
savepoint c;

delete from dept;
rollback to b;

select * from dept;
라고 하면 b 지점 전까지 출력됨!

082. 데이터 입력, 수정, 삭제 한번에 하기(MERGE)

merge

merge : insert 와 update 와 delete 를 한번에 수행하는 명령어
upsert 로 많이 알려져 있다. 성능이 조아서 튜닝방법으로 많이 쓰인다.

emp와 dept 테이블을 다시 모두 drop 하고 새로 생성하자 (첫날 데이터로..)
https://cafe.daum.net/oracleoracle/Sdyr/846

문제 460. (복습문제)
emp와 dept를 조인해서 이름과 부서위치를 출력하시오
select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno ;

db모델링 반정규화

예를들면 loc 컬럼을 자주 emp랑 조인해서 쓰는 경우인데,
조인할 때 성능이 너무 느리다면 그냥 loc 컬럼을 emp 테이블에 추가 시켜버린다.
그리고 조인하지 않고 다음과 같이 검색할 수 있게 한다.
select ename, loc
from emp;

문제 461. emp 테이블에 loc 컬럼을 추가하시오 (뒤에서 자세히 배울것, 컬럼추가 작업)
alter table emp
 add loc varchar2 (10);

select ename, loc
 from emp;
loc 칼럼을 10자 이내로 emp 테이블에 추가! 라는 공식

컬럼추가

alter table 테이블명
add 컬럼명 varchar2 (문자길이);

문제 462. 방금 추가한 loc 컬럼의 데이터를 해당 사원의 부서위치로 값을 갱신하시오

merge into emp e               -> 갱신할 테이블
 using dept d                 -> 이용하겠다
 on ( e.deptno = d.deptno )   -> merge 하기위한 조인조건 (연결고리가 되는)
 when matched then            -> 조건이 같다면!
  update set e.loc = d.loc;   -> d.loc 테이블을 e.loc 테이블로 업데이트해라!

 select * from emp;

문제 463. (복습문제)
emp와 salgrade 테이블을 서로 조인해서 이름과 월급과 grade 를 출력하시오
select e.ename, e.sal, s.grade
 from emp e, salgrade s
 where e.sal between s.losal and s.hisal ;

문제 464. (dba만 할 수 있는거)
emp 테이블에 grade 컬럼을 추가 하시오
alter table emp
 add grade number(10);

문제 465. (오늘의 마지막 문제)
emp 테이블에 추가한 grade 컬럼에
해당 사원의 월급의 등급으로 값을 갱신하시오
(merge 문으로 하세요)
merge into emp e
using salgrade s
on ( e.sal between s.losal and s.hisal)
when matched then
update set e.grade = s.grade ;

권주희

열씨미하자

이전 포스트

230705 Oracle SQL 8 [서브쿼리 EXISTS, DB link]

다음 포스트