[데이터 엔지니어링] SQL (1)

Colacan·2022년 5월 11일
sql데이터 엔지니어링
1

[데이터 엔지니어링]

목록 보기
7/12

기존 데이터의 저장방식

  1. In-Memory
    1) 특정 프로그램이 실행될 때에만 존재하는 데이터
    2) 특정변수에 값을 넣는 것과 같은 방식

  2. File I/O
    1) 엑셀 시트와 CSV방식은 매번 읽어와야하고 크기가 커질수록 버거워진다.
    2) 따라서 SQL을 활용하여 복잡하거나 큰 용량의 데이터를 가져옴

SQL

데이터베이스 언어의 기준으로 주로 관계형 데이터베이스에서 사용

  1. MySQL, Oracle, SQLite, PostgreSQL에서 이용된다.
  2. 구조화된 쿼리 언어이다.
  3. SQL을 사용하는 이유
    1) SQL은 통상적으로 많이 이용된다.
    2) SQL은 데이터분석 분야에서 수요가 많다.
    3) SQL은 쿼리언어로 데이터 베이스에서 데이터를 가져오고 필터링하는데 사용된다.

쿼리

SQL에서 사용되는 질의문을 뜻한다.

  1. 존재하는 데이터를 검색어로 필터하듯 쿼리는 저장된 정보를 필터한다.
  2. 따라서 SQL은 데이터베이스에 쿼리를 보내 원하는 데이터만 가져온다.
  3. SQL은 관계형으로 데이터가 구조화된 테이블을 사용하는 데이터베이스에서 활용
  4. 구조가 고정되어있지 않은 데이터베이스는 NoSQL이라고 한다. (MongoDB같은 문서지향 데이터베이스)

SQL 문법

데이터 조회나 테이블 형성에 다양한 문법이 존재한다.

  1. Data Definition Language : 테이블과 같은 오브젝트를 정의할 때 사용되는 언어
    1) CREATE : 테이블 만듬
    2) DROP : 테이블 제거

  2. Data Manipulation Language : 데이터베이스에 데이터를 변경할 때 사용
    1) INSERT : 레코드 추가
    2) DELETE : 데이터 삭제
    3) UPDATE : 데이터 변경

  3. Data Control Language : 데이터베이스에 대한 접근 권한과 관련된 문법
    1) GRANT : 권한을 주는 역할
    2) REVOKE : 권한을 가져감

  4. Data Query Language : 정해진 스키마 내에서 쿼리를 할 수 있는 언어
    1) SELECT : 해당 테이블의 모든열을 읽음
    2) DML의 일부라고 말하기도 한다.

  5. Transaction Control Language : DML을 거친 데이터의 변경사항을 수정
    1) COMMIT : DML이 작업한 내용을 데이터베이스에 기록
    2) ROLLBACK : 커밋한 내용을 취소

  6. SQL의 경우 쿼리문에 대한 답을 한번에 내놓는다.
    (print문과 같이 순차적으로 이루어지는 것이 아님)

관계형 데이터베이스

SQL을 이용하여 관계형 데이터 베이스를 관리할 수 있다.

  1. 구조화된 데이터가 하나의 테이블로 표현

  2. 테이블간의 상호작용을 관계라고 한다.

  3. 관계형 데이터베이스 키워드
    1) 데이터 : 각 항목에 저장되는 값
    2) 테이블 : 사전에 정의된 행과 열로 구성된 데이터
    3) 필드 : 테이블의 열을 가리킴
    4) 레코드 : 테이블의 한 행에 저장된 정보
    5) 키 : 테이블의 레코드를 구분하는 값, 레코드마다 고유값,기본키와 외래키로 나뉨
    6) 기본키 : 후보키 중 대표로 삼는키 (중복될 수 없다. 널값을 가질 수 없다.)
    7) 외래키 : 다른 관계의 기본키를 참조하는 속성

  4. 관계 종류
    1) 1:1 관계 : 레코드 하나당 다른 테이블의 한 레코드와 연결 (1대1관계라면 두개의 테이블을 합치는 것이 보편적)
    2) 1:N 관계 : 레코드 하나당 여러개의 레코드 연결 (가장 흔하게 사용되는 관계)
    3) N:N 관계 : 여러개의 레코드가 여러개의 레코드를 가지는 관계 (조인 테이블을 만들어 관리한다, 양방향에서 다수를 가질 수 있는 경우이다.)
    4) 조인 테이블 : 양 테이블에서 일대다 관계를 만드는 테이블
    5) 자기참조관계 : 테이블내에서의 관계 (추천인의 경우 여기에 포함, 1:N 관계와 유사하다)

스키마

데이터베이스에서 데이터가 구성되는 방식

  1. 엔티티들간의 관계에 관한 설명

  2. 엔티티 : 고유한 정보의 단위 (데이터베이스에서 테이블로 표시할 수 있다.)

SQL에서 문제점과 해결법

문제점

  1. 여러개의 항목이 담겨져 있는 경우 데이터 찾을 때 오래걸린다. (1:N 에서의 문제점)

  2. 테이블에는 기본적으로 ID값이 존재하므로 간단히 표현가능 (하지만 숫자로 대체한다고 시간이 짧아지지는 않는다.)

  3. 필드에 데이터를 담는데는 한계가 존재한다.
    (하나의 열에 여러값이 힘드므로 여러개의 레코드를 만드는 방법도 가능하다.)
    (이 경우 동일한 값을 가지는 레코드에 대해서 불확실한 측면이 생긴다.)

해결법

  1. 위의 문제들을 해결하는 방법 : N의 테이블에 1의 테이블의 고유번호를 넣어준다.

  2. 단, N:N의 경우 앞선 방법으로 해결불가.

  3. 위의 경우 두개의 테이블 ID를 결합한 테이블을 만들어 1:N 관계를 두개 만들어준다. (조인테이블)

SQL 문법 +

  1. 쿼리문법 키워드
    1) 자세한 것은 공식문서 참고
    2) Select, Where, And, Or, Not, Order By, Insert Into, Null Values, Update, Delete, Count, Like, Wildcards, Aliases, Joins (Inner Join, Left Join, Right Join), Group By

  2. 데이터베이스 관련 작업 키워드
    1) 자세한 것은 공식문서 참고
    2) SQL Create DB, SQL Drop DB, SQL Create Table, SQL Drop Table, SQL Alter Table, SQL Not Null, SQL Unique, SQL Primary Key, SQL Foreign Key, SQL Default, SQL Auto Increment, SQL Dates

  3. 문법 예시

1) SELECT : 데이터셋에 포함될 특성들을 선택

SELECT 'hello world';

2) FROM : 테이블과 관련이 있는 경우 필수로 명시, 결과들을 도출해낼 데이터베이스 테이블을 명시

SELECT 특성_1
FROM 테이블_이름;

3) WHERE : 필터 역할을 하는 쿼리문
특정값과 동일한 데이터 찾는 경우

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_1 = "특정 값";

특정값을 제외한 데이터 찾는 경우

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 <> "특정 값";

크거나 작은 데이터를 필터할 때에는 '<', '>', 비교하는 값을 포함하는 '이상', '이하' 값은 '<=', '>=' 을 사용
문자열에서 특정 값과 비슷한 값들을 필터할 때에는 LIKE와 \% 혹은 * 를 사용

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 LIKE "%특정 문자열%";

리스트의 값들과 일치하는 데이터를 필터할 때에는 IN 을 사용

SELECT 특성_1, 특성_2
FROM 테이블_이름
WHERE 특성_2 IN ("특정값_1", "특정값_2");

값이 없는 NULL 과 같은 경우를 찾을 때에는 IS 를 사용
값이 없는 경우를 제외할 때에는 NOT 을 추가해 이용

4) ORDER BY : 데이터 정렬방법
기본적으로 오름차순

SELECT *
FROM 테이블_이름
ORDER BY 특성_1;

내림차순은 DESC 이용

SELECT *
FROM 테이블_이름
ORDER BY 특성_1 DESC;

5) LIMIT : 돌려받는 데이터개수 제한, 쿼리문에서 이용시 마지막에 추가

SELECT *
FROM 테이블_이름
LIMIT 200;

6) DISTINCT : 고유값 선택, SELECT 뒤에 붙여 사용

SELECT DISTINCT 특성_1
FROM 테이블_이름;

7) INNER JOIN : 공통된 부분으로 연결, JOIN으로도 가능하다.

SELECT *
FROM 테이블_1
JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B;

8) OUTER JOIN : 다양한 선택지의 JOIN 수행
LEFT INCLUSIVE : LEFT OUTER JOIN 으로 진행

SELECT *
FROM 테이블_1
LEFT OUTER JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B

RIGHT INCLUSIVE : RIGHT OUTER JOIN 으로 진행

SELECT *
FROM 테이블_1
RIGHT OUTER JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B

sqlite 에서는 RIGHT OUTER JOIN / FULL OUTER JOIN 을 지원하지 않기 때문에 순서를 바꾸어 LEFT JOIN 을 이용한다.

profile
Colacan
For DE, DA / There is no royal road to learning
이전 포스트

[데이터 엔지니어링] DB API

다음 포스트

[데이터 엔지니어링] SQL (02)

0개의 댓글