패스트캠퍼스 데이터분석 부트캠프 학습일지3

https://fastcampus.co.kr/

패스트캠퍼스 [직장인 실무교육]

 

3주차: SQL

오리엔테이션

데이터: 컴퓨터가 처리할 수 있는 정보

데이터베이스: Data Base, 여러 사람이 공유하고 사용할 목적으로 통합 관리되는 데이터 저장소

- 특징 : 자료를 구조화해 저장하기 때문에 효율적인 관리 가능, 여러 업무에 여러 사용자가 동시에 사용 가능, 사용자가 DB기능을 사용하기 위해서는 응용 프로그램 활용 필요

- DBMS: 데이터 베이스의 기능을 제공하는 프로그램. 사용자는 반드시 DBMS를 통해서만 데이터에 접근할 수 있음. 종류로는 Oracle, MySQL, SQL Server, MariaDB등(모두 RDBMS)이 있으나 비슷비슷함(세부적인 것만 좀 다름)

    - SQL: Structured Query Language. 데이터베이스와 대화하기 위해 사용하는 약속의 언어. 데이터베이스 활용을 위해서 이에 맞는 문법 적용이 필요함. 여기서 Query란 SQL로 쓰인, 데이터베이스에 명령을 내리는 문장

    - RDBMS: 데이터의 일관성과 정합성 우선 / NoSQL: 대용량 데이터를 빠른 속도로 처리하는 것 우선

 

데이터베이스 다루기

데이터 타입: MySQL에서는 정수형, 실수형, 문자형, 날짜형 데이터를 많이 사용

- MySQL에서는 데이터를 저장하기 전에 저장 공간의 데이터 타입을 미리 정해주어야 하며, 맞는 데이터 타입이 아닌 경우 데이터 저장이 불가능함

- 바이트 수에 따라 데이터 타입이 나뉘므로, 범위에 맞는 데이터 타입을 지정해 주어야 함

- 정수형: TINYINT(-128~127, 1바이트), SMALLINT(-32768~32767, 2바이트), MEDIUMINT(-838백만~838백만, 3바이트), INT(-21억~21억, 4바이트), BIGINT(-900경~900경, 8바이트)

- 실수형: FLOAT(소수점 아래 7자리, 4바이트), DOUBLE(소수점 아래 15자리, 8바이트)

- 문자형: CHAR(1~255의 n지정, 최대 255바이트, 지정 안 할 시 1, 고정 길이로 문자열 저장), VARCHAR(1~65535의 n지정, 최대 65535바이트, 지정 안 할 시 사용 불가, 변동 길이로 문자열 저장), TINYTEXT(고정 바이트 수 255), TEXT(고정 바이트 수 65535), MEDIUMTEXT(고정 바이트 수 1600만), LONGTEXT(고정 바이트 수 42억)

    - CHAR(5), VARCHAR(5)에 ABC라는 세 글자만 줄 경우 CHAR에선 'A,B,C,공백,공백'이 되나 VARHCAR에는 'A,B,C'만 있음(필요한 만큼만 사용)

- 날짜형: DATE(날짜만), DATETIME(날짜+시간), TIME(시간만), YEAR(연도만)

- 숫자형 데이터는 수이므로 데이터 간 연산이 가능하며, 문자형 데이터는 반드시 ""나 ''와 함께 쓰여야 함(따옴표가 없으면 키워드, 함수, DB/테이블/컬럼 이름으로 인식되기 때문). 특정 함수를 사용해 서로 간의 형변환 가능(CAST, CONVERT, 등)

 

테이블: 데이터베이스에서 데이터의 형태르 정해 모아놓은 저장공간. 행과 열로 이루어진 데이터 표

- 컬럼(열): 데이터를 저장하기 위한 틀. 1)컬럼 이름과 데이터 타입은 테이블을 만들 때 미리 정함 2)컬럼의 이름은 동일 테이블 내에서 중복될 수 없음 3)테이블은 반드시 1개 이상의 컬럼을 가져야 함

- 로우(행): 관계된 값의 리스트. 1)하나의 로우는 하나의 관계된 데이터를 의미 2)같은 테이블 안에서 로우는 항상 동일한 구조를 가짐 3)로우를 단위로 데이터 삽입

데이터베이스: (광의)여러 사람이 공유하고 사용할 목적으로 통합 관리되는 데이터 저장소, (협의)테이블을 저장해두는 저장소. 스키마.

- 보통 데이터베이스 생성 -> 데이터베이스 내 테이블 생성. 폴더를 먼저 만들고 그 안에 들어갈 파일을 만드는 형식

- CREATE DATABASE DB명(컬럼명 데이터타입, 컬럼명 데이터타입 ...); / SHOW DATABASES; / USE DB명; ::컬럼명과 데이터타입을 갖는 DB만들기 / (존재하는 모든)DB 보여주기 / DB를 사용하겠다(DB사용 선언 후 테이블을 불러와야 함)

    - 쿼리 끝에는 항상 세미콜론을 붙여주어야 하며, 쿼리를 만들 때 사용하는 키워드(함수)는 약속임

    - 쿼리 가독성을 위해 키워드나 함수명은 대문자, 사용자가 정의한 이름에는 소문자 사용(기능적으로는 대소문자 구분 x)

- ALTER TABLE 테이블명 RENAME 새테이블명; / ALTER TABLE 테이블명 ADD COLUMN 컬럼명 컬럼타입; / ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 새컬럼타입; / ALTER TABLE 테이블명 CHANGE COLUMN 컬럼명 새컬럼이름 새컬럼타입; / ALTER TABLE 테이블명 DROP COLUMN 컬럼명; ::기존 테이블명을 새 테이블명으로 바꿈 / 테이블에 새 컬럼을 만듦 / 기존 컬럼을 다른 타입으로 바꿈 / 기존 컬럼의 이름과 타입 모두 바꿈 / 테이블에서 컬럼 드랍

- DB, 테이블, 컬럼 이름 규칙 1)문자, 숫자, _사용 2)이름에 쓰이는 문자는 주로 영문 소문자 3)예약어 사용 불가 4)단어와 단어 사이엔 빈칸 대신 _사용 5)문자로 시작 6)DB이름은 중복될 수 없음

    - 한글도 사용 가능하나 인코딩 이슈로 인해 잘 사용하지 않음

    - 예약어: 키워드, 함수명 등 문법적인 용도로 사용되고 있어 이름으로 사용할 수 없는 단어

    - 테이블 이름도 하나의 DB내에서 중복될 수 없으며, 컬럼 명도 하나의 테이블 내에서 중복될 수 없음

- DROP DATABASE DB명; / DROP TABLE 테이블명; / TRUNCATE TABLE 테이블명; / DROP DATABASE IF EXISTS DB명; / DROP TABLES IF EXISTS 테이블명; ::DB드랍 / 테이블 드랍 / 테이블 '값만' 드랍 / DB가 존재한다면 드랍 / 테이블이 존재한다면 드랍

- INSERT INTO 테이블명 (컬럼1, 컬럼2, 컬럼3) VALUES (컬럼1값, 컬럼2값, 컬럼3값); / DELETE FROM 테이블명 WHERE 조건값; / UPDATE 테이블명 SET 컬럼명 = 새 값 WHERE 조건값; ::테이블에 컬럼1, 컬럼2, 컬럼3의 값 삽입 / 테이블에서 조건에 해당하는 값 삭제 / 테이블에서 조건에 해당하는 값 새로 할당

    - INSERT할 때 테이블의 컬럼들과 컬럼값들의 개수가 일치해야 함: 불일치 할 경우 에러 발생

 

데이터 가져오기

- SELECT: 원하는 데이터를 가져올 때 사용하는 것. 모든 쿼리에 필수적으로 포함. 

- FROM: 데이터를 가져올 테이블 지정.

    - SELECT 컬럼1, 컬럼2 FROM 테이블명;

    - 테이블이 어떤 DB 안에 있는지도 명시해주어야 하지만, USE로 사용할 데이터베이스를 먼저 지정해 준 경우 생략 가능. DB를 지정해주지 않으면 테이블 특정이 불가능

- AS: 가져온 데이터에 별명 지정. 실제 컬럼 이름엔 적용되지 않음

    - SELECT 컬럼명 AS 별칭 FROM 테이블명;

- LIMIT: 데이터 일부만 확인하고 싶을 때 사용. 가져올 데이터의 로우 개수 지정. 쿼리 가장 마지막에 위치함

    - SELECT 컬럼명 FROM 테이블명 LIMIT 숫자;

- DISTINCT: 중복 데이터는 제외하고 같은 값은 한 번만 가져옴. SELECT절에서 사용

    - SELECT DISTINCT 컬럼명 FROM 테이블명;

 

조건에 맞는 데이터 가져오기

- WHERE: 가져올 데이터 조건 지정. 조건식이 True가 되는 로우만 가져옴

    - SELECT 컬럼명 FROM 테이블명 WHERE 조건절;

- 조건식을 작성할 땐 보통 연산자를 사용해 원하는 데이터의 조건이 True가 되게끔 함: 비교 연산자, 논리 연산자, 기타 주요 연산자

- 비교 연산자: 값을 비교하는 기호. =, >, < 등

- 논리 연산자: 논리조건을 적용하는 기호. AND(모두 True일때만 True), OR(둘 중 하나만 True여도 True), NOT(반대)

- 기타 주요 연산자: BETWEEN(범위 내 데이터 선택), IN(목록 내 데이터 선택)

    - SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 BETWEEN 조건1 AND 조건2;

    - SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 IN (조건1, 조건2);

- LIKE: 특정 문자열이 포함된 데이터 선택. 검색할 문자열 내 와일드 카드를 사용해 검색 조건 구체화

    - 와일드카드: %(0개 이상의 문자), _(1개의 문자) ::%는 0개 이상의 문자로, 전체 길이를 알 수 없음

    - '%n'은 n으로 끝나는 문자열, '%_m_%'은 m을 포함하고 앞뒤로 1개 이상의 문자가 있는 문자열

    - SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 LIKE 검색할문자열;

- IS NULL: 데이터가 NULL인지 아닌지 확인. WHERE절에서 사용 시 NULL인 로우만 선택됨

    - NULL: 데이터 값이 존재하지 않는다는 표현. 0이나 공백이 아닌 알 수 없는 값

    - SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 IS NULL;

    - 널이 아닌 것들만 보려면 IS NOT NULL로 사용

 

원하는 데이터 만들기

- ORDER BY: 가져온 데이터 정렬. 순서를 정해 원하는 데이터를 가져옴

    - 디폴트 ASC(오름차순)(쓰지 않아도 오름차순 정렬됨), 내림차순을 원하면 DESC

    - SELECT 컬럼명 FROM 테이블명 WHERE 조건식 ORDER BY 컬럼명 ASC/DESC;

    - 컬럼 여러 개를 기준으로 정렬하고 싶은 경우, 먼저 나타나는 순서대로 데이터 정렬

- RANK: 데이터를 정렬해 순위를 만듦

    - SELECT절에서 항상 ORDER BY와 함께 사용(실질적인 정렬이 ORDER BY에서 일어나기 때문)

    - SELECT 컬럼명 RANK() OVER (ORDER BY 컬럼명) FROM 테이블명 WHERE 조건식;

    - 비슷한 기능을 하는 함수: DENSE_RANK(공동순위가 있어도 다음 순위를 뛰어넘지 않음), ROW_NUMBER(공동순위를 무시함)

- MySQL 내의 데이터들은 함수로 변형 가능: 함수 사용 시 결과값이 새 컬럼으로 반환됨. 함수이름(적용할 값이나 컬럼)

- 문자열에 사용하는 함수들: LOCATE, SUBSTRING, RIGHT/LEFT, UPPER/LOWER, LENGTH, CONCAT, REPLACE 등

    - LOCATE("A", "ABC"): ABC에서 A가 몇 번째에 있는지 위치 반환. 문자가 여러 개라면 가장 먼저 찾은 문자 위치를 가져오고, 찾는 문자가 없다면 0을 가져옴

    - SUBSTRING("ABC", 2): ABC에서 2번째 문자부터 반환. 입력 숫자가 문자열 길이보다 크다면 아무 것도 가져오지 않음

    - RIGHT/LEFT("ABC", 1): ABC에서 오른쪽/왼쪽 1번째 문자까지 반환

    - UPPER/LOWER("abc"/"ABC"): 대소문자 변경

    - LENGTH("ABC"): ABC의 글자 수 반환

    - CONCAT("ABC", "DEF"): ABC와 CDF문자열을 합친 "ABCDEF" 반환

    - REPLACE("ABC", "A", "Z"): ABC의 A를 Z로 바꿔 반환

 - 숫자형에 사용하는 함수들: ABS, CEILING/FLOOR, ROUND/TRUNCATE, POWER, MOD 등

    - ABS(숫자): 숫자 절댓값 반환

    - CEILING/FLOOR(숫자): 숫자를 정수로 올림/내림해 반환

    - ROUND/TRUNCATE(숫자, 자릿수): 숫자를 소수점 자릿수까지 반올림/버림해 반환

    - POWER(숫자1, 숫자2): 숫자1의 숫자2제곱 반환

    - MOD(숫자1, 숫자2): 숫자1을 숫자2로 나눈 나머지 반환

- 날짜형에 사용하는 함수들: NOW, CURRENT_DATE, CURRENT_TIME, YEAR, MONTH, MONTHNAME, DAYNAME, DAYOFMONTH, DAYOFWEEK, WEEK, HOUR, MINUTE, SECOND, DATE_FORMAT, DATEDIFF

    - NOW() / CURRENT_DATE() / CURRENT_TIME(): 현재 날짜+시간 / 현재 날짜 / 현재 시간 반환

    - YEAR(날짜) / MONTH(날짜) / MONTHNAME(날짜): 날짜의 연도 / 월 / (영어)월 반환

    - DAYNAME(날짜) / DAYOFMONTH(날짜) / DAYOFWEEK(날짜) / WEEK(날짜): 날짜의 (영어)요일 / 일 / (숫자)요일 / 해당 연도의 몇번째 주인지 반환

    - HOUR(시간) / MINUTE(시간) / SECOND(시간): 시간의 시 / 분 / 초 반환

    - DATE_FORMAT(날짜/시간, 형식): 날짜/시간의 형식을 파라미터로 넣어준 형식으로 변환해 반환

    - DATEDIFF(날짜1, 날짜2) / TIMEDIFF(시간1, 시간2): 날짜1과 2의 차이 / 시간1과 시간2의 차이 반환

 

데이터 그룹화하기

- GROUP BY: 컬럼에서 동일한 값을 가지는 로우를 그룹화함. 주로 그룹 별 데이터 집계에 사용(엑셀의 피벗(테이블)과 유사)

    - GROUP BY가 쓰인 쿼리의 SELECT절에는 GROUP BY 대상 컬럼과 그룹함수만 사용 가능

    - 여러 컬럼으로 그룹화도 가능하며, 컬럼 번호로도 그룹화 가능 (컬럼 번호: SELECT절에 쓴 컬럼의 순서)

    - SELECT 그룹바이컬럼이나 그룹함수 FROM 테이블명 WHERE 조건식 GROUP BY 컬럼명;

- HAVING: 가져올 데이터 그룹에 조건을 지정. 조건식이 True가 되는 그룹만 선택됨

    - HAVING절의 조건식에는 그룹 함수를 활용: HAVING은 GROUP BY 뒤에서 묶일 그룹의 조건을 만드는 역할

    - SELECT 컬럼명이나 그룹함수 FROM 테이블명 WHERE 조건식 GROUP BY 컬럼명 HAVING 조건식;

- 다양한 그룹함수: COUNT, SUM, AVG, MIN/MAX

    - 집계할 컬럼 이름은 그룹 기준 컬럼과 상관 없으며, GROUP BY가 없는 쿼리에서도 사용 가능(이땐 전체 로우에 함수 적용)

    - SELECT 컬럼명 COUNT/SUM/AVG/MIN/MAX(컬럼명) FROM 테이블명 GROUP BY 컬럼명 HAVING 조건문;

- 쿼리 작성 순서: SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

- 쿼리 실행 순서: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

규칙 만들기

- IF: 조건을 만들 때 사용하는 함수. 주로 SELECT절에 사용하며, 결과 값을 새 컬럼으로 반환

    - 조건식이 참이라면 참일때의 값, 거짓이라면 거짓일 때의 값 반환

    - SELECT IF (조건, 참일 때, 거짓일 때) FROM 테이블명

- IFNULL: 데이터가 널인지 확인하고 널이라면 새 값을 반환하는 함수('확인'만 하는 ISNULL과는 다름). 주로 SELECT절에 사용하며, 결과 값을 새 컬럼으로 반환

    - SELECT IFNULL (확인할 컬럼, 널일 경우 채울 값) FROM 테이블명

- CASE: 조건을 여러 개 만들 때 사용하는 함수. 주로 SELECT절에 사용하며, 결과 값을 새 컬럼으로 반환

    - CASE WHEN 조건식1 THEN 결과값1 WHEN 조건식2 THEN 결과값2 ELSE 결과값3 END;

    - 조건식 1이 참이라면 결과값 1, 참이 아니라면 조건식 2확인 후 참이라면 결과값 2... 이런 식.

    - CASE 컬럼명 WHEN 조건값1 THEN 결과값1 WHEN 조건값2 THEN 결과값2 ELSE 결과값3 END;

    - 컬럼명에 대해 조건값1이라면 결과값1, 조건값2라면 결과값 2... 이런 식.

    - ELSE를 생략할 순 있으나, ELSE에 해당하는 값(모든 조건에 해당하지 않는 데이터)이 있을 경우 NULL이 반환됨

- 직접 함수를 만들 수도 있음. 코드는 아래 참조

SET GLOBAL log_bin_trust_function_creators=1;
#MySQL Workbench에서 사용시 먼저 실행시켜주어야 함

DELIMITER//
CREATE FUNCTION 함수명 ([입력값] [데이터타입], ...)
RETURNS 결과값타입
BEGIN
DECLARE 임시값 임시값타입
SET 임시값 = 입력값
SELECT 쿼리 INTO 데이터(임시값)
RETURN INTO데이터(임시값)
END
//DELIMITER;

    

테이블 합치기

- JOIN: 테이블을 합칠 때 사용. 같은 의미를 가지는 컬럼 값 기준으로 테이블을 합침. INNER / LEFT / RIGHT / OUTER /  CROSS / SELF JOIN의 총 여섯 가지 종류로 나뉨

    - INNER JOIN: 두 테이블 모두에 있는 값만 합침.

    - LEFT / RIGHT JOIN: 한 테이블을 기준으로 값을 합침.

    - SELECT 컬럼명 FROM 테이블A INNER/LEFT/RIGHT JOIN 테이블B ON 테이블A.컬럼A = 테이블B.컬럼B;

    - OUTER JOIN: 두 테이블에 있는 모든 값을 합침. MySQL에는 구현되어 있지 않아 LEFT JOIN과 RIGHT JOIN의 UNION(합집합 연산자)으로 연결해 사용. 코드는 아래 참조

SELECT 컬럼명 FROM 테이블A LEFT JOIN 테이블B ON 테이블A.컬럼A = 테이블B.컬럼B
UNION ##두 쿼리 결과를 중복 제외하고 합쳐 보여주는, 합집합 연산자
SELECT 컬럼명 FROM 테이블A RIGHT JOIN 테이블B ON 테이블A.컬럼A = 테이블B.컬럼B

    - CROSS JOIN: 두 테이블에 있는 모든 값 각각 합침. A테이블 로우 수 x B테이블 로우 수 만큼의 로우를 갖는 테이블 생성

    - SELECT 컬럼명 FROM 테이블A CROSS JOIN 테이블B;

    - SELF JOIN: 같은 테이블에 있는 값 합치기. '같은 테이블 끼리' 합치는 것이기 때문에 헷갈리지 않게 alias 사용해야 함

    - SELECT 컬럼명 FROM 테이블A AS t1 INNER JOIN 테이블A AS t2 ON t1.컬럼A = t2.컬럼A;

- 같은 이름을 갖는 컬럼이 있다면 SELECT 시 어떤 테이블에서 합쳐진 컬럼을 가져올 것인지 명시해 주어야 함!: 테이블A.컬럼A 같은 식

 

여러 테이블 한번에 다루기

- UNION, UNION ALL: 합집합 연산자. UNION은 동일 값을 제외하고 보여주나 UNION ALL은 동일 값을 포함해 보여줌

    - 쿼리A UNION 쿼리B의 결과값 개수가 같아야 함: 다를 경우 에러 발생

    - ORDER BY는 쿼리 가장 마지막에 작성 가능하며, 쿼리 A에서 가져온(SELECT해 온) 컬럼으로만 가능

    - SELECT 컬럼명 FROM 테이블A UNION/UNION ALL SELECT 컬럼명 FROM 테이블B

- 교집합(INTERSECT)과 차집합(MINUS)는 MySQL에 존재하지 않음: JOIN으로 표현.

    - 교집합: SELECT 컬럼명 FROM 테이블A A INNER JOIN 테이블B B ON A.컬럼1 = B.컬럼1;

    - 교집합을 확인하고 싶은 컬럼 모두 다 기준으로(ON 안에 두고) 합쳐주어야 함

    - 차집합: SELECT 컬럼명 FROM 테이블A A LEFT JOIN 테이블B B ON A.컬럼1 = B.컬럼1 WHERE B.컬럼명 IS NULL;

    - B테이블에서 널인 것만 가져옴 = A와의 합집합에서 교집합 부분을 빼줌. 교집합과 마찬가지로 차집합을 확인하고 싶은 컬럼 모두 다를 기준으로 두고 합쳐주어야 함

 

조건에 조건 더하기

- 서브쿼리: 하나의 쿼리 내에 포함된 또 하나의 쿼리. 일반 쿼리와 동일하나, 반드시 괄호 안에 있어야 함. SELECT, FROM, WHERE, HAVING, ORDER BY절과 INSERT, UPDATE, DELETE문에도 사용 가능. 세미콜론 붙이지 않아도 됨

    - SELECT절의 서브쿼리: 스칼라 서브쿼리. 반드시 결과값이 하나의 값이어야 함

    - SELECT 컬럼명, (SELECT 컬럼명 FROM 테이블명 WHERE 조건식) FROM 테이블명 WHERE 조건식;

    - FROM절의 서브쿼리: 인라인 뷰 서브쿼리. 반드시 결과값이 하나의 테이블이어야 하며, 서브쿼리로 만든 테이블은 반드시 별명(ALIAS)을 가져야 함

    - SELECT 컬럼명 FROM (SELECT 컬럼명 FROM 테이블명 WHERE 조건식) AS 별명 WHERE 조건식;

    - WHERE절의 서브쿼리: 중첩 서브쿼리. 반드시 결과값이 하나의 컬럼이어야 함(EXISTS 제외). 연산자와 함께 사용

    - SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 연산자 (SELECT 컬럼명 FROM 테이블명 WHERE 조건식);

    - 주로 IN, ALL, ANY, EXISTS사용. ALL은 '모든', ANY는 '하나라도', EXISTS는 서브쿼리에 해당하는 데이터의 존재 확인