SQL DAY 5

220516 DAY 22

■ CONSTRAINTS (제약 조건)

PRIMARY KEY	하나의 테이블의 각각의 행은 구분될 수 있어야 한다. (NOT NULL+UNIQUE)
FORIEGN KEY	다른 테이블 열을 참조
UNIQUE	현재 테이블의 현재 컬럼에서 중복된 데이터가 나오면 안된다. (NULL 포함)
NOT NULL	입력할 때, 빼 먹으면 안된다.
CHECK	입력할 때, 확인을 해라.
DEFAULT	입력을 안하면 자동으로 들어가는 값

 

■ SEQUENCE : 번호표

 - 테이블이나 데이터와는 전혀 상관 없음
 - 여러 테이블이 공유할 수도 있음
 - 시퀀스이름.nextval : 시퀀스의 다음 번호 꺼내기
 - 시퀀스이름.currval : 시퀀스의 현재 번호 확인하기, nextval이 1번 이상 수행 후 사용 가능

start with : 시작 번호
increment by : 번호가 증가하는 크기
maxvalue : 마지막 번호
cycle : 마지막 번호 다음에 다시 시작 번호로 돌아간다.

create sequence [이름 마음대로]
start with [숫자 마음대로]
increment by [숫자 마음대로]
maxvalue [숫자 마음대로]
cycle;

create sequence class1 
start with 1
increment by 1
maxvalue 9999999
cycle;

 

■ CONSTRAINTS와 SEQUENCE 활용하여 테이블 만들기

create table member (
	mname varchar2(90) not null
, mtel varchar2(13) not null
, maddr varchar2(150) --nullable
, mage number(3) default 0 check( mage > 0 )
, mdate date default sysdate
);

1. mname, mtel은 null을 삽입할 수 없다. 
insert into member(mtel) values('010-1111-2222'); -- error
--NULL을 ("C##SCOTT"."MEMBER"."MNAME") 안에 삽입할 수 없습니다
insert into member(mname) values('강길동'); -- error
--NULL을 ("C##SCOTT"."MEMBER"."MTEL") 안에 삽입할 수 없습니다

2. mage 체크 제약 조건
insert into member(mname, mtel) values('강길동', '010-1111-2222'); -- error
--체크 제약조건(C##SCOTT.SYS_C008326)이 위배되었습니다
insert into member(mname, mtel, mage) values('강길동', '010-1111-2222', 0); -- error
--체크 제약조건(C##SCOTT.SYS_C008326)이 위배되었습니다
-----> 모순!!! -> mage number(3) default 0 check( mage > 0 ) -> 수정 필요!!!

3. 제약조건 확인하기
select * from user_cons_columns where table_name = 'MEMBER';

4. 제약 조건 수정하기
alter table member drop constraint SYS_C008326;
alter table member add constraint member_mage_ck check(mage >= 0);
-----> 모순 해결!!!

5. 기본키의 필요성
insert into member(mname, mtel) values('강길동', '010-1111-2222');--OK
-----> 동명 이인을 구분할 수 없다는 문제 발견!!! -> primary key 필요!!!

* primary key : 각 행을 구분할 수 있는 고유값. 없다면 만들어라!!!
 - 예) 주민번호
 - primary key를 만드는 가장 쉬운 방법 : sequence

6. 기본키 생성
truncate table member;
alter table member add mno number(5,0) primary key;
desc member; --테이블 보기

insert into member(mname, mtel) values('강길동', '010-1111-2222'); -- error
--NULL을 ("C##SCOTT"."MEMBER"."MNO") 안에 삽입할 수 없습니다

insert into member(mno, mname, mtel) values(1, '강길동', '010-1111-2222'); -- ok

7. 시퀀스로 mno 자동 생성하기
create sequence mno_seq
start with 2
increment by 1
maxvalue 99999
nocycle;

insert into member(mno, mname, mtel) 
values(mno_seq.nextval, '강길동', '010-1111-2222'); --mno 순번이 자동으로 증가

<member 테이블의 최종 모습>
create table member (
	mno number(5, 0) primary key --정수 5자리 number(5)로 해도 됨.
, mname varchar2(90) not null
, mtel varchar2(13) not null
, maddr varchar2(150) --nullable
, mage number(3) default 0 check( mage > 0 )
, mdate date default sysdate
);

 

■ JOIN

• 2개 이상의 테이블을 연결하여, data를 select하는 기법.

(1)  cross join : Cartesian Product 카티션 프로덕트

두 개 이상의 테이블에서 모든 가능한 경우의 수를 조회.

(2) foriegn key

• 하나의 테이블에서 다른 테이블을 연결하는 속성(attribute, column).
• 대부분 다른 테이블의 pk이다.
• 예) emp의 deptno(fk)는 dept의 pk이다.

(3) equi join (등가 조인)

• 동등한 조건으로 두 개 이상의 테이블을 조회 하는 것.
• 동등 비교 연산자(=)를 사용한 join 문장.
• 가장 일반적이고 가장 많이 사용하는 join 문장.
• WHERE 절에 기술되는 JOIN 조건을 검사

(4) non equi join(비등가 조인)

• 비교 연산자를 통한 join ( = 은 제외 )
• 같지 않은 모든 조건 판단

select ename, job, sal, grade
from emp, salgrade
where emp.sal >= salgrade.losal
and emp.sal <= salgrade.hisal;

--Quiz. 상기의 query를 between and로 전환하시오.
select ename, job, sal, grade
from emp, salgrade
where emp.sal between salgrade.losal and salgrade.hisal;

 

(5) 세 개 이상의 테이블 join

select ename, job, sal, grade, dname
from emp, salgrade, dept
where emp.sal between salgrade.losal and salgrade.hisal
and emp.deptno = dept.deptno;

 

(6) self join

• 하나의 테이블을 두 번 이상 사용해서 join → FROM절에서 별칭(Alias) 사용
• 예) from emp e1, emp e2

 

(7) inner join

• equi join을 ansi sql에서는 inner join으로 표현하다.
• join만 할거면 where절이 필요없다.
• where절은 데이터를 제한할 때 쓴다.

----> equi join
select ename, job, emp.deptno, dname
from emp, dept
where emp.deptno = dept.deptno;

----> equi join의 데이터 제한 : 매니저의 결과만 보기
select ename, job, emp.deptno, dname
from emp, dept
where emp.deptno = dept.deptno;
and job = 'MANAGER';

----> inner join
select ename, job, emp.deptono.dname
from emp inner join dept 
on emp.deptno = dept.deptno;

----> inner join의 데이터 제한
select ename, job, emp.deptono.dname
from emp inner join dept 
on emp.deptno = dept.deptno;
where job = 'MANAGER';

----> 아래 쿼리를 inner join으로 
select ename, job, sal, grade
from emp, salgrade
where emp.sal >= salgrade.losal
and emp.sal <= salgrade.hisal;

select ename, sal, grade
from emp inner join salgrade
on emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal;

----> 아래 쿼리를 inner join으로
select ename, job, dname, grade
from emp, dept, salgrade
where emp.deptno = dept.deptno
and emp.sal >= salgrade.losal
and emp.sal <= salgrade.hisal;

select ename, job, dname, grade
from emp inner join dept on emp.deptno = dept.deptno
inner join salgrade on emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal;

 

(8) outer join(외부 조인)

• 내부 조인은 두 테이블 모두 데이터가 있어야 결과가 나오지만, 외부 조인은 데이터가 한쪽만 있어도 결과가 나온다.

left outer join	두 개의 테이블 중, 좌측의 테이블을 중심으로 join을 수행. 무조건 좌측은 다 나온다. 우측은 join에 해당하는 내용만 나온다. from dept, emp : dept가 좌측
right outer join	두 개의 테이블 중, 우측의 테이블을 중심으로 join을 수행. 무조건 우측은 다 나온다. 좌측은 join에 해당하는 내용만 나온다. from emp, dept : dept가 우측
full outer join	• 두 개의 테이블 모두에서 데이터가 나온다. • 무조건 다 나온다.

1. LEFT OUTER JOIN
select dname, loc, emp.ename, emp.deptno
from dept left outer join emp on dept.deptno = emp.deptno;

2. RIGHT OUTER JOIN
select emp.ename, emp.deptno, dname, loc
from emp right outer join dept
on emp.deptno = dept.deptno;

3. FULL OUTER JOIN
select 고객.*, 제품.*
from 고객 full outer join 제품 on 고객.고객아이디 = 제품.제품명;

4.OUTER JOIN의 의미
예제1. 고객 중에 주문한 사람과 주문하지 않은 사람을 확인해야 함.
---->주문하지 않은 고객은 안나옴!
select 고객.고객아이디, 주문.*
from 주문, 고객
where 주문.주문고객 = 고객.고객아이디;--주문 안한 고객은 안 나옴.

---->해결 : 주문내역 null인 고객까지나옴.
select 고객.*, 주문.*
from 주문 right outer join 고객 on 주문.주문고객 = 고객.고객아이디
order by 고객.고객아이디;

예제2. 주문된 제품과 주문되지 않은 제품을 확인 해야 함. 
---->안팔린 상품은 안 나온다. 
select 제품.*, 주문.*
from 제품, 주문
where 제품.제품번호 = 주문.주문제품;--주문있었던 10개만 조인됨.

---->해결 : 안팔린 제품도 나옴
select 제품.*, 주문.*
from 제품 left outer join 주문
on 제품.제품번호 = 주문.주문제품;