<TIL> DDL

이영민·2023년 3월 31일
DatabaseTIL
0

2023_TABA_2기

목록 보기
2/3
post-thumbnail

단국대학교 TABA2기 Tibero를 활용한 데이터베이스 실습 수업을 정리한 내용입니다!

1. DDL(Data Definition Language)

  • 데이터 간의 관계를 정의하여 데이터베이스 구조를 설정하는 SQL문장



2. 테이블

A. 테이블 생성, 제거, 구조 확인

CREATE TABLE (테이블 명) ((열 이름) (타입) (제약조건),(열 이름) (타입) (제약조건)....);
DROP TABLE (삭제하려는 테이블 명);
DESC (확인하려는 테이블 이름)

→ 타입은 DB마다 지원하는 방식이 다르므로 확인해야한다.

  • Tibero 제공 데이터 타입
    구분데이터 타입
    문자형CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR, NVARCHAR2, RAW, LONG, LONG RAW
    숫자형NUMBER, INTEGER, FLOAT
    날짜형DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
    간격형INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
    대용량 객체형CLOB, BLOB, XMLTYPE
    내재형ROWID
    DatatypeDescription
    CHAR(s)고정된 문자열 길이, 최대 2,000자까지 선언, 문자열의 길이가 0인 값은 NULL로 인식
    CHAR(size[BYTECHAR]) -> EXAM CHAR(10)
    VARCHAR2(s)가변 문자열 길이, 최대 4,000자까지 선언, 문자열의 길이가 0인 값은 NULL로 인식
    VARCHAR2(size[BYTECHAR]) -> EXAM VARCHAR2(10)
    VARCHAR(s)VARCHAR2 타입과 동일
    LONGVARCHAR2와 비슷하지만, 최대 2GB까지 선언
    DATE연도는 BC 9,999 ~ AD 9,999까지 표현,
    NUMBER(p,s)정수 또는 실수를 저장,  음양으로 절댓값이 1.0×10-130보다 크거나 같고, 1.0×10126보다 작은 38자리의 수를 표현할 수 있으며 0과 ±무한대를 포함한다.
  • 제약조건
    ConstraintDescription
    NOT NULLNULL값을 허용하지 않고, 반드시 데이터를 입력. 제약조건이 NULL이면 해당 컬럼은 NULL값을 허용.
    UNIQUE해당 칼럼이 중복되는 데이터가 존재할 수 없는 유일성을 보장하는 제약조건
    PRIMARY KEYNOT NULL, UNIQUE 제약 조건의 결합과 같다. 테이블 또는 뷰는 단 한 개의 PRIMARY KEY 제약조건을 가질 수 있다.
    FOREIGN KEY같은 테이블 또는 서로 다른 두 개 테이블의 키 컬럼 사이의 관계
    CHECKexpr로 표현한 조건이 항상 참이 되도록 유지. 특정 조건을 평가 후 만족하지 못하면 에러 발생

B. 테이블 추가 및 수정, 삭제

추가
ALTER TABLE (테이블 명) ADD ((추가  할 열의 이름) (타입) (제약조건), (추가  할열이름) (타입) (제약조건)..);
ALTER TABLE (테이블 명) ADD CONSTRAINT (테이블 명)_fk_id FOREIGN KEY(외래 키로 설정할 열 이름) REFERENCES (참조할 테이블 명(참조할 열 이름));
-> 외래키 속성 추가는 위와 같이 한다.
열 수정
ALTER TABLE (테이블 명) MODIFY ((수정 할 열이름) (타입) (제약조건), (추가  할열이름) (타입) (제약조건)..);
삭제
ALTER TABLE (테이블 명) DROP ;COLUMN (열 이름);
-> 만약 삭제하려는 열이 외래키로 묶여 있어 삭제가 안될 경우 
ALTER TABLE (테이블 명) DROP FOREIGN KEY (테이블 명)_ibfk_1;

C. 테이블에 데이터 삽입

INSERT INTO (테이블 명) VALUES (추가한 순서대로 값 삽입);

D. 테이블의 제약 조건 조회

select * from information_schema.table_constraints where table_name = '테이블이름';

3. 뷰

  • 뷰는 테이블의 실제 데이터가 포함되지 않는 가상의 테이블이다. 뷰와 테이블은 같은 네임 스페이스를 사용하므로 스키마 내 다른 이름과 중복되면 안된다.
    • 장점: 접근 제어로 보안 제공 / 데이터 관리가 편리
    • 단점: 삽입, 삭제, 갱신에 제약이 있다.

A. 뷰 생성, 조회, 삭제

//생성
create view (뷰 이름) as select (테이블의 열 이름1) (뷰에서 볼 열 이름1),(테이블의 열 이름2) (뷰에서 볼 열 이름2) ....
from (테이블 이름) where (열의 조건)

create view (뷰 이름)((뷰에서 볼 열 이름1), (뷰에서 볼 열 이름2)...) as select (테이블의 열 이름1),(테이블의 열 이름2) ....
from (테이블 이름) where (열의 조건)
//조회
SHOW FULL TABLES IN (데이터베이스 이름) WHERE TABLE_TYPE LIKE 'VIEW' // MySQL

// 삭제
DROP VIEW (뷰 이름);

4. 시퀸스

  • 유일한 연속적인 값을 생성할 수 있는 스키마 객체이다.
  • 기본 키나 유일 키에 값을 넣을때 사용한다.

A. 시퀸스 생성

CREATE SEQUENCE (시퀸스 이름)                         
		[ INCREMENT by n ]                   시퀸스 간격
    [ START WITH n ]                     시퀸스 시작 값
    [ MIN_VALUE]                         시퀸스 최솟값  
		[ {MAXVALUE n | NOMAXVALUE } ]       시퀸스 최댓값 / 최댓값 지정 X
    [ {CYCLE | NOCYCLE } ]               최댓값 도달시 재시작 O / X
    [ {CACHE n | NOCACHE } ]             캐시를 사용해서 미리 할당 O/ X
    [ {ORDER | NOORDER } ];              시퀸스 값 순서 유지

	
CREATE SEQUENCE s_dept_id
	MINVALUE 1
  MAXVALUE 99999
	INCREMENT BY 10
  START WITH 50
  NOCACHE
  NOORDER
  NOCYCLE;

B. 시퀸스 조회

  • 시퀸스는 USER_SEQUENCES 테이블에 저장되어 있고 SEQUENCE_NAME 열에 생성된 시퀸스의 이름이 저장되어있다.
desc user_sequences; //USER_SEQUENCES 테이블의 도메인 조회

select sequence_name from user_sequences; // 조회

C. 시퀸스 사용

  • INSERT 명령에서 시퀸스를 참조하여 자동으로 값을 생성
    ExpressionDescription
    sequence_name.NEXTVAL시퀀스의 다음 값을 반환
    sequence_name.CURRVAL시퀀스의 마지막 값은 반환
  • 아까 만든 시퀸스 s_dept_id는 시작이 50, 증가하는 크기가 10이다.
  • 아까 만든 테이블 s_dept 는 아래와 같다.
DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
  • INSERT 명령시 시퀸스를 참조한 결과
INSERT INTO s_dept VALUES(s_dept_id.nextval,'HR','SEOUL');
INSERT INTO s_dept VALUES(s_dept_id.nextval,'FINANCE','PARIS');
DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 HR             SEOUL
        60 FINANCE        PARIS

D. 시퀸스 삭제

DROP SEQUENCE (시퀸스 이름)

5. 쿼리 성능 향상

  • 데이터베이스 테이블에 하나 이상의 인덱스를 작성하여 일부 쿼리문의 성능을 향상 시킬 수 있다.
  • 데이터베이스에서 테이블의 데이터가 엑세스 되는 방법
    Access MethodDescription
    by ROWID데이터의 정확한 위치를 나타내는 행 주소를 사용한 방법
    FULL-TABLE SCAN테이블의 모든 행을 순차적으로 검색하는 방법
    by INDEX열 값의 정렬된 트리 구조를 사용한 이진 검색

    A. 인덱스 만들기; 열에 중복될 수 없는 유일 값 보장

    CREATE INDEX (인덱스 이름) ON (테이블 이름(인덱스 만드려는 열 이름));
  • 인덱스에 대한 정보는 USER_INDEXES 테이블에서 확인하고 이 테이블은 아래와 같이 구성되어있다.
    ColumnDescription
    INDEX_NAME인덱스 이름
    TABLE_OWNER인덱스 소유자
    TABLE_NAME인덱싱된 개체 이름
    TABLE_TYPE인덱싱된 개체 유형
    UNIQUESNESS인덱스의 고유성:
    UNIQUE or NONUNIQUE

    B. 인덱스 삭제

    DROP INDEX (삭제하려는 인덱스 이름);

6. Control User Access

  • 데이터 베이스 관리자는 사용자에게 SQL 보안 명령을 사용해 테이블에 대한 엑세스 권한을 제공한다.
  • 시스템 권한의 Type
    System PrivilegeDescription
    In One’s Own Schema자신의 스키마에 테이블 및 시퀀스를 생성할 수 있는 권한
    On all Objects of a Specified Type모든 스키마에서 테이블 생성 및 테이블 또는 뷰를 업데이트 할 수 있는 권한
    On the System or a User사용자를 생성할 수 있는 권한
  • 각 시스템 권한을 통해 사용자는 특정 작업을 수행할 수 있다. | Class | System
    Privilege | Operations
    Permitted |
    | --- | --- | --- |
    | SESSION | CREATE
    SESSION | 데이터베이스
    연결 허용 |
    | TABLE | CREATE
    TABLE | 테이블
    및 인덱스 생성 |
    | TABLE | CREATE
    TABLE | CONNECT,
    DML, DROP, ALTER, TRUNCATE 가능 |
    | TABLE | SELECT
    ANY TABLE | 모든
    스키마에 모든 테이블, 뷰 쿼리 사용 가능 |

A. 시스템 권한 부여

GRANT (부여되는 시스템 권한) TO (권한을 부여받는 대상)
ex)
CREATE USER scott IDENTIFIED by tibero; -- 사용자 생성
GRANT CREATE SESSION, CREATE TABLE TO scott; -- scott은 DB에 연결가능하고 테이블을 만들수 있는 권한 얻음
GRANT ALTER ANY TABLE TO scott; -- scott은 스키마의 테이블을 변경할 수 있는 권한 얻음
  • DBA_SYS_PRIVS 에서 부여된 시스템 권한을 확인할 수 있다.
    desc DBA_SYS_PRIVS; --> grantee, privilege, admin_option 도메인을 가진다.

select grantee,privilege from DBA_SYS_PRIVS where grantee='scott'; -- scott에게 부여된 시스템 권한을 조회할 수 있다.

B. 객체 권한 부여

  • 데이터 베이스 관리자가 사용자에게 부여할 수 있는 객체 권한으로는 테이블, 뷰, 시퀸스, 프로시저가 있다. | 스키마 객체 특권 | 테이블 | 뷰 | 시퀀스 | PSM
    프로그램
    (프러시저,
    함수
    등) | 디렉터리 |
    | --- | --- | --- | --- | --- | --- |
    | SELECT | O | O | O | | |
    | INSERT | O | O | | | |
    | ALTER | O | | O | | |
    | UPDATE | O | O | | | |
    | DELETE | O | O | | | |
    | TRUNCATE | O | | | | |
    | EXECUTE | | | | O | |
    | INDEX | O | | | | |
    | REFERENCES | O | O | | | |
    | READ | | | | | O |
    | WRITE | | | | | O |
  • 데이터 베이스 관리자는 GRANT 명령을 사용하여 객체의 권한을 부여할 수 있다.
    GRANT (부여되는 객체 권한) ON (특정 객체 일부 열) (스키마 객체 권한 대상이 되는 객체..테이블,뷰,시퀸스 등등) TO (그 권한을 부여받는 사용자) (부여받은 권한을 다른 사용자에게 부여 할 수 있는 권한)
ex)
GRANT SELECT ON s_emp TO scott;
GRANT SELECT,INSERT ON s_dept TO scott WITH GRANT OPTION; -- scott에게 s_dept 테이블 조회, 삽입에 대한 권한과 다른 사용자에게 권한을 부여할 수 있는 권한도 줌

GRANT SELECT ON sys.s_dept TO PUBLIC; -- scott이 모든 사용자에게 시스템에 있는 s_dept테이블을 조회할 수 있는 권한을 줬다.
  • USER_TAB_PRIVIS_MADE 에서 부여된 객체 권한을 확인할 수 있다.
    desc USER_TAB_PRIVIS_MADE

SELECT * FROM USER_TAB_PRIVIS_MADE WHERE GRANTEE ='scott'
-- 현재 사용자가 scott에게 부여된 객체 권한 조회
SELECT * FROM USER_TAB_PRIVIS_RECD;
-- 자신에게 부여된 객체 권한 조회

C. 권한 삭제

  • SQL 명령어 REVOKE를 사용하여 사용자에게 부여된 권한을 삭제할 수 있다.
REVOKE (부여된 권한중 삭제할 권한) ON (삭제할 권한이 속한 객체) FROM (권한을 삭제할 사용자 이름)
profile
이영민
이전 포스트

<TIL> File Programming

다음 포스트

<TIL> DML

0개의 댓글