TCL / DDL ( ALTER , DROP ) / DCL < 2021-09-27 >

목차

 1.   DDL (CREATE) + TCL 

+ DDL (SUBQUERY를 이용한 테이블 생성)

+ DDL (제약조건 추가)

+ TCL - COMMIT

         - ROLLBACK

         - SAVEPOINT

 

 2.  DDL (ALTER / DROP) 

+ 컬럼(COLUMN)의 추가 / 수정 / 삭제

+ 제약조건의 추가 / 삭제 

 

 3.  DCL 

+ 사용자 생성

+ 권한 부여 / 회수

---

 

1. DDL (CREATE) + TCL   

* DDL (서브쿼리를 이용한 테이블 생성) 

- 컬럼명, 데이터 타입, 값이 복사되고 제약조건은 NOT NULL만 복사됨

1) 테이블 전체 복사

CREATE TABLE '테이블명' AS
 SELECT * 
   FROM '복사할 테이블'


2) JOIN 후 원하는 컬럼만 테이블로 복사

CREATE TABLE '테이블명' AS
 SELECT '컬럼1', '컬럼2', '컬럼3' ..
   FROM '복사할 테이블'
  (LEFT) JOIN '조인할 테이블' ON / USING ( );
   

3) 데이터 없이 테이블의 틀만 복사하기

CREATE TABLE '테이블명' AS
 SELECT * 
   FROM '복사할 테이블'
  WHERE 1 = 0; --> 무조건 FALSE가 나오는 조건을 설정하여 빈 데이터 조회

 

* 제약조건 추가

- ALTER (바꾸다, 변조하다) : 객체의 구조를 변경하는 DDL임

 

ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명)

ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] FOREIGN KEY(컬럼명) REFERENCES 참조 테이블명(참조컬럼명)

ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] UNIQUE(컬럼명)

ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] CHECK(컬럼명 비교연산자 비교값)

ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;

 

* TCL (TRANSACTION CONTROL LANGUAGE) 

- 트랜잭션 관리(제어, 처리) 언어

TRANSACTION이란?

- 데이터베이스의 논리적 연산 단위

- 데이터의 변경 사항을 묶어 하나의 TRANSACTION에 담아서 처리한다

- TRANSACTION에 담겨지는 대상이 되는 SQL : INSERT, UPDATE, DELETE (DML , 데이터 조작 언어)

1) COMMIT : 메모리 버퍼에 임시 저장된 데이터 변경사항 (INSERT, UPDATE, DELETE)을 DB에 실제로 반영함.

2) ROLLBACK : 메모리 버퍼에 임시 저장된 데이터 변경사항 삭제, 마지막 COMMIT 상태로 돌아감.

3) SAVEPOINT : 저장 지점을 정의하여 ROLLBACK 시 TRANSACTION 전체의 내용을 삭제하는 것이 아니라

   저장 지점까지만 삭제하는 것

* SAVEPOINT 작성법

SAVEPOINT 포인트명1;

SAVEPOINT 포인트명2;

...

ROLLBACK TO 포인트명; ==> 포인트 지점까지만 ROLLBACK

 

*** TCL 주의사항 ***

1. TCL 구문은 DML에만 적용된다. (INSERT , UPDATE , DELETE)

2. DML 구문 작성 중 DDL 또는 DCL 구문이 수행되면 자동  COMMIT

==> TRANSACTION 내용이 바로 DB에 반영됨.

 

 

2. DDL (ALTER , DROP)

DDL (DATA DEFINITION LANGUAGE)

- 데이터 정의 언어 

- 객체를 만들고(CREATE) , 수정(ALTER)하고, 삭제(DROP) 하는 구문

 

* ALTER - 객체를 수정하는 구문

[ 표현식 ] 

ALTER TABLE 테이블명 수정할 내용;

컬럼의 추가 / 수정 / 삭제

1. 컬럼의 추가 / 수정 / 삭제


1) 컬럼의 추가

ALTER TABLE 테이블명
ADD ( 컬럼명 데이터 타입 );

- 컬럼 추가 시 DEFAULT값 지정
ALTER TABLE 테이블명
ADD ( 컬럼명 데이터 타입 DEFAULT '값' );


2) 컬럼 수정 
- 데이터 타입, 기본값 수정

ALTER TABLE 테이블명
MODIFY 컬럼명 (변경)데이터 타입

* 데이터 타입의 크기를 수정할 경우 기록된 값이 변경하려는 크기를 초과하면 오류발생
ORA-01441: cannot decrease column length because some value is too big

- DEFAULT 값 수정하기
ALTER TABLE 테이블명
MODIFY 컬럼명 DEFAULT '변경 값';

- 여러 컬럼 한번에 수정하기
ALTER TABLE 테이블명
MODIFY 컬럼명 (변경)데이터 타입
MODIFY 컬럼명 DEFAULT '변경 값';


3) 컬럼 삭제
* 데이터가 기록되어 있어도 삭제된다.
* 삭제된 컬럼은 복구가 거의 안됨 (DDL은 COMMIT/ROLLBACK 불가)
* 테이블에는 최소 한 개의 컬럼 존재해야함 => 모든 컬럼 삭제 불가능

ALTER TABLE 테이블명
DROP (삭제할 컬럼명);

* 만일 모든 컬럼을 삭제하려고하면
ORA-12983: cannot drop all columns in a table

- FK(외래키) 제약조건으로 인해 참조되고 있는 컬럼의 삭제
- 그냥 삭제하려고하면 오류 발생
ORA-12992: cannot drop parent key column

방법 : CASCADE CONSTRAINTS 옵션 사용
==> FK 제약조건을 무시하고 컬럼을 삭제하는 옵션

ALTER TABLE 테이블명
DROP (삭제할 컬럼명) CASCADE CONSTRAINTS;

 

제약조건의 추가 / 삭제

- 제약조건을 수정하는 구문은 없음

제약조건명은 임의로 지정하거나 안할 수 있음

- 제약조건 추가

1. PK 제약조건 추가
ALTER TABLE 테이블명
ADD CONSTRAINT 조건명 PRIMARY KEY (컬럼); -- 조건명 지정

2. UNIQUE 제약조건 추가
ALTER TABLE 테이블명
ADD  UNIQUE(컬럼명) (컬럼); -- 조건명 미지정

3. CHECK 제약조건 추가
ALTER TABLE 테이블명
ADD CHECK (컬럼 비교연산자 비교값);

*************************
4. NOT NULL 제약조건 추가
ALTER TABLE 테이블명
MODIFY 컬럼명 NOT NULL; -- NULL 저장 가능 여부를 변경하는 것.

* 만일 [ ADD 컬럼명 NOT NULL; ]이라고 작성하면 컬럼 추가 구문으로 인식함
need to specify the datatype for this column


- 제약조건 삭제

ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;

* NOT NULL 제약조건 삭제방법1
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;

* NOT NULL 제약조건 삭제방법2
ALTER TABLE 테이블명
MODIFY 컬럼명 CONSTRAINT 제약조건명 NULL;

 

컬럼 / 제약조건 / 테이블 이름 변경

1)컬럼 이름 변경(RENAME COLUMN 컬럼명 TO 변경명)

ALTER TABLE 테이블명
RENAME COLUMN 컬럼명 TO 변경할 컬럼명;

2) 제약조건 이름 변경(RENAME CONSTRAINT 제약조건명 TO 변경명)

ALTER TABLE 테이블명
RENAME CONSTRAINT 제약조건명 TO 변경할 제약조건명;

3) 테이블명 변경(RENAME [테이블명] TO 변경명)

ALTER TABLE 테이블명
RENAME TO 변경할 테이블명;

-------------------------------------------------------------------------------------------

테이블 삭제( DROP TABLE 테이블명 [CASCADE CONSTRAINTS]; )

DROP TABLE 테이블명;

** 참조 관계에서 부모테이블 삭제 시 발생하는 문제 및 해결법

- 참조관계의 부모테이블을 삭제하려고 하면 오류 발생
ORA-02449: unique/primary keys in table referenced by foreign keys

방법1) 자식 => 부모테이블 순으로 삭제
DROP TABLE 자식테이블;
DROP TABLE 부모테이블;

방법2) CASCADE CONSTRAINTS 옵션 사용
DROP TABLE 부모테이블 CASCADE CONSTRAINTS;
DROP TABLE 자식 테이블;

 

 

2. DCL (DATA CONTROL LANGUAGE) : 데이터 제어 언어

- 데이터베이스, 데이터베이스 내의 객체에 대한 접근 권한을 제어(부여 / 회수) 하는 언어

- GRANT : 권한 부여

- REVOKE : 권한 회수

시스템 권한 부여
- 사용자에게 시스템 권한을 부여할 때 사용

[표기법]
 GRANT 권한1, 권한2, ...
 TO 사용자 이름;
 
* 권한의 종류
CREATE SESSION	: 데이터베이스 접속 권한
CREATE TABLE	: 테이블 생성 권한
CREATE VIEW		: 뷰 생성 권한
CREATE SEQUENCE	: 시퀀스 생성 권한
CREATE PROCEDURE: 함수 생성 권한
CREATE USER		: 사용자(계정) 생성 권한
DROP USER		: 사용자(계정) 삭제 권한
DROP ANY TABLE	: 임의 테이블 삭제 권한

***********
계정의 종류
***********
1) 관리자 계정 
- 데이터베이스의 생성과 관리를 담당하는 계정이며 거의 모든 권한과 책임을 가짐
EX) sys, system

2) 사용자 계정
- 데이터베이스에 대하여 질의, 갱신 ,보고서 작성 등의 작업을 수행할 수 있는 계정
  업무에 필요한 최소한의 권한만 가지는 것이 원칙
  
* 사용자 계정 생성 - 관리자 계정으로 실행
CREATE USER 계정명 IDENTIFIED BY 비밀번호;

* 데이터베이스 접속 권한 부여 - 관리자 계정으로 실행
GRANT CREATE SESSION TO 계정명;

* 테이블 생성 권한 부여 - 관리자 계정으로 실행
GRANT CREATE TABLE TO 계정명;

* 테이블 공간 부여 - 관리자 계정으로 실행
ALTER USER 계정명 QUOTA 용량 ON SYSTEM;

 

* ROLE : 권한의 묶음

--> 여러가지 관련된 권한들을 묶어서 한번에 부여, 회수하는 용도

1) CONNECT : 데이터베이스 접속 권한 (== CREATE SESSION)
2) RESOURCE : 데이터베이스를 사용하기 위한 기본 생성 권한을 묶어둔 ROLE
- TABLE 외 7가지 생성 권한 + 공간을 기본값으로 자동 부여

EX) ROLE을 사용하여 sample 사용자 계정 생성 및 권한 부여 - 관리자 계정으로 실행

CREATE USER sample IDENTIFIED BY 1234;
GRANT CONNECT , RESOURCE TO sample;

 

* 객체에 권한 부여하기

- 특정 객체를 조작할 수 있는 권한을 부여

[표기법]
    GRANT 권한 종류 [(컬럼명)] | ALL
    ON 객체명 | ROLE 이름 | PUBLIC
    TO 사용자 이름;
    
    
    권한 종류           설정 객체
   
    SELECT              TABLE, VIEW, SEQUENCE
    INSERT              TABLE, VIEW
    UPDATE              TABLE, VIEW
    DELETE              TABLE, VIEW
    ALTER               TABLE, SEQUENCE
    REFERENCES          TABLE
    INDEX               TABLE
    EXECUTE             PROCEDURE
    
    
ex) sample 계정으로 sample2 계정의 TB 테이블 조회하기

SELECT *
  FROM sample2.TB;
-- ORA-00942: table or view does not exist

sample2 계정에 접속하여 sample 계정에 대한 권한 부여 - sample2 계정으로 접속
GRANT SELECT ON TB TO sample;

권한 회수하기
REVOKE SELECT ON TB FROM sample;

(TO / FROM) => 권한의 부여와 회수에 따라 바뀜

 

---