STUDY/SQL

[SQL] 2. SQL 기본 및 활용 - Chapter 3. 관리 구문 : DML, TCL, DDL, DCL

JUNGY00N 2023. 3. 13. 00:02

2023 유선배 SQL개발자(SQLD) 과외노트 를 읽고 내용을 정리한 글입니다. 

 

2023 유선배 SQL개발자(SQLD) 과외노트 - YES24

SQL Server 분야 베스트 1위!핵심만 쏙쏙 담은 알찬 수험서! SD에듀가 가장 효율적·효과적인 합격의 길을 제안합니다.유튜브 선생님에게 배우는 유·선·배, 『유선배 SQL개발자 과외노트』와 함께 20

www.yes24.com

DML

: Data Manipulation Language, DDL에서 정의한 대로 데이터를 입력하고, 입력된 데이터를 수정, 삭제, 조회하는 명령어

INSERT

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

INSERT INTO 입사 (부서명, 입사년월, 입사자사번 ) VALUES ('개발''202201','220101');

- 위 방식의 경우 명시되지 않은 컬럼에는 NULL값이 입력되는데, PK나 NOT NULL 제약조건이 걸린 컬럼에는 NULL 값이 입력될 수 없으니 주의해야 한다. 

INSERT INTO 입사 VALUES ('개발''202201','220101','신입');

- 위 방식의 경우 전체 컬럼에 대한 데이터가 테이블의 컬럼 순서대로 빠짐없이 나열되어야 하는데, 순서가 뒤바뀌어 데이터 유형이 맞지 않거나 누락된 데이터가 있어 전체 컬럼 갯수와 맞지 않았을 경우 데이터베이스는 에러를 발생시킨다. 

UPDATE

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

- 수정하고 싶은 컬럼이 많다면 SET 절에 콤마로 이어서 명시해줄 수 있다. 

- WHERE 절이 없으면 테이블의 모든 Row가 변경되니 주의해야 한다. 

UPDATE 테이블명 SET 컬럼명 = 새로운 데이터 (WHERE 수정할 데이터에 대한 조건);
UPDATE 입사 SET 구분 = '경력' WHERE 입사자사번 = '220101';

DELETE

: 이미 저장된 데이터를 삭제하고 싶을 때 사용하는 명령어 

- WHERE 절이 없으면 테이블의 모든 Row가 삭제되니 주의해야 한다. 

- 만약 WHERE 절 없이 테이블 전체 데이터를 삭제하고자 하는 경우 TRUNCATE 명령어를 사용할 수도 있다. 

- TRUNCATE는 별도의 로그를 쌓지 않아 ROLLBACK이 불가능하며 DELETE는 COMMIT 전에 ROLLBACK이 가능하다. 

DELETE FROM 테이블명 (WHERE 수정할 데이터에 대한 조건);
DELETE FROM 입사 WHERE 입사자사번 = '202201';
INSERT, UPDATE, DELETE
 명령어를 날리고 별도의 COMMIT 명령어를 실행시켜 주어야 데이터가 반영되며 ROLLBACK도 가능하다.
 ※ MSSQL의 경우에는  DML도 AUTO COMMIT 된다. 

MERGE 

: 테이블에 새로운 데이터를 입력하거나 이미 저장되어 있는 데이터에 대한 변경 작업을 한 번에 할 수 있도록 해주는 명령어 

TCL

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

  • COMMIT
  • ROLLBACK
  • SAVEPOINT

트랜잭션

: 쪼개질 수 없는 업무처리의 단위, 절대 한 세트로 묶일 수 밖에 없는 논리적인 업무 단위

- ex) 쇼핑몰에서 티셔츠를 산다고 가정했을 때의 결제 트랜잭션

  • 티셔츠를 하나 결제한다.
  • 티셔츠 재고가 하나 차감된다. 

- 이 두가지 액션은 하나로 묶여 동작되어야 한다.

트랜잭션의 특징

원자성

: 트랜잭션으로 묶인 일련의 동작들은 모두 성공하거나 모두 실패해야 한다.

- 살아도 같이 살고 죽어도 같이 죽는 관계 

일관성

: 트랜잭션이 완료된 후에도 데이터베이스가 가진 데이터에 일관성이 있어야 한다. 

- ex) 이미 결제된 티셔츠의 수량과 남아있는 티셔츠 재고의 합은 언제나 쇼핑몰이 처음 보유하고 있었던 티셔츠의 총 수량과 일치해야 한다.

고립성

: 하나의 트랜잭션은 고립되어 수행되어야 한다.

- ex) 구매하고자 하는 티셔츠를 지금 다른 사람이 먼저 구매하고 있다면 나는 재고 데이터를 참조하거나 변경할 수 없고 그 사람의 트랜잭션이 끝날 때까지 대기해야 한다.

지속성

: 트랜잭션이 성공적으로 수행되었을 경우 트랜잭션이 변경한 데이터가 영구적으로 저장되어야 함을 의미한다.

- 모든 트랜잭션이 로그에 남겨진 뒤 COMMIT 되어야 하고, 그래서 시스템 장애가 발생하더라고 복구 가능해야 한다는 의미이다. 

COMMIT

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

- COMMIT을 실행하지 않으면 메모리까지만 반영이 되는데, 메모리는 휘발성이기 때문에 언제든 사라질 수 있고, 다른 사용자는 변경된 값을 조회할 수 없다.

- COMMIT을 실행해야 최종적으로 데이터 파일에 기록이 되고 비로소 트랜잭션이 완료되는 것이다. 

- UPDATE 한 뒤 오랜 시간 동안 COMMIT이나 ROLLBACK을 하지 않았을 경우 Lock에 걸려서 다른 사용자가 변경할 수 없는 상황이 발생할 수 있으니 주의해야 한다. 

ROLLBACK

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

- ROLLBACK을 하면 변경하기 이전 값으로 복구된다.

- UPDATE 한 뒤 오랜 시간 동안 COMMIT이나 ROLLBACK을 하지 않았을 경우 Lock에 걸려서 다른 사용자가 변경할 수 없는 상황이 발생할 수 있으니 주의해야 한다. 

SAVEPOINT

: ROLLBACK을 수행할 때 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있게 하는 기능을 가진 명령어 

- ROLLBACK 뒤에 특정 SAVEPOINT를 지정해주면 그 지점까지만 데이터가 복구된다. 

DDL

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

- 테이블을 생성하면서 안에 담게 될 데이터에 대한 데이터 유형을 SQL에 명시해줌으로써 데이터를 정의한다.

- 데이터 유형은 크게 문자, 숫자, 날짜 타입으로 나뉜다.

- 선언해놓은 유형이 아닌 다른 유형의 데이터 유형을 저장하려고 하면 데이터베이스는 에러를 발생시키므로 주의가 필요하다. 

- 크기(Size)도 적절한 크기로 정의해주어야 한다. 

- 정의된 크기보다 더 큰 데이터를 저장하려고 할 때에도 데이터베이스는 에러를 발생시키므로 주의가 필요하다. 

  • CREATE
  • ALTER
  • DROP
  • RENAME
  • TRUNCATE
유형 데이터타입
문자 CHAR (크기가 고정)
VARCHAR (크기가 가변)
CLOB
숫자 NUMBER
날짜 DATE

CREATE

: 테이블을 생성하기 위한 명령어 

CREATE TABLE 테이블명 (
	컬럼명1 데이터타입(DEFAULT/NULL 여부),
    ...
);
  • NULL 
    : 공백과는 다르며, 존재하지 않는 값이라는 의미
  • DAFAULT
    : 데이터의 기본 값

테이블 생성 시 반드시 지켜야 할 규칙

- 지키지 않을 경우 에러 발생

  • 테이블명은 고유해야 한다.
  • 한 테이블 내에서 컬럼명은 고유해야 한다.
  • 컬럼명 뒤에 데이터 유형과 데이터 크기가 명시되어야 한다.
  • 컬럼에 대한 정의는 괄호 안에 기술한다.
  • 각 컬럼들은 콤마로 구분된다.
  • 테이블명과 컬럼명은 숫자로 시작될 수 없다.
  • 마지막은 세미콜론으로 끝낸다. 

제약조건(CONSTRAINT)

- CREATE TABLE을 할 때 제약조건도 함께 정의해 줄 수 있다.

- 제약조건은 테이블에 저장될 데이터의 무결성, 즉 데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정합이 없음을 보증하기 위해 해놓는 장치라고 볼 수 있다. 

- 테이블 생성 시 정의해야 할 필수 요소는 아니지만 데이터가 많이 쌓인 후에 정의하려고 하면 골치 아파지므로 초기에 정의해주는 것이 바람직하다. 

  • PRIMARY KEY(기본키)
    : 테이블에 저장된 각각의 Row에 대한 고유성을 보장한다.
    - 한 테이블에 하나씩만 지정할 수 있으며 PK로 지정된 컬럼에는 NULL 값이 입력될 수 없고 자동으로 UNIQUE 인덱스로 생성된다.
  • UNIQUE KEY(고유키)
    : PK와 유사하게 테이블에 저장된 각각의 Row에 대한 고유성을 보장하기 위한 제약조건이지만 NULL값이 허용된다는 차이점이 있다. 
  • NOT NULL
    : 해당 컬럼에 NULL값이 입력되는 것을 허용하지 않는 제약조건이다. 
  • CHECK
    : 컬럼에 저장될 수 있는 값의 범위를 제한한다. 
  • FORIGN KEY(외래키)
    : 하나의 테이블이 다른 테이블을 참조하고자 할 때 FK를 정의해 준다. 
    - 참조 무결성 제약 옵션은 별도로 선택 가능하다.
참조 무결성 규정 관련 옵션
CASCADE Parent 값 삭제 시 Child 값 같이 삭제
SET NULL Parent 값 삭제 시 Child의 해당 컬럼 NULL 처리
SET DEFAULT Parent 값 삭제 시 Child의 해당 컬럼 DEFAULT 값으로 변경
RESTRICT Child 테이블에 해당 데이터가 PK로 존재하지 않는 경우에만 Parent 값 삭제 및 수정 가능
NO ACTION 참조 무결성 제약이 걸려있는 경우 삭제 및 수정 불가

 

ALTER

: 테이블 구조를 변경해야 할 때 쓰는 명령어 

- 컬럼 변경, 컬럼 삭제, 제약조건 추가, 제약조건 삭제 등 

ADD COLUMN

: 새로운 컬럼을 추가할 때 쓰는 명령어 

- 추가된 컬럼의 위치는 늘 맨 끝이 되며 별도로 위치를 지정해 줄 수 없다. 

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

DROP COLUMN

: 기존의 컬럼을 삭제하는 명령어 

- 삭제한 컬럼은 복구할 수 없으므로 주의해야 한다. 

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

MODIFY COLUMN

: 기존에 있던 컬럼을 변경하는 명령어 

- 데이터 유형, DEFAULT 값, NOT NULL 제약조건에 대한 변경이 가능하다.

- 단, 컬럼에 저장된 모든 데이터의 크기가 줄이고자 하는 컬럼의 크기보다 작을 경우에만 줄일 수 있다.

- 컬럼에 저장된 데이터가 없는 경우에만 데이터 유형을 변경 할 수 있다.

- DEFAULT 값 변경 시에는 변경 이후 저장되는 데이터에만 적용되며 현재 NULL 값이 저장되어 있지 않은 컬럼에만 NOT NULL 제약 조건 추가가 가능하다. 

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

RENAME COLUMN

: 기존에 있던 컬럼의 이름을 변경하고 싶을 때 쓰는 명령어

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

ADD CONSTRAINT

: 제약조건을 추가하고 싶을 때 쓰는 명령어 

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

DROP TABLE

: 테이블을 삭제할 때 쓰는 명령어

- 해당 테이블을 참조하고 있는 다른 테이블이 존재하는 경우 CASCADE 옵션을 명시하지 않으면 삭제되지 않는다.

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

TRUNCATE TABLE

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

- DELETE 명령어와 유사하지만 저장공간이 재사용되도록 초기화된다는 차이점이 있고, ROLLBACK이 불가능해 DDL로 분류된다.

TRUNCATE TABLE 테이블명;

DCL

: USER를 생성하고 USER에게 데이터를 컨트롤 할 수 있는 권한을 부여하거나 회수하는 명령어

  • CREATE USER
  • ALTER USER
  • DROP USER

USER 관련 명령어

CREATE 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 SESSION, CREATE USER, CREATE TABLE 권한을 묶어서 CREATE_R이라고 지정할 수 있다.

- ROLE의 이름은 상황에 따라 적절하게 지정해 주면 된다. 

ROLE을 이용한 권한 부여

// ROLE을 생성한다.
CREATE ROLE 롤명;

// ROLE에게 권한을 부여한다.
GRANT 권한 TO 롤명;

// ROLE을 사용자에게 부여한다. 
GRANT 롤명 TO 사용자명;

 

저작자표시