[Oracle SQL]13일차_23.07.06

망구씨·2023년 7월 6일
DROPFLASHBACKINSERTRollbackSavepointTRUNCATEcommitdeletemergeupdate
0

Oracle SQL

목록 보기
13/21
post-thumbnail

오늘의 TIL

  1. 데이터 입력하기(INSERT)
  2. 데이터 수정하기(UPDATE)
  3. 데이터 삭제하기 (DELETE, TRUNCATE, DROP)
  4. 타임머신 기능 ! FLASHBACK
  5. 휴지통 확인하기, 복구
  6. 테이블 리스트의 정보를 담는 테이블 생성
  7. 데이터 저장 및 취소하기(COMMIT, ROLLBACK)
  8. Savepoint
  9. 데이터 입력, 수정, 삭제 한번에 하기 (MERGE)

복습

이름, 월급, 직업이 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;

위 코드 튜닝 후의 코드를 스스로 생각해보기!

데이터 입력하기(INSERT)

SQL종류 ?

  1. Query : select 문의 6가지 절
  2. DML (Data Mainpulation Language) : insert, update, delete, merge, select
  3. DDL (Data Ddfinition Language) : create, alter, drop, truncate, rename
  4. DCL (Data Control Language) : grant, revoke
  5. TCL ( Transaction Control Language) : commit, rollback, savepoint

예제. 사원 테이블에 아래의 데이터 입력

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

insert into emp(empno, ename, sal, hiredate)
  values(9321, 'JANE', 4000, to_date('2023/07/06', 'RRRR/MM/DD'));
  • emp 다음에 괄호를 열고 컬럼들을 기술했으면, values 다음에 해당하는 값을 기술해줘야 합니다.
    만약 아래와같이 괄호를 열고 컬럼을 안썼으면, values 다음에 다 써줘야 합니다. 
insert into emp // 여기 괄호 없으면 values 안에 컬럼순서 맞추어 다쓰기
  values(9321, 'JANE', 4000, to_date('2023/07/06', 'RRRR/MM/DD'));

※ 숫자를 입력할 때는 양쪽에 싱글 쿼테이션 마크를 안둘러줘도 되는데, 문자와 날짜를 입력할 때는 양쪽에 싱글쿼테이션 마크를 둘러주어야 한다!

문제 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; 하면, 그동안 작업했던 데이터 입력, 수정, 삭제 작업을 취소하는 명령어!
    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');

  • 날짜를 넣을때는 to_date함수를 써서 해주기! 안해도 상관은 없지만 .. 우리나라에서만 상관없음

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

1. 명시적 입력 

insert into emp(empno, ename, sal)
  values(7234, null, 4000);

------------------------------------
insert into emp(empno, ename, sal)
  values(7234, '' , 4000); 

  // 1. null
  // 2. '' <---- 싱글쿼테이션 마크를 딱 붙여서 2개 쓴 것

2. 암시적 입력 : 입력하는 컬럼 외에 나머지 컬럼들은 모두 null이 자동으로 입력된다.
ex) 아래의 empno, ename, sal 이외는 모두 null값으로 입력!

insert into emp(empno, ename, sal)
  values(3933, 'aaa', 5000);

문제 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 not like '% %';

답

select ename, sal
  from emp
  where trim(ename) is not null;
  • trim을 사용하면, 공백이 제거가 되면서 null로 변경된다. null값은 제외해서 출력됨
    이외에 rtrim도 많이쓰인다.
select ename, sal
  from emp
  where rtrim(ename) = 'SCOTT';
                좌변  =  우변
  • = 기준으로 좌변, 우변인데 위와같이 좌변을 가공하면 sql검색성능이 느려진다.
  • 가공이란? 함수를 둘러주었다. ename -> rtrim(ename)
  • 검색성능이 느려지지만 공백이 있으면 ename으로만 출력했을 때 데이터 출력이 안된다. 애초에 입력받을 때 공백이 입력되지 않게끔 해야함
  • 위 SQL 튜닝방법은 나중에 index 에서 배운다!!!

insert 정리

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

문제 438. (mySQL) mySQL의 emp테이블에 아래의 데이터 입력하기

사원번호 : 5969
사원이름 : JACK
월급 : 3000

insert into emp (empno, ename, sal)
value(5969, 'JACK', 3000);
  • rollback이 안된다!!! -> mySQL은 자동커밋기능이 기본값으로 활성화 되어있다. 
    SELECT @@autocommit;
SHOW VARIABLES LIKE 'autocommit';
  • 자동커밋기능이 활성화 되어있는지 확인할 수 있는 명령어!
    ※ mySQL을 사용하기 전에는 반드시 이게 활성화 되어있는지 확인하고 시작하기.
    set autocommit=false;
  • 위 명령어로 끄기 ! 숫자가 0이면 꺼진거고 1이면 활성화가 되어있는 것!
  • mySQL을 껐다가 켜면 다시 활성화가 되어있는데(세션레벨), 매번 이것을 설정하고 작업을 하기가 번거롭다 하면, DBA가 권한으로 아예 꺼야한다. 우리가 database level로 하면 됨 (db관리수업에서 배울 예정)
  • 세션레벨이란, 지금 접속한 세션에서만 유효하다.

문제 439. (mySQL)아래의 데이터를 EMP테이블에 입력

사원번호: 5543
사원이름: JONE
월급: 3999
입사일: 오늘날짜 (2023/07/06)

insert into emp (empno, ename, sal, hiredate)
 value (5543, 'JONE', 3999, date_format('2023/07/06', '%Y/%m/%d'));
  • muSQL에서는 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(3946, '' , 3000);
  • 오라클에서는 두개가 모두 null이라고 들어갔는데, mySQL에서는 위코드는 NULL로 들어가는데 두번째꺼는 NULL로 들어가지 않았다. 그냥 공백으로 들어감.

데이터 수정하기(UPDATE)

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

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

문법
update + 테이블명
set + 어떻게 바꿀지
where + 어떤데이터 바꿀지

문제 441. 직업이 SALESMAN인 사원들의 커미션을 9000으로 수정하세요

update emp
   set sal = 9000
   where JOB ='SALESMAN';

문제 442. 월급이 2000 이상인 사원들의 부서번호를 10번으로 변경

update emp
   set deptno = 10
   where sal >= 2000;

문제 443. 사원 테이블의 월급을 모두 0으로 변경하시오!

update emp
   set  sal = 0;

  • 여기서 주의할 점은, 
update emp
   set  sal = 0;
   where ename = 'SCOTT';
  • 위처럼 세미콜론을 잘못쓰면 스캇말고 모든사원의 월급이 변경된다.

문제 444. ALLEN의 직업을 null로 변경 -> 여기서 나는 is null이라고 했는데 아님!

update emp
   set job  = null
   where ename = 'ALLEN';

문제 445.(mysql) 직업이 salesman인 사원들의 월급을 8000으로 변경

update emp
   set sal  = 8000
   where job = 'SALESMAN';

문제 446.(mysql) 사원테이블에서 커미션이 null인 사원들의 부서번호를 90번으로 수정
-> is null로 써야함!!!!!

update emp
   set deptno  = 90
   where comm is null;

점심시간문제

select ename, sal, deptno, job, 
    (select round(avg(sal))
     from emp s
     where s.deptno = e.deptno) 부서번호평균,
     (select round(avg(sal))
     from emp s
     where s.job = e.job) 직업평균
 from emp e
 order by deptno;

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

  • DML : ROLLBACK 가능 -> DELETE
  • DDL : 자동커밋된다. -> TRUNCATE, DROP

    플래쉬백(타임머신 기능)은 DELETE, DROP만 가능.

    1. DELETE : 데이터 삭제, 저장공간 남김, 저장구조 남김, 취소여부 가능, 플래쉬백 가능
    2. TRUNCATE :
    3. DROP:

예제. delete문으로 emp 테이블 지우기

delete from emp;

예제2. emp 테이블 delete로 지우고 commit;

delete from emp;

commit;
  • 이건 rollback 안됨

예제3. 타임머신 기능으로 복구를 시도

  1. emp테이블을 flashback이 가능한 상태로 구성
alter table emp enable row movement;
  1. 현재시간에서 10분전으로 emp 테이블을 되돌립니다.
flashback table emp to timestamp
  ( systimestamp - interval '10' minute );
  • 골든타임 15분임! 15분 지나면 복구 불가능.
    -> 그사이
  1. flashback이 가능한 database의 설정 시간을 확인하세요.
    900초니까 복구가능 시간은 15분! -> 컴퓨터마다 다른듯.. 강사님꺼는 3600!
show parameter undo

문제 447. 직업이 SALESMAN인 사원들을 삭제

delete 
 from emp
 where job = 'SALESMAN';

문제 448. 월급이 2400이상인 사원들 지우기

delete 
 from emp
 where sal >= 2400;

문제 449. (mySQL) 자동 커밋기능을 해제하는것을 database 레벨로 지정하시오

edit > preference > sql execution 들어가서 저기 체크 풀기

문제 450. mySQL에서 emp 테이블 삭제하기

delete from emp;

문제 451. emp 테이블을 truncate 하시오

truncate table emp;

  • 롤백 불가능, 구조는 남아있음.

문제 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;

  select * from emp;

※ truncate 명령어 수행전에 반드시 create table as select 문으로 백업을 받도록 합시다.

create table emp_backup2
as
  select * from emp;

문제 454. (mySQL) mySQL에 있는 emp 테이블을 truncate 하세요

create table emp_backup
as
  select * from emp; // truncate 전에 백업 테이블 생성

truncate table emp; // truncate 실행

복구 ↓

insert into emp 
  select *
  from emp_backup;

문제 455. emp테이블을 drop 하시오!

drop table emp;

문제 456. 휴지통속에 emp 테이블이 있는지 확인하기

select *
 from user_recyclebin;

문제 457. 휴지통속에 있는 emp 테이블 복구하기

flashback table emp to before drop;

문제 458. (mySQL) mySQL의 EMP 테이블 DROP하기

create table emp_backup
as 
  select * from emp;

drop table emp;
  • mySQL은 오라클과 같은 flashback이 없다!!!

문제 459. (mySQL)백업받은 emp_backup테이블로 emp 테이블 생성하기

create table emp
 as
 select * from emp_backup;
  • insert가 아니고 create 하는 이유는 구조자체가 날라가서 인듯..!

문제 460. SCOTT유저가 가지고 있는 테이블 리스트를 확인하기

select table_name
 from user_tables;

문제 451. 지금 현재 가지고 있는 테이블 리스트의 정보를 담는 테이블을 다음과 같이 생성하시오

create table table_list_0706
as
 select *
 from user_tables;
  • 이렇게 테이블 리스트를 가지고있으면, 누가 내 테이블을 drop했다고 하더라도, 괜찮다.

문제 452. 짝꿍이랑 자리를 바꾸어서, 짝꿍의 테이블중 2개를 drop 하고 다시 자리로 돌아오세요!

문제 453. 짝꿍이 drop한 테이블이 뭔지 확인하세요!

minus로 찾는 방법 ↓

select table_name from table_list_0706
minus
select table_name from user_tables;

휴지통 보는 방법 ↓

select *
 from user_recyclebin;
 order by droptime desc; // 언제지운건지 시간도 나옴

문제 454. 짝꿍이 drop한 테이블 복구

dept, price가 지워져있었고, 복구!

flashback table dept to before drop; 
flashback table price to before drop;

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

purge recyclebin; // 비우고

select *
 from user_recyclebin; // 확인
  • 디스크 공간확보!

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

  1. commit: 현재까지 작업한 db에 변경작업을 영구히 저장
  2. rollback: 현재까지 작업한 db에 변경작업을 취소


왼쪽에서는 delete하고 commit하지 않은상태로 select* from dept;하니까 안보이고,
오른쪽에서는 아직 보인다. 그렇다면 왼쪽에서 commit;을 하면, 오른쪽에서도 데이터가 보이지 않는다.

다시 dept를 백업했지만 오른쪽 화면에서는 여전히 보이지 않는다. -> commit;을 하지 않아서 !
commit;하면 보임

다른세션에서도 볼 수 있도록 하는것이 commit;

문제 456. A세션에서 emp 테이블의 월급을 모두 0으로 변경하세요!

  • commit 해서 오른쪽 세션도 모두 월급이 0로 변했다.

문제 457. A 세션에서 flashback table로 emp 테이블을 10분전으로 복구

alter table emp enable row movement;

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

  • flashback되면서 자동commit이 됨!!! -> 따로 커밋할 필요는 없지만 기억하기.

암시적 commit

  1. DDL 명령어 수행했을 때

    • delete 명령어 이후에 create(DDL) 을 수행해서 , 이후에 ROLLBACK 해도 다시 데이터 복구가 안된다.
      create, alter, drop, truncate, rename

  2. DCL 명령어 수행했을 때 grant, revoke

  3. 정상종료(exit)

    ex) 만약에 도스창에서 delete from emp; 하고 놀래서 exit 하고 창 나오면 이거 commit이 되어버린다. 롤백도 안됨!

암시적 rollback

DB가 비정상적으로 종료되었을 때!

이렇게 먼저 수행했고, select * from salgrade; 했을 때 데이터가 지워진 것을 확인했다.
(commit;하지 않았음) 이후 아래 수행.

1. 오라클 최고 권한자로 접속!

"/as sysdba"

2. 진짜 급할 때 강제종료(??)

shutdown abort

3. 다시 데려오기

startup


다시 sql디벨로퍼 가니까 이렇게 뜸!

문제 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;
    ▶ commmit; 이후인 6,7 만 rollback이 된다. 1,2,3,4 작업은 database에 반영되었다.

Savepoint

문제 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;
select * from dept;

  • rollback to b 하면 포인트 b 지점까지 다시 불러온다.
  • savepoint는 나중에 롤백을 할 지점을 표시하는 것!

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

merge는 insert, update, delete를 한번에 수행하는 명령어
-> upsert로 많이 알려져있다 ! 속도가 빨라서 튜닝방법으로 많이 쓰인다.

emp, dept 테이블 모두 drop 하고 새로 생성!

alter session set nls_Date_format='RR/MM/DD';
drop table emp;
drop table dept;


CREATE TABLE DEPT
       (DEPTNO number(10),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );


INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE EMP (
 EMPNO               NUMBER(4) NOT NULL,
 ENAME               VARCHAR2(10),
 JOB                 VARCHAR2(9),
 MGR                 NUMBER(4) ,
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) );


INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'81-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'81-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'81-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'81-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'81-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'81-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'81-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'81-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'81-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'81-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'80-12-09',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'82-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'83-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'82-01-11',1300,NULL,10);

문제 460. (복습) emp, dept를 조인해서 이름, 부서위치를 출력

select e.ename, d.loc
  from emp e join dept d
  on e.deptno = d.deptno;
----------------------------------------------------------------------------
select e.ename, d.loc
  from emp e , dept d
  where e.deptno = d.deptno;
  • db 모델링중에 반정규화 -> 위와같이 조인을 자주 해야하는 상황인데, 조인을 할 때 너무 성능이 느리면 그냥 loc컬럼을 emp 테이블에 그냥 넣어버린다. 그리고 조인하지 않고 다음과 같이 검색한다.
select ename, loc // 얘가 들어갔다는 가정하에!
  from emp;

문제 461. emp테이블에 loc 컬럼을 추가하세요! (컬럼추가 작업 - 뒤에서 자세하게 배울 것!!)

alter table emp // alter table + 테이블명
 add loc varchar2(10); // add + 컬럼명 + data타입

 select ename, loc
  from emp;


여기 null값을 갱신해줄거다! merge로

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

merge into emp e // emp 테이블을 머지하겠다
  using dept d // dept 사용해서 emp 를 머지하겠다
  on ( e.deptno = d.deptno) // merge 위해서 조인조건 (연결고리가 되는 조건)
  when matched then // 만약 위 조건이 같다면 ( 매치된다면! )
  update set e.loc = d.loc; // e.loc를 d.loc로 update 해라!

문제 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. (only 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;

profile
망구씨
Slow and steady wins the race.
이전 포스트

[Oracle SQL]12일차_23.07.05

다음 포스트

[Oracle SQL]14일차_23.07.07

0개의 댓글