22일차 2023-04-06

oracle

22일차 2023-04-06

choi Hoon 2023. 4. 6. 18:36

1. OUTER JOIN

OUTER JOIN은 INNER JOIN과는 달리 두 테이블에 조인 조건이 정확히 일치하지 않더라도
어느 한쪽 테이블에서 결과값을 모두 가져오는 JOIN문

OUTER JOIN문을 사용할때는 반드시 OUTER 전에 데이터를 어느 쪽 테이블에서
가져올지 즉 왼쪽인지(LEFT) 오른쪽인지(RIGHT) 아니면 양쪽 테이블(FULL)인지
반드시 기술. (NULL값을 가져올 수 있음)

자식을 기준으로 아웃터 조인하면 INNER조인과 같다.
그래서 보통 부모를 기준으로 아웃터 조인한다.
공통 컬럼도 부모 기준으로 SELECT 한다
관계형 데이타베이스에서는 Full outer조인은 의미 없다

-LEFT OUTER JOIN

JOIN문 왼쪽을 중심으로 왼쪽에 있는 테이블에서 데이터를 모두 가져온다.

사용구문:

     SELECT 컬럼명…..
     From 테이블명1  LEFT OUTER JOIN 테이블명2
     ON 조인 조건

-RIGHT OUTER JOIN

JOIN문 오른쪽을 중심으로 오른쪽에 있는 테이블에서 데이터를 모두 가져온다.

     SELECT 컬럼명…..
     From 테이블명1  RIGHT OUTER JOIN 테이블명2
     ON 조인 조건

-FULL OUTER JOIN

관계형 데이타베이스에서는 Full outer조인은 의미 없다

JOIN문 양쪽에 있는 모든 테이블에서 데이터를 모두 가져온다.

     SELECT 컬럼명…..
     From 테이블명1  FULL OUTER JOIN 테이블명2
     ON 조인 조건

2. 서브커리

-서브쿼리는 다른 하나의 SQL문장 안에 기술된 SELECT문장을 말한다.

  -서브쿼리는 괄호로 묶어야 한다.

  -서브쿼리만을 단독 실행 시 실행이 되어야 한다.

  -두 종류의 연산자가 서브쿼리에 사용된다.

   =,>,>=,<,<=,<>,!=(단일행 연산자)  혹은 IN, NOT IN(복수행 연산자)등

  -서브퀴리는 연산자의 오른쪽에 기술되어야 한다.

  -단일행 서브 쿼리에는 단일행 연산자를 다중행 서브쿼리에는 복수행 연산자를 사용한다

  -서브쿼리는 SELECT절, FROM절 WHERE절 등에 위치할 수 있다.

3. 탑커리

- 얻어진 질의 결과에서 위에서부터 순서대로 몇 개만 가져오는 경우에 사용함

- 데이타가 입력된 순서대로 혹은
  서브퀴리에 의해 생성된 테이블에 레코드가 생성된 순서대로
  내부적으로  번호가 순차적으로 부여되고 그 부여된 번호는
  ROWNUM이라는 컬럼에 내부적으로 저장되어 있다.

행의 데이터를 rownum이라는 칼럼으로 위에서 몇 개를 선택하여 가져오는 것

-서브쿼리 이용

- 위에서부터 3개

Select * From (
Select * From 테이블명 Order By PK컬럼명 Desc
)
Where rownum <= 3 ORDER BY PK컬럼명 Desc;

- 특정 구간에 있는 레코드 (Between A and B 이용)

SELECT *
FROM
(
Select a.*,rownum num -서브쿼리
FROM (
Select * from employees order by salary desc(서브쿼리 안의 서브쿼리)
) a
)
WHERE num between 11 and 20

방법:

STEP1] 서브쿼리 안의 서브쿼리는 특정 컬럼(보통 PK컬럼)으로 ORDER BY DESC
그리고 생성된 테이블 별칭을 부여

STEP2] 서브쿼리안의 SELECT절에서 STEP1의 별칭.*,ROUNUM 컬럼별칭
을 기술한다

STEP3] 밖의 WHERE절에서 ROWNUM을 별칭한 이름으로 between a and b 한다.

3. 테이블 생성

3-1). 생성 방법

CREATE TABLE  테이블명(
컬럼명1  자료형1 [NOT NULL],
컬럼명2  자료형2 [NOT NULL],
------------------
컬럼명N 자료형N
[,PRIMARY KEY (컬럼명)]
)

3-2). 테이블 이름 및 컬럼 명명규칙

-문자로 시작한다
-30자 이내로 지정한다
-동일한 이름을 사용할 수 없다 또한 예약어도 사용할 수 없다
-대소문자를 구별하지 않는다

3-3). 테이블 생성 시 제약 조건

기본 키(Primary Key) :
-참조무결성을 유지하기 위한 제약조건이다

=> 참조무결성이란 pk값 (10,20,30)이 있는데 fk로 40을 참조하려 하면 오라클이 참조하지 못한다고 오류가 뜬다)

-하나의 데이블에는 하나의 PK만 존재한다.(고유)

=>한 레코드에 대해 두 개의 값이 존재하면 레코드 간 관계를 설정하기 복잡해짐. 이럴 땐 복합키 생성이 더 좋음

-PK로 설정되면 그 칼럼은 값이 중복되거나 NULL을 허용하지 않는다.

=> 중복 방지(not duplicate)와 값이 존재 (not null)를 말함.

- PK생성 시 인덱스가 자동으로 생성된다.(이건 일단 이렇게 기억 나중에 공부)

-pk를 한 개

-2개 칼럼으로 묶어 pk 한 개

=> 코드 끝에 PK_CLUSTER_TBL(COL2,COL3) VALUES('DATA3',SYSDATE);

이렇게 COL2와 COL3를 AND 조건으로 묶어서 두 개 값이 다 같아야 아래 코드(SELECT)를 할 수 있다.

(INSERT도 마찬가지 다르면 들어갈 수 있음. 하지만 같으면 데이터 삽입 불가능)

create table nopk(
 col1 NUMBER,
 col2 NVARCHAR2(10)

);
desc nopk
insert into nopk values(1,'data1');
insert into nopk values(1,'data2');
insert into nopk(col2) values('data3');
select * from nopk;

create table pk_noname_tbl(
    col1 NUMBER primary key,
    col2 NVARCHAR2(10)
);

insert into pk_noname_tbl values(1,'data1');
insert into pk_noname_tbl values(1,'data2');
insert into pk_noname_tbl(col2) values('data3');
select * from pk_noname_tbl;



create table pk_name_tbl(
    col1 NUMBER CONSTRAINT pk_pk_ctname_tbl primary key,
    col2 NVARCHAR2(10)
);

insert into pk_name_tbl values(1,'data1');
insert into pk_name_tbl values(1,'data2');
insert into pk_name_tbl(col2) values('data3');
select * from pk_name_tbl;



create table PK_CLUSTER_TBL(
    COL1 NUMBER ,
    COL2 VARCHAR2(10),
    COL3 DATE,
    CONSTRAINT PK_PK_CLUSTER_TBL PRIMARY KEY(COL1,COL2)
);
INSERT INTO PK_CLUSTER_TBL VALUES(1,'DATA1',SYSDATE);
INSERT INTO PK_CLUSTER_TBL VALUES(1,'DATA2',SYSDATE);
INSERT INTO PK_CLUSTER_TBL VALUES(2,'DATA1',SYSDATE);
SELECT * FROM pk_cluster_tbl WHERE COL1=1 AND COL2='DATA1';
INSERT INTO PK_CLUSTER_TBL VALUES(2,'DATA1',SYSDATE);
INSERT INTO PK_CLUSTER_TBL(COL2,COL3) VALUES('DATA3',SYSDATE);
INSERT INTO PK_CLUSTER_TBL(COL1,COL3) VALUES('100',SYSDATE);
select * from PK_CLUSTER_TBL;