#23. DML, DDL, DCL, TCL, OBJECT(VIEW)

DML : SELECT FROM, INSERT INTO, DELETE FROM, UPDATE SET

DDL : CREATE, DROP, ALTER

DCL : GRANT TO, REVOKE

TCL : COMMIT, ROLLBACK, SAVEPOINT

 

23-1. DML

(DATA MANIPULATION LANGUAGE)

데이터 조작 언어

 

DML을 쓰고나면 꼭 트랜잭션을 해줘야 한다(COMMIT 또는 ROLLBACK)

 

1. INSERT : 테이블에 새로운 행을 추가하는 구문 (순서가 중요하다)

1) INSERT INTO 테이블명 VALUES(값, 값, 값, ....);

INSERT INTO EMPLOYEE
VALUES (900, '김개똥', '880101-255934', 'KIM_GT@kh.or.kr', '01044445555', 'D1', 'J7', 'S2', 5000000, 0.2, 201, SYSDATE, NULL, DEFAULT);

 

2) INSERT INTO 테이블명(컬럼명, 컬럼명, 컬럼명) VALUES(값, 값, 값);

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, DEPT_CODE, JOB_CODE, SAL_LEVEL, HIRE_DATE)
VALUES (901, '정패진', '440404-3434444', 'D1', 'J2', 'S1', SYSDATE);

INSERT는 무조건 한 행 단위로 추가되기 때문에 선택이 안된 컬럼은 기본적으로 NULL값이 들어감

NOT NULL 제약조건이 걸려있는 컬럼은 반드시 선택해서 직접 값을 제시해야 함!
(단, 기본값(DEFAULT)이 지정되어있을 경우 기본값이 들어감)

 

3) INSERT INTO 테이블명 (서브쿼리)

INSERT INTO EMP_01 (
                    SELECT EMP_ID, EMP_NAME, DEPT_TITLE
                      FROM EMPLOYEE
                      LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
                    );

2. INSERT ALL 

1) INSERT ALL
       INTO 테이블명1 VALUES(컬럼명, 컬럼명, ...)
       INTO 테이블명2 VALUES(컬럼명, 컬렴명, ...)
       서브쿼리;

INSERT ALL
  INTO EMP_JOB VALUES(EMP_ID, EMP_NAME, JOB_NAME)       -- 9개행 INSERT
  INTO EMP_DEPT VALUES(EMP_ID, EMP_NAME, DEPT_TITLE)    -- 9개행 INSERT
SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE
  FROM EMPLOYEE
  JOIN JOB USING (JOB_CODE)
  JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
 WHERE SALARY >= 3000000;

총 18개 행이 들어갔다.

 

2) INSERT ALL
       WHEN 조건1 THEN
        INTO 테이블명1 VALUES(컬럼명, 컬럼명)
       WHEN 조건2 THEN
        INTO 테이블명2 VALUES(컬럼명, 컬럼명)
       서브쿼리;

INSERT ALL
  WHEN HIRE_DATE < '2010/01/01' THEN
  INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
  WHEN HIRE_DATE >= '2010/01/01' THEN
  INTO EMP_NEW VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
  FROM EMPLOYEE;

EMPLOYEE에서 조건에 맞게 두개의 테이블을 만든다.

 

3. UPDATE : 테이블에 기록된 기존의 데이터를 수정하는 구문

UPDATE 테이블명
       SET 컬럼명 = 바꿀값
             ,컬럼명 = 바꿀값
             ,...  => 여러개의 컬럼값 동시에 변결할 수 있음(,로 나열해야 함. AND 아님!!!!!!!!!!)
 WHERE 조건; => WHERE절은 생략 가능

UPDATE DEPT_COPY
   SET DEPT_TITLE = '미래교육부';

두가지 이상 값을 변경할 경우 콤마(,)를 이용해 바꿔준다. (SET을 여러번 쓰지 않도록 주의하자.)

UPDATE EMP_SALARY
   SET SALARY = 4000000
      ,BONUS = 0.2
 WHERE EMP_NAME = '이승금';

UPDATE에 서브쿼리를 넣어 사용할 수도 있다.

UPDATE EMP_SALARY
   SET DEPT_CODE = (SELECT DEPT_CODE
                      FROM EMPLOYEE
                     WHERE EMP_NAME = '김개똥')
 WHERE EMP_NAME = '이말똥';

김개똥의 DEPT_CODE를 조회해 이말똥의 DEPT_CODE를 바꿔준다.

 

4. DELETE : 테이블에 기록된 데이터를 삭제하는 구문

UPDATE 테이블명
       SET 컬럼명 = (서브쿼리)

 WHERE 조건; => 생략가능

 

1) DELETE

조건에 맞는 행을 삭제해주고 싶다면,  조건을 걸어주면 된다.

DELETE FROM EMPLOYEE
 WHERE EMP_NAME IN ('김개똥', '이말똥');

하지만 만약! 데이터를 가져다 쓰고 있는 자식테이블이 존재한다면 삭제되지 않는다.

 

테이블의 모든 행을 삭제하고 싶다면,

DELETE FROM EMPLOYEE;

 

2) TRUNCATE : 테이블의 전체 행을 삭제할 때 사용하는 구문(절삭)이다.

DELETE보다 수행속도가 빠르나(이론적으로)

별도의 조건 제시 불가, ROLLBACK이 불가능하다!

TRUNCATE TABLE EMP_SALARY;

"Table EMP_SALARY이(가) 잘렸습니다." (복구 불가)

 

 

23-2. DDL

(DATA DEFINITION LANGUAGE)

데이터 정의 언어

 

1. ALTER : 객체 구조를 수정하는 구문

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

- 수정할 내용
    1) 컬럼 추가 / 수정 / 삭제
    2) 제약조건 추가 / 삭제 => 수정은 불가(수정해야 할 경우 삭제 후 다시 추가)
    3) 테이블명 / 컬럼명 / 제약조건명 변경

 

ALTER는 테이블 안의 내용이 아닌 구조를 바꿔주는 거다! UPDATE랑 다르다~

 

1) 컬럼 추가 / 수정 / 삭제

1_1) 컬럼추가 (ADD) : ADD 추가할컬럼명 데이터타입 DEFAULT 기본값(DEFAULT 기본값은 생략 가능)

ALTER TABLE DEPT_CODE ADD CNAME VARCHAR2(20);

새롭게 컬럼이 만들어지고 기본적으로 NULL값으로 채워진다. 

DEFAULT값도 바로 지정해 줄 수 있다.

ALTER TABLE DEPT_COPY ADD LNAME VARCHAR2(20) DEFAULT '한국';

1_2) 컬럼 수정 (MODIFY) 

 - 데이터 타입 수정 : MODIFY 수정할컬럼명 바꾸고자하는데이터타입

ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(3);

변경하고자 하는 내용을 MODIFY 바로 뒤에 써주면 된다.

현재 변경하고자 하는 컬럼에 이미 담겨있는 값과 완전히 다른 타입으로 변경이 불가능하다

(ex. 문자 -> 숫자(X) / 문자열 사이즈 축소(X) - 이건 현재 들어가 있는 값에 지장이 없으면 가능.. / 사이즈확대 O )

 

 - DEFAULT 값 수정 : MODIFY 수정할컬럼명 DEFAULT 바꾸고자하는기본값

 ALTER TABLE DEPT_COPY MODIFY LNAME DEFAULT '미국';

 

1_3) 컬럼 삭제(DROP COLUMN) : DROP COLUMN 삭제하고자하는 컬럼명

ALTER TABLE DEPT_COPY2 DROP COLUMN DEPT_ID;

컬럼에 제약조건이 있으면 제약조건을 먼저 삭제해야 한다고 한다.

마지막 컬럼을 삭제할 때는 오류가 발생한다 !! => 테이블에 최소 한 개의 컬럼은 존재해야한다!

 

2) 제약조건 추가 / 삭제

2_1) 제약조건 추가

ADD [CONSTRAINT 제약조건명] 제약조건명

ALTER TABLE DEPT_COPY 
  ADD PRIMARY KEY(DEPT_ID)
  ADD CONSTRAINT DCOPY_UQ UNIQUE(DEPT_TITLE);

ADD 뒤에 테이블 라벨 방식 쓰듯이 써주면 된다.

 

2_2) 제약조건 삭제

DROP CONSTRAINT 제약조건명

ALTER TABLE DEPT_COPY DROP CONSTRAINT DCOPY_PK;

하지만 수정은 안된다.

  MODIFY LNAME CONSTRAINT DCOPY_NN NOT NULL;

불가..

 

3) 컬럼명 / 제약조건명 / 테이블명 변경(RENAME)

3_1) 컬럼명 변경 : RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명

ALTER TABLE DEPT_COPY RENAME DEPT_TITLE TO DEPT_NAME;

3_2) 제약조건명 변경 : RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명

ALTER TABLE DEPT_COPY RENAME CONSTRAINT SYS_C007119 TO DCOPY_NN

3_3) 테이블명 변경 : RENAME 기존테이블명 TO 바꿀테이블명

ALTER TABLE DEPT_COPY RENAME TO DEPT_TEST;

+) 제약조건명을 조회해보고 싶다면,

SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = '테이블명';

 

2. DROP : 객체를 삭제하는 구문

DROP TABLE DEPT_TEST;

데이터가 자식테이블에서 참조되고 있다면 삭제되지 않는다.

만약에 삭제하고 싶다면 

1. 자식테이블을 먼저 삭제한 후 부모테이블을 삭제하거나,

2. CASCADE를 이용하여 부모테이블만 삭제할 수 있다.

DROP TABLE 부모테이블명 CASCADE;

 

23-3. DCL

< DCL : DATA CONTROL LANGUAGE >
    데이터 제어 언어
    계정에게 시스템권한 또는 객체접근권한을 부여(GRANT)하거나 회수(REVOKE)하는 언어

 

1. GRANT

* 권한부여 (GRANT)
    - 시스템 권한    : 특정 DB에 접근하는 권한, 객체들을 생성할 수 있는 권한
    - 객체 접근 권한 : 특정 객체들에 접근해서 조작할 수 있는 권한
    
    * 시스템권한의 종류
    - CREATE SESSION : 계정에 접속할 수 있는 권한
    - CREATE TABLE : 테이블을 생성할 수 있는 권한
    - CREATE VIEW : 뷰를 생성할 수 있는 권한
    - CREATE SEQUENCE : 시퀀스를 생성할 수 있는 권한
    ......
    
    [ 표현법 ]
    GRANT 권한1, 권한2, ... TO 계정명;

-- 1. SAMPLE 계정 생성
CREATE USER SAMPLE IDENTIFIED BY SAMPLE;

-- 2. SAMPLE 계정에 접속하기 위한 CREATE SESSION 권한 부여
GRANT CREATE SESSION TO SAMPLE;

-- 3_1. SAMPLE 계정에 테이블을 생성할 수 있는 CREATE TABLE 권한 부여
GRANT CREATE TABLE TO SAMPLE;

-- 3_2. SAMPLE 계정에 테이블스페이스를 할당해주기 (SAMPLE 계정 변경)
ALTER USER SAMPLE QUOTA 2M ON SYSTEM;
-- 2M : 2MEGA BYTE

-- 4. SAMPLE 계정에 뷰를 생성할 수 있는 CREATE VIEW 권한 부여
GRANT CREATE VIEW TO SAMPLE;

    * 객체 권한
    특정 객체들을 조작(SELECT, INSERT, UPDATE, DELETE)할 수 있는 권한
    
    [ 표현법 ]
    GRANT 권한종류 ON 특정객체 TO 계정명;
    
    * 객체권한 종류
    권한종류 : 특정객체
    ==================================================
    SELECT | TABLE, VIEW, SEQUENCE
    INSERT | TABLE, VIEW,
    UPDATE | TABLE, VIEW,
    DELETE | TABLE, VIEW
    ...

-- 5. SAMPLE 계정에 KH.EMPLOYEE 테이블을 조회할 수 있는 권한 부여
GRANT SELECT ON KH.EMPLOYEE TO SAMPLE;

-- 6. SAMPLE 계정에 KH.DEPARTMENT 테이블에 행을 삽입할 수 있는 권한 부여
GRANT INSERT ON KH.DEPARTMENT TO SAMPLE;

-- 최소한의 권한을 부여하고자 할 때, CONNECT, RESOURCE만 부여
-- GRANT, CONNECT, RESOURCE TO 계정명;

 

    < 롤 ROLE >
    특정 권한들을 하나의 집합으로 모아놓은 것
    
    CONNECT : CREATE SESSION (데이터베이스에 접속할 수 있는 권한)
    RESOURCE : CREATE TABLE, CREATE SEQUENCE(특정 객체들을 생성 및 관리할 수 있는 권한)

SELECT * 
  FROM ROLE_SYS_PRIVS
 WHERE ROLE IN ('CONNECT', 'RESOURCE');

 

2. REVOKE

    * 권한 회수 (REVOKE)
    권한을 회수할 때 사용하는 명령어
    
    [ 표현법 ]
    REVOKE 권한1, 권한2, ... FROM 사용자이름;

-- 7. SAMPLE 계정에게 테이블을 생성할 수 없도록 권한 회수
REVOKE CREATE TABLE FROM SAMPLE;

 

23-4. TCL

    < TCL : TRANSACTION CONTROL LANGUAGE >
    트랜잭션을 제어하는 언어
    
    * 트랜잭션(TRANSACTION) 
    - 데이터베이스의 논리적 연산단위
    - 데이터의 변경사항(DML)들을 하나의 트랜잭션에 묶어서 관리
      COMMIT(확정)하기 전까지의 변경사항들을 하나의 트랜잭션에 담게 됨
    - 트랜잭션의 대상이 되는 SQL : INSERT, UPDATE, DELETE (DML)
    
    COMMIT (트랜잭션 종료 처리 후 확정), ROLLBACK(트랜잭션 취소), SAVEPOINT(임시저장점 잡기)
    
    * TCL의 종류
    - COMMIT; : 하나의 트랜잭션에 담겨 있는 변경사항들을 실제 DB에 반영하겠다는 의미
                실제 DB에 반영시킨 후 트랜잭션은 비워짐
    - ROLLBACK; : 하나의 트랜잭션에 담겨있는 변경사항들을 모두 삭제한 후 마지막 COMMIT시점으로 돌아감
    - SAVEPOINT 포인트명 : 현재 이 시점에 임시저장점을 정의해두는 것
    - ROLLBACK 포인트명 : 전체 트랜잭션을 바꾸는 것이 아니라 해당 포인트 지점까지의 트랜잭션만 롤백함

 

    DDL구문(CREATE, ALTER, DROP)을 실행하는 순간
    기존에 트랜잭션에 있던 모든 변경사항들은 무조건 실제DB에 반영(COMMIT) 시킨 후에 DDL을 수행!
    => DDL 수행전 변경사항들이 있었다면 COMMIT/ROLLBACK하고 나서 DDL을 수행해야 함!