JAVA 풀스택 33일차 - Oracle DB 3

1. JOIN

두 개 이상의 테이블을 연결해서 조회하는 방법

- 테이블 하나에 모든 정보가 들어있지 않을 때 사용

- 공통된 컬럼 값을 기준으로 연결

SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

2. INNER JOIN

두 테이블에서 조건이 일치하는 데이터만 조회

SELECT DEPT_CODE, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

- EMPLOYEE.DEPT_CODE와 DEPARTMENT.DEPT_ID가 같은 데이터만 조회

3. OUTER JOIN

일치하지 않은 데이터도 포함해서 조회

3-1 LEFT JOIN

왼쪽(FROM) 테이블의 데이터는 모두 조회

SELECT DEPT_CODE, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

- EMPLOYEE 기준으로 모두 조회

3-2 RIGHT JOIN

오른쪽 테이블의 데이터는 모두 조회

SELECT DEPT_CODE, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
RIGHT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

- DEPARTMENT 기준으로 모두 조회

3-3 FULL JOIN

양쪽 테이블 데이터를 모두 조회

SELECT DEPT_CODE, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
FULL JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

- 일치하는 데이터 + 일치하지 않는 데이터는 모두 포함

4. Oracle 전용 OUTER JOIN 문법

4-1 LEFT JOIN

SELECT DEPT_CODE, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+);

4-2 RIGHT JOIN

SELECT DEPT_CODE, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID;

5. CROSS JOIN

두 테이블의 모든 조합을 조회

SELECT EMP_NAME, NATIONAL_CODE
FROM EMPLOYEE
CROSS JOIN NATIONAL;

- 조건 없이 모든 경우의 수를 연결

- 결과 개수가 많이 늘어날 수 있음

6. NON-EQ JOIN

같다(=) 조건이 아니라 범위 조건으로 JOIN

BETWEEN A AND B : A 이상 B 이하

SELECT EMP_NAME, DEPT_CODE, SALARY, E.SAL_LEVEL
FROM EMPLOYEE E
JOIN SAL_GRADE S
ON(SALARY BETWEEN MIN_SAL AND MAX_SAL);

- 급여가 최소 급여와 최대 급여 사이에 있는지 확인

7. SELF JOIN

자기 자신 테이블을 다시 JOIN

SELECT E.EMP_ID "사번",
       E.EMP_NAME "사원명",
       E.MANAGER_ID "관리자 사번",
       M.EMP_NAME "관리자명"
FROM EMPLOYEE E
JOIN EMPLOYEE M ON(E.MANAGER_ID = M.EMP_ID);

- 같은 EMPLOYEE 테이블을 직원용 E, 관리자용 M으로 나눠서 사용

- 별칭을 반드시 잘 구분해야 함

8. 다중 JOIN

여러 개의 테이블을 JOIN

SELECT EMP_NAME, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE);

- EMPLOYEE + DEPARTMENT + LOCATION 연결

- JOIN 순서와 연결 조건을 잘 확인해야 함

9. SUB QUERY

메인 쿼리 안에 들어가는 또 다른 쿼리

SELECT *
FROM EMPLOYEE
WHERE SALARY = (
    SELECT MIN(SALARY)
    FROM EMPLOYEE
);

- 먼저 서브쿼리에서 최소 급여를 구함

- 그 결과를 메인쿼리 조건에 사용

10. 단일행 서브쿼리

결과가 1개 나오는 서브쿼리

SELECT *
FROM EMPLOYEE
WHERE SALARY = (
    SELECT MIN(SALARY)
    FROM EMPLOYEE
);

11. 다중행 서브쿼리

결과가 여러 개 나오는 서브쿼리

SELECT *
FROM EMPLOYEE
WHERE SALARY IN (
    SELECT MIN(SALARY)
    FROM EMPLOYEE
    GROUP BY JOB_CODE
);

12. 다중행 다중열 서브쿼리

결과가 여러 행, 여러 칼럼으로 나오는 서브쿼리

SELECT *
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (
    SELECT JOB_CODE, MIN(SALARY)
    FROM EMPLOYEE
    GROUP BY JOB_CODE
)
ORDER BY JOB_CODE;

13. 인라인 뷰(INLINE VIEW)

FROM 위치에서 사용하는 서브쿼리

SELECT *
FROM (
	SELECT EMP_ID,
		   EMP_NAME,
		   DEPT_TITLE
	FROM EMPLOYEE
	JOIN DEPARTMENT
	ON(DEPT_CODE = DEPT_ID)
);

14. ROWNUM

조회된 데이터에 번호 부여

ORDER BY보다 먼저 번호가 부여됨

SELECT ROWNUM,
	   EMP_NAME,
	   SALARY
FROM EMPLOYEE;

- 조회 순서대로 번호 부여

15. RANK()

순위 함수

SELECT EMP_NAME,
	   SALARY,
	   RANK() OVER(
	   	ORDER BY SALARY DESC
	   ) 순위
FROM EMPLOYEE;

- 동일 순위 존재 시 다음 순위에 영향 미침

16. DENSE_RANK()

순위 함수

SELECT EMP_NAME,
	   SALARY,
	   DENSE_RANK() OVER(
	   	ORDER BY SALARY DESC
	   ) 순위
FROM EMPLOYEE;

- 동일 순위가 존재하여도 다음 순위에 영향 미치지 않음

17. 상관쿼리(상호연관쿼리)

메인쿼리 컬럼을 서브쿼리에서 사용하는 쿼리

SELECT EMP_ID,
	   EMP_NAME,
	   JOB_CODE,
	   SALARY
FROM EMPLOYEE E1
WHERE SALARY >
(
	SELECT AVG(SALARY)
	FROM EMPLOYEE E2
	WHERE E1.JOB_CODE = E2.JOB_CODE
);

흐름

1. 현재 사원의 직급 확인
2. 같은 직급 평균 급여 계산
3. 평균보다 많이 받는지 비교

18. 스칼라 서브쿼리

결과가 1개인 서브쿼리

SELECT EMP_NAME,
	   NVL(
	   	(
	   		SELECT EMP_NAME
	   		FROM EMPLOYEE E2
	   		WHERE E1.MANAGER_ID = E2.EMP_ID
	   	),
	   	'없음'
	   )
FROM EMPLOYEE E1;

- 관리자 이름 조회
- 없으면 '없음' 출력

19. DDL(DATA DEFINITION LANGUAGE)

데이터배아스 객체 생성/수정/삭제 언어

명령어	의미
CREATE	생성
ALTER	수정
DROP	삭제

19-1 CREATE TABLE

테이블 생성

CREATE TABLE MEMBER(
	MEMBER_NO NUMBER,
	MEMBER_ID VARCHAR2(20),
	MEMBER_PW VARCHAR2(20),
	MEMBER_NAME VARCHAR2(20)
);

 

자료형	의미
NUMBER	숫자
VARCHAR2	문자열
CHAR	고정 길이 문자열

19-2 데이터 사전 (DATA DICRIONARY)

DB 정보 저장 테이블

19-2-1 USER_TABLES

현재 계정 테이블 조회

SELECT *
FROM USER_TABLES;

19-2-2 USER_TAB_COLUMNS

테이블 컬럼 조회

SELECT *
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'MEMBER';

19-2-3 USER_CONSTRAINTS

제약 조건 조회

SELECT *
FROM USER_CONSTRAINTS;

 

19-3 NOT NULL

데이터 값에 NULL 혀용 안함

CREATE TABLE USER_NOT_NULL(
	ID VARCHAR(20) NOT NULL,
	PW VARCHAR(20) NOT NULL,
	NAME VARCHAR(20) NOT NULL,
	GENDER CHAR(3)
);

- 반드시 값 입력 필요

19-4 UNIQUE

중복 값 허용 안함

CREATE TABLE USER_UNIQUE( 
	ID VARCHAR(20) UNIQUE,
	PW VARCHAR(20),
	NAME VARCHAR(20),
	GENDER VARCHAR(3)
);

- ID에 같은 값 저장 불가

19-5 PRIMARY KEY

기본 키 (NOT NULL + UNIQUE)

- 테이블 행을 구분하는 고유값

- 테이블 당 1개만 가능

19-6 FOREIGN KEY

외래 키

- 다른 테이블의 값을 참조하는 키

19-7 제약 조건 종류

코드	의미
P	PRIMARY KEY
F	FOREIGN KEY
U	UNIQUE
C	CHECK / NOT NULL

19-8 DROP

객체 삭제

DROP TABLE USER_UNIQUE;