복기
테이블생성
저장관리하기 위한 것 = 테이블
객체에는 속성이 있는데 속성에 영속성이 있으야 하면 데이터베이스에 들어가야 하는 것. 저장성이 필요 없으면 로직에서 만들어졌다가 없어졌다가 함.
영속성을 가진 데이터 = 도메인데이터
도메인을 분석해서 타입을 뭐할건지 길이는 뭐할건지...
순서는 어떻게 할건지..
속성에는 기본속성이라는게 있다.
원래객체가 갖는 속성 = 기본속성
가공된 데이터 = 유추속성(주민번호에 있는 출생지)
설계속성(아이디, 비번..)

---

DataBase : TCL, JOIN, 집합연산자, VIEW

TCL

Tansaction : 최소 업무(작업) 단위

• commit; = db에 영속 반영

• rollback; = 원상복구 (트랙잭션의 시작점부터 롤백됨)

• savepoint 포인트명; 트랜잭션의 중간 저장점을 지정

• rollback to 포인트명; 저장점부분 복구

• DDL : 자동 반영 (auto commit)

• SQLDeveloper, SQLPlus : 종료, 연결해제시에도 auto commit;

• DML : 명시적으로 commit, rollback 지정해줘야 함

JOIN

• 데이터베이스 모델링 정규화 :
  중복된 데이터를 분리 설계
• 1개 이상의 여러개의 테이블을 결합해서 조회
  자기자신을 참조할 수도 있음

예제 : SCOTT(DEPT/ EMP)
1. Oracle 전용 JOIN
2. ANSI 표준 JOIN (SQLD/SQLP)

• 오라클조인 기본형식
  select
  from 테이블명1 [alias], 테이블명2 [alias], (SUB-QUERY) [alias]
  (,로 연결하고 서브쿼리도 올 수 있고 alias쓸 수 있다)
  [WHERE ~ ]

JOIN 종류

1. CROSS JOIN
2. EQUI JOIN
3. OUTER JOIN
4. SELF JOIN
5. NON-EQUI JOIN

1. CROSS JOIN

• 조인조건 없음, 모든 경우의 수 반환 : 레코드행수*레코드행수
  SELECT *
  FROM EMP, DEPT;

2. EQUI JOIN

• 조인테이블의 조인조건 = 에 해당하는 레코드 조회
• 조인테이블의 모두 존재하는 레코드만 대상 조회

직원테이블에 신입사원 레코드 등록
사번 : 7777, 이름 : 홍길동, 입사일 : 현재날짜, 급여 : 4000
insert into EMP (empno, ename, hiredate, sal) values (7777, '홍길동', sysdate, 4000);

직원의 부서번호, 부서명, 부서위치, 사번, 이름 정보를 조회 : 조인(직원의 부서번호 => 부서테이블 부서번호)
select emp.deptno, dname, loc, empno, ename
from emp, dept
where emp.deptno = dept.deptno
order by emp.deptno;
-- 조인조건에 해당되지 않는 레코드는 조회에서 제거됨

3. OUTER JOIN

• equi join시에 누락된 레코드도 함께 조회
• equi join 조건식에서 데이터가 없는 테이블쪽에 (+) 기호로 표기.
• outer join 시에는 null도 조인의 대상데이터로 간주

부서를 배정받지 못한 직원의 정보도 함께 조회
select emp.deptno, dname, loc, empno, ename
from emp, dept
where emp.deptno = dept.deptno(+)
order by emp.deptno;

부서원이 없는 부서의 정보도 함께 조회
select dept.deptno, dname, loc, empno, ename
from emp, dept
where emp.deptno(+) = dept.deptno
order by emp.deptno;

부서를 배정받지 못한 직원, 부서원이 없는 부서의 정보도 함께 조회
select dept.deptno, dname, loc, empno, ename
from emp, dept
where emp.deptno(+) = dept.deptno(+)
order by emp.deptno;

• error : oracle은 full outer join 지원하지 않음
• 해결책 : oracle은 집합연산 union 이용
• 해결코드
  : select emp.deptno, dname, loc, empno, ename
  from emp, dept
  where emp.deptno = dept.deptno(+)
  union
  select dept.deptno, dname, loc, empno, ename
  from emp, dept
  where emp.deptno(+) = dept.deptno;

4. SELF JOIN

• 현재 테이블 자신을 참조 조인
• 테이블에 대한 alias를 지정 사용

select alias.컬럼명, alias.컬럼명
from table alias, table alias
where alias.컬럼명 = alias.컬럼명
;

• 직원의 사번, 이름. 상사사번, 상사이름 정보 조회
• 직원의 사번 정렬 조회
  select 직원.empno 직원사번, 직원.ename 직원이름, 상사.empno 상사사번, 상사.ename 상사이름
  from emp 직원, emp 상사
  where 직원.mgr = 상사.empno
  order by 직원.empno;

-- 상사가 없는 직원정보 누락
-- outer join 상사가 없는 직원정보도 함께 조회
select 직원.empno 직원사번, 직원.ename 직원이름, 상사.empno 상사사번, 상사.ename 상사이름
from emp 직원, emp 상사
where 직원.mgr = 상사.empno(+)
order by 직원.empno;

5. NON-EQUI JOIN

• 조인조건이 =
• 범위 sql 전용 연산자 : between a and b

급여 등급 테이블(SALGRADE) 조회
: 구조, 전체 레코드 조회
desc salgrade;
select * from salgrade;

직원의 사번, 급여, 급여등급 조회
테이블조인 : 직원테이블, 급여등급테이블
select E.empno, E.sal, S.grade
from emp E, salgrade S
where sal between losal and hisal
order by 1;

[실습] 부서번호, 부서명, 사번, 이름, 급여, 급여등급 정보 조회
select D.deptno, D.dname, E.empno, E.ename, S.sal, S.grade
from emp E, deptno D, salgrade S
where sal between losal and hisal
;

집합연산자

• 2개 이상의 SQL 구문의 결과를 연결시켜주는 연산자
• SQL 구문의 결과 컬럼의 숫자, 타입이 동일하게 매핑되어야 함

1. 합집합 (중복 포함)
   SELECT1 ~ UNION ALL SELECT2;

2. 합집합 (중복 제외)
   SELECT1 ~ UNION SELECT2;
   -- ORACLE FULL OUTER JOIN

3. 교집합
   SELECT1 ~ INTERSECT SELECT2;

4. 차집합
   SELECT1 ~ MINUS SELECT2;

--1. 직원의 부서번호, 이름 조회 : 10번, 20번 부서원 조회
select deptno, ename from emp where deptno in (10,20);

--2. 직원의 부서번호, 이름 조회 : 10번, 30번 부서원 조회
select deptno, ename from emp where deptno in (10,30);

-- 1. 합집합 (중복 포함)
select deptno, ename from emp where deptno in (10,20)
union all
select deptno, ename from emp where deptno in (10,30);

--2. 합집합 (중복 제외)
select deptno, ename from emp where deptno in (10,20)
union
select deptno, ename from emp where deptno in (10,30);

--3. 교집합
SELECT1 ~ INTERSECT SELECT2;
select deptno, ename from emp where deptno in (10,20)
INTERSECT
select deptno, ename from emp where deptno in (10,30);

--4. 차집합
SELECT1 ~ MINUS SELECT2;
select deptno, ename from emp where deptno in (10,20)
MINUS
select deptno, ename from emp where deptno in (10,30);

VIEW

• 가상의 테이블
• 뷰 생성에 따라서 CUD 가능/불가능, 읽기전용
• 실제 데이터는 테이블에 있는데 임의로 만드는 것
• 목적(장점) : 사용자 편리성, 보안
• 뷰종류 :
  - DB 저장 : CREATE VIEW ~ , 반복 사용 가능
  • IN-LINE VIEW : SELECT ~ FROM (SUB-QUERY), 일회성 VIEW

• 뷰 데이터 딕셔너리 : user_views
  VIEW_NAME NOT NULL VARCHAR2(30)
  TEXT_LENGTH NUMBER
  TEXT LONG

1. VIEW 생성 / 2. VIEW 변경
   CREATE [OR REPLACE] VIEW 뷰이름
   AS
   SELECT 구문 ~~
   [WITH READ ONLY]
   ;

   (OR REPLACE : 만약에 해당된 뷰가 존재하면 변경해주고 존재하지 않으면 새로 만들어줘
   무조건 이거 쓰면 있던 테이블 엎어치기 할 수도 있으니, 조심. 기존뷰를 변경해야할때 사용)

2. VIEW 삭제

   DROP VIEW 뷰이름;

• hr view :
  EMP_DETAILS_VIEW 전체레코드 조회
  SELECT * FROM EMP_DETAILS_VIEW;

• VIEW 데이터딕셔너리 : USER_VIEWS
  -- USER_VIEWS 구조 조회
  DESC USER_VIEWS;

• EMP_DETAILS_VIEW 생성 SQL 구문 조회
  SELECT VIEW_NAME, TEXT_LENGTH, TEXT
  FROM USER_VIEWS
  WHERE VIEW_NAME = 'EMP_DETAILS_VIEW';

-- HR VIEW SQL 조회

"SELECT
e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.country_id,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.job_title,
l.city,
l.state_province,
c.country_name,
r.region_name
FROM
employees e,
departments d,
jobs j,
locations l,
countries c,
regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id
AND j.job_id = e.job_id
WITH READ ONLY"

INDEX

-- 명령문의 처리속도를 향상하기위해서 컬럼에 적용하는 객체
-- 책의 목차와 같은 개념
-- 인덱스는 검색의 속도는 향상 되지만, 지나친 인덱스는 cud 발생시에 속도를 저하는 시킬 수 있음
-- PK 컬럼인 경우에는 자동으로 index 객체 생성됨
- PK = unique + not null : index 자동 생성됨
- FK = 존재하는 컬럼, null (FK + NOT NULL) : index 생성되지 않음.
-- 인덱스 데이터 딕셔너리 : user_indexes

-- user_indexes
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_NAME NOT NULL VARCHAR2(30)

ANSI JOIN

-- scott : ansi join : emp, dept
-- ansi cross join
select * from emp cross join dept;

-- ansi equi join : natural join
select deptno, dname, empno, ename
from emp natural join dept;

-- 조인컬럼여러개면 using 사용
select deptno, dname, empno, ename
from emp join dept
using (deptno);

-- 부서를 배정받지 못한 직원의 정보도 함께 조회
select emp.deptno, dname, loc, empno, ename
from emp, dept
where emp.deptno = dept.deptno(+)
order by emp.deptno;

-- ansi join : 부서를 배정받지 못한 직원의 정보도 함께 조회
select emp.deptno, dname, loc, empno, ename
from emp left outer join dept
on (emp.deptno = dept.deptno)
;

-- ansi join : 부서원이 없는 부서의 정보도 함께 조회
select dept.deptno, dname, loc, empno, ename
from emp right outer join dept
on (emp.deptno = dept.deptno)
;

-- ansi join : full outer join 제공 : 부서원이 없는 부서, 부서를 배정받지 못한 직원 조회
select dept.deptno, dname, loc, empno, ename
from emp full outer join dept
on (emp.deptno = dept.deptno)
;

-- non-equi join
-- oracle
--직원의 사번, 급여, 급여등급 조회
--테이블조인 : 직원테이블, 급여등급테이블
select E.empno, E.sal, S.grade
from emp E, salgrade S
where sal between losal and hisal
;

-- ansi join
select empno, sal, grade
from emp join salgrade
on (sal between losal and hisal);