2주차(5) - [SQL] 데이터베이스 & SQL

1.데이터베이스

데이터베이스란?

• 데이터를 체계적으로 저장, 관리 및 검색하기 위한 시스템
• 데이터 구조화를 통해 효율적 접근, 조작 및 관리를 가능하게 함

데이터베이스 시스템은 다양한 작업을 지원하기 위한 Query 언어를 제공

SQL(Structured Query Language)은 가장 널리 사용되는 쿼리 언어

저장된 데이터를 검색, 삽입, 수정, 삭제하는 데 사용

엑셀과 유사한 형태

각 테이블은 특정 유형의 데이터를 저장하는 데 사용

• 행(row) / 열(column)의 형태로 사용
• 관계형 데이터베이스 시스템에서 주로 사용

이름생년월일성별

0
1
2

즉, 한 행은 유저 한 명 한 명을 나타낸다고 할 수 있음

• 이러한 테이블 형태가 Relational Database 시스템에서 주로 사용

왜 데이터베이스를 사용할까?

• 데이터베이스는 데이터를 구조화해 저장, 필요한 형식과 구조에 맞게 데이터를 정리
• 쿼리, 언어(SQL)을 이용해 데이터베이스에서 필요한 정보를 추출, 데이터를 삽입, 수정, 삭제할 수 있음
• 내부 데이터는 시스템 장애나 오류가 발생해도 보존
• 데이터베이스는 여러 사용자가 동시에 데이터에 엑세스, 공유할 수 있는 환경 제공

데이터의 종류

정형 데이터

• 형태 O / 연산 O
• 엑셀에 넣을 수 있으면 다 정형데이터
• RDB,Excel,CSV

반정형 데이터

• 형태 O / 연산 X
• XML / HTML / JSON

비정형 데이터

• 변수로 한 번에 넣을 수 없는 것들
• 음성 / 이미지 / 지문 / 영상 ···

SQL vs. NoSQL

• NoSQL은 Key-value / Graph / Documnet 등등으로 정의된다

관계형 데이터베이스(RDB)

• 스키마에 따라 열과 행으로 구성
• 이 데이터들을 SQL을 통해 가져옴
• 복잡한 쿼리나 다양한 연산을 처리하는데 강점
• 구조화된 데이터 다루는 데 적합 / 대용량 트랜잭션 처리에도 효과적

데이터 관리를 위해 테이블 형식으로 저장

테이블 관계를 정의해 데이터 저장 및 관리

예시 - 쇼핑몰데이터

• 유저 정보 테이블
• 상품 정보 테이블
• 주문 정보 테이블

Items

idnameprice

1	청바지	10000
2	티셔츠	15000
3	양말	3000

user

idnamegenderreferral

1	john	M	None
2	peter	M	1
3	merry	F	1

Orders

iduser_iditem_idcount

1	1	3	1
2	2	2	2
3	3	2	1

NoSQL DB

• 필요에 따라 손쉽게 만들 수 있음 / 확장성이 좋음
• 비정형(반정형) 다루는 데 적합 / 분산형 데이터 처리에 적합
• 테이블 형태가 아닌 컬렉션 , 문서, 키 -값 쌍의 모델
• 수평적 확장 가능하기 때문에 대용량 분산 데이터 적합
• 스키마 유연(=만들기 간편) = 데이터 구조를 미리 정의하지 않고, 필요에 따라 동적 변경 가능

• JSON과 같은 예시를 보면, JSON에 key만 추가하면 돼서 편리하다는 것을 알 수 있음

요약

구분RDBNoSQL

스키마	미리 정의되고 고정적	동적이고 유연
쿼리 언어	SQL	표준화된 쿼리 언어 없음
확장성	수직적 확장	수평적 확장
성능	구조화된 데이터에 적합	대규모 비구조화된 데이터에 적합
예시	MySQL,Oracle,RostgreSQL	MongoDB, Cassandra,Couchbase

• RDB → 정리를 잘 해서 넣어줌
• NoSql → 우선 넣음

로컬 DB vs. 클라우드DB

로컬: PC / 클라우드: 네이버 클라우드

로컬 DB클라우드 DB

설명	python 앱을 실행하는 로컬환경에서 동장하는 시스템	클라우드 환경에서 호스팅되고 관리하는 DB 시스템
시스템	SQLite	PostgreSQL

기본키(Primary key) vs. 외래키(Foreign key)

primary key(기본키)foreign key(외래 키)

설명	테이블 각 레코드를 고유하게 식별하는 필드(열) / 고유 식별값이므로 중복 or NULL 불가	다른 테이블에서 참조하는 column
구성	정수(정수ID) 또는 고유한 문자열	다른 테이블과의 참조관계를 나타냄 / 한 테이블과 데이터의 관계를 정의한다
예시	User 테이블의 기본키 → 사용자 ID	Order 테이블의 외래키 → Customer 테이블 기본키 ID 참조

데이터베이스 관계 종류

• 1:1 관계
  • 각 엔티티(테이블)의 레코드는 다른 엔티티(테이블)의 레코드와 1:1대응
  • 예시) 각 유저는 하나의 주민등록번호
• 1:n 관계
  • 한쪽 엔티티(테이블)의 레코드는 다른 엔티티(테이블)의 레코드와 1:n대응
  • 예시) 한 부서에 여러 직원이 속할 수 있지만, 각 직원은 하나의 부서에만 속할 수 있음
• n:m 관계
  • 한쪽 엔티티(테이블)의 레코드는 다른 엔티티(테이블)의 레코드와 n:m대응
  • 예시) 한 학생 → 여러과목 수강 / 한 과목 → 여러 학생 수강

2. SQL

SQL

SELECT 이해하기

• 데이터베이스에서 원하는 데이터 조회
• 테이블 열에서 데이터 선택 , 결과로 반환
• 별칭을 사용해 결과 집합에서 칼럼의 이름을 변경할 수 있음
• *(별표) → 모든 컬럼 호출
• as로 별칭을 붙여줄 수 있음

• 문자열을 연결 가능
• 덧셈 뺄셈을 할 수 있음

중복 제거하기

• DISTINCT: 열의 중복 제거 가능
• SELECT DISTINCT 속성명

2. 함수

문자함수

• 모든 함수를 기억할 필욘 없음
• length / replace(문자열 변경) / round(반올림) / turnc(버림) / sum / avg 정도는 기억

CASE 함수

• 꼭 기억해야 하는 함수
• CASE(IF문 시작) / WHEN(IF) / THEN(PRINT)을 이용해 조건을 붙여줌
• [python]의 IF문과 유사
• END AS: CASE문을 끝내면 카테고리 이름을 새로 만들겠다
• Primary Key: AlbumId
• Foreign Key: ArtistID

실습

5번 과제 실습

SELECT Total
CASE
	WHEN Total <= 10 THEN '낮음'
	WHEN Total <= 50 THEN '보통'
	WHEN Total > 50 THEN '높음' 
END as 'category'
FROM invoices

4. WHERE

WHERE?

• 특정 조건을 기반으로 데이터 필터링 하는데 사용
• SELECT / UPDATE / DELETE 등의 쿼리에서 사용 가능
• 쿼리의 일부로 사용, 조건식을 지정해 참인 행만 반환
• 비교 연산자 / 논리연산자 / 기타연산자로 활용

기타연산자 종류

• IN: 테이블에서 일정 조건
• LIKE :포함되는 모든 데이터
• BETWEEN: A 이상 B 미만
• IS NULL: 할당되지 않은 데이터

LIKE

• ‘_’(언더스코어)는 임의의 단일 문자
  • Like ‘_ohn’
• %: 임의의 문자열(0개 이상의 문자)
  - 예시) 이름에서 S가 들어가는 행을 출력 -> '%S%'

5. GROUP BY & HAVING

GROUP BY

• 데이터를 특정 열 값에 따라 그룹화
• 집계함수(sum / count / avg 등)와 함께 사용해 그룹별로 계산될 결과를 얻을 수 있음

GROUP BY 주요 사용 함수

• COUNT
• SUM
• AVG / MEAN
• MEDIAN / MIN / MAX
• STD / STDDEV
• VAR / VARIANCE
• FIRST / LAST

HAVING

• 그룹화된 결과에 대한 조건을 지정
• 집계 함수와 함께 사용돼 그룹별로 계산된 결과
• GROUP BY에서 쓰는 WHERE문

예제

(중요) SQL 실행순서

• 실행 순서에 대한 오류가 발생할 수 있기 때문에 HAVING이 존재

실습 #1

SELECT Country , COUNT(CustomerId)
FROM customers c 
GROUP BY Country 
HAVING COUNT(CustomerId) >= 5

실습 #3

SELECT GenreId ,AVG(Milliseconds)
FROM tracks t 
GROUP BY GenreId 
HAVING AVG(Milliseconds) >= 300000

실습 #5

SELECT AlbumId , COUNT(TrackId) 
FROM tracks t 
GROUP BY AlbumId 
HAVING COUNT(TrackId) >= 15

6. ORDER BY & JOIN

ORDER BY

순서 정렬 함수

• ASC: 오름차순
• DESC: 내림차순

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

• 두번째 AVG(Total) 기준(오름차순)로 정렬해달라는 것

작성순서 & 실행순서

실행 순서:

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

작성 순서:

SELECT → FROM → JOIN → ON → WHERE → GROUP BY → HAVING → ORDER BY

• 예약어 하나 하나가 사실상 비용이 드는 ‘자원’
• 이것을 효율적으로 굴리는 과정이 ‘데이터 설계’라고 할 수 있음

JOIN

저어어어엉말 중요 중요!

• 둘 이상의 테이블을 연결

SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition;

• table1과 table2가 갖는 같은 column을 연결

JOIN의 종류

• INNER JOIN(교집합): 테이블의 같은 걸 조인. 디폴트 조인. 가장 null 값이 적게 발생할 가능성
• LEFT JOIN: 왼쪽 기준으로 붙음, LEFT JOIN을 현업에서 가장 많이 씀
• RIGHT JOIN: 오른쪽 기준으로 붙음
• FULL JOIN(합집합): 두 테이블을 합해줌. 나머지 값은 Null. 가장 null 값이 많이 발생할 가능성

#과제 2번

SELECT e.EmployeeId, COUNT(c.CustomerId) as Customer_Count
FROM employees e 
JOIN customers c 
ON c.SupportRepId = e.EmployeeId 
GROUP BY e.EmployeeId

#과제 3번

SELECT a.Title as AlbumTitle, t.TrackId ,g.Name as GenreName
FROM tracks t
LEFT JOIN albums a ON
	a.AlbumId = t.AlbumId
LEFT JOIN genres g ON
	t.GenreId = g.GenreId 
GROUP BY a.AlbumId