oracle join

DB 정규화(Normalization)

정규화란?

한마디로 DB 서버의 메모리를 낭비하지 않기 위해서 어떤 테이블을 식별자를 가지는 여러개의 테이블로 나누는 과정을정규화라고 한다. 정규화된 데이타베이스는 중복이 최소화되도록 설계된 데이타베이스이다

장점

1. 메모리를 절약할수 있다
2. 구조화된 시스템으로 인해서 관리하기가 편하다

단점

1. 조회비율이 높은 시스템의 경우에는 테이블간의 join 연산이 반복적으로 이뤄지기 때문에 질의 응답 속도가 살짝 늦어질수 있다

오라클 조인

1. outer join : 조건에 일치하지 않아도 출 력
2. self join : 자신의 테이블과의 조인
3. equi join or inner join :기본키(primary key) 와 외부키(foreign key) 를 사용하여 join ,일치할때만 조회( 제일 많이 사용)**

Ansi 조인

ms-sql,db1 같은 오라클이 아닌 환경에서도 사용이 가능한 표준화된 조인
cross 조인,natural 조인, outer join,self join … 등등

예제

-- Join

-- 1. emp와 dept를 join해서 emp사람들의 부서명을 구해보자.
-- 사원명        부서명      
select e.empname, d.dname from emp e, dept d where e.deptno=d.deptno;
select empname, dname from emp e, dept d where e.deptno=d.deptno; -- 테이블의 고유명일경우 e. d. 생략가능 --
-- 고유한 이름은 앞에 e.d 등을 생략해도 된다.

-- student 와 professor 를 조인하여 다음과 같이 출력하시오.
-- 학번     학생명      지도교수명
select s.studno 학번, s.name 학생명, p.name 교수명  from student s, professor p where s.profno=p.profno; -- where 이후에는 같은 걸 붙여놔야 한다.

-- 학번       학생명     제1전공명       제2전공명
select s.studno 학번, s.name 학생명, d.dname 제1전공명
from student s, department d
where s.deptno1=d.deptno;

-- 교수명      직급      학과명
select p.name 교수명, p.position 직급, d.dname 학과명 from professor p, department d where p.deptno=d.deptno;

--  사원번호     사원명     근무도시
-- e1111       smith    new york
select empno 사원번호, empname 사원명, loc 근무도시 from emp e,dept d where e.deptno=d.deptno;

-- panmae prodect join
-- 판매일자     과자명     전체판매액
select pm.p_date 판매일자, pd.p_name 과자명, pm.p_total 전체판매액 
from panmae pm, product pd 
where pm.p_code=pd.p_code;

select * from panmae;
select * from product;
select * from department;
select * from student;
select * from professor;
select * from emp;
select * from dept;

foreign 키 지정 (부모, 자식 table)

on delete cascade

외부키로 테이블이 연결되어있다 하더라도부모테이블의 데이타를 삭제하면 자식테이블의 데이타까지도 자동으로삭제시켜주는 기능

-- primary키와 poreign키를 생성한 조인테이블(부모, 자식) 만들어보기

-- 시퀀스 생성 (sequence 생성)
create sequence seq_shop;

-- shop테이블 생성(부모 테이블)
create table shop(shop_num number(5) primary key, sangname varchar2(30), color varchar2(20));

-- 5개 정도 상품추가
insert into shop values(seq_shop.nextval, '요가매트' , 'pink');
insert into shop values(seq_shop.nextval, '아령' , 'black');
insert into shop values(seq_shop.nextval, '레깅스' , 'beige');
insert into shop values(seq_shop.nextval, '져지' , 'white');
insert into shop values(seq_shop.nextval, '양말' , 'yellow');
commit;

-- cart 1 (자식 테이블)... 상품정보는 shop 테이블의 shop_num값을 외부키로 지정 (겹치지 않아야 한다. primary key가 된다.)
create table cart1(idx number(5) primary key, shop_num number(5) constraint cart_fk_shopnum references shop(shop_num), cnt number(5), guipday date);
-- constraint  : 여기에다가 제약조건을 줄께 라는 의미
-- references : 서로 연결을 시켜준거다. 뒤에꺼가 앞에꺼랑 같은 의미야...

-- cart 2 (자식 테이블)... 상품정보는 shop 테이블의 shop_num값을 외부키로 지정 (겹치지 않아야 한다. primary key가 된다.)
-- 부모테이블의 상품을 지우면 그 상품과 연결된 카트를 자동으로 지워준다 (on delete cascade)
create table cart2(idx number(5) primary key, shop_num number(5) references shop(shop_num) on delete cascade, cnt number(5), guipday date);

-- cart1에 상품 추가 _1번
insert into cart1 values(seq_shop.nextval,1,2,sysdate);
insert into cart1 values(seq_shop.nextval,3,4,sysdate);
insert into cart1 values(seq_shop.nextval,6,4,sysdate); -- 무결성 요류 발생. 부모 테이블에 6번이 없기 때문.
insert into cart1 values(seq_shop.nextval,3,4,sysdate);
insert into cart1 values(seq_shop.nextval,2,1,sysdate);
insert into cart1 values(seq_shop.nextval,4,1,sysdate);
insert into cart1 values(seq_shop.nextval,5,8,sysdate);

delete from cart1 where shop_num=4;
delete from cart1 where shop_num=3;

-- cart2에 상품추가
insert into cart2 values(seq_shop.nextval,4,19,sysdate);
insert into cart2 values(seq_shop.nextval,2,1,sysdate);
insert into cart2 values(seq_shop.nextval,5,4,sysdate);
insert into cart2 values(seq_shop.nextval,1,6,sysdate);
insert into cart2 values(seq_shop.nextval,3,7,sysdate);

delete from cart2 where shop_num=4;

-- cart1은 부모 1,2,5 가 존재,   cart2는 부모 1,2,3,5 가 있다.

-- shop의 1번 상품 삭제?
delete from shop where shop_num=1; -- 무결성 제약조건 위배 : 자식 레코드에 있으면 삭제 불가.
delete from shop where shop_num=3; -- on delete cascade 사용하면 자식 테이블에 상관없이 부모껄 삭제하면 삭제된다.

-- Join 가즈아!!
-- cart1에 담긴 상품을 shop 테이블과 join해서 자세히 출력해보자.
-- 방법 1
select c1.idx, s.shop_num, s.sangname,s.color, cnt, c1.guipday from shop s,cart1 c1 where s.shop_num=c1.shop_num; -- 앞에 단축키 붙이는거 (c1,s) 는 숫자는 안된다.

-- 방법 2
select idx, s.shop_num, sangname, color, cnt, c1.guipday from shop s,cart1 c1 where s.shop_num=c1.shop_num; -- 앞에 단축키 붙이는거 (c1,s) 는 숫자는 안된다.

select * from shop;
select * from cart1;
select * from cart2;

-- 부모
-- Menu 테이블명
-- m_num : 기본키
-- m_name : 음식명
-- price : 가격
--
-- 자식
-- MyOrder 테이블명
-- o_num:기본키
-- m_num: 외부키
-- cnt : 숫자
-- o_date: 현재 날짜

-- 부모테이블
create table menu(m_num number(5) primary key, m_name varchar2(30), price number(20));
create sequence seq_menu;

insert into menu values(seq_menu.nextval,'우동',2000);
insert into menu values(seq_menu.nextval,'돈까스',4000);
insert into menu values(seq_menu.nextval,'김밥',1000);
insert into menu values(seq_menu.nextval,'라면',2500);
insert into menu values(seq_menu.nextval,'만두',1500);

-- 자식테이블(자식테이블에 연결된 부모가 있어도 부모를 삭제할 수 있으며, 부모테이블삭제시 자동으로 자식테이블이 삭제되어야 한다.)
create table myorder(o_num number(10) primary key, 
m_num number (5) constraint myorder_fk_m_name references menu(m_num) on delete cascade, 
cnt number(10), o_date date);

insert into myorder values(seq_menu.nextval,22,3,sysdate);
insert into myorder values(seq_menu.nextval,24,2,sysdate);
insert into myorder values(seq_menu.nextval,25,2,sysdate);
insert into myorder values(seq_menu.nextval,21,1,sysdate);
insert into myorder values(seq_menu.nextval,23,4,sysdate);

-- 최종출력
-- 주문번호     음식명     가격(원단위)      개수(개)      주문일자.

select o_num 주문번호, m_name 음식명, to_char(price,'L999,999') "가격" , cnt||'개' 개수, o_date "주문일자" from menu me, myorder mo where me.m_num=mo.m_num;

commit;

select * from menu;
select * from myorder;

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

create table board (bno number(5) constraint board_pk_bno primary key, writer varchar2(20), subject varchar2(100), writeday date);

-- bord에 5개씩 insert 시키세요
insert into board values(seq_sawon.nextval,'임형준','안녕 클레오파트라',sysdate);
insert into board values(seq_sawon.nextval,'이성신','7강의실 스타 1타',sysdate);
insert into board values(seq_sawon.nextval,'박병주','크아왕',sysdate);
insert into board values(seq_sawon.nextval,'김선범','술꾼',sysdate);
insert into board values(seq_sawon.nextval,'김석진','내가 무릎이다',sysdate);

-- answer 라는 댓글(자식) 생성. -> 부모글 지우면 자식글 삭제되도록 합시다.

create table answer (num number(5) constraint anser_pk_num primary key, bno number(5) constraint answer_fk_bno references board(bno) on delete cascade, 
nickname varchar2(20), content varchar2(30), writeday date);

insert into answer values(seq_sawon.nextval,18,'진평이','선범아 나 오늘 데이트 해야돼..',sysdate);
insert into answer values(seq_sawon.nextval,18,'성신','오늘 숙제해야돼..',sysdate);
insert into answer values(seq_sawon.nextval,18,'성혀니','나 메이플 시작했어..',sysdate);
insert into answer values(seq_sawon.nextval,18,'병주','크아 이기면 나감 ㅅㄱ..',sysdate);
insert into answer values(seq_sawon.nextval,19,'현규','구라 ㄴ ',sysdate);
insert into answer values(seq_sawon.nextval,15,'희찬','세상에서',sysdate);
insert into answer values(seq_sawon.nextval,16,'현규','맞다이 고고 ',sysdate);

-- join 출력
-- 원글번호     작성자     작성자글제목      댓글단사람       댓글내용        원글날짜        댓글날짜    
select b.bno 원글번호, b.writer 작성자, b.subject 글제목, a.nickname 댓글단사람, a.content 댓글내용, b.writeday 원글날짜, a.writeday 댓글날짜 from board b, answer a where b.bno=a.bno;

delete from board where bno=18;

select * from board;
select * from answer;

-- board 를 먼저 삭제
select * from board;  

drop table board;-- 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다 라는 오류와 함께 삭제 불가함..

drop table answer;

drop table board; -- 자식 삭제하고 나니까 부모도 삭제 됌.

drop table cart1;
drop table cart2;
drop table shop;

-- 외부키로 연결이 된 경우 자식테이블 먼저 삭제 후 부모테이블 삭제 가능하다.

commit;

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

-- 배달의 민족
--==============================
-- 시퀀스: seq_food
-- 부모
-- food 테이블명
-- fno : 기본키 (시퀀스)
-- foodname : 문자열
-- price : 숫자
-- shopname : 상호명(문자열)
-- loc : 가게 위치 (문자열)
--==============================
-- 자식
-- jumun 테이블 명
-- num 숫자(시퀀스)
-- name : 주문한 사람 이름 (문자열)
-- fno : 외부키
-- addr : 배달 주소 (문자열)

-- 출력
-- 주문번호     주문자     음식명     가격      상호명     가게위치        배달주소        
-- 주문자 이름이 오름차순으로 출력 gogo

create sequence seq_food;

create table food (fno number(10) primary key, foodname varchar2(20),  price number(10), shopname varchar2(20), loc varchar2 (50));

insert into food values(seq_food.nextval,'떡볶이',4000,'쌍용떡볶이','서울시 강남구 역삼동');
insert into food values(seq_food.nextval,'족발',4000,'가족같은 족발','서울시 동작구 상도동');
insert into food values(seq_food.nextval,'짜장면',4000,'니취뽤러마','서울시 관악구 청룡동');
insert into food values(seq_food.nextval,'피자',4000,'표정좀피자','서울시 강동구 천호동');
insert into food values(seq_food.nextval,'부대찌개',4000,'부대차렷','서울시 중랑구 중화동');
insert into food values(seq_food.nextval,'마라탕',4000,'하지마라','서울시 성북구 미아동');

create table jumun (num number(10), name varchar2(20), fno number(10) constraint food_pk_fno references food(fno) on delete cascade, addr varchar2(50));

insert into jumun values(seq_food.nextval,'임형준',2,'서울시 관악구 봉천동');
insert into jumun values(seq_food.nextval,'이성신',5,'서울시 광진구 구의동');
insert into jumun values(seq_food.nextval,'김갑수',5,'서울시 강서구 목동');
insert into jumun values(seq_food.nextval,'이동진',6,'서울시 노원구 노동');
insert into jumun values(seq_food.nextval,'박희찬',1,'서울시 마포구 서교동');
insert into jumun values(seq_food.nextval,'경성수',1,'서울시 서초구 기모동');
insert into jumun values(seq_food.nextval,'민락동',1,'서울시 동작구 숭실동');

select f.fno 주문번호, name 주문자, foodname 음식명, to_char(price,'L999,999,999') 가격, shopname 상호명, loc 가게위치, addr 주문자주소 from food f, jumun j where f.fno=j.fno order by name asc;

commit;

select * from food;
select * from seq_food;