2024.04.26 47일차 DB RDBMS, DDL

RDBMS 관계형 데이터베이스 관리 시스템

DDL (Data Definition Language): 데이터베이스 스키마를 생성, 변경, 삭제하기 위한 명령어
DML (Data Manipulation Language): 데이터를 검색, 삽입, 수정, 삭제하기 위한 명령어
DCL (Data Control Language): 데이터베이스 사용자의 권한을 관리하기 위한 명령어
TCL (Transaction Control Language): 데이터베이스 트랜잭션을 관리하기 위한 명령어

종류	설명	대표 명령어
DDL	데이터 정의 언어	CREATE, ALTER, DROP, RENAME, TRUNCATE
DML	데이터 조작 언어	SELECT, INSERT, UPDATE, DELETE
DCL	데이터 제어 언어	GRANT, REVOKE
TCL	트랜잭션 제어 언어	COMMIT, ROLLBACK, SAVEPOINT

SQL 관례 - 테이블 이름 생성 규칙

스네이크 표기법 (대소문자 구분 없음)

유효한 문자 사용
테이블 이름은 알파벳, 숫자, 밑줄(_). 특수 문자나 공백은 사용X
첫 글자
테이블 이름은 알파벳 문자로 시작
길이 제한
테이블 이름은 일반적으로 30자 이내의 길이로 제한
의미 있는 이름 사용
테이블 이름은 데이터를 담고 있는 내용을 명확하게 설명, 전체 단어를 사용
단수/복수형 일관성
테이블 이름에 단수형 또는 복수형을 사용할 때 일관성을 유지. (팀 내에서 사용하는 규칙 우선)
예약어 피하기
데이터베이스 시스템에서 사용하는 예약어를 테이블 이름으로 사용하지 X

제약조건

제약조건	설명	예시
PRIMARY KEY	테이블의 각 행을 고유하게 식별할 수 있는 키 기본적으로 NOT NULL + UNIQUE 한 테이블에 1개만 설정하도록 권장	PRIMARY KEY (id)
UNIQUE	열의 모든 값이 고유한지 확인 (중복 불가)	UNIQUE (email)
FOREIGN KEY	다른 테이블의 기본 키에 대한 참조를 설정하여 관계를 정의	FOREIGN KEY (department_id) REFERENCES departments(department_id)
CHECK	열의 값이 특정 조건을 만족하는지 확인	CHECK (salary > 0)
NOT NULL	열의 값이 NULL이 아닌지 확인 (필수입력사항) 없으면 선택사항(NULL 명시 또는 생략)	NOT NULL

** PK
변경이 잦은 데이터는 PK로 사용 X (ex. 전화번호)

너무 민감한 개인정보는 PK로 두지 않음 (ex.주민번호)

** PK 와 UNIQUE 비교

UNIQUE는 중복을 허용하지 않는다는 점에서 PK와 비슷하지만 아래와 같은 차이점이 있음

- NULL값을 허용
- 테이블에서 여러 개 생성 가능

DDL 데이터 정의어

CREATE, ALTER, DROP, TRUNCATE

CREATE TABLE문

// 테이블 생성
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(25) NOT NULL UNIQUE,
    phone_number VARCHAR2(15),
    hire_date DATE NOT NULL,
    job_id VARCHAR2(10) NOT NULL,
    salary NUMBER(8, 2),
    commission_pct NUMBER(2, 2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
);

ON DELETE 옵션

On Delete 옵션은 RDBMS에서 외래 키(Foreign Key) 제약 조건에서 사용
참조된 테이블의 레코드가 삭제될 때 어떻게 처리할지를 지정하는 옵션
On Delete 옵션의 종류 : cascading, restrict, set null, set default, no action

1. Cascading

참조된 레코드가 삭제될 때 해당 레코드를 참조하는 다른 레코드도 함께 삭제합니다.

CREATE TABLE Orders (
   order_id int PRIMARY KEY,
   customer_id int REFERENCES Customers(customer_id) ON DELETE CASCADE,
   order_date date,
   total_amount decimal(10,2)
);

customer_id 컬럼은 Customers 테이블의 customer_id 컬럼을 참조하는 외래 키(Foreign Key)
ON DELETE CASCADE 이므로 Customers 테이블의 레코드가 삭제될 때
해당 customer_id를 참조하는 Orders 테이블의 레코드도 함께 삭제됨

2. Restrict

참조된 레코드가 삭제될 때 해당 레코드를 참조하는 다른 레코드를 삭제하지 않습니다.

customer_id int REFERENCES Customers(customer_id) ON DELETE RESTRICT,

-- Customers 테이블의 레코드가 삭제될 때 해당 customer_id를 참조하는
Orders 테이블의 레코드를 삭제하지 않음

3. Set Null

참조된 레코드가 삭제될 때 해당 레코드를 참조하는 다른 레코드의 컬럼 값을 Null로 설정합니다.

-- Customers 테이블의 레코드가 삭제될 때 해당 customer_id를 참조하는
Orders 테이블의 레코드의 customer_id 컬럼 값을 Null로 설정

4. Set Default

참조된 레코드가 삭제될 때 해당 레코드를 참조하는 다른 레코드의 컬럼 값을 기본값으로 설정합니다.

-- Customers 테이블의 레코드가 삭제될 때 해당 customer_id를 참조하는
Orders 테이블의 레코드의 customer_id 컬럼 값을 기본값으로 설정

5. No Action

참조된 레코드가 삭제될 때 해당 레코드를 참조하는 다른 레코드를 삭제하지 않습니다.

ALTER

기존의 데이터베이스 객체를 수정하는데 사용됨
테이블, 인덱스, 시퀀스, 뷰 등의 데이터베이스 객체를 변경할 수 있음

(** 뷰 정의 변경하기 ---?????)

ALTER TABLE : 기존 테이블 수정

ALTER TABLE 테이블명 ADD (컬럼명 데이터타입 제약조건)
ALTER TABLE 테이블명 DROP COLUMN 컬럼명
ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 제약조건)
ALTER TABLE 테이블명 ADD CONSTRAINT 컬럼명 제약조건
ALTER TABLE 테이블명 DROP CONSTRAINT 컬럼명

ALTER INDEX
기존 인덱스를 수정하는 데 사용. 인덱스의 상태를 변경하거나 재구성

-- 인덱스 비활성화
ALTER INDEX emp_email_index UNUSABLE;

-- 인덱스 활성화
ALTER INDEX emp_email_index REBUILD;

ALTER SEQUENCE
기존 시퀀스를 수정하는 데 사용. 시작값, 증가값, 최소값, 최대값 등 시퀀스의 속성을 변경

ALTER SEQUENCE employee_seq INCREMENT BY 10;

ALTER VIEW
기존 뷰를 수정하는 데 사용. 뷰의 정의를 변경하거나 뷰와 관련된 제약 조건을 추가/삭제

ALTER VIEW employee_view COMPILE;

** SQL Server에서 컬럼 변경 시

ALTER TABLE 테이블명 ALTER COLUMN (컬럼명 데이터타입 제약조건)

*** 컬럼 ALTER 시 한 번에 한 개만 변경

제약조건을 변경하지 않더라도 모두 작성해야 함

DROP TABLE

DROP TABLE 문은 데이터베이스에서 테이블을 완전히 삭제하는 데 사용
테이블과 그 테이블에 관련된 모든 데이터, 인덱스, 제약 조건, 트리거, 권한 등을 제거
삭제한 테이블은 복구할 수 없으므로 주의

DROP TABLE employees;

RENAME 문

RENAME문은 테이블의 이름을 변경하는 데 사용

이 명령은 테이블의 구조나 데이터에 영향을 주지 않고 오직 이름만 변경

RENAME employees TO staff;

TRUNCATE TABLE 문

TRUNCATE TABLE 문은 테이블의 모든 데이터를 빠르게 삭제하는 데 사용

이 명령은 테이블의 구조, 인덱스, 제약 조건 등은 그대로 유지하면서 오직 데이터만 삭제

TRUNCATE는 롤백이 불가능하며, DELETE 문보다 더 빠르게 데이터를 삭제

TRUNCATE TABLE employees;

DROP TABLE과 TRUNCATE TABLE의 차이점

두 명령어 모두 데이터 복구는 불가능

복구 가능성: DROP TABLE은 테이블 자체를 삭제하므로 복구할 수 없음.
TRUNCATE TABLE은 테이블의 데이터만 삭제하므로 테이블 구조는 복구 가능
영향 받는 객체: DROP TABLE은 테이블과 관련된 인덱스, 제약 조건, 트리거, 권한 등 모든 객체를 삭제
TRUNCATE TABLE은 오직 데이터만 삭제하고, 다른 객체는 그대로 둠
속도: TRUNCATE TABLE은 테이블의 모든 데이터를 빠르게 삭제
DROP TABLE은 테이블 자체를 삭제하므로 시간이 다소 소요
트랜잭션 및 롤백: TRUNCATE TABLE은 트랜잭션을 사용하지 않으며 롤백이 불가능
DROP TABLE은 트랜잭션을 사용하며 롤백 가능 (단, 오라클에서는 롤백 불가)
용도: DROP TABLE은 테이블을 완전히 제거할 때 사용
TRUNCATE TABLE은 테이블의 데이터만 빠르게 삭제하려는 경우 사용

CTAS (테이블 복사)

-- drop: 테이블 제거, 강력한 제거
-- 테이블 복사 (CTAS)
CREATE TABLE tb_emp_copy
AS SELECT * FROM tb_emp;

컬럼에 제약조건 추가

-- ALTER문으로 제약조건 추가하기
-- stu_num에 깜빡하고  primary key를 안 걸었다면?
ALTER TABLE tbl_score 
ADD CONSTRAINT pk_tbl_score  -- 변수명처럼 ALIAS를 만들어줌
PRIMARY KEY (stu_num);

제약조건 삭제

-- pk를 제거
ALTER TABLE tbl_score
DROP CONSTRAINT pk_tbl_score;

코딩 일지

2024.04.26 47일차 DB RDBMS, DDL

RDBMS 관계형 데이터베이스 관리 시스템

SQL 관례 - 테이블 이름 생성 규칙

제약조건

DDL 데이터 정의어

CREATE TABLE문

ON DELETE 옵션

ALTER

DROP TABLE

RENAME 문

TRUNCATE TABLE 문

DROP TABLE과 TRUNCATE TABLE의 차이점

CTAS (테이블 복사)

컬럼에 제약조건 추가

제약조건 삭제

티스토리툴바

2024.04.26 47일차 DB RDBMS, DDL

RDBMS 관계형 데이터베이스 관리 시스템

SQL 관례 - 테이블 이름 생성 규칙

제약조건

DDL 데이터 정의어

CREATE TABLE문

ON DELETE 옵션

ALTER

DROP TABLE

RENAME 문

TRUNCATE TABLE 문

DROP TABLE과 TRUNCATE TABLE의 차이점

CTAS (테이블 복사)

컬럼에 제약조건 추가

제약조건 삭제

관련글

티스토리툴바