cmd 창 열기 > sqlplus 접속 > /as sysdba 입력 > alter system set deferred_segment_creation = false;

- 오라클 11g 부터는 테이블이 생성되었다 하더라도 데이터가 들어오기 전까지는 segment를 생성하지 않는다.

- 오라클 export는 segment 단위로 이루어지기 때문에 segment가 생성되어 있지 않다면 export의 대상이 되지 않는다. (row가 0인 테이블은 export 되지 않는다.)

deferred_segment_creation = true; 데이터가 들어올 때까지 segment를 생성하지 x

deferred_segment_creation = false; create 시점에 segment를 생성

업무파악 > 개념적 데이터 모델링 > 논리적 데이터 모델링 > 물리적 데이터 모델링

(표 작성) (sql 코드 작성)

ERD : Entity Relationship Diagram

UI : User Interface (사용자가 보는 화면)

(1) 식별자란 ?

identifier(식별자) : 그 대상을 제외한 누구도 해당 값을 가질 수 없는 키로 설정!! 유일값

- 하나의 Entity(table)는 반드시 하나의 유일한 식별자가 존재해야 한다.

(2) 식별, 비식별

실선 : 식별관계 - 부모 테이블의 PK가 자식테이블의 FK/PK가 되는 경우

점선 : 비식별관계 - 부모 테이블의 PK가 자식테이블의 일반속성이 되는 경우

(3) ERD

O : Optional(선택) - 0개가 될 수 있다.

| : Mandatory - 1개가 될 수 있다.

* 사원을 기준으로

한명의 사원은 반드시 하나의 부서에 소속되어야 한다.

* 부서를 기준으로

하나의 부서는 여러명의 사원으로 구성될 수 있다.

 

* 게시판 작성 시, 스크립트 꼭 만들어 놓기!!!!

중간에 잘못 됐을 때 cmd창에서 @ + 해당파일드래그 : 처음부터 다시 스크립트 돌려서 오류 수정 가능

(*sql developer에서 하지 말고, 되도록이면 cmd창에서 하기)

스크립트로 한번에 돌리면 logtime으로 정렬했을 때, 뒤죽박죽이 된다. 같은 시간이 되기 때문에 !!!!

(1) sysdate 복습

ex) 3일전, 3시간 전, 시간까지 나타내라

select sysdate, sysdate -3, to_char(sysdate - 3/24,'yy.mm.dd hh24:mi:ss')

from dual;

(2) Reboard 게시판

문1 s10 궁구미 8번글의 첫번째 답글을 다시오. (작성시간 - 2일전)

update reboard

set step = step +1

where gcode = 8 and step > 0;

insert all

into board (seq, userid, username, email, subject, content, hit, logtime, bcode)

values (BOARD_SEQ.nextval,'merry','사사사','ccc@naver.com','Re :s10 궁구미','궁구미궁구미',0,sysdate-2,4)

into reboard (rseq, seq, gcode, depth, step, pseq, reply)

values (reboard_rseq_seq.nextval,board_seq.nextval,8,1,1,8,0)

select * from dual;

update reboard

set reply = reply +1

where seq = 8;

Q. 답변 최신글이 위로 올라가게 하려면??? 1,2,3 순으로 !!!!!

1.update reboard

set step = step +1

where gcode = (원글의 gcode) and step > (원글의 step)

2.insert

3.update reboard

set reply = reply +1

where seq = (원글 seq);

문2 s10 궁구미 8번글의 두번째 답글을 다시오. (작성시간 - 1일전)

update reboard

set step = step +1

where gcode = 8 and step > 0;

insert all

into board (seq, userid, username, email, subject, content, hit, logtime, bcode)

values (BOARD_SEQ.nextval,'merry','사사사','ccc@naver.com','Re :s10 궁구미','궁구미궁구미',0,sysdate-1,4)

into reboard (rseq, seq, gcode, depth, step, pseq, reply)

values (reboard_rseq_seq.nextval,board_seq.nextval,8,1,1,8,0)

select * from dual;

update reboard

set reply = reply +1

where seq = 8;

Q. 8번(원글) - 두번째 답글 - 첫번째 답글 순으로 출력되야 함!!

select *

from board b, reboard r

where b.seq = r.seq

and gcode = 8

order by step;

문3 s10 궁구미 13번글의 첫번째 답글을 다시오. (작성시간 : 오늘)

update reboard

set step = step +1

where gcode = 13 and step > 0;

insert all

into board

values (BOARD_SEQ.nextval,'merry','사사사','ccc@naver.com','Re :s10 궁구미','궁구미궁구미',0,sysdate,4)

into reboard

values (reboard_rseq_seq.nextval,board_seq.nextval,13,1,1,13,0)

select * from dual;

update reboard

set reply = reply +1

where seq =13;

Q. 13번(원글) - 13번의 첫번째 답글 - 8번(원글) - 8번의 두번째 답글 - 8번의 첫번째 답글 순으로 출력되야 함!

select *

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by gcode desc, r.step;

문4 s10 궁구미 12번글의 첫번째 답글을 다시오. (작성시간 : 오늘)

update reboard

set step = step +1

where gcode = 8 and step > 1;

insert all

into board

values (BOARD_SEQ.nextval,'merry','사사사','ccc@naver.com','Re :s10 궁구미','궁구미궁구미',0,sysdate,4)

into reboard

values (reboard_rseq_seq.nextval,board_seq.nextval,8,2,2,12,0)

select * from dual;

update reboard

set reply = reply +1

where seq =12;

commit;

(*insert를 하기 위해서는 12번 원글의 정보를 알아야 한다!)

문5

-- page당 5개씩 출력. (변수 &pg)

-- 오늘쓴글 o : 14:25:37

-- 오늘쓴글 x : 19.10.10

-- 게시판번호 4번

(틀렸...어렵...;;)

select c.*

from (select rownum rn, a.*, decode(logtime,sysdate, to_char(logtime,'hh24:mi:ss'),to_char('YYYY/MM/DD')) as logtime

from (select *

from board b, reboard r

where b.seq = r.seq and b.bcode = 4

order by gcode desc, step) a

where rownum <=&pg*5) c

where c.rn > &pg *5-5;

(정답) CASE문으로 ~ (상대비교 가능)

1.select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step;

2.select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

case

when to_char(b.logtime, 'yymmdd') = to_char(sysdate, 'yymmdd')

then to_char(b.logtime, 'hh24:mi:ss')

else to_char(b.logtime, 'yy.mm.dd')

end logtime,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

(* select 뒤에 order by가 처리되기 때문에 rownum이 뒤죽박죽 된다..SELECT문으로 한 번 더 감싸서 처리!!!!)

3. select rownum rn, a.*

from (

select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

case

when to_char(b.logtime, 'yymmdd') = to_char(sysdate, 'yymmdd')

then to_char(b.logtime, 'hh24:mi:ss')

else to_char(b.logtime, 'yy.mm.dd')

end logtime,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

) a

where rownum <= &pg * 5

(* rownum은 크다 비교가 안되기 때문에 SELECT문으로 한 번 더 감싸서 처리!!!!)

4.select b.*

from (

select rownum rn, a.*

from (

select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

case

when to_char(b.logtime, 'yymmdd') = to_char(sysdate, 'yymmdd')

then to_char(b.logtime, 'hh24:mi:ss')

else to_char(b.logtime, 'yy.mm.dd')

end logtime,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

) a

where rownum <= &pg * 5

) b

where b.rn > &pg * 5 - 5;

정답) DECODE문으로 ~ (동등비교만 가능)

1.select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step;

2.select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

decode(to_char(b.logtime, 'yymmdd'),

to_char(sysdate, 'yymmdd'), to_char(b.logtime, 'hh24:mi:ss'),

to_char(b.logtime, 'yy.mm.dd')) logtime2,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

3. select rownum rn, a.*

from (

select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

decode(to_char(b.logtime, 'yymmdd'),

to_char(sysdate, 'yymmdd'), to_char(b.logtime, 'hh24:mi:ss'),

to_char(b.logtime, 'yy.mm.dd')) logtime2,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

) a

where rownum <= &pg * 5

4.select b.*

from (

select rownum rn, a.*

from (

select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

decode(to_char(b.logtime, 'yymmdd'),

to_char(sysdate, 'yymmdd'), to_char(b.logtime, 'hh24:mi:ss'),

to_char(b.logtime, 'yy.mm.dd')) logtime2,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

) a

where rownum <= &pg * 5

) b

where b.rn > &pg * 5 - 5;

(1) Insert all : 여러개의 테이블에서 데이터를 일괄적으로 등록하고 싶을 때 사용

(but, sequence가 생성되어 있을때는 사용할 수 없다. 하나씩 insert 해야 됨!!! )

ex) insert all

into member (userid,username,userpwd) values ('aaa','가나다',1234)

into member_detail (userid,tel1,zipcode) values ('aaa','123','14590');

(2) Comment on 주석달기 : 오라클 쿼리를 작성하다 보면 컬럼의 의미 파악이 안될 때가 있다.

이럴때, 컬럼의 의미를 쉽게 조회 할 수 있도록 사용

- Table comment 추가

comment on table 테이블명 is '설명';

ex) comment on table emp is '사원';

- Column comment 추가

comment on column 테이블명.컬럼명 is '설명';

ex) comment on column emp.empno is '사원번호';

- Table comment 삭제

comment on table 테이블명 is '';

ex) comment on table emp is '';

- Column comment 삭제

comment on column 테이블명.컬럼명 is '';

ex) comment on column emp.empno is '';

- Table commnt 확인

select *

from all_col_comments

where table_name = '테이블명';

(3) RollUp 함수 : 총 합계를 구하고자 할 때 사용 (sum, count 함수 등과 함께 사용)

: group by rollup ( ) 컬럼 순서에 따라서 결과가 달라지고,

( )를 어떻게 묶느냐에 따라서도 결과가 달라진다.

ex) 직업별로 급여 합계와 전체 급여의 합을 구하라

select job, sum(sal)

from emp

group by rollup(job);

ex) 직업, 사원번호별로 사원 수를 구하되,

직업별 사원수의 합, 전체 사원의 합을 구하라

select job, deptno, count(*)

from emp

group by rollup (job, deptno);

ex) 직업, 사원번호, 급여별로 사원 수를 구하되,

직업과 사원번호별 사원수의 합, 직업별 사원수의 합, 전체 사원의 합을 구하라

select job, deptno,sal, count(*)

from emp

group by rollup (job, deptno,sal);

* 이렇게 세세히 알기 보다, 직업별 사원수의 합만 알고 싶다면, ( )로 묶어주면 ok

select job, deptno,sal, count(*)

from emp

group by rollup (job, (deptno,sal));

(3) DB 모델링

(4) Index

(1) 문자열 인코딩 방식

Unicode (U+) : 문자열을 숫자로 표현하기 위해 문자 하나와 숫자 하나를 각각 매핑(연결)한 것

UTF-8 : 아시아 문자는 3byte로 가변 표기 (전세계 언어 모두 처리 가능)

ex) varchar2(20)는 6글자까지 처리 가능

EUC-KR : ksc5601, 아시아 문자는 2byte로 처리 (표현할 수 없는 한글이 존재)

(* 영어랑 숫자는 1byte로 처리)

(2) 데이터 타입 비교

(* 아시아 문자 입력한다고 보면,)

varchar2 4000byte 1333자 (변동사항이 있으면 안좋음, 중간에 빈 공간을 찾아 변동사항 입력 후 연결해야 함)

char 2000byte 666자 (변동사항이 있을 때 좋음, 변동사항을 입력하기 위한 공간이 있기 때문에)

(사이즈가 큰 데이터를 외부 파일로 저장하기 위한 데이터 타입)

clob 4GB (주로 사용)

blob 4GB (학생증 사진,신입사원 사진 등에 사용)

long 1GB or 2GB (검색시에 성능이 떨어지기 때문에 clob을 더 선호, 잊어도 됨)

(3) 데이터베이스 설계 단계

요구사항 분석 (용도 파악) ==> 개념적 설계 (ER 다이어그램) ==> 논리적 설계 (릴레이션 스키마)

- ER 다이어그램을 테이블로 변환

-- 기본 회원 정보

create table member

( userid varchar2(16),

username varchar2(20) not null,

userpwd varchar2(20) not null,

emailid varchar2(16),

emaildomain varchar2(30),

profile varchar2(30),

joindate date default sysdate,

role number(3) default 0, (check 보다는 default값으로 설정,

처음에는 일반회원으로 가입을 시켜놓고, 추후에 등급 변경)

constraint member_userid_pk primary key(userid)

);

-- 상세 회원 정보

create table member_detail

( userid varchar2(16), (*number로 하면 010 or 02 일때, 맨 처음 0을 인식하지 못함)

tel1 varchar2(3),

tel2 varchar2(4),

tel3 varchar2(4),

zipcode varchar2(5),

address varchar2(100),

address_detail varchar2(100),

constraint member_detail_userid_fk foreign key (userid) references member (userid)

);

- 테이블 삭제 (삭제하는 순서 중요)

drop table member_detail; (참조하고 있는 테이블을 먼저 지워야 함)

drop table member; (그 이후에 참조가 되어지는 테이블을 지워야 함)

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

-- 게시판 형식

create table board_type

(btype number,

btype_name varchar2(20),

constraint board_type_btype_pk primary key(btype)

);

- 시퀀스 생성

create sequence board_type_btype_seq

increment by 1

start with 1;

-- 카테고리 목록

create table catagory_list

(ccode number,

cname varchar2(30),

constraint cataagory_list_ccode_pk primary key(ccode)

);

- 시퀀스 생성

create sequence catagory_list_ccode_seq

increment by 1

start with 1;

- 게시판 목록

create table board_list

(bcode number,

bname varchar2(30),

ccode number,

btype number,

constraint board_list_bcode_pk primary key(bcode),

constraint board_list_ccode_fk foreign key (ccode) references catagory_list (ccode),

constraint board_list_btype_fk foreign key (btype) references board_type (btype)

);

- 시퀀스 생성

create sequence board_list_bcode_seq

increment by 1

start with 1;

- 테이블 삭제 (삭제 순서 중요)

drop table board_list;

drop table board_type;

drop table category_list;

- 시퀀스 삭제 (순서 중요하지 않음)

drop sequence board_type_btype_seq;

drop sequence catagory_list_ccode_seq;

drop sequence board_list_bcode_seq;

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

-- 게시판

create table board

(seq number,

userid varchar2(16),

username varchar2(20),

email varchar2(50),

subject varchar2(100) not null,

content clob not null,

hit number default 0,

logtime date default sysdate,

bcode number,

constraint board_seq_pk primary key(seq),

constraint board_userid_fk foreign key (userid) references member (userid),

constraint board_bcode_fk foreign key(bcode) references board_list (bcode)

);

create sequence board_seq_seq

increment by 1

start with 1;

-- 댓글

create table memo

( mseq number,

seq number,

userid varchar2(16),

mcontent varchar2(200) not null,

mlogtime date default sysdate,

ipaddress varchar2(15),

constraint memo_mseq_pk primary key(mseq),

constraint memo_seq_fk foreign key (seq) references board (seq),

constraint memo_userid_fk foreign key (userid) references member (userid),

);

create sequence memo_seq_seq

increment by 1

start with 1;

-- 답변형 게시판

create table reboard

( rseq number,

seq number,

gcode number,

depth number,

step number,

pseq number,

reply number,

constraint reboard_rseq_pk primary key(rseq),

constraint reboard_seq_fk foreign key (seq) references board (seq)

);

create sequence reboard_seq_seq

increment by 1

start with 1;

- 앨범형 게시판

create table album

( aseq number,

seq number,

savefolder varchar2(8) , (*YYYY/MM/DD로 처리)

originalpicture varchar2(100),

savepicture varchar2(100),

picturemode number(1) default 0, (*정사각형을 기준으로 잡는다면 0이면 가로, 1이면 세로,

요즘엔 많이 사용 x)

constraint album_aseq_pk primary key(aseq),

constraint album_seq_fk foreign key (seq) references board (seq)

);

create sequence album_seq_seq

increment by 1

start with 1;

- 자료형 게시판

create table bbs

( bseq number,

seq number,

savefolder varchar2(8),

originalfile varchar2(100),

savefile varchar2(100),

filesize number,

constraint bbs_bseq_pk primary key(bseq),

constraint bbs_seq_fk foreign key (seq) references board (seq)

);

create sequence bbs_seq_seq

increment by 1

start with 1;

drop table memo;

drop table reboard;

drop table album;

drop table bbs;

drop table board;

drop sequence board_seq_seq ;

drop sequence memo_seq_seq;

drop sequence reboard_seq_seq;

drop sequence album_seq_seq;

drop sequence bbs_seq_seq;

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

(* 드라이브는 나눠서 사용하는 것이 좋다.)

d 드라이브 : 자료 저장만

c 드라이브 : 설치만

- 네이버에서 반디집 다운로드

- 네이버에서 에디트플러스 다운로드 에디트플러스 문서 편집기 5.2 다운로드

- 네이버에서 에버노트 다운로드

(1) TOP_N 질의 활용

문1 emp 테이블에서, 급여를 가장 많이 받는 3명을 출력하라.

select a.*

from (select *

from emp

order by sal desc) a

where rownum <=3;

문2 사원의 사번, 이름, 급여, 커미션포함급여, 부서이름, 도시를 출력하라.

-- 급여를 가장 많이 받는 순으로 정렬

-- 부서이름이 null이면 '대기발령중'으로 출력

-- 한 화면당 5명씩 출력

-- 2번째 화면의 사원번호를 출력

(틀림.... 어렵다...)

select e.empno,e.ename,e.sal,e.sal+nvl(comm,0),nvl2(dname,dname,'대기발령중'),d.loc

from emp e, dept d, (select empno,ename,sal,sal+nvl(comm,0),nvl2(dname,dname,'대기발령중'),loc

from emp natural join dept

order by 3 desc)

where e.deptno=d.deptno and rownum <= 10

order by 3 desc;

(정답 ==> 한번에 다 하려고 하지 말고, 천천히 하나하나씩 풀어가기)

( ① => ② => ③ 풀이과정 )

① select empno,ename,sal,sal+nvl(comm,0),nvl(dname,'대기발령'), d.loc

from emp e, dept d

where e.deptno = d.deptno(+) (* emp 테이블에 사원번호가 null 값이니까 출력하려면 left outer join 사용)

order by 3 desc;

select rownum, a.*

from (select empno,ename,sal,sal+nvl(comm,0),nvl(dname,'대기발령'), d.loc

from emp e, dept d

where e.deptno = d.deptno(+)

order by 3 desc) a

where rownum <= 10; (* 1번째 화면은 1등~5등 출력되니까 2번째 화면은 6등~10등까지 총 10명 출력)

cf) 출력순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

select b.*

from (select rownum rn, a.*

from (select empno,ename,sal,sal+nvl(comm,0),nvl(dname,'대기발령'), d.loc

from emp e, dept d

where e.deptno = d.deptno(+)

order by 3 desc) a

where rownum <= &pg*5) b (* 한 화면당 5명씩 출력)

where b.rn > &pg * 5-5; (* pg에 대한 값 입력 : 2)

(2) 치환변수 : sql문에서 '&'치환변수를 사용하면 값을 입력하도록 요구할 수 있다.

: 동일한 컬럼을 데이터만 바꿔서 검색하거나 입력하고자 할 때 사용

: 문자 or 날짜를 입력할 때는 작은따옴표(' ')를 사용

(3) RANK( ) OVER 함수 : 서브쿼리 안써도 되는!!

: rownum과 같지만 rownum은 무조건 1부터 순번을 매긴다면,

rank( )는 순위가 같을 경우 같은 순위로 처리된다.

select rank( ) over(order by sal desc) rn, ename, sal s

from emp;

select empno, sum(sal), rank() over(order by sum(sal) desc) as rank

from emp

group by empno;

(* 동점일 경우 같은 순위로 처리되기 때문에 만약 2등이 2명이라면 그 후 순위은 3등이 아닌 4등이 된다.)

이렇게 나타나는 것이 싫다면? DENSE_RANK() OVER() 함수 사용! 2명의 2등 뒤에 그 후 순위는 3등으로 출력~

select empno, sum(sal), dense_rank() over(order by sum(sal) desc) as rank

from emp

group by empno;

(* 동점일 경우라도 같은 등수로 처리되는 것이 싫다면? ROW_NUMEBER() 사용! )

select empno, sum(sal), row_number() over(order by sum(sal) desc) as rank

from emp

group by empno;

(* 전체가 아닌 부서번호별로 등수를 매기고 싶다면? rank함수 or row_number 안에 partition by 컬럼명)

select deptno,ename,job,sum(sal),rank() over(partition by deptno order by sum(sal) desc) as rank

from emp

group by deptno,ename,job;

(* 1위만 출력하고 싶다면? select문으로 한 번 더 감싸주기)

select * from

(select deptno,ename,job,sum(sal),rank() over(partition by deptno order by sum(sal) desc) as rank

from emp

group by deptno,ename,job)

where rank = 1;

(4) Entity Relationship Diagram = ERD

- 객체(Entity), 속성(Attribute), 관계(Relationship)로 구성

- 점선은 선택(may be)를 의미하고, 실선은 필수(must be)를 의미

- 양쪽을 모두 고려해야 한다. 우선, 왼쪽에서 오른쪽으로! 그후, 오른쪽에서 왼쪽 관계 따져보기!

ex) 사원과 부서 객체가 있다고 가정해보자. (M : 1 관계)

사원 입장에서 부서와의 관계 : 사원은 어느 부서든 하나의 부서에 반드시 소속되기 때문에 실선

부서 입장에서 사원과의 관계 : 신생부서일 경우 아직 사원이 없을 수도 있기 때문에 점선 or 각 부서는

여러 명의 사원을 배치

(5) PRIMARY KEY 기본키 vs FOREIGN KEY 외래키

PRIMARY KEY : UIQUE, NOT NULL

: 학번, 주민번호, 사원번호 등등

 

FOREIGN KEY : 외부 테이블의 PRIMARY KEY를 참조한다는 의미에서 외래키

: NULL값 가능

ex) EMP 테이블과 DEPT 테이블

DEPT 테이블이 부모 테이블이며, DEPTNO가 기본키

EMP 테이블이 자식 테이블이며, DEPTNO가 외래키

- 부모 테이블에 없는 값이 자식 테이블에 입력될 수 없다.

- 자식 테이블이 참조 하고 있는 값은 부모 테이블에서 삭제할 수 없다.

(1) VIEW

: Table과 유사하지만, Table과는 달리 Data를 저장하기 위한 물리적인 저장공간을 필요로 하지 않음.

: 활용도가 높아서 많이 사용

- 보안 관리를 위한 View

: 전체 데이터에서 보안상 특정 정보를 빼고 보여주기 위한 용도

- 융통성 향상을 위한 View

: 자주 사용할 데이터를 미리 만들어 수정이 간편하게 하기 위한 용도

- 사용편의를 위한 View

: 검색조건 단순화, join 문장 단순화

- 실행속도 향상을 위한 View

: 특정절차를 수행시키기 위해 미리 만들어 놓는 용도

- CREATE VIEW

뉴욕에 있는 사원들

select ename, loc

from emp, dept

where emp.deptno = dept.deptno

and loc = 'NEW YORK';

CREATE VIEW V_NEW_YORK

AS select ename, loc

from emp, dept

where emp.deptno = dept.deptno

and loc = 'NEW YORK';

SELECT *

FROM V_NEW_YORK;

(FROM에 테이블이 있는 것처럼 부질의로 사용했던 것도 일종의 View)

SELECT *

FROM (select ename, loc

from emp, dept

where emp.deptno = dept.deptno

and loc = 'NEW YORK') loc_info;

(SELF JOIN) 상사의 정보 출력

SELECT E1.EMPNO, E1.ENAME, E2.ENAME

FROM EMP E1, EMP E2

WHERE E1.MGR = E2.EMPNO;

CREATE VIEW V_MGR_INFO AS

SELECT E1.EMPNO, E1.ENAME ENAME, E2.ENAME MGR_ENAME

FROM EMP E1, EMP E2

WHERE E1.MGR = E2.EMPNO;

SELECT *

FROM V_MGR_INFO;

(있으면 추가로 넣고 아니면 새로 만들고자 할 때 = 같은 이름으로 데이터 추가하고자 할 때)

CREATE OR REPLACE VIEW V_MGR_INFO AS

SELECT E1.EMPNO EMPNO, E1.ENAME ENAME, E1.JOB JOB,

E2.ENAME MGR_ENAME

FROM EMP E1, EMP E2

WHERE E1.MGR = E2.EMPNO;

-DROP VIEW V_MGR_INFO;

DROP VIEW V_NEW_YORK;

문1 EMP 테이블로부터 10,20번 부서의 사원들로 이루어진 뷰를 만드시오.

CREATE VIEW V_EMPNO

AS select *

from emp

where deptno in (10,20);

문2 사원번호, 사원이름, 부서이름, 상급자 이름으로 구성된 뷰를 만드시오.

CREATE VIEW V_MGR

AS select e1.empno, e1.ename ENAME, d.dname, e2.ename MGR_ENAME

from dept d, emp e1, emp e2

where e1.mgr = e2.empno

AND e1. deptno = d.deptno;

문3 학과별 평균 몸무게와 평균 키를 가지는 뷰를 만드시오.

CREATE VIEW V_AVG

AS select stu_dept, round(avg(stu_weight)) 평균몸무게, round(avg(stu_height)) 평균키

from student

group by stu_dept;

문4 학생이름, 키, 몸무게, BMI 지수를 가지는 뷰를 만드시오.

CREATE VIEW V_BMI

AS select stu_name, stu_height, stu_weight, round((10000*stu_weight/(power(stu_height,2))),1) BMI

from student;

(원본 TABLE도 바뀐다~)

UPDATE V_EMPNO

SET SAL = 4000

WHERE EMPNO = 7839;

(TABLE에 없는 VIEW 컬럼의 데이터 값을 변경할 순 없다)

UPDATE V_BMI

SET BMI = 20

WHERE STU_NAME = '박희철'; (안되는 식)

SELECT * FROM USER_VIEWS;

문5 사원번호, 이름, 급여, 커미션, 전체수입(급여+커미션)을 가지는 뷰를 만드시오.

CREATE OR REPLACE VIEW V_SAL

AS select empno, ename, sal, comm, (sal+nvl(comm,0)) 전체수입

from emp;

(2) TOP-N 질의 : 상위 데이터(가장 많은) or 하위 데이터(가장 적은) 몇 개만 추출하고 싶을 때 사용

: FROM 절에서 ORDER BY절을 사용할 수 없는 불편함을 개선

: WHERE절에 ROWNUM(행의 넘버) 사용

문6 수입 가장 많은 상위 3명의 수입과 직원번호를 출력하시오.

select empno,income

from (select empno, ename, sal, comm, (sal+nvl(comm,0)) income

from emp

order by incom desc) emp_income;

where rownum <= 3;

**문7 부서별 평균 급여가 작은 부서 2개의 부서명, 평균급여를 출력하시오.

select *

from (select dname, round(avg(sal))

from emp, dept

where emp.deptno = dept.deptno

group by dname

order by 2) DNAME_AVG

WHERE ROWNUM <=2;

문8 BMI가 큰 학생 상위 5명의 이름, 학번, BMI 지수를 출력하시오.

create view v_bmi_desc as

select stu_name, stu_no, NVL(round((10000*stu_weight/(power(stu_height,2))),1),0) BMI

from student

order by bmi desc;

select *

from v_bmi_desc

where rownum <=5;

(3) INDEX : PRIMARY KEY를 지정하면 INDEX가 자동으로 설정됨

(STU_NAME을 INDEX하기)

CREATE INDEX i_stu_name

ON STUDENT(STU_NAME);

DROP INDEX i_stu_name;

(4) Sequence : 연속적인 숫자 값을 자동적으로 증가시키는 것

Cycle : 최댓값 도달 시 순환 여부

Cache : 원하는 숫자만큼 미리 만들어서 저장

(1~100까지 증가시키는 Sequence)

CREATE SEQUENCE seq_num

INCREMENT BY 1

START WITH 1

MAXVALUE 100;

CREATE TABLE SEQ_EMP (SEQ_NO, EMPNO, ENAME) AS

SELECT SEQ_NUM.nextval, EMPNO, ENAME

FROM EMP;

INSERT INTO SEQ_EMP

VALUES (SEQ_NUM.nextval,1111,'JUN');

문9 초기값 1, 증감값 2, 최댓값 20, CYCLE이 가능한 시쿼스를 만드시오.

(ORA-04013: CACHE 에는 1 사이클보다 작은 값을 지정해야 합니다)

CREATE SEQUENCE seq_s

INCREMENT BY 2

START WITH 1

MAXVALUE 20

CYCLE;

(최댓값을 20보다 큰 수로 하기)

CREATE SEQUENCE seq_s

INCREMENT BY 2

START WITH 1

MAXVALUE 200

CYCLE;

(NOCACHE 붙이기)

CREATE SEQUENCE seq_s

INCREMENT BY 2

START WITH 1

MAXVALUE 20

CYCLE

NOCACHE;

(1) 데이터 락(Lock)

병행처리의 문제를 해결하기 위해 사용한다.

한명의 사용자가 아니라 다른 사용자도 사용할 경우 commit을 완료해야 변경된 내용이 반영된다.

(2) 데이터 정의어(DDL)

-SQL DDL(Table Create, Alter, Drop)

-생성할 수 있는 객체 종류

1.테이블 : 기본적인 데이터 저장 단위

2.뷰 : 데이터의 부분집합

3.시퀀스 : 고유번호를 자동으로 생성

4.인덱스 : 빠른 검색을 위함

(3)CREATE TABLE

테이블명은 ORACLE의 예약어를 쓸 수 없다.

테이블명은과 칼럼 명은 반드시 문자로 시작, 최대 30자까지 허용

-SELECT * FROM TAB; (내가 만든 테이블 모두 확인 가능)

-SELECT TABLE_NAME FROM USER_TABLES; (내가 만든 테이블 모두 확인 가능)

-TABLE 새로 만들기

CREATE TABLE TEST(

U_ID VARCHAR2(10) PRIMARY KEY,

U_DATE DATE

);

- 기존 테이블 활용해서 타입과 컬럼만 복사하기

(WHERE 1 = 0 데이터는 안 넣고, 타입이랑 컬럼만 복사하고 싶은 경우 사용)

CREATE TABLE ACCOUNT2

AS SELECT * FROM ACCOUNT

WHERE 1 = 0;

- 기존 테이블 활용해서 똑같은 데이터로 복사하기

CREATE TABLE ACCOUNT3

AS SELECT * FROM ACCOUNT;

문1 ACCOUNT 테이블을 생성한다.

ACCOUNT_NO : 숫자 4자리,PK

ACCOUNT_DATE : 날짜

CODE : 가변문자 14자리

WITHDRAWAR : 숫자 8자리

DEPOSIT : 숫자 10자리

BALANCE : 숫자 12자리

BRANCH_CODE : 문자 8자리

CREATE TABLE ACCOUNT

(ACCOUNT_NO NUMBER(4) PRIMARY KEY,

ACCOUNT_DATE DATE DEFAULT SYSDATE, **DEFAULT SYSDATE(데이터를 넣지 않아도 현재시간으로 자동으로 입력됨)

CODE VARCHAR2(14),

WITHDRAWAR NUMBER(8),

DEPOSIT NUMBER(10),

BALANCE NUMBER(12),

BRANCH_CODE CHAR(8));

(4) DROP : 테이블 자체를 삭제 VS DELETE : 데이터를 삭제

DROP TABLE [테이블명]; VS DELETE FROM TABLE;

(5) RENAME : 테이블 이름 바꾸기

RENAME [이전 테이블명] TO [이후 테이블명];

RENAME S_STU TO S_STUDENT;

(6) ALTER TABLE

① 테이블 컬럼 이름 변경하기

ALTER TABLE [테이블명]

RENAME COLUMN 기존컬럼명 TO 새컬럼명;

ex) ALTER TABLE S_STU

RENAME COLUME BMI TO BMIs;

② 테이블 열 추가

ALTER TABLE [테이블명]

ADD ([칼럼명] [칼럼타입]) ;

ex)ALTER TABLE S_STUDENT

ADD (BMI NUMBER(4,1));

③ 테이블 열 타입 변경

ALTER TABLE [테이블명]

MODIFY ([칼럼명] [이후 변경타입]);

(문자에서 숫자로 or 자릿수를 이전보다 작게 하는 것은 기존 데이터에 문제를 발생시켜 오류)

ex)ALTER TABLE S_STU

MODIFY (BMIs NUMBER);

④ 테이블 칼럼 자체를 삭제

ALTER TABLE [테이블명]

DROP COLUMN [컬럼명];

ex)ALTER TABLE T_STUDENT

DROP COLUMN STU_DEPT;

(7) TRUNCATE : 테이블내의 데이터 삭제

(DELETE와 같지만 TRUNCATE는 자동 COMMIT 되어 데이터를 잘못 삭제하더라도 ROLLBACK을 할 수 없다.)

(DELETE는 로그를 쌓지만 TRUNCATE는 로그가 쌓이지 않기 때문에, 속도면에서 월등히 빠르다.)

하지만, 잘못 날렸을 경우 ROLLBACK이 되지 않기 때문에 굉장히 신중히 날려야 하는 쿼리다. )

(8) 제약조건(Constraint) : 부적절한 자료가 입력되는 것을 방지하기 위해서 사용

- 그냥 컬럼명, 컬럼타입 뒤에 제약조건만 적어도 되지만, 제약 조건명을 명시하면 추후에 어떤 제약 조건을 위해했는지 알 수 있게 되기 때문에 편리하다.

-Oracle에서 제공하는 제약조건

(DESC명령어로는 NOT NULL 제약조건만 확인 가능)

확인하고 싶다면?

=(DESC USER_CONSTRAINTS;)

1. NOT NULL : NULL 값의 입력을 금지, 필수 입력을 위한 컬럼에 사용

CREATE TABLE STUDENT_1 (

STU_NO CHAR(9) NOT NULL,

STU_NAME VARCHAR2(20) NOT NULL

);

2. UNIQUE : 해당 칼럼의 값이 테이블 안에서 유일한 값이 되도록 하되 NULL을 허용.

: 유일 키 제약을 작성하면 자동으로 유일 인덱스를 생성한다.

CREATE TABLE STUDENT_2 (

STU_NO CHAR(9) UNIQUE,

STU_NAME VARCHAR2(20) UNIQUE

);

3. CHECK : 입력할 수 있는 값의 범위 등을 제한한다 (급여 컬럼을 500에서 5000사이 값만 입력

or

성별 입력시 사용)

CREATE TABLE STUDENT_3 (

STU_NO CHAR(9),

STU_NAME VARCHAR2(20) CONSTRAINT u_stu_name NOT NULL

STU_GENDER CONSTRAINT c_stu_gender CHECK (STU_GENDER IN ('M','F')

);

4. DEFAULT : 데이터를 입력하지 않아도 지정된 값이 기본으로 입력.

(DEFAULT 값을 지정하지 않으면 NULL로 출력)

ex) LOC VARCHAR2(13) DEFAULT 'SEOUL'

5. PRIMARY KEY : (UNIQUE, NOT NULL) 두가지 제약조건이 걸린 키

: 데이터 중복이 있어서는 안된다!!

: 사원번호 OR 학번 등에 적용

6. FOREIGN KEY : 테이블 다 만들고 추가하는 것이 좋다 (부모 테이블과 자식 테이블 설정을 위해서)

: 부모 키가 되기 위한 컬럼은 반드시 부모 테이블의 PRIMARY KEY로 설정되어야 함

(DEPT 테이블에 DEPTNO는 10,20,30,40인 부서가 존재한다.

EMP 테이블에 DEPTNO는 10,20,30으로만 기록되어 있다.

이처럼, 만일 DEPT 테이블에 존재하지 않는 부서번호가 EMP 테이블에 지정된다면 이치에 맞지 않는다.

그러므로, EMP 테이블에 부서번호를 입력 할 때 DEPT 테이블에 존재하는 부서번호만 입력하도록 해야 하므로

이때, EMP 테이블의 DEPTNO에 FOREIGN KEY를 사용하는 것이다.)

CREATE TABLE T_EMP(

EMPNO CHAR(3) PRIMARY KEY,

ENAME VARCHAR2(30),

DEPTNO CHAR(2) CONSTRAINT fk_deptno

REFERENCE DEPT(DEPTNO)

);

ALTER TABLE T_EMP

ADD CONSTRAINT fk_deptno REFERENCE DEPT (DEPTNO);

- 제약조건 확인

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'STUDENT_1';

문2 다음 테이블(nike)을 만드시오.

id : 문자 10자리 (pk)

style : 문자 20자리

color : 가변문자 20자리

owner : 가변문자 20자리

c_size : XS, S, M, L, XL, XXL만 입력가능

gender : M, F만 입력 가능

count : NUMBER (1~9999 입력 가능)

m_date : DATE

CREATE TABLE NIKE(

ID CHAR(10) CONSTRAINT pk_id PRIMARY KEY,

STYLE CHAR(20),

COLOR VARCHAR2(20),

OWNER VARCHAR2(20),

C_SIZE CHAR(3),

CONSTRAINT C_C_SIZE CHECK (C_SIZE IN ('XS','S','M','L','XL','XXL')),

GENDER CHAR(1)

CONSTRAINT C_GENDER CHECK (GENDER IN ('M','F')),

COUNT NUMBER

CONSTRAINT C_COUNT CHECK (COUNT BETWEEN 1 AND 9999),

M_DATE DATE

);

(9) 제약조건 추가

//NOT NULL 추가

ALTER TABLE NIKE MODIFY style NOT NULL;

ALTER TABLE NIKE MODIFY style CONSTRAINT nn_style NOT NULL;

ALTER TABLE NIKE MODIFY style NULL;

//CHECK, UNIQUE, PK, FK 추가

ALTER TABLE NIKE

ADD CONSTRAINT c_color CHECK (COLOR IN ('YELLOW','BLUE','RED'));

ALTER TABLE NIKE

ADD CONSTRAINT u_color UNIQUE (COLOR);

(10) 제약조건 삭제

ALTER TABLE NIKE

DROP CONSTRAINT c_color;

(11) PRIMARY KEY 삭제

ALTER TABLE [TABLE 명]

DROP PRIMARY KEY

(12) PRIMARY KEY 만들기

ALTER TABLE [TABLE 명]

ADD PRIMARY KEY (COL1, COL2, COL3 ...)

ALTER TABLE NIKE

ADD CONSTRAINT pk_id PRIMARY KEY (ID);

문3 다음 테이블(ADIDAS)을 만드시오.

ID : 기본키 (pk)

STYLE : UNIQUE

COLOR : 색깔 입력은 항상 존재

OWNER : 제약 조건 없음

C_SIZE : XS, S, M, L, XL, XXL만 입력가능

GENDER : M, F만 입력 가능

COUNT : 제약 조건 없음

M_DATE : DEFAULT SYSDATE

OWNER -> NOT NULL 추가

C_SIZE -> CHECK 조건 삭제

COUNT -> CHECK 조건 추가 (0 <= COUNT <= 1000)

CREATE TABLE ADIDAS(

ID CHAR(10) CONSTRAINT pk_id PRIMARY KEY,

STYLE CHAR(20) CONSTRAINT u_style UNIQUE,

COLOR VARCHAR2(20) CONSTRAINT n_color NOT NULL,

OWNER VARCHAR2(20),

C_SIZE CHAR(3) CONSTRAINT c_c_size CHECK (C_SIZE IN ('XS','S','M','L','XL','XXL')),

GENDER CHAR(1) CONSTRAINT c_gender CHECK (GENDER IN ('M','F')),

COUNT NUMBER,

M_DATE DATE CONSTRAINT d_m_date DEFAULT SYSDATE

);

ALTER TABLE ADIDAS MODIFY OWNER NOT NULL;

ALTER TABLE ADIDAS DROP CONSTRAINT c_c_size;

ALTER TABLE ADIDAS ADD CONSTRAINT c_count CHECK (COUNT BETWEEN 1 AND 1000);

문제4.

테이블 명 : students -> 1.student_t 테이블명 변경

STUDENT_NO(PK) SURNAME FORENAME -> 2.삭제 FORENAME

20060101 Dickens Charles

20060102 ApGwilym Dafydd

20060103 Zola Emile

20060104 Mann Thomas

20060105 Stevenson Robert

3.FORENAME 생성, 4.UNIQUE, 5.NOT NULL

charles

dafydd

emile

thomas

robert

-> 6.students테이블 PK 삭제

-> 7.PK 새로 추가

-> 8.STUDENT_NO 에 CHECK (20060001 ~ 20069999) 제약 설정

테이블 명 : modules

MODULE_CODE(PK) MODULE_NAME

CM0001 Databases

CM0003 Operating Systems

CM0004 Graphics

CM0002 Middle Ware -> 11. 컬럼추가

테이블 명: marks

STUDENT_NO MODULE_CODE MARK -> 9.MARKS으로 컬럼명 변경,

10. 컬럼타입 변경 (NUMBER(2,0))

12. marks FK 추가

20060101 CM0001 80

20060101 CM0002 65 (*ora-00001 무결성 제약 조건 위배됩니다 : PK 지정된 값이 중복)

20060101 CM0003 50

20060102 CM0001 75

20060102 CM0003 45

20060102 CM0004 70

20060103 CM0001 60

20060103 CM0002 75

20060103 CM0004 60

20060104 CM0001 55

20060104 CM0002 40

20060104 CM0003 45

20060105 CM0001 55

20060105 CM0002 50

20060105 CM0006 65 -> 13. 새로운 로우 데이터 추가

CREATE TABLE STUDENTS(

STUDENT_NO CHAR(8) CONSTRAINT pk_student_no PRIMARY KEY,

SURNAME VARCHAR2(10),

FORENAME VARCHAR2(10));

INSERT INTO STUDENTS VALUES(20060101,'Dickens','Charles');

INSERT INTO STUDENTS VALUES(20060102,'ApGwilym','Dafydd');

INSERT INTO STUDENTS VALUES(20060103,'Zola','Emile');

INSERT INTO STUDENTS VALUES(20060104,'Mann','Thomas');

INSERT INTO STUDENTS VALUES(20060105,'Stevenson','Robert');

CREATE TABLE modules(

MODULE_CODE CHAR(8) CONSTRAINT pk_module_code PRIMARY KEY,

MODULE_NAME VARCHAR2(20)

);

INSERT INTO modules VALUES('CM0001','Databases');

INSERT INTO modules VALUES('CM0003','Operating Systems');

INSERT INTO modules VALUES('CM0004','Graphics');

CREATE TABLE marks(

STUDENT_NO CHAR(8),

MODULE_CODE CHAR(8),

MARK NUMBER(2)

);

INSERT INTO marks VALUES(20060101,'CM0001',80);

INSERT INTO marks VALUES(20060101,'CM0002',65);

INSERT INTO marks VALUES(20060101,'CM0003',50);

INSERT INTO marks VALUES(20060102,'CM0001',75);

INSERT INTO marks VALUES(20060102,'CM0003',45);

INSERT INTO marks VALUES(20060102,'CM0004',70);

INSERT INTO marks VALUES(20060103,'CM0001',60);

INSERT INTO marks VALUES(20060103,'CM0002',75);

INSERT INTO marks VALUES(20060103,'CM0004',60);

INSERT INTO marks VALUES(20060104,'CM0001',55);

INSERT INTO marks VALUES(20060104,'CM0002',40);

INSERT INTO marks VALUES(20060104,'CM0003',45);

INSERT INTO marks VALUES(20060105,'CM0001',55);

INSERT INTO marks VALUES(20060105,'CM0002',50);

1.RENAME students TO student_t;

2.ALTER TABLE student_t

DROP COLUMN FORENAME;

3.ALTER TABLE student_t ADD (FORENAME VARCHAR2(10));

(ORA-01400: NULL을 ("C##ORA_USER"."STUDENT_T"."STUDENT_NO") 안에 삽입할 수 없습니다)

*이렇게 하면 안됨!!

INSERT INTO student_t(FORENAME) VALUES('Charles');

INSERT INTO student_t(FORENAME) VALUES('dafydd');

INSERT INTO student_t(FORENAME) VALUES('emile');

INSERT INTO student_t(FORENAME) VALUES('thomas');

INSERT INTO student_t(FORENAME) VALUES('robert');

*정답

UPDATE student_t

SET forename = 'charles'

WHERE student_no = '20060101';

UPDATE student_t

SET forename = 'dafydd'

WHERE student_no = '20060102';

UPDATE student_t

SET forename = 'emile'

WHERE student_no = '20060103';

UPDATE student_t

SET forename = 'thomas'

WHERE student_no = '20060104';

UPDATE student_t

SET forename = 'robert'

WHERE student_no = '20060105';

4.ALTER TABLE student_t ADD CONSTRAINT u_forename UNIQUE(forename);

5.ALTER TABLE student_t MODIFY forename NOT NULL;

6.ALTER TABLE student_t DROP PRIMARY KEY;

7.ALTER TABLE student_t ADD CONSTRAINT pk_student_t PRIMARY KEY(STUDENT_NO);

8.ALTER TABLE student_t ADD CONSTRAINT c_student_no CHECK(STUDENT_NO BETWEEN 20060001 AND 20069999);

9.ALTER TABLE marks RENAME COLUMN MARK TO MARKS;

10.ALTER TABLE marks MODIFY COLUMN(MARKS NUMBER(2,0));

11.INSERT INTO marks VALUES(CM0002,'Middle Ware');

12.ALTER TABLE marks ADD CONSTRAINT fk_marks FOREIGN KEY(MODULE_CODE) REFERANCE modules(MODULE_CODE);

13.INSERT INTO marks VALUES(20060105,'CM0006',65);

+ Recent posts