[SQLD][아답터] 2과목 - 3. 관리 구문

[관리 구문]

1-1.  DML
1-1-1. INSERT
1-1-2. UPDATE
1-1-3. DELETE
1-1-4. MERGE

2-1. TCL
2-1-1. TCL
2-1-2. 트랜잭션 격리 수준
2-1-3. SAVEPOINT
2-1-4. 자동커밋(AUTOCOMMIT)

3-1. DDL
3-1-1. DDL
3-1-2. CREATE
3-1-3. ALTER

4-1. DCL
4-1-1. DCL
4-1-2. GRANT
4-1-3. REVOKE
4-1-4. 권한 부여 옵션

[관리 구문]

1-1. DML

데이터 조작 언어 (INSERT, UPDATE, DELETE, MERGE 문)

1️⃣ INSERT

새로운 데이터(행)을 테이블에 추가

 

🔹 전체 컬럼 값 추가

🔹 명시된 컬럼 값 추가

2️⃣ UPDATE

🔹 기존 데이터를 수정

※ WHERE 절이 없는 경우, 모든 튜플(행)에 대하여 적용되므로 주의 필요

3️⃣ DELETE

🔹 데이터를 삭제

※ DDL의 TRUNCATE도 테이블의 데이터를 삭제하나, TRUNCATE는 작업 취소가 불가능함

4️⃣ MREGE

🔹 두 테이블을 조건에 따라 데이터를 삽입(INSERT) 하거나 수정(UPDATE)

2-1. TCL

1️⃣ TCL

🔹트랜잭션 : 데이터베이스에서 가장 작은 작업 단위

🔹 트랜잭션 제어문

🔹 트랜잭션 특징

- 원자성(Atomicity)

- 일관성(Consistency)

- 고립성(Isolation)

- 연속성(Durability)

명령어	설명
COMMIT	변경사항을 데이터베이스에 영구적으로 반영
ROLLBACK	COMMIT 전 모든 변경사항을 되돌림
SAVEPOINT	롤백으로 되돌릴 특정 지점을 지정

2️⃣ 트랜잭션 격리 수준

격리수준	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	발생	발생	발생
Read Committed	발생하지 않음	발생	발생
Repeatable Read	발생하지 않음	발생하지 않음	발생
Serializable	발생하지 않음	발생하지 않음	발생하지 않음

 

※ 격리수준이 높아질수록 일관성은 증가하지만, 성능이 감소한다.

3️⃣ SAVEPOINT

INSERT INTO 계좌 VALUES ('홍길동', 5000);
INSERT INTO 계좌 VALUES ('임꺽정', 10000);

UPDATE 계좌 SET 가격 = 가격 - 1000 WHERE 이름 = '홍길동'
SAVEPOINT SV1;
UPDATE 계좌 SET 가격 = 가격 - 3000 WHERE 이름 = '임꺽정'
SAVEPOINT SV2;
UPDATE 계좌 SET 가격 = 가격 - 2000 WHERE 이름 = '홍길동'
SAVEPOINT SV3;

ROLLBACK TO SV2;

 

※  SV2 지점으로 롤백하여 최종 상태 ➜ (홍길동 = 4000, 임꺽정 = 7000)

4️⃣ 자동커밋(AUTOCOMMIT)

🔹 Oracle

- DDL : 무조건 자동커밋이 수행됨

- DML : 자동커밋 모드 ON / OFF 에 따라 롤백이 가능

※  오라클은 기본적으로 자동커밋 기능이 OFF 

🔹 SQL Server

- 자동커밋 모드 ON / OFF 에 따라서 DDL과 DML이 동작

※  SQL Server는 기본적으로 자동커밋 기능이 ON

3-1. DDL

1️⃣ DDL

🔹 데이터 구조(스키마)를 정의

2️⃣ CREATE

🔹 새로운 테이블의 생성

CREATE TABLE 학생(
    학번 INT PRIMARY KEY,
    이름 VARCHAR(10) NOT NULL,
    입학일 DATE,
    주소 VARCHAR(100) UNIQUE,
    학과 VARCHAR(10) NOT NULL,
    성별 VARCHAR(1) NOT NULL CHECK (성별 IN ('M', 'F'))
    FOREIGN KEY (학과) REFERENCES 학과리스트(학과)
)

 

➜ 학번 INT PRIMARY KEY : 학번을 PK로 지정

➜ FOREIGN KEY(학과) : 학과를 FK로 지정

➜ INT, VARCHAR, DATE : 각 컬럼의 값에 해당하는 데이터 타입 지정

➜ NOT NULL : NULL 값을 가지지 못하게 설정

➜ UNIQUE : 중복 값이 없도록 조건 설정, PK와 달리 NULL 값 허용

➜ CHECK : 가질 수 있는 값을 제한

※  PK는 개체무결성을 보장하고 FK는 참조무결성을 보장함

 

유형	설명
CHAR(n)	고정 길이 문자열, n글자 미만이면 공백
VARCHAR(n)	가변 길이 문자열, 최대 n글자까지 저장
NUMBER	숫자 데이터 저장 (INT, FLOAT 등)
DATE	날짜와 시간

※  VARCHAR는 저장공간 효율화 측면, CHAR는 탐색 측면에서 유리

3️⃣ ALTER

🔹 테이블의 구조 변경

ALTER TABLE 학생 ADD 학년 INT

➜ INT 자료형의 학년 컬럼 추가

 

ALTER TABLE 학생 MODIFY 이름 VARCHAR(30)

➜ 이름의 가변 길이 문자열 크기를 최대 30자까지 가능하도록 지정

 

ALTER TABLE 학생 DROP COLUMN 주소

➜ 주소 컬럼을 삭제

 

ALTER TABLE 학생 RENAME COLUMN 학번 TO ID

➜ 학번 컬럼을 ID로 변경

4️⃣ DROP

🔹 테이블의 삭제

DROP TABLE 학생

➜ 학생 테이블 삭제 (다른 테이블에서 학생 테이블을 참조하는 경우 삭제 불가)

 

DROP TABLE 학생 CASCADE CONSTRAINTS

➜ 학생 테이블을 삭제하면서 테이블을 참조하는 모든 FK 제약조건도 함께 삭제

5️⃣ TRUNCATE

🔹 테이블 안의 모든 데이터를 삭제하지만, 테이블 구조는 유지(스키마는 유지)

TRUNCATE TABLE 학생

➜ 학생 테이블의 모든 데이터 삭제

※  TRUNCATE는 DELETE와 달리 기억장치에서 바로 데이터가 지워지므로 ROLLBACK 불가

4-1. DCL

1️⃣ DCL

🔹 사용자의 권한을 관리

2️⃣ GRANT

🔹 사용자에게 권한을 부여

GRANT SELECT, INSERT ON 학생 TO user1

➜ user1 에게 학생 테이블에 대하여 SELECT, INSERT 권한 부여

 

※  권한 내역

- SELECT : 조회

- INSERT : 삽입

- UPDATE : 수정

- DELETE : 삭제

- ALL PRIVILEGES : 모든 권한

3️⃣ REVOKE

🔹 사용자의 권한을 회수

REVOKE INSERT ON 학생 FROM user1

➜ user1에게 부여했던 INSERT 권한을 회수

4️⃣ 권한 부여 옵션

🔹 WITH GRANT OPTION

- 객체 권한 부여 (SELECT, INSERT, UPDATE, DELETE 등)

GRANT SELECT ON 학생 TO user1 GRANT OPTION

➜ user1은 SELECT 권한을 다른 사용자에게도 부여 가능

➜ user1이 user2에게 권한을 준 경우, user1 권한이 회수되면 user2도 권한 회수

 

🔹 WITH ADMIN OPTION

- 시스템 권한 부여 (CREATE TABLE, DROP TABLE 등)

 

GRANT data TO user1 WITH ADMIN OPTION

➜ user1이 DBA(데이터베이스관리자) 권한을 다른 사용자에게도 부여나 회수 가능

➜ user1이 user2에게 권한을 준 경우, user1 권한이 회수되어도 user2 권한은 유지됨

---

🔗 출처

[SQLD 완벽 요약강의]