[데브코스][데이터 분석] RDMS과 SQL 기초 문법

< 4주차 SQL 쿼리 문법을 활용하여 데이터 분석을 진행하기 (1) >

1. SQL과 RDB란 무엇인가

1-1. SQL 학습 목적

: 데이터로 다양한 Data Product를 만들기 위해 Database에서 데이터를 가져와야 함

-> 이때 사용되는 언어가 SQL

(Data Product란? 분석 보고서 혹은 머신러닝 모델 등 데이터 과학자/분석가가 만들어내는 다양한 모든 산출물)

 

1) 원하는 형태로 데이터를 가져올 수 있다. 

2) 효율적으로 데이터를 가져올 수 있다. 

3) 간단한 데이터 분석을 수행할 수 있다.  -> 보통 데이터만 적재하고 나머지는 P 등으로 분석하는데, SQL으로 간단하게 데이터를 분석할 수도 있음

 

1-2. RDBMS / SQL 개요

데이터베이스: 데이터를 쌓아두는 곳 & 관리시스템

- 관계형 데이터베이스 = RDBMS

: 표 형태의 데이터베이스

ex) MySQL, Oracle 등

 

- 비관계형 데이터베이스 = NoSQL

: Key-Value Store, Column-family, Document, Graph

-> DB마다 데이터를 쌓는 형태가 다양함. 기준이되는 키를 통해 대응되는 값에 접근하는 형태

ex) 카싼드라, MongoDB 등

 

구분	관계형 데이터베이스(RDBMS)	비관계형 데이터베이스(NoSQL)
사용 목적	- 정형 데이터 : 테이블/행/열로 이루어진 형태 (표와 같은 형태) - Transaction 처리 : 데이터의 일관성과 무결성이 중요한 서비스에서 사용. (Transaction : DB상태를 변환시키는 상태변화) - 예> 은행의 금융서비스, 쇼핑몰 주문 & 정산	- 비정형/반정형 데이터 : JSON, XML 등 다양한 형태 - 대량의 데이터와 빠른 읽기/쓰기 : 대규모/분산 데이터 처리가 필요한 서비스에서 사용
데이터 일관성	- ACID (Atomicity, Consistency, Isolation, Durability) 특성을 준수하여 데이터 일관성을 보장	- DB마다 다르고, ACID 특성을 보장하지 않는 경우도 있음 - 중복이 발생
스키마	- 고정된 스키마(데이터의 타입) 사용. 데이터 구조 변경이 어려울 수 있음	- 스키마 없는 (또는 유연한 스키마) 데이터 모델을 사용하여 데이터 구조를 자유롭게 변경할 수 있음
쿼리 언어	- SQL(Structured Query Language)을 사용하여 데이터를 가져오고 관리	- DB마다 고유한 쿼리 언어 또는 API를 제공하며, SQL과는 다른 방식으로 데이터를 질의
수평 확장	- 수평 확장이 복잡하고 어려움	- 수평 확장이 상대적으로 용이

 

SQL

: 관계형 데이터베이스를 사용하기 위한 표준 언어

: 테이블, 행, 열로 이루어짐. 

: 행은 가로, 열은 세로

: 테이블은 하나 이상의 열과 행으로 이루어지고, 모든 데이터가 테이블에 저장됨. 

-> 행과 열이 관계를 맺고 있음

 

2. SQL 기초 구문

2-1. 실습 환경 안내

SQL 코드 학습을 위한 실습 환경을 만들어보자! 

SQL 서버를 직접 띄우는 방법을 다소 복잡하기에 웹으로 실습을 해보겠다! 

 

1) SQL을 실습하기 위한 웹사이트에 이동한다. 

https://www.db-fiddle.com/

DB Fiddle - SQL Database Playground

위의 사이트말고도 다양한 사이트가 있다. 검색 등을 통해 원하는 사이트에서 실습해보자! 

https://www.jdoodle.com/execute-sql-online/

https://sqliteonline.com/

 

2) 원하는 DDL/DML 코드를 붙여넣어 원하는 스키마(테이블)을 만든다.  

DDL (Data Definition Language) 이란?
: 데이터 정의어 / 데이터베이스를 정의하는 언어를 말하며, 데이터를 생성, 수정, 삭제하는 등 데이터의 전체 골격을 결정하는 역할의 언어 (CREATE, ALTER, DROP, TRUNCATE)

DML (Data Manipulation Language) 이란?
: 데이터 조작어 / 정의된 데이터베이스에 입력된 레코드를 조회, 수정, 삭제하는 등의 역할을 하는 언어. 
테이블의 행과 열을 조작하는 언어 (SELECT, INSERT, UPDATE, DELETE) 

 

3) 원하는 SQL 쿼리문을 입력해주고, "달리다" 버튼을 눌러주면 원하는 결과가 나오게 된다! 

 

4) 원하는 SQL 쿼리문을 바꿔가며 실습을 해보고 즉각적으로 결과를 확인할 수 있다!

 

 

2-2. 데이터 조회 : SELECT, FROM, WHERE

SELECT

: 무엇을 가져올지 지정하는 표현

즉, 테이블에서 원하는 컬럼을 선택함. 

테이블이 클 때, *를 통해 모든 컬럼을 가져오면 리소스가 매우 커짐.

-> 따라서 실무에서는 필요한 컬럼만을 지정하여 사용하는 것을 권장함

 

FROM

: 어디에서 가져올지 지정하는 표현

즉, 테이블 이름을 지정해줌

-- products 테이블에서 모든 컬럼을 가져오는 구문
SELECT *
FROM products;

 

WHERE

: 어떤 조건으로 가져올지 지정하는 표현

즉, 필터링하는 구문. 조건문이라고 생각, 행을 선택하기 위한 목적

-- where절의 조건을 "무조건 참인 조건"으로 지정하였음
-- 코드 작성의 편리함을 위해 사용 -> where 뒤에 이어질 조건들을 쉽게 삭제, 추가하기 위해 사용
SELECT product_id, category
FROM products
WHERE 1=1;

 

-- where절의 조건을 1=1로 지정한 예시
SELECT product_id, category
FROM products
WHERE 1=1
AND category = '주방용품'
AND price > 5000;


-- 만약 1=1 조건이 없으면, 첫 조건을 수정, 삭제할 때 번거로워짐
SELECT product_id, category
FROM products
WHERE 1=1
-- AND category = '주방용품' # 해당 조건 삭제
AND price > 5000;

 

LIMIT

: 몇 개의 데이터를 가져올 것인지 정의 (MySQL 기준)

-- 상위 2개의 행만 필터링하여 가져옴
SELECT product_id, category
FROM products
WHERE 1=1
AND category = '주방용품'
AND price > 5000
LIMIT 2

 

2-3 (1). 비교 연산자

등호 연산자  : =

: 양쪽의 값이 같은지 비교

-- category가 주방용품과 같은 테이블만 가져옴
SELECT product_id, category
FROM products
WHERE 1=1
AND category = '주방용품';

 

부등호 연산자 : >, <, >=, <=

-- 부등호를 통해 조건을 설정해줌
SELECT product_id, category
FROM products
WHERE price > 5000;

 

비교 연산자 : <>, !=

서로 같지 않은 것을 뽑아온다. 

-- category가 키즈가 아닌 행을 뽑아옴
SELECT product_id, name
FROM products
WHERE 1=1
AND category <> '주방용품';

 

2-3 (2). 논리 연산자

논리 연산자 : AND

: 두 개 이상의 조건이 모두 참일 때만 참을 반환함

-- price가 10000 미만이고, category가 주방용품인 행을 뽑아옴
SELECT name, price
FROM products
WHERE 1=1
AND price < 10000
AND category == '주방용품';

 

논리 연산자 : OR

: 여러 조건 중 하나만 참이여도 참을 반환함

-- price가 12000 미만이고, category가 주방용품이거나 name이 손목보호대인 행을 뽑아옴
SELECT name, price
FROM products
WHERE 1=1
AND price < 12000
AND (category == '주방용품'
	OR name = '손목보호대');

 

논리 연산자 : NOT

: 참, 거짓을 뒤집는데 사용함

-- category가 키즈가 아닌 행을 뽑아옴
SELECT name, price
FROM products
WHERE 1=1
AND NOT category = '키즈';

 

논리 연산자 : IN

: 좌측값이 우측 괄호 안에 포함될 때만 참을 반환함

-- 카테고리 값이 스포츠, 디지털, 식품에 포함되는 행들만 반환함
SELECT name, price
FROM products
WHERE 1=1
AND category IN ('스포츠', '디지털', '식품');

 

논리 연산자 : NOT IN

: 좌측값이 괄호 안에 포함되지 않은 행만 반환

-- 카테고리 값이 스포츠, 디지털, 식품에 포함되지 않는 행들만 반환함
SELECT name, price
FROM products
WHERE 1=1
AND category NOT IN ('스포츠', '디지털', '식품');

 

논리 연산자 : LIKE

: 문자열의 패턴을 검색하는데 사용

-- % 기호는 '모두'라는 의미
-- 보호라는 문자열을 포함하고 있으면 모두 반환
SELECT name, price
FROM products
WHERE 1=1
AND name LIKE '%보호%';

 

-- off 컬럼 중 '일'이라는 문자열이 포함된 행 반환
SELECT name, price
FROM products
WHERE 1=1
AND off LIKE '%일%';

 

-- name 컬럼 중 '밥'으로 시작하고, 밥 뒤에는 어떤 문자열이 오든 상관없는 행 반환
SELECT name, price
FROM products
WHERE 1=1
AND name LIKE '밥%';

 

-- _는 한 개의 문자열이라는 의미. 즉, 그릇이라는 문자열 앞에 단 하나의 문자열이 오는 경우만 반환
-- _ 개수만큼의 문자열이 따라와야함
SELECT name, price
FROM products
WHERE 1=1
AND name LIKE '_그릇';

 

논리 연산자 : BETWEEN

: 범위 사이에 위치한 값을 반환함(양쪽 끝 값을 포함함)

-- 가격이 5000원 이상, 15000원 이하인 값 반환. (끝 값을 포함함)
SELECT name, price
FROM products
WHERE 1=1
AND price between 5000 and 15000;

 

-- 2023년 10월 1일부터 2023년 10월 3일까지의 구매를 추출함
SELECT *
FROM orders
WHERE 1=1
AND purchase between '20231001' and '20231003';

 

논리 연산자 : IS NULL

: 컬럼값이 비어있는지 검사함. 

-- purchase가 null인 행만 추출
SELECT *
FROM orders
WHERE 1=1
AND purchase IS NULL;

 

-- purchase가 null이 아닌 행만 추출
SELECT *
FROM orders
WHERE 1=1
AND purchase IS NOT NULL;

 

 

2-4. 정렬과 집계 : ORDER BY, GROUP BY

ORDER BY

: 특정 열을 기준으로 데이터를 오름차순 혹은 내림차순으로 정렬

-- price를 기준으로 오름차순 정렬
SELECT *
FROM products
WHERE 1=1
AND price > 5000
ORDER BY price;

 

-- price를 기준으로 내림차순 정렬
SELECT *
FROM products
WHERE 1=1
AND price > 5000
ORDER BY price DESC;

 

-- name를 기준으로 오름차순 정렬 (가나다순)
SELECT *
FROM products
WHERE 1=1
ORDER BY name;

 

-- price를 기준으로 내림차순 우선 정렬, 같은 값이 있으면 name으로 오름차순 정렬
SELECT price, name
FROM products_v2
WHERE 1=1
ORDER BY price DESC, name;

 

-- 컬럼명 대신 숫자를 사용. 1은 최종 결과의 첫 번째 컬럼, 2는 최종 결과의 두 번째 컬럼
-- select 구문을 통해 지정했기 때문에 숫자 표현 사용 가능
-- price를 기준으로 내림차순 우선 정렬, 같은 값이 있으면 name으로 오름차순 정렬
SELECT price, name
FROM products_v2
WHERE 1=1
ORDER BY 1 DESC, 2;

 

전체 집계

: 여러 행으로부터 하나의 결과값을 반환함

ex. 평균, 합계, 최대, 최소 등

-- price의 합과 price의 평균을 추출함
SELECT sum(price) as sum_price, avg(price) as avg_price
FROM products_v2
WHERE 1=1;

 

-- 전체 count를 셈
-- count(1) = count(*) : 모든 행의 개수를 셈(null값 상관 X)
-- count(price) : null을 제외한 모든 행의 개수를 가져옴
SELECT count(1) as cnt
FROM products_v2
WHERE 1=1;

 

-- distinct를 통해 해당 컬럼의 중복이 없는 개수를 셈
SELECT count(distinct price) as unique_price_cnt
FROM products_v2
WHERE 1=1;

 

-- where 이하의 조건을 만족하는 행들의 count를 셈
SELECT count(1) as some_cnt
FROM products_v2
WHERE 1=1
AND category LIKE '%용품';

 

GROUP BY

: 일부 그룹으로 묶어 집계함수 사용 가능

-- select에 그룹으로 묶을 기준이 되는 열을 먼저 써줌. 
-- 카테고리별 상품 수를 셈. 첫 번째 컬럼(카테고리)를 기준으로 그룹화함
SELECT category, count(1) as sales_cnt
FROM products_v3
WHERE 1=1
AND sale_yn = 'yes'
GROUP BY 1
ORDER BY 2 DESC, 1;

 

HAVING

: WHERE과 유사하지만, where은 그룹화를 하기 전의 필터링, having은 그룹화 이후를 필터링하는 함수

-- category를 기준으로 먼저 그룹화를 하고, avg_price가 3000이상인 값만 반환함
SELECT category, sale_yn, avg(price) as avg_price
FROM products_v3
WHERE 1=1
GROUP BY 1, 2
HAVING avg_price > 3000;

 

 

2-5. 기초 SQL 함수

CONCAT

: 문자열을 다루는 함수. 

: 여러 컬럼의 문자열 값들을 하나의 컬럼으로 합침

-- 카테고리와 상품이름을 합쳐 comb_name으로 반환
SELECT CONCAT(category, '-', name) as comb_name
FROM products_v3
WHERE 1=1
LIMIT 3;

 

문자열도 아닌 값도 사용 가능(공백, 숫자 등 모두 가능!)

-- 카테고리와 상품이름을 합쳐 comb_name으로 반환
-- 카테고리와 name을 공백을 통해 합침
SELECT CONCAT(category, ' ', name) as comb_name
FROM products_v3
WHERE 1=1
LIMIT 3;

-- 카테고리와 상품이름을 합쳐 comb_name으로 반환
SELECT CONCAT(name, '의 가격은 ', price, '입니다. ') as comb_name
FROM products_v3
WHERE 1=1
LIMIT 3;

 

SUBSTRING

: 문자열에서 일부부만 추출하는 함수

문자열을 잘라 부분만을 반환함. INPUT으로 3개를 받음

SUBSTRING(컬럼명, 시작 위치, 추출 길이)

-- name 컬럼의 첫 번째 값부터 길이 2만큼을 추출함
-- ex. 어린이칫솔 -> 어린 / 손목보호대 -> 손목
SELECT SUBSTRING(name, 1, 2) as sub_string
FROM products_v3
WHERE 1=1;

 

LEFT

: 왼쪽으로부터 문자열을 잘라서 추출하는 함수

LEFT(컬럼명, 추출할 개수)
-- ex. 어린이칫솔 -> 어린이 / 손목보호대 -> 손목보

 

RIGHT

: 오른쪽으로부터 문자열을 잘라서 추출하는 함수

RIGHT(컬럼명, 추출할 개수)
-- ex. 어린이칫솔 -> 이칫솔 / 손목보호대 -> 보호대

 

UPPER

: 영어를 대문자로 통일

SELECT UPPER('John') as up
-- 결과 : JOHN

 

LOWER

: 영어를 소문자로 통일

SELECT LOWER('John') as low
-- 결과 : john

 

CHAR_LENGTH

: 문자열의 길이를 반환

-- name열의 문자열 길이를 반환함
SELECT CHAR_LENGTH(name) as name_length
FORM products

 

ROUNT

: 반올림을 하는 함수

-- 가격 평균을 반올림하여 정수형으로 반환함
SELECT ROUNT(avg(price)) as avg_price
FORM products

 

- ROUND(컬럼명) : 소수점 1번째 자리에서 반올림

- ROUND(컬럼명, n) : 몇 번째 자리까지 표시할 것인지 지정

- ROUND(컬럼명, -1) : 10단위로 반올림( ex. 123 -> 120 )

 

CEIL

: 올림함수

 

TRUNCATE

: 버림

- TRUNCATE(컬럼명, 1) : 반드시 버릴 자릿수 명시 필요

 

FLOOR

: 버림

- FLOOR(컬럼명) : 소수점 아래 모두 버림

 

ABS

: 절댓값 함수

 

MOD

: 나머지를 구하는 함수

: 특정 숫자의 배수를 구하는 경우 사용 가능

MOD(분자, 분모)

 

POW

: X의 Y승을 반환

 

GREATEST

: 최댓값 반환

 

LEAST

: 최솟값 반환

 

COALESCE

: null 값을 채우기 위해 사용

빈 값을 특정한 값 혹은 컬럼으로 채울 수 있음

-- purchase_date의 null 값을 Unknown으로 지정
SELECT COALESCE(purchase_date, 'Unknown') as purchase_date
FROM orders;

 

-- purchase_date의 null 값을 같은 행의 product_name으로 채움
SELECT COALESCE(purchase_date, product_name) as purchase_date
FROM orders;

 

 

2-6. (Optional) DDL, DML

DDL (Data Definition Language) : 데이터베이스 구조를 정의하고 관리

1. CREATE : 테이블/뷰(가상 테이블)/인덱스(색인) 생성

2. ALTER : 테이블/뷰/인덱스 구조 변경

3. DROP : 테이블/뷰/인덱스 삭제

 

DML (Data Manipulation Language) : 데이터를 쿼리하거나 조작

1. SELECT : 질의

2. INSERT : 삽입

3. UPDATE : 수정

4. DELETE : 삭제

 

CREATE

: 테이블을 생성함

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(6) unsigned NOT NULL,
  `category` varchar(40) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` int(6) unsigned NOT NULL,
  PRIMARY KEY (`product_id`)
) DEFAULT CHARSET=utf8;

 

- IF NOT EXISTS : 해당 테이블이 존재하지 않는 경우에 새롭게 테이블을 생성한다는 의미

- 컬럼명에서 컬럼 이름, 컬럼 타입, null 값이 들어가도 되는지 정의함

- unsigned : 음수를 쓰지 않는다는 의미. 음수를 할당하지 않기 때문에 더 큰 숫자 할당 가능

- varchar : 가변 길이의 문자열. 작성된 문자열에 따라 공간을 할당함. 문자열 길이를 기록함

- char : 정해진(고정된) 길이만큼의 문자열이 저장될 수 있음. 문자열이 짧아도 공백으로 저장됨

출처) 프로그래머스 데이터분석 2기 수업자료 중 일부

 

- PRIMARY KEY :  기본키. 테이블 내의 모든 행에 적용되는 고유한 아이디. 행마다 모두 다른 값이여야 함. 

- DEFAULT CHARSET=utf8; : 테이블의 기본 문자셋 지정. (유니코드 문자셋 -> 한국어) 

 

 

ALTER

: 컬럼 삭제, 추가, 이름변경, 타입변경 등을 수행할 수 있다. 

-- 컬럼 삭제
ALTER TABLE products drop column price;

-- 컬럼 추가
-- price가 원래는 NOT NULL이었는데, NULL값이 올 수 있는 정수 타입으로 바꾸어줌
ALTER TABLE products add price int NULL;

 

DROP

:삭제

-- products라는 테이블 삭제
DROP TABLE products;

 

INSERT

: 테이블을 만든 후 데이터 삽입

INSERT INTO `products` (`product_id`, `category`, `name`, `price`) VALUES
(0, '가전', '에어컨', 1500),
(1, '가전', '냉장고', 10000),
(2, '컴퓨터', '노트북', 2000),
(3, '디지털', '마우스', 1500);

 

UPDATE

: 테이블에 삽입된 값을 수정

-- 가격이 15000원인 행을 찾아 가격을 20000원으로 바꿈
SELECT products
SET price = 20000
WHERE price = 15000;

 

DELETE

: 테이블내에서 특정 레코드를 삭제

-- 가격이 15000원인 행을 삭제
DELETE FROM products
WHERE price = 15000;

 

---

💻 2024년 3월 4일 월요일 TIL 후기
지금까지 배웠던 것은 모두 이미 배웠던 내용이라 쉽게 수강하고 있다. 
더 심화적이고 어려운 내용을 배워보고 싶다! 
그래도 다시 한 번 이론을 탄탄히하니 도움은 많이 될 듯 하다. 
남는 시간에는 코딩테스트 문제를 풀어야겠다.