SQL 기초와 데이터분석

1. SQL 학습목적

1. 원하는 형태로 데이터를 가져올 수 있다.
2. 효율적으로 데이터를 가져올 수 있다.
3. 간단한 데이터분석을 수행할 수 있다.

2. RDBMS / SQL

관계형 데이터베이스 (Relational DB : RDBMS)

• MySQL, SQL Server, PostgreSQL, ORACLE
• 사용목적
  • 정형 데이터 : 테이블/행/열로 이루어진 형태
  • Transaction처리 : 데이터의 일관성과 무결성이 중요한 서비스에서 사용
  • 예 : 은행의 금융서비스, 쇼핑몰 주문&정산
• 데이터 일관성
  • ACID(Atomicity, Consistency, Isolation, Durability)특성을 준수하여 데이터 일관성을 보장
• 스키마
  • 고정된 스키마 사용, 데이터 구조 변경이 어려울 수 있음
• 쿼리언어
  • SQL(Structured Query Language)을 사용하여 데이터를 가져오고 관리
    • SQL : 관계형 데이터베이스를 사용하기 위한 표준 언어
• 수평확장
  • 수평확장이 복잡하고 어려움

비관계형 데이터베이스 (Not Only SQL : NoSQL)

• Key-vale Store, Column-family, Document, Graph
• 사용목적
  • 비정형/반정형 데이터 : JSON, XML 등 다양한 형태
  • 대량의 데이터와 빠른 읽기/쓰기 : 대규모/분산 데이터 처리가 필요한 서비스에서 사용
• 데이터 일관성
  • DB마다 다르고, ACID특성을 보장하지 않는 경우도 있음
  • 중복이 발생
• 스키마
  • 스키마가 없는(또는 유연한 스키마) 데이터 모델을 사용하여 데이터 구조를 자유롭게 변경할 수 있음
• 쿼리언어
  • DB마다 고유한 쿼리 언어 또는 API를 제공하며, SQL과는 다른 방식으로 데이터를 질의
• 수평확장
  • 수평 확장이 상대적으로 용이

3. SQL 기초 구문

SQL서버 설치 없이 SQL문 사용할 수 있는 사이트 : http://sqlfiddle.com/

1. 데이터 조회

SELECT : 무엇을 가져올지 칼럼 선택
FROM : 어디에서(테이블) 컬럼을 가져올지
WHERE 어떤 조건으로 행을 가져올지

• WHERE 1=1 : 항상 참인 조건, 코드 작성에 편의성을 갖기 위해 코드를 사용함, AND 논리연산자이나 LIMIT 절을 효율적으로 사용하기 위해 사용하는 경우가 많다.

LIMIT : 제한 ex) LIMIT 2이면 상위 2개만 필터링
EX
SELECT name, price
FROM products
WHERE price > 5000
➡️ price컬럼에서 price 값이 5000 초과인 데이터를 products 테이블에서 name, price 컬럼을 추출해라

2. 비교 연산자, 논리 연산자

비교연산자

• = (같다), >(초과),<(미만),>=(이상),<=(이하), <>(같지않다), != (같지 않다)
• 조건절(WHERE)에서 사용

논리연산자

• AND (두개가 참)
• OR (하나만 참이여도 참)
• NOT (부정)
  • SELECT * FROM products WHERE 1=1 AND NOT category='키즈' : category가 키즈인거 제외하고 출력
• IN (포함되는 것)
  • SELECT product_id, name FROM products WHERE 1=1 AND category IN ('스포츠','디지털','식품') : category에서 '스포츠','디지털','작품'인 데이터만 출력
• NOT IN (포함되지 않는것)
  • SELECT product_id, name FROM products WHERE 1=1 AND category NOT IN ('스포츠','디지털','식품') : category에서 '스포츠','디지털','작품'인 데이터 제외하고 출력
• LIKE (문자열 사용해서 문자열 포함하는 데이터 출력)
  • SELECT product_id, name FROM products WHERE 1=1 AND name LIKE '%보호%' : name칼럼에서 '보호'라는 단어 포함된 데이터 출력 (손목보호대)
  • SELECT product_id, name FROM products WHERE 1=1 AND name LIKE '밥%' : name칼럼에서 '밥'으로 시작하는 단어 데이터 출력 (밥그릇)
  • SELECT product_id, name FROM products WHERE 1=1 AND name LIKE '__그릇' : name칼럼에서 '그릇'으로 끝나는 단어 데이터 출력 (밥그릇)
• BETWEEN
  • SELECT name, price FROM products WHERE 1=1 AND price between 5000 and 15000
    : price가 5000에서 15000사이인 데이터 출력 (5000, 15000포함)
• IS NULL
  • SELECT * FROM orders WHERE 1=1 AND purchase_date IS NULL : purchase_date의 값이 null값인 데이터 출력

3. 정렬과 집계

ORDER BY

• SELECT * FROM products WHERE 1=1 AND price > 5000 ORDER BY price : price값이 5000 초과인 데이터를 price 기준으로 오름차순 정렬
• SELECT * FROM products WHERE 1=1 AND category in ('주방용품','디지털') ORDER BY price DESC : category가 '주방용품','디지털'인 데이터를 price 기준으로 내림차순 정렬
• SELECT * FROM products WHERE ODDER BY price DESC, name : price 기준으로 내림차순, 이름 기준으로 오름차순 정렬
• SELECT price, name FROM product_v2 WHERE 1=1 ORDER BY 1 DESC, 2 : price 기준으로 내림차순, 이름 기준으로 오름차순

전체집계

• SELECT sum(price) as sum_price, avg(price) as avg_price FROM product_v2 WHERE 1=1 : price를 모두 합한 데이터를 sum_price 칼럼으로 , price의 평균을 구한 데이터를 avg_price 칼럼으로 출력해라
• SELECT count(1) as cnt FROM product_v2 WEHRE 1=1 : product_v2의 테이블 데이터 행의 개수를 cnt칼럼으로 출력해라
• SELECT count(distinct price) as unique_price_cnt FROM products_v2 WHERE 1=1 : products_v2 테이블에서 price가격이 다른 데이터(중복X)의 개수를 unique_price_cnt로 출력해라
• SELECT count(1) as some_cnt FROM products_v2 WHERE 1=1 AND category like '%용품' : category 칼럼에서 ~용품으로 끝나는 데이터의 개수를 some_cnt로 출력해라
• SELECT category, count(1) as sales_cnt FROM product_v3 WHERE 1=1 AND sale_yn = 'yes' GROUP BY 1 ORDER BY 2 DESCT, 1 : category별로 그룹화하고 그룹별로 count한 데이터를 sales_cnt로 내림차순 출력, categroy로 오름차순 출력
• SELECT category, sale_yn, avg(price) as avg_price FROM products_v3 WHERE 1=1 GROUP BY 1,2 HAVING avg_price > 3000 : products_v3 테이블에서 category, sale_yn으로 그룹화하고 그룹화한 데이터에서 price의 가격 평균값을 avg_price로 출력하되, 3000 초과인 데이터만 출력해라

4. 기초 SQL 함수

UPPER 대문자로, LOWER 소문자로 변경

• SELECT UPPER('John') as up, LOWER('JOHN') as low : UPPER('John') ➡️ JOHN, LOWER('JOHN') ➡️ john으로 출력

CONCAT

• SELECT CONCAT(category,'-',name) as comb_name FROM products WHERE 1=1 LIMIT 3 : category 컬럼의 데이터 + '-' + name 컬럼의 데이터 합한 데이터를 comb_name 컬럼으로 출력하되 상위 3개까지 출력 (ex : 키즈-어린이칫솔)

SUBSTRING

• SELECT SUBSTRING(name, 1, 2) as sub_string FROM products WHERE 1=1 : name컬럼의 데이터에서 1,2번째 단어만 sub_string으로 출력해라 (ex : 어린이 칫솔 ➡️ 어린)

CHAR_LENGTH

• SELECT CHAR_LENGTH(name) as name_length FROM products : name칼럼 데이터의 길이를 name_length로 출력해라 (ex 어린이 칫솔 ➡️ 5)

ROUND

• SELECT ROUND(avg(price)) as avg_price FROM products_v2 WHERE 1=1 : price 칼럼의 데이터 평균을 정수자리까지 반올림해서 avg_price로 출력해라
• CEIL : 숫자 올림
• FLOOR : 숫자 내림
• TRUNCATE : 숫자 버림

ABS : 절댓값

• SELECT ABS(-1.0) as res, ABS(1) as res_2, ABS(price) as abs_price FROM products_v2 LIMIT 3

MOD : 나머지

• SELECT MOD(price, product_id) as mods FROM products_v2 : price 가격을 product_id로 나눈 나머지를 mods로 출력해라
• POW : 제곱 (mysql)
  • SELECT POW (2,4) : 16
• GREATEST : 최댓값 리턴 (오라클)
  • SELECT GREATEST(1,2,3,4,5,6,7,2) FROM DUAL : 7 출력
• LEAST : 최소값 리턴 (오라클)
  • SELECT GREATEST(1,2,3,4,5,6,7,2) FROM DUAL : 1 출력

COALESCE : null값 변환

• SELECT COALESCE(purchase_date, 'Unknown') as purchase_date FROM orders : purchase컬럼에서 null값인 데이터를 null에서 'Unknown'으로 바꿔 출력

5. DDL, DML

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

• CREATE : 테이블/뷰/인덱스 생성
• ALTER : 테이블/뷰/인덱스 구조 변경
• DROP : 테이블/뷰/인덱스 삭제
  2. DML(Data Manipulation Language) : 데이터를 쿼리하거나 조작
• SELECT : 질의
• INSER : 삽입
• UPDATE : 수정
• DELETE : 삭제