백엔드 개발자의 클라우드 다이브

 권한부여 & 회수 

- 생성된 계정이 할 수 있는 일을 부여하는 것

   > 생성된 계정은 아무런 권한을 가지고 있지 않음

- 관리자 계정만 권한 부여, 권한 회수가 가능함

- grant로 주는 권한은 oracle에 재접속해야 반영됨

* synonym

- 테이블에 별명을 부여하여 사용하는 객체

- create synonum 권한이 부여된 계정만 사용할 수 있음

- alias와는 다르게, synonym명과 원래 테이블명 둘다 사용 가능

- user_synonyms data dictionary에서 확인 가능

- synonym과 public synonym지원 (public synonym은 다른 계정에서도 사용 가능함)

- 권한 부여) grant [public] create synonym to 계정명;

- 권한 회수) revoke [public] create synonym from 계정명;

- 시노님 생성) create synonym 시노님명 for 테이블명;

- 시노님 삭제) drop synonym 시노님명;

* grant,revoke 사용 (DCL)

- 권한 부여) grant 권한,,, to 계정명;

- 권한 회수) revoke 권한,,, from 계정명;

- Oracle 12c 부터는 resource 권한만 부여되어서는 테이블을 생성할 수 없음

   >> resource 권한 이후 table space에 대한 사용권한을 부여해야 함

   > 문법) alter user 계정명 default tablespace 테이블 스페이스명 quota unlimited on users

- 계정 사용하는 기본 tablespace는 users01.dbf파일이고 users라는 이름으로 사용됨

* 권한 관련 data dictionary

- DBMS 자체의 사용권한 조회 : dba_role_privs

- SQL문의 사용권한 조회(synonym, view도 확인가능) : dba_sys_privs

- synonym 조회 : user_synonyms

 view 

- 실제 테이블로부터 도출된 가상테이블

- 권한이 부여된 계정만 view를 생성할 수 있음

- user_views data dictionary에서 확인 가능

- 장점) 실제 테이블을 숨겨놓고 가상 테이블을 사용하므로 보안성이 향상됨

- DML 작업이 가능

    > view에 작업한 내용이 실제 테이블에 반영되고 테이블에서 작업한 내용이 view에 반영됨

- view에 DML작업을 막고 싶다면 veiw를 생성할 때 가장 마지막에 with read only를 사용

- 단순 뷰(하나의 테이블로 도출된 view), 복합 뷰(여러 개의 테이블에서 도출된 view)로 생성할 수 있음

① 단순 뷰

- 하나의 테이블에서 도출된 뷰

- 함수나 연산자를 사용하지 않고 만들어진 뷰

- DML 수행(insert, updtae, select, delete)이 가능함

② 복합 뷰

- 여러 개의 테이블에서 도출된 뷰

- join, 함수, 연산식을 사용한 뷰

 > 함수/연산식 :
     (insert,update) 연산, 함수가 사용된 열은 추가 작업이 되지 않음 / 해당 컬럼을 제외한 일반 컬럼은 사용 가능
     (delete) 집계함수를 사용하여 view를 생성하면 delete 불가 /
                      단일함수, 연산자를 사용한 컬럼에 대해서는 view에 생성된 값과 같다면 삭제 가능

  > join : DML 중 select만 가능 (insert, update, delete 불가)

              : view를 생성하면 select이 단순해짐

         => 복잡한 join을 만들어두고, 필요한 것만 그때그때 select하자!

* view 권한부여/회수 : 관리자 계정만 가능

- 권한 부여) grant creat view to 계정명;

- 권한 회수) revoke create view from 계정명;

- view 생성) create view 뷰명 (컬럼명,,,) as (select ,,,);  -- create subquery와 문법 동일

                                                                                                          -- (컬럼명,,)은 생략 가능

- view 삭제) drop veiw 뷰명;  -- 테이블이 삭제되면 view도 삭제됨

 1. union 

- 여러 개의 테이블의 조회 결과를 아래로 붙여서 조회하는 쿼리문

- 컬럼의 이름은 상관없음. (단, 조회되는 인라인뷰의 컬럼명은 첫 번째 쿼리문의 컬럼명이 나옴)

1) 조건

 ① 위 테이블의 컬럼 데이터형과 아래에 연결되는 테이블의 컬럼의 데이터형이 일치할 것

 ② 위 테이블의 컬럼 개수와 아래 테이블의 컬럼의 개수가 일치할 것
    > 테이블이 가지고 있는 컬럼 개수가 아니라 조회할 때의 컬럼 개수

2) 종류

- union : 중복 레코드가 출력되지 않음

- union all : 중복 레코드가 출력됨

3) 문법)

         select 컬럼명 ,,, from 테이블명 [where~group by ~ ,,,,]

         union [all]

         select 컬럼명 ,,, from 테이블명 [where~group by ~ ,,,,]

         union [all]

         select 컬럼명 ,,, from 테이블명 where~group by ~ ,,,,]

         .... 

** union은 컬럼을 조회하지 않고 고정값으로 조회할 수 있음

 - 원래 데이터가 있음에도 지정한 고정값으로 조회 결과가 합쳐짐

 - 고정값 지정 시 컬럼의 데이터형과 같은 형식으로 기입해야 함!

 - 없는 컬럼도 고정값으로 추가하여 조회할 수 있음

  2. join 

- 서로 다른 테이블을 합쳐서(옆으로 붙여서) 조회할 때 

- 정규화(normalization)가 진행되면 테이블이 분리되어 데이터가 저장됨

  => 쪼개진 데이터를 붙여서 가져오기 위해 join 수행

- oracle에서는 Oracle join문법과 ANSI join 문법을 제공함

=> ANSI(American National Strandards Institute) query는 모든 DBMS에서 사용가능(좀 길다)

- 종류 : inner join, outer join, self join, cross join(=full outer join)이 존재

- 주의

   ① 조인 조건을 잘못 설정하면 cartesian product(카티션 프로덕트)가 발생 (=데카르트 곱)

         => 모든 레코드가 곱해져서 결과로 생성됨 (이렇게 조회된 레코드는 난장판~)

   ② 조인의 키가 되는 driving table을 주의하여 선정 (속도의 차)

** driving table

- 조인 시 key가 되는 table

  (조인 조건 작성할때, ‘비교테이블 = 기준테이블’ 이렇게 작성 / 기준 table이 driving 테이블)

- 선정기준 : 부모(PK)-자식(FK) 관계라면 부모가 driving table

                    : 레코드의 건수가 적은 테이블

                    : 레코드의 수가 비슷하다면, 레코드의 다양성이 적은 테이블

- driving table을 잘 선정하면 검색횟수가 줄어서 속도가 향상됨!!

** equi join(등가조인) : 값이 같은 걸(=) 조인 조건에 넣는 것 (emp.dept no = dept.deptno)

    non-equi join : ‘=’ 연산자 이외의 비교 연산자를 사용하는 것

1) inner join

- 양쪽 테이블에 같은 컬럼 값이 존재하는 레코드만 조회

- 모든 레코드를 조회할 수 없음

- 문법)

* on은 보통 key를 비교하게 됨 (PK나 FK)

* from의 테이블과 join 테이블의 순서는 상관없음 (어차피 같은 값 찾아서 조회하기 때문!)

2) outer join

- 한쪽 테이블에만 레코드가 존재하더라도 검색 가능

- outer join 종류 : left, right, full

   > 레코드가 존재하는 테이블을 선정하는 것 (잘못 선정하면 inner join과 같아짐)

   > 레코드의 구성을 알 때 : left, right / 레코드의 구성을 모를 때 : full

   > full outer를 사용하면 속도가 엄청 느려지고 순서가 뒤섞여서 잘 안씀 (oracle에서 full outer join은 지원 안 함)

- 문법)

* 주의) ANSI와 Oracle은 레코드가 있는쪽/없는쪽 사용이 다름

* ANSI) 조인할 테이블과 조회할 컬럼명에는 key가 되는 테이블을 사용해야 함

    > 부모 테이블과 같이 모든 종류의 레코드를 가지고 있는 테이블을 사용!

    > left outer join일 경우, from에 키가 되는 테이블

       right outer join일 경우, from 다음에 오는 조인 테이블에 키가 되는 테이블 기재

* oracle outer join) 조인 조건에 (+) 기호를 레코드가 없는 쪽 컬럼에 기술함

    > driving table을 뒤에 배치하니까 웬만해서는 alias.컬럼명(+) = alias.컬럼명 (반대도 당연 가능)

    >> 단, (+)를 양쪽 모두에 붙일 수 없음 (oracle은 full outer join 지원하지 않음)

3) self join

- 하나의 테이블을 join하는 것 

- 테이블에 alias를 필수적으로 부여하고(테이블 식별), 검색목적과 조건목적으로 테이블을 구분하여 사용함

> 조회목적 (조회하는 컬럼과 조인 조건에만 등장) / 조건목적 (조인조건과 검색조건에만 등장)

- 조인 조건이 다른 것을 가져오는 non-equi join을 많이 사용함 ('='인 equi join을 사용하면 쓰레기값이 나오기도 함)

- ANSI는 없고 Oracle query만 존재함

# 오늘의 코딩 #

- union과 join을 사용해보잣

#union

--emp : 본사, cp_emp4 : 지사

--------------------union : 중복 레코드가 출력되지 않음--------------------
--본사와 지사 사원을 조회 (단, 중복레코드를 조회하지 않아야 함)
select empno, ename, sal, hiredate, deptno
from emp
union
select empno, ename,  sal, hiredate, deptno
from cp_emp4;

#출력 결과#

-> 본사와 지사의 중복 값을 제외한 데이터들이 출력됨!

-> union all을 사용하면 중복 레코드도 출력된당

#join

- outer join을 ANSI와 Oracle 문법으로 사용해보장

----------------outer join-----------------
--제조사가 '현대'인 모든 차량의 모델명, 연식, 가격, 옵션을 조회해보자
--주의사항) 조회컬럼에 키가 되는 컬럼을 사용해야 결과를 얻을 수 있음

--<ANSI>
select           cma.maker, cma. model, cmo.car_year, cmo.price, cmo.car_option
from             car_model cmo
right outer join car_maker cma
on               cmo.model = cma.model
where            cma.maker = '현대';

--<Oracle>
select cma.maker, cma. model, cmo.car_year, cmo.price, cmo.car_option
from   car_maker cma, car_model cmo
where  (cmo.model(+) = cma.model) and cma.maker = '현대';

#출력 결과#

-> ANSI보단 Oracle 문법이 사용하기엔 간단하다

-> outer join이라 한쪽 테이블에만 값이 존재하더라도 모든 값이 나오는 것을 확인!