Skip to content
BranchesTags

Repository files navigation

SQL Study (MySQL 기준 작성)

Chapter 1. 데이터 분석

DBMS와 SQL

  • 데이터베이스 : row & coulmn으로 이뤄진 table
  • DBMS : 데이터베이스 관리시스템
  • SQL : DBMS에 명령을 내리기 위해 사용하는 언어 (My SQL 사용예정)
  • My SQL : 페이스북, 유튜브 등을 비롯한 유명한 서비스에서도 활발히 사용되고 있는 DBMS (오픈소스)

DBMS와 서버-클라이언트 구조

  • DBMS는 주요 구성요소 2종류의 프로그램 有 : client를 통해 server에 접속해 원하는 명령을 내린다.
  1. client(클라이언트) : 사용자가 server에 접속해서 원하는 데이터베이스 관련 작업을 할 수 있도록, SQL을 입력할 수 있는 화면 등을 제공하는 프로그램
  2. server(서버) : client로부터 SQL 문 등을 전달받아 데이터베이스 관련 작업을 직접 처리하는 프로그램
  • MySQL에서 서버 프로그램 이름 = 클라이언트 프로그램 이름 = ‘mysqld’ → 보통 CLI 환경에서 사용하지만 GUI환경에서도 가능.
  • Oracle이 공식적으로 제공하는 GUI환경 : MySQL Workbench
  • sys : MySQL 서버의 성능 관련 정보들을 갖고있는 데이터베이스

데이터베이스 생성하기

  • 데이터 베이스를 만들어 주는 구문 create database copang_main

Primary key의 종류

  • primary key(기본키) : 테이블에서 하나의 ROW를 고유하게 식별할 수 있도록 해주는 column
  • Natural Key : 개체가 갖고 있는 속성을 나타내는 컬럼이 Primary Key가 됐을 때
  • Surrogate Key : 개체의 실제 속성은 아니지만 Primary Key로 쓰기 위해 추가한 컬럼 (주로 1부터 순차적으로 증가하는 숫자)
    ㄴ Auto Increment(AL) = 이전보다 1이 더 큰 정수를 자동으로 넣어주는 기능

NOT NULL의 의미

  • NOT NULL = NULL이 아니다. (NULL=값이 존재하지 않는 상태) = primary key는 NN이어야 한다.

데이터 조회로 기본 다지기

SELECT : 테이블의 데이터를 조회할 때 사용하는 구문

SELECT*FROMcopang_main.member; # "*" 는 asterisk로 all coulumn
SELECT email, age, address FROMcopang_main.member; # "FROM"은 ~로 부터

WHERE : 조건을 달아 찾을 때 쓰는 구문

SELECT*FROMcopang_main.memberwhere EMAIL ='taehos@hanmail.net'; 
SELECT*FROMcopang_main.memberwhere age >=27;
SELECT*FROMcopang_main.memberwhere age BETWEEN 30AND39; # 30, 39 포함 그 사이 숫자SELECT*FROMcopang_main.memberwhere age NOT BETWEEN 30AND39;
SELECT*FROMcopang_main.memberwhere sign_up_day >'2019-01-01';
SELECT*FROMcopang_main.memberwhere address LIKE'서울%'; # LIKE 처럼SELECT*FROMcopang_main.memberwhere address LIKE'%고양%';
SELECT*FROMcopang_main.memberwhere gender !='m'; # != 같지 않음SELECT*FROMcopang_main.memberwhere gender <>'m'; # <> 같지 않음SELECT*FROMcopang_main.memberwhere age in (20, 30); # IN 이 중에 있는SELECT*FROMcopang_main.memberwhere email LIKE'c_____@%'# LIKE 뒤의 언더바 하나=문자 하나

연도, 월, 일 추출하기

SELECT*FROMcopang_main.memberwhere year(birthday) ='1992'; # 1992년에 태어난 회원만 조회SELECT*FROMcopang_main.memberWHERE MONTH(sign_up_day) IN (6,7,8); # 여름(6, 7, 8월)에 가입한 회원들만 조회하기SELECT*FROMcopang_main.memberWHERE DAYOFMONTH(sign_up_day) BETWEEN 15AND31; # 각 달의 후반부(15일~31일)에 가입했던 회원들만 조회하기

날짜 간의 차이 구하기

SELECT email, sign_up_day, DATEDIFF(sign_up_day, '2019-01-01') FROMcopang_main.member; # 2019년 1월 1일~sign_up_day 차이 일 수SELECT email, sign_up_day, CURDATE(), DATEDIFF(sign_up_day, CURDATE()) FROMcopang_main.member; # 오늘 날짜 기준SELECT email, sign_up_day, DATEDIFF(sign_up_day, birthday) /365FROMcopang_main.member; # 몇살이었을 때 가입?

날짜 더하기 빼기

SELECT email, sign_up_day, date_add(sign_up_day, INTERVAL 300 DAY) FROMcopang_main.member; # 가입일(sign_up_day) 기준으로 300일 이후의 날짜SELECT email, sign_up_day, DATE_SUB(sign_up_day, INTERVAL 250 DAY) FROMcopang_main.member; # 가입일(sign_up_day) 기준 250일 이전의 날짜

UNIX Timestamp 값

SELECT email, sign_up_day, from_unixtime(UNIX_TIMESTAMP(sign_up_day)) FROMcopang_main.member; # DATE 타입의 값을 Unix Timestamp로 바꿔줌

여러 개의 조건 걸기

SELECT*FROMcopang_main.memberWHERE gender ='m'AND address LIKE'서울%'AND age BETWEEN 25and29;
SELECT*FROMcopang_main.memberWHERE (gender ='m'AND height >=180) OR (gender ='f'AND height >=170)

여러 조건을 걸 때 주의할 점

  1. OR를 사용할 때의 주의사항 - WHERE id = 1 OR id = 2 (O)
  2. AND와 OR 간의 우선순위 : AND가 우선 순위 이므로, 괄호를 꼭 쳐주자

문자열 패턴 매칭 조건을 사용할 때 주의할 점

  1. 이스케이핑(escaping) 문제 : \ 후 기재
  2. 대소문자 구분 문제 : BINARY '%g%' 소문자만 조회

데이터 정렬해서 보기

SELECT*FROM sales ORDER BY YEAR(sign_up_day) DESC, email ASC; # 이름을 먼저 쓴 컬럼을 우선으로 해서 정렬이 차례대로 수행SELECT*FROM sales ORDER BY CAST(registration_num AS signed); # 일시적으로 다른 데이터 타입으로 변경할 수 있게 해주는 함수

데이터의 일부만 추려보기

SELECT*FROMdb.search_result ~ ORDER BY registration_date DESCLIMIT0, 10

데이터의 특성 구하기

집계 함수 : 여러 row의 값들을 동시에 고려해서 실행되는 함수

SELECTCOUNT(email) FROMcopang_main.member; # 갯수SELECTMAX(height) FROMcopang_main.member; # 최대 값SELECTMIN(weight) FROMcopang_main.member; # 최소 값SELECTAVG(weight) FROMcopang_main.member; # 평균 값SELECTSUM(weight) FROMcopang_main.member; # 합계SELECT STD(weight) FROMcopang_main.member; # 표준편차

산술 함수 : 각 row의 값마다 실행되는 함수

SELECT ABS(weight) FROMcopang_main.member; # 절대값SELECT SQRT(weight) FROMcopang_main.member; # 제곱근SELECT CEIL(weight) FROMcopang_main.member; # 올림SELECT FLOOR(weight) FROMcopang_main.member; # 내림 SELECT ROUND(weight) FROMcopang_main.member; # 반올림

NULL 다루는 방법

SELECT*FROMcopang_main.memberWHERE address IS NULL; # NULL 값 조회SELECT*FROMcopang_main.memberWHERE address IS not NULL;# NULL 아닌 값 조회
WHERE height IS NULLOR weight IS NULLOR address IS NULL;
SELECT COALESCE(height, '####'), COALESCE(weight, '---'), COALESCE(address, '@@@')
FROMcopang_main.member; # COALESCE 는 결합한다는 뜻으로, NULL인 값을 채워넣어준다.

이상한 값을 제외하고 싶다면?

SELECTAVG(age) FROMcopang_main.memberWHERE age between 5and100;
SELECT*FROMcopang_main.memberWHERE address NOT LIKE'%호';
SELECT*FROMcopang_main.memberWHERE address LIKE'%호';

컬럼의 값 변환해서 보기

SELECT email,
CONCAT(height, 'cm', ',', weight, 'kg') AS'키와 몸무게',
weight / ((height/100) * (height/100)) AS BMI,
(CASE
WHEN weight IS NULLOR height IS NULL THEN '비만 여부 알 수 없음'
WHEN weight/((height/100) * (height/100)) >=25 THEN '과체중 또는 비만'
WHEN weight/((height/100) * (height/100)) >=18.5AND weight/((height/100) * (height/100)) <25 THEN '정상'
ELSE '저체중'
END) AS obesity_check
FROMcopang_main.member;

NULL을 다른 값으로 변환하는 다양한 함수

COALESCE 함수

SELECT COALESCE(height, 'N/A') FROMcopang_main.member;
SELECT COALESCE(height, weight *2.3, 'N/A') FROMcopang_main.member; # NULL이면 weight *2.3, 그래도 NULL이면 'N/A'

IFNULL 함수

SELECT IFNULL(height, 'N/A') FROMcopang_main.member; IF NULL이면, 'N/A'로 표시

IF 함수

SELECT IF(height IS NOT NULL, height, 'N/A') FROMcopang_main.member; # height가 not null이면, height 반환 아니면 'N/A'

CASE 함수

SELECT CASE WHEN height IS NOT NULL THEN height ELSE 'N/A'	END FROMcopang_main.member;

고유값만 보기

SELECT DISTINCT(gender) FROMcopang_main.member;
SELECT DISTINCT(SUBSTRING(address, 1, 2)) FROMcopang_main.member; # 첫번째 시작 2개만큼

문자열 관련 함수들

SELECT*, length(address) FROMcopang_main.member;
SELECT email, upper(email), lower(email) FROMcopang_main.member;
SELECT email, LPAD(age, 10, '0'), RPAD(age, 10, '!') FROMcopang_main.member;

그루핑해서 보기

SELECTSUBSTRING(address, 1,2) AS region, gender, COUNT(*)
FROMcopang_main.memberGROUP BYsubstring(address, 1, 2), gender
WITH ROLLUP HAVING region IS NOT NULLORDER BY region ASC, gender DESC;

SELECT 문의 입력 및 실행 순서

입력 순서

SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT

실행 순서

  1. FROM : 어느 테이블을 대상으로 할 것인지를 먼저 결정합니다.
  2. WHERE : 해당 테이블에서 특정 조건(들)을 만족하는 row들만 선별합니다.
  3. GROUP BY : row들을 그루핑 기준대로 그루핑합니다. 하나의 그룹은 하나의 row로 표현됩니다.
  4. HAVING : 그루핑 작업 후 생성된 여러 그룹들 중에서, 특정 조건(들)을 만족하는 그룹들만 선별합니다.
  5. SELECT : 모든 컬럼 또는 특정 컬럼들을 조회합니다. SELECT 절에서 컬럼 이름에 alias를 붙인 게 있다면, 이 이후 단계(ORDER BY, LIMIT)부터는 해당 alias를 사용할 수 있습니다.
  6. ORDER BY : 각 row를 특정 기준에 따라서 정렬합니다.
  7. LIMIT : 이전 단계까지 조회된 row들 중 일부 row들만을 추립니다.

테이블 간의 연결 고리

Foreign Key : '다른 테이블의 특정 row를 식별할 수 있게 해주는 컬럼'
ㄴ stock table 에서 item의 item_id 를 referenced Table로 지정해 준다.

다른 종류의 테이블 조인하기

SELECTi.id,
i.name,
s.item_id,
s.inventory_count# FROM item AS i RIGHT OUTER JOIN stock # 오른쪽 기준 조회# FROM item LEFT OUTER JOIN stock # 왼쪽 기준 조회FROM item AS i INNER JOIN stock AS s # 둘다 있는 것 조회ONi.id=s.item_id;

결합 연산과 집합 연산

SELECT*FROM member_A INTERSECT # A ∩ B (INTERSECT 연산자 사용)
MINUS # A - B (MINUS 연산자 또는 EXCEPT 연산자 사용)UNION# A U B (UNION 연산자 사용) OR UNION ALL (열이 생략되는 경우도 있기 때문)SELECT*FROM member_B

ON 대신 USING을 쓸 수도 있어요

ON old.id = new.id 와 USING(id)의 의미는 같다.

다른 3개의 테이블 조인해 의미있는 데이터 추출하기

SELECT
YEAR(i.registration_date) as'등록 연도',
COUNT(*) as'리뷰 개수',
AVG(r.star) as'별점 평균값'FROM
review as r
INNER JOIN item as i
ONr.item_id=i.idINNER JOIN member as m
ONr.mem_id=m.idWHEREi.gender='u'GROUP BY YEAR(i.registration_date)
HAVINGCOUNT(*) >=10ORDER BYAVG(r.star) DESC;

다른 종류의 조인들

  1. NATURAL JOIN - 같은 이름의 컬럼을 찾아서 자동으로 그것들을 조인 조건을 설정하고, INNER JOIN을 해주는 조인
  2. CROSS JOIN - 두 테이블의 Cartesian Product를 구하는 조인
  3. SELF JOIN - 테이블이 자기 자신과 조인을 하는 경우. 같은 테이블을 마치 별도의 테이블인 것처럼 간주하고 진행된다는 점
  4. FULL OUTER JOIN - LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과를 합치는 조인
  5. Non-Equi JOIN - 동등 조건이 아닌 다른 종류의 조건

서브쿼리란? 하위 데이터베이스에 보내는 요청

SELECTi.id, i.name, AVG(star) AS avg_star
FROM
item as i LEFT OUTER JOIN review as r
ONr.item_id=i.idGROUP BYi.id, i.nameHAVING avg_star < (SELECTAVG(star) FROM review) # 서브쿼리ORDER BY avg_star DESC;
SELECT*FROM item
WHERE id IN
(
SELECT item_id
FROM review
GROUP BY item_id HAVINGCOUNT(*) >=3# WHERE 절에 있는 서브쿼리
);

ANY(SOME), ALL

  1. ANY=SOME의 의미 - 하나라도 조건을 만족하는 경우가 있으면 True를 리턴한다
  2. ALL의 의미 - 모든 경우에 대해서 해당 조건이 성립해야 True를 리턴

FROM 절에 있는 서브쿼리

SELECTAVG(review_count)
FROM (SELECTSUBSTRING(address, 1, 2) AS region,
COUNT(*) AS review_count
FROM review AS r LEFT OUTER JOIN member AS m
ONr.mem_id=m.idGROUP BYSUBSTRING(address, 1, 2)
HAVING region IS NOT NULLAND region !='안드') AS review_count_summary;

EXISTS, NOT EXISTS와 상관 서브쿼리

  1. 비상관(Non_Correlated) 서브쿼리 : outer query와 상관 관계가 없는 서브쿼리
  2. 상관(Correlated) 서브쿼리 : outer query와 상관 관계가 있는 서브쿼리 (단독으로 실행 불가)
SELECT*,
(SELECTMIN(height) # 해당 row(의/들의) height 컬럼의 최솟값FROM member AS m2 WHERE birthday IS NOT NULLAND height IS NOT NULL# birthday & height 컬럼 둘다 값이 있는 회원들만AND YEAR(m1.birthday) = YEAR(m2.birthday)) # 같은 YEAR(birthday) ('생일연도') 값을 가진 row(를/들을) AS min_height_in_the_year
FROM member AS m1
ORDER BY min_height_in_the_year ASC;

서브쿼리 종합 정리

SELECTMAX(copang_report.price) AS max_price, AVG(copang_report.star) AS avg_star, COUNT(DISTINCT(copang_report.email)) AS distinct_email_count FROM (SELECT price, star, email FROM item AS i INNER JOIN review AS r ONr.item_id=i.idINNER JOIN member AS m ONr.mem_id=m.id)
AS copang_report;

서브쿼리로 더 간결해진 CASE 함수 내부

SELECT email, BMI,
(CASE
WHEN weight IS NULLOR height IS NULL THEN '비만 여부 알 수 없음'
WHEN BMI >=25 THEN '과체중 또는 비만'
WHEN BMI >=18.5AND BMI <25 THEN '정상'
ELSE '저체중'
END) AS obesity_check
FROM (SELECT*, weight/((height/100)*(height/100)) AS BMI FROM member) AS subquery_for_BMI
ORDER BY obesity_check ASC;

뷰 : 조인 등의 작업을 해서 만든 '결과 테이블'이 가상(물리적X)으로 저장된 형태

CREATEVIEWthree_tables_joinedAS# 뷰 생성SELECTi.id, i.name, AVG(star) AS avg_star, COUNT(*) AS count_star
FROM item AS i LEFT OUTER JOIN review AS r onr.item_id=i.idLEFT OUTER JOIN member AS m ONr.mem_id=m.idWHEREm.gender='f'GROUP BYi.id, i.nameHAVINGCOUNT(*) >=2ORDER BYAVG(star) DESC, COUNT(*) DESC;
SELECT*FROMcopang_main.three_tables_joinedWHERE avg_star = (
SELECTMAX(avg_star) FROMcopang_main.three_tables_joined
) AND count_star = (
SELECTMAX(count_star) FROMcopang_main.three_tables_joined
);

장점 : 사용자에게 높은 편의성(재활용 가능), 다양한 구조의 데이터 분석 기반을 구축 가능, 데이터 보안 제공

CREATE VIEW emp_view2 AS SELECT id, name, age, department FROM employee WHERE department != 'secret'; # 부서는 미조회 되게
CREATE VIEW v_emp AS SELECT id, name, age, department, phone_num, hire_date FROM employee
SELECT * FROM v_emp;

실무에서 해야할 일

  1. 존재하는 데이터 베이스 확인
SHOW DATABASES;
  1. 한 데이터베이스 안의 테이블(뷰도 포함)들 파악
SHOW FULL TABLES IN copang_main;
  1. 한 테이블의 컬럼 구조 파악
DESCRIBE item;
  1. Foreign Key(외래키) 파악 : 해당 회사의 데이터베이스를 설계한 분의 설명 or 본인이 직접 데이터의 관계 및 흐름을 파악

About

SQL 정리 (MySQL 기준)

Topics

mysqlsql

Resources

Readme
Activity

Stars

0 stars

Watchers

1 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors