[SQLD] 2과목 SQL 기본 및 활용 - 관리구문

🚀 DML(Data Manipulation Language)

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

 

1️⃣ INSERT

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

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

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

 

 

2️⃣ UPDATE

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

- 수정하고 싶은 칼럼이 많다면 SET절에 ,(콤마)로 이어서 명시

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

 

UPDATE 입사 SET 구분 = '경력' WHERE 입사자사번 = '250317';

 

3️⃣ DELETE

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

- WHERE 절이 없으면 테이블의 모든 Row가 삭제되니 주의

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

DELETE FROM 입사 WHERE 입사자사번 = '250317';

📌 TRUNCATE

- DELETE는 COMMIT 전에 ROLLBACK이 가능

- TRUNCATE는 별도의 로그를 남기지 않아 ROLLBACK 불가능, 시스템 부하 측면에서 유리

 

 

4️⃣ MERGE

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

- 특정 조건에 맞는 레코드는 UPDATE로 갱신, 그렇지 않은 레코드는 INSERT를 사용해 신규 추가

MERGE INTO 입사 target
USING (SELECT '250317' AS 입사자사번, '개발' AS 부서, '경력' AS 구분 FROM DUAL) source
ON (target.입사자사번 = source.입사자사번)
WHEN MATCHED THEN
    UPDATE SET target.구분 = source.구분
WHEN NOT MATCHED THEN
    INSERT (입사자사번, 부서, 구분) VALUES (source.입사자사번, source.부서, source.구분);

 

 

---

🚀 TCL(Transaction Control Language)

- 트랜잭션이란 쪼개질 수 없는 업무처리의 단위

- 예) 결제 트랜잭션: 결제 + 제고 차감

 

✅ 트랜잭션의 특징

특징	설명
원자성(Atomicity)	하나의 트랜잭션으로 묶인 연산들은 All or Nothing, 모두 실행되든지 아니면 전혀 실행되지 않아야 한다.
일관성(Consistency)	트랙잭션이 완료된 후에도 데이터베이스가 가진 데이터에 일관성이 있어야 한다.
고립성(Isolation)	트랜잭션은 독립적으로 수행되며 다른 트랜잭션이 실행 중간에 간섭하거나 영향을 미치지 않는다.
지속성(Durability)	트랜잭션의 결과는 데이터베이스에 영구적으로 저장되어 유지된다.

 

1️⃣ COMMIT

 

- INSERT, UPDATE, DELETE와 같은 DML 명령어 후 변경된 내용을 데이터베이스에 영구적으로 반영

 

2️⃣ ROLLBACK

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

 

3️⃣ SAVEPOINT

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

 

 

 

---

🚀 DDL(Data Definition Language)

- DDL은 데이터를 정의하는 명령어로 CREATE, ALTER, DROP, RENAME, TRUNCATE가 있다.

 

✅ DDL을 사용한 테이블 생성 요소

요소	테이블의 이름을 지정한다.
테이블 이름	테이블의 이름을 지정한다.
컬럼 이름	테이블을 구성하는 컬럼들의 이름을 지정한다.
컬럼 데이터 타입	각 칼럼의 데이터 타입을 지정한다.
컬럼 데이터 크기	각 칼럼의 데이터 크기를 지정한다.
제약조건(Constraints)	PK, NOT NULL 등 칼럼이 가지는 제약조건을 지정한다.

 

✅ 데이터 타입

유형	데이터 타입
문자	CHAR VARCHAR CLOB
숫자	NUMBER
날짜	DATE

 

1️⃣ CREATE

 

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

CREATE 테이블1 (
	칼럼1 데이터타입[DEFAULT 기본값1] [NULL / NOT NULL]
    ...
    [, CONSTRAINT 이름1 PRIMARY KEY (칼럼1)]
    [, CONSTRAINT 이름2 FOREIGN KEY (칼럼2) REFERENCES 테이블2(칼럼3)]
);

🚨 테이블 생성 시 유의사항

- 테이블명은 데이터베이스 내에서 고유해야한다.

- 칼럼 이름은 테이블 내에서 고유해야한다.

- 칼럼에 대해 이름, 데이터 타입은 필수로 지정되어야 한다. (데이터 크기는 데이터 타입에 따라 지정 여부 다름)

- DEFAULT, NULL, NOT NULL 등은 선택적으로 지정 가능하다. (아무것도 지정하지 않은 경우 NULL이 기본값)

- 테이블 이름, 칼럼 이름, 제약조건 이름 등은 숫자로 시작될 수 없고, A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

- 칼럼의 정의는 괄호 안에 기술한다.

- 각 칼럼은 콤마(,)로 구분한다.

- 다른 SQL문과 마찬가지로 끝은 세미콜론(;)으로 끝난다.

- 테이블 이름을 지정할 때, 의미 없이 짓지 않고 테이블이 담는 데이터의 성격에 맞는 이름을 사용한다.

- 칼럼의 이름은 통일성을 살려서 지어야 한다. 예) 회원번호 칼럼 MEMBER는 ID, QUESST는 NO와 같이 하지 않음.

 

❶ PRIMARY KEY

- PK인 칼럼을 지정한다.

- PK로 지정된 칼럼은 NULL값을 가질 수 없고, 모든 값이 고유(Unique)해야 한다. 자동으로 UNIQUE INDEX.

 

❷ FOREIGN KEY

- FK 또는 왜래키, 다른 테이블의 PK로부터 가져온 칼럼을 지정

- FK 지정 시 참조 대상인 칼럼에 대해서 참조 무결성 제약조건을 지정할 수 있다.

✅ 참조 무결성 제약조건

- 데이터의 입력, 수정, 삭제 과정에서 데이터의 일관성이 깨지는 것을 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와 마찬가지로 모든 값이 고유해야하는 조건을 지정한다. 단, PK와 달리 NULL값을 가질 수 있다.

 

❹ NOT NULL

- NULL값을 가질 수 없다.

 

❺ CHECK

- 가질 수 있는 값을 특정 범위로 제한한다.

- 예) 'Y', 'N' 처럼 두 가지 값만 가지도록 제한

 

2️⃣ ALTER

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

 

❶ 칼럼 추가: ADD

- 테이블1이란 기존 테이블에 칼럼1이란 새로운 칼럼을 추가한다. 기존 칼럼들의 끝에 추가된다.

ALTER TABLE 테이블1 ADD 칼럼1 데이터타입1[(데이터크기1)];

 

❷ 칼럼 삭제: DROP COLUMN

- 테이블1에서 칼럼1을 삭제한다. ALTER로 삭제한 칼럼은 복구가 불가능하므로 주의해야 한다.

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

 

❸ 칼럼 변경: MODIFY

- 데이터 타입 변경: 칼럼에 저장되 데이터가 하나도 없는 경우만

- 데이터 크기 변경: 줄이는 경우 현재 데이터를 줄어든 데이터 크기에 모두 담을 수 있을 때

- DEFAULT를 지정할 경우, 변경 이후에 추가되는 레코드에 대해서만 기본값 적용

- NOT NULL로 변경하려면 현재 칼럼의 값 중에 NULL값이 없어야 함

ALTER TABLE 테이블1 MODIFY(
	칼럼1 데이터타입1[(데이터크기1)] [DEFAULT 기본값1] [NULL / NOT NULL]
);

 

❹ 칼럼 이름 변경: RENAME COLUMN

- 테이블1의 칼럼1을 칼럼2로 이름을 변경한다.

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

 

❺ 제약조건 추가: ADD CONSTRAINT

- 테이블1의 칼럼1에 대해 이름1이란 이름의 제약조건을 추가한다.

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

 

❻ 테이블 삭제: DROP TABLE

- 다른 테이블에서 테이블1을 참조하고 있는 경우 CASCADE CONSTRAINTS를 명시하지 않으면 삭제가 불가능하며 오류가 발생한다.

DROP TABLE 테이블1 [CASCADE CONSTRAINTS];

 

❼ 테이블 초기화

- 테이블의 데이터만 삭제하고 스키마는 그대로 둔다.

- DELETE와 달리 롤백이 불가능하고, 저장 공간이 릴리즈(Release)된다.

TRUNCATE TABLE 테이블1;

 

📌 DROP, TRUNCATE, DELETE 차이 

	DROP	TRUNCATE	DELETE
문법	DROP TABLE	TRUNCATE TABLE	DELETE FROM
SQL 종류	DDL	DDL	DML
동작	테이블을 스키마까지 완전하게 삭제	테이블을 생성 초기 상태로 초기화 (데이터 삭제, 스키마 남김)	스키마는 담겨두고 데이터만 삭제 (WHERE로 특정 행만 삭제 가능)
자동커밋 여부	O	O	X
디스크 공간 릴리즈	O	O	X
작업취소(UNDO)를 위한 로그 데이터 생성 여부	X	X	O

 

---

🚀 DCL(Data Control Language)

- 데이터 제어 언어로 사용자를 생성하고 권한을 부여하는 명령어

 

1️⃣ USER 관련 명령어

 

- 계정을 생성하고 변경하며 삭제하는 명령어

❶ USER 생성

- 사용자1이란 이름의 USER를 새로 생성하여 패스워드1으로 비밀번호를 지정한다.

- 사용자1이란 USER가 이미 있을 경우 오류가 발생한다. 이 경우 ALTER USER를 사용해야 한다.

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

 

❷ USER 변경

- 이미 생성되어 있는 사용자1의 비밀번호를 패스워드1로 변경한다.

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

 

❸ USER 삭제

- 사용자1이란 USER를 삭제한다.

DROP USER 사용자;

 

2️⃣ 권한 관련 명령어

- 계정에 대해서 특정 데이터베이스에 대한 읽기, 쓰기 등의 접근 권한을 부여하는 명령어

 

✅ 권한의 종류

권한	설명
CREATE USER	사용자를 생성할 수 있는 권한
ALTER USER	생성된 사용자의 정보를 변경할 수 있는 권한
DROP USER	사용자를 삭제할 수 있는 권한
CREATE SESSION	데이터베이스 접속 권한
ALTER SESSION	데이터베이스 접속 상태에서 환경 값을 변경할 수 있는 권한
CREATE TABLE	자신 소유의 테이블을 생성할 수 있는 권한
CREATE ANY TABLE	임의의 스키마 소유 테이블을 생성할 수 있는 권한

 

❶ GRANT

- 사용자1에게 권한1을 부여한다.

- 특정 테이블에 대해서만 권한을 부여하려면 ON 옵션을 추가한다.

- WITH GRANT OPTION을 추가하면 권한을 받는 사용자가 다른 사용자에게 자신이 부여받은 권한을 줄 수 있다.

- 여기서 지정할 수 있는 권한은 DBMS에 따라 여러 가지가 있다.

- 대표적으로 테이블을 생성할 수 있는 권한을 부여하는 예시: GRANT CREATE TABLE TO 사용자1;

GRANT 권한1 [ON 테이블1] TO 사용자1 [WITH GRANT OPTION];

 

❷ REVOKE

- 사용자1에게 부여한 권한1을 회수한다.

- 특정 테이블에  대해서만 권한을 회수하려면 ON 옵션을 추가한다.

- RESTRICT는 해당 권한을 회수할 때 다른 권한도 같이 회수되어야 하는 경우, 즉 다른 권한에 의존적인 경우에 본 권한회수 명령이 수행되지 않는다.

- CASCADE는 해당 권한을 회수할 때 의존적인 다른 권한까지 함께 회수한다.

- 테이블 생성 권한 회수 예시: REVOKE CREATE TABLE FROM 사용자1;

REVOKE 권한1 [ON 테이블1] FROM 사용자1;

 

3️⃣ ROLE 관련 명령어

- 권한들을 세트로 묶어 역할 부여

- 똑같이 REVOKE, ALTER 쓸 수 있음

 

❶ ROLE 생성

- ROLE_MGR이라는 이름의 ROLE을 생성한다.

CREATE ROLE ROLE_MGR;

 

❷ ROLE에 권한 부여

- 생성된 ROLE_MGR에 세션 연결(CREATE SESSION), 테이블 생성(CREATE TABLE), 사용자 생성(CREATE USER) 권한을 부여한다.

GRANT CREATE SESSION, CREATE TABLE, CREATE USEER TO ROLE_MGR;

 

❸ ROLE을 사용자에게 부여

- ROLE_MGR 역할을 USER1이라는 사용자에게 부여한다.

GRANT ROLE_MGR TO USER1;