SEB[데이터베이스]

이번에는 데이터베이스 기초에 대해 학습을 했다. 데이터베이스는 학생 때 수업을 통해서, 정보처리기사를 준비하며 학습한 것이 내 데이터베이스의 개념에 대한 모든 것이었지만, 이번에 다시 배우며 꽤 도움이 된 것 같다는 생각이 든다. 데이터베이스를 모두 다루긴 힘드니, 학습을 하며 내가 더 기억하면 좋을 것 같은 개념들을 정리를 해보겠다.

SQL

데이터베이스의 필요성

• 데이터베이스를 이용하지 않고 데이터를 저장하고 불러오는 경우

  • In-Memory

    • 변수를 만들어 저장하고, 프로그램이 종료될 때 해당 프로그램이 사용하던 데이터도 같이 사라진다. 이는 저장한 데이터가 프로그램의 실행에 의존하며, 예기치 못한 상황으로부터 데이터를 보호할 수 없게된다는 말이다.

  • File I/O

    • 파일을 읽는 방식으로 작동하는 형태를 말하지만 단점이 존재한다.
      • 데이터가 필요할 때마다 전체 파일을 읽어야 한다. 파일의 크기가 커질수록 비효율적이다.
      • 파일이 손상되거나 여러 개의 파일들을 동시에 다뤄야 하거나 한다면 복잡하고 데이터량이 많아질수록 데이터를 불러들이는 작업이 점점 힘들어진다.

• 하지만 관계형 데이터베이스에서는 하나의 CSV 파일이나 엑셀 시트같이 한 개의 테이블로 저장을 할 수 있다.

SQL이란?

• Structured Query Language 약자이며 데이터베이스 언어이며 구조화된 쿼리 언어이다., 주로 관계형 데이터베이스에서 사용한다. (MySQL, Oracle, SQLite, PostgreSQL 등)

• 쿼리

  • 쿼리는 질의문이라는 뜻이며, 저장되어 있는 데이터를 필터하기 위한 질의문이라고 할 수 있다.
  • SQL은 데이터의 구조가 고정되어 있는 데이터베이스에서 사용하지만, 그렇지 않은 구조의 데이터베이스에서는 NOSQL을 사용한다.(대표적으로 MongoDB)

SQL 기본

• 나는 CRUD와 어느 정도의 조작법은 알고 있었지만 생각보다 더 많은 명령어가 있었다. 처음보거나 알면 좋을 듯한 것들을 정리하겠다.

데이터베이스 사용

USE DATABASE_NAME;

테이블 정도 확인

DESCRIBE(OR DESC) TABLE_NAME;

LIMIT

• 결과로 출력할 데이터의 갯수를 정할 수 있다.

ACID

트랜잭션

• 트랜잭션이란 여러 개의 작업을 하나로 묶은 실행 유닛이다.

• 각 트랜잭션은 하나의 특정 작업으로 시작을 해 묶여 있는 모든 작업들을 다 완료해야만 정상적으로 종료를 한다. 하나의 트랜잭션에 속해있는 작업 중 하나의 작업이라도 실패한다면, 이 트랜잭션에 속한 모든 작업을 실패한 것으로 판단한다. 모든 작업이 성공하면 성공 그것이 아니면 실패 이 두가지의 결과만 존재한다. (COMMIT, ROLLBACK이 트랜잭션과 관련있다.) 트랜잭션이 ACID의 특성을 가지고 있다.

Atomicity(원자성)

• 하나의 트랜잭션에 속해있는 모든 작업이 전부 성공하거나 전부 실패하여 결과를 예측할 수 있어야 한다.

Consistency(일관성)

• 데이터베이스의 상태가 일관되어야 한다.
• 하나의 트랜잭션 이전과 이후, 데이터베이스의 상태는 이전과 같이 유효해야 한다. 이는 트랜잭션이 일어난 이 후의 데이터베이스는 데이터베이스의 제약이나 규칙을 만족해야 한다는 것이다.

Isolation(격리성)

• 모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다.
• 동시에 여러 개의 트랜잭션들이 수행될 때, 각 트랜잭션은 격리되어 있어 연속으로 실행된 것과 동일한 결과를 나타낸다.

Durability(지속성)

• 하나의 트랜잭션이 성공적으로 수행되었다면, 해당 트랜재개션에 대한 로그가 남아있어야 한다. 오류가 발생하더라고, 해당 기록은 영구적이어야 한다.

데이터베이스 설계

관계형 데이터베이스

• 구조화된 데이터는 하나의 테이블로 표현할 수 있다. 테이블을 relation이라고도 부르기 때문에, 테이블을 사용하는 데이터베이스를 관계형 데이터베이스(Relational database)라고 한다.

• 관계형 데이터베이스의 키워드

  • 데이터 : 각 항목에 저장되는 값

  • 테이블(table or relation) : 사전에 정의된 열의 데이터 타입대로 작성된 데이터가 행으로 축적된다.

  • 컬럼(column or field) : 테이블의 한 열을 가리키며 속성을 나타낸다.

  • 레코드(record or tuple) : 테이블의 한 행에 저장된 데이터이며, 한 객체라고 봐도 좋다.

  • 키(key) : 테이블의 각 레코드를 구분할 수 있는 값이다. 각 레코드마다 고유한 값을 가지며, 기본키(primary key)와 외래기(foreign key) 등이 있다.

• 관계 종류

  • 1:1 관계
    • 하나의 레코드가 다른 테이블의 레코드 한 개와 연결된 경우
  • 1:N 관계
    • 하나의 레코드가 서로 다른 여러 개의 레코드와 연결된 경우
  • N:M 관계
    • 여러 개의 레코드가 다른 테이블의 여러 개의 레코드와 관계가 있는 경우
    • 이 관계를 위한 테이블은 조인 테이블이라 하며 N:M 을 1:N, M:1의 관계로 조인한다.

SQL 내장함수

• 집합연산 : 레코드를 조회하고 분류한 뒤, 특정 작업을 하는 연산

  • GROUP BY : 데이터를 조회할 때 그룹으로 묶어서 조회한다.

  • HAVING : GROUP BY로 조회된 결과를 필터링한다. 그룹화한 결과에 대한 필터이므로, WHERE과 다르다. WHERE는 저장된 레코드를 필터링한다.

  • COUNT() : 레코드의 갯수를 뽑을 때 사용한다.

  • SUM() : 레코드의 합을 리턴한다.

  • AVG() : 레코드의 평균값을 계산한다.

  • MAX(), MIN() : 각각 레코드의 최대값과 최소값을 리턴한다.

• SELECT의 실행 순서

  FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

SELECT CustomerId, AVG(Total)
FROM invoices
WHERE CustomerId >= 10
GROUP BY CustomerId
HAVING SUM(Total) >= 30
ORDER BY 2

위 쿼리문의 실행 순서는 다음과 같다.
1. FROM invoices: invoices 테이블에 접근을 한다다.
2. WHERE CustomerId >= 10: CustomerId 필드가 10 이상인 레코드들을 조회한다.
3. GROUP BY CustomerId: CustomerId를 기준으로 그룹화한다.
4. HAVING SUM(Total) >= 30: Total 필드의 총합이 30 이상인 결과들만 필터링한다.
5. SELECT CustomerId, AVG(Total): 조회된 결과에서 CustomerId 필드와 Total 필드의 평균값을 구한다.
6. ORDER BY 2: AVG(Total) 필드를 기준으로 오름차순 정렬한 결과를 리턴한다.

데이터베이스 정규화

• 데이터베이스를 설계할 때는 아래의 기준을 잘 살펴봐야 한다.
  • Data Redundancy(데이터 중복)
    • 데이터 중복은 실제 데이터의 동일한 복사본이나 부분적인 복사본을 뜻한다. 문제점도 존재한다.
      • 일관된 자료 처리의 어려움
      • 저장 공간 낭비
      • 데이터 효율성 감소
  • Data Integrity(데이터 무결성)
    • 데이터 정규화는 데이터 무결성을 강화하기 위한 목적을 지닌다.
    • 데이터 무결성은 데이터의 수명 주기 동안 정확성과 일관성을 유지하는 것을 뜻한다. 이는 데이터가 입력된 그대로 데이터를 사용할 수 있다는 뜻이다.
  • Anomaly(데이터 이상 현상)
    • 3가지의 이상현상이 존재한다.
      • Update Anomaly(갱신이상)
        • 동일한 데이터가 여러 행에 걸쳐 있을 때에 어느 데이터를 갱신해야 하는지에 대한 논리적 일관성이 없어 발생하게 된다.
      • Insertion Anomaly(삽입 이상)
        • 데이터베이스 스키마에 따르지 않고 데이터베이스에 데이터를 삽입하는 경우 삽입을 못하는 경우이다.
      • Deletion Anomaly(삭제 이상)
        • 데이터의 특정 부분을 지울 때에 의도치 않게 다른 부분들도 함께 지워지는 이상 현상이다.
• 정규화의 과정
  1. 비정규 릴레이션을 도메인이 원자값이 되도록 1차 정규형을 만든다.
  2. 부분적 함수 종속을 제거하여 2차 정규형을 만든다.
  3. 이행적 함수 종속을 제거하여 3차 정규형을 만든다.
  4. 결정자이면서 후보키가 아닌 것들을 제거하여 BCNF로 만든다.
  5. 다치 종속을 제거하여 4차 정규형을 만든다.
  6. 조인 종속성을 이용하여 5차 정규형을 만든다.

SQL의 종류

• 쿼리문을 어떻게 작성하느냐에 따라, 기능이나 작업이 달라지며, 아래와 같이 분류한다.

Data Definition Language(DDL)

• DDL(Data Definition Language)은 데이터를 정의할 때 사용하는 언어다. 테이블을 만들 때 사용하는 CREATE 이나 테이블을 제거할 때 사용되는 DROP 등이 DDL에 해당한다. 데이터베이스의 테이블과 같은 오브젝트를 정의할 때 사용한다.

Data Manipulation Language(DML)

• DML(Data Manipulation Language)은 데이터베이스에 데이터를 저장할 때 사용하는 언어다. INSERT 처럼 새로운 레코드를 추가할 때 사용되는 문법을 포함해 데이터를 삭제하는 DELETE 나, 변경하는 UPDATE 가 DML에 포함된다.

Data Control Language(DCL)

• DCL(Data Control Language)은 데이터베이스에 대한 접근 권한과 관련된 문법이다. 어느 유저가 데이터베이스에 접근할 수 있는지 권한을 설정한다. 권한을 주는 GRANT 나, 권한을 가져가는 REVOKE 등이 DCL에 포함된다.

Data Query Language(DQL)

• DQL(Data Query Language)은 정해진 스키마 내에서 쿼리할 수 있는 언어다. SELECT 가 DQL에 해당한다. 이렇게 언어를 분류했지만, DQL을 DML의 일부분으로 취급하기도 한다.

Transaction Control Language(TCL)

• TCL(Transaction Control Language)은 DML을 거친 데이터의 변경사항을 수정할 수 있다. COMMIT 처럼 DML이 작업한 내용을 데이터베이스에 커밋하거나, ROLLBACK 처럼 커밋했던 내용을 다시 롤백하는 문법이 있다.

SQL 더 깊게 알아보기

CASE

• SQL에서도 프로그래밍 언어의 if문과 같은 기능을 사용할 수 있다. CASE 를 사용하면, 특정 조건에 따라 다른 결과를 받을 수 있다.

  SELECT CASE
  		WHEN CustomerId <= 25 THEN 'GROUP 1'
  		WHEN CustomerId <= 50 THEN 'GROUP 2'
  		ELSE 'GROUP 3'
  	END
  FROM customers

이 쿼리문은 CustomerId 필드값에 따라 3개의 그룹('GROUP 1', 'GROUP 2', 'GROUP 3')으로 나뉜다.

SUBQUERY

• 쿼리문을 작성할 때, 다른 쿼리문을 포함할 수 있다. 서브쿼리는 실행되는 쿼리에 중첩으로 위치해, 정보를 전달한다. 서브쿼리는 소괄호로 감싸야 한다.

• 서브쿼리의 결과는 개별 값이나 레코드 리스트이다.

• 서브쿼리의 결과를 하나의 칼럼으로 사용할 수 있다.

  SELECT CustomerId, CustomerId = (SELECT CustomerId FROM customers WHERE CustomerId = 2)
  FROM customers
  WHERE CustomerId < 6

IN, NOT IN

• IN은 특정값이 서브쿼리에 있는지 확인할 수 있다.

  SELECT *
  FROM customers
  WHERE CustomerId IN (SELECT CustomerId FROM customers WHERE CustomerId < 10)

EXISTS, NOT EXISTS

• 돌려받은 서브쿼리에 존재하거나 존재하지 않는 레코드를 확인한다.
• TRUE or FALSE로 리턴을 한다.

  SELECT EmployeeId
  FROM employees e
  WHERE EXISTS (
  	SELECT 1
  	FROM customers c
  	WHERE c.SupportRepId = e.EmployeeId
  	)
  ORDER BY EmployeeId

FROM

SELECT *
FROM (
	SELECT CustomerId
	FROM customers
	WHERE CustomerId < 10
	)

Reference

• 코드스테이츠 강의자료