| 목차 | |
| 19. | DDL |
| 20. | DCL |
19. DDL
● DDL(Data Definition Language)
- 데이터 정의어
- 데이터 구조 정의(객체 생성, 삭제, 변경) 언어
- CREATE(객체 생성), ALTER(객체 변경), DROP(객체 삭제), TRUNCATE(데이터 삭제)
- AUTO COMMIT(명령어 수행하면 즉시 저장, 원복 불가)
● CREATE
- 테이블이나 인덱스와 같은 객체를 생성하는 명령어
- 테이블 생성 시 테이블명, 컬럼명, 컬럼순서, 컬럼크기, 컬럼의 데이터타입 정의 필수
- 테이블 생성 시 각 컬럼의 제약조건 및 기본값은 생략 가능
- 테이블 생성 시 소유자 명시 가능(생략 시 명령어 수행 계정 소유)
- 숫자컬럼의 경우 컬럼 사이즈 생략 가능(날짜 컬럼은 사이즈 명시 X)
** 문법 1
** 문법 2(테이블 복제)
** 특징
- 복제테이블의 컬럼명과 컬럼의 데이터 타입이 복제됨
- SELECT 문에서 컬럼별칭 사용 시 컬럼별칭 이름으로 생성
- CREATE 문에서 컬럼명 변경 가능
- NULL 속성도 복제됨
- 테이블에 있는 제약조건, INDEX 등은 복제되지 X
● 데이터타입
-> SQL Server 의 경우도 유사, VARCHAR2 -> VARCHAR 사용, NUMBER -> NUMERIC 사용
-> SQL Server 의 경우 문자타입도 사이즈 생략 가능(생략 시 1)
※ NUMBER(7,2)의 경우 총 자리수가 7을 초과할 수 없음
예제) MERGE_OLD 테이블 만들기
예제) EMP 테이블을 복제하여 TEST 테이블 만들기
예제) EMP 테이블 데이터 없이 구조만 복제
-> 항상 거짓인 조건을 SELECT 절에 전달하면, 데이터는 아무것도 출력되지 않지만 컬럼 정보들은 출력됨 따라서 테이블 내용은 제외하고 구조만 복제할 때 주로 사용!
예제) 테이블 복제 시 컬럼명 변경 가능
< 결과 >
● ALTER
- 테이블 구조 변경(컬럼명, 컬럼 데이터타입, 컬럼사이즈, DEFAULT 값, 컬럼삭제, 컬럼추가, 제약조건)
- 컬럼순서 변경 불가(재생성으로 해결)
1. 컬럼 추가
- 새로 추가된 컬럼위치는 맨 마지막(절대 중간 위치에 추가 불가)
- 컬럼 추가 시 데이터타입 필수, DEFAULT 값, 제약조건을 명시할 수 있음
- 여러 컬럼 동시 추가 가능(반드시 괄호 사용)
** 문법)
예제) 컬럼 추가
※ 동시에 여러 컬럼을 추가할 경우 반드시 괄호와 함께 전달!
※ 컬럼 추가시 NOT NULL 속성 전달 불가(컬럼 추가 시 모두 NULL인 값을 갖고 추가되므로)
※ 컬럼 추가시 DEFAULT 을 선언하면 NOT NULL 속성을 갖는 컬럼 추가 가능
※ 순서 주의(NOT NULL 은 DEFAULT 값 선언 뒤)
2. 컬럼(속성) 변경
- 컬럼 사이즈, 데이터타입, DEFAULT 값 변경 가능
- 여러 컬럼 동시 변경 가능
**문법
☞ 괄호 생략 가능
1) 컬럼 사이즈 변경
- 컬럼 사이즈 증가는 항상 가능
- 컬럼 사이즈 축소는 데이터 존재 여부에 따라 제한(데이터가 있는 경우 데이터의 최대 사이즈 만큼 축소 가능)
- 동시 변경 가능(반드시 괄호 필요)
예제) 여러 컬럼 사이즈 수정
-> 최대 길이보다 크거나 같은 사이즈로는 변경 가능
2) 데이터 타입 변경
- 빈 컬럼일 경우 데이터 타입 변경 가능
- CHAR, VARCHAR 타입일 경우 데이터가 있어도 서로 변경 가능
예제) 데이터타입 변경
< 테이블 구조 및 데이터 >
< 컬럼 데이터 타입 변경 >
예제) CHAR <-> VARCHAR 데이터타입 변경
< 테이블 구조 및 데이터 >
< 컬럼 데이터 타입 변경 >
3) DEFAULT 값 변경
- DEFAULT 값이란 특정 컬럼에 값이 생략될 경우(입력 시 언급되지 않을 경우) 자동으로 부여되는 값
- INSERT 시 DEFAULT 값이 선언된 컬럼에 NULL을 직접 입력할 때는 DEFAULT 값이 아닌 NULL 이 입력됨
- 이미 데이터가 존재하는 테이블에 DEFAULT 값 선언 시 기존 데이터 수정 안됨(이후 입력된 데이터부터 적용)
- DEFAULT 값 해제 시 DEFAULT 값을 NULL 로 선언
예제) DEFAULT 값 변경 및 적용
< TEST DATA 생성 >
< DEFAULT 값 수정 >
< 새로운 값 입력 >
< 데이터 확인 >
-> PARK 는 SAL 값이 DEFAULT 값이 아닌 NULL 로 입력됨
-> CHOI 는 SAL 값이 DEFAULT 값으로 입력됨(입력 시 SAL 컬럼 언급 안됐기 때문)
3. 컬럼 이름 변경
- 항상 가능
- 동시 여러 컬럼 이름 변경 불가(괄호 전달 불가)
- ALTER ... RENAME 명령어로 처리
** 문법
예제) 컬럼 이름 변경
< 테이블 구조 >
< 컬럼 이름 변경 >
4. 컬럼 삭제
- 데이터 존재 여부와 상관없이 언제나 가능
- RECYCLEBIN 에 남지 X(FLASHBACK 으로 복구 불가)
- 동시 삭제 불가
예제) COL_A 컬럼의 삭제
예제) 2 개이상 컬럼 삭제 시도 시 에러 발생
-> 괄호로 묶어서 전달하여도 동시 삭제 불가
● DROP
- 객체(테이블, 인덱스 등) 삭제
- DROP 후에는 조회 불가
**문법
※ PURGE 로 테이블 삭제시 RECYCLEBIN 에서 조회 불가
예제) TEST 테이블 DROP 후 조회 결과
-> 테이블 또는 뷰가 존재하지 않는다는 에러 발생
● TRUNCATE
- 구조 남기고 데이터만 즉시 삭제, 즉시 반영(AUTO COMMIT)
- RECYCLEBIN 에 남지 않음
**문법
예제) TRUNCATE 사용하여 데이터 전부 삭제
< 삭제 후 데이터 조회 >
-> 구조(테이블 명, 컬럼 등)만 남고 데이터는 삭제된 것을 확인할 수 있음
● DELETE / DROP / TRUNCATE 차이
- DELETE : 데이터 일부 또는 전체 삭제, 롤백 가능
- TRUNCATE : 데이터 전체 삭제만 가능(일부 삭제 불가), 즉시 반영(롤백 불가)
- DROP : 데이터와 구조를 동시 삭제, 즉시 반영(롤백 불가)
● 제약조건
- 데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치
- 테이블 생성 시 정의 가능, 컬럼 추가 시 정의 가능, 이미 생성된 컬럼에 제약조건만 추가 가능
1. PRIMARY KEY(기본키)
- 유일한 식별자(각 행을 구별할 수 있는 식별자 기능)
- 중복 허용 X, NULL 허용 X => UNIQUE + NOT NULL
- 특정 컬럼에 PRIMARY KEY 생성하면 NOT NULL 속성 자동 부여(CTAS 로 테이블 복사 시 복사되지 X)
- 하나의 테이블에 여러 기본키를 생성할 수 없음
- 하나의 기본키를 여러 컬럼을 결합하여 생성할 수 있음
- PRIMARY KEY 생성 시 자동으로 UNIQUE INDEX 생성
**문법
1) 테이블 생성 시 제약조건 생성
2) 컬럼 추가 시 제약 조건 생성
3) 이미 생성된 컬럼에 제약조건만 추가
4) 제약조건 삭제
예제) 테이블 생성 시 제약조건 설정(이름 전달 없이)
-> 제약조건 생성 시 이름을 설정하지 않으면 자동으로 부여
-> CREATE 문 밑에 제약조건 이름과 함께 전달 가능!
예제) 테이블 생성 시 제약조건 설정(이름과 함께 전달)
예제) 컬럼 추가 시 제약조건 생성
예제) 이미 있는 컬럼에 제약조건만 생성
2. UNIQUE
- 중복을 허용하지 않음
- NULL은 허용
- UNIQUE INDEX 자동 생성
예제) UNIQUE KEY 가 생성된 컬럼의 값 입력
< UNIQUE KEY 가 생성 >
< 값의 입력 >
< 확인 >
3. NOT NULL
- 다른 제약조건과 다르게 컬럼의 특징을 나타냄 => CTAS 로 복제 시 따라감
- 컬럼 생성 시 NOT NULL 을 선언하지 않으면 Nullable 컬럼으로 생성됨
- 이미 만들어진 컬럼에 NOT NULL 선언 시 제약조건 생성이 아닌 컬럼 수정(MODIFY)으로 해결
예제) NOT NULL 선언
4. FOREIGN KEY
- 참조테이블의 참조 컬럼에 있는 데이터를 확인하면서 본 테이블 데이터를 관리할 목적으로 생성
- 반드시 참조(부모)테이블의 참조 컬럼(REFERENCE KEY)이 사전에 PK 혹은 UNIQUE KEY 를 가져야 함!
**문법
예제) FOREIGN KEY 테스트
** 테스트 테이블 생성
** 부모 테이블(DEPT_TEST1)에 REFERENCE KEY(참조대상)에 PK 설정!
** 자식 테이블(EMP_TEST1)에 FOREIGN KEY 생성
TEST1) 자식 테이블(EMP_TEST1)에서 10 번 부서원 삭제 시도
TEST2) 자식 테이블(EMP_TEST1)에서 20 번 부서원 50 번으로 변경 시도(불가)
-> 부모 테이블에 50 번 부서번호가 정의되어 있지 않아 자식 테이블에 해당 값으로 수정 불가!
TEST3) 자식 테이블(EMP_TEST1)에서 50 번 부서원(나머지 정보 자유) 입력 시도(불가)
-> 부모 테이블에 50 번 부서번호가 정의되어 있지 않아 자식 테이블에 해당 값으로 입력 불가!
TEST4) 부모 테이블(DEPT_TEST1)에 20 번 부서원 삭제 시도(불가)
-> 20 번 부서 정보가 자식 테이블에 존재하므로 삭제 불가
TEST5) 부모 테이블(DEPT_TEST1)에 20 번 부서원의 부서번호를 60 변경 시도(불가)
-> 20 번 부서 정보가 자식 테이블에 존재하므로 다른 값으로 변경 불가
● FOREIGN KEY 옵션(생성 시 정의, 변경 불가 -> 재생성)
1. ON DELETE CASCADE : 부모 데이터 삭제 시 자식 데이터 함께 삭제
2. ON DELETE SET NULL : 부모 데이터 삭제 시 자식 데이터의 참조값은 NULL 로 수정
예제) FOREIGN KEY 옵션 TEST(ON DELETE CASCADE )
< FOREIGN KEY 재생성(ON DELETE CASCADE ) >
< 부모 데이터 삭제 >
-> 부모 데이터 삭제 시, 자식 데이터도 함께 삭제됨!
예제) FOREIGN KEY 옵션 TEST(ON DELETE SET NULL)
< FOREIGN KEY 재생성(ON DELETE SET NULL ) >
< 부모 데이터 삭제 >
-> 자식 테이블의 데이터도 함께 삭제되지 않음(NULL 로 수정)
5. CHECK
- 직접적으로 데이터의 값 제한
ex) 양수(1, 2, 3, 4) 중 하나
예제) EMP_TEST1 테이블의 SAL 값은 0 이상이어야 한다는 CHECK 제약조건 추가
● 기타 오브젝트
1) 뷰(VIEW)
- 저장공간을 가지지는 않지만 테이블처럼 조회 및 수정할 수 있는 객체
** 뷰(VIEW)의 종류
- 단순뷰 : 하나의 테이블 조회 뷰(VIEW)
- 복합뷰 : 둘 이상의 테이블 조인 뷰(VIEW)
** 뷰(VIEW)의 특징
- 뷰(VIEW)는 기본 테이블로부터 유도된 테이블이기에 기본 테이블과 같은 형태의 구조를 가지고 있으며, 조작도 기본 테이블과 거의 같음
- 뷰는 가상의 테이블이기에 물리적으로 구현되어 있지 않으며 저장공간을 차지하지 않음
- 데이터를 안전하게 보호가능
- 이미 정의되어 있는 뷰(VIEW)는 다른 뷰(VIEW)의 정의에 기초가 될 수 있음
- 기본 테이블이 삭제되면 그 테이블을 참조하여 만든 뷰 역시 삭제됨
** 뷰(VIEW)의 장점
- 논리적 독립성을 제공
- 데이터의 접근을 제어 함으로써 보안유지
- 사용자의 데이터 관리 단순화
- 데이터의 다양한 지원가능
** 뷰(VIEW)의 단점
- 뷰의 정의 변경 불가
- 삽입, 삭제, 갱신 연산에 제한
- 인덱스 구성불가
** 문법
** 뷰(VIEW)의 삭제
예제) 뷰 생성 및 조회
< 생성 >
-> EMP 테이블과 DEPT 테이블에서 가져올 데이터를 선택
< 조회 >
2) 시퀀스(SEQUENCE)
- 자동으로 연속적인 숫자 부여해주는 객체
** 문법
3) 시노님(SYNONYM)
- 테이블 별칭 생성
EX) HR 계정에서 SCOTT.EMP 를 EMP 로 조회하는 방법
** 문법
-> OR REPLACE : 기존에 같은 이름으로 시노님이 생성되어 있는 경우 대체
-> PUBLIC : 시노님을 생성한 유저만 사용 가능한 PRIVATE SYNONYM 의 반대(누구나 사용가능)
-> PUBLIC 으로 생성한 시노님은 반드시 PUBLIC 으로 삭제
예제) 시노님 생성 전 후 테이블 조회비교(HR 계정으로 조회)
< 시노님 생성 >
< 시노님 생성 전 HR 계정에서 EMP 조회 >
< 시노님 생성 후 HR 계정에서 EMP 조회 >
20. DCL
● DCL(Data Control Language)
- 데이터 제어어로 객체에 대한 권한을 부여(GRANT)하거나 회수(REVOKE)하는 기능
- 테이블 소유자는 타계정에 테이블 조회 및 수정 권한 부여 및 회수 가능
● 권한
- 일반적으로 본인(접속한 계정) 소유가 아닌 테이블은 원칙적으로 조회 불가(권한 통제)
- 업무적으로 필요시 테이블 소유자가 아닌 계정에 테이블 조회, 수정 권한 부여 가능
※ 권한 종류
1) 오브젝트권한
- 테이블에 대한 권한 제어
ex) 특정 테이블에 대한 SELECT, INSERT, UPDATE, DELETE, MERGE 권한
- 테이블 소유자는 타계정에 소유 테이블에 대한 조회 및 수정 권한 부여 및 회수 가능 페이지
2) 시스템권한
- 시스템 작업(테이블 생성 등)등을 제어
ex) 테이블 생성 권한, 인덱스 삭제 권한
- 관리자 권한만 권한 부여 및 회수 가능
● GRANT
- 권한 부여 시 반드시 테이블 소유자나 관리자계정(SYS, SYSTEM)으로 접속하여 권한을 부여하여야 함
- 동시에 여러 유저에 대한 권한 부여 가능
- 동시 여러 권한 부여 가능
- 동시 여러 객체 권한 부여 불가
** 문법
예제) 오브젝트 권한 부여(PROFESSOR 소유자 실행)
예제) 시스템 권한 부여(관리자 권한으로 실행)
● REVOKE
- 동시 여러 권한 회수 가능
- 이미 회수된 권한 재회수 불가
- 동시 여러 유저로부터의 권한 회수 가능
** 문법
예제) 오브젝트 권한 회수
● 롤(ROLE)
- 권한의 묶음(생성 가능한 객체)
- SYSTEM 계정에서 ROLE 생성 가능
** 문법
예제) 롤(ROLE)
< 생성 >
< 롤에 권한 담기 >
< 롤 부여 >
< HR 계정에서 수행 >
< 롤에서 권한 빼기 >
< 권한 회수 후 HR 계정에서 조회 >
-> 권한이 불충분하다는 에러
-> ROLE 에서 회수된 권한은 즉시 반영되므로 다시 ROLE 을 부여할 필요가 없음
< 롤을 통해 부여한 권한 직접 회수(SCOTT 에서 실행) >
-> ROLE 을 통해 부여한 권한은 직접 회수 불가
-> ROLE 을 통한 회수만 가능
● 권한부여 옵션(중간관리자의 권한)
1. WITH GRANT OPTION
- WITH GRANT OPTION 으로 받은 오브젝트 권한을 다른 사용자에게 부여할 수 있음
- 중간관리자(WITH GRANT OPTION 으로 권한을 부여받은 자)가 부여한 권한은 중간관리자만 회수 가능
- 중간관리자에게 부여된 권한 회수 시 제 3 자에게 부여된 권한도 함께 회수됨
2. WITH ADMIN OPTION
- WITH ADMIN OPTION 을 통해 부여 받은 시스템 권한/롤 권한을 다른 사용자에게 부여할 수 있음
- 중간관리자를 거치지 않고 직접 회수 가능
- 중간관리자 권한 회수시 제 3 자에게 부여된 권한도 함께 회수 X(남아있음)
예제) WITH GRANT OPTION / WITH ADMIN OPTION TEST
권한 부여)
오브젝트 권한 회수 시도)
-> 중간관리자를 통해 부여한 제 3 계정의 권한은 관리자가 직접 회수 불가
-> 대신 중간관리자에게 부여된 권한을 회수(회수 시 제 3 의 계정에 부여된 권한도 함께 회수됨)
-> 중간관리자(HDATALAB)에 의해 부여된 제 3 의 계정(PARK) 권한도 함께 회수됨&nb
'자격증 > SQLD' 카테고리의 다른 글
| SQLD SQL 기본 및 활용(17~18) - 16일차 (0) | 2024.11.10 |
|---|---|
| SQLD SQL 기본 및 활용(13~16) - 15일차 (0) | 2024.11.09 |
| SQLD SQL 기본 및 활용 (9~12) - 14일차 (0) | 2024.11.09 |
| SQLD SQL 기본 및 활용(5~8) - 13일차 (0) | 2024.11.03 |
| SQLD SQL 기본 및 활용(1~4) - 12일차 (0) | 2024.10.25 |