[SQLD]「3」(3.1) DML, TCL, DDL, DCL

[SQLD]「3」(3.1) DML, TCL, DDL, DCL

「1」 데이터 모델링의 이해

「2」 SQL 기본 / 활용

> 「3」 관리 구문

 

> 1. DML

> 2. TCL

> 3. DDL

> 4. DCL

 

1. DML

DML

⸰ 입력된 데이터를 수정, 삭제, 조회하는 명령어

 

INSERT

⸰ 테이블에 데이터를 입력하는 명령어

√. 칼럼명 명시되지 않은 경우, 전체 데이터를 모두 삽입하여야 함

INSERT INTO 테이블명 (칼럼명1, 칼럼명2, ... ) VALUES (데이터1, 데이터2, ... );

⸰ 명시되지 않은 칼럼에는 NULL 값이 입력되는데, 해당 칼럼이 PK나, NOT NULL 제약조건이 걸린 칼럼인 경우 에러 발생

 

INSERT INTO 테이블명 VALUES (데이터1, 데이터2, 데이터3, ... );

⸰ 순서가 바뀌어 데이터 유형에 맞지 않거나, 전체 칼럼 개수와 맞지 않는 경우 에러 발생

 

UPDATE

⸰ 이미 저장된 데이터를 수정하고 싶을 때 사용하는 명령어

UPDATE 테이블명 SET 칼럼명1 = 데이터1, 칼럼명2 = 데이터2, ... (WHERE 조건);

 

DELETE

⸰ 이미 저장된 데이터를 삭제하는 명령어, ROLLBACK 가능

√. WHERE 절 없이 테이블 전체 데이터를 삭제하는 경우, TRUNCATE(ROLLBACK 불가능)를 사용하는 것이 좋음

DELETE FROM 테이블명 (WHERE 조건);

 

MERGE

⸰ 테이블에 새로운 데이터 입력, 이미 저장되어 있는 데이터 변경을 한 번에 할 수 있는 명령어

MERGE
 INTO 타겟_테이블명
USING 비교_테이블명
   ON 조건
 WHEN MATCHED THEN
      UPDATE
         SET 칼럼명1 = 새로운_데이터1, 칼럼명2 = 새로운_데이터2, ...
 WHEN NOT MATCHED THEN
      INSERT 칼럼명1, 칼럼명2, ...
      VALUES 데이터1, 데이터2, ...;

 

2. TCL

TCL

⸰ 트랜잭션을 제어하는 명령어

 

트랜잭션

⸰ 나눌 수 없는 업무 처리의 단위

  -원자성: 모두 성공 or 모두 실패

  -일관성: 트랜잭션 전과 후, 데이터베이스의 데이터가 일치

  -격리성: 하나의 트랜잭션은 고립되어 수행

  -지속성: 데이터가 영구적으로 저장

 

COMMIT

⸰ INSERT, DELETE, UPDATE 후 변경된 내용을 확정, 반영하는 명령어

⸰ 최종적으로 데이터 파일에 기록되고, 트랜잭션 완료

√.  COMMIT 후, 이전 SAVEPOINT도 삭제

 

ROLLBACK

⸰ INSERT, DELETE, UPDATE 후 변경된 내용을 취소하는 명령어

√. COMMIT 후, COMMIT 전에 있는 SAVEPOINT로 ROLLBACK 하면 에러 발생

 

SAVEPOINT

⸰ 전체 작업을 되돌리지 않고, 일부만 되돌릴 수 있게 하는 명령어

⸰ ROLLBACK 뒤에 특정 SAVEPOINT 를 지정해주면, 그 지점까지 데이터 복구

 

3. DDL

DDL

⸰ 데이터를 정의하는 명령어

⸰ 데이터에 대한 데이터 유형을 정함

문자	CHAR VARCHAR CLOB
숫자	NUMBER
날짜	DATE

 

-CHAR

  : 고정, 정의된 용량 전부 사용, 공백 무시하고 비교

-VARCHAR

  : 가변, 정의된 용량 중 실제 사용된 용량만 사용, 공백 포함하여 비교

 

CREATE

⸰ 테이블을 생성하는 명령어

⸰ 테이블 생성 시 제약조건을 함께 정의하는 것이 좋음

CREATE TABLE 테이블명 (
   칼럼명1  데이터_유형 [DEFAULT 값] [NOT NULL],
   칼럼명2  데이터_유형 [DEFAULT 값] [NOT NULL],
   ...
);

-DEFAULT

  : 값을 별도로 명시하지 않았을 때 저장되는 데이터의 기본값

-NULL

  : 공백(' ')과 다르며, 존재하지 않는 값

 

⸰ 테이블 생성 시 지켜야 할 규칙, 지키지 않으면 에러 발생

  - 테이블명은 고유

  - 한 테이블 내 칼럼명 고유

  - 칼럼명 뒤에 데이터 유형, 데이터 크기 명시

  - 칼럼에 대한 정의는 ( ) (괄호)안에 기술

  - 각 칼럼들은 , (콤마)로 구분

  - 마지막은 ; (세미콜론)

 

⸰ 테이블 생성 시 지향할 점

  - 테이블명은 각각의 정체성을 가져야 함

  - 칼럼명은 다른 테이블과 통일성 있어야 함

 

⸰ 제약조건의 종류

  -PRIMARY KEY

    : 테이블에 저장된 각각의 행에 대한 고유성 보장

    : 한 테이블 당 한 개

    √. 생성하지 않아도 됨

    : NULL 값 불가

    : UNIQUE 인덱스로 생성

  -UNIQUE KEY

    : 테이블에 저장된 각각의 행에 대한 고유성 보장

    : NULL 값 가능

  -NOT NULL

    : NULL 값 불가

  -CHECK

    : 칼럼의 도메인 지정

  -FOREIGN KEY

    : 하나의 테이블이 다른 테이블을 참조하기 위한 키를 정의

    : NULL 값 가능

    : 참조 무결성 제약 옵션 선택 가능

      ‣CASCADE: 부모 값 삭제 시 자식 값 같이 삭제

      ‣SET NULL: 부모 값 삭제 시 자식 값 NULL 값으로 변경

      ‣SET DEFAULT: 부모 값 삭제 시 자식 값 DEFAULT 값으로 변경

      ‣RESTRICT: 부모 값을 참조하지 않는 경우에만 부모 값 삭제, 수정 가능

      ‣NO ACTION: 참조 무결성 제약이 걸려있는 경우 삭제, 수정 불가

 

CTAS(CREATE TABLE ~ AS SELECT)

⸰ 기존에 존재하던 테이블을 복사해서 생성하는 명령어

⸰ 칼럼별로 데이터 유형을 다시 명시하지 않아도 됨

⸰ 제약조건 중 NOT NULL만 복사되고, 나머지 제약조건은 ALTER 명령어를 사용하여 정의해야 함

CREATE 테이블명 AS SELECT * FROM 복사할_테이블명;

 

ALTER

⸰ 테이블 구조를 변경하는 명령어

 

⸰ADD COLUMN

  - 새로운 칼럼 추가하는 명령어

  - 추가된 칼럼의 위치는 맨 끝 고정

ALTER TABLE 테이블명 ADD 칼럼명 데이터_유형;

 

⸰DROP COLUMN

  - 칼럼을 삭제하는 명령어

  - 삭제 시 복구 불가능

ALTER TABLE 테이블명 DROP COLUMN 칼럼명;

 

⸰MODIFY COLUMN

  - 기존의 칼럼을 변경하는 명령어

  - 데이터 크기 줄이기 : 줄이고자 하는 데이터 크기가 모든 데이터 중 가장 큰 경우만 줄일 수 있음

  - 데이터 크기 늘리기 : 모두 가능

  - 데이터 유형 변경 : 데이터가 없는 경우에만 가능

  - DEFAULT 값 변경 : 변경 이후 저장되는 데이터에만 적용

  - NOT NULL 추가 : 현재 NULL 값이 저장되어 있지 않은 칼럼만 가능

ALTER TABLE 테이블명 MODIFY (
   칼럼명1  데이터_유형 [DEFAULT 값] [NOT NULL], 
   칼럼명2  데이터_유형 [DEFAULT 값] [NOT NULL],
   ...
);

 

⸰RENAME COLUMN

  - 기존에 있던 칼럼명을 변경하는 명령어

ALTER TABLE 테이블명 RENAME COLUMN 기존_칼럼명 TO 변경할_칼렴명;

 

⸰ADD CONSTRAINT

  - 제약조건을 추가하는 명령어

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

 

DROP TABLE

⸰ 테이블을 삭제하는 명령어

⸰ CASCADE 옵션이 없고, 해당 테이블을 참조하는 다른 테이블이 존재하는 경우, 삭제되지 않음

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 

RENAME TABLE

⸰ 테이블명을 변경하는 명령어

RENAME 기존_테이블명 TO 변경할_테이블명;

 

TRUNCATE TABLE

⸰ 테이블에 저장되어 있는 데이터를 모두 제거하는 명령어

⸰ ROLLBACK 불가능

⸰ 저장 공간이 재사용되도록 초기화 (DELETE는 재사용 불가)

√. 데이터만 삭제, 테이블 삭제 X

TRUNCATE TABLE 테이블명;

 

4. DCL

DCL

⸰ USER 를 생성하여 권한을 부여하거나 회수하는 명령어

 

USER 관련 명령어

⸰CREATE USER

  - 사용자를 생성하는 명령어

CREATE USER 사용자명 IDENTIFIED BY 패스워드;

 

⸰ALTER USER

  - 사용자를 변경하는 명령어

ALTER USER 사용자명 IDENTIFIED BY 패스워드;

 

⸰DROP USER

  - 사용자를 삭제하는 명령어

DROP USER 사용자명;

 

권한 관련 명령어

⸰GRANT

  - 사용자에게 권한을 부여하는 명령어

GRANT 권한 TO 사용자명;

 

⸰REVOKE

  - 사용자에게 권한을 회수하는 명령어

REVOKE 권한 FROM 사용자명;

 

ROLE 관련 명령어

⸰ 특정 권한을 하나로 묶는 것

 

⸰ ROLE 을 이용한 권한 부여

CREATE ROLE 롤명;
GRANT 권한 TO 롤명;
GRANT 롤명 TO 사용자명;

- ROLE 을 생성

- ROLE 에 권한 부여

- ROLE 을 사용자에게 부여

 

 

DML	INSERT, UPDATE, DELETE, MERGE
TCL	COMMIT, ROLLBACK, SAVEPOINT
DDL	CREATE, ALTER, DROP, RENAME, TRUNCATE
DCL	CREATE USER, DROP USER, GRANT, REVOKE

 

 

 

 

 

유선배 SQLD 과외노트