도중에 오라클이 한번 멍청해져서 강제로 종료하고 켜니까 화면이 깨끗해짐... 난 원래 이 프로그램 화면이 흐리고 선명하지 않은 줄 알고 구리다 했는데... 파일을 잃었지만 눈이 맑아졌습니다
11. 무결성 제약 조건(Integrity Constraint Rule)
: 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해 테이블 생성할 때 컬럼에 대해 정의하는 여러가지 규칙
* 제약조건명
* NOT NULL
* UNIQUE
* PRIMARY KEY (기본키)
: null 값을 허용하지 않음 (not null의 특징)
: 중복을 허용하지 않음 (unique의 특징) ---> not null+unique
* FOREIGN KEY
: 참조되는 테이블의 칼럼값이 존재하면 허용
dept -> deptno (pk) : 10, 20, 30, 40
emp -> deptno (fk) : 10, 20, 30
---> 참조는 부모테이블 내에 존재하는 값만 가능 ex) 40은 해당되는 데이터가 없어도 dept에 존재하므로 참조 가능하지만 50은 dept에 없으므로 참조 불가능 (무결성)
공통컬럼이 pk인 쪽이 부모테이블 ---> dept (부모테이블) - emp (자식테이블) 관계
* CHECK
: 특정 범위나 조건을 만족하는 데이터만 허용
* DEFAULT
: 아무 값을 입력하지 않으면 디폴트로 설정한 값을 입력
* 제약조건 지정 방식
- column label 방식: create table로 테이블 생성하면서 컬럼명 데이터타입 제약조건을 한번에 지정
- table label 방식: column을 모두 정의한 뒤 마지막에 제약조건을 한꺼번에 지정
(단, not null은 column label만 가능하므로 아래에 쓰지 말 것.)
- table label만 사용해야 하는 상황
01. 복합키로 기본키를 지정 -> 둘 이상의 컬럼을 기본키로 설정하는 경우
02. alter table로 제약 조건 추가 -> 테이블 정의 완료된 후에 제약조건을 추가하게 된 경우
alter table 테이블명 add primary key(empno);
alter table 테이블명 add foreign key(deptno) references dept(deptno);
alter table 테이블명 modify 컬럼명 not null; --not null은 컬럼 라벨만 가능하니까 수정으로
+) 삭제
alter table 테이블명 drop primary key;
alter table 테이블명 drop unique(ename);
alter table 테이블명 drop constraint constraint_name -- not null같은건 이렇게 제약조건명 걸어서 지워야
* 제약조건 활성화/비활성화
: 제약 조건 때문에 작업을 할 수 없는데 또 제약 조건을 삭제할 수는 없을 때 비활성화 사용
| create table emp01( empno number(4) primary key, ename varchar2(10) not null, job varchar2(10) unique, deptno number(2) references dept01(deptno) ); -- dept01의 deptno(primary key)를 참조하는 emp01가 있을 때 -----> dept01의 데이터를 삭제하기 위해서는 참조 중인 emp01의 foreign key 제약조건을 비활성화해야함 (그래야 참조가 풀리니까) alter table emp01 disable constraint SYS_C007041; -- disable: 비활성화 / enable: 활성화 |
근데 어쨌든 삭제해야 할 때마다 하나하나 비활성화하고 지우고 활성화하고 하는건 번거롭고 비효율적임
그래서 바로 cascade를 사용해야 하는 것이다
- cascade★
01. 부모 테이블 dept01의 제약조건을 비활성화 -> 참조하고 있는 자식테이블 emp01의 foreign key 제약조건도 함께 비활성화
alter table dept01 disable constraint SYS_C007037 cascade; --둘다 disable 상태
02. 부모 테이블 dept01의 primary key 제약조건을 제거 -> 자식테이블 emp01의 foreign key 제약조건도 함께 제거
alter table dept01 drop primary key cascade; --dept의 primary key를 참조하던 emp01의 foreign key도 함께 없어짐
12. 뷰View --데이터 딕셔너리에 간단한 설명 있움
: 물리적인 테이블에 근거한 논리적인 가상테이블(visible table)
-> 기본 테이블에서 파생된 객체, 쿼리문이며 실질적으로 데이터를 저장하고 있지 않지만 테이블을 사용하는 것과 동일한 느낌으로 뷰를 사용할 수 있기 때문에 가상 테이블이라고 명명함
: 이미 존재하고 있는 물리적인 테이블(기본 테이블)에 제한적으로 접근하도록 제어함
* 사용하는 이유
01. 복잡하고 긴 쿼리문을 뷰로 정의 -> 접근을 단순화
02. 보안에 유리 -> 사용자마다 권한을 부여해, 특정 사용자만 해당 뷰에 접근할 수 있도록 함
* 뷰 생성
create table dept_copy as select *from dept;
createtable emp_copy as select *from emp;
---> dept, emp의 카피테이블 생성
★ create view emp_view30 as select empno, ename, deptno from emp_copy where deptno=30;
---> 뷰 생성 (기존 테이블을 카피하는 거랑 비슷함 table 대신 view일 뿐이지...)
+) insufficient privileges 오류
: 니 계정에 뷰 생성 권한이 없단 뜻. system 계정으로 grant create view to scott; 권한 부여해야함
select *from user_views; --->뷰 확인
* 뷰 삭제
drop view emp_view30;
※ 뷰에 데이터를 입력하면, 기본 테이블에도 데이터가 입력됨
insert into emp_view30 values(1111, '가나다',30);
select*from emp_view30;
select*from emp_copy; ---> 기본테이블에도 값 추가되어있음을 확인 가능
+) dept=10인 데이터도 기본테이블에 입력됨 (뷰에는 안보이지만 올라감)
* 뷰 종류
01. 단순 뷰Simple View
: 하나의 기본 테이블로 생성된 뷰
ex. emp_copy를 이용해 20번 부서 소속 사원들의 empno, ename, deptno, mgr 출력하는 뷰(emp_view20)
-> create view emp_view20 as
select empno, ename, deptno, mgr from emp_copy where deptno=20;
02. 복합 뷰Complex View
: 여러 개의 기본 테이블로 생성된 뷰
ex. 각 부서 별(부서명으로 표시) 최대급여와 최소급여를 출력하는 뷰 (sal_view) -> emp랑 dept 조인하고 부서별로 그룹화하고 각 최대, 최소급여 출력
-> create view sal_view as
select dname, max(sal) MAX, min(sal) MIN from emp_copy natural join dept_copy group by dname;
* 뷰 옵션
01. or replace★
: 뷰가 존재하지 않으면 생성insert, 뷰가 존재하면 내용을 수정update
create or replace view emp_view30 as
select empno, ename, deptno, sal, comm from emp_copy where deptno=30;
02. with check option
: where 조건절에 사용된 값을 수정할 수 없음 (deptno 조건 걸려있음 deptno 못 바꾸는거)
create or replace view emp_view_chk30 as
select empno, ename, deptno, sal, comm from emp_copy where deptno=30 with check option;
03. with read only
: 기본 테이블의 어떤 부분도 내용을 변경할 수 없음 (view를 이용한 접근은 only 읽기만 가능)
create or replace view view_read30 as
select empno, ename, sal, comm, deptno from emp_copy where deptno=30 with read only;
* rownum 컬럼
: 데이터의 저장 순서를 가지고 있는 논리적인 컬럼
: 1번부터 시작 ---> order by로 정렬해도 순서가 바뀌지 않음 (처음 입력한 순서대로 부여받은 번호기 때문에)
: rownum 값을 변경하기 위해서는 테이블을 통째로 변경해야 함(다시 입력해서 순서를 바꾸는 것밖에는)
---> select를 emp를 기준으로 하면 emp에 입력된 순서대로 rownum이 부여되어 있는데
emp를 정렬한 것을 view로 생성하고 검색하면 -> view로 새로 생성된 가상 테이블이기 때문에 view에 정렬된 순서대로 rownum이 부여됨
-> emp의 view에 대한 rownum인 것 (가상테이블도 테이블이라는 소리)
-> 데이터를 정렬해 그중 일부만 출력해야 한다면, 이런 식으로 가상테이블로 부여된 rownum을 이용해 출력하는 방법 이용
* 인라인 뷰 (=서브쿼리로 만들어진 뷰)
select rownum, ename, hiredate from (select empno, ename, hiredate from emp order by hiredate asc);
---> 뷰를 create한 것은 아니고, 서브쿼리로 정렬시켜놓은 인라인 뷰를 넣어놓고 메인쿼리로 rownum, ename, hiredate를 구하는 검색테이블을 새로 생성한 것
-> from 뒤로 정렬된 쿼리가 붙어있기 때문에 메인에는 rownum이 새로 부여됨
- top-N 구하는 방법 두 가지~ 1 create view 2 인라인 뷰
- 3~5번째를 구하고 싶다면? ---> 서브쿼리를 두 번 사용해야 함!
+) 범위가 조건(근데 이제 이상 이하인)이라면 between A and B를 쓰는 것도 좋음
'수업 > 정리' 카테고리의 다른 글
| 220204 동의어, PL/SQL-변수,조건,반복, 저장 프로시저 (0) | 2022.02.04 |
|---|---|
| 220203 시퀀스, 인덱스, 권한, 롤 (0) | 2022.02.03 |
| 220127 DML, TCL, 무결성 제약조건 (0) | 2022.01.27 |
| 220126 ANSI Join, 서브쿼리, DDL, DML (0) | 2022.01.26 |
| 220125 oracle: sql함수_형변환, 그룹함수, 조인 (0) | 2022.01.25 |