Today I Learned

# 데이터 vs 정보

  • 데이터: 그 자체로는 의미 없는 기록 조각이다.
  • 구조화된 데이터: 수집 단계에서 최소한의 구조를 가지고 정리된 데이터
    • 각 데이터 조각에 레이블을 붙여주는 단계
    • 데이터에 의미는 있지만 통찰은 없는 상태
  • 정보: 구조화된 데이터를 기반으로 특정 질문이나 목적을 가지고 분석 및 가공하여 얻어낸 유의미한 결과물
    • 특정 날짜에 가장 많이 팔린 상품은 무엇이며, 총 몇 개나 팔렸는가?
    • 위 질문에 대해 필터링, 그룹화 및 집계가 이루어진다.
  • 데이터베이스는 위와 같이 데이터를 체계적으로 저장하고 원하는 조건에 맞춰 쉽고 빠르게 찾아내 정보로 만들 수 있게 해주는 도구이다.

# 데이터의 보관

  • 컴퓨터 파일 시스템을 이용한 데이터 관리
    1. txt: 데이터의 구조가 명확하지 않고, 특정 데이터 조회 및 수정이 어렵다.
    2. excel: 행렬 기반으로 구조화된 데이터, 조회 및 간단한 연산이 쉽다.
      • 규모가 커지고 데이터 복잡성이 커지기 시작하면 관리가 어려워진다.
  • 파일에 직접 저장 및 관리하는 경우 아래와 같은 문제들이 발생한다.
    1. 데이터 중복 / 불일치
      • 각자 관리하는 파일에서의 중복된 데이터 발생
      • 어떤 데이터가 실제 데이터인지 불일치하는 상황 발생
    2. 데이터 접근성 저하
      • 파편화된 파일들을 뒤져가며 의미있는 정보 추출을 해내야 함
    3. 데이터 무결성 제약조건 적용의 어려움
      • 데이터 오기입 문제를 강제로 막을 수가 없다.
    4. 동시성 제어 문제
      • 여러 고객이 동시에 특정 데이터 접근을 하는 경우 예상하지 못한 결과가 나타날 수 있다.
    5. 보안 문제
      • 파일 시스템 권한 설정만으로는 세밀한 데이터 접근 제어가 어렵다.
    6. 데이터 복구 및 백업의 어려움
      • 파일을 백업해두지 않은 경우 데이터 복구가 어렵다.

데이터 무결성 제약조건

  • 데이터에 잘못된 데이터가 저장되지 않도록 하는 규칙을 말한다.
  • 회원의 아이디는 중복되면 안된다, 주문 수량은 1개 이상이어야 한다 등

# DBMS

  • 위와 같은 파일 시스템 기반의 관리 시스템 한계들을 해결하기 위해 데이터베이스 관리 시스템이 등장하게 되었다.
  • 이를 DBMS라고 한다.
  • DBMS란 데이터베이스를 생성, 정의, 공유, 사용, 관리하는 기능을 수행하는 소프트웨어 시스템을 말한다.
    • 데이터베이스는 데이터를 체계적으로 모아놓은 창고이다.
    • DBMS는 창고를 효율적으로 관리하는 소프트웨어이다.
  • DBMS 특징
    1. DBMS는 데이터를 효율적으로 저장, 검색, 수정, 삭제할 수 있도록 하는 소프트웨어
    2. 데이터 저장: 운영체제 파일 시스템 위에 있는 특정 형식의 파일들에 저장한다.
    3. 추상화: DBMS 내부적으로 어떤 파일에 어떻게 저장하는지 알 필요가 없다.
      • SQL 기반으로 동작이 수행된다. 선언형 기반으로 동작한다.
    4. 직접 접근 금지: 내부 데이터 파일들은 사용자가 직접 열어보거나 수정하려 하면 안된다.
  • DBMS 역할 및 기능
    1. 데이터 정의 기능 (Data Definition Language - DDL)
      • DBMS는 데이터베이스 구조를 정의할 수 있는 기능을 제공한다.
      • 각 데이터는 어떤 형태가 될지, 데이터 간의 관계, 제약조건 등
    2. 데이터 조작 기능 (Data Manipulation Language - DML)
      • 데이터베이스 구조 정의가 되면 구조에 맞춰 데이터 등록 / 조회 / 수정 / 삭제를 할 수 있어야 한다.
      • DBMS는 데이터 조작을 위한 효율적인 방법을 제공한다.
      • SQL이라는 표준화된 언어를 사용한다.
    3. 보안, 동시성 제어, 트랜잭션 관리 기능
      • DBMS는 보안을 유지하고 여러 사용자가 동시에 데이터에 접근할 때 발생할 수 있는 문제를 제어하는 기능을 수행한다.
      • 보안: 허가된 사용자만이 데이터에 접근할 수 있게 한다. 사용자별로 접근 가능한 데이터 범위 제어가 가능
      • 동시성 제어: 여러 사용자가 동시에 같은 데이터를 수정하려 할때 락 메커니즘을 사용
      • 트랜잭션 관리
    4. 데이터 중복 최소화 및 일관성 유지
      • DBMS는 정규화 과정을 통해 데이터를 여러 테이블로 분리하여 저장하여, 불필요한 데이터 중복을 줄인다.
      • 정규화란, 데이터 중복을 제거하고, 이상현상(삽입/수정/삭제 오류)이 생기지 않도록 테이블을 올바르게 분리하는 과정을 말한다.
    5. 데이터 백업 및 복구
      • 시스템 장애나 실수로 인한 데이터 손상 및 유실에 대비하여 데이터를 주기적으로 백업하고, 문제 발생 시 안전하게 복구할 수 있는 기능을 제공한다.

트랜잭션

  • 여러 작업을 하나의 단위로 묶어, 전부 성공하거나 전부 실패하게 만드는 것을 의미한다.
  • 전부 성공적으로 완료되거나 (Commit)
  • 하나라도 실패하면 전부 이전 상태로 돌아감 (Rollback)
  • 위 두 동작이 잘 수행되어야만 데이터 일관성이 보장된다.
    • 이를 원자성이라고 한다.

# 관계형 DB vs NoSQL

  • 관계형 데이터베이스 관리 시스템 (RDBMS)
    • 가장 널리 사용되는 데이터베이스 시스템
    • 테이블 간의 관계를 기반으로 데이터 구조화
  • NoSQL(Not Only SQL) 데이터베이스 관리 시스템
    • 빅데이터와 특정 요구사항에 맞춰 최근 등장한 시스템

# RDBMS(Relational Database Management System)

  • 데이터를 테이블이라는 정형화된 구조에 저장한다.
  • RDBMS가 기본인 이유는 다음과 같다.
    1. 정형화된 데이터 관리
      • 데이터 구조가 명확
      • 데이터 타입 / 제약조건을 통한 데이터 무결성 보장
    2. ACID 트랜잭션 보장
      • ACID(원자성, 일관성, 고립성, 지속성) 특성을 잘 지원
      • 데이터 신뢰성
    3. SQL 기반 표준 질의어
    4. 성숙한 기술 / 풍부한 생태계
    5. 압도적인 사용 빈도

# RDBMS 종류

  1. Oracle
    • 장점
      • 강력하고, 기능이 많다.
      • 안정성이 좋다.
    • 단점
      • 라이선스 비용이 비싸다
      • 전문성이 요구된다
  2. MySQL
    • 장점
      • 가장 널리 사용되는 오픈소스 RDBMS
      • 빠른 속도, 사용 편의성
      • 운영체제 호환성, 커뮤니티 지원 등
    • 단점
      • 복잡한 분석 쿼리 / 대규모 트랜잭션 처리 성능은 Oracle이나 PostgreSQL에 비해 다소 부족하다는 평가
      • 격차는 줄여가고 있음
  3. PostgreSQL
    • 장점
      • 오픈소스 RDBMS
      • 복잡한 쿼리 처리, 표준 SQL, 확장 기능 등 기능적으로 뛰어남
    • 단점
      • MySQL에 비해서는 사용자가 적다
  4. H2 Database(SQLite)
    • 내장형 임베디드 DB로 사용됨
    • 애플리케이션 자체에 포함되어 실행되는 가벼운 데이터베이스

# NoSQL

  • 관계형 모델을 사용하지 않거나 SQL을 주된 데이터 접근 언어로 사용하지 않는 DBMS를 통칭
  • 매우 빠른 읽기 및 쓰기속도, 대량의 비정형 데이터, 유연한 데이터 모델 등에 대응하기 위한 목적
  • NoSQL 4대 분류
    1. 키값 저장소
      • 초고속 단건 읽기 및 쓰기
      • 세션, 캐시에 사용
      • Redis, Memcached
    2. 문서 DB
      • JSON/BSON 문서
      • 유연한 스키마
      • MongoDB
    3. 컬럼 패밀리 저장소
      • 열 그룹
      • 대용량 분산 및 분석
      • 로그 및 시계열
      • Cassandra, HBase
    4. 그래프 DB
      • 노드, 엣지
      • 복잡한 관계 분석

# RDBMS 핵심 개념

  • 테이블
    • 관계형 데이터베이스에서 데이터를 저장하는 가장 기본적인 구조
    • 특정 주제와 관련된 데이터들의 집합
  • 행
    • 테이블 각 가로줄
    • 하나의 행은 개별적인 데이터 항목
    • 레코드, 튜플이라고도 불림
  • 열
    • 어떤 종류의 데이터가 저장될지를 정의
    • 속성, 필드라고도 불림
  • Primary Key, 기본 키
    • 유일한 데이터 하나만을 식별하기 위해 PK, Primary Key라는 개념을 도입했다.
    • PK는 고유성, NOT NULL 규칙을 지켜야 한다.
      • 고유성: 같은 테이블 내에서 PK 열의 값은 중복되어서는 안된다.
      • NOT NULL: PK로 지정된 열에는 반드시 값이 있어야 한다.
  • Foreign Key, 외래 키
    • 외래 키란 한 테이블의 열이 다른 테이블의 PK 값을 참조하는 것을 말한다.
    • orders 테이블에서 customers 테이블의 customer_id 컬럼을 가져와 참조하는 구조를 예시로 들 수 있다.
    • 두 테이블이 FK값을 통해 한쪽을 부모, 한쪽을 자식이라 한다.
    • 자식 테이블은 FK를 통해 부모 테이블을 참조한다. FK값을 갖는 테이블이 자식이다.
    • FK는 반드시 참조 무결성(Referential Integrity) 규칙을 지켜야 한다.
      • 반드시 부모 테이블의 PK값 중 하나이거나 NULL이어야 한다.
      • 부모 테이블의 PK에 존재하지 않는 값을 억지로 넣게 되면 오류를 발생시킨다.

# 데이터베이스 및 테이블 생성

  • CREATE DATABASE 데이터베이스_이름: 데이터베이스 만들기 명령어
  • USE 데이터베이스_이름: 데이터베이스 선택 명령어
  • CREATE TABLE 테이블명 ( 컬럼 정의 )
    • 데이터베이스 테이블에는 반드시 PK가 있어야 한다.
    • VARCHAR 타입 지정 시 최대 글자 수도 지정해줘야 한다.
    • DATE타입은 YYYY-MM-DD 날짜 데이터를 저장할 때 사용한다.
CREATE TABLE sample (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price INT,
    stock_quantity INT,
    release_date DATE
)
  • DESCRIBE 테이블명: 테이블 구조 확인
    • DESC 테이블명도 동일하다.
  • SHOW DATABASES, SHOW TABLES: 데이터베이스 및 테이블 리스트 조회
    • SHOW TABLES는 USE 명령어를 통해 선택된 데이터베이스의 테이블만 조회한다.
  • DROP DATABASE 데이터베이스_이름, DROP TABLE 테이블명: 데이터베이스 및 테이블 내의 데이터와 함께 영구적으로 삭제한다.
    • 신중히 사용해야 한다.

# CRUD 기본

  • INSERT INTO 테이블 (컬럼명1, 컬럼명2, ...) VALUES (컬럼1_데이터, 컬럼2_데이터, ...)
    • 문자열과 날짜는 작은따옴표로 감싼다.
  • SELECT * FROM 테이블명
    • 테이블 내의 전체 데이터 조회
  • SELECT 컬럼명1, 컬럼명2 .. FROM 테이블명
    • 특정 컬럼만 조회
  • UPDATE: row 업데이트
UPDATE 테이블명
SET 컬럼명 = 새로운_컬럼_데이터
WHERE 조건
  • DELETE: 특정 행 삭제
DELETE FROM sample
WHERE 조건

SQL 작성시 대소문자 관례

  • 일반적으로 쿼리 키워드들은 대문자로, 직접 짓는 테이블 및 열 이름 등은 소문자로 작성하는 것이 관례이다.
  • 이름은 언더스코어 조합으로 사용한다.

# SQL이란

  • SQL은 국제 표준 기구에 의해 표준이 정해진 관계형 데이터베이스의 표준 언어이다.
    • 각 DBMS들은 자신만의 추가 기능이나 문법들을 제공하는데 이를 Dialect라고 한다.
  • SQL 모든 명령어는 목적과 기능에 따라 4가지 그룹으로 나눌 수 있다.
    1. DDL(Data Definition Language, 데이터 정의어)
      • 목적: 데이터 구조를 정의하고 관리하는 언어이다.
      • 주요 명령어
        • CREATE: 데이터베이스, 테이블 등의 구조를 생성한다.
        • ALTER: 이미 만들어진 테이블 구조를 변경한다.
        • DROP: 데이터베이스, 테이블을 완전히 삭제한다.
    2. DML(Data Manipulation Language, 데이터 조작어)
      • 목적: 테이블 안에 들어있는 실제 데이터를 직접 조작하는 언어이다. (CRUD)
      • 주요 명령어
        • INSERT: 테이블에 새로운 데이터를 추가한다.
        • SELECT: 테이블에서 데이터를 조회 / 검색한다.
        • UPDATE: 기존 데이터를 수정한다.
        • DELETE: 기존 데이터를 삭제한다.
    3. DCL(Data Control Language, 데이터 제어어)
      • 목적: 데이터에 대한 접근 권한을 부여하거나 회수하는 등의 보안 관련 권한을 제어한다.
      • 주요 명령어
        • GRANT: 특정 사용자에게 특정 작업에 대한 수행 권한을 부여한다.
        • REVOKE: 특정 사용자에게서 이미 부여한 권한을 회수한다.
    4. TCL(Transaction Control Language, 트랜잭션 제어어)
      • 목적: DML에 의해 수행된 데이터 변경 작업들을 트랜잭션 단위로 묶어 관리하는 언어이다.
      • 주요 명령어
        • COMMIT: 트랜잭션의 모든 작업을 최종적으로 데이터베이스에 확정 및 저장한다.
        • ROLLBACK: 트랜잭션의 모든 작업을 취소하고, 이전 상태로 되돌린다.

# 데이터 타입

  • 숫자 타입
    • INTEGER, INT, TINYINT, BIGINT 등 할당 바이트값에 따라 다양한 범위의 정수값을 저장할 수 있다.
    • FLOAT, DOUBLE 타입을 통해 부동소숫점 데이터 저장도 가능하다.
  • 문자열 타입
    • VARCHAR(n): 최대 n글자까지 저장되는 가변 길이 문자열 타입
      • 데이터 길이 확인을 위해 1~2바이트 사이의 길이 정보를 저장할 공간이 추가로 필요함
      • 최대 65,535자까지 저장 가능하다.
      • 대부분 첫 설계의 시작을 VARCHAR로 시작한다.
    • CHAR(n): 항상 n글자 길이를 차지하는 고정 길이 문자열
      • 입력 데이터가 선언된 길이보다 짧으면 나머지를 공백으로 채운다.
      • 최대 255자까지 저장 가능하다.
    • TEXT: 매우 긴 텍스트 저장 시 사용
  • 날짜 및 시간타입
    • DATE: YYYY-MM-DD 날짜 정보만 저장한다.
    • DATETIME: YYYY-MM-DD HH:MM:SS 시간 정보까지 저장한다.
    • TIMESTAMP: 현재 서버의 타임존을 기준으로 UTC로 변환하여 저장한다.
    • 현대에는 TIMESTAMP보다 DATETIME 사용을 권장한다.
      • TIMESTAMP는 2038년 까지의 데이터만 보관할 수 있는 문제
      • TIMESTAMP 행 생성 및 수정 시 자동으로 현재 시간을 기록해주는 기능이 DATETIME에도 지원된다.
      • 글로벌 환경에서 운영되는 경우 DATETIME에 데이터 보관시 UTC 시간으로 변환하여 보관하면 된다.
  • 기타 타입
    • BLOB: 이미지, 오디오, 비디오와 같은 이진 대용량 데이터 저장
    • ENUM: 단일 선택 타입
    • SET: 다중 선택 타입
      • ENUM, SET은 잘 사용되지 않는다고 함.

# 제약 조건

  • 제약 조건은 테이블에 데이터를 저장할 때 특정 규칙을 지키도록 강제하는 장치이다.
  • 아래와 같은 제약 조건들이 존재한다.
    1. NOT NULL: 필수 입력 항목 지정
      • 위 제약 조건이 걸린 행에는 NULL 데이터를 허용하지 않는다.
    2. UNIQUE: 중복 불가
      • 위 제약 조건이 걸린 열의 값은 테이블 내에서 항상 고유해야 한다.
      • PK는 테이블 내에서 하나의 열에만 설정 가능하지만, UNIQUE는 여러 열에 사용 가능하다.
    3. PK: 테이블 대표 식별자
      • NOT NULL과 UNIQUE 특징을 모두 가진다
      • AUTO_INCREMENT: PK에 자주 사용하는 옵션으로, 정수타입 PK 열에 해당 옵션을 설정하면 새 데이터 추가시마다 1씩 자동으로 증가하는 번호를 할당해준다.
    4. FK: 테이블 간의 관계 설정
      • 한 테이블을 다른 테이블과 연결해준다.
      • 참조하는 열의 값은 반드시 참조되는 테이블의 PK값중 하나여야 한다는 참조 무결성을 강제한다.
    5. DEFAULT: 기본값 설정
      • INSERT시 특정 열 값을 명시하지 않으면 자동으로 설정된 기본값이 입력된다.
    6. CHECK: 컬럼에 입력되는 값이 특정 조건을 만족하는지 검사한다.
      • 조건에 맞지 않는 데이터 입력을 막는다.

FK가 참조 가능한 값

  • FK는 PK값 외에도 UNIQUE 제약조건이 설정된 컬럼의 값도 참조 가능하다.
  • 이러한 이유로 FK 컬럼에 NOT NULL 제약조건을 추가해야 할 수 있다.

날짜와 기본값 설정 옵션

CREATE TABLE test (
    ...
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
  • 위 기능을 사용하면 새 행이 추가될때 CURRENT_TIMESTAMP로 기본값이 사용된다.
  • ON UPDATE를 사용하면 같은 행의 컬럼 값이 변경될때 현재 날짜 및 시간으로 자동 갱신된다.
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY ,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT '주문 접수',

    CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT fk_orders_products FOREIGN KEY  (product_id) REFERENCES products(product_id)
);
  • 고객, 상품, 주문 테이블이 존재한다고 할때 위와 같은 쿼리로 테이블을 생성할 수 있다.
    • 주문은 고객과 상품의 아이디를 참조한다.
    • CONSTRAINT 제약조건_이름 FOREIGN KEY (현재_테이블의_컬럼명) REFERENCES 테이블(참조할_컬럼명)
    • 위 제약조건이 걸리면 참조할 컬럼명에 없는 데이터를 삽입하는 행위를 차단한다.
    • fk_참조하는테이블_참조되는테이블 이름으로 제약조건 이름을 명명하면 관계 파악이 쉽다.
  • 고객과 주문의 관계는 1대다 관계이다.
    • 1명의 고객은 여러 주문을 할 수 있다.

# DDL - 테이블 변경 및 제거

  • ALTER TABLE: 이미 만든 테이블의 구조를 변경한다.
    • ADD COLUMN 컬럼명 제약조건: 컬럼을 추가한다.
    • MODIFY COLUMN 컬럼명 제약조건: 기존 컬럼 데이터 타입을 변경한다.
      • 기존 데이터들이 '123'이어서 INT로 쉽게 변경 가능한 경우에만 데이터 타입 자체 변경이 가능하다.
      • VARCHAR의 길이 변경도 가능하다.
    • DROP COLUMN 컬럼명: 컬럼을 제거한다.
ALTER TABLE customers
ADD COLUMN point INT NOT NULL DEFAULT 0;
  • DROP TABLE vs TRUNCATE TABLE
    • DROP TABLE은 테이블 존재 자체를 삭제한다.
      • 테이블 데이터 및 구조까지도 삭제된다.
      • 테이블을 사용하려면 CREATE TABLE 부터 시작해야 한다.
    • TRUNCATE TABLE은 테이블 구조는 남기고 내부 데이터만 삭제한다.
      • DELETE FROM 테이블명과 처리 결과는 같지만 TRUNCATE은 테이블 초기화 개념이기 때문에 내부 처리방식이 훨씬 빠르다.
      • TRUNCATE은 AUTO_INCREMENT 값도 초기화한다. 마지막 값이 1000이어서 1001부터 시작되어야 했어도 초기화 이후에는 1부터 시작한다.
    • DROP, TRUNCATE 모두 다른 테이블에 의해 참조되고 있는 상태라면 처리되지 않는다.
    • SET FOREIGN_KEY_CHECKS 값을 0으로 설정하면 외래키 제약조건을 비활성화 할 수 있다.
      • SET 쿼리는 데이터베이스 접속 유지 동안만 유효하다.
      • 재접속시 설정을 다시 해야한다.

# DML - CRUD

  • INSERT
    • 기본 문법: INSERT INTO 테이블 (값을 지정할 컬럼명들) VALUES (값들)
      • 컬럼명을 지정하지 않으면 열 순서대로 값을 넣어야 한다.
      • 입력 값들은 열 목록 순서와 갯수가 정확히 일치해야 한다.
    • INSERT시 AUTO_INCREMENT 열에는 NULL값을 넣어도 된다. 내부적으로 자동으로 다음 순번의 값을 넣어준다.
    • DEFAULT 혹은 AUTO_INCREMENT가 설정된 열은 굳이 명시하지 않아도 된다.
    • 실무에서는 열 목록을 명시하는 것이 안전하다.
    • VALUES 이후 값 그룹을 여러개 동시에 명시하여 한번에 여러 값들을 넣을 수 있다.
  • UPDATE
    • 기본 문법: UPDATE 테이블명 SET 컬럼1 = 새로운 값1, 컬럼2 = 새로운 값2, ... WHERE 조건
      • WHERE 생략시 모든 행이 수정되므로 주의해야 한다.
    • 기본적으로 safe update mode가 활성화되어 있기 때문에 WHERE절 생략 후 업데이트 시 처리되지 않는다.
    • 변경뿐 아니라 삭제 시에도 동일하게 적용된다.
  • DELETE
    • 기본 문법: DELETE FROM 테이블명 WHERE 조건
    • DELETE는 DML(데이터 조작어), TRUNCATE은 DDL(데이터 정의어)이다.
    • DELETE는 한줄씩 삭제하고 각 행의 삭제를 기록하기 때문에 느리다.
    • TRUNCATE은 테이블 전체를 한 번에 삭제하기 때문에 빠르다.
    • DELETE는 AUTO_INCREMENT 정보가 초기화되지 않는다.
    • TRUNCATE은 롤백이 불가능하다. DELETE는 트랜잭션 내에서 가능하다.

# 조회와 정렬

# SELECT

  • SELECT: 무엇을 가져올 것인가? (조회할 열 지정)
  • FROM: 어디서 가져올 것인가? (테이블 지정)
  • 단순 SELECT *는 데이터베이스가 대량의 데이터를 관리하는 경우, 시스템에 큰 부하를 줄 수 있다.
    • 성능 저하, 가독성 저하, 네트워크 트래픽 낭비 문제가 발생한다.
    • 데이터베이스 서버에서 네트워크를 통해 클라이언트로 전달할 데이터가 과도해진다.
  • 실무에서는 SELECT시 AS로 컬럼 별칭을 지정하는 것이 일반적이다.

# WHERE

  • WHERE절로 특정 조건을 만족하는 데이터를 추출할 수 있다.
  • WHERE는 FROM 바로 뒤에 위치한다.
  • 여러 조건을 조합하는 경우 AND, OR, NOT을 사용한다.
  • BETWEEN
    • 특정 범위에 있는 값을 찾는다. AND 조건으로 이어도 되지만 더 간단하다.
    • WHERE price BETWEEN 5000 AND 15000;
      • 양 끝값을 포함한다.
    • NOT 조건을 붙여 범위 조건을 뒤집을 수 있다.
      • 양 끝값을 포함하지 않는다.
  • IN
    • WHERE name IN ('ABC', 'DEF', 'GHJ');
    • 괄호 안에 있는 목록 중 하나라도 일치하는 것이 있으면 선택한다.
    • NOT을 붙이면 리스트 중 하나라도 해당되는 게 있으면 제외한다.
  • LIKE
    • 문자열 일부로 패턴매칭 기반 검색을 처리한다.
    • % 와일드카드 조건으로 문자 패턴을 매칭시킬 수 있다.
      • jun%인 경우 jun으로 시작하는 모든 문자열이 매칭된다.
      • %jun인 경우 jun으로 끝나는 모든 문자열이 매칭된다.
      • %jun%인 경우 jun이라는 단어를 포함하는 모든 문자열이 매칭된다.
    • _ 언더스코어로 정확히 한 개 문자에 대해 패턴 매칭을 한다.
      • 박_준: 박으로 시작하고 준으로 끝나는 세 글자 이름만 매칭
    • NOT LIKE로 패턴 매칭이 된 케이스를 제외하고 조회할 수 있다.

# ORDER BY 정렬

  • ORDER BY
    • 결과의 순서를 정렬한다.
    • 정렬 방식은 오름차순(ASC), 내림차순(DESC) 두 가지가 존재한다.
    • ORDER BY 정렬 방식의 기본값은 ASC이므로 오름차순 정렬시 ASC는 생략해도 된다.
SELECT 열 FROM 테이블
WHERE 조건
ORDER BY 정렬기준열 정렬방식
  • 정렬 기준이 여러개인 경우 콤마를 기준으로 n차 기준을 정의할 수 있다.
ORDER BY column1 ASC, column2 DESC

# LIMIT

  • 출력 결과를 상위 N개 또는 특정 구간의 데이터만 잘라서 볼 수 있다.
ORDER BY price DESC LIMIT 2;
  • Offset을 활용하면 페이징도 적용 가능하다.
LIMIT 오프셋_갯수, 가져올_갯수;

SELECT * FROM products ORDER BY product_id LIMIT 0, 2; // 0~1
SELECT * FROM products ORDER BY product_id LIMIT 2, 2; // 2~3

# DISTINCT

  • 조회된 결과에서 중복된 행을 모두 제거하고 유일한 값만 남겨준다.
SELECT DISTINCT 컬럼명 FROM 테이블명;
  • 여러 컬럼을 대상으로도 중복 제거가 가능하다.
    • 지정된 모든 컬럼의 조합이 유일한 것들을 보여준다.
SELECT DISTINCT customer_id, product_id FROM orders;

# NULL 처리

  • 만약 특정 컬럼이 NULL인 데이터를 조회할때 SELECT * FROM 테이블 WHERE 컬럼 = NULL; 쿼리를 수행하면, 조회 결과가 없음으로 나타난다.
  • NULL은 특정한 값이 아닌, 내부 값을 알 수 없음을 나타내는 상태이므로 비교 연산이 불가능하다.
  • NULL여부 검사를 위해서는 IS NULL, IS NOT NULL 구문을 사용해야 한다.

NULL 정렬

  • NULL은 정렬 시 가장 작은 값으로 취급한다.
  • 오름차순 정렬시 NULL값이 가장 먼저 등장한다.
  • 내림차순 정렬시 NULL값이 가장 나중에 등장한다.

NULL 포함 컬럼 정렬시 NULL값 정렬은 다르게 하고싶을때

  • NULL은 가장 작은 값으로 취급되기 때문에 내림차순 정렬 시 가장 나중에 등장한다.
  • NULL값들을 가장 앞에 노출시키면서 나머지 값 기준은 내림차순으로 처리하고 싶은 경우, IS NULL을 활용하면 된다.
SELECT product_id, name, description, description IS NULL
FROM products
ORDER BY description IS NULL DESC, description DESC;
  • NULL 정렬을 먼저 수행하고, 나머지 row 정렬을 위해 컬럼정렬을 두 번째 기준으로 다시 수행하면 된다.

# 데이터 가공

# 산술 연산

  • SELECT 절 안에서 산술 연산이 가능하다.
    • 컬럼 간, 혹은 단순 값과 사칙연산도 가능하다.
  • AS를 통해 보기좋게 컬럼 이름을 수정하는 것이 좋다.
# 컬럼 간 사칙연산
SELECT price * stock_quantity FROM products;

# 단순 사칙연산
SELECT price + 3000 FROM products

# 문자열 함수

  • 두 컬럼의 문자열에 대해 여러 문자열 조작을 해서 결과물을 보여줘야 할 경우가 있다.
  • CONCAT
    • 문자열 합치기
    • CONCAT(string1, string2, ..)
SELECT CONCAT(name, '(', email, ')') AS name_and_email FROM customers;
  • CONCAT_WS(구분자, string1, string2, ...)
    • 구분자를 기준으로 문자열들을 합친다.
    • 위 함수는 MySQL의 dialect로, 다른 DBMS에서는 호환되지 않을 수 있다.
SELECT CONCAT_WS(' - ', name, email, address) AS customer_details FROM customers;
  • UPPER(string) / LOWER(string)
    • 전달된 컬럼의 문자열을 대문자 혹은 소문자로 변경한다.
  • LENGTH(string)
    • 문자열 길이를 바이트 단위로 반환한다. (한글은 UTF-8 기준 3바이트)
  • CHAR_LENGTH(string)
    • 글자 수를 반환한다.

# NULL 함수

  • NULL값을 그대로 출력 결과로 노출하기에는 보기 좋지 않다.
  • IFNULL(표현식1, 표현식2)
    • 표현식1: NULL 여부를 검사할 컬럼 혹은 값
    • 표현식2: 표현식 1에서 NULL인 경우 대신 반환할 값
SELECT
    name,
    IFNULL(description, '상품 설명 없음') AS description
FROM products;
  • COALESCE(표현식1, 표현식2, ..., 최종적으로 null일때 표시할 값)
    • 우선순위를 두고 각 컬럼들의 NULL 여부를 순서대로 검사
    • NULL이 아닌 값을 만나면 그 값을 표시한다.

# 집계와 그룹핑

# 집계 함수

  • 실무에서는 다양한 데이터 집계 함수를 제공한다.
  • COUNT(*)
    • 전체 행 갯수를 세는 함수
    • NULL 상관없이 모든 행의 갯수를 센다.
  • COUNT(컬럼)
    • 특정 컬럼 이름을 지정하면 해당 컬럼의 값 중 NULL이 아닌 값의 갯수만 센다.
  • COUNT(DISTINCT 컬럼)
    • 특정 컬럼에서 중복을 제거한 고유 갯수를 계산한다.
SELECT
    COUNT(*) AS '전체 주문 건수',
    COUNT(category) AS '카테고리 등록 건수'
FROM order_stat;
  • SUM
    • 컬럼의 전체 합계 값을 계산한다.
  • AVG
    • 컬럼 내 전체 값을 평균낸다.
  • SUM, AVG 두 함수 모두 NULL값을 제외한다.
  • MAX, MIN으로 컬럼 내 최댓값, 최솟값을 구할 수 있다.
  • GROUP BY 기준컬럼
    • 특정 컬럼의 값이 같은 행들을 하나의 그룹으로 묶어준다.
    • 아래와 같이 카테고리 값이 같은 케이스들을 그룹화한다.
    • GROUP BY연산은 NULL 값도 하나의 그룹으로 취급한다.
      • 데이터 정제 필요성을 인지할 수 있다.
    • COUNT, SUM, MAX 등의 집계 함수와 함께 활용할때 강력하다.
SELECT customer_name,
       COUNT(*) AS `총 주문 횟수`,
       SUM(quantity) AS `총 구매 수량`,
       SUM(quantity * price) AS `총 구매 금액`
FROM order_stat
GROUP BY customer_name
ORDER BY `총 구매 금액` DESC;

컬럼명 백틱

  • 컬럼명을 백틱으로 감싸 alias 처리를 하면 ORDER BY에서 사용 가능하다.
  • 같은 SELECT 계층에서는 불가능하다.
  • GROUP BY로 그룹화 시, 여러 컬럼을 전달하는 경우 세분화된 그룹을 만들 수 있다.
    • GROUP BY customer_name, category인 경우, 각 고객이 어떤 카테고리의 상품을 구매했는지 조회할 수 있게 되는 것이다.

GROUP BY 주의사항

  • SELECT 절에는 GROUP BY에 사용된 컬럼과 집계 함수만 사용 가능하다.
  • 다른 컬럼의 개별 데이터 조회는 불가능하다.
  • 그룹화된 각 그룹은 여러 행이 하나의 데이터로 합쳐지게 된다.
    • 합쳐지는 기준이 그룹명이거나 집계 함수가 되는 것이다.
  • HAVING
    • 그룹화 이후 그룹 자체를 걸러내는 조건이다.
    • WHERE의 경우 그룹화가 이루어지기 전 테이블 개별 행 하나하나에 대해 조건 검사를 하는 역할을 한다.
    • FROM -> WHERE -> GROUP BY -> .. SELECT -> ORDER BY 이와 같은 순서로 SQL이 작동한다.
SELECT
    category,
    SUM(price * quantity) AS total_sales
FROM order_stat
WHERE SUM(price * quantity) >= 500000 # ??
GROUP BY category;

# ---

SELECT
    category,
    SUM(price * quantity) AS total_sales
FROM order_stat
GROUP BY category
HAVING total_sales >= 500000;
  • HAVING절에 집계함수 표현식을 그대로 쓰는 것이 SQL 표준이고, SELECT alias를 참조하는 것은 dialects이다.
기준 WHERE HAVING
작동 시점 GROUP BY 이전 GROUP BY 이후
필터링 대상 개별 행 (row) 그룹 (group)
집계함수 사용 ❌ 불가 ✅ 가능
역할 개별 행 선택 그룹화된 결과 중에 조건에 맞는 그룹만 선택
  • 쿼리 실행의 순서로 인해 WHERE절에서는 컬럼 별칭을 재사용하지 못한다.
    • FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

← Database 기본 →