1. TIL
Outer Join : 결여 될 수 있는 쪽에 (+) 사용
A. Sub Query
1. WHERE 서브쿼리 : 일반 서브 쿼리 (단일 행 서브쿼리)
-
동등 조인에서 사용하던 것을 서브쿼리로 대체할 수 있음
-
create 및 insert 에도 사용 가능
-
test 위해서 원본이 아닌 복사본 table활용 권장
-
실행순서 : sub query가 main 쿼리 이전에 실행
EX) 동등 join을 서브쿼리로
SELECT dname
FROM EMP e, DEPT d
WHERE ename = 'SMITH' AND e.deptno = d.deptno;SELECT dname
FROM DEPT
WHERE deptno = (SELECT deptno
FROM EMP
WHERE ename = 'SMITH');2. SELECT 서브쿼리 : 스칼라 서브쿼리 (단 하나의 값을 반환)
-- 직원이름, 부서번호, 부서명 출력
SELECT e.ename, e.deptno, (SELECT dname
FROM DEPT d
WHERE e.deptno = d.deptno) AS dname
FROM EMP e;3. FROM 서브쿼리 : 인라인뷰
-- 각 부서별 최대 연봉을 받는 사람들의 정보 출력
-- 컬럼명 재지정, MAX() 또한 스네이크 케이스로 익명화
SELECT e.ename, e.deptno, e.sal
FROM EMP e, (SELECT deptno, MAX(sal) AS max_sal
FROM EMP
GROUP BY deptno) i
WHERE e.deptno = i.deptno AND e.sal = i.max_sal;4. 다중행 서브쿼리 + IN( )
-- 급여가 3000이상의 사원이름, 급여, 부서번호
SELECT ename, sal, deptno
FROM EMP
WHERE deptno IN(SELECT deptno
FROM EMP
WHERE sal >= 3000);5. 다중 컬럼 서브쿼리
-- 부서별 가장 급여를 많이 받는 사원의 번호, 이름, 급여, 부서번호
SELECT e.empno, e.ename, e.sal, e.deptno
FROM EMP e
WHERE deptno IN (SELECT deptno
FROM EMP
GROUP BY deptno)
AND
sal IN (SELECT MAX(sal)
FROM EMP
GROUP BY deptno);다중행 서브쿼리로 표현
SELECT e.empno, e.ename, e.sal, e.deptno
FROM EMP e
WHERE (deptno, sal) IN (SELECT deptno, MAX(sal)
FROM EMP
GROUP BY deptno)그 외 연산자
-
ANY : 서브쿼리의 결과가 하나라도 있으면 참, OR, IN 과 비슷함
-
ALL : 서브쿼리의 결과가 모든 결과가 만족하면 참, AND와 비슷함
-
EXISTS : 서브쿼리의 결과가 존재하면 참
-
NOT EXISTS : 서브쿼리의 결과가 존재하지 않아야 참
서브쿼리가 바뀔 수 없는 경우
- 집게된 함수의 결과값으로 반환하는 서브쿼리가 WHERE에 있는 경우
- 인라인뷰에 사용된 서브쿼리에 GROUP BY를 사용한 경우
B. Data Definition Language
1. DDL (데이터 정의 언어)
Oracle Data Type : 자바 타입
-
varchar2[최대 메모리], char[고정사이즈메모리] : String
-
number(전체자리수[ , 소수점이하자리]) : 정수, 실수
-
date : java.util.Date, java.sql.Date, String
테이블 생성 :
CREATE TABLE 테이블명(
컬럼명1 컬럼타입[(사이즈)] [제약조건] ,
컬럼명2....
); 테이블명 뒤에 AS SELECT * FROM EMP 사용시 EMP테이블을 전부 복사함
테이블의 구조만 복제하려면 WHERE 1 = 0;
서브 쿼리를 이용하여 테이블을 생성할 수 있음
CREATE TABLE EMP02 AS SELECT empno FROM EMP;테이블 삭제 : DROP TABLE 테이블명
테이블 구조 수정 : ALTER TABLE 테이블명 변경명령어
- ADD : 미존재하는 컬럼 추가
- DROP : 존재하는 컬럼 삭제
- MODIFY : 존재하는 컬럼의 타입(사이즈) 변경
EX)
ALTER TABLE EMP01 ADD(job varchar2(10));ALTER TABLE EMP01 DROP COLUMN job;ALTER TABLE EMP01 MODIFY(job varchar2(20));TRUNCATE : 순수 데이터만 삭제 (구조 X)
TRUNCATE TABLE test01;RENAME : 이름 재지정
RENAME EMP01 to test01;C. Data Mainpulation Language
DML (데이터 조작 언어)
INSERT, UPDATE, DELETE, COMMIT, ROLLBACK
DML은 ROLLBACK이 가능함
1. INSERT
컬럼명 기술 없이 데이터 INSERT
INSERT INTO PEOPLE VALUES('brother', 25);컬럼명 기술 후 데이터 INSERT
INSERT INTO PEOPLE(age, name) VALUES(23, 'hansol');다중 테이블에 한꺼번에 데이터 INSERT
-- EMP01, EMP02의 데이터가 비어있다는 가정하에
INSERT ALL
INTO EMP01 (empno, ename, deptno) VALUES (empno, ename, deptno)
INTO EMP02 (empno, ename, deptno) VALUES (empno, ename, deptno)
SELECT empno, ename, deptno FROM EMP;조건을 포함한 다중테이블 데이터 INSERT
조건 표현 : WHEN THEN
INSERT ALL
WHEN deptno = 10 THEN
INTO EMP01 (empno, ename, deptno) VALUES (empno, ename, deptno)
WHEN deptno = 20 OR deptno = 30 THEN
INTO EMP02 (empno, ename, deptno) VALUES (empno, ename, deptno)
SELECT empno, ename, deptno FROM EMP;2. UPDATE
모든 행 UPDATE
UPDATE EMP01 SET deptno = 60;
UPDATE EMP01 SET sal = sal * 1.1조건을 포함한 UPDATE
UPDATE EMP01 SET sal = sal * 1.1
WHERE sal >= 3000;서브쿼리를 사용하여 UPDATE
UPDATE EMP01 SET sal = sal + 1000
WHERE deptno = (SELECT deptno
FROM DEPT
WHERE loc = 'DALLAS');3. DELETE
모든 데이터 DELETE, DML이므로 롤백 가능함
DELETE FROM EMP01;해당 조건의 데이터 DELETE
DELETE FROM EMP01
WHERE comm IS NOT NULL;서브쿼리를 이용한 DELETE
DELETE FROM EMP01
WHERE deptno = (SELECT deptno
FROM DEPT
WHERE dname = 'RESEARCH');[Optional] MERGE : 두 테이블을 병합
-- MERGE 구조
MERGE INTO 병합테이블 t
USING 병합대상테이블 p1
ON (t.판매번호 = p1.판매번호)
WHEN MATCHED THEN
UPDATE SET t.수량 = t.수량 + p2.수량
WHEN NOT MATCHED THEN
INSERT VALUES (p1.판매번호, p1.수량, p1.금액);2. 에러
만들어진 구조에서 새로 INSERT 했을 경우 원했던 대로 저장결과가 안나올 수 있음
준비된 데이터 구조가 window환경에서는 사용 가능하지만 mac에서는 안되는 경우가 있었음
종종 만난 예외는 매핑되는 데이터와 컬럼이 안맞아서 생기는 경우 였음
3. 보완 해야 할 것
join에 대한 것은 많이 능숙해짐 대신 인라인 뷰 (FROM 서브쿼리)가 어려우므로 추가 학습이 필요함
DML에서 INSERT ALL 하는 부분도 살짝 헷갈림
4. 느낀점
서브쿼리를 정말 아무곳에서나 사용할 수 있어서 생각을 많이하게 됨
며칠 동안의 DB학습은 정말 환경셋팅, docker 사용 때문에 피로도가 심했음
