SQL Basic - DDL

조정훈·2024년 3월 25일

DDL

DDL : Data Define Language
이미 정의되어있는 테이블을 변경하는 것
주요 명령어 : CREATE, ALTER, DROP, TRUNCATE

CREATE

테이블 생성할 때 사용
주로 사용하는 컬럼 타입 : SERIAL, INTEGER, FLOAT, NUMERIC, VARCHAR, TEXT, TIMESTAMP, BOOLEAN, ARRAY, JSON(B)

CREATE TABLE 테이블이름 (
    열1 데이터타입,
    열2 데이터타입,
    ...
);

PRIMARY KEY
기본키 설정. UNIQUE, NOT NULL 을 만족하는 인덱스가 생성된다. 모든 로우는 이 컬럼을 기준으로 고유하게 식별된다.
FOERIGN KEY
다른 테이블의 컬럼에 존재하는 값을 사용하는지 검사하는 제약조건.
CONSTRAINT 구문으로 설정 가능하고 삭제 또는 업데이트 시 원본 테이블 적용 유무 결정가능.
참조무결성 을 유지하는데 사용한다.

참조무결성 : 참조 할 때 참조하는 값이 실제로 그 테이블에 있어야한다. 참조되는 값을 insert, update 할 때 마다 데이터베이스가 어떻게 동작해야할지 정의(같이 삭제할지, null값으로 채울지, 그냥 유지할지 등)
NOT NULL
'null값이 존재해선 안된다'라는 제약조건
CHECK
값이 조건을 만족하는지 검사하는 제약조건
DEFAULT
기본값 설정. 단순값도 되고 now()같은 함수도 들어감
UNIQUE
'중복이 있으면 안된다' 라는 제약조건

위 기능 모두 다 사용한 예제코드

-- CREATE 문법을 사용하여 테이블 생성
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, -- PRIMARY KEY 제약: EmployeeID 열을 기본 키로 지정하여 각 행을 고유하게 식별합니다.
    FirstName VARCHAR(50) NOT NULL, -- NOT NULL 제약: FirstName 열은 비어 있을 수 없습니다.
    LastName VARCHAR(50) NOT NULL, -- NOT NULL 제약: LastName 열은 비어 있을 수 없습니다.
    Department VARCHAR(50),
    Salary DECIMAL(10, 2) DEFAULT 0, -- DEFAULT 제약: Salary 열의 기본값을 0으로 설정합니다.
    
    -- CHECK 제약: Salary 열의 값은 0보다 커야 합니다.
    CONSTRAINT check_salary CHECK (Salary > 0),

    -- FOREIGN KEY 제약: Department 열은 Departments 테이블의 DepartmentID 열을 참조합니다.
    DepartmentID INT,
    CONSTRAINT fk_department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),

    -- UNIQUE KEY 제약: Email 열의 값은 고유해야 합니다.
    Email VARCHAR(100) UNIQUE,

    -- CONSTRAINT를 사용하여 복합 PRIMARY KEY 제약을 정의할 수 있습니다.
    CONSTRAINT pk_employee_id_department PRIMARY KEY (EmployeeID, DepartmentID)
);

ALTER

테이블 속성 값 변경할 때 사용

컬럼 추가

ALTER TABLE table_name ADD COLUMN column_name datatype column_constraint;

컬럼 삭제

ALTER TABLE table_name DROP COLUMN column_name;

컬럼 이름 변경

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

컬럼 기본값 추가 혹은 삭제

ALTER TABLE table_name ALTER COLUMN column_name [SET DEFAULT value | DROP DEFAULT];

컬럼 NOT NULL 제약 조건 추가 및 삭제

ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL | DROP NOT NULL];

컬럼 CHECK 제약 조건 추가

ALTER TABLE table_name ADD CHECK expression;

테이블 이름 변경

ALTER TABLE table_name RENAME TO new_table_name;

테이블 PRIMARY KEY 제약조건 추가

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (pk_columns);

테이블 UNIQUE KEY 제약조건 추가

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (uk_columns);

테이블 FOREIGN KEY 제약조건 추가

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (fk_columns) REFERENCES parent_table (alter table);

테이블 제약조건 제거

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

DROP

해당 테이블이 존재할 때 테이블 삭제

DROP TABLE IF EXISTS table_name;

TRUNCATE

테이블의 데이터를 모두 지울 때 사용(테이블은 x)

TRUNCATE TABLE table_name [CASCADE];

CASCADE
'나랑 연관된 다른 테이블에도 영향을 줘라' 라는 뜻
ex) a테이블에서 어떤값이 지워지면 b테이블에 있는 값도 같이 지워짐
DELETE vs TRAUNCATE ?
DELETE FROM의 경우 WAL로그를 기록한 뒤 테이블을 삭제하기 때문에 데이터 삭제 이후에도 트랜잭션 로그를 기반으로 데이터 복구가 가능. 다만 속도가 좀 느려 질 수 있다.
TRUNCATE TABLE은 WAL로그를 남기지 않으면서 테이블을 삭제하기 때문에 롤백이 불가능. 다만 속도가 훨씬 빠르다.

위의 모든 구문을 사용한 예제코드

-- CREATE 문법을 사용하여 테이블 생성
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

-- ALTER 문법을 사용하여 테이블에 새로운 열 추가
ALTER TABLE Employees
ADD Salary INT;

-- ALTER 문법을 사용하여 기존 열의 데이터 타입 변경
ALTER TABLE Employees
ALTER COLUMN Salary DECIMAL(10, 2);

-- ALTER 문법을 사용하여 기존 열의 이름 변경
ALTER TABLE Employees
RENAME COLUMN FirstName TO First_Name;

-- DROP 문법을 사용하여 테이블 삭제
DROP TABLE Employees;

-- TRUNCATE 문법을 사용하여 테이블 내의 모든 데이터 삭제
TRUNCATE TABLE Employees;

CREATE INDEX

연산 효율을 높이기 위해 사용. PRIMARY KEY인덱스로 충분하다면 사용하지 않아도 된다. 그렇지 않거나 특정 연산 효율을 올리고 싶다면 인덱스를 만드는게 좋다.

장점
기본적으로 정렬되어있기 때문에 order by 또는 min/max 결과를 빠르게 찾을 수 있다.
단점
인덱스도 하나의 자료구조이므로 별도 저장 공간이 필요하다.
insert/update/delete 시 테이블 및 인덱스 양쪽에 모두 연산을 해야하므로 속도가 느려진다.
인덱스의 순서를 실제로 where, order by 를 고려하지 않고 정의했다면 기대하는 만큼의 성능 향상을 얻을 수 없다. 또는 정상적으로 인덱스를 사용하지 않아 느려진다.
B-tree의 경우 테이블의 대부분을 조회한다면, Full Scan이 일어날 수 있다.
-> DBMS에 내장된 SQL 옵티마이저가 인덱스를 통해 테이블을 검색하는 것 보다 Full Scan이 더 빠르다고 판단하기 때문이다.
주의할 점
인덱스 정의 순서가 실제로 서비스에서 조회하거나 필터링 하는 컬럼으로 잘 정의되어있는지?
-> 불필요하게 인덱스가 정의되는 것을 방지해야한다.
해당 테이블에 CUD 연산이 자주 발생하는지?
✔ EXPLAIN으로 쿼리 플랜으로 인덱스를 잘 사용하는지 확인하기

종류

B-Tree 인덱스
정렬된 키-값 쌍을 저장하여 검색된 정렬된 결과에 대한 빠른 조회를 지원
범위검색이나 정렬된 결과에 대한 조회가 필요한 경우에 사용

CREATE INDEX idx_address_phone ON public.address USING btree (phone)

CREATE UNIQUE INDEX idx_address_phone ON public.address USING btree (phone)

뒤에 USING btree (phone) 안써도 디폴트가 B-tree 인덱스라서 생략 가능(chatgpt피셜)

Hash 인덱스
해시 함수를 사용하여 키-값 쌍 저장해서 빠른 등호(=)검색을 제공
등호(=)를 사용한 검색이 자주 필요하고 빠른 조회가 필요한 경우 사용

단건으로 찾을때는 Hash가 제일 빠름.

단점 : bucket의 크기가 충분하지 않으면 하나의 bucket에 여러가지 데이터가 들어감.
-> 그러면 충돌이 날 수가 있는데 이 때 chaining 기법을 이용해서 여러 데이터를 연결해서 linked list와 같은 구조로 연결시켜버린다. 이렇게 저장하는거를 '버킷체인'이라고함. 같은 값이여도 링크드리스트로 순서를 갖기 때문에 각 노드에는 데이터와 다음 노드로 가리키는 포인터가 포함되어 있어서 어떤 데이터였는지 알 수 있게 해준다.
```
버킷 : 해시 테이블에서 충돌(Collision)이 발생했을 때 데이터를 저장하는 공간
 충돌 : 해시 함수를 통해 계산된 두 개 이상의 입력 데이터가 동일한 해시 값을 가지는 상황
```

CREATE INDEX idx_address_phone ON public.address USING hash (phone)

GIN 인덱스 (Generalized Inverted Index)
텍스트 검색에서 사용
전체 텍스트 검색이나 배열의 포함 여부와 같은 컬렉션 형식의 데이터를 색인화하고 검색할 때 사용

CREATE INDEX idx_address_phone ON public.address USING gin (phone)

VIEW TABLE

뷰테이블은 하나 이상의 테이블로부터 생성된 가상 테이블로, 데이터의 일부 또는 전체를 나타내는데 사용되며 데이터를 간단하게 조회하거나 보안강화 목적으로 활용된다.

실제 저장공간을 사용하는 테이블이 아니고 SQL 형태로 정의되며 조회시 해당 SQL를 기반으로 데이터를 가져올 수있다.
-> 이로인해 복잡한 연산이 포함된 뷰테이블의 경우 조회가 느릴 수 있다.
대상테이블에 인덱스가 존재하고, 조회 시 인덱스 조건을 잘 썼다면 인덱스로 조회 가능

사용목적

데이터 보안
민감한 정보는 비공개하고 필요한 컬럼만 포함하여 뷰를 만들수 있다
복잡한 쿼리 단순화
복잡한 쿼리를 단순화하기 위해 여러 테이블을 조인하거나 집계함수를 사용하는 경우, 뷰를 사용하여 해당 로직을 캡슐화하고 간단한 형태로 쿼리 가능
중복코드 제거
자주 사용되는 쿼리를 뷰로 만들어서 중복코드 제거하고 코드 재사용성 높일 수 있다.
데이터 포맷 변환
데이터를 필요한 형식으로 변환할 때 사용 가능
ex) 날짜, 시간 데이터 형식을 변경하거나 특정 컬럼 값 계산하여 새로운 컬럼으로 표시
접근성 향상
사용자에게 간단한 인터페이스를 제공하기 위해 뷰를 사용하여 데이터를 필요한 형식으로 표시할 수 있다. 이를 통해 사용자가 데이터에 쉽게 접근하고 사용 가능

명령어

VIEW 생성

CREATE VIEW contact AS 
SELECT first_name, last_name, email 
   FROM customer;

MATERIALIZED VIEW 생성

CREATE MATERIALIZED VIEW [IF NOT EXISTS] contact
AS
SELECT first_name, last_name, email 
   FROM customer
WITH [NO] DATA;

MATERIALIZED VIEW REFRESH (CONCURRENTLY 옵션을 주지 않으면 대상 테이블을 LOCK 합니다. 꼭 넣으세요)

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

VIEW 삭제

DROP [MATERIALIZED] VIEW view_name;

조정훈

이전 포스트

네트워크(1)

다음 포스트