01_basic_crud.sql

  1-- =============================================================================
  2-- PostgreSQL CRUD 기본 예제
  3-- Basic CRUD Operations (Create, Read, Update, Delete)
  4-- =============================================================================
  5
  6-- 이 파일은 PostgreSQL의 기본 CRUD 작업을 보여줍니다.
  7-- 실행 전 데이터베이스에 연결하세요: psql -U postgres -d your_database
  8
  9-- =============================================================================
 10-- 1. CREATE - 테이블 생성 및 데이터 삽입
 11-- =============================================================================
 12
 13-- 테이블 생성
 14DROP TABLE IF EXISTS employees CASCADE;
 15DROP TABLE IF EXISTS departments CASCADE;
 16
 17-- 부서 테이블
 18CREATE TABLE departments (
 19    dept_id SERIAL PRIMARY KEY,
 20    dept_name VARCHAR(50) NOT NULL UNIQUE,
 21    location VARCHAR(100),
 22    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 23);
 24
 25-- 직원 테이블
 26CREATE TABLE employees (
 27    emp_id SERIAL PRIMARY KEY,
 28    first_name VARCHAR(50) NOT NULL,
 29    last_name VARCHAR(50) NOT NULL,
 30    email VARCHAR(100) UNIQUE,
 31    hire_date DATE DEFAULT CURRENT_DATE,
 32    salary NUMERIC(10, 2) CHECK (salary > 0),
 33    dept_id INTEGER REFERENCES departments(dept_id),
 34    is_active BOOLEAN DEFAULT TRUE,
 35    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 36    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 37);
 38
 39-- 인덱스 생성
 40CREATE INDEX idx_employees_dept ON employees(dept_id);
 41CREATE INDEX idx_employees_email ON employees(email);
 42
 43-- =============================================================================
 44-- INSERT - 데이터 삽입
 45-- =============================================================================
 46
 47-- 단일 행 삽입
 48INSERT INTO departments (dept_name, location)
 49VALUES ('Engineering', 'Seoul');
 50
 51INSERT INTO departments (dept_name, location)
 52VALUES ('Marketing', 'Busan');
 53
 54INSERT INTO departments (dept_name, location)
 55VALUES ('Sales', 'Daegu');
 56
 57INSERT INTO departments (dept_name, location)
 58VALUES ('HR', 'Seoul');
 59
 60-- 여러 행 한번에 삽입
 61INSERT INTO employees (first_name, last_name, email, hire_date, salary, dept_id)
 62VALUES
 63    ('철수', '김', 'kim.cs@company.com', '2020-01-15', 50000, 1),
 64    ('영희', '이', 'lee.yh@company.com', '2019-06-20', 55000, 1),
 65    ('민수', '박', 'park.ms@company.com', '2021-03-10', 48000, 2),
 66    ('수진', '정', 'jung.sj@company.com', '2018-11-05', 62000, 1),
 67    ('동욱', '최', 'choi.dw@company.com', '2022-08-01', 45000, 3),
 68    ('미영', '강', 'kang.my@company.com', '2020-05-15', 52000, 2),
 69    ('지훈', '조', 'cho.jh@company.com', '2019-09-20', 58000, 3),
 70    ('소영', '윤', 'yoon.sy@company.com', '2021-12-01', 47000, 4);
 71
 72-- RETURNING 절로 삽입된 데이터 확인
 73INSERT INTO employees (first_name, last_name, email, salary, dept_id)
 74VALUES ('새직원', '테스트', 'test@company.com', 40000, 1)
 75RETURNING emp_id, first_name, last_name;
 76
 77-- =============================================================================
 78-- 2. READ - 데이터 조회
 79-- =============================================================================
 80
 81-- 전체 조회
 82SELECT * FROM employees;
 83
 84-- 특정 컬럼만 조회
 85SELECT first_name, last_name, email, salary
 86FROM employees;
 87
 88-- 조건부 조회 (WHERE)
 89SELECT first_name, last_name, salary
 90FROM employees
 91WHERE salary > 50000;
 92
 93-- 여러 조건 (AND, OR)
 94SELECT *
 95FROM employees
 96WHERE dept_id = 1 AND salary > 50000;
 97
 98SELECT *
 99FROM employees
100WHERE dept_id = 1 OR salary > 55000;
101
102-- BETWEEN, IN, LIKE
103SELECT first_name, last_name, salary
104FROM employees
105WHERE salary BETWEEN 45000 AND 55000;
106
107SELECT first_name, last_name, dept_id
108FROM employees
109WHERE dept_id IN (1, 2);
110
111SELECT first_name, last_name, email
112FROM employees
113WHERE email LIKE '%@company.com';
114
115-- NULL 체크
116SELECT *
117FROM employees
118WHERE email IS NOT NULL;
119
120-- 정렬 (ORDER BY)
121SELECT first_name, last_name, salary
122FROM employees
123ORDER BY salary DESC;
124
125SELECT first_name, last_name, dept_id, salary
126FROM employees
127ORDER BY dept_id ASC, salary DESC;
128
129-- 제한 (LIMIT, OFFSET)
130SELECT first_name, last_name, salary
131FROM employees
132ORDER BY salary DESC
133LIMIT 5;
134
135-- 페이지네이션 (2페이지, 페이지당 3개)
136SELECT first_name, last_name, salary
137FROM employees
138ORDER BY emp_id
139LIMIT 3 OFFSET 3;
140
141-- DISTINCT - 중복 제거
142SELECT DISTINCT dept_id
143FROM employees;
144
145-- 별칭 (AS)
146SELECT
147    first_name AS "이름",
148    last_name AS "성",
149    salary AS "연봉"
150FROM employees;
151
152-- 계산 컬럼
153SELECT
154    first_name,
155    last_name,
156    salary,
157    salary * 12 AS annual_salary,
158    salary * 1.1 AS after_raise
159FROM employees;
160
161-- =============================================================================
162-- 3. UPDATE - 데이터 수정
163-- =============================================================================
164
165-- 단일 행 수정
166UPDATE employees
167SET salary = 52000
168WHERE emp_id = 1;
169
170-- 여러 컬럼 수정
171UPDATE employees
172SET salary = 55000, updated_at = CURRENT_TIMESTAMP
173WHERE emp_id = 1;
174
175-- 조건부 대량 수정
176UPDATE employees
177SET salary = salary * 1.1
178WHERE dept_id = 1;
179
180-- 서브쿼리를 이용한 수정
181UPDATE employees
182SET salary = salary * 1.05
183WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Engineering');
184
185-- RETURNING으로 수정된 데이터 확인
186UPDATE employees
187SET salary = salary * 1.02
188WHERE emp_id = 3
189RETURNING emp_id, first_name, salary;
190
191-- =============================================================================
192-- 4. DELETE - 데이터 삭제
193-- =============================================================================
194
195-- 조건부 삭제
196DELETE FROM employees
197WHERE emp_id = 9;
198
199-- 전체 삭제 (주의!)
200-- DELETE FROM employees;
201
202-- TRUNCATE (빠른 전체 삭제, 트랜잭션 불가)
203-- TRUNCATE TABLE employees RESTART IDENTITY;
204
205-- RETURNING으로 삭제된 데이터 확인
206DELETE FROM employees
207WHERE email = 'test@company.com'
208RETURNING *;
209
210-- =============================================================================
211-- 5. 트랜잭션 (Transaction)
212-- =============================================================================
213
214-- 트랜잭션 시작
215BEGIN;
216
217INSERT INTO employees (first_name, last_name, email, salary, dept_id)
218VALUES ('트랜잭션', '테스트', 'trans@company.com', 45000, 1);
219
220UPDATE employees
221SET salary = salary * 1.05
222WHERE email = 'trans@company.com';
223
224-- 확인
225SELECT * FROM employees WHERE email = 'trans@company.com';
226
227-- 커밋 또는 롤백
228COMMIT;
229-- 또는 ROLLBACK;
230
231-- =============================================================================
232-- 6. UPSERT (INSERT ... ON CONFLICT)
233-- =============================================================================
234
235-- 중복 시 무시
236INSERT INTO departments (dept_name, location)
237VALUES ('Engineering', 'Incheon')
238ON CONFLICT (dept_name) DO NOTHING;
239
240-- 중복 시 업데이트
241INSERT INTO departments (dept_name, location)
242VALUES ('Engineering', 'Incheon')
243ON CONFLICT (dept_name)
244DO UPDATE SET location = EXCLUDED.location;
245
246-- =============================================================================
247-- 7. 테이블 확인 및 정보
248-- =============================================================================
249
250-- 테이블 구조 확인
251\d employees
252
253-- 테이블 목록
254\dt
255
256-- 인덱스 확인
257\di
258
259-- =============================================================================
260-- 정리 (필요시)
261-- =============================================================================
262-- DROP TABLE IF EXISTS employees CASCADE;
263-- DROP TABLE IF EXISTS departments CASCADE;