[SQLD] 관리구문 - DML, DDL, DCL, TCL

DML

• DML이란? DML은 Data Manipulation Language란 의미로, DDL에서 정의한 대로 데이터를 입력하고, 입력된 데이터를 수정, 삭제, 조회하는 명령어이다.

INSERT

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

INSERT INTO 테이블명 (컬럼명, 컬럼명2) VALUES (데이터1, 데이터2);
INSERT INTO 테이블명 VALUES (전체 컬럼에 입력될 데이터 리스트);

• 명시되지 않은 컬럼에는 NULL 값이 입력되는데, PK와 같이 NOT NULL 제약조건이 걸린 컬럼에는 NULL 값이 입력될 수 없으니 주의해야 한다.
• 전체 컬럼에 대한 데이터가 테이블의 컬럼 순서대로 빠짐없이 나열되어야 하는데, 순서가 바뀌어 데이터 유형이 맞지 않거나 누락된 데이터가 생기지 않도록 주의해야 한다.

UPDATE

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

UPDATE 테이블명 SET 컬럼명 = 새로운 데이터 (WHERE 수정할 데이터에 대한 조건);

• 수정하고 싶은 컬럼이 많은 경우 SET 절에 , (콤마)로 이어서 명시할 수 있다.
• SET (컬럼명1 = 데이터, 컬럼명2 = 데이터)
• WHERE 절이 없는 경우 테이블의 모든 Row가 변경되니 주의해야 한다.

DELETE

• 저장된 데이터를 삭제하고 싶을 때 사용하는 명령어
• WHERE 절이 없는 경우 테이블의 모든 Row가 삭제되니 주의해야 한다.

DELETE FROM 테이블명 (WHERE 수정할 데이터에 대한 조건);

• 테이블 전체 데이터를 삭제하고자 하는 경우 TRUNCATE 명령어를 사용할 수도 있는데, 이는 별도의 로그를 쌓지 않아 ROLLBACK이 불가능하다.
• DELETE는 COMMIT 전에 ROLLBACK이 가능하다.
• INSERT, UPDATE, DELETE의 경우 명령어를 날리고 별도의 COMMIT 명령어를 실행시켜 주어야 데이터가 반영되며 ROLLBACK이 가능하다. 단, SQL Server(MSSQL)의 경우 DML도 AUTO COMMIT 된다.

MERGE

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

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

TCL

• 트랜잭션이란 쪼개질 수 없는 업무처리의 단위로, 논리적인 업무 단위라고 할 수 있다.
• 트랜잭션의 특징
  • 원자성(Atomicity) : 트랜잭션으로 묶인 일련의 동작들은 모두 성공하거나 모두 실패해야 한다.
  • 일관성(Consistency) : 트랜잭션이 완료된 후에도 데이터베이스가 가진 데이터에 일관성이 있어야 한다.
  • 고립성(Isolation) : 하나의 트랜잭션은 고립되어 수행되어야 한다. 다른 트랜잭션이 끝날 때까지 대기해야 한다.
  • 지속성(Durability) : 트랜잭션이 성공적으로 수행되었을 경우 트랜잭션이 변경한 데이터가 영구적으로 저장되어야 한다. 모든 르랜잭션이 로그에 남겨진 뒤 COMMIT되어야 한다.

• TCL이란 트랜잭션을 제어하는 명령어이다.

COMMIT

• INSERT, UPDATE, DELETE 후 변경된 내용을 확정, 반영하는 명령어로, COMMIT을 실행해야 최종적으로 데이터 파일에 기록되고 트랜잭션이 완료되는 것이다.

ROLLBACK

• INSERT, UPDATE, DELETE 후 변경된 내용을 취소하는 명령어로 ROLLBACK를 하면 변경하기 이전 값으로 복구된다.

SAVEPOINT

• ROLLBACK을 수행할 때 전체 작업을 되돌리지 않고 일부만 되돌리 수 있게 하는 기능을 가진 명령어로 ROLLBACK 뒤에 특정 SAVEPOINT를 지정해주면 그 지점까지만 데이터가 복구된다.

 

DDL

• DDL이란 DATA Definition Language란 의미로 데이터 정의어, 즉 데이터를 정의하는 명령어이다.
• 데이터 타입
  
  • 문자 : CHAR(크기 고정), VARCHAR(크기 가변), CLOB
  • 숫자 : NUMBER
  • 날짜 : DATE

CREATE

• 테이블을 생성하기 명령어
• DEFAULT : 데이터의 기본값, DEL_YN(삭제여부) 컬럼의 DEFAULT를 ‘N’으로 정의하면 DEL_YN 값을 별도로 명시하지 않았을 때 NULL 대신 ‘N’이 저장된다.

CREATE TABLE 테이블명 (
    컬럼명 1 데이터 타입(DEFAULT / NULL 여부),
);

• 테이블 생성 규칙
  
  • 테이블명은 고유해야 한다.
  • 한 테이블 내에서 컬러명은 고유해야 한다.
  • 컬럼명 뒤에 데이터 유형과 데이터 크기가 명시되어야 한다.
  • 컬럼에 대한 정의는 괄호( ) 안에 기술한다.
  • 각 컬럼들은 ,(콤마)로 구분된다.
  • 테이블명과 컬럼명은 숫자로 시작될 수 없다.
  • 마지막은 ;(세미콜론)으로 끝난다.
• CTAS : CREATE 테이블명 AS SELECT * FROM 복사할 테이블명;
• 기존에 존재하던 테이블을 복사해서 생성하고 싶은 경우 CTAS 문을 활용할 수 있는데, 컬럼별로 데이터 유형을 다시 명시해주지 않아도 된다는 장점이 있다.
• CREATE TABLE 할 때 제약조건(CONSTRAINT)도 함께 정의할 수 있는데, 제약조건은 테이블에 저장될 때 데이터의 무결성, 즉 데이터의 정확성과 일관성을 유지하고 결손과 부정합이 없음을 보증하기 위해 해놓는 장치라고 할 수 있다.
• 제약조건의 종류
  
  • PRIMARY KEY(기본키) : 테이블에 저장된 각 Row에 대한 고유성을 보장하며 NULL 값이 입력될 수 없고 자동으로 UNIQUE 인덱스로 생성된다.
  • UNIQUE KEY(고유키) : PRIMARY KEY와 유사하게 테이블에 저장된 각 Row에 대한 고유성을 보장하지만, NULL 값이 허용된다.
  • NOT NELL : 해당 컬럼에 NULL 값이 입력되는 것을 허용하지 않는다.
  • CHECK : 컬럼에 저장될 수 있는 값의 범위를 제한한다.
  • FOREIGN : 하나의 테이블이 다른 테이블을 참조할 때 정의한다.
• 참조 무결성 규정 관련 옵션
  
  • CASCADE : 부모 값 삭제 시 자식 값 같이 삭제
  • SET NULL : 부모 값 삭제 시 자식의 해당 컬럼 NULL 처리
  • SET DEFAULT : 부모 값 삭제 시 자식의 해당 컬럼 DEFAULT 값으로 변경
  • RESTRICT : 자식 테이블에 해당 테이터가 PK로 존재하지 않는 경우에만 부모 값 삭제 및 수정 가능
  • 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 데이터 유형 [DEFAULT 값][NOT NULL], 컬럼명2 데이터 유형);

• RENAME COLUMN : 기존 컬럼의 이름을 변경할 때 쓰는 명령어

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

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

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

• DROP TABLE : 테이블을 삭제할 때 쓰는 명령어
• CASCADE : 만약 해당 테이블을 참조하고 있는 테이블이 존재하는 경우 삭제되지 않는다.
• CASCADE CONSTRAINT : 참조 제약조건도 함께 삭제한다.

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

• TRUNCATE TABLE : 테이블에 저장되어 있는 데이터를 모두 제거하는 명령어
• DELETE와 유사하지만 저장 공간이 재사용되도록 초기화된다는 차이점이 있고, ROLLBACK이 불가능해 DDL로 분류된다.

TRUNCATE TABLE 테이블명;

 

DCL

• DCL이란 Data Control Language란 의미로 USER를 생성하고, 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 롤명;

• ROLE에 권한 부여

GRANT 권한 TO 롤명;

• ROLE를 사용자에게 부여

GRANT 롤명 TO 사용자명;

 

Reference

• 2024 SD에듀 유선배 SQL개발자(SQLD) 과외노트