Acorn Academy 11/21 DDL, 뷰

Bae Seong Jun·2023년 11월 21일

Acorn academy

목록 보기
1/70

제약 조건 Constraints Rule

제약 조건은 테이블에 올바르지 않은 부적절한 데이터가 저장되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러가지 규칙을 의미한다.
이것은 데이터베이스의 설계 단계에서 데이터의 무결성을 보장 받기 위한 방법이다.
업로드중..

PRIMARY KEY

  • 기본키
  • UNIQUE와 NOT NULL의 특성을 가진다.
  • 자동으로 UNIQUE INDEX 객체가 생성된다.
  • 기본키를 이용한 데이터 검색은 기본적으로 빠르다.
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] PRIMARY KEY,
컬럼명 데이터타입,
...
);

UNIQUE 제약조건

  • 중복되는 값의 저장이 불가능
  • UNIQUE INDEX가 생성 빠른 검색효과
  • null 가능 / null은 중복도 가능
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] UNIQUE,
컬럼명 데이터타입,
...
);

NOT NULL

  • 테이블 단에서 설정이 불가능
  • NULL, '' 저장 불가능
  • 값의 중복 입력은 가능함
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] NOT NULL,
컬럼명 데이터타입,
...
);

CHECK

  • 설정한 조건 내의 값만 저장 가능
  • 조건절에는 IN연산자, 비교연산자, AND/OR연산자, like, is null, 논리연산자 등등
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] CHECK(조건식) , 컬럼명 데이터타입,
...
);

FOREIGN KEY 외래키, 참조키, 노예키

  • 참조하는 부모키는 보통 기본키이거나 유니크키이다. 부모키는 중복되는 값이 있으면 안된다.
  • 참조 무결성
  • 가질 수 있는 값에 제한이 있다. 부모키가 가지기 않은 값은 가질 수 없다.
  • NULL값은 가질 수 있다.
  • 부모테이블이 먼저 존재해야 설정할 수 있다.
  • 부모테이블의 값이 먼저 존재해야 외래키값을 insert할 수 있다.
  • 참조하고 있는 값은 삭제가 불가능하다.
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] REFERENCES 부모테이
블명(컬럼명),
컬럼명 데이터타입,
...
);

테이블단에서 외래키를 설정할 경우 문법이 다름

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, 컬럼명 데이터타입,
...,
[CONSTRAINT 제약조건명] FOREIGN KEY(컬럼명)
 REFERENCES 부모테이블명(컬럼명)
);

*객체 무결성 ?

foreign key 제약조건의 추가 옵션

  • ON DELETE CASCADE : 참조되는 부모 테이블의 행이 삭제되면, 해당 행을 참조하는 자식 테이블의 행도 같이 연쇄적으로 삭제되도록 한다. 
create table emp02
(empno number(04) constraint emp02_empno_pk primary key,
ename varchar2(15),
deptno number(2)
constraint emp02_deptno_fk references dept02(deptno) ON DELETE CASCADE);
  • ON DELETE SET NUL : 참조되는 부모 테이블의 행이 삭제되면, 해당 행을 참조하는 자식 테이블의 컬럼값을 널(null)로 설정한다.
    create table emp02
(empno number(4), constraint emp02_empno_pk primary key,
ename varchar2(15),
deptno number(2),
constraint emp02_deptno_fk foreign key(deptno) references dept02(deptno) on delete set null);
  • 테이블을 만들 때 왠만한 제약조건은 테이블단에 만들자.

테이블의 삭제

DROP

  • 참조되고 있는 부모테이블은 삭제할 수 없다.
  • CASCADE CONSTRAINTS절을 붙이면 자식테이블의 외래키 제약조건이 지워지고 부모테이블이 삭제된다.
DROP TABLE 테이블명 [CASCADE CONSTRAINTS];

Flashback Drop (스킵)(이 버전에서 안됨)

테이블의 변경 (중요)

ALTER

생성된 테이블에 대한 구조를 변경할 수 있으며 ALTER TABLE 명령문을 사용한다. 테이
블에 대한 구조 변경은 컬럼의 추가,삭제 및 컬럼의 타입이나 길이 변경, 제약조건 추가,삭
제등이 가능하다. 테이블에 대한 구조 변경은 기존에 저장되어 있던 데이터에 영향을 주게
된다.

  1. 컬럼의 추가
  • ALTER TABLE ADD 문을 사용하여 기존 테이블에 새로운 컬럼을 추가한다.
  • 새로운 컬럼은 테이블 마지막에 추가되며 데이터는 자동으로 널값으로 저장된다.
ALTER TABLE 테이블명
ADD ( 컬럼명 데이터타입 [, 컬럼명 데이터타입]);
  1. 컬럼 변경
  • 기존 컬럼 변경
  • 타입, 크기, default값 변경 가능
  • 모든 행의 컬럼이 널(null)이거나 행이 없는 경우에만 컬럼 길이 축소와 데이터 타입 변경이 가능하다.
  • DEFAULT 값을 변경하는 경우에는 변경 이후부터 입력되는 행에 대해서만 적용이 된다.
ALTER TABLE 테이블명
MODIFY ( 컬럼명 데이터타입 [, 컬럼명 데이터타입]);
  1. 컬럼 삭제
  • ALTER TABLE DROP 문을 사용하면 기존 컬럼을 삭제 할 수 있다.
  • 컬럼은 값의 존재여부와 상관없이 무조건 삭제된다.
  • 한꺼번에 여러 개의 컬럼들을 동시에 삭제할 수도 있으나 반드시 최소한 하나의 컬럼은 존재해야 된다
ALTER TABLE 테이블명
DROP ( 컬럼명 [,컬럼명] );
  1. 제약조건 추가
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건타입(컬럼명);

  • 테이블 레벨단에 제약조건 추가하듯이 같은 문법으로 추가하면 된다. 컬럼명을 명시해야함.

  • 테이블 레벨 제약조건 설정방법과 동일한 문법이기 때문에 컬럼 레벨 방식만 가능한 NOT NULL은 ADD로 추가가 불가능하고 MODIFY로 추가할 수 있다.

  • NOT NULL 제약조건 추가 문법

ALTER TABLE 테이블명
MODIFY ( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] NOT NULL )
  1. 제약조건 삭제
  • 기존 테이블의 제약조건을 삭제하기 위해서는 제약조건명을 이용하여 ALTER TABLE
    DROP 문을 작성해야 된다. 필요시 USER_CONSTRAINTS와 USER_CONS_COLUMNS 데
    이터 사전을 이용하면 제약조건명을 조회할 수 있으며 CASCADE 옵션은 모든 종속적인 제
    약조건을 같이 삭제하는 방법이다. 기본적으로 제약조건명을 이용하여 제약조건을 삭제하지
    만 기본 키(PRIMARY KEY)와 UNIQUE는 제약조건명 없이 PRIMARY KEY와 UNIQUE 키
    워드만 사용하여 삭제할 수 있다.
ALTER TABLE 테이블명
DROP PRIMARY KEY|UNIQUE(컬럼)|
 CONSTRAINT 제약조건명 [CASCADE];
ALTER TABLE dept03
DROP PRIMARY KEY;
ALTER TABLE dept03
DROP CONSTRAINT dept03_deptno_pk;
  • cascade절 사용하면 참조되고 있는 기본키의 제약조건을 삭제할 수 있다.
  • 모든 제약조건을 보는 법
  • USER_CONSTRAINTS에서 볼 수 있다.
  • select * from USER_CONSTRAINTS where table_name = '테이블이름(대문자)';
  • 이런식으로 쓰면 보기 좋다. select table_name, constraint_name FROM user_CONSTRAINTs where table_name IN( 'EMP05', 'DEPT05');
  1. 제약 조건 활성화/비활성화
  • 데이터 무결성이 보장되는 데이터라는 가정 하에, 기본키 제약조건을 비활성화 시킨 후에 데이터를 저장하면 저장 성능을 향상시킬 수 있다.
  • 제약조건은 기본적으로 데이터의 무결성은 보장 받을 수 있지만 성능은 떨어지는 작업이기 때문이다.
ALTER TABLE 테이블명
DISABLE|ENABLE CONSTRAINT 제약조건명 [CASCADE];

  • 보니까 제약조건 이름을 사용할 때는 대부분 앞에 CONSTRAINT를 붙이는 것 같다.

  • 데이터 사전 (Data Dictionary)

뷰, 시퀀스(상당히 중요), 시노님

뷰 (View)

  • 물리적인 테이블을 기본 테이블 (base table), 원본 테이블이라고 하고
  • 원본테이블에서 필요한 컬럼들만 추출해내서 만든 것을 가상 테이블, 뷰라고 함.
  • 선택적으로 데이터를 보여줄 수 있음 db에 대한 접근제한
  • 민감한 데이터를 가진 컬럼들은 언제든지 접근을 제한하여 보안을 강화시킬 수 있다.
  • 결과를 검색하기 위한 복잡한 질의를 단순한 질의로 변경할 수 있기 때문이다.
    - 조인으로 만들어야할 테이블을 뷰로 먼저 만들어두고 두고두고 사용할 수 있다.

뷰 생성

CREATE [OR REPLACE] VIEW 뷰이름 [(alias[,alias] ...)]
AS 
서브쿼리
[WITH CHECK OPTION [CONSTRAINT 제약조건명]]
[WITH READ ONLY [CONSTRAINT 제약조건명] ];
  • create : 해당하는 뷰이름이 없으면 만들기.
  • replace : 해당하는 뷰 이름이 있으면 덮어쓰기.

뷰 생성 권한 없으면 생성이 안됨
ERROR MESSAGE : insufficient privileges
SCOTT 계정 만들 때 CONNECT(접속권한), RESOURCE(테이블생성권한) 만 GRANT했기 때문에 따로 뷰 권한을 줘야함.

  • sys계정에서 GRANT create view TO scott; 로 scott 계정에 권한 부여

뷰를 생성하면 테이블처럼 사용할 수 있음
약간 영구적인 사용하기 편한 테이블 느낌이 남.

  • 뷰를 생성할 때 컬럼 별칭(alias)을 명시하지 않으면 뷰를 정의하는 기본 테이블의 컬럼명을
    사용하게 된다.
  • 제약조건은 적용되지 않는다.

뷰 수정

CREATE OR REPLACE 구문을 사용

create or replace view emp_view2 (사원번호, 이름, 직업, 관리자번호, comm, 부서번호)
as
select empno, ename, job, mgr, comm, deptno from emp;

뷰 종류

기본테이블(원본테이블)의 개수에 따라서 단순 뷰와 복합 뷰로 구분된다.

1. 단순 뷰
  • 기본적으로 INSERT,UPDATE,DELETE와 같은 DML문 실행이 가능하다.
  • 단순 뷰에 대해서 실행한 DML문의 처리 결과는 실제로 기본 테이블에도 반영이 된다.
  • 새로 생성되는 뷰에 대해서 별칭(alias)을 지정하지 않으면 기본 테이블의 컬럼명을 상속받는다.
  • 뷰를 생성하면서 함수를 사용하는 경우에는 반드시 컬럼 별칭을 지정해야 한다.

*모든 상황에서 단순 뷰의 DML 작업이 가능한 것은 아니다. 단순 뷰가 그룹함수,GROUP BY, DISTINCT 같은 표현식을 포함한 경우에는 DML 작업이 불가능하다.

2. 복합 뷰( Complex View )
  • 복합 뷰는 두 개 이상의 기본 테이블에 대해서 정의한 뷰이다. 두 개 이상의 테이블을 조인
    해서 사용할 경우 매번 SELECT 문을 작성하지 않고 뷰로 생성하여 사용할 수 있다. 이렇
    게 두 개 이상의 테이블을 조인하는 SELECT문을 뷰로 생성한 것이 복합 뷰이다.

WITH CHECK OPTIN 제약 조건

CREATE VIEW EMP_VIEW6
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 30;

SELECT시 부서번호가 30명인 사원이 6명이 출력된다.

SELECT * FROM EMP_VIEW6;

UPDATE EMP_VIEW6
SET DEPTNO = 40
WHERE EMPNO = 7499;

1명이 40번으로 변경되었다.
SELECT시 뷰에서 제외되었다.

  • 위와 같이 뷰에 대해서 기본적으로 DML문이 수행되는 것을 WHERE 조건에 일치하는 데이
    터만 변경 가능하도록 제약하는 방법이 WITH CHECK OPTION 이다.
CREATE OR REPLACE VIEW EMP_VIEW6
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 30
WITH CHECK OPTION;
  • 부서번호가 30인 사원만 DML이 가능하도록 WITH CHECk OPTION을 지정한다.

WITH READ ONLY 제약 조건

  • 뷰를 통한 DML 작업은 불가능하도록 설정하는 방법이다.
CREATE OR REPLACE VIEW emp_view6
AS
SELECT empno,ename,sal, deptno
FROM emp
WITH READ ONLY;

뷰 삭제

  • 뷰의 삭제는 뷰에 대한 기본 테이블에는 어떠한 영향도 미치지 않는다. 따라서 기본 테이블
    의 데이터 손실 없이 뷰가 삭제된다. 뷰가 삭제된다는 것은 결국 USER_VIEWS 데이터 사
    전에 저장된 text컬럼의 서브쿼리가 삭제되는 것이기 때문이다.
DROP VIEW 뷰이름;

시퀀스 (중요)

  • 오라클 객체로서 테이블의 특정 컬럼값을 넘버링(numbering)하기 위해서 사용된다.
CREATE SEQUENCE 시퀀스명
[ START WITH n]
[ INCREMENT BY n]
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ] 
[ CYCLE | NOCYCLE ]
[ CACHE n | NOCACHE ]

START WITH n
INCREMENT BY n

  • 음수값 설정도 가능하고 생략시 1씩 증가된다.
    MAXVALUE n
    MINVALUE n
  • 시퀀스가 가질수 있는 최소값을 지정한다. CYCLE인 경우에는 새로 시작하는 값 역할을 한
    다.
    CYCLE 옵션
  • 지정된 시퀀스 값이 최대값까지 증가가 완료되면 START WITH값부터 다시 시작하는 것이
    아니고 MINVALUE 값부터 다시 시작된다. NOCYCLE은 증가가 완료되게 되면 에러가 발
    생된다.
    CACHE 옵션
  • db 사용시 nocache 가급적 권장
  • 궁금점 : 사이클 on, max,minvalue 비활성화시 나타나는 현상?
  • cache 생략시 기본 상태?

CURRVALL

  • 현재 시퀀스 값을 구한다.
  • 현재 값 확인용 구문

NEXTVAL (중요)
(중요) create sequence 시퀀스이름. NEXTVAL

SELECT dept_deptno_seq.NEXTVAL, dept_deptno_seq.CURRVAL
 FROM dual;
  • 만약에 increment 값이 음수고 cycle 옵션이 있다면, max와 min이 반대가된다.
  • 한마디로 cycle중 값이 min에 다다르면 max부터 시작

  • USER_SEQUENCES 데이터 사전 에 등록됨

    시퀀스 수정

  • 시퀀스 수정은 ALTER SEQUENCE문을 사용하여 증가치,최대값,최소값,CYCLE여부,캐시값
    을 변경할 수 있다. 시퀀스가 변경되면 다음 시퀀스 번호값부터 변경사항이 적용되고
    START WITH옵션은 변경이 불가능하기 때문에 필요시 시퀀스를 삭제하고 재 생성해야 된
    다.
ALTER SEQUENCE 시퀀스명
[ INCREMENT BY n]
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ] 
[ CYCLE | NOCYCLE ]
[ CACHE n | NOCACHE ]

테이블에 시퀀스 값 저장 (자주 쓰임)

INSERT문 사용시 같이 사용하는 형태로 쓰인다.

INSERT INTO DEPT06 (DEPTNO, DNAME, LOC)
VALUES (DEPT_DEPTNO_SEQ. NEXTVAL, '개발', '서울');

시퀀스 삭제

DROP SEQUENCE 시퀀스명;

동의어 synonym

동의어는 db 객체에 대한 별칭을 뜻한다.
다른객체에 접근할 때 스키마.객체 형식으로 접근한다. 이 스키마.객체 형식을 별칭으로 대체하는 것을 시노님이라고한다.

CREATE [PUBLIC] SYNONYM 동의어
FOR 스키마.객체;
  • public는 모든 사용자가 사용할 수 있고 private는 동의어를 만든 사용자만 사용이 가능하다.
-- sys 계정
-- tester 계정 생성 및 권한부여
create user tester identified by tester;
alter user tester identified by tester;
grant connect, resource to tester;

  • oracle sql developer에서 접속정보 삭제는 db의 사용자 계정을 삭제하지는 않는다.

  • sys -> 다른 사용자 계정에서 삭제하면 계정이 삭제된다.

  • tester 계정에서 scott의 컬럼에 접근하는게 안된다.

select * from scott.emp;    <-- 오류
  • 접근권한 grant가 필요
-- scott 계정에서 (접근할 컬럼이 있는 계정에서)
GRANT
select on dept
to tester;

select 권한만 부여한 것. insert시 insufficient privileges 오류메세지 발생 (권한부족)

  • 시노님 생성 권한 sys -> scott에게 부여해야 시노님 생성이 가능하다.
-- SYS
GRANT create synonym
TO tester;
  • 시노님은 생성한 계정 내에서 사용가능하다.

인덱스 (가볍게)

  • 읽어보기

사용자관리 (간단하게 보고 넘어가기)(데이터베이스 관리자 배워야할 내용)

(oracle dba 자격시험 에 필요 따로 책이 있을 정도임)

  • 개념만

  • 다수의 사용자들이 데이터베이스에 저장된 데이터를 공유해서 사용한다.

  • 따라서 보안을 위해서 오라클 db는 인증과 권한을 사용하여 개별 사용자들의 db 접근 및 사용에 적절한 보안을 유지시킨다.

  • 인증 : 사용자 계정을 생성, 암호를 변경, 디스크 공간 할당 등 시스템 수준에서 db 접근 및 사용을 관리하는 것을 의미한다.

  • 권한 : db 객체에 대한 사용자들의 접근 및 사용을 관리하는 개념

  • 계정: 사용자 (sys가 추가) => 권한부여 (sys가 부여 grant, 회수도 가능 revoke )

CREATE USER 계정
IDENTIFIED BY 비밀번호;
  • 롤(role) : 권한의 묶음

ex) cott => tester 권한부여
grant select on dept to tester
grant insert ...
grant update ...
grant delete ...

user_grant 롤생성 <= grant select on dept to tester
grant insert ...
grant update ...
grant delete ...

grant user_rant to tester;
grant user_rant to tester2;

권한

권한의 종류

  1. 시스템 권한

  • DBA가 권한을 부여 (SYS)

  • DB에 특별한 작업을 수행하는 것을 가능하게 해준다.

  • 접속, 사용자생성, 테이블생성, 뷰생성 등

    CREATE USER 새롭게 사용자를 생성하는 시스템 권한
    DROP USER 사용자를 삭제하는 시스템 권한
    DROP ANY TABLE 임의의 테이블을 삭제할 수 있는 시스템 권한
    QUERY REWRITE 함수 기반 인덱스를 생성하는 시스템 권한
    BACKUP ANY TABLE 임의의 테이블을 백업할 수 있는 시스템 권한

  1. 객체 권한

  • 객체 수준에서의 권한

  • 객체의 소유자가 권한을 부여 (객체란 테이블과 같은 것들)

  • select,insert,update,delete 같은 작업을 수행하는 것을 가능하게 해준다.

    CREATE SESSION 데이터베이스에 접속할 수 있는 시스템 권한
    CREATE TABLE 사용자가 테이블을 생성할 수 있는 시스템 권한
    CREATE SEQUENCE 사용자가 시퀀스를 생성할 수 있는 시스템 권한
    CREATE VIEW 사용자가 뷰를 생성할 수 있는 시스템 권한
    CREATE PROCEDURE 사용자가 PL/SQL의 프로시저를 생성할 수 있는 시스템 권한

권한 할당 문법

GRANT 시스템권한[,시스템권한]
TO 사용자계정|role|PUBLIC;
  • 해당 유저가 가진 권한을 알고싶을 때 : select * from session_privs;

권한 회수 문법

REVOKE 시스템권한[,시스템권한]
FROM 사용자계정|role|PUBLIC;

< 계정 생성 및 테이블 생성 >
1. user01 계정 생성 + 디벨로퍼에서 접속정보 저장
2. sys에서 user01에 접속권한 부여
3. sys에서 user01에 테이블 생성 권한 부여
4. sys에서 user01에 테이블저장공간을 사용할 권한을 부여

create user user01 identified by user01;

grant create session to user01;

grant create table to user01;

grant unlimited tablespace to user01;
profile
Bae Seong Jun
코딩 프로?
다음 포스트

Acorn Academy 11-15

0개의 댓글