[SQL]SQL -Day.2

Oracle

[SQL]SQL -Day.2

MoZZANG 2022. 3. 28. 22:15

JOIN문

1. INNER JOIN

두 개 이상의 테이블로부터 자료를 검색하기 위해서 join을 사용한다.
일반적으로 primary key(PK)와 foreign key(FK)를 이용하여 join하는 경우가 대부분이다.
가장 많이 사용되는 조인문으로 테이블 간에 연결 조건을 모두 만족하는 행을 검색하는데 사용한다
검색시 검색되는 Column이 join하는 테이블 모두에 존재한다면 반드시 컬럼명에 테이블 이름을 "테이블명.컬럼명"의 형태로 기술해야 한다.
INNER JOIN은 자식테이블(FK가 있는 테이블)을 기준으로 모든 데이터를 가져온다. 예를들면 자식테이블에 전체 레코드가 10개이면 INNER JOIN한 결과의 레코드 수도 10개가 출력된다.

<표준 SQL식 INNER JOIN>

SELECT 컬럼명.. FROM 테이블명1 (Inner) JOIN 테이블명2 ON 테이블명1.PK컬럼명 = 테이블명2.FK컬럼명

▲ ename,sal,deptno는 emp테이블에 있는 컬럼들이지만 dname컬럼은 emp테이블에는 없고 dept테이블에만 있다. 나는 emp테이블에 있는 컬럼들과 dept테이블에 있는 컬럼들을 같이 출력하고 싶기 때문에 Join을 이용하여 두 테이블을 연관지어줘야한다. 이를 '부모와 자식관계로 만든다' 라고도 한다.

join을 할 때 소소한 팁이 있다면 SELECT하고 싶은 컬럼들 중에서 다수의 컬럼이 속해있는 테이블을 메인으로 잡고 그러니까 FROM뒤에 오게하고, 소수의 컬럼이 속해있는 테이블을 sub로 잡으면 된다. 즉, join 뒤에 적으면 된다는 것이다.

ON 뒤에는 연결고리를 만들어주어야 한다. emp테이블과 dept테이블이 공통적으로 가지고 있는 컬럼을 서로 연결시켜준다고 생각하면 좋다.

▲ dept테이블을 보면 각 deptno에 해당하는 dname(부서명)과 loc(위치)를 데이터로 가지고 있으며

▲emp테이블에서의 deptno는 결국 dept테이블의 부서정보를 참조하고 있는 것이다.

즉 deptno를 기준으로 보면 dept테이블이 부모가 되는 것이며 emp는 부모인 dept에서의 deptno를 참조하고 있는 자식이 되는 것이다.

또한 SELECT한 컬럼명들 중에서 deptno는 emp테이블에도 존재하고, dept테이블에도 존재하는 것이므로 어느테이블에 있는 deptno를 가져올 것인지를 정해서 마침표(.)로 구분 및 연결해주어야 한다. 나머지 ename,sal,dname은 어느 한쪽 테이블에만 존재하는 컬럼이므로 구분해주지 않다도 된다.

자식이 참조하고 있는 부모의 컬럼 데이터값은 삭제할 수 없다는 점도 꼭 기억하자

1-2. 테이블에 별칭 부여

테이블에 별칭을 부여하여 긴 테이블 명을 간단하게 사용한다.
그리하여 테이블 이름대신 별칭을 사용한다.
SQL코드를 적게 사용하여 코딩시간이 절약되고 메모리를 보다 적게 사용할 수 있기 때문이다.
테이블 별칭은 30자까지 사용가능하지만 짧을 수록 좋다.
테이블명 별칭부여시에는 AS 사용불가하다.

JOIN을 여러개도 할 수 있다. 규칙과 형식만 잘 지키면 말이다

2. OUTER JOIN

OUTER JOIN은 INNER JOIN과는 달리 두 테이블에 JOIN조건이 정확히 일치하지 않더라도 어느 한쪽 테이블에서 결과값을 모두 가져오는 JOIN문이다.
OUTER JOIN문을 사용할 때는 반드시 OUTER 전에 데이터를 어느 쪽 테이블에서 가져올지 즉, 왼쪽인지 오른쪽인지 아니면 양쪽(FULL)인지 반드시 기술해야한다.
자식을 기준으로 OUTER JOIN을 하면 INNER JOIN과 같다. 그래서 보통 부모를 기준으로 OUTER JOIN한다.
공통 Column도 부모 기준으로 SELECT한다.
관계형 데이터베이스에서는 FUll OUTER JOIN은 의미없다,

LEFT(RIGHT) OUTER JOIN

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

SELECT 컬럼명... FROM 테이블명1 LEFT(RIGHT) OUTER JOIN 테이블명2 ON join조건

▲ dept에는 deptno가 40인 dname이 OPERATIONS 도 있다. 하지만 emp테이블에서는 deptno가 40인 데이터는 없다.

LEFT OUTER JOIN이므로 JOIN문 기준 왼쪽에 있는 테이블에서 일치하지 않아도 정보를가져오므로 13행과 같은 결과가 출력될 수 있는 것이다.

▲만약 위 코드처럼 LEFT를 RIGHT로 바꾸면 아래와 같은 결과가 나온다.

▲JOIN문 기준 오른쪽에 있는 테이블인 emp테이블에서 데이터를 가져오기 때문에 존재하지 않는 deptno40에 대한 데이터는 없는 것이다.

▲ 조건을 잘 만들면 위와 같은 결과도 출력할 수 있다

TOP 쿼리

얻어진 질의 결과에서 위에서부터 순서대로 몇 개만 가져오는 경우에 사용한다.
데이터가 입력된 순서대로 혹인 서브쿼리에 의해 생성된 테이블에 레코드가 생성된 순서대로 내부적으로 번호가 순차적으로 부여되고 그 부여된 번호는 ROWNUM(행번호 출력하는 컬럼)이라는 컬럼에 내부적으로 저장되어 있다.

STEP1) 서브쿼리안의 서브쿼리는 무조건 정렬해야하며 특정 컬럼(보통 PK컬럼)으로 ORDER BY DESC 그리고 생성된 테이블 별칭을 부여한다.
STEP2) 서브쿼리안의 SELECT절에서 STEP1의 별칭.* , ROWNUM컬럼별칭을 기술한다.
STEP3) 밖의 WHERE절에서 ROWNUM을 별칭한 이름으로 BETWEEN a AND b한다.

▲ 첫번째 코드는 낮은 연봉 TOP3를, 두번째 코드는 높은 연봉 TOP3를 출력하는 코드이다.

▲ 위 예제를 TOP쿼리로 풀어보면 아래와 같다

테이블 생성

DDL문(Data Definiton Language) :

Create(생성),Alter(수정),Drop(삭제)를 통해 새로운 Data를 만들 수 있다. 여기서 말하는 data는 단순한 컬럼안의 데이터들이 아니라 DBMS에서 관리하는 개체 예를들면 Table,View 혹은 StoredProcedure등을 의미한다.

1. 생성 방법

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

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

- 문자로 시작한다.

- 30자 이내로 지정한다.

- 동일한 이름을 사용할 수 없다. 또한 예약어도 사용할 수 없다.

- 대소문자를 구별하지 않는다.

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

기본 키(Primary Key) :

- 참조 무결성을 유지하기 위한 제약조건이다.

- 하나의 테이블에는 하나의 PK만 존재한다.

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

-NOT NULL과 UNIQUE가 자동 생성된다.

[PRIMARY KEY 예]

CREATE TABLE 테이블명(
컬럼명1 자료형1 [CONSTRAINT PK제약명] PRIMARY KEY, (=>컬럼명1을 기본키로 설정함)
-------------------
컬럼명N 자료형N
)

▲ 위와 같이 YESPK라는 이름의 테이블을 생성하면서 ID를 PRIMARY KEY 즉, 기본키로 설정하면서 그 PK제약명을 PK_YESPK로 정하였다. PRIMARY KEY로 설정하면 해당 컬럼은 NOT NULL 즉, NULL이 될 수 없고, UNIQUE 즉, 유일해야하므로 중복되어서 저장될 수 없게 자동 setting이 된다.

또한 만약에 CONSTRAINT를 통해 PK제약명을 설정하지 않으면 ORACLE에서 자동으로 PK제약명을 부여해준다.

데이터를 몇개씩 넣으면서 좀 더알아보자

▲ DDL문에서 데이터를 입력할 때는 INSERT INTO ~ VAlLUES 구문을 하용한다.

1행에서 ID를 KIM, 비밀번호를 1234, 이름을 김길동으로 입력했다.

ID는 우리가 PRIMARY KEY로 정하였기때문에 기존에 KIM이라는 ID로 저장되어있던 데이터를 지우지 않는한 똑같은 ID를 가지는 데이터는 입력할 수 없다.

만약 동일한 ID로 저장하려고 하면 아래와 같은 오류가 발생한다.

▲ 노란색부분이 우리가 CONSTRAINT를 이용해 정한 PK제약명이다..

▲ 만약 위 코드처럼 CONSTRAINT로 PK제약명을 정해주지 않으면 중복입력오류 발생 시 아래와 같이 나오게된다.

▲ 프로그램 자체에서 제약명을 부여해준다.

▲ ID가 다르면 비밀번호나 이름이 동일해도 다른 데이터값으로 취급한다.

▲ 출력해보면 잘 저장된 것을 볼 수 있다.

▲ PRIMARY KEY를 하나의 컬럼만이 아닌 여러개의 컬럼을 묶어서 지정할 수도 있다. 위 처럼 COL1과 COL3을 묶어서 PRIMARY KEY로 지정하게 되면 COL1과 COL3이 모두 같아야 중복으로 인식이 된다.

▲ COL1이 다 똑같아도 COL3이 다르므로 동일한 키로 인식되지 않기때문에 잘 입력이 된다.

▲PRIMARY KEY가 되면 자동으로 NOT NULL과 UNIQUE가 설정되기 때문에 COL3을 NULL로 해서 입력할 수는 없다.

이번에는 부모와 자식관계가 있는 테이블들을 만들어보자

FOREIGN KEY : 외래키로 참조 무결성을 유지하기 위한 제약조건이다.
FOREIGN KEY 예]

방법1)
CREATE TABLE 테이블명(
컬럼명1  자료형1 PRIMARY KEY
, 컬럼명2  자료형2  [CONSTRAINT 제약조건명] REFERENCES 부모테이블(부모테이블의 PK컬럼)
, ------------------
컬럼명3 자료형3 [CONSTRAINT 제약조건명] REFERENCES 부모테이블2(부모테이블2의 PK컬럼)
컬럼명N 자료형N
)

방법2)
CREATE TABLE 테이블명(
컬럼명1  자료형1 PRIMARY KEY
, 컬럼명2  자료형2
, ------------------
컬럼명N 자료형N ,
[CONSTRAINT 제약조건명] FOREIGN KEY(컬러명2) REFERENCES 부모테이블(부모테이블의 PK컬럼)

)

*FOREIGN 생성시 부모의 컬럼과 자료형이 일치해야 한다.
*FOREIGN KEY 생성시  ON DELETE CASCADE 혹은 ON DELETE SET NULL조건을 추가할수 있다.
단, ON UPDATE CASCADE는 없음(오라클에서)

예]
컬럼명2  자료형2 [CONSTRAINT 제약조건명] REFERENCES 부모테이블(부모테이블의 PK컬럼)
ON DELETE CASCADE  (부모 레코드 삭제시 자식까지 삭제됨)

혹은

[CONSTRAINT 제약조건명] FOREIGN KEY(컬러명2)
REFERENCES 부모테이블(부모테이블의 PK컬럼)
ON DELETE SET NULL(부모 레코드 삭제시 자식레코드값이  NULL로 설정됨)