[HAE Cloud] [DAY 1-2] Database

1-2일차가 빠르게 지나갔다.

적응 기간이지만, JOIN 부분과 수요일 부터 배울 DCL, DDL 부분은 상당히 중요할 것으로 보인다.

create database [데이터베이스 명]
use [데이터베이스 명]

5 SELECT
1 FROM
2 WHERE
3 GROUP BY
4 HAVING
6 ORDER BY

use mysql;

create database lim;

use lim;

 

-- emp table에서 부서번호 별로 그룹화한후 월급의 평균을 조회하시오

단 동일한 부서번호가 4번 이상인 것만 조회

 

select deptno, avg(sal)

from emp

where count(deptno) >= 4

group by deptno

 

-- sub query

--- emp table에서 사원명이 밀러인 사원과 동일한 부서의 사원명을 조회

 

select ename

from emp

where deptno = (select deptno from emp where ename='MILLER')

 

where sal >= all(select sal from emp)

where sal >= (select MAX(sal) from emp)

 

-- EMP 테이블에서 스캇보다 더 큰 sal의 값을 갖는 사원의 ENAME, SAL 조회

 

SELECT ENAME, SAL

FROM EMP

WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SCOTT')

 

<EQUI JOIN>

SELECT ENAME, DNAME, LOC

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

 

<NON EQUI JOIN>

 조인 조건이 부등호인 경우

EMP TABLE에 SAL = 급여.

SALGRADE TABLE의 GRADE : 등급, LOSAL , HISAL : 등급의 최소/최대 급여

 

이 경우의 사원 이름과 등급을 조회하고자 하는 경우

 

SELECT ENAME, GRADE

FROM EMP, SALGRADE

WHERE EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL;

(이 경우 테이블 이름 빼도 상관 없음. 각 열은 각 테이블에만 존재하기 때문에, NOT AMBIGUOUS)

<**SELF JOIN>

=> 하나의 테이블을 가지고 조인을 수행

=> 이 경우 동일한 테이블을 사용하므로 반드시 테이블 이름을 수정해서 사용해야 한다

=> 하나의 테이블에 동일한 의미를 갖는 칼럼이 2개 이상 존재하는 경우 사용

 

EMP T에는 empno가 사원번호, MGR이 매니저 사원번호

 

ENAME : SMITH --> HIS MANAGER NAME?

 

하나의 테이블에서만 데이터를 가져올 수 있다면 서브쿼리로 해결이 가능하다

 

SELECT ENAME

FROM EMP

WHERE EMPNO = (SELECT MGR FROM EMP WHERE ENAME='SMITH');

EMP 테이블에서 모든 사원의 이름과 각 사원의 매니저 이름을 찾아오기 --> SELF JOIN

 

SELECT E1.ENAME 사원명, E2.ENAME 관리자명

FROM EMP E1, EMP E2

WHERE E1.MGR = E2.EMPNO;

 

--> 친구 추천에 활용됨

 

ID, FID

A, B

A, C

B, C

B, D

C, A

C, B

D, B

 

친구 찾기

친구의 친구 찾기

친구의 친구 중 내 친구가 아닌 사람들 찾기

 

--> 상품 추천에서도 자주 활용되는 방법

 

WHERE B.FID = A.ID AND ...

-- EMP 테이블에서 사원 이름과 관리자가 근무하는 부서 번호를 조회

 

SELECT E1.ENAME, E2.DEPTNO

FROM EMP E1, EMP E2

WHERE E1.MGR = E2.EMPNO

<ANSI JOIN>

- ANSI CROSS JOIN : CATESIAN PRODUCT와 동일한 효과

 

FROM TABLE_NAME CROSS JOIN TABLE_NAME

 

<INNER JOIN : EQUI JOIN과 결과는 동일>

- 양쪽 테이블에 모두 존재하는 데이터만 결합

 

FROM TABLE_NAME INNER JOIN TABLE_NAME

ON JOIN_CONDITION

 

JOIN_CONDITION에서 컬럼 이름이 동일한 경우 ON 대신 USING이라고 쓰고 컬럼 이름만 기술하는 것도 가능하며, 이 경우 조인 조건을 생략하고 INNER 대신에 NATURAL을 붙여도 된다.

 

이 경우는 조인에 참여하는 컬럼이 1번만 출력된다

 

SELECT ENAME, DNAME, LOC

FROM EMP INNER JOIN DEPT

 

USING (DEPTNO);

 

ON EMP.DEPTNO = DEPT.DEPTNO;

 

FROM EMP NATURAL JOIN DEPT

- 양쪽에 있었던 컬럼이 한번만 나옴

- WHERE절을 사용했다면 컬럼이 2번 발생함

- 연산 과정은 똑같음

 

<OUTER JOIN>

- 한쪽 테이블에만 존재하는 데이터도 조인에 참여

 

- EMP T에는 DEPTNO가 10,20,30인 데이터만 존재

- DEPT T에는 DEPTNO가 10,20,30,40인 데이터가 존재

 

오라클은 LEFT, RIGHT, FULL OUTER JOIN 존재

MARIA DB, MYSQL : FULL OUTER JOIN 미지원

해결방법 --> LEFT OUTER JOIN UNION RIGHT OUTER JOIN

 

제약조건

PK : primary key = UNIQUE, NOT NULL

FK : foreign key = 다른 테이블에서 UNIQUE. 자신의 테이블에서는 Nullable

--> full table scan을 피하기 위함