11-2. 데이터 조작 언어(DDL문)

■DDL 명령어 (5가지)

create, alter, drop , truncate, rename

 

 

■ 오라클의 데이터를 삭제하는 방법 3가지

               delete           truncate           drop

1. 데이터    삭제              삭제               삭제

2. 저장공간  유지             삭제                삭제

3. 저장구조  유지             유지                삭제

4. 롤백유무  가능             불가능             불가능

5. flachback    가능             불가능             가능

     유무

 

flashback table emp to before drop;

**우리 프로그램은 안되요~

 

*저장공간:  컬럼/행들.....

*저장구조: 테이블....

 

각각의 코딩

delete from emp;

truncate table emp;

 

**만약 작동 안되면

도스창 다 끄거나

sqlgate 도 껐다 다시켜서 해볼 것.

 

 

■ TRUNCATE 문

 

truncate는

속도 엄청 빠름. 억단위 건도 순식간에 툭.

rollback 해도 안됨. 날라갔음.

 

drop table emp;

***드랍날려도

플래시백으로 복구 가능

 

delete는 골든타임을 가진다.

truncate 는 그런거 없음.

롤백도 안 먹음. 그냥 날라간거임.

 

 

 

-update 문을 사용한 서브쿼리

*update 문의 서브쿼리를 쓸 수 있는 절

update-----서브쿼리가능

set----------서브쿼리가능

where-----서브쿼리가능

 

 

문제340.

ALLEN 의 월급보다 많은 월급을 받는 사원들의

월급을

SMITH 의 월급으로 변경하시오

 

update emp

set sal=( select sal from emp

                where ename='SMITH')

where sal > (select sal from emp

                where eanme='ALLEN');

 

 

 

문제341.

JONES 와 같은 직업을 갖는 사원들의 커미션을

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

 

update emp

set comm = (select comm from emp

               where ename = 'MARTIN')

where job in (select job from emp

                              where ename='JONES');

 

 

update emp

set nvl(comm, -1) = (select nvl(comm, -1) from emp

          where ename= 'MARTIN')

where job in(select job from emp

               where ename = 'JONES');  

이거 왜 안됨????????

 

SET 자리에

COMM 이라고 써야함.

(변환값, 함수 다 못쓰는 듯)

SET 컬럼 =

SET 은 무조건 = 등호식으로 간다.

 

 

 

문제342.

emp 테이블에 loc 컬럼을 추가하시오

 

alter table emp

add loc varchar2(20);

**추가가 안되면 다른창을 꺼버려야함

 

 

문제343.

지금 emp 테이블에 추가한 loc 컬럼을

해당 사원의 부서위치로 값을 갱신하시오

(merge 문으로)

 

merge into emp e

   using dept d

on( e.deptno = d.deptno)

when matched then

update set e.loc = d.loc;

 

*방법 3가지

1. merge 문 (튜닝된 sql)

2. update 절의 서브쿼리문(+제약) (튜닝된 SQL)

3. 상호관련 서브쿼리문을 이용한 update문(악성sql)

 

 

 

 

-상호관련 서브쿼리문을 이용한 update문

(악성sql)

악성sql 로 emp 테이블의 loc 에 다가

detp 테이블의 loc 값을 갱신시켜라

 

update emp e

set loc = (select loc from dept d

             where d.deptno = e.deptno);

 

서브쿼리부터 실행되는게 아니라

메인쿼리부터 실행된다

그래서

메인쿼리에서

update emp

set loc =

하면

  loc컬럼에 값 하나 넣을걸 찾으라고 명령이 들어가고

 그래서 서브쿼리에서 2테이블의 deptno 일치하는 값 찾아내면

 그걸 emp테이블의 loc 컬럼에 집어넣는다.

이렇게 하나하나 찾아서

컬럼에 넣어주는 작업이라서

악성 sql 이다.

 

update emp e 부터 시작.

근데

왜 악성이냐면

where deptno = e.deptno 할 때

deptno 14건을 일일이 조회하면서 업데이트 한다.

만약

where deptno = 10 해주면

deptno 14건 일일이 조회하면서 10번을 골라내

                         업데이트 한다.

코딩작성은 간단하나

내부적으로 일일이 다 조회하면서 하니까

오래걸린다.

빅데이터를 다루면 더 오래 걸린다.

 

 

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

사원테이블에 grade 컬럼을 추가하고

salgrade 테이블의 grade(등급)으로 값을 갱신하시오

(자신의 월급에 맞는 급여등급으로 갱신)

(악성sql 로 짜시오)

 

alter table emp

add grade nember(10);

 

 

update emp e

set grade= (select grade from salgrade s

                    where e.sal between losal and hisal);

 

 

 

 

■ 10장. DDL 문 (2권 67페이지)

 

*DDL 문이 필요한 이유?

  데이터 분석가 입장에서 본다면?

    분석해야 할 데이터를 테이블로 생성해야

     SQL을 작성할 수 있기 때문이다.

(예: 엑셀파일, csv 파일)

 

 

■ DDL 문이란?

 Data   Definition Language

데이터  정의어

: create, alter, drop, truncate, rename

 

DDL 문을 통해 만들어 내는

DATABASE OBJECT

 

■ database object 5가지

1. table:  데이터를 저장하는 기본 저장 단위

2. index: 데이터 검색 속도를 높이는 오브젝트

3. view : 하나이상의 테이블에 있는 데이터의

                  부분집합

4. synonym: 동의어 (다른이름저장....같은거)

5. sequence: 번호를 자동으로 생성하는 번호 생성기

 

 

 

■CREATE 문

table 생성 방법

 create table emp03 

(empno number(10),

 ename varchar2(20),

 sal      number(10),

 job    varchar2(20),

 hiredate date);

 

 

1. 테이블명(컬럼명) 지을 때 주의사항

       -문자로 시작해야 한다

       -25자 이내여야 한다

       -오라클 예약어를 사용하면 안된다

      (ex: select 같은거

           create table select

           (empno number(10) );

 

 

2. 데이터 타입의 종류 3가지

    -문자: char, varchar2, long, clob, blob

      -숫자:  number

    -날짜: date(<<<이건 길이 지정 안해준다. 

                      9999년12월31일까지로

                      정해져있다.)

 

 

■ long, clob, blob 데이터 타입

1. long : 긴 텍스트 파일을 저장할 때 사용하는

         데이터 타입(최대2GB)

2. clob, blob: 이미지 데이터를 저장할 때

              사용하는 데이터 타입(최대4GB)

 

예:

create table resume

 (ename varchar2(20),

age number(10),

mobile varchar2(20),

self_intro long);

 

insert into resume(ename, age, mobile, self_intro)

values('chio jae hyeock', 27, '01053692647',

            'when i was young, my family was poor,

             my mother said, she doesnt like chinese noodle.

            Yahee Yahee Ya');

싱글쿼테이션을 넣고 싶다!!!!!!!!!!!

       doesn''t >>>쓰고자 하는 싱글쿼테이션 앞에

                       싱글쿼테이션을 붙인다.

 

문제345.

emp03 데이터를 2건 정도 입력하시오

 

insert into emp03(empno, ename, sal, job, hiredate)

values (1235, 'SMITH', 2000, 'SALESMAN', to_date('1999/12/12', 'rrrr/mm/dd') );

 

 

문제346.

아래의 테이블을 생성하시오

테이블명: emp04

컬럼명: 이름, 나이, 주민등록번호, 생일, 주소, 핸드폰번호

 

 create table emp04 

(ename varchar2(20),

 age number(10),

 p_number number(30),

 birth date,

 address varchar2(80),

 mobile number(30) );

 

 

 

 

 

 

문제347.

본인 개인정보를 emp04 에 입력하시오

 

 insert into

 emp04(ename, age, p_number, birth, address, mobile)

values( 'OHSEHEE', 31, 880401-2111111, to_date('1988/04/01', 'rrrr/mm/dd'),

'서울시 광진구 구의동',

01049385541);

 

 

 

 

문제348.

부도예측 데이터3.csv 를 오라클 데이터베이스에

로드하시오

1. table 생성

2. SQL Gate 툴로 로드

 

창업건수.csv, 폐업건수.csv 를 오라클 데이터 베이스에

로드하시오!

 

창업건수 테이블명: new_cnt

폐업건수 테이블명: end_cnt

 

create table new_cnt

(year number(30), hairshop number(30), restaurant number(30), sushi number(30), chicken number(30), coffee number(30), korea_food number(30), hof number(30));

 

create table end_cnt

(year number(30), hairshop number(30), restaurant number(30), sushi number(30), chicken number(30), coffee number(30), korea_food number(30), hof number(30));

 

 

문제349.

치킨집 폐업건수가 가장 높은 연도와

그 폐업건수를 출력하시오

select year, max(chicken)

from end_cnt

group by year;

 

select year, chicken

from end_cnt

where chinken = (select max(chicken)

                     from end_cnt);

 

 

 

문제350.

2006년도에 가장 폐업을 많이 한 업종이 무엇인가

 

create table end_cnt2

as

select * from end_cnt

unpivot(cnt for column1 in(HAIRSHOP, RESTAURANT,

SUSHI, CHICKEN, COFFEE, KOREAN_FOOD,HOF) ) ;

 

 

 

화면 캡처: 2018-10-26 오후 4:58

 

select *

from end_cnt2

where cnt = (select max(cnt)

                       from end_cnt2

                       where year = 2006)

and year=2006;

 

이건 컬럼을 통째로 데이타에 넣어야 하는데

unpivot 을 이용해야 한다

■ 컬럼을 데이터 속으로 입력하는 방법---> unpivot 문

데이터------------->컬럼:  pivot 문

컬럼---------------->데이터: unpivot 문

 

 unpivot:      컬럼---------------->데이터

 

10         20          30                          deptno     sum(sal)

----      ------      -------                       ----------    ------------

8750  21973   9400                            30              9400

                                                              20            21973

                                                              10              8750

 

create table emp406

 ("10" number (10),

"20" number(10),

"30" number(10));

 

insert into emp406

values(8750, 21973,9400);

 

 

SELECT * FROM emp406

unpivot(SUMsal FOR deptno IN ("10", "20", "30"));

 

함수아님.

 

 

색 표시한 곳은 어떤걸 넣어도 상관없음

a,d 라고 넣어도 나옴

 

 

 

화면 캡처: 2018-10-26 오후 4:28

 

a 붙인거에 10,20,30 이 컬럼명이 값으로 들어가고

d 는 새로만든 D 라는 컬럼에 값을 넣어준거다.

 

 

문제351.

end_cnt 테이블을 unpivot 문을 이용해서

아래와 같이 결과를 출력하시오

 

select * from end_cnt

unpivot(A for B in(hairshop~모든 컬럼명 );

 

 

 

 

문제351.(마지막문제)

년도, 업종, 폐업건수, 순위를 출력하는데

순위가 1위만 출력하시오

 

년도     업종    폐업건수   순위

2005                                               1

2006                                               1

2007                                               1

2008                                               1

2009                                               1

2010                                               1

2011                                               1

2012                                               1

2013                                               1

2014                                               1

 

 

 

 

 

 

create table end_cnt3

as

select * from end_cnt

unpivot( CNT  for SHOP  in(HAIRSHOP, RESTAURANT,

SUSHI, CHICKEN, COFFEE, KOREAN_FOOD,HOF);

 

 

 

 

 

 

 

 

select *

from (

select year, colmn1 , cnt,

dense_rank() over ( partition by year

order by cnt desc ) 순위

from end_cnt2

)

where 순위 = 1;

 

 

■ALTER 문

-DDL 문의 alter 문

"테이블의 컬럼을 추가, 삭제, 변경 할 때

사용하는 명령어"

 

문제352.

사원 테이블의 사원번호, 이름. 월급, 직업, 부서번호를 가지는

emp09 라는 테이블을 생성하시오

(서브쿼리 방식으로)

 

create table emp09

as

   select empno, ename, sal, job, deptno

   from emp;

 

※ 설명: CTAS(create table as subquery)

>>서브쿼리로 테이블 생성

 

 

문제353.

emp09 에 입사일 컬럼을 추가하시오

 

alter table emp09

add hiredate date;

 

>>>add + 컬럼명 + 데이터 타입(유형)

 

 

문제354.

emp09 에 hiredate 에 emp 테이블의

hiredate 로 값을 갱신하시오

 

empno 컬럼이 없을 거다.

 A 테이블의 내용을

 B 테이블에 옮기는 과정이

alter/add -> update/merge/update상호관련

과정을 겪는다

 

merge into emp09 e9

 using emp e

 on (e9.empno = e.empno)

 when matched then

 update set e9.hiredate = e.hiredate;

 

 

 

문제355.

emp09 에 hiredate 에 emp 테이블의

hiredate 로 값을 갱신하는데

상호관련 서브쿼리인

update 문으로 수행하시오

(악성 SQL)

 

update emp e

set hiredate = (select hiredate from emp e

             where empno = e.deptno);

 

 

문제356.

emp09 에 loc 컬럼을 추가하고

해당사원의 부서위치로

값을 갱신하시오

 

alter table emp09

add loc varchar2(20);

 

merge into emp09 e09

 using dept d

on(e09.deptno = d.deptno)

when matched then

update set e09.loc= d.loc;

 

 

 

문제356.

(select 문)

이름, 나이, 순위(나이 많은 순)를 출력하시오

select ename, age,

dense_rank()over(order by age desc) as "순위"

from emp2;

 

 

문제357.

우리반 테이블에 순위(rnk) 라는

컬럼을 추가하고

나이에 대한 순위로

값을 갱신하시오

 

alter table emp2

add rnk number(10);

 

 

merge into emp2 e2

 using (select ename, age,

       dense_rank()over(order by age desc) as "랭크"

       from emp2) aa

on ( e2.ename = aa.ename)

when matched then

update set e2.rnk =  aa.랭크

 

 

문제359.

우리반 테이블에 EMPNO 컬럼을 추가하고

번호를 1~27 로 갱신하시오

 

alter table emp2

add empno number(10);

 

update emp2

set empno =rownum;

 

 

참고

****

select rownum, ename, age

from emp2;

****

 

 

 

화면 캡처: 2018-10-29 오전 11:12

숨겨진 컬럼인데

순서에 따라 번호가 매겨져 있음.

 

 

**컬럼 추가는 무조건 맨 뒤에 생성된다

만약 맨 앞으로 오게 하고 싶으면???

 

 

문제360.

우리반 테이블에 empno 를 맨 앞에

추가해서 만들려면 어떻게 해야 하는가?

 

일단 컬럼 삭제

alter table emp2

drop column empno;

 

 

create table emp2_backup7

 as

  select rownum as empno, e.*

   from emp2 e;

 

e.* >>>>>emp2 e 의 모든걸 갖고 오라는거

근데 그냥 * 로 하면 안되나?

create table emp2_bbb

as

select rownum as empno, * from emp2;

에러뜸.

이미  rownum as empno 를 먼저 썼기 때문에 * 값이 안 먹힌다.

전체값을 또 뽑기 위해서

테이블 별칭지정해서 e.* 해줘야

새로이 전체값을 뽑아라 라고 명령먹는다.

 

 

 

■RENAME문

■테이블 이름 변경 방법(rename)

 

rename emp2_backup7 to emp2;

 

 

문제362.

이상엽 학생 데이터를 우리반 테이블에

한건 더 입력하시오

 

insert into emp2
 select *

  from emp2

   where ename = '이상엽';

insert 는 행 입력

 

 

문제363.

우리반 테이블의 empno 를 다시

번호 순서대로 1~28까지

 

먼저 확인

select rownem, ename, age

from emp2

 

갱신

update emp2

set empno =rownum;

 

 

 

문제364.

우리반 테이블에 rnk 컬럼을 삭제하고

다시 rnk 컬럼을 추가한 후에

해당 학생의 나이에 대한 순위로

값을 갱신하시오

 

alter table emp2

 drop column rnk;

 

alter table emp2

 add rnk number(10);

 

merge into emp2 e1

using(select empno,

        dense_rank()over(order by age desc)순위

        from emp2) aa

on(e1.empno= aa.empno)     

when matched then

update set e1.rnk = aa.순위;

 

만약

on 절에

e1.ename= aa.ename 으로 하면

에러가 난다.

동명이인 있어서!!(이상엽 추가한거)

그래서 절대적, 유니크한

(중복, 널 없는)

사원번호로 해주는게 좋다.

 

 

 

건휘의 생각!

위의 SQL 을 merge 문이 아니라

상호관련 update 문으로

수행하려면 어떻게 해야 할까(점심시간문제)

 

 

문제365.

순위(rnk) 컬럼의 데이터를 전부 null 로

변경하시오

 

update emp2

set rnk = null;

 

 

문제366

진철이의 나이의 순위가 어떻게 되는가?

select ename, age,

   dense_rank()over(order by age desc)

from emp2

where ename= '김진철';

 

이러면 1로 나온다

(2가 답임)

왜냐하면

조건절을 김진철로 지정했으니까.

실행순서도 select 보다

where 가 먼저니까.

 

랭킹 맞게 뽑으려면

 

select *

from

   ( select ename, age,

          dense_rank()over(order by age desc)순위

   from emp2)

where ename = '김진철'

 

 

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

진철이만 rnk 컬럼의 값을 갱신하시오

(update로 하시오)

 

Update emp2 e

Set e.rnk=

  (SELECT r.순위 

    fROM

        (SELECT empno, age,

          rank() over (order by age desc)순위

          from emp2) r

   where e.empno=r.empno)

Where ename='김진철';

 

 

 

이건 merge문

merge into emp2 aa

using (select empno, ename,

        dense_rank()over(order by age desc)순위

        from emp2) bb

on(aa.empno = bb.empno)

when matched then

update set aa.rnk = bb.순위

where ename='김진철' ;

 

 

 

 

-컬럼 변경 modify

■컬럼 변경 alter/ modify

문제368.

우리반 테이블의 주소 컬럼의 데이터 타입이

무엇이고 그 길이가 어떻게 되는지 확인하시오!

 

desc emp2

 

 

 

화면 캡처: 2018-10-29 오후 1:51

 

 

문제369.

우리반 테이블의 주소 컬럼의 길이를

varchar2(100) ----> varchar2(200) 으로

변경하시오 !

 

alter table emp2

modify address varchar2(200);

 

desc emp2;

 

 

 

 

화면 캡처: 2018-10-29 오후 1:52

 

 

 

 

문제370.

위에 결과에서

다시 varchar2(200) -----> varchar2(100)

줄여보시오

 

alter table emp2

modify address varchar2(100);

 

 

 

문제371.

사원 테이블에 sal 컬럼을 감추시오

 

alter table emp

set unused column sal;

 

감추면 끝이다

다시 나타나게 할 수 없다;;;;;;;;

 

엄청 많은 자료 작업하는데

필요없는 컬럼 때문에

drop 시키고 다시 create 하면

오래 걸리고 부하 걸린다.

그 때

필요없는 컬럼 날리려고 set unused 쓴다.

 

 

alter table emp

 drop unused columns;

 

감춰놨던 컬럼을

날려버리는 작업이다.