디지털 마케팅 LAB

패스트캠퍼스 데이터분석 부트캠프 8주차_SQL (1) 본문

패스트캠퍼스 데이터분석 부트캠프 14기/주차별 학습기록

패스트캠퍼스 데이터분석 부트캠프 8주차_SQL (1)

홍보하swu 2024. 6. 14. 15:44
데이터베이스와 테이블
  • 데이터베이스
    • 데이터베이스 생성 
      CREATE DATABASE [데이터베이스 이름];
    • 데이터베이스 목록 확인 
      SHOW DATABASE;
    • 데이터베이스 사용 
      USE [데이터베이스 이름];
    • 데이터베이스 삭제 
      DROP DATABASE IF EXISTS [데이터베이스 이름];
  •  테이블
    • 테이블 생성 
      CREATE TABLE [테이블 이름] (
	[컬럼 이름] [데이터 타입],
	[컬럼 이름] [데이터 타입],
	...
);
    • 테이블 편집
      • 테이블 이름 변경 
        ALTER TABLE [테이블 이름] RENAME [새로운 테이블 이름];
      • 새로운 컬럼 추가 
        ALTER TABLE [테이블 이름] ADD COLUMN [컬럼 이름] [데이터 타입];
      • 기존 컬럼 타입 변경 
        ALTER TABLE [테이블 이름] MODIFY COLUMN [컬럼 이름] [새로운 데이터 타입];
      • 기존 컬럼 이름과 타입 모두 변경 
        ALTER TABLE [테이블 이름]
CHANGE COLUMN [컬럼 이름] [새로운 컬럼 이름] [새로운 데이터 타입];
      • 컬럼 삭제 
        ALTER TABLE [테이블 이름] DROP COLUMN [컬럼 이름];
    • 테이블 삭제 
      DROP TABLE IF EXISTS [테이블 이름];
    • 테이블 값만 삭제 
      TRUNCATE TABLE [테이블 이름];
  • 데이터
    • 데이터 삽입 
      # 데이터 하나만 삽입
INSERT INTO [테이블 이름] ([컬럼1 이름], [컬럼2 이름], [컬럼3 이름])
VALUES ([컬럼1 값], [컬럼2 값], [컬럼3 값]);

# 데이터 여러개 삽입
INSERT INTO [테이블 이름] ([컬럼1 이름], [컬럼2 이름], [컬럼3 이름])
VALUES ([컬럼1 값], [컬럼2 값], [컬럼3 값]),
		([컬럼1 값], [컬럼2 값], [컬럼3 값]),
		...
		([컬럼1 값], [컬럼2 값], [컬럼3 값]);
    • 데이터 삭제 
      DELETE FROM [테이블 이름]
WHERE [조건 값];
    • 데이터 수정 
      UPDATE [테이블 이름]
SET [컬럼 이름] = [새 값]
WHERE [조건 값];

 

데이터 다루기
  • 기본 문법 
    USE [데이터베이스 이름];

# 컬럼 하나만 선택
SELECT [컬럼 이름] FROM [테이블 이름];

# 컬럼 여러개 선택
SELECT [컬럼 이름], [컬럼 이름], ... [컬럼 이름]
FROM [테이블 이름];

# 컬럼 전체 선택
SELECT * FROM [테이블 이름];
  • 별명 붙이기 
    SELECT [컬럼 이름] AS [컬럼 별명]
FROM [테이블 이름];
  • 데이터 일부만 보기 
    SELECT [컬럼 이름] FROM [테이블 이름]
LIMIT [가져올 로우 수];
  • 중복 제거 
    SELECT DISTINCT [컬럼 이름]	# 고유값만 확인
FROM [테이블 이름];
  • 조건 활용
    SELECT [컬럼 이름] FROM [테이블 이름]
# 조건식이 True인 로우만 선택
WHERE 조건식;
  • IN 문법
    # 목록 내 포함되는 데이터를 선택할 때 사용하는 연산자
SELECT [컬럼 이름] FROM [테이블 이름]
WHERE [컬럼 이름] IN (조건 1, 조건 2, ...);
  • 문자형 데이터
    • LIKE 문법
      # 특정 문자열이 포함된 데이터를 선택하는 연산자
SELECT [컬럼 이름] FROM [테이블 이름]
WHERE [컬럼 이름] LIKE [검색할 문자열];
    • 와일드카드 → 검색 조건 표현
와일드카드 의미 사용 예제 해석
% 0개 이상의 문자 '%e' e로 끝나는 문자열
'e%' e로 시작하는 문자열
'%e%' e가 포함된 문자열
_ 1개의 문자 '_e' e로 끝나고 e앞에 1개의 문자가 있는 문자열
'%_e' e로 끝나고 e앞에 1개 이상의 문자가 있는 문자열
'%_e_%' e를 포함하고 앞뒤로 각각 1개 이상의 문자가 있는 문자열
  • NULL 데이터
    • IS NULL 문법
    • # 데이터가 NULL인지 아닌지를 확인하는 연산자
SELECT [컬럼 이름] FROM [테이블 이름]
WHERE [컬럼 이름] IS NULL;

# null이 아닌 데이터만 찾을 경우
SELECT [컬럼 이름] FROM [테이블 이름]
WHERE [컬럼 이름] IS NOT NULL;
    •  
원하는 데이터 만들기
  • 정렬 
    # 오름차순
SELECT [컬럼 이름] FROM [테이블 이름]
WHERE 조건식
ORDER BY [컬럼 이름] (ASC);	# 디폴트

# 내림차순
SELECT [컬럼 이름] FROM [테이블 이름]
WHERE 조건식
ORDER BY [컬럼 이름] DESC;

# 여러 컬럼으로 정렬
## [컬럼 1] 기준으로 정렬 -> [컬럼 1] 값이 동일한 로우 간에 [컬럼 2] 기준으로 정렬
SELECT [컬럼 이름] FROM [테이블 이름]
WHERE 조건식
ORDER BY [컬럼1 이름], [컬럼2 이름] DESC;
  • 순위 매기기
    • RANK 
      # B컬럼 기준으로 오름차순 순위 컬럼 생성
## 내림차순은 DESC 추가
SELECT [A 컬럼 이름], ..., RANK() OVER (ORDER BY [B 컬럼 이름])
FROM [테이블 이름]
WHERE 조건식;
    • DENSE_RANK 
      SELECT [컬럼 이름], ..., DENSE_RANK() OVER (ORDER BY [컬럼 이름])
FROM [테이블 이름]
WHERE 조건식;
    • ROW_NUMBER
      SELECT [컬럼 이름], ..., ROW_NUMBER() OVER (ORDER BY [컬럼 이름])
FROM [테이블 이름]
WHERE 조건식;
    • 세 함수 비교
      # 예시 코드
SELECT name, attack,
	RANK() OVER (ORDER BY attack DESC) AS rank_rank,
	DENSE_RANK() OVER (ORDER BY attack DESC) AS rank_dense_rank,
	ROW_NUMBER() OVER (ORDER BY attack DESC) AS rank_row_number
FROM mypokemon;

RANK 공동 순위가 있으면 다음 순서로 건너 뜀
DENSE_RANK 공동 순위가 있어도 다음 순위를 뛰어 넘지 않음
ROW_NUMBER 공동 순위를 무시함
  • 함수 - 문자형 데이터
함수 예시 설명
LOCATE(“A”, “ABC”) “ABC”에서 “A”는 몇 번째에 위치해 있는지 검색해 위치 반환
SUBSTRING(“ABC”, 2) “ABC”에서 2번째 문자부터 반환
RIGHT(“ABC”, 1) “ABC”에서 오른쪽에서 1번째 문자까지 반환
LEFT(“ABC”, 1) “ABC”에서 왼쪽에서 1번째 문자까지 반환
UPPER(“abc”) “abc”를 대문자로 바꿔 반환
LOWER(“ABC”) “ABC”를 소문자로 바꿔 반환
LENGTH(“ABC”) “ABC”의 글자 수를 반환
CONCAT(“ABC”, “DEF”) “ABC” 문자열과 “CDF” 문자열을 합쳐 반환
REPLACE(“ABC”, “A”, “Z”) “ABC”의 “A”를 “Z”로 바꿔 반환
  • 함수 - 숫자형 데이터
함수 예시 설명
ABS(숫자) 숫자의 절댓값 반환
CEILING(숫자) 숫자를 정수로 올림해서 반환
FLOOR(숫자) 숫자를 정수로 내림해서 반환
ROUND(숫자, 자릿수) 숫자를 소수점 자릿수까지 반올림해서 반환
TRUNCATE(숫자, 자릿수) 숫자를 소수점 자릿수까지 버림해서 반환
POWER(숫자A, 숫자B) 숫자A의 숫자B 제곱 반환
MOD(숫자A, 숫자B) 숫자A를 숫자B로 나눈 나머지 반환
  • 함수 - 날짜형 데이터
함수 예시 설명
NOW() 현재 날짜와 시간 반환
CURRENT_DATE() 현재 날짜 반환
CURRENT_TIME() 현재 시간 반환
YEAR(날짜) 날짜의 연도 반환
MONTH(날짜) 날짜의 월 반환
MOTHNAME(날짜) 날짜의 월을 영어로 반환
DAYNAME(날짜) 날짜의 요일을 영어로 반환
DAYOFMONTH(날짜) 날짜의 일 반환
DAYOFWEEK(날짜) 날짜의 요일을 숫자로 반환 (일:1, 월:2, ~ , 토:7)
WEEK(날짜) 날짜가 해당 연도에 몇 번째 주인지 반환
HOUR(시간) 시간의 시 반환
MINUTE(시간) 시간의 분 반환
SECOND(시간) 시간의 초 반환
DATEFORMAT(날짜/시간, 형식) 날짜/시간의 형식 변환
DATEDIFF(날짜1, 날짜2) 날짜1과 날짜2의 차이 반환 (날짜1 ‒ 날짜2)
TIMEDIFF(시간,1 시간2) 시간1과 시간2의 차이 반환 (시간1 ‒ 시간2)
DATE_ADD(날짜, INTERVAL 간격) 날짜에 간격 추가
DATE_SUB(날짜, INTERVAL 간격) 날짜에 간격 뺌
EXTRACT(추출부분 FROM 컬럼) 칼람에서 특정 부분을 추출
더보기

DATE_FORMAT 함수의 형식

 

데이터 그룹화
  • GROUP BY 문법
    # GROUP BY가 쓰인 쿼리의 SELECT 절에는 GROUP BY 대상 컬럼과 그룹 함수만 사용 가능
SELECT [그룹화 대상 컬럼 이름], ... , [그룹 함수]
FROM [테이블 이름]
WHERE 조건식
GROUP BY [컬럼 이름];
  • HAVING 문법
    # 가져올 데이터 그룹에 조건을 지정 -> GROUP BY에서의 WHERE절
SELECT [컬럼 이름], ..., [그룹 함수]
FROM [테이블 이름]
WHERE 조건식
GROUP BY [컬럼 이름]
HAVING 조건식;
  • 그룹 함수
    • COUNT
      # 그룹의 값 수를 세는 함수
# 그룹함수들은 모두 GROUP BY가 없는 쿼리에서도 사용 가능하며, 이때는 전체 로우에 함수가 적용
SELECT [컬럼 이름], ..., COUNT([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;
    • SUM
      # 그룹의 합을 계산하는 함수
SELECT [컬럼 이름], ..., SUM([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;
    • AVG
      # 그룹의 평균을 계산하는 함수
SELECT [컬럼 이름], ..., AVG([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;
    • MIN
      # 그룹의 최솟값을 반환하는 함수
SELECT [컬럼 이름], ..., MIN([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;
    • MAX
      # 그룹의 최댓값을 반환하는 함수
SELECT [컬럼 이름], ..., MAX([컬럼 이름])
FROM [테이블 이름]
GROUP BY [컬럼 이름]
HAVING 조건문;
  • 쿼리 순서
    • 작성 순서
      1. SELECT
      2. FROM
      3. WHERE
      4. GROUP BY
      5. HAVING
      6. ORDER BY
    • 실행 순서
      1. FROM
      2. WHERE
      3. GROUP BY
      4. HAVING
      5. SELECT
      6. ORDER BY

 

조건 & 함수 생성
  • 조건문
    • 주로 SELECT절에 사용하는 함수로, 결과 값을 새로운 컬럼으로 반환
    • IF 문법 - 조건 하나를 만들 때 사용
      # 예시 코드
## attack_class컬럼의 값을 공격력이 60이상이면 strong, 아니면 weak로 반환
SELECT name, IF(attack >= 60, ‘strong’, ‘weak’) AS attack_class
FROM pokemon.mypokemon;
    • IFNULL
      # 데이터가 NULL이라면 새로운 값을 반환하는 함수

# 예시 코드
## full_name컬럼의 값을 이름이 NULL이면 unknown으로 반환
SELECT name, IFNULL(name, ‘unknown’) AS full_name
FROM pokemon.mypokemon;
       
    • CASE - 조건 여러개 만들 때 사용
      # 주로 SELECT절에 사용하는 함수로, 결과 값을 새로운 컬럼으로 반환 
# ELSE 문장을 생략 시 NULL 값을 반환 

# 형식 1
CASE
	WHEN 조건식1 THEN 결과값1
	WHEN 조건식2 THEN 결과값2
	ELSE 결과값3
END

# 예시 코드 
SELECT name, 
	CASE 
    		WHEN attack >= 100 THEN ‘very strong’ 
        	WHEN attack >= 60 THEN ‘strong’ 
        	ELSE ‘weak’ 
    END AS attack_class 
FROM pokemon.mypokemon;

      # 형식 2
CASE [컬럼 이름]
	WHEN 조건값1 THEN 결과값1
	WHEN 조건값2 THEN 결과값2
	ELSE 결과값3
END

# 예시 코드
SELECT name, type
	CASE type
		WHEN ‘bug’ THEN ‘grass’
		WHEN ‘electric’ THEN ‘water’
		WHEN ‘grass’ THEN ‘bug’
	END AS rival_type
FROM pokemon.mypokemon;
  • 함수
    • 함수 만들기
      CREATE FUNCTION [함수 이름] ( [입력값 이름] [데이터 타입], ...)
	RETURNS [결과값 데이터 타입]
BEGIN
	DECLARE [임시값 이름] [데이터 타입];
	SET [임시값 이름] = [입력값 이름];
	쿼리;
	RETURN 결과값
END
    • 함수 지우기
      DROP FUNCTION [함수 이름];
    • 예시 코드
      CREATE FUNCTION getAbility(attack INT, defense INT)
                     RETURNS INT
      BEGIN
                     DECLARE a INT;
                     DECLARE b INT;
                     DECLARE ability INT;
                     SET a = attack;
                     SET b = defense;
                     SELECT a + b INTO ability;
                     RETURN ability;
      END
  • MySQL Workbench에서 함수 생성 시 주의할 점
    SET GLOBAL log_bin_trust_function_creators = 1;	# 사용자 계정에 function create 권한 생성

DELIMITER //	# 함수의 시작 지정

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

//
DELIMITER ;	# 함수의 끝 지정
		# ; 전에 반드시 1칸 띄어주기
테이블 합치기
  • JOIN의 종류

  • INNER JOIN 문법
    # 두 테이블 모두에 있는 값만 합치기
# 디폴트 (그냥 JOIN으로 해도 INNER JOIN으로 수행)
SELECT [컬럼 이름]
FROM [테이블 A 이름]
INNER JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
WHERE 조건식;
  • LEFT JOIN 문법 
    # 왼쪽 테이블에 있는 값만 합치기
SELECT [컬럼 이름]
FROM [테이블 A 이름]
LEFT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
WHERE 조건식;
  • RIGHT JOIN 문법 
    # 오른쪽 테이블에 있는 값만 합치기
SELECT [컬럼 이름]
FROM [테이블 A 이름]
RIGHT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
WHERE 조건식;
  • OUTER JOIN 문법 
    # 두 테이블에 있는 모든 값 합치기
# OUTER JOIN은 MySQL 키워드에 없음
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 문법 
    # 두 테이블에 있는 모든 값을 각각 합치기
# ON 키워드가 없어도 됨
SELECT [컬럼 이름]
FROM [테이블 A 이름]
CROSS JOIN [테이블 B 이름]
WHERE 조건식;
  • SELF JOIN 문법 
    # 같은 테이블에 있는 값 합치기
SELECT [컬럼 이름]
FROM [테이블 A 이름] AS t1
INNER JOIN [테이블 A 이름 ] AS t2
ON t1.[컬럼 A 이름] = t2.[컬럼 A 이름]
WHERE 조건식;

 

저작자표시 (새창열림)

'패스트캠퍼스 데이터분석 부트캠프 14기 > 주차별 학습기록' 카테고리의 다른 글

패스트캠퍼스 데이터분석 부트캠프 12주차_ChatGPT를 활용한 데이터 분석 실습하기  (3) 2024.07.12
패스트캠퍼스 데이터분석 부트캠프 9주차_SQL (2)  (0) 2024.06.21
패스트캠퍼스 데이터분석 부트캠프 5주차_PYTHON (3)  (0) 2024.05.24
패스트캠퍼스 데이터분석 부트캠프 4주차_PYTHON (2)  (0) 2024.05.17
패스트캠퍼스 데이터분석 부트캠프 3주차_PYTHON (1)  (0) 2024.05.10
'패스트캠퍼스 데이터분석 부트캠프 14기/주차별 학습기록' Related Articles more