[멀티캠퍼스] 풀스택 개발자 아카데미 (10) - SQL

0. SQL

데이터가 지배하는 시대, 데이터를 효율적으로 관리하고 활용하는 능력은 필수적이다. 그 중심에는 **SQL(Structured Query Language)**이 있다. SQL은 데이터베이스와 소통하기 위한 표준 언어로, 데이터를 정의하고, 조작하며, 제어하는 모든 과정을 관장한다.

1. 데이터베이스와 SQL

데이터베이스는 논리적으로 연관된 데이터를 구조적으로 통합하여 여러 사용자가 공유하고 동시에 접근할 수 있도록 만든 체계적인 모음이다. 과거의 파일 시스템은 데이터 중복과 불일치 문제를 야기했지만, 데이터베이스 관리 시스템(DBMS)은 이를 해결하며 데이터의 독립성, 안정성, 무결성을 보장한다.

SQL은 바로 이 DBMS와 소통하기 위한 언어이다. 1970년대 IBM에서 개발된 SQL은 특정 데이터베이스 유형에 얽매이지 않고 사용자가 편리하게 데이터를 다룰 수 있도록 설계되었다. Oracle의 PL/SQL, MS SQL Server의 T-SQL처럼 각 DBMS는 표준 SQL을 기반으로 특화된 기능을 추가하여 사용하지만, 그 근간은 표준 SQL에 두고 있다.

SQL은 기능에 따라 크게 세 가지로 분류된다.

• 데이터 정의어 (DDL: Data Definition Language): 데이터베이스의 구조를 정의 (CREATE, ALTER, DROP)
• 데이터 조작어 (DML: Data Manipulation Language): 데이터를 삽입, 수정, 삭제, 조회 (INSERT, UPDATE, DELETE, SELECT)
• 데이터 제어어 (DCL: Data Control Language): 데이터에 대한 접근 권한을 제어 (GRANT, REVOKE, COMMIT, ROLLBACK)

2. 데이터 정의어 (DDL)

DDL은 데이터베이스 객체(테이블, 뷰, 인덱스 등)의 구조를 만들고, 변경하고, 삭제하는 역할을 한다.

2.1. CREATE

CREATE 문은 새로운 데이터베이스 객체를 생성하는 데 사용된다. 가장 대표적인 것이 CREATE TABLE이다. 테이블을 생성할 때는 각 열(Column)의 이름과 데이터 타입, 그리고 다양한 제약 조건(Constraints)을 지정해야 한다.

2.1.1. 주요 제약 조건:

• PRIMARY KEY (기본키): 테이블의 각 행(Row)을 고유하게 식별하는 키. NOT NULL과 UNIQUE의 특징을 모두 가짐

• FOREIGN KEY (외래키): 다른 테이블의 기본키를 참조하는 열. 테이블 간의 관계를 설정하는 데 사용

  • 참조 옵션: 외래키 설정 시, 부모 테이블의 데이터가 변경(UPDATE)되거나, 삭제(DELETE)될 때, 자식 테이블의 데이터를 어떻게 처리할 지 정의할 수 있다.

    • ON DELETE CASCADE: 부모 데이터가 삭제되면, 이를 참조하는 자식 데이터도 함께 삭제된다.
    • ON DELETE SET NULL: 부모 데이터가 삭제되면, 자식 데이터의 외래키 값을 NULL로 변경
    • ON DELETE RESTRICT (또는 NO ACTION): 자식 데이터가 존재하는 한, 부모 데이터의 삭제나 수정이 불가능
• NOT NULL: 해당 열에 NULL 값을 허용하지 않음
• UNIQUE: 해당 열의 모든 값에 대해 중복 값을 불허
• DEFAULT: 값이 명시적으로 지정되지 않았을 때 사용될 기본값을 설정
• CHECK: 특정 조건을 만족하는 값만 허용

-- 학생(student) 테이블 생성 예제
CREATE TABLE student (
    std_no VARCHAR(10) PRIMARY KEY, -- 학번 (기본키)
    std_name VARCHAR(30) NOT NULL, -- 이름 (NULL 불허)
    std_near NUMBER(3) DEFAULT 1, -- 학년 (기본값 1)
    email VARCHAR(50) UNIQUE, -- 이메일 (유일 값)
    dpt_no VARCHAR(10), -- 학과번호
    CONSTRAINT fk_department FOREIGN KEY (dpt_no) REFERENCES department(dpt_no), -- 외래키 설정: 학과 번호
    CONSTRAINT ch_std_year CHECK (std_year BETWEEN 1 AND 4)
);

테이블을 생성할 때는 참조 무결성 제약조건을 고려해야 한다. 외래키를 포함하는 테이블(자식 테이블)은 참조되는 테이블(부모 테이블)이 먼저 생성되어 있어야 한다. 마찬가지로 데이터를 입력할 때도 부모 테이블에 해당 데이터가 먼저 존재해야 한다.

2.1.2. SEQUENCE

Sequence는 데이터베이스에서 유일한 숫자를 순차적으로 생성하는 객체이다. 주로 기본키 값을 자동으로 증가시켜야 할 때 사용된다. 테이블과 독립적으로 존재하므로, 하나의 시퀸스를 여러 테이블에서 공유할 수 있다.

CREATE SEQUENCE student_seq
    START WITH 1          -- 1부터 시작
    INCREMENT BY 1        -- 1씩 증가
    NOMAXVALUE            -- 최댓값 없음
    NOCYCLE               -- 최댓값 도달 시 순환 안 함
    CACHE 20;             -- 성능 향상을 위해 20개씩 미리 생성

SEQUENCE의 사용:

• NEXTVAL: 시퀀스의 다음 값을 가져온다.
• CURRVAL: 시퀀스의 현재 값을 확인한다.

-- 시퀀스를 사용하여 데이터 삽입
INSERT INTO student (std_no, std_name)
VALUES (student_seq.NEXTVAL, '홍길동');

2.2. ALTER

ALTER 문은 이미 생성된 테이블의 구조를 변경할 때 사용한다. 새로운 열을 추가하거나(ADD), 기존 열의 데이터 타입을 변경하고(MODIFY), 열의 이름을 바꾸거나(RENAME COLUMN), 열을 삭제할 수 있다(DROP COLUMN). 또한, 기본키나 외래키 같은 제약조건을 추가하거나 삭제하는 것도 가능하다.

-- product 테이블에 재고(prdStock) 열 추가
ALTER TABLE product
ADD prd_s_tock NUMBER(8);

2.3. DROP

DROP 문은 테이블을 포함한 데이터베이스 객체를 완전히 삭제한다. DROP TABLE을 실행하면 테이블의 구조뿐만 아니라 저장된 모든 데이터가 영구적으로 사라지므로 신중하게 사용해야 한다. 외래키에 의해 참조되고 있는 부모 테이블은 바로 삭제할 수 없으며, 자식 테이블을 먼저 삭제하거나 외래키 제약조건을 먼저 제거해야 한다.

3. 데이터 조작어 (DML)

DML은 테이블 내의 데이터를 직접 관리하는 명령어들로, CRUD(Create, Read, Update, Delete) 작업을 수행한다.

3.1. INSERT

INSERT 문은 테이블에 새로운 행을 추가한다. INSERT INTO 테이블명 (열 리스트) VALUES (값 리스트) 형식으로 사용하며, 열 리스트와 값 리스트의 순서와 데이터 타입은 일치해야 한다.

-- publisher 테이블에 데이터 삽입
INSERT INTO publisher (pub_no, pub_name)
VALUES ('1', '서울 출판사');

데이터를 입력할 때는 기본키 중복, 외래키 참조 오류 등 제약조건 위배에 주의해야 한다.

3.2. SELECT

SELECT 문은 DML에서 가장 중요하고 빈번하게 사용되는 명령어로, 테이블에서 원하는 데이터를 조회한다.

기본 형식:

SELECT [ALL | DISTINCT] 열_리스트
FROM 테이블명
[WHERE 조건]
[GROUP BY 그룹화_열]
[HAVING 그룹_조건]
[ORDER BY 정렬_열 [ASC | DESC]];

• FROM: 데이터를 가져올 테이블을 지정한다.
• WHERE: 특정 조건을 만족하는 행만 필터링한다. 비교(=, <, >), 범위(BETWEEN), 리스트(IN), NULL, 논리(AND, OR), 패턴 매칭(LIKE) 등 다양한 연산자를 사용할 수 있다.
• GROUP BY: 특정 열의 값을 기준으로 행들을 그룹화한다. 주로 집계 함수(SUM, AVG, COUNT, MAX, MIN)와 함께 사용된다.
• HAVING: GROUP BY로 그룹화된 결과에 대한 조건을 지정한다.
• ORDER BY: 결과를 특정 열의 값을 기준으로 오름차순(ASC) 또는 내림차순(DESC)으로 정렬한다.
• DISTINCT: 중복된 값을 제거하고 유일한 값만 보여준다.

-- book 테이블에서 가격이 20000원 이상인 책의 이름과 가격을 가격 내림차순으로 정렬하여 조회
SELECT book_name, book_price
FROM book
WHERE book_price >= 20000
ORDER BY book_price DESC;

3.3. UPDATE

UPDATE 문은 테이블에 이미 존재하는 데이터의 값을 수정한다. WHERE 절을 사용하여 수정할 행을 특정하지 않으면 테이블의 모든 행이 변경될 수 있으므로 매우 주의해야 한다.

-- 상품번호가 '5'인 상품의 이름을 'UHD TV'로 변경
UPDATE product
SET prd_name = 'UHD TV'
WHERE prd_no = '5';

3.4. DELETE

DELETE 문은 테이블에서 특정 행을 삭제한다. UPDATE와 마찬가지로 WHERE 절을 생략하면 테이블의 모든 데이터가 삭제되므로 신중하게 사용해야 한다.

-- 상품명이 '그늘막 텐트'인 상품 삭제
DELETE FROM product
WHERE prd_name = '그늘막 텐트';

4. 데이터 제어어 (DCL)

DCL은 데이터의 보안, 무결성, 회복 등을 정의하는 데 사용된다. 주로 데이터베이스 관리자(DBA)가 사용하여 사용자에게 특정 권한을 부여하거나(GRANT), 부여된 권한을 회수한다(REVOKE).

• GRANT: 사용자에게 테이블 생성, 데이터 조회/수정/삭제 등의 권한을 부여한다.
• REVOKE: 사용자에게 부여했던 권한을 회수한다.
• COMMIT: 모든 작업을 정상적으로 처리했음을 확정하고, 변경된 내용을 데이터베이스에 영구적으로 반영한다.
• ROLLBACK: 작업 중 문제가 발생했을 때, 변경된 내용을 취소하고 마지막 COMMIT 상태로 되돌린다.

5. 아쉬운 점

자료에 대한 아쉬운 점

이번 주제에 대한 수업은 SQL의 DDL, DML, DCL 전반에 걸쳐 기본적인 내용을 충실히 다루고 있다. 하지만 몇 가지 아쉬운 점도 존재한다.

1. 고급 SQL 개념의 부재: JOIN, 서브쿼리(Subquery), 뷰(View), 인덱스(Index), 트랜잭션(Transaction) 등 실무에서 매우 중요하게 사용되는 고급 SQL 개념에 대한 설명이 부족하다. 특히 여러 테이블의 데이터를 결합하는 JOIN은 관계형 데이터베이스의 핵심이므로 반드시 다루어져야 한다.
2. 데이터 모델링에 대한 심층적 설명 부족: 관계형 데이터베이스 설계의 기초가 되는 데이터 모델링(정규화 등)에 대한 내용이 거의 언급되지 않았다. 좋은 데이터베이스 설계가 선행되어야 효율적인 SQL 작성이 가능하다.
3. 성능 튜닝 관련 내용 부재: 대용량 데이터를 다룰 때 필수적인 쿼리 성능 최적화(Execution Plan 분석, 인덱스 활용 등)에 대한 내용이 없다.
4. DCL에 대한 구체적인 예제 부족: GRANT, REVOKE에 대한 개념 설명은 있지만, 실제 사용자 계정을 만들고 권한을 부여하고 회수하는 구체적인 시나리오 예제가 부족하여 실제 활용법을 익히기 어렵다.

학습 방향 및 팁

1. JOIN과 서브쿼리 마스터하기: 먼저 INNER JOIN, LEFT/RIGHT OUTER JOIN, FULL OUTER JOIN 등 다양한 JOIN의 종류와 사용법을 완벽히 익혀야 한다. 그 다음, 쿼리 안에 또 다른 쿼리를 사용하는 서브쿼리를 학습하여 복잡한 데이터 조회 능력을 길러야 한다.
2. 실습, 또 실습: SQL은 이론보다 실습이 훨씬 중요하다. 제공된 자료의 예제를 직접 따라 쳐보는 것을 넘어, 자신만의 가상 시나리오(예: 온라인 쇼핑몰, 도서관 관리 시스템)를 설정하고 테이블을 직접 설계하고 데이터를 채워 넣으며 다양한 쿼리를 작성해보는 것이 큰 도움이 된다.
3. 데이터 모델링의 이해: 정규화(Normalization) 과정을 이해하고 직접 테이블을 설계해보는 연습을 하는 것이 좋다. 이는 데이터 중복을 최소화하고 데이터 무결성을 높이는 데 필수적이다.
4. 실행 계획(Execution Plan) 분석 연습: 사용하는 DBMS가 특정 쿼리를 어떻게 처리하는지 보여주는 실행 계획을 분석하는 습관을 들이면, 비효율적인 쿼리를 개선하고 성능을 최적화하는 능력을 기를 수 있다.
5. 다양한 DBMS 경험: Oracle, MySQL, PostgreSQL 등 여러 종류의 DBMS를 사용해보며 각 시스템의 미묘한 SQL 문법 차이와 특성을 경험해보는 것도 좋은 학습 방법이다.

SQL은 배우기 쉬우면서도 깊이가 있는 언어이다. 꾸준한 학습과 실습을 통해 데이터베이스를 자유자재로 다루는 전문가로 거듭나길 바란다.