> 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와 문법 동일
① 제약사항을 붙여서 추가/변경 등 할 수 있으나 레코드의 구성이 제약사항에 위배된다면 추가할 수 없음
② 컬럼 데이터형 변경이나 컬럼명의 경우 기존 데이터 값에 따라 될수도 안 될수도 있음
1) 컬럼 추가
- 추가된 컬럼은 제일 마지막에 추가됨
- 문법) alter table 테이블명 add 컬럼명 데이터형(크기) 컬럼단위 제약사항;
2) 컬럼 데이터형 변경
- 레코드가 존재하지 않으면 데이터 형 자체를 변경할 수 있음
- 레코드가 존재하면 동일 데이터형에서 크기만 변경할 수 있음
> 크기를 줄이는 것은 기존 컬럼 값의 크기에 따라 될 수도 안 될수도 있음.
- 문법) alter table 테이블명 modify 컬럼명 데이터형(크기) 컬럼단위 제약사항;
3) 컬럼 삭제
- 컬럼에 설정된 제약사항도 같이 삭제됨
- 문법) alter table 테이블명 drop column 컬럼명;
4) 컬럼명 변경
- 테이블의 다른 컬럼명과는 다르게 변경
- 문법) alter table 테이블명 rename column 원본컬럼명 to 변경할 컬럼명;
5) 테이블명 변경
- 문법) alter table 원본테이블명 rename to 변경할 테이블명;
2. 제약사항 관리
- 제약사항 추가, 삭제, 활성화, 비활성화
- 제약사항의 활성화(enable), 비활성화(disable) 상태는 user_constraints의 status 컬럼에서 확인 가능
1) 제약사항 추가
- 테이블단위 제약사항 문법으로 추가함
- 문법) alter table 테이블명 add constraint 제약사항명 제약사항종류 (적용컬럼);
2) 제약사항 삭제
- PK를 지우면 pk 설정시 따라오는 not nul도 같이 지워짐
- 문법) alter table 테이블명 drop constraint 제약사항명;
3) 제약사항 활성화/비활성화
- 주의) 비활성화 후 활성화를 할 때, 컬럼 값의 상황에 따라 활성화가 되지 않을 수도 있음
- 문법) alter table 테이블명 상태 constraint 제약사항명;
> 상태에 enable 기입 : 활성화 / 컬럼에 설정된 제약사항을 체크함
> 상태에 disable 기입 : 비활성화 / 컬럼에 설정된 제약사항을 체크하지 않음 (제약사항이 PK인데 비활성화 되어있으면 중복값도 추가 가능해짐)
3. 계정 관리
- 계정 잠그거나 열기, 계정의 비밀번호 변경
1) 계정의 비밀번호 변경
- 모든 계정이 가능 (관리자는 다른 계정의 비밀번호를 변경(=재설정)할 수 있음)
- 문법) alter user 계정명 identified by 비번;
2) 계정을 잠그거나 열기
- 관리자 계정만 가능 (관리자 계정으로 로그인 한 후 다른 계정을 잠그거나 열 수 있음)
- 연습해볼 때는 login을 관리자계정으로 하고 해보자 (id : system / pw : managerjang)
- 문법) alter user 계정명 account lock | unlock;
** dba_users data dictionary : 계정에 대해 조회할 수 있는 data dictionary
# 오늘의 코딩 #
- alter를 사용해보자
#컬럼명 변경하기
--table 생성
select * from emp;
create table test_emp as (select * from emp);
--alter로 컬럼명 변경 (ename -> name)
alter table test_emp rename column ename to name;
#출력 결과#
<기존 테이블>
<변경된 테이블>
=> 컬럼명 변경 완료!
#PK 제약사항 추가하기
-- alter로 empno에 PK 제약사항 추가
alter table test_emp add constraint pk_test_emp primary key (empno);
-- 제약사항 조회
select *
from user_constraints
where table_name='TEST_EMP';
테이블단위 제약사항(table level constraints) - PK를 n개의 컬럼으로 구성할 수 있음 - constraint pk_테이블명 primary key (컬럼명,,,) --> 컬럼 정의와 제약사항 정의를 분리할 수 있음
* 테이블단위 제약사항에서 PK를 n개의 컬럼으로 구성하는 경우
- 생산물 추적(자동차의 리콜 LOT 번호 관리), 영화관 좌석 예매
> 영화관의 좌석은 특정 시간에만 유일하다.
> 1일에 1번 타임에 1좌석, 1일에 1타임에 2좌석은 OK . 1일에 1타임에 2좌석 또 하면 error
* constraint pk_테이블명 생략 가능 (하지만 PK,FK,UK는 다 준다고 생각하면 됨!!)
- Oracle이 제약사항명을 자동 생성하여 primary key를 설정함 (SYS_C...형태)
- 단, 제약사항명을 pk_테이블명으로 주지 않으면 무결성 제약조건 위배에 관한 error msg에서 오류 원인을 식별하기 힘듦
2) foreign key (외래키, 참조키) - FK
- 컬럼의 값을 다른 테이블(부모 테이블)의 값으로만 추가해야 할 때
- 하나의 테이블에서 여러 개의 컬럼에 FK를 설정할 수 있음
- null 허용
- user_constraints에서 constraint_type : 'R'
- foreign key가 참조하는 레코드가 존재하는 부모테이블의 레코드는 삭제되지 않음
> 삭제 방법1) 참조하는 자식 레코드를 모두 삭제한 후 부모테이블의 레코드를 삭제
> 삭제 방법2) 컬럼의 FK 부여시, on delete cascade 옵션 추가
- 컬럼 단위 제약사항과 테이블 단위 제약사항 두가지로 설정 가능
- foreign key로 설정하는 컬럼은 부모 테이블의 컬럼명과 같을 필요는 없고, 데이터형만 같으면 됨
- 문법)
컬럼단위 제약사항(column level constraints) > 제약사항 종류 명시하지 않음 > 컬럼명 데이터형(크기) constraint fk_컬럼명 references 부모테이블명(참조할컬럼명) [on delete cascade] --> FK는 테이블 하나에 여러 컬럼을 설정할 수 있으니까 테이블명 대신 컬럼명으로 제약사항명을 작성함 --> 다른 테이블에 동일 컬럼명의 fk가 존재한다면 충돌할 수 있으므로, 이때는 fk_테이블명_컬럼명 이렇게 작성
테이블단위 제약사항(table level constraints) > 제약사항 종류를 명시함 > constraint fk_컬럼명 foreign key (적용 컬럼) references 부모테이블명(참조할컬럼명) [on delete cascade] --> FK 적용컬럼의 수와 참조하는 부모의 참조컬럼의 수가 같아야 함! PK처럼 한 번에 여러개 설정 불가!
3) unique
- null을 허용하면서, 유일한 값을 추가해야할 때 (null은 몇 개든 넣을 수 있음)
(Ex. email 주소, 휴대폰 번호, 카드 번호 - 없을 수도 있지만 있다면 유일함)
- 하나의 테이블에 여러 개의 unique를 설정할 수 있음
- user_constraints에서 constraint_type : 'U'
- unique를 설정하면 index가 자동 생성됨
- 컬럼단위 제약사항, 테이블단위 제약사항 두 가지로 사용 가능
- 문법)
컬럼단위 제약사항(column level constraints) > 컬럼명 데이터형(크기) constraint uk_컬럼명 unique, --> UK도 테이블 하나에 여러 개의 컬럼을 설정하므로 컬럼명으로 제약사항명을 작성함 (uk_테이블명_컬럼명 이렇게 해도 됨)
- 조인 조건이 다른 것을 가져오는 non-equi join을 많이 사용함 ('='인 equi join을 사용하면 쓰레기값이 나오기도 함)
- ANSI는 없고 Oracle query만 존재함
ORACLE
select a.컬럼명 ,,, -- 검색목적 테이블의 alias를 사용 from 테이블명 alias a (검색목적), 테이블명 alias b (비교/조건목적) ,,, where 조인조건 and 검색조건 -- 조인 조건은 보통 non-equi -- 검색 조건은 비교목적 테이블의 alias를 사용
----------------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이라 한쪽 테이블에만 값이 존재하더라도 모든 값이 나오는 것을 확인!
> 컬럼 하나에 레코드 하나 나와야 함 // Ex) select sal from emp where empno=7788
- 복수행 문법) insert into 테이블명 (컬럼명,,,) (select,,,)
> 컬럼 여러 개에 레코드 여러 개 나올수있음
3) update subquery
- 다른 테이블의 컬럼 값으로 현재 테이블의 값을 변경해야 할 때
- 단수행 서브쿼리만 가능 (복수행 서브쿼리 사용하려면 in 씀)
> =은 하나의 값만 비교할 수 있으므로 in 써야 함
- 문법) update 테이블명 set 컬럼명 = (select,,,) where 컬럼명 = (select,,,)
- update subquery에서 복수행 subquery를 사용하려면 in을 사용하면 됨
> where empno in (select empno from emp where ename = 'ADAMS' or ename = 'SCOTT');
또는 where empno in (select empno from emp where ename in ('ADAMS', 'SCOTT'));
4) delete subquery
- 단수행 서브쿼리만 가능 (복수행 서브쿼리 사용하려면 in 씀)
- 다른 테이블의 컬럼 값으로 현재 테이블의 레코드를 삭제해야 할 때
- 문법) delete from 테이블명 where 컬럼명 = (select,,,)
5) select subquery
- 단수행 서브쿼리와 복수행 서브쿼리 모두 사용 가능
- 단수행 서브쿼리 : 다른 테이블의 컬럼 값으로 조회, Scalar subquery 사용하여 조회
** Scalar subquery
- 조회되는 컬럼의 subquery를 사용하는 것
- 조회된 컬럼의 값으로 다른 테이블에서 조회하는 서브쿼리
- 서브쿼리만 별도로 실행될 수 없음
-복수행 서브쿼리 : inline view를 사용한 조회 (조회된 결과를 가지고 재조회하는 것)
- 단수행 문법) select 컬럼명 from 테이블명 where 컬럼명 = (select)
- Scalar subquery 문법)
select 컬럼명, (select 컬럼명 from 테이블명 where 테이블명.컬럼명 = 테이블명.컬럼명)from 테이블명
>> Scalar 서브쿼리 안에서는 from에 있는 테이블명.컬럼명 먼저 써주고 나중에 전체 테이블명
- 복수행 문법)
where절 in : 여러 개의 값에 해당하는 레코드를 조회할 때 > 문법) where in ( select … )
inline view를 사용한 조회 : 조회된 결과를 가지고 재조회할 때 사용 > 문법) select 컬럼명 from (select ,,, ) where group by ~ 주고싶은거 다 주면 됨 > 실제 테이블이 아닌 서브쿼리의 검색 결과(inline view)를 사용한 조회 > 바깥 컬럼명은 실제 컬럼이 아닌 inline view에 의해 조회된 컬럼명을 사용해야 함 (또는 alias)
* inline view : select 쿼리에 의해 조회된 결과로 생성된 테이블 - 바깥 쿼리문은 inline view를 사용한 검색을 수행하므로 사용할 수 있는 컬럼은
inline view에 나오는 컬럼만 사용할 수 있음 (컬럼명 동일해야 함, alias면 alias로 조회)
- inline view를 통해 재조회할 때, 안쪽 서브 쿼리에서 조회한 컬럼을 모두 사용할 필요는 없음
- inline view는 같은 쿼리 안에서 inline view를 중복하여 사용할 수 있음
# 오늘의 코딩 #
- select subquery를 해보자!
#단수행 subquery
-- 부서의 지역이 'DALLAS'인 부서에 근무하는 사원정보 조회
-- 조회컬럼 : 사원번호, 사원명, 부서번호, 연봉, 직무
select empno, ename, deptno, sal, job
from emp
where deptno = (select deptno from dept where LOC = 'DALLAS');
#출력결과#
-> where절의 select subquery의 연산결과로 추출된 deptno을 비교하여 필요한 정보가 출력된다!
#Scalar subquery
-- 사원테이블에서 사원번호, 사원명, 부서번호, 부서명 조회
select empno, ename, deptno,
(select dname from dept where dept.deptno=emp.deptno) dename
from emp;
#출력결과#
-> 사원 테이블에는 부서명이 없으나, 부서 테이블과 사원 테이블의 부서번호를 매칭하여
일치하는 부서번호에 해당하는 부서명을 부서테이블에서 가져온당
#복수행 subquery
-- 사원테이블에서 부서의 인원수가 4명 이상인 부서의 사원정보 조회
-- 조회컬럼 : 사원번호, 사원명, 부서번호, 부서명, 연봉, 입사일
select empno, ename, deptno,
(select dname from dept where dept.deptno=emp.deptno) dname
, sal, hiredate
from emp
where deptno in (select deptno
from emp
group by deptno
having count(empno) >= 4);
#출력결과#
-> where절에서 도출된 deptno은 복수이므로, in을 사용하여 복수행 subquery를 수행했다!
