Database 개요
Database가 없었다면?
데이터베이스(DB)가 없던 시절에는 역사적으로 정보나 데이터를 책의 형태로 관리 되였다.
전화번호부나 사전처럼 데이터를 모아 놓은 책이 대표적이고, 사람들은 필요한 정보를 색인(index) 과 순서를 통해 직접 찾아야 했고, 이 과정은 매우 불편하고 시간이 오래 걸렸다.
또한 정보를 추가하거나 수정하는 것도 어렵고 비효율적이었기 때문에, 변경 사항이 생기면 책 전체를 다시 제작해야 하는 번거로움이 존재 하였다.
데이터베이스(Database)의 개념
데이터베이스는 데이터를 영구적(영속성, persistence)으로 저장하고 여러 사용자가 공유하여 활용할 수 있도록 체계적으로 통합·관리하는 구조화된 데이터 집합이다. 이는 데이터베이스 관리 시스템(DBMS)을 통해 효율적으로 저장, 검색, 수정, 삭제할 수 있으며, 응용 프로그램과 사용자가 원하는 정보를 안정적으로 제공한다. 데이터베이스에는 계층형, 네트워크형, 관계형, 객체-관계형 등 다양한 데이터 모델이 존재한다.
자료(Data)와 정보(Information)의 비교
Data
현실에서 수집된 결과로 나타난 정량적 혹은 정성적인 실제 값(수치, 텍스트)
Information
데이터를 기반으로 사용자에게 유용하게 활용 되도록 목적에 따라 가공한 결과물
Database에서 관리 되는 데이터의 종류
- 공용 데이터(Shared Data)
다수의 사용자에게 실시간으로 공유되는 공동의 데이터
→ 다중 사용자로 부터 동시에 삽입, 삭제, 수정이 병행 되며 문제가 발생! 이러한 병행 관리가 기술의 핵심 - 통합 데이터(Integrated Data)
다수 목적으로 사용되는 데이터가 한곳으로 통합저장
중복 최소화로 중복으로 인한 데이터 불일치 현상 제거 - 운영 데이터(Operational Data)
조직의 목적을 위해 사용되는 데이터
사용자별 권한, 업무 목적에 따라 접근 제어가 필요 - 저장 데이터(Stored Data)
컴퓨터 저장장치에 저장된 데이터로 주로 서버에 설치
병행성 문제 (Concurrency Issue)
데이터베이스는 실시간으로 다수의 사용자가 접근이 가능함으로 병행성의 문제가 발생 가능하다.
병행성 문제란? 특정 데이터를 여러 사용자가 동시에 조회/수정할 경우, 데이터가 의도와 다르게 처리될 수 있는 상황
예를 들어, 은행 계좌에서 여러 프로세스가 동시에 출금을 시도하는 상황에서 병행성 문제가 해결되지 않은 상태라면, 잔고가 충분하지 않더라도 두 개 이상의 출금 요청이 동시에 성공할 수 있으며, 그 결과 잔액이 2번 출금 되고 계좌 잔고가 음수로 떨어지거나 데이터 무결성이 깨질 수 있다.
트랜잭션(transaction)이란?
트랜잭션은 DB 관리 시스템의 상호 작용(업무 처리) 단위이다.
트랜잭션은 보통 2가지 방법을 위해 활용되는데, 첫 번째 활용은 업무 단위를 만들어 업무에 실패하는 경우 이를 모두 Rollback(되돌리기)하는 시점으로 활용될 수 있으며, 두 번째 활용은 처리 시간 단위로 Lock을 걸고, 다른 요청이 해당 업무(데이터)의 접근을 불가능하도록 보호 할 수 있다. (트랜잭션의 원자성 유지)
트랜잭션의 – ACID 특성
ACID(원자성, 일관성, 고립성, 지속성)는 데이터베이스 트랜잭션이 안전하게 수행된다는 것을 보장하기 위한 성질을 가리키는 약어이다. 짐 그레이는 1970년대 말에 신뢰할 수 있는 트랜잭션 시스템의 이러한 특성들을 정의하였으며 이는 점차 연구되어 DB의 핵심 기능으로 유지되고 있다.
- 원자성(Atomicity)
트랜잭션과 관련된 작업들이 부분적으로 실행되다가 중단되지 않는 것을 보장하는 능력이다. (다수의 명령을 업무처리 단위를 보장) - 정합성(Consistency)
트랜잭션 처리 전과 처리 후 데이터 모순이 없는 상태를 유지하는 것을 의미, 만일 모순 발견 시 되돌릴 수 있다. (무결성, Rollback) - 독립성(Isolation)
트랜잭션 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미
이것은 트랜잭션 밖에 있는 어떤 연산도 중간 단계의 데이터를 볼 수 없음을 의미 (트랜잭션 Locking 단위) - 지속성(Durability)
성공적으로 수행된 트랜잭션은 영원히 반영되어야 함을 의미한다.
시스템 문제, DB 일관성 체크 등을 하더라도 유지되어야 함을 의미
데이터 무결성 (data integrity)
데이터 무결성은 데이터베이스에서 데이터의 정확성과 일관성을 보장하기 위한 개념으로, 데이터가 생성되고 수정되는 전체 수명 주기 동안 변질되거나 오류가 발생하지 않도록 유지하는 것을 의미한다.
특히 RDBMS(관계형)에서 데이터 무결성은 핵심적인 기능 중 하나이며, 이를 보장하기 위해 무결성 제약조건이 시스템 차원에서 강제된다.
- 개체 무결성(Entity Integrity) : 기본 키(PK)는 NULL이 될 수 없으며, 중복되지 않아야 함
- 참조 무결성(Referential Integrity) : 외래 키(FK)는 참조하는 기본 키가 실제 존재해야 함
- 도메인/범위 무결성(Domain/Range Integrity) : 속성의 값은 정의된 범위나 형식에 따라야 함
제약조건(Constraints)
제약조건(Constraint)은 데이터베이스에 저장되는 데이터의 정확성, 일관성, 무결성을 보장하기 위해 테이블
컬럼에 부여하는 규칙을 의미한다.
| 제약조건 | 설명 | 적용 예시 |
|---|---|---|
| PRIMARY KEY (기본키) | - 테이블에서 각 행을 고유하게 식별 - NOT NULL + UNIQUE 성질 가짐 - DBMS에 따라 자동으로 인덱스 생성 | id 컬럼에 기본키 설정 id INT PRIMARY KEY |
| FOREIGN KEY (외래키) | - 다른 테이블의 기본키를 참조 - 참조 무결성(Referential Integrity) 유지 | user_id → users(id) 참조 user_id INT, FOREIGN KEY(user_id) REFERENCES users(id) |
| UNIQUE | - 중복 값 불가 - 단, NULL은 허용 | email 컬럼 중복 방지 email VARCHAR(100) UNIQUE |
| NOT NULL | - 반드시 값이 있어야 함 - 비어있는 값 허용하지 않음 | username 컬럼 빈 값 불가 username VARCHAR(50) NOT NULL |
| CHECK | - 특정 조건 만족 시에만 값 허용 | age INT CHECK (age >= 0) salary DECIMAL CHECK (salary > 1000) |
| DEFAULT | - 값이 입력되지 않았을 때 자동으로 기본값 지정 | status 기본값 ACTIVE status VARCHAR(20) DEFAULT 'ACTIVE' |
DBMS (Data Base Management System)
데이터를 만들고 저장하고 관리하는 프로그램(=시스템)
DBMS는 다수의 사용자가 DB 내의 데이터를 접근할 수 있도록 해주는 소프트웨어 도구의 집합이다.
DBMS는 사용자 또는 다른 프로그램의 요구를 처리하고 적절히 응답하여 데이터를 사용할 수 있도록 해준다.
또한 운영 단계에서 필요한 데이터의 백업, 복원, 보안등의 다양한 기능을 제공하여 상업용으로 활용할 수 있는 시스템이다.
| 구분 | 의미 | 주요 기능 | 예시 명령어 |
|---|---|---|---|
| 데이터 조작 (DML, Data Manipulation Language) | - 사용자가 요구하는 데이터를 조회, 삽입, 수정, 삭제하는 기능 - 실제 사용자와 DB 간의 데이터 연동 수단 | - 데이터 조회 (SELECT) - 데이터 삽입 (INSERT) - 데이터 수정 (UPDATE) - 데이터 삭제 (DELETE) | sql SELECT * FROM users; INSERT INTO users VALUES (...); UPDATE users SET name='Tom' WHERE id=1; DELETE FROM users WHERE id=1; |
| 데이터 정의 (DDL, Data Definition Language) | - 데이터의 구조를 정의 - 테이블, 인덱스, 뷰, 스키마 등의 생성·변경·삭제 수행 | - 데이터베이스 객체 생성 (CREATE) - 구조 변경 (ALTER) - 삭제 (DROP, TRUNCATE) | sql CREATE TABLE users (...); ALTER TABLE users ADD COLUMN age INT; DROP TABLE users; |
| 데이터 제어 (DCL, Data Control Language) | - DB 사용자의 권한 관리 및 보안 제어 - 백업과 회복, 동시성 제어 지원 | - 권한 부여 (GRANT) - 권한 회수 (REVOKE) | sql GRANT SELECT ON users TO user1; REVOKE INSERT ON users FROM user1; |
DBMS 브랜드 및 특징
| 구분 | 개발사 | 특징 | 비용 | 운영체제 기반 | 용도 |
|---|---|---|---|---|---|
| Access | Microsoft | - Office 연동 쉬움 - 개인/소규모 환경에 적합 | MS Office 구매 시 활용 | 윈도우 | 소규모 개인 DB |
| SQL Server | Microsoft | - .NET 연동 가능 - 안정적인 상업용 DB | Copy 당 약 1,000만 원 이상 | 윈도우 | 상업용 DB |
| Oracle | Oracle | - 안정성과 확장성 최고 - 대규모 엔터프라이즈 DB에서 최다 사용 | Copy 당 약 2,000만 원 이상 (가장 비쌈) | 윈도우, 유닉스, 리눅스 | 대규모 엔터프라이즈 DB |
| MySQL | (현재 Oracle 소속) | - 오픈소스 - 속도 빠름 - 웹서비스에서 널리 활용 | 무료 (추가 비용 지불 시 A/S 지원) | 윈도우, 유닉스, 리눅스 | 상업용 DB (웹서비스 등) |
| MariaDB | MariaDB 재단 | - MySQL 호환성 보장 - 성능 개선 버전 | 무료 (추가 비용 지불 시 A/S 지원) | 윈도우, 유닉스, 리눅스 | 상업용 DB |
| PostgreSQL | PostgreSQL 재단 | - 객체-관계형 기능 탑재 - 확장성과 안정성 우수 | 무료 (추가 비용 지불 시 A/S 지원) | 윈도우, 유닉스, 리눅스 | 오픈소스, 상업/연구용 DB |
DBMS 브랜드 별 점유율
RDB 기준으로 데이터베이스 순위는 DB-Engines Ranking이 대표적이며, 검색 추세, 기술 토론, 구인 정보 등 다양한 지표를 반영해 산출된다. 2025년 기준 상위 RDBMS는 Oracle, MySQL, Microsoft SQL Server, PostgreSQL 순으로 나타내고 있다. 상위 두 엔진은 모두 Oracle 라이선스 모델로 사실상 DB는 Oracle DB의 점유율이 월등히 높다.
DBMS 사용 이점
- 데이터 독립화
데이터와 응용 프로그램을 분리함으로써 상호 영향 정도를 줄일 수 있음 - 데이터 중복 최소화, 데이터 무결성 보장
중복되는 데이터를 최소화하면 데이터 무결성이 손상될 가능성이 줄어듦
중복되는 데이터를 최소화하면 필요한 저장공간의 낭비를 줄일 수 있음 - 데이터 보안 향상
응용프로그램은 DBMS를 통해 DBMS가 허용하는 데이터에만 접근 가능
권한에 맞게 데이터 접근을 제한하거나 데이터를 암호화시켜 저장 가능 - 관리 편의성 향상
다양한 방법으로 데이터 백업 가능
장애 발생 시 데이터 복구 가능
Database 유형 - 계층형 데이터베이스
트리 형태의 계층적 구조를 가진 데이터베이스로 최상위 계층의 데이터부터 검색하는 구조 파일시스템 보다는 체계적으로 구성되었지만, 데이터의 중복과 병행성의 문제로 폐기 되었다.
Database 유형 - 네트워크형 데이터베이스
하위 데이터들 간의 관계까지 정의할 수 있는 구조로 데이터의 중복이 없어 무결성을 유지 할 수 있으며, 병행성 문제도 해결 되었지만 DB의 설계 및 구현이 복잡하고 어려워 역시 폐기되었다.
Database 유형 - 관계형 데이터베이스(Relational Database)
RDB는 키와 값들의 간단한 관계를 테이블(행렬구조, 2차원 배열)화 시킨 매우 간단한 원칙의 DB이다.
모든 데이터를 2차원 테이블 형태로 표현하고 테이블 사이의 비즈니스적 관계를 도출하여 연동할 수 있으며, 데이터의 중복을 최소화할 수 있으며 업무 변화에 대한 적응력 우수한 DB이다.
→ 1970년도에 개발되었지만, 2020년대 시점에도 가장 많이 활용하는 DB 고전 구조이다.
특징
| 구분 | 특징 |
|---|---|
| 데이터 구조 | 데이터를 테이블(Table, Relation) 형태로 관리하며, 행(Row, Tuple)과 열(Column, Attribute)로 표현 |
| 데이터 무결성 | - 개체 무결성(Primary Key): 각 행을 고유하게 식별 - 참조 무결성(Foreign Key): 테이블 간 참조 일관성 보장 - 도메인 무결성: 컬럼 값의 유효 범위 보장 |
| 데이터 독립성 | - 논리적/물리적 데이터 독립성 보장 - 스키마 변경 시 응용 프로그램 영향 최소화 |
| 데이터 조작 | - SQL(Structured Query Language) 지원 - 데이터 정의(DDL), 조작(DML), 제어(DCL) 가능 |
| 관계 연산 | 관계 대수(Relational Algebra)와 관계 해석(Relational Calculus)에 기반한 연산 수행 |
| 데이터 중복 최소화 | - **정규화(Normalization)**를 통해 데이터 중복 제거 - 이상(Anomaly) 현상 방지 |
| 동시성 제어 | - 다중 사용자 환경에서 트랜잭션 지원 - ACID (Atomicity, Consistency, Isolation, Durability) 보장 |
| 보안 및 권한 관리 | - 사용자 권한 부여/회수 가능 - 뷰(View)를 통한 접근 제어 및 보안 강화 |
Database 유형 - 객체-관계형 데이터베이스(ORDB)
관계형 데이터베이스(RDBMS)에 객체지향 데이터베이스(OODBMS)의 개념을 결합한 형태 즉, 관계형 모델을 기반으로 하되 객체지향의 특징(상속, 다형성, 사용자 정의 타입 등)을 지원하는 DBMS
→ 현대에서는 RDB의 진보된 버전이 ORDB를 RDB와 동일하게 취급한다.
- 사용자 정의 타입 지원
사용자가 임의로 정한 데이터 유형으로 기본형을 넘어 다양한 형태의 데이터를 다룰 수 있음 - 참조(reference)타입 지원
객체들로 이루어진 객체 테이블의 경우 하나의 레코드가 다른 레코드를 참조할 수 있는 것 - 중첩 테이블 지원
테이블을 구성하는 로우(row)자체가 또 다른 테이블로 구성되는 테이블을 지원하며 조금 더 복잡하고 복합적인 정보 표현 가능 - 대단위 객체의 저장 및 추출 가능
이미지, 오디오, 비디오 등 저장하기 위한 대단위 객체(LOB) 지원 - 객체 간의 상속 관계 지원
오라클의 경우 OBJECT 타입을 지원함으로써 상속 기능을 구현하고 있음
Database 유형- NoSQL
NoSQL은 전통적인 관계형 데이터베이스(RDBMS)와 달리, SQL과 같은 구조적 질의 언어를 사용하지 않고, 주로 프로그래밍 언어의 라이브러리를 통해 데이터에 접근하는 방식의 DBMS이다.
NoSQL은 고정된 테이블 스키마 없이 데이터를 저장하며, 일반적으로 Join 연산을 지원하지 않으며, 수평적 확장(Scale-out)에 적합하도록 설계되어 있다.
이러한 구조는 성능 측면에서 유리하며, 시스템 구조가 단순하고 유연하여 최근에는 고성능이 요구되는 시스템이나 대규모 데이터 분석 환경에서 널리 활용되고 있다.
종류
| 유형 | 개념 | 주요 특징 | 대표 DBMS |
|---|---|---|---|
| Document Store (문서 저장소) | JSON, BSON, XML과 같은 문서 단위로 데이터를 저장 | - 유연하고 중첩된 스키마 지원 - REST API 친화적 - 문서 기반 쿼리 가능 | MongoDB, CouchDB, Amazon DocumentDB |
| Key-Value Store (키-값 저장소) | 하나의 키에 하나의 값을 매핑하는 단순 구조 | - 매우 빠른 응답 속도 - 캐시/세션 관리에 적합 - 검색은 키 기반으로만 가능 | Redis, DynamoDB, Riak, Memcached |
| Column Store (컬럼 기반 저장소) | 행(Row)마다 서로 다른 컬럼 구조를 가질 수 있는 테이블 형태 | - 수천 개 컬럼 지원 - 대규모 분산 처리에 최적화 - 실시간 분석에 강함 | Cassandra, HBase, ScyllaDB |
| Graph Store (그래프 저장소) | **노드(Node)**와 **관계(Edge)**로 데이터를 표현 | - 관계 중심 데이터 관리에 적합 - 연결 쿼리에 최적화 - 소셜 네트워크 분석에 강력 | Neo4j, Amazon Neptune, ArangoDB |
PostgreSQL 개요
PostgreSQL의 개념 및 특징
PostgreSQL은 객체-관계형 데이터베이스(Object-Relational DBMS, ORDBMS)로 ANSI SQL 표준을 충실히 준수하는 오픈소스 DBMS이다. 1986년 미국 UC Berkeley에서 시작된 POSTGRES 프로젝트를 기반으로 발전하여, 1996년부터 PostgreSQL이라는 이름으로 배포되어 현재는 글로벌 커뮤니티 주도로 활발히 개발되며, 상용 DBMS에 버금가는 안정성과 기능을 제공한다.
PostgreSQL 데이터 저장 구조
데이터 저장 구조는 Database → Schema → Table 순으로 계층화되어 관리되며, 데이터베이스는 물리적 단위, 스키마는 논리적 단위, 테이블은 논리적 구조이면서 물리적 저장 단위를 가진다.
→ 물리/논리적 저장 단위는 DB 설계에 있어 IO 및 검색 성능과 연관 있는 개념으로 매우 중요!
- Database(DB): 하나의 데이터 집합을 관리하는 물리적 단위로, 별도의 파일 집합으로 저장됨
- Schema: 데이터베이스 내부 객체를 그룹화하는 논리적 단위(네임스페이스)
- Table: 데이터를 행과 열로 표현하는 논리적 구조이면서 내부적으로는 파일에 저장되는 물리적 단위
PostgreSQL 아키텍처
PostgreSQL 아키텍처는 클라이언트 요청을 처리하는 백엔드 프로세스, 데이터를 관리하는 공유 메모리(Shared Buffer, WAL Buffer), 그리고 데이터 파일 및 WAL 로그 관리 프로세스로 구성된다.
쿼리 실행 시 클라이언트는 백엔드 프로세스를 통해 공유 메모리에 접근하며, WAL Writer, Background Writer, Checkpointer 같은 백그라운드 프로세스가 데이터 파일과 로그를 동기화한다.
또한 Autovacuum 프로세스가 주기적으로 불필요한 데이터를 정리하여 성능과 저장 공간을 최적화한다.
PostgreSQL 쿼리 실행 단계별 개념
SQL 문장은 Parser에서 구문 분석된 후, Analyzer/Rewriter를 거쳐 최종 Query Tree로 변환된다.
이후 Optimizer가 통계 정보를 기반으로 최적의 실행 계획을 수립한다. 마지막으로 Executor가 실행 계획을 수행하여 스토리지 엔진에서 데이터를 가져와 결과를 반환한다.
쿼리 실행 절차
| 단계 | 개념 |
|---|---|
| Parser (파서) | SQL 문장을 구문 분석하여 Parse Tree 생성, 문법 오류 검사 |
| Analyzer (분석기) | Parse Tree를 실제 DB 객체와 매핑하여 Query Tree 생성 |
| Rewriter (리라이터) | 뷰(View), 규칙(Rules) 등을 적용해 쿼리 재작성 |
| Optimizer (옵티마이저) | 여러 실행 비용을 계산하고 최적의 실행 계획(Plan Tree) 생성 |
| Executor (실행기) | 실행 계획에 따라 실제 데이터를 읽고 최종 결과 반환 |
PostgreSQL 접근 도구 – DB Ever
DBEver(DBeaver)는 다양한 데이터베이스를 지원하는 범용 데이터베이스 관리 툴이다.
오픈소스 기반으로 MySQL, PostgreSQL, Oracle, SQL Server 등 대부분의 DBMS를 연결해 쿼리, 설계, 관리 작업을 수행할 수 있는 도구이다. GUI 환경에서 SQL 실행, 데이터 시각화, ERD 생성 등을 제공하여 개발자와 DBA 모두가
활용할 수 있는 통합 도구이다.
→ 관리 되는 DB가 다양한 종류일 때 자주 활용되며, 고전적으로 가장 많이 활용되던 DB 도구
PostgreSQL 접근 도구 – DataGrip
DataGrip은 JetBrains에서 개발한 상용 데이터베이스 통합 개발 환경(IDE)이다. PostgreSQL, MySQL, Oracle, SQL Server 등 다양한 DBMS를 지원하며 SQL 작성, 실행, 디버깅 기능을 제공하는 도구이다. 스마트 코드 자동완성,
스키마 탐색, 쿼리 최적화 기능을 지원하여 개발자와 DBA의 생산성을 높이는 전문 IDE이다.
→ 가장 좋지만, 유료인 도구로 회사에서 실제 사용은 제한적일 수도 있음.
psql (PostgreSQL interactive terminal)
psql은 PostgreSQL에서 제공하는 대화형 터미널 기반 클라이언트 도구이다. 데이터베이스에 접속하여 SQL 명령을
실행하고, 테이블· 스키마· 사용자 등 메타데이터를 조회할 수 있는 관리 툴이다. 쿼리 실행, 결과 저장, 스크립트
실행 등을 지원한다.
→ 터미널 기반으로 DB 접근이 필요할 때 활용되는 도구, 생각보다 자주 사용된다.
psql 주요 명령어
| 명령어 | 설명 |
|---|---|
\l | 전체 데이터베이스 목록 보기 |
\c dbname | 다른 데이터베이스로 접속 (connect) |
\dt | 현재 스키마의 테이블 목록 보기 |
\d 테이블명 | 특정 테이블의 구조(DDL) 확인 |
\du | 사용자(roles) 목록 보기 |
\dn | 스키마 목록 보기 |
\di | 인덱스 목록 보기 |
\dv | 뷰(View) 목록 보기 |
\g | 마지막 쿼리 다시 실행 |
\q | psql 종료 |
SQL의 개념과 필요성
SQL(Structured Query Language)의 개념
SQL은 관계형 데이터베이스에서 데이터를 조회하거나 정의 및 조작하기 위해 사용하는 표준 언어이다.
선언형 고급 언어(Declarative High-Level Language)로 고급 언어(Java, C++, C)보다 더 높은 단계의 언어로 데이터가 무엇을 할지(What) 선언하면, 어떻게 처리할지(How)는 DBMS가 알아서 처리한다.
→ SQL은 DBMS 브랜드별로 방언 문법이 있으나 ANSI 표준 SQL은 모든 DBMS에서 동작한다.
| 종류 | 설명 |
|---|---|
| ANSI 표준 SQL | ANSI에서 정의한 기본 SQL 문법으로, 모든 DBMS에서 기본적으로 지원 → SELECT, INSERT, JOIN 등 데이터 조작 중심 |
| PL/SQL (Oracle, PostgreSQL, Tibero) | 표준 SQL에 조건문, 반복문, 예외 처리 등을 추가한 절차적 확장 SQL → BEGIN...END, LOOP, EXCEPTION 구문 사용 가능 |
| T-SQL (MS SQL Server, Azure SQL) | 표준 SQL에 시스템 함수, 오류 처리, 트랜잭션 제어 기능 추가 → TOP, TRY...CATCH, ROW_NUMBER() 등 제공 |
| MySQL SQL (MariaDB, Amazon Aurora) | 표준 SQL 기반이지만 MySQL에 최적화된 고유 문법 포함 → LIMIT, AUTO_INCREMENT, 일부 함수/구문 차별화 비교적 단순하고 가벼움 |
SQL의 표준화(Standardization)와 방언(Dialects)
표준 SQL : SQL은 관계형 데이터베이스를 다루기 위해 국제 표준 기구(ANSI, ISO)에서 정의한 표준 SQL
SQL 방언 : 각 DBMS 벤더(Oracle, MySQL, PostgreSQL, SQL Server 등)가 표준 SQL을 기반으로 확장/변형한 문법
벤더 별 방언(Dialects) 예시
| DBMS | 주요 방언 예시 |
|---|---|
| Oracle | ROWNUM, NVL(), CONNECT BY, SEQUENCE |
| MySQL | LIMIT, AUTO_INCREMENT, IFNULL(), ENUM |
| PostgreSQL | SERIAL, RETURNING, JSON/JSONB, ILIKE |
| SQL Server | TOP, IDENTITY, ISNULL() |
PostgreSQL의 방언
| 구분 | PostgreSQL 방언 | 기능 설명 |
|---|---|---|
| 자동 증가 | SERIAL, BIGSERIAL | 자동 증가 정수 컬럼 정의 (표준 SQL의 IDENTITY와 유사) |
| 행 제한 (페이징) | LIMIT … OFFSET | 결과 집합에서 행 개수 제한 및 시작 위치 지정 (표준 SQL은 FETCH FIRST n ROWS ONLY) |
| 데이터 반환 | RETURNING | INSERT/UPDATE/DELETE 후 변경된 행을 바로 반환 |
| JSON 지원 | JSON, JSONB 타입 | JSON 데이터를 구조적으로 저장 및 처리, ->, ->> 연산자로 접근 가능 |
| 문자열 함수 | ILIKE | 대소문자 구분 없는 문자열 비교 (표준 SQL은 LIKE) |
| 배열 타입 | ARRAY | 하나의 컬럼에 배열 형태 데이터 저장 가능 |
| UPSERT | INSERT … ON CONFLICT DO UPDATE | 중복 키 발생 시 업데이트 수행 (표준 SQL에는 없음) |
| 재귀 쿼리 | WITH RECURSIVE | 계층적 데이터 조회 지원 |
| 타입 확장 | 사용자 정의 타입 (UDT) | 개발자가 직접 새로운 데이터 타입 정의 가능 |
| 함수/스토어드 | PL/pgSQL | SQL 확장 프로시저 언어, 트리거·함수 작성 가능 |
SQL 문장의 유형
SQL 문장은 데이터베이스에서 데이터 정의, 조작, 제어, 질의, 트랜잭션 관리를 위해 사용하는 명령어 집합이다. 크게 DDL, DML, DCL, TCL, DQL 다섯 가지 유형으로 분류된다.
| 분류 | 용도 | 명령어 |
|---|---|---|
| DQL (Data Query Language) | 데이터 검색 | SELECT |
| DML (Data Manipulation Language) | 데이터 조작 | INSERT, UPDATE, DELETE |
| DDL (Data Definition Language) | 데이터 정의 | CREATE, DROP, ALTER, TRUNCATE |
| DCL (Data Control Language) | 데이터 제어 | GRANT, REVOKE |
| TCL (Transaction Control Language) | 트랜잭션 제어 | COMMIT, ROLLBACK |
DML(Data Manipulation Language)
DML은 데이터베이스에 저장된 데이터를 조회, 추가, 수정, 삭제하기 위한 언어이다.
데이터를 조작하여 응용 프로그램과 데이터베이스 간 상호작용을 가능하게 하는 가장 기본적인 언어
| 명령어 | 설명 | 예시 |
|---|---|---|
| SELECT | 테이블의 데이터를 조회 | sql SELECT name, age FROM users WHERE age > 20; |
| INSERT | 새로운 데이터를 테이블에 삽입 | sql INSERT INTO users (id, name, age) VALUES (1, '홍길동', 30); |
| UPDATE | 기존 데이터를 수정 | sql UPDATE users SET age = 31 WHERE id = 1; |
| DELETE | 기존 데이터를 삭제 | sql DELETE FROM users WHERE id = 1; |
DDL(Data Definition Language)
| 구분 | 명령어 | 설명 |
|---|---|---|
| 생성 | CREATE | 데이터베이스 객체(테이블, 뷰, 인덱스 등)를 생성 |
| 변경 | ALTER | 기존 객체의 구조 변경 |
| 삭제 | DROP | 데이터베이스 객체 삭제 |
| 초기화 | TRUNCATE | 테이블의 모든 데이터 삭제 (구조는 유지) → DELETE보다 빠르지만 복원 불가 |
DCL (Data Control Language)
DCL은 데이터베이스 사용자에게 권한을 부여하거나 취소하여 보안을 관리하는 언어이다.
데이터 접근 제어와 사용자 관리에 사용된다.
| 명령어 | 설명 |
|---|---|
| GRANT | 사용자에게 특정 권한을 부여 |
| REVOKE | 사용자에게 부여된 권한을 회수 |
TCL (Transaction Control Language)
TCL은 트랜잭션 단위로 데이터의 일관성과 무결성을 유지하기 위해 실행 흐름을 제어하는 언어이다.
변경 내용을 확정하거나 취소하고, 부분 저장을 통해 안정적으로 데이터 처리를 관리한다.
| 명령어 | 설명 |
|---|---|
| COMMIT | 트랜잭션의 변경 사항을 최종 반영 |
| ROLLBACK | 트랜잭션의 변경 사항을 취소 |
| SAVEPOINT | 트랜잭션 내 특정 지점을 저장 |
| SET TRANSACTION | 트랜잭션 속성(예: 격리 수준 등) 설정 |
SQL 구문의 기본 구조
SQL 구문의 기본 구조 (Basic Structure)
SQL 구문의 기본 구조는 SELECT ... FROM ... 형태로 데이터를 어떤 칼럼에서, 어느 테이블에서 가져올지를 정의하는 것에서 시작한다. 이후 WHERE, JOIN, GROUP BY, HAVING 등을 이용해 조건을 걸거나 데이터를 묶어 원하는 결과를 선별한다. 마지막으로 ORDER BY, LIMIT 등을 통해 출력 순서와 개수를 제어할 수 있다.
| 구문 | 역할 |
|---|---|
| FROM | 조회할 테이블 지정 |
| JOIN ... ON | 여러 테이블을 결합 |
| WHERE | 행(Row) 단위 조건 필터링 |
| GROUP BY | 특정 컬럼 기준으로 그룹화 |
| HAVING | 그룹화된 결과에 조건 필터링 |
| SELECT | 출력할 컬럼 지정 |
| ORDER BY | 결과를 정렬 |
| LIMIT | 출력할 행(Row)의 개수 제한 |
FROM → WHERE → JOIN → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
SQL 키워드와 식별자 (Keywords & Identifiers)
- 키워드(Keyword): SELECT, FROM, WHERE, INSERT, UPDATE 같은 예약어
→ PostgreSQL은 대소문자 구분을 하지 않지만, 가독성을 위해 보통 대문자로 작성한다. - 식별자(Identifier): 테이블명, 컬럼명, 스키마명 등 사용자가 정의하는 이름, 소문자 작성을 권장한다.
→ PostgreSQL은 식별자를 모두 기본적으로 소문자로 저장 된다. (OS 별 대소문자 이슈 방지)
→ 만일, 대문자를 그대로 쓰고 싶으면 큰따옴표(")로 감싸야 한다. (권장 안함)
CREATE TABLE "Employees" (employee_id SERIAL, "FirstName" VARCHAR(50));
INSERT INTO "Employees" (employee_id, "FirstName") VALUES (100, 'John');
SELECT "FirstName" FROM "Employees"; -- "를 쓰면 대소문자 엄격 구분
SELECT first_name FROM employees; -- 에러 발생!!데이터 타입 이해 (Data Types)
데이터 타입(Data Types)은 데이터베이스에서 저장되는 값의 종류와 형식을 정의하는 규칙이다.
숫자·문자·날짜·논리값 등 데이터를 어떤 범위와 제약 조건으로 다룰지를 지정하는 기본 단위를 의미
| 분류 | 데이터 타입 | 설명 |
|---|---|---|
| 숫자형 (Numeric) | SMALLINT, INTEGER, BIGINT | 정수형 (2바이트, 4바이트, 8바이트) |
NUMERIC(p,s), DECIMAL(p,s) | 고정 소수점, 정확한 계산 가능 | |
REAL, DOUBLE PRECISION | 부동 소수점, 근사치 계산 | |
| 문자형 (Character) | CHAR(n) | 고정 길이 문자 |
VARCHAR(n) | 가변 길이 문자 (길이 제한 있음) | |
TEXT | 가변 길이 문자 (제한 없음) | |
| 날짜/시간형 (Date/Time) | DATE | 날짜 (YYYY-MM-DD) |
TIME | 시간 (HH:MI:SS) | |
TIMESTAMP | 날짜 + 시간 | |
INTERVAL | 기간, 시간 간격 | |
| 불리언 (Boolean) | BOOLEAN | TRUE, FALSE, NULL 값 가능 |
| 자동 증가형 | SERIAL, BIGSERIAL | 자동 증가 정수 (PK에 자주 사용) |
| 고유 확장 타입 (PostgreSQL) | JSON, JSONB | JSON 데이터 저장 및 처리 |
ARRAY | 배열 형태 데이터 저장 | |
UUID | 범용 고유 식별자 (Universally Unique Identifier) |
주석 작성법
한 줄 주석 (Single-line comment) : -- 뒤에 오는 내용은 같은 줄에서 모두 주석 처리됨
여러 줄 주석 (Multi-line comment) : /* */ 사이에 있는 내용은 여러 줄이라도 모두 주석 처리됨
-- 한 줄 주석 (ANSI 표준)
SELECT * FROM users; -- 사용자 전체 조회
/* 여러 줄 주석 */
SELECT name, age
FROM users
WHERE age > 20;