Note 312 Database, SQL, Relations

실무에서는 csv 파일, 엑셀 파일을 쓰는게 아니라 기업 내의 데이터 베이스에서 데이터를 불러와서 사용한다. 이 데이터 베이스에 대한 개념들과 기본 쿼리문에 대해 알아보자.

Database를 사용하지 않았을 때 문제점

1) In-Memory: 프로그래밍을 할 때 저장한 변수들은 프로그램을 종료하면 정보가 사라진다. 이렇게 변수를 저장할 때 메모리에 올라갔다가 프로그램 종료시에 다시 사라지는 것을 휘발성이라고 하고, 이 변수를 다시 저장시키려면 위에서부터 다시 실행을 시켜야한다. 복잡하고, 연산량이 많을수록 많은 실행시간이 소요된다.

2) File I/O: 데이터를 조작하려면 먼저 데이터를 불러와야 한다. 데이터의 양이 적으면 파일 형태로 불러와도 상관이 없지만, 데이터가 정말 크다면 불러오는데에도 엄청난 시간이 소요된다. 또, 데이터가 파일 형태로 저장되어있으면 모든 데이터가 필요한게 아님에도 모두 불러와야한다. 파일 형태의 데이터 역시 프로그램을 다시 시작할 때마다 모든 데이터를 불러와야한다.

-> 이 두 가지 문제를 해결하기 위해 데이터베이스를 사용할 수 있다. 관계형 데이터베이스는 데이터를 여러가지 테이블 형태로 저장함으로써 여러가지 파일을 써야 표현이 가능한 데이터를 한 데이터베이스 안에 저장할 수 있다. 엑셀 파일에서 한 시트를 한 테이블로 생각하면 이해하기 쉬울 것 같다. 또한, 쿼리를 사용하여 필요한 데이터만 불러올 수 있기 때문에 큰 용량의 데이터를 저장하고 불러오기가 수월하다. 모든 데이터를 불러올 필요없기 때문에 로딩 시간도 훨씬 줄어들 것이다.

SQL(Structured Query Language) & Relational Database(관계형 데이터베이스)

쿼리는 저장되어 있는 정보를 필터하기 위한 질의문이다. SQL은 주로 관계형 데이터베이스에서 사용하는 쿼리 언어다.

관계형 데이터베이스란, 데이터가 테이블 형태로 구조화 되어있는 데이터베이스를 말한다. 예시로는 MySQL, Maria DB 등이 있다. 반대로 NoSQL은 관계형 데이터베이스와는 달리 데이터의 구조가 명확히 정의되어 있지 않는 데이터베이스다. MongoDB와 같은 문서 지향 데이터베이스가 NoSQL에 해당한다.

관계형 데이터 베이스

• 데이터: 각 항목에 저장되는 값
• 테이블: 행과 열로 구성된 구조화된 데이터
• 필드: 칼럼, 열, 속성
• 레코드: 튜플, 인스턴스, 행
• 키: 테이블의 각 레코드를 식별할 수 있는 값. 각 레코드마다 고유의 값이어야 하고 기본키, 외래키 등이 있음

기본키: 테이블 안에서 레코드를 식별할 수 있는 키. 중복 안 됨. NULL값이 없어야함. 다른 속성을 대표할 수 있는 값.

외래키: 외래키는 다른 테이블의 기본키를 참조해서 갖는 키. 외래 테이블에서는 그 키가 기본키여야한다. 기본키가 아니면 중복인 값들이 발생할 수 있기 때문에 리소스가 낭비된다. 또한, 관계 테이블 간의 일관성을 의미하는 참조무결성을 위해.

외래키 테이블의 기본키에 없는 값(외래키 테이블의 기본키 = 외래키)이 외래키에 존재하면 참조 무결성에 위배된다.

테이블 관계설정: 외래 테이블의 기본키를 원하는 테이블의 속성으로 사용해서, 관계를 설정해준다. 다른 테이블의 기본키를 참조해서 속성으로 사용하는 방법.

도메인: 데이터베이스에서 도메인은 타입을 말한다. int, test 등.

데이터 모델링: 데이터 스키마를 짜는 과정.

관계(Relations)

관계형 데이터베이스에서 관계란 테이블 간의 상호작용을 말한다.

1) 1:1 관계

테이블의 레코드 하나당 다른 테이블의 한 레코드와 연결되어 있는 경우다.

ex) 학생 한명 당 하나의 사물함을 배정받는다. 그 학기 중에는 하나의 사물함은 다른 학생과 연결될 수 없고, 해당 학생하고만 연결되어있다. 서로 한 레코드만 공유하게 되므로 1:1 관계이다.

2) 1:N 관계

테이블의 레코드 하나당 다른 테이블의 여러 레코드와 연결되어 있다.

ex) 한 사람이 여러가지 구글 계정을 가질 수 있다. 하지만 그 반대는 성립되지 않는다. 즉, 한 계정이 여러 유저를 가질 수 없다 = 각 구글 계정의 유저는 한 사람 밖에 될 수 없다.

3) N:N 관계

테이블에 여러 레코드가 여러 개의 레코드를 가지는 관계이다.

ex) 축구 선수는 여러가지 축구화를 신을 수 있고, 그 축구화도 여러 선수들에 의해 신겨진다.

하지만 이 관계는 데이터베이스에서 물리적으로 표현할 수 없기 때문에 조인 테이블을 만들어서 각 테이블과 조인 테이블을 일대 다 관계를 만든 후 표현한다. 다대다는 사실상 두개의 일대 다 관계를 연결시킨 것으로 표현가능하다.

ex) 축구 선수 ID가 조인테이블의 축구 선수 ID와 일대 다 관계이다. 축구화 ID는 조인테이블의 축구화 ID와 일대 다 관계이다. 조인테이블은 축구선수와 축구화 ID를 통합한 unique한 ID를 기본키로 가지고 있다(중복방지).

축구 선수 ID -> 축구선수 축구화 ID, 축구 선수 ID, 축구화 ID <- 축구화 ID

외래키를 리스트 형식으로 저장하면 정리하기는 편하지만, 탐색할 때 많은 시간이 소요되기 때문에 비효율적이다. 그래서 리스트 형식으로 저장하는 것보다 1대다 관계로 표현하는 것이 더 효율적이고 적합한 방법이다.

4) 자기 참조 관계

테이블 내에서 관계를 가지는 것이다. 주로 추천인 파악, 조직도 등에 사용된다.

ex) 대학교(id=1), ICT창업학부(id=2), 경영학부(id=3), ict융합전공(id=4). ICT 창업학부는 대학교를 참조하고, ict융합 전공은 ICT 창업학부를 참조한다. 여기서 대학교라는 id는 여러 학부를 가질 수 있지만, 학부는 하나의 대학교 id만 가질 수 있다. 전공도 마찬가지이다. 따라서, 자기 참조 내부는 보통 1대 다 관계로 이루어져있다.

Schema

스키마는 데이터베이스에서 데이터가 구성되는 방식과 서로 다른 엔티티(ex. 테이블) 간의 관계에 대한 설명이다.

이미지 출처: https://database.guide/what-is-a-database-schema/

SQL Basic Query

• Create

CREATE TABLE 테이블이름(
  변수 변수타입(NOT NULL도 같이 쓸 수 있음),
  변수 변수타입,
  PRIMARY KEY(기본키로 사용할 변수),
  FOREIGN KEY(외래키로 사용할 변수),
  REFERENCES 참조할 테이블 (참조할 테이블의 기본키, 위의 외래키와 일치해얌.)  
) 

• Like

/* Son이라는 글자가 포함된 이름 사용. Like는 포함되는 것. %를 쓰면 길이수 제한 없이 특정 단어 앞 혹은 뒤에 어느 문자가 와도됨 */
SELECT ID FROM players WHERE name LIKE '%Son%'

• Join(예시는 inner join, outer join은 다른 것 참고)

SELECT PlayerId FROM players p JOIN matches m ON p.PlayerId = m.PlayerId

• GROUP BY + COUNT. sum이나 다른 함수도 사용 가능

나라별로 선수 몇명인지.

SELECT COUNT(PlayerId) FROM players GROUP BY Country;

• ORDER BY + LIMIT

골수를 내림차순으로 정렬하고, 젤 위에꺼 5개만 출력.

SELECT * FROM players ORDER BY Goals DESC LIMIT 5;