자격증

SQLD - 5. 관리구문

Beekei 2025. 5. 30. 16:20
반응형

DML(Data Manipulation Language)

  • 테이블에 레코드를 추가, 변경, 삭제 등 데이터를 조작하는 작업을 수행하는 SQL 명령어
  • DDL과 달리 자동커밋(AUTOCOMMIT)되지 않고 명시적으로 커밋(COMMIT)을 실행
  • 커밋 전에는 롤백(ROLLBACK)이 가능하며 해당 데이터를 조회할 때는 변경 사항이 보이지 않음

INSERT

  • 테이블에 레코드를 추가하는 명령어
  • 테이블의 칼럼 목록을 기입 없이 VALUES만으로 행을 추가할 때는 테이블의 칼럼 수와 VALUES 인수의 개수가 같아야 함
INSERT INTO 테이블 칼럼1, 칼럼2, 칼럼3 ... VALUES(데이터1, 데이터2, 데이터3 ...);
INSERT INTO 테이블 VALUES(데이터1, 데이터2, 데이터3 ...);

UPDATE

  • 기존의 레코드를 수정하는 명령어
  • WHERE절은 생략 가능하지만 전체 데이터를 대상으로 수정됨
UPDATE 테이블 SET 칼럼1 = 데이터1, 컬럼2 = 데이터2 ... WHERE 조건

DELETE

  • 기존의 레코드를 삭제하는 명령어
  • TRUNCATE는 작업취소(UNDO)를 위한 데이터를 생성하지 않아 속도가 빠르고 디스크 공간을 릴리즈(Release)하여 재사용이 가능
  • DELETE는 로그를 남기며 롤백이 가능
DELETE FROM 테이블 WHERE 조건 # WHERE절은 생략 가능하지만 전체 데이터가 삭제됨

MERGE

  • 테이블 단위로 데이터를 갱신하는 명령어
  • 두 테이블을 비교하여 특정 조건에 맞는 레코드에 대해서는 UPDATE문을 그렇지 않은 레코드는 INSERT문 실행
  • 테이블의 변경 사항을 백업 테이블에 반영하거나 개발을 위해 추가, 변경된 건을 운영 환경에 적용할 때 사용 가능
MERGE INTO 테이블1 USING 테이블2 # 테이블2의 변경사항을 테이블1에 반영
ON 조건
WHEN MATCHED THEN # ON절 조건에 만족하면
    UPDATE SET  테이블1.칼럼1 = 테이블2.칼럼1, 테이블1.칼럼2 = 테이블2.칼럼2, 테이블1.칼럼3 = 테이블2.칼럼3
    DELETE WHERE 테이블1.칼럼1 = 값1 # 해당 DELETE절은 위에서 UPDATE절에 의해 갱신된 데이터에만 적용(갱신된 데이터 기준)
WHEN NOT MATCHED THEN # ON절 조건에 만족하지 않으면
    INSERT (테이블1.칼럼1, 테이블1.칼럼2, 테이블1.칼럼3) VALUES (테이블2.칼럼1, 테이블2.칼럼2, 테이블2.칼럼3);

TCL(Transaction Control Language)

  • 트랜잭션을 제어하는 SLQ 명령어
  • INSERT, UPDATE, DELETE 등의 명령들을 하나의 트랜잭션으로 묶어서 처리하거나 취소할 수 있는 명령어를 제공

트랜잭션의 특징

  • 원자성(Atomicity): 하나의 트랜잭션에 묶인 연산들은 모두 실행되든지 전혀 실행되지 않아야 함(All or Nothing)
  • 일관성(Consistency): 트랜잭션의 결과는 DB 정합성을 깨지 않음, 트랜잭션 이전에 오류가 없다면 이후에도 오류가 없음
  • 고립성(Isolation): 트랜잭션은 독립적으로 수행되며 다른 트랜잭션이 중간에 간섭하거나 영향을 미치지 않음
  • 영속성(Durability): 트랜잭션의 결과는 DB에 영구적으로 저장되고 유지됨

COMMIT

  • INSERT, UPDATE, DELETE와 같은 DML 명령들을 통한 변경사항을 DB에 영구적으로 반영하고 락을 해제하여 트랜잭션을 완료
BEGIN TRANSACTION;
INSERT ~
UPDATE ~
DELETE ~
COMMIT;

ROLLBACK

  • 이전의 커밋 이후 새로운 트랜잭션에 포함되는 전체 변경사항 또는 지정된 저장점(SAVEPOINT) 이후의 변경사항을 취소하고 복원(커밋 후의 변경사항은 롤백 불가)
  • DDL 명령어는 자동커밋이 되므로 롤백이 불가하지만 INSERT, UPDATE, DELETE와 같은 DML 명령어는 자동커밋이 되지 않아 롤백이 가능(Oracle 기준)
BEGIN TRANSACTION;
INSERT ~
UPDATE ~
DELETE ~
ROLLBACK;

SAVEPOINT

  • 롤백을 위한 저장점을 지정
  • 트랜잭션에 포함되는 전체 변경사항이 취소되는 것이 아닌 저장점 이후에 해당하는 변경사항만 취소
# Oracle
SAVEPOINT 이름;
ROLLBACK TO 이름;

#SQL Server
SAVE TRANSACTION 이름;
ROLLBACK TRANSACTION 이름;

DDL(Data Definition Language)

  • 스키마를 정의하고 관리하는 SQL 명령어
  • ERD로 그려진 데이터 모델링은 논리적 모델링, DDL을 사용해서 DBMS에 적용하는 것은 물리적 모델링
  • DDL에서 저장해야 할 요소
    • 테이블 이름: 테이블의 이름을 지정
    • 칼럼 이름: 테이블을 구성하는 칼럼 이름 지정
    • 칼럼 데이터 타입: 각 컬럼의 데이터 타입 지정
    • 칼럼 데이터 크기: 각 컬럼의 데이터 크기 지정
    • 제약조건(Constraints): PK, NOT NULL 등 칼럼이 가지는 제약조건 지정

CREATE

  • 테이블을 생성하는 명령어
  • 테이블의 이름은 DB 내에서 고유, 칼럼 이름은 해당 테이블 내에서 고유
  • 테이블의 이름은 담는 데이터의 성격에 맞는 이름으로, 칼럼의 이름은 통일성을 생각하여 지정
  • 테이블, 칼럼, 제약조건의 이름은 숫자로 시작될 수 없고 A-Z, a-z, 0-9, _, $, # 문자만 사용 가능
CREATE TABLE 테이블1 (
    칼럼1 데이터타입(데이터크기) DEFAULT 기본값 NULL / NOT NULL, 
    칼럼2 데이터타입(데이터크기) DEFAULT 기본값 NULL / NOT NULL,
    칼럼3 데이터타입(데이터크기), # DEFAULT, NULL / NOT NULL 생략 가능 (기본값은 NULL 값으로 설정)
    ... ,
    CONSTRAINT 이름1 PRIMARY KEY (칼럼1), # 제약조건 지정
    CONSTRAINT 이름2 FOREIGN KEY (칼럼2) REFERENCES 테이블2(칼럼4),
)

PRIMARY KEY

  • PK 칼럼을 지정
  • 모든 행의 값이 고유(Unique)해야하고 NULL 값을 가질 수 없음
  • 자동으로 UNIQUE INDEX 생성

FOREIGN KEY

  • FK(외래키) 칼럼을 지정
  • 다른 테이블의 PK로부터 가져온 칼럼을 지정
  • 논리적 모델링에서 정의한 관계(Relationship)를 표현
  • 참조 대상인 칼럼에 대해서 참조 무결성 제약조건을 지정 가능(일관성이 깨지는 것을 DBMS 차원에서 방지)

DELETE/MODIFY ACTION 

제약조건 설명
CASCADE 부모의 값 삭제 시 자식의 값도 삭제
SET NULL 부모의 값 삭제 시 자식의 값은 NULL
SET DEFAULT 부모의 값 삭제 시 자식의 해당 컬럼은 기본값
RESTRICT 자식 테이블에 해당 데이터가 PK로 지정되지 않은 경우에만 부모 테이블에서 삭제 및 수정 가능
NO ACTION 제약조건을 위배한 경우 동작이 실패하며 아무런 액션되 취하지 않음

 

INSERT ACTION

제약조건 설명
AUTOMATIC 부모 테이블에 PK가 없는 경우 PK 생성 후 자식 테이블에 값 입력
SET NULL 부모 테이블에 PK가 없는 경우 자식 테이블에 NULL값 입력
SET DEFAULT 부모 테이블에 PK가 없는 경우 자식 테이블에 기본값 입력
DEPENDENT 부모 테이블에 PK가 존재할 때만 자식 테이블에 값 입력을 허용
NO ACTION 제약조건을 위배한 경우 동작이 실패하며 아무런 액션되 취하지 않음

UNIQUE KEY

  • 모든 값이 고유해야 하는 조건 지정
  • PK와 달리 NULL값 허용

NOT NULL

  • NULL 값을 가질 수 없도록 지정
  • NULL이 입력되는 경우 오류 발생

CHECK

  • 가질 수 있는 값을 특정 범위로 제한
CONSTRAINT CHK_GENDER CHECK(GENDER IN 'M', 'F')) # 성별값 제한

ALTER

  • 생성되어 있는 테이블의 스키마를 변경하는 명령어

칼럼 추가

  • 기존 칼럼들의 끝에 추가됨
ALTER TABLE 테이블 ADD 칼럼 데이터타입(데이터크기) DEFAULT 기본값 NULL / NOT NULL;

칼럼 삭제

  • ALTER로 삭제한 칼럼은 복구 불가
ALTER TABLE 테이블 DROP COLUMN 칼럼;

칼럼 변경

  • 데이터 타입 변경 시 칼럼에 저장된 테이터가 없을 경우에만 수정 가능
  • 데이터 크기 감소 시 기존 데이터를 모두 담을 수 있는 경우에만 수정 가능
  • DEFAULT 설정 시 변경 이후에 추가되는 레코드에만 적용
  • NOT NULL 설정 시 현재 칼럼의 값 중에 NULL이 없는 경우에만 수정 가능
ALTER TABLE 테이블 MODIFY(
    칼럼1 데이터타입(데이터크기) DEFAULT 기본값 NULL / NOT NULL,
    칼럼2 데이터타입(데이터크기) DEFAULT 기본값 NULL / NOT NULL,
    ...
)

# SQL Server은 아래 명령어 형식을 따르며 여러 칼럼을 동시에 수정할 수 없음
ALTER TABLE 테이블 ALTER COLUMN 칼럼1 데이터타입(데이터크기) DEFAULT 기본값 NULL / NOT NULL;
ALTER TABLE 테이블 ALTER COLUMN 칼럼2 데이터타입(데이터크기) DEFAULT 기본값 NULL / NOT NULL;
...

칼럼 이름 변경

ALTER TABLE 테이블 RENAME COLUMN 칼럼1 TO 칼럼2;

제약조건 추가

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

테이블 삭제

  • 다른 테이블에서 삭제할 테이블을 참조하고 있는 경우 CASCADE CONSTRAINTS를 명시하지 않으면 오류가 발생해 삭제 불가
DROP TABLE 테이블 CASCADE CONSTRAINTS;

테이블 초기화

  • 테이블의 데이터만 초기화
  • DELETE와 달리 롤백이 불가능하고 저장 공간이 릴리즈(Release)됨
TRUNCATE TABLE 테이블;

DROP, TRUNCATE, DELETE의 차이

  DROP TRUNCATE DELETE
문법 DROP TABLE 테이블 TRUNCATE TABLE 테이블 DELETE FROM 테이블
SQL 종류 DDL DDL DML
동작 스키마까지 삭제 테이블 생성 초기 상태로 초기화
(데이터만 삭제)		 데이터만 삭제
자동커밋 여부 ⭕ ⭕ ❌
디스크 공간 릴리즈 ⭕ ⭕ ❌
작업취소(UNDO)를 위한
로그 데이터 생성 여부		 ❌ ❌ ⭕

DCL(Data Control Language)

  • 사용자를 생성하고 권한을 부여하는 작업을 수행하는 SQL 명령어

USER 관련 명령어

  • DB 생성 후 데이터를 읽고 쓰기 위해 계정(USER)를 생성하고 변경, 삭제하는 명령어

USER 생성

  • 같은 이름으로 생성된 계정이 존재할 경우 오류 발생
CREATE USER 사용자 IDENTIFIED BY 패스워드;

USER 변경

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

USER 삭제

DROP USER 사용자;

권한 관련 명령어

  • DB에 대한 접근(읽기, 쓰기) 권한을 부여하는 명령어

GRANT

  • 권한을 부여하는 명령어
  • 특정 테이블에 대해서만 권한을 부여하려면 ON 옵션 지정
  • WITH GRANT OPTION 지정 시 권한을 받은 사용자가 다른 사용자에게 자신의 권한 부여 가능
GRANT 권한 ON 테이블 TO 사용자 WITH GRANT OPTION;

GRANT CREATE SESSION TO 사용자; # 데이터베이스 접속 권한 부여
GRANT ALTER SESSION TO 사용자; # 데이터베이스 접속 상태에서 환경 값 변경 권한 부여
GRANT CREATE USER TO 사용자; # 사용자 생성 권한 부여
GRANT ALTER USER TO 사용자; # 사용자 정보 변경 권한 부여
GRANT DROP USER TO 사용자; # 사용자 삭제 권한 부여
GRANT CREATE TABLE TO 사용자; # 테이블 생성 권한 부여
GRANT CREATE ANY TABLE TO 사용자; # 임의의 스키마 소유 테이블 생성 권한 부여

REVOKE

  • 권한을 회수하는 명령어
  • 특정 테이블에 대해서만 권한을 회수하려면 ON 옵션 지정
  • RESTRICT: 해당 권한을 회수할 때 의존적인 다른 권한이 존재하는 경우 명령이 수행되지 않음
  • CASCADE: 해당 권한을 회수할 때 의존적인 다른 권한까지 함께 회수
REVOKE 권한 ON 테이블 FROM 사용자 RESTRICT / CASCADE;

REVOKE CREATE SESSION FROM 사용자; # 데이터베이스 접속 권한 회수
REVOKE ALTER SESSION FROM 사용자; # 데이터베이스 접속 상태에서 환경 값 변경 권한 회수
REVOKE CREATE USER FROM 사용자; # 사용자 생성 권한 회수
REVOKE ALTER USER FROM 사용자; # 사용자 정보 변경 권한 회수
REVOKE DROP USER FROM 사용자; # 사용자 삭제 권한 회수
REVOKE CREATE TABLE FROM 사용자; # 테이블 생성 권한 회수
REVOKE CREATE ANY TABLE FROM 사용자; # 임의의 스키마 소유 테이블 생성 권한 회수

ROLE 관련 명령어

  • 많은 권한들을 각각 따로 부여하지 않고, 몇 가지의 권한을 묶어서 패키지 형태(ROLE)로 부여하는 명령어
CREATE ROLE ROLE_MANAGER; # ROLE_MANAGER이라는 이름의 ROLE 생성
GRANT CREATE SESSION, CREATE TABLE, CREATE USER TO ROLE_MANAGER; # 생성된 ROLE_MANAGER에 여러가지 권한 부여
GRANT ROLE_MANAGER TO 사용자; # ROLE_MANAGER을 사용자에게 부여
반응형
저작자표시 (새창열림)