251022 [ Day 71 ] - SQL (1)

시작하며

오늘부터 새로운 파트인 MySQL에 대해서 수업을 시작했다. 저번 프로젝트를 통해 SQLite는 어느 정도 다뤄봤지만, 조금 더 본격적으로 SQL에 대해서 공부해보고 싶었는데 마침 좋은 타이밍에 수업을 시작했다.

데이터베이스

데이터, 정보, 지식

데이터가 모이면 정보가 되고, 정보가 모이면 지식이 됨

• 여러 사람에 의해 공유되어 사용될 목적으로 통합하여 관리되는 데이터의 집합
• 다량의 데이터를 효율적으로 저장 , 검색 , 수정 , 삭제할 수 있도록 설계된 구조
• 데이터
  • 관찰의 결과로 나타난 정량적 혹은 정성적인 값
• 정보
  • 유의미하게 가공한 데이터
• 지식
  • 가치 있는 정보

데이터베이스 정의

• 통합된 데이터
  • 데이터베이스는 중복을 최소화하고 정합성을 유지 하도록 데이터를 통합하여 저장
  • 정합성: 데이터가 논리적으로 일관성을 유지하고 , 신뢰할 수 있는 상태
• 저장된 데이터
  • 데이터베이스는 컴퓨터의 저장장치 (디스크 , SSD 등)에 물리적으로 저장되는 데이터를 의미
• 운영 데이터
  • 기업이나 조직의 업무 수행을 위해 생성 및 사용되는 실시간 데이터
• 공용 데이터
  • 하나의 데이터베이스를 여러 사용자가 공유하여 사용할 수 있는 데이터

데이터베이스 특징

• 실시간 접근성
  • 데이터는 사용자가 필요할 때 즉시 접근하여 사용할 수 있어야 함
• 계속적인 변화
  • 데이터는 지속적으로 변경되고 갱신되며 , 최신 상태를 유지
• 동시 공유
  • 데이터베이스는 여러 사용자가 동시에 같은 데이터를 공유하고 사용
• 내용에 따른 참조
  • 데이터는 고유한 값(내용 )으로 식별되며 , 위치가 아닌 값에 따라 참조

파일 시스템 VS 데이터베이스

파일 시스템 특징

• 데이터를 기록하고 여러 사람이 공유하여 사용 가능
• 파일 시스템의 단점
  • 데이터 중복이 생길 수 있음
  • 데이터 공유에 있어서 제한이 존재
  • 구현과 유지보수에 시간이 오래 걸림

데이터베이스 특징

• 파일 시스템의 단점을 개선하기 위해 등장
• 데이터베이스의 장점
  • 데이터 일치성 증가
  • 데이터 공유 원활
  • 의도치 않은 데이터 중복 없음
  • 데이터 보안 향상

DBMS

• DBMS(데이터베이스 관리 시스템 , Database Management System)
  • 데이터베이스를 생성 , 관리 , 조작 및 공유할 수 있도록 해주는 소프트웨어 시스템

DBMS 언어

• SQL(Structured Query Language)
  • 관계형 데이터베이스(RDBMS)에서 데이터를 정의 , 조작 , 제어하기 위한 표준 언어
  • 표준 SQL(Standard SQL) : 모든 RDBMS 에서 공통적으로 사용할 수 있도록 정해진 SQL 표준 규격
• 주요 구성 요소
  • 데이터 정의어 (DDL) : 데이터베이스의 구조(스키마)를 정의하는 명령어
  • 데이터 조작어 (DML) : 테이블의 데이터를 조작하는 명령어
  • 데이터 제어어 (DCL) : 데이터베이스의 접근 권한을 관리하는 명령어

RDBMS

• 관계형 데이터베이스 (Relational Database Management System)
  • 테이블(Table) 기반의 데이터 저장 구조를 사용하는 데이터베이스

주요 특징

• 테이블 기반 저장 : 데이터를 행(Row) 과 열(Column) 로 구성된 테이블에 저장
• SQL 사용 : 표준화된 SQL로 데이터 조작
• 관계(Relation) 지원 : 여러 테이블 간의 관계를 통해 데이터 조회 가능
• ACID 원칙 준수 : 트랜잭션의 안정성과 데이터 정합성을 유지
• 정해진 스키마 필요 : 데이터 구조를 사전에 정의해야 함

장단점

• 장점
  • 데이터 무결성과 정합성이 뛰어남
  • 표준 SQL을 사용하여 데이터 관리가 용이
  • 관계형 모델로 데이터 간 참조가 쉬움
• 단점
  • 대용량 데이터 처리에서 성능이 저하될 수 있음
  • 확장성이 제한적(수직 확장 중심 )
  • 사전 정의된 스키마가 필요하여 유연성이 떨어짐

NoSQL

• 비관계형 데이터베이스 NoSQL(Not Only SQL
  • 비정형 또는 반정형 데이터를 저장하기 위해 설계된 데이터베이스 시스템
  • 비정형 데이터 : 일정한 구조나 형식 없이 저장된 데이터
  • 반정형 데이터 : 데이터의 일부에 일정한 규칙성이나 형식이 존재하지만, 완전히 고정된 스키마(구조)를 따르지 않는 데이터

특징

• 스키마가 없음 → 데이터 구조가 유연하며 , 동적으로 변경 가능
• 수평 확장(Scale- out) 가능 → 서버를 추가하여 성능 확장 가능
• 다양한 데이터 모델 지원 → 문서 (Document), 키- 값(Key- Value), 그래프 (Graph) 등
• 트랜잭션 지원이 제한적 → 일부 NoSQL 시스템은 ACID를 완벽히 지원하지 않음
• 빅데이터 및 실시간 처리에 최적화 → 빠른 데이터 삽입 및 검색 가능

주요 유형 및 시스템

• 키-값 저장소 : 간단한 키(Key)와 값(Value) 쌍으로 데이터를 저장Redis, DynamoDB, Riak)
• 문서 저장소 : JSON, BSON 등 문서 형식으로 데이터 저장 (MongoDB, CouchDB)
• 열 저장소 : 특정 컬럼 단위로 데이터를 저장하여 빠른 검색 지원(Cassandra, HBase)
• 그래프 저장소 : 노드와 엣지의 관계를 기반으로 데이터 저장(Neo4j, ArangoDB)

장단점

• 장점
  • 데이터 구조가 유연하여 변경이 쉬움
  • 수평 확장(Scale - out)이 가능하여 대량의 데이터를 효과적으로 처리
  • 특정한 사용 사례(빅데이터 , 실시간 데이터 처리 )에 강점
• 단점
  • SQL과 같은 표준화된 쿼리 언어가 없음
  • 데이터 무결성(Integrity) 을 보장하는 기능이 부족할 수 있음
  • 관계형 데이터베이스처럼 정형화된 데이터 처리에는 부적합

RDBMS vs NoSQL 비교

• RDBMS는 정형 데이터와 높은 정합성이 필요한 경우에 적합
• NoSQL은 대량의 비정형 데이터를 빠르게 처리하고 확장성이 필요한 경우에 적합

ACID

트랜잭션

• 데이터베이스에서 하나의 작업 단위를 구성하는 연산들의 집합
• 모든 작업들이 성공적으로 수행되어야 트랜잭션이 성공 된 것
  • 중간에 하나라도 실패하게 되면 트랜잭션도 실패

ACID 원칙

• 원자성(Atomicity): 전부 수행되거나 전혀 수행되지 않아야 함
• 일관성(Consistency): 트랜잭션 전후의 데이터가 일관된 상태 유지
• 격리성(Isolation): 다른 트랜잭션 간 간섭 없이 독립적으로 실행
• 지속성(Durability): 완료된 트랜잭션의 결과는 영구히 저장됨

데이터 모델링

• 현실 세계의 데이터를 구조적으로 표현하는 과정
  • 효율적인 데이터 저장과 관리 , 데이터 일관성 유지 , 개발 및 유지보수 비용 절감 가능

데이터 모델링 과정

• 요구사항 분석
• 개념적 데이터 모델 설계
• 논리적 데이터 모델 설계
• 물리적 데이터 모델 설계

개념 모델(Conceptual Model)

• 현실 세계의 비즈니스 구조를 개념적으로 표현한 모델

논리 모델(Logical Model)

• 개념 모델을 바탕으로 데이터를 더 구체적으로 정리하여 테이블 형태로 구조화하는 과정
• 데이터의 속성과 관계를 정의하고, 데이터를 효율적으로 저장할 수 있도록 정리

물리 모델(Physical Model)

• 논리 모델을 기반으로 실제 데이터베이스에서 구현할 수 있는 형태로 변환한 모델

ERD

ER 모델(Entity- Relationship Model)

• 현실 세계의 데이터를 개념적으로 표현하는 모델
• 데이터를 엔티티(Entity), 속성(Attribute), 관계(Relationship)로 정의하여 데이터베이스 설계의 기초를 마련
  • ER 모델을 기반으로 ERD(Entity- Relationship Diagram) 을 그려 데이터 간의 관계를 쉽게 이해할 수 있도록 시각화

구성 요소

• 엔티티(Entity) : 현실 세계에서 고유하게 존재하는 데이터의 집합, 객체
• 속성(Attribute) : 엔터티가 가지는 데이터의 특징
• 관계(Relationship) : 두 개 이상의 엔티티 간의 연관성을 표현하는 개념

ERD(Entity -Relationship Diagram))

• ERD는 ER 모델을 시각적으로 표현한 다이어그램
  • 데이터베이스 설계 과정에서 가장 중요한 단계
• 다량의 데이터를 효율적으로 저장, 검색, 수정, 삭제할 수 있도록 설계된 구조

개체 타입(Entity Type))

• Entity Type을 ER 다이어그램으로 나타낼 때 직사각형 사용

이름 규칙

• 단수형으로 작성 : Customer (O) / Customers (X
• 명확한 의미를 가지도록 작성 : User (X) → Customer (O)
• 약어 사용을 지양하고, 의미가 명확한 이름 사용 : Emp (X) → Employee (O)
• 네이밍 컨벤션(PascalCase, snake_case )을 일관되게 유지
• 유일해야 함 : 하나의 ER Model에 같은 이름의 Entity Type이 존재할 수 없음

속성 타입(Attribute)

• Attribute를 ER 다이어그램으로 나타낼 때 타원 사용
• Entity Type(직사각형 )과 실선으로 연결

속성 규칙

• 단순 명사 사용
• 하나의 Entity Type에 같은 Attribute 불가능

키 속성(Key Attribute)

• 다른 객체들과 중복되지 않는 고유한 값을 갖는 Attribute
• 밑줄 표시

속성 유형

• 단순 속성(Simple Attribute) : 더 이상 나눌 수 없는 속성
• 복합 속성(Composite Attribute) : 독립적인 의미를 갖도록 나눌 수 있는 속성
• 단일 값 속성(Singled- valued Attribute) : 하나의 값만 갖는 속성
• 다중 값 속성(Multi- valued Attribute) : 여러 값을 갖는 속성
  • 타원 2개 사용
• 유도 속성(Derived Attribute) : 다른 속성을 기반으로 계산되거나 유도될 수 있는 속성
  • 예를 들어, 직원의 나이는 주민번호로부터 계산

관계 타입(Relation Type)

• Relation Type을 ER 다이어그램으로 나타낼 때 마름모 사용
• Relation Type은 관계를 나타내므로 문장 정의할 때 동사를 사용

ER 모델 제약조건(Constraints)

• ER 모델에서는 데이터의 무결성과 일관성을 유지하기 위해 제약 조건을 적용
• 데이터가 올바른 형식으로 저장되고 , 잘못된 데이터가 입력되지 않도록 방지
  • 데이터 무결성(Data Integrity) : 데이터의 정확성 , 일관성 , 신뢰성을 유지하여 원래의 의미와 상태가 훼손되지 않도록 보장하는 원칙

제약 조건 종류

• 키(Key) 제약 조건 : 각 엔터티는 유일한 식별자(PK)를 가져야 함
• 개체(Entity) 무결성 제약 조건 : 기본 키(PK)는 NULL 값을 가질 수 없음
• 참조(Referential) 무결성 제약 조건 : 외래 키(FK)는 유효한 기본 키(PK)만 참조해야 함
• 도메인(Domain) 제약 조건 : 속성 값은 미리 정의된 값의 범위 내에 있어야 함
• 카디널리티(Cardinality) 제약 조건 : 엔터티 간의 관계(1:1, 1:N, N:M)를 정의하는 제약
• 참여(Participation) 제약 조건 : 특정 관계에서 엔터티의 필수 또는 선택적 참여 여부

키의 제약 조건

• 키(Key)
  • 키는 각 엔터티를 고유하게 식별하는 속성
  • 동일한 값을 가질 수 없으며, 한 개 이상의 속성으로 구성될 수 있음
• 키의 유형
  • 기본 키(Primary Key, PK) : 한 엔터티 내에서 각 튜플(Row)을 유일하게 식별
  • 후보 키(Candidate Key) : 기본 키로 사용할 수 있는 속성들의 집합
  • 대체 키(Alternate Key) : 후보 키 중에서 기본 키로 선택되지 않은 속성
  • 슈퍼 키(Super Key) : 한 개 이상의 속성 조합으로 유일성을 만족하는 키
  • 외래 키(Foreign Key, FK) : 다른 엔터티의 기본 키를 참조하는 속성
• 키 제약 조건의 특징
  • 기본 키(PK)는 NULL 값을 가질 수 없음
  • 기본 키는 중복이 허용되지 않음
  • 외래 키(FK)는 참조하는 엔터티의 기본 키(PK) 값만 가질 수 있음

개체 무결성 제약 조건

• 모든 엔터티는 고유하게 식별 가능해야 한다는 제약
• 기본 키(PK)는 NULL 값이 될 수 없음

참조 무결성 제약 조건

• 외래 키(FK)는 반드시 참조하는 테이블의 기본 키(PK) 값을 가져야 한다는 제약
• 참조 대상이 없는 외래 키 값은 허용되지 않음
• 삭제 규칙(ON DELETE)
  • CASCADE : 부모 엔터티가 삭제되면, 자식 엔터티도 자동 삭제
  • SET NULL : 부모 엔터티가 삭제되면, 자식 엔터티의 FK 값을 NULL로 설정
  • RESTRICT : 부모 엔터티가 삭제될 경우, 참조하는 자식 엔터티가 있으면 삭제 불가능
• 수정 규칙(ON UPDATE)
  • CASCADE : 부모 엔터티의 PK가 변경되면, 자식 엔터티의 FK도 자동 변경
  • SET NULL : 부모 엔터티의 PK가 변경되면, 자식 엔터티의 FK 값을 NULL로 설정
  • RESTRICT : 부모 엔터티의 PK가 변경될 경우, 참조하는 자식 엔터티가 있으면 변경 불가능

도메인 제약 조건

• 속성(Attribute)에 저장될 수 있는 값의 범위를 제한

고유 무결성 제약 조건

• 특정 속성에 대해 고유한 값을 가지도록 조건이 주어진 경우 릴레이션의 각 튜플이 가지는 속성 값들은 서로 달라야 함

NULL 무결성 제약 조건

• 릴레이션의 특정 속성 값은 NULL이 될 수 없음

구조적 제약 조건

• 실선 위에 (min, max) 값을 표시하여, 구체적인 조건을 명시하는 제약조건

카디널리티 제약 조건

• 엔터티 간의 관계에서 각 엔터티가 가질 수 있는 데이터 개수(1:1, 1:N, N:M)를 정의하는 제약
• 잘못된 카디널리티 설정은 데이터 구조에 심각한 오류를 유발할 수 있음

일대일 관계

• 각 개체들이 1:1로 대응되는 관계

일대다, 다대일 관계

• 한 개체가 여러 개의 개체와 관계를 맺을 수 있으나, 역은 성립되지 않음

다대다 관계

• 한 개체가 여러개의 개체를 가질 수도 있고, 그 역도성립

참여 제약 조건

• 특정 엔터티가 반드시 특정 관계에 참여해야 하는지 여부를 결정
  • 전체 참여(Total Participation) : 모든 엔터티가 반드시 관계에 참여해야 함
  • 부분 참여(Partial Participation) : 일부 엔터티만 관계에 참여할 수 있음

관계의 존속성

약한 객체(약한 엔티티, Weak Entity)

• 자체적으로 고유한 기본 키(PK)를 가지지 못하고, 다른 엔티티에 의존해야만 존재할 수 있는 엔티티

약한 객체의 특징

• 고유 식별자(Primary Key)가 없음
  • 다른 엔터티의 기본 키 + 자신의 고유 속성으로 식별
• 식별 관계(Identifying Relationship)를 통해 강한 엔터티와 연결
  • 반드시 강한 엔터티(Strong Entity)의 도움을 받아야 식별 가능
• 관계의 참여 제약은 '전체 참여'가 강제됨
  • 약한 엔터티는 관계에 무조건 참여해야 하며, 강한 엔터티 없이는 존재할 수 없음

표현 방법

• 식별 관계 : 이중 마름모로 표현
• 약한 객체 : 이중 직사각형으로 표현
• 구별자 : 점선 밑줄로 표현

관계 데이터 모델로 사상

사상(Mapping)

• 개념적 모델(ER 모델)에서 논리적 모델(관계 데이터 모델)로 변환하는 과정

이진 1:1 관계 타입

• ER 모델에서 두 개의 엔티티 타입 사이에 정의된 관계

MySQL 설치 확인

# 버전 확인
mysql --version

# 접속 테스트
mysql -u root -p

# 종료
exit;

자료형(숫자형)

정수형

• TINYINT / 1 byte / -128 ~ 127 / 작은 정수
• SMALLINT / 2 byte / -32,768 ~ 32,767 / 비교적 작은 정수
• MEDIUMINT / 3 byte / -8백만 ~ / 중간 크기 정수
• INT, INTEGER / 4 byte / -21억 ~ 21억 / 일반적인 정수
• BIGINT / 8 byte / 매우 큰 정수 / 64비트 정수

실수형

• FLOAT / 단정도 실수 (정밀도 낮음)
• DOUBLE / 배정도 실수 (정밀도 높음)
• DECIMAL(p, s) / 고정 소수점 (정밀 계산에 적합, 금융 관련)

자료형(문자형)

• CHAR(n) / 고정 길이 문자열 / n바이트 항상 사용
• VARCHAR(n) / 가변 길이 문자열 / 실제 길이에 따라 저장
• TEXT / 최대 65,535자 저장 / 긴 텍스트 저장용
• ENUM(’A’, ‘B’) / 지정된 값 중 하나 / 선택지 저장에 적합
• SET(’A’, ’B’, ’C’) / 여러 개 선택 가능 / 복수 선택 가능

CHAR

• 장점
  • 고정 길이이므로 정렬, 인덱싱 속도가 빠름
  • 데이터 길이가 일정할 경우 성능이 안정적
• 단점
  • 길이가 짧은 데이터를 저장해도 공간 낭비
  • 저장 시 자동을 공백 패딩

VARCHAR

• 장점
  • 가변 길이 문저열로, 저장 공간을 효율적으로 사용
  • 다양한 길이의 데이터를 저장하기 적합
• 단점
  • 가변 길이로 인해 정렬, 검색 시 성능이 떨어질 수 있음
  • 길이에 따라 저장 구조가 달라지므로 내부 처리 복잡도 증가

자료형(날짜 및 시간형)

• DATE : 날짜 (YYYY-MM-DD)
• TIME : 시간 (HH:MM:SS)
• DATETIME : 날짜 + 시간 (YYYY-MM-DD HH:MM:SS)
• TIMESTAMP : UNIX 시간 기반 타임스탬프
• YEAR : 연도 (YYYY)

SQL

SQL(Structured Query Language)

• 관계형 데이터베이스에서 데이터를 정의, 조작, 제어하기 위한 표준 언어
• SQL : 비절차적인 언어
  • 비절차적 : 무엇을 할지(What)를 기술하고, 어떻게 처리할지는 시스템(DBMS)이 알아서 수행

SQL 구성 요소

• 데이터 정의어 (DDL) : 데이터베이스의 구조(스키마)를 정의하는 명령어
• 데이터 조작어 (DML) : 테이블의 데이터를 조작하는 명령어
• 데이터 제어어 (DCL) : 데이터베이스의 접근 권한을 관리하는 명령어

DDL

• DDL (Data Definition Language) : 데이터베이스의 구조를 정의하는 언어
• 주요 명령어
  • CREATE, ALTER, DROP, RENAME, TRUNCATE

CREATE 문

• 데이터베이스와 테이블을 생성하는 명령어
  • 테이블 이름, 열 이름, 데이터 형식 등을 지정
  • 기본키, 외래키 정의
• CHARSET (문자셋) : 어떤 문자를 어떻게 저장할 것인가를 정의하는 규칙
  • latin1 : 영어만 저장 가능 (1바이트)
  • utf8 : 대부분의 언어 저장 가능 (최대 3바이트)
  • utf8mb4 : 이모지까지 저장 가능 (최대 4바이트)
• COLLATE (정렬 규칙) : 문자를 어떻게 비교하고 정렬할지 정하는 규칙
  • utf8mb4_general_ci : 구분 안 함 (빠르고 단순)
  • utf8mb4_unicode_ci : 구분 안 함 (유니코드 규칙에 따라 더 정확하게 정렬)
  • utf8mb4_bin : 구분함 (대소문자, 공백, 특수문자까지 엄격 비교)

-- 데이터베이스 생성
CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 테이블 생성
CREATE TABLE user (
	user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	user_name VARCHAR(10) NOT NULL,
    address VARCHAR(45),
    join_date DATE
);

DROP 문

• 생성된 테이블 삭제 (테이블 구조와 데이터 모두 삭제)

TRUNCATE 문

• 데이터만 삭제, 구조는 유지 (로그를 남기지 않으며 빠르지만 복구 불가능)
  
  

마치며

SQL 첫날이었지만 짧은 기간에 많은 내용을 다뤄야 하다 보니 템포가 상당히 빨랐다. 초반 데이터베이스와 SQL에 대한 이론을 공부할 때는 낯선 내용들이라 이해하기도 힘들어서 돌아와서 계속 복습하면서 조금씩 이해해갔다. 하지만 MySQL Workbench에서 코드를 작성하며 내용을 공부하니까 확실히 재미있어서 더 이해가 잘된 것 같다.