[멋쟁이사자처럼 데이터분석 부트캠프 4주차]-SQL

 

이번 주차에는 MySQL Workbench를 설치하고 실습 환경을 구축한 뒤,
SQL의 기본 문법과 다양한 함수 및 연산자들을 폭넓게 학습하였습니다.
⚠️본 글은 부트캠프 수업 내용을 기반으로 한 개인 학습 기록입니다.

✅ SQL을 이용한 데이터 조회

1. SELECT문

• 컬럼에 별명(Alias) 붙여줄 수 있음
• 별명에 공백 또는 특수문자가 있는 경우에는 반드시 큰따옴표나 작은따옴표안에 별명을 넣어준다

2. WHERE절

• 조건에 맞는 행(레코드)만 조회할 때 사용
• 비교 연산자, 논리 연산자 등을 사용하여 필터링 기능

3. ORDER BY절

• 레코드 순서대로 정렬할 때 사용
• 오름차순 정렬(ASC): 작은 값부터 순서대로  (생략 가능)
• 내림차순 정렬(DESC): 큰 값부터 순서대로
• 컬럼명 대신 별명이나 컬럼의 순서 넣을 수 있음  ORDER BY 4 DESC;

4. LIMIT n

• 반환되는 레코드의 개수 지정 가능
• 문장의 맨 마지막에 추가
• ORDER BY 절 뒤에 LIMIT와 가져올 레코드의 수를 넣으면 상위 또는 하위 n개의 레코드 조회 가능

5. DISTINCT

• 데이터 조회 시 중복 제거
• SELECT절에서 사용

---

✅ SQL연산자

1. 산술 연산자

• 더하기(+), 빼기(-), 곱하기(*), 나누기(/), 나머지(%), 몫(DIV), 나머지(% 또는 MOD)

2. 비교 연산자

• 크거나 같다(>=), 작거나 같다(<=), 크다(>), 작다(<), 같다(=), 같지 않다(!= 또는 <>)

3. 논리 연산자

• AND(모두 참이어야 참), OR(하나만 참이어도 참), NOT(부정)

4. 집합 연산자 (합집합)

• UNION:  중복 제거
• UNION ALL: 중복 포함
• ⚠️컬럼 개수, 컬럼 순서, 데이터 타입이 모두 동일해야 하며, 상호 호환 가능해야 함

5. IS NULL

• NULL은 값이 없는 상태, 알 수 없는 값
• 0이나 ' '(빈 문자열)과는 다른 의미

6. IN과 BETWEEN~AND 연산

• IN: 동일한 컬럼에 대해 여러 값 중 하나 일 경우 ➡️ OR 조건을 간단하게 표현
• BETWEEN~AND: 범위 조건(이상~이하) 표현

#IN
SELECT * FROM 주문
WHERE 제품번호 IN (100, 101, 102);

#BETWEEN
SELECT* FROM 제품
WHERE 단가 BETWEEN 1000 AND 5000;

7. LIKE 연산자

• 와일드카드 문자(%, _)를 사용하여 원하는 결과 얻을 수 있음
• %: 0개 이상 문자
• _: 정확히 한 글자

#도시가 '광역시'이면서 고객번호 두 번째 글자 또는 세 번째 글자가 'C'인 모든 고객의 정보
SELECT * FROM 고객 
WHERE 도시 LIKE '%광역시' AND (고객번호 LIKE '_C%' OR 고객번호 LIKE '__C%');

---

✅ SQL 함수

1. 문자형 함수

• CHAR_LENGTH(): 문자의 개수를 반환
• LENGTH(): 문자열에 할당된 바이트 수를 반환

---

• CONCAT(): 문자열을 연결할 때 사용
• CONCAT_WS(구분자, 문자열1, 문자열2...): 구분자와 함께 문자열을 연결할 때 사용

---

• LEFT(문자열, 길이): 문자열의 왼쪽부터 길이만큼 반환
• RIGHT(문자열, 길이): 문자열의 오른쪽부터 길이만큼 반환

---

• SUBSTR(문자열, 시작위치, 길이): 지정한 위치로부터 길이만큼 문자열 번환 == SUBSTRING()
• SUBSTRING_INDEX(문자열, 구분자, 인덱스): 지정한 구분자를 기준으로 문자열을 분리해서 가져올 때
• ⚠️SUBSTR_INDEX는  존재하지 않으므로 불가능

---

• LPAD(문자열, 길이, 채울 문자열): 지정 길이에서 문자열을 제외한 빈칸을 특정 문자로 채움 (LPAD는 왼쪽, RPAD는 오른쪽)

---

• LTRIM(), RTRIM(): 왼쪽 및 오른쪽 공백 제거
• TRIM([LEADING | TRAILING | BOTH] '제거할문자' FROM 문자열)
  
  • LEADING: 왼쪽만 제거
  • TRAILING: 오른쪽만 제거
  • BOTH(기본값): 양쪽 제거

---

• FIELD(): 리스트에서 특정 값이 몇 번째인지 반환 
  • FIELD('B', 'A', 'B', 'C') ➡️ 2
• FIND_IN_SET(): 문자열 리스트에서 지정한 문자열 찾아 위치 값 반환 
  • FIND_IN_SET('B', 'A,B,C') ➡️ 2
• INSTR(), LOCATE(): 기준 문자열 중 부분 문자열을 찾아 위치 값 반환
  • INSTR(기준문자열, 찾을문자열)
  • LOCATE(찾을문자열, 기준문자열) 
  • INSTR('ABCDEF', 'DE') ➡️ 4
  • LOCATE('CD', 'ABCDEF') ➡️ 3

⚠️모두 찾는 값이 없으면 0을 반환하나, "0"은 위치 없음을 뜻함. 실제 인덱스는 1부터 시작

---

• ELT(찾을 문자열 위치, 문자열1, 문자열2..): 지정한 위치에 있는 문자열을 반환
  • ELT(2, 'JAVA', 'PYTHON', 'C') ➡️ PYTHON

❗ 지정한 위치가 인덱스 범위를 벗어나면 'NULL' 반환

---

• REPEAT(문자열, 횟수): 문자열을 지정한 횟수로 반복하고자 할 때 사용
• REPLACE(문자열, 원래문자열, 바꿀문자열): 문자열의 일부를 다른 문자열로 대체
• REVERSE(): 문자열을 거꾸로 뒤집음

---

2. 숫자형 함수

• CELING(): 올림
• FLOOR(): 버림 (내림)
• ROUND(): 반올림
• TRUNCATE(숫자, 자를 자릿수): 소수점 아래 선택한 위치에서 자름 (절삭)
  • CELING(3.14) ➡️ 4
  • FLOOR(3.95) ➡️ 3
  • ROUND(3.1415, 2) ➡️ 3.14
  • TRUNCATE(3.1415, 1) ➡️ 3.1
  • TRUNCATE(3.1415, 2) ➡️ 3.14

---

• ABS(): 절댓값 반환
• SIGN(): 양수의 경우 1, 음수의 경우 -1 반환
• MOD(): 나머지 구하기
  • MOD(203, 4)
  • 203 % 4
  • 203 MOD 4

---

• POWER(숫자1, 숫자2): n제곱승 (숫자1의 숫자2 제곱)
• SQRT(): 제곱근 값 반환
• RAND(): 0과 1사이 임의의 실수 값 반환
  • RAND() 안에 Seed값 설정하면 매번 동일한 임의의 값 반환
  • ROUND(), TRUNCATE() 와 함께 사용하면 난수의 정수값으로 반환 가능

---

3. 날짜/시간형 함수

• NOW(), SYSDATE(): 시스템의 현재 날짜와 시간 반환
• CURDATE(): 시스템의 현재 날짜 반환
• CURTIME(): 시스템의 현재 시간 반환

---

• YEAR(): 연도
• QUARTER(): 분기
• MONTH(): 월
• DAY(): 일
• HOUR(): 시
• MINUTE(): 분
• SECOND(): 초

---

• 👉 기간 반환 함수
• DATEDIFF(): 두 날짜 사이의 차이를 일(DAY) 단위로 반환
• TIMESTAMPDIFF(): 날짜 사이의 차이를 지정한 단위 (YEAR, MONTH, DAY 등)로 반환
• DATEDIFF(끝 일자, 시작 일자)
• TIMESTAMPDIFF(단위, 시작 일자, 끝 일자)
  • DATEDIFF('2025-07-22', '2025-07-20') ➡️ 2
  • TIMESTAMPDIFF(MONTH, '2025-03-22', '2025-07-22') ➡️ 4

---

• 👉 기간 반영 날짜 함수
• ADDDATE(): 지정한 날짜 기준으로 그 기간만큼 더한 날짜 반환
• SUBDATE(): 기간만큼 뺀 날짜 반환
• ADDDATE(날짜, 기간) OR ADDDATE(날짜, INTERVAL 기간 단위)
• SUBDATE(날짜, 기간) OR SUBDATE(날짜, INTERVAL 기간 단위)
  • ADDDATE(NOW(), 50) ➡️ 50일 후 (단위 생략 시 'DAY(일)'가 기본값)
  • ADDDATE(NOW(), INTERVAL 50 MONTH) ➡️ 50개월 후
  • SUBDATE(NOW(), INTERVAL 50 DAY) ➡️ 50일 전

---

• 👉 기타 날짜 반환 함수
• LAST_DAY(): 해당 월의 마지막 일자 반환
• DAYOFYEAR(): 현재 연도에서 며칠이 지났는지 반환
• MONTHNAME(): 월을 영문으로 반환
• WEEKDAY(): 요일을 정수로 반환
• DAYOFWEEK(): 요일을 숫자로 반환 
  • LAST_DAY('2025-07-20') ➡️ 2025-07-31
  • DAYOFYEAR('2025-01-01') ➡️ 1
  • MONTHNAME('2025-07-22') ➡️ July
  • WEEKDAY('2025-07-21') ➡️ 0 (월요일)
  • DAYOFWEEK('2025-07-21') ➡️ 2 (월요일) 
    • WEEKDAY의 요일 기준: 0=월요일, 1=화요일, 2=수요일, 3=목요일, 4=금요일, 5=토요일, 6=일요일
    • DAYOFWEEK의 요일 기준: 1=일요일, 2=월요일, 3=화요일, 4=수요일, 5=목요일, 6=금요일, 7=토요일
    • ❗ETL은 DAYOFWEEK만 사용 가능하다

---

4. 기타 단일 행 함수

• 👉 형 변환 함수
• CAST(), CONVERT(): 원하는 형태로 데이터타입을 변경
  
  • CAST('100' AS SIGNED) ➡️ 100 (정수형)
  • CAST(3.14 AS CHAR) ➡️ '3.14' (문자형)
  • CONVERT('2025-07-22', DATE) ➡️ 2025-07-22 (날짜형)
  • CONVERT('300.12' DECIMAL(10, 1)) ➡️ 300.1 (숫자형 - 소수2자리)

---

• 👉 제어 흐름 함수
• IF(조건, 수식1, 수식2): 조건의 결과가 참이면 수식1 반환, 그렇지 않으면 수식2의 결과 반환
  • IF(12500 * 450 > 5000000, '초과달성', '미달성') ➡️ '초과달성'

---

• IFNULL(수식1, 수식2): 수식1이 NULL이 아니면 수식1 값 반환, NULL이면 수식2 값 반환
• NULLIF(수식1, 수식2): 두 수식 값 비교하여 값이 같으면 NULL, 다르면 수식1 값 반환
  • IFNULL(NULL, 0) ➡️ NULL
  • NULLIF(12 * 10, 1200) ➡️ 120

---

• 👉 CASE문
• 함수는 아니지만, 조건 비교가 여러 개일 때 유용하게 사용 가능
• WHEN 조건 1 THEN 값 WHEN 조건2 THEN 값2 END
• CASE문은 END로 마무리 되어야한다

CASE WHEN 12500 * 450 > 5000000 THEN '초과달성'
     WHEN 2500 * 450 > 4000000 THEN '달성'
     ELSE '미달성' END
     
# 초과달성

---

5. 집계함수

• COUNT(): 레코드 개수
• SUM(): 합계
• AVG(): 평균
• MAX(): 최댓값
• MIN(): 최솟값
• STDDEV(): 표준편차

---

• 👉 집계함수의 활용
• WHERE절
  • 집계 전에 조건을 걸 때 사용
  • 조건에 맞는 레코드만 집계 대상이 된다
• GROUP BY절
  • 그룹별로 집계할 때 사용
  • SELECT절에 그룹으로 묶을 컬럼명과 집계 함수를 넣어준다
  • SELECT절에는 반드시 집계함수 또는 GROUP BY 컬럼만 포함해야 한다
• HAVING절
  • GROUP BY의 결과에 대하여 추가 조건을 넣고자 할 때 사용
  • WHERE과 달리 집계함수 조건을 쓸 수 있다

#주문 테이블에서 고객별 총 주문 횟수를 구하고, 3번 이상 주문한 우수 고객만 조회하세요
SELECT 고객번호, COUNT(주문번호) AS '총 주문 횟수'
FROM 주문
GROUP BY 고객번호
HAVING COUNT(주문번호) >= 3
ORDER BY 2 DESC;

 

• WITH ROLLUP
  • 그룹별 소계 + 전쳬 합계를 함께 보여준다
  • GROUP BY절 다음에 WITH ROLLUP을 사용하면 그룹별 소계와 전체 합계를 같이 나타낼 수 있다
•  GROUPING()
  • WITH ROLLUP의 결과로 나온 NULL에 대해서는 1을 반환하고, 그렇지 않은 NULL에 대해서는 0을 반환

#담당직위가 대표이사인 고객에 대하여 지역별로 묶어서 고객수를 보이고, 전체 고객수도 함꼐 보이시오
SELECT 지역, COUNT(*) AS 고객수, GROUPING(지역)
FROM 고객
WHERE 담당자직위 = '대표 이사'
GROUP BY 지역
WITH ROLLUP;

 

• GROUP_CONCAT()
  • 각 행에 있는 값을 결합

#고객 테이블에 들어있는 지역을 한 행에 나열하되 중복되는 지역은 한번씩만 보이시오
SELECT GROUP_CONCAT(DISTINCT 지역) AS 지역
FROM 고객;

---

✅ 조인(Join)

1. 조인의 개념

• 두 개 이상의 테이블을 연결하여 데이터를 검색
• 서로 다른 테이블에 저장되어 관련된 데이터를 함께 가져와 하나의 결과로 표시함

---

2. 조인의 종류

• 크로스 조인 (CROSS JOIN) 
  • 한쪽 테이블의 각 행마다 다른 쪽 테이블의 모든 행이 한 번씩 각각 매칭되는 조인
  • 카티션 곱(Cartesian Product)이라고도 부름
  • Cross Join결과 행의 개수: 테이블A의 행의 개수 x 테이블 B의 행의 개수
  • 💡ON절 없이 사용

#사원 테이블과 부서 테이블을 크로스 조인하여 '배재용' 사원에 대한 정보
SELECT 부서.부서번호, 부서.부서명, 사원.이름, 사원.부서번호 #테이블명.컬럼이름
FROM 사원
CROSS JOIN 부서
WHERE 사원.이름 = '배재용';

---

• 내부 조인 (INNER JOIN)
  • 각 테이블에서 조인 조건에 일치되는 데이터만 가져오는 조인
  • 여러 테이블을 사용할 때 조인 조건을 제대로 기술하지 않으면 크로스 조인을 한 결과가 나오게 됨
  • 💡ON절과 함께 사용

#'이소미'사원의 사원번호, 직위, 부서번호, 부서명을 보이시오
SELECT 사원.사원번호, 사원.직위, 부서.부서번호, 부서.부서명
FROM 사원
INNER JOIN 부서 ON 사원.부서번호 = 부서.부서번호
WHERE 사원.이름 = '이소미';

---

• 외부 조인 (OUTER JOIN)
  • 조건에 맞지 않는 행도 결과에 포함시킬 수 있
  • 두 테이블 중 한쪽에는 데이터가 없더라도, 다른 쪽의 데이터를 기준으로 결과를 출력한다
  • ➕ 매칭되지 않는 쪽의 컬럼은 NULL로 채워진다 
    • LEFT JOIN: 왼쪽에 있는 테이블의 결과를 기준으로 오른쪽 테이블의 데이터를 매칭
    • RIGHT JOIN: 오른쪽에 있는 테이블의 결과를 기준으로 왼쪽 테이블의 데이터를 매칭
    • FULL OUTER JOIN: (MySQL은 지원 안 함): 양쪽 모두 기준으로, 매칭되지 않는 행도 포함

---

• 셀프 조인 (SELF JOIN)
  • 하나의 테이블을 두 번 참조하여 조인을 수행하는 방식
  • 💡같은 테이블을 서로 다른 별칭(Alias)으로 지정하여 서로 다른 테이블인 것처럼 사용한다

#사원번호, 사원의 이름, 상사의 사원번호, 상사의 이름을 보이시오
SELECT 사원.사원번호, 사원.이름, 상사.사원번호 as '상사 사원번호', 상사.이름 AS '상사 이름'
FROM 사원 
INNER JOIN 사원 AS 상사
ON 사원.상사번호 = 상사.사원번호;

---

✅ 서브쿼리 (SubQuery)

1. 서브쿼리의 개념

• SQL문 내부에서 사용하는 SELECT문
• 서브쿼리는 괄호 안에 기술해야 하며, WHERE절, SELECT절, FROM절, HAVING절 등에 넣어 사용 가능하다
• 일반적으로 서브쿼리가 먼저 실행된 후 메인 쿼리가 실행되는 순서로 작업이 이루어진다

---

2. 단일 행 서브쿼리 (Single-Row SubQuery)

• 서브쿼리의 결과로 단일 행을 반환
• =, <, <=, >, >=, <> 등의 단일 행 비교 연산자를 사용하여 메인 쿼리와 서브쿼리 연결 가능

#최고 마일리지를 보유한 고객의 정보를 보이시오
SELECT * #MAX마일리지 행을 찾음
FROM 고객
WHERE 마일리지 = (SELECT MAX(마일리지)
			   FROM 고객);

#주문번호가 'H0250'을 주문한 고객에 대해 고객회사명과 담당자명을 보이시오
SELECT 고객회사명, 담당자명
FROM 고객
WHERE 고객번호 = (SELECT 고객번호
			   FROM 주문
               WHERE 주문번호 = 'H0250');

---

3. 복수 행 서브쿼리 (Multi-Row SubQuery)

• 서브쿼리의 결과가 여러 행이 나오는 쿼리
• IN: 메인 쿼리의 비교 조건이 서브쿼리 결과 중 일치하는 것이 하나라도 있으면 참
• ALL: 비교연산자로 비교하여 모두 일치하면 참, 최대 결괏값과 비교
• ANY, SOME: 비교 연산자로 비교하여 하나 이상 일치하면 참
  • = ANY: 서브쿼리에 하나라도 일치하면 TRUE
  • > ANY: 최솟값보다 크면 TRUE
  • < ANY: 서브쿼리 결과중에 최댓값보다 작으면 TRUE
• EXISTS
  • 비교조건을 만족하는 결과가 존재하면 참
  • 컬럼으로 비교하지 않고 행의 존재 여부로 비교하므로 EXISTS 연산자 앞에는 컬럼명을 넣지 않음

#각 지역의 어느 평균 마일리지보다도 마일리지가 큰 고객의 정보를 보이시오
SELECT 담당자명, 고객회사명, 마일리지
FROM 고객 
WHERE 마일리지 > ALL (SELECT AVG(마일리지)
			       FROM 고객
                   GROUP BY 지역);

#한 번도 주문한적 없는 제품의 정보를 보이시오
SELECT 제품번호, 제품명, 포장단위, 단가, 재고, 단가 * 재고 AS 재고금액
FROM 제품
WHERE NOT EXISTS(SELECT *
			     FROM 주문세부
				 WHERE 제품.제품번호 = 주문세부.제품번호);

---

4. CTE (Common Table Expression)

• 쿼리로 만든 임시 데이터 셋으로 WITH 절에서 정의
• 인라인 뷰와 마찬가지로 파생 테이블처럼 사용 가능
• 하나의 쿼리를 논리적인 블록으로 나눌 수 있어, 가독성 좋음
• 쿼리 내에서 여러 번 참조할 수 있으므로 재사용성이 좋음

# CTE 예시
WITH 도시별요약 AS (
SELECT 도시 , AVG(마일리지) AS 도시_평균마일리지
FROM 고객
GROUP BY 도시
)
SELECT * , 도시_평균마일리지, (도시_평균마일리지 - 마일리지) AS 차이
FROM 고객 , 도시별요약
WHERE 고객.도시 = 도시별요약.도시;

---

✅ 데이터 조작어 (DML)

📌 데이터를 관리하는데 사용하는 언어

• SELECT
  • 데이터 조회할 때 사용
  • SELECT * FROM 테이블명
• INSERT
  • 테이블에 새로운 행을 삽입
  • 삽입할 값이 테이블의 컬럼 수와 순서가 동일해야 한다
  • INSERT INTO 테이블명 VALUES (값1, 값2, ...)
• UPDATE
  • 기존 행에 있는 데이터 값을 변경 할 때 사용
  • UPDATE 테이블명 SET 컬럼 WHERE
• DELETE
  • 기존에 있는 행을 삭제
  • DELETE 문에 WHERE절이 없으면 모든 행이 삭제되므로 주의
  • DELETE FROM 테이블명 WHERE 조건

---

✅ 데이터 정의어 (DDL)

📌 데이터베이스 내 테이블이나 인덱스, 뷰 등의 객체를 만들거나 수정, 삭제할 때 사용

• CREATE
  • 객체(테이블, 뷰 등) 생성
  • CREATE TABLE 테이블명 (컬럼명, 타입)
• ALTER
  • 객체 구조 수정 (컬럼 추가/변경/삭제)
  • ALTER TABLE 테이블명 ADD 컬럼명 타입
• DROP
  • 데이터베이스, 테이블 등 삭제
  • DROP TABLE 테이블명