MySQL 문법 정리 (w. 프로그래머스 SQL Lv. 4 문제 풀이 -1)

[ 이전글 (프로그래머스 SQL 3단계 문제 풀이 및 문법 정리) ]

https://maemae22.tistory.com/118

MySQL 문법 정리 (w. 프로그래머스 SQL Lv. 3 문제 풀이)

---

[1] JOIN, LIKE, REGEXP(LIKE 여러개), 서브쿼리

# https://school.programmers.co.kr/learn/courses/30/lessons/59045

# 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다.
# 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 아이디 순으로 조회하는 SQL 문을 작성해주세요.
# 중성화를 거치지 않은 동물은 성별 및 중성화 여부에 Intact, 중성화를 거친 동물은 Spayed 또는 Neutered라고 표시되어있습니다.
SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID=O.ANIMAL_ID
    WHERE I.SEX_UPON_INTAKE LIKE '%Intact%' AND (O.SEX_UPON_OUTCOME LIKE '%Spayed%' OR O.SEX_UPON_OUTCOME LIKE '%Neutered%')
    ORDER BY I.ANIMAL_ID ASC

SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID=O.ANIMAL_ID
    WHERE I.SEX_UPON_INTAKE LIKE '%Intact%' AND O.SEX_UPON_OUTCOME REGEXP ('Spayed|Neutered')
    ORDER BY I.ANIMAL_ID ASC

SELECT ANIMAL_ID, ANIMAL_TYPE, NAME FROM ANIMAL_INS
    WHERE ANIMAL_ID IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS WHERE SEX_UPON_OUTCOME REGEXP ('Spayed|Neutered'))
      AND SEX_UPON_INTAKE LIKE '%Intact%'
    ORDER BY ANIMAL_ID ASC

SELECT ANIMAL_ID, ANIMAL_TYPE, NAME FROM ANIMAL_INS
    WHERE SEX_UPON_INTAKE LIKE '%Intact%'
      AND ANIMAL_ID IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS WHERE SEX_UPON_OUTCOME REGEXP ('Spayed|Neutered'))
    ORDER BY ANIMAL_ID ASC

---

1. JOIN :

SELECT ~~ FROM TABLE_A A (LEFT|생략(INNER)|RIGHT|FULL) JOIN TABLE_B B ON A.KEY=B.KEY

2. LIKE : 특정 문자 포함되어 있는지 검색하기

% : 0개 이상의 문자
_ : 1개의 문자

WHERE 컬럼명 LIKE '값%' : 값으로 시작하는 경우만
WHERE 컬럼명 LIKE '%값' : 값으로 끝나는 경우만
WHERE 컬럼명 LIKE '%값%' : 값이 들어가는(포함되는) 경우 전체

WHERE 컬럼명 LIKE '% 값 %' : 앞 뒤 공백까지 포함해서 검색하므로 주의 !! 공백 제거 원할 경우 TRIM 사용하기

WHERE 컬럼명 LIKE 'm_ri_' : 5개의 글자이면서, 1.3.4번째 문자열이 m.r.i이고, 2.5번째 문자열은 어떤 문자열이라도 허용함
ㄴ ex. maria, mvriz, mqrip, m9ri8

- 만약 검색하려는 문자열이 WILDCARD 인 _이거나 % 인 경우에는?
 : WILDCARD인 _ % 앞에 \(역슬레쉬)를 넣어주면 된다 !
 
WHERE 컬럼명 LIKE '%\%%' : %(퍼센트) 표기가 들어있는 데이터들을 모두 불러오는 쿼리

3. REGEXP : LIKE 여러개 OR로 검색하기 (LIKE IN 같은 느낌)

WHERE 컬럼명 REGEXP ('문자열1|문자열2|문자열3')
ㄴ 괄호는 생략 가능

= WHERE 컬럼명 LIKE '%문자열1%'
    OR 컬럼명 LIKE '%문자열2%'
    OR 컬럼명 LIKE '%문자열3%'

---

[2] 서브쿼리 다중 조건, IN절, GROUP BY, WHERE vs. HAVING

# https://school.programmers.co.kr/learn/courses/30/lessons/131116

# FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요.
# 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME FROM FOOD_PRODUCT
    WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY IN ('과자', '국', '김치', '식용유') GROUP BY CATEGORY)
    ORDER BY MAX_PRICE DESC

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME FROM FOOD_PRODUCT
    WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY HAVING CATEGORY IN ('과자', '국', '김치', '식용유'))
    ORDER BY MAX_PRICE DESC

---

서브쿼리 다중 조건 방법

SELECT * FROM 테이블명 WHERE (컬럼1, 컬럼2) IN (SELECT 서브쿼리_컬럼1, 서브쿼리_컬럼2 FROM 서브쿼리_테이블);

---

GROUP BY 할 때, WHERE과 HAVING의 성능 차이

위의 두 쿼리 모두 같은 검색 결과를 반환하며, 두개 모두 정답이다.

두 쿼리의 차이점은 특정 CATEGORY만 뽑을 때, WHERE절로 먼저 필요한 행만 뽑은 뒤 그룹핑할 것이냐, 그룹핑을 한 뒤 조건에 맞는 그룹만 뽑을 것이냐의 차이이다.

결론부터 말하자면, WHERE+GROUP BY가 GROUP BY+HAVING보다 성능이 좋다 !!

 

WHERE절에 의해 그룹화 과정에 불필요한 행을 미리 제외한 후에 GROUP BY절을 실행하면 내부정렬에 필요한 행의 수를 줄여주므로 효율적이기 때문이다.

반면, HAVING을 사용할 경우 GROUP BY절에 의해 전체 행 집합을 먼저 정렬한 후에 HAVING절을 적용하여 조건에 맞는 그룹만 가져오는 방식이므로 비효율적이다.

 

따라서 위와 같이 같은 내용의 조건이라면, WHERE+GROUP BY를 사용하도록 하자 !

---

IN절

WHERE 일치하길 원하는 컬럼명 IN (조건1, 조건2, 조건3, ....)
WHERE 일치하지 않길 원하는 컬럼명 NOT IN (조건1, 조건2, 조건3, .....)

WHERE 일치하길 원하는 컬럼명 IN (조건) : 다중 조건이 아니여도 가능함

- 조건들을 OR 관계로 묶어 검색함 (IN 연산자가 OR 연산자 보다 실행 속도가 빠름 !)

- 값은 콤마( , )로 구분하여 괄호 내에 묶으며, 이 값 중에서 하나 이상과 일치하면 조건에 맞는 것으로 평가된다.

---

[3] JOIN + GROUP BY, SUM 함수

# https://school.programmers.co.kr/learn/courses/30/lessons/131117

# FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요.
# 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, P.PRICE*O.SUM TOTAL_SALES FROM FOOD_PRODUCT P JOIN
    (SELECT PRODUCT_ID, SUM(AMOUNT) SUM FROM FOOD_ORDER WHERE DATE_FORMAT(PRODUCE_DATE, "%Y%m")='202205' GROUP BY PRODUCT_ID) O ON P.PRODUCT_ID=O.PRODUCT_ID
    ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, P.PRICE*SUM(O.AMOUNT) TOTAL_SALES FROM FOOD_PRODUCT P JOIN FOOD_ORDER O ON P.PRODUCT_ID=O.PRODUCT_ID
    WHERE DATE_FORMAT(O.PRODUCE_DATE, "%Y%m")='202205'
    GROUP BY P.PRODUCT_ID
    ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(O.AMOUNT*P.PRICE) TOTAL_SALES FROM FOOD_PRODUCT P JOIN FOOD_ORDER O ON P.PRODUCT_ID=O.PRODUCT_ID
    WHERE DATE_FORMAT(O.PRODUCE_DATE, "%Y%m")='202205'
    GROUP BY P.PRODUCT_ID
    ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC

---

[4] ROUND, AVG 함수

# https://school.programmers.co.kr/learn/courses/30/lessons/131118

# REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요.
# 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.
SELECT I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS, ROUND(AVG(R.REVIEW_SCORE), 2) SCORE FROM REST_INFO I
    JOIN REST_REVIEW R ON I.REST_ID=R.REST_ID
    WHERE I.ADDRESS LIKE '서울%'
    GROUP BY I.REST_ID
    ORDER BY SCORE DESC, I.FAVORITES DESC

SELECT I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS, R.SCORE FROM REST_INFO I
    JOIN (SELECT REST_ID, ROUND(AVG(REVIEW_SCORE), 2) SCORE FROM REST_REVIEW GROUP BY REST_ID) R ON I.REST_ID=R.REST_ID
    WHERE I.ADDRESS LIKE '서울%'
    ORDER BY R.SCORE DESC, I.FAVORITES DESC

---

[ 숫자 함수 ]

1. ROUND(숫자, 반올림할 자릿수) : 숫자를 반올림할 자릿수+1 자릿수에서 반올림
2. TRUNCATE(숫자, 버릴 자릿수) : 숫자를 버릴 자릿수 아래로 버림 (※ 반드시 버릴 자릿수를 명시해 주어야 함)

SELECT ROUND(3456.1234567) FROM DUAL // 3456
SELECT ROUND(3456.1234567 ,1) FROM DUAL // 3456.1
SELECT ROUND(3456.1234567 ,4) FROM DUAL // 3456.1235
SELECT ROUND(3456.1234567 ,-1) FROM DUAL // 3460
SELECT ROUND(3456.1234567 ,-2) FROM DUAL // 3500

SELECT TRUNCATE(3456.1234567 ,1) FROM DUAL // 3456.1
SELECT TRUNCATE(3456.1234567 ,4) FROM DUAL // 3456.1234
SELECT TRUNCATE(3456.1234567 ,-1) FROM DUAL // 3450
SELECT TRUNCATE(3456.1234567 ,-2) FROM DUAL // 3400

3. CEIL(숫자), CEILING(숫자) : 올림 - 정수값 출력

4. FLOOR(숫자) : 내림/버림 - 정수값 출력

SELECT CEIL(21.35) // 22 
SELECT CEIL(21.9) // 22

SELECT FLOOR(21.35) // 21 
SELECT FLOOR(21.9) // 21

5. ABS(숫자) : 절대값

6. POW(A, B), POWER(A, B) : A의 B승(A를 B만큼 제곱)

7. SQRT(숫자) : 제곱근   ex. SQRT(16)=POWER(16, 1/2)=4

8. MOD(분자, 분모) : 분자를 분모로 나눈 나머지 반환

 

9. GREATEST(숫자1, 숫자2, ...) : 주어진 숫자 중에 가장 큰 값을 반환, (괄호 안에서) 가장 큰 값
10. LEAST(숫자1, 숫자2, ...) : 주어진 숫자 중에 가장 작은 값을 반환, (괄호 안에서) 가장 작은 값

( != MAX, MIN : 여러 행들 중에서 최댓값과 최솟값을 구함 )

SELECT
  OrderDetailID, ProductID, Quantity,
  GREATEST(OrderDetailID, ProductID, Quantity),
  LEAST(OrderDetailID, ProductID, Quantity)
FROM OrderDetails;

---

[ 집계 함수(그룹 함수) ] : 주로 GROUP BY와 함께 사용 !

MAX(컬럼)	해당 컬럼 값들 중 가장 큰 값 (최댓값)
MIN(컬럼)	해당 컬럼 값들 중 가장 작은 값 (최솟값)
COUNT(컬럼)	해당 컬럼의 데이터 갯수 (NULL값 제외)
SUM(컬럼)	해당 컬럼 값들의 총합
AVG(컬럼)	해당 컬럼 값들의 평균 값

---

[5] COUNT+DISTINCT, GROUP BY 3번

# https://school.programmers.co.kr/learn/courses/30/lessons/131532

# USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요.
# 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.
SELECT YEAR(S.SALES_DATE) YEAR, MONTH(S.SALES_DATE) MONTH, U.GENDER, COUNT(DISTINCT S.USER_ID) USERS FROM ONLINE_SALE S
    JOIN USER_INFO U ON S.USER_ID=U.USER_ID
    WHERE U.GENDER IS NOT NULL
    GROUP BY YEAR(S.SALES_DATE), MONTH(S.SALES_DATE), U.GENDER
    ORDER BY YEAR ASC, MONTH ASC, GENDER ASC

1. COUNT : 데이블에 컬럼의 데이터 개수 반환 (NULL인 데이터는 제외하고 카운팅, 중복 제거 X)

    ㄴ COUNT(*) 일 경우에는, NULL 값이 존재하는 행도 포함한다.

2. DISTINCT : 중복을 제거해줌.

    ex. DISTINCT 컬럼, SELECT DISTINCT 컬럼 FROM 테이블 WHERE 조건식;

3. COUNT+DISTINCT : NULL 값이 아닌 데이터 중에서 중복을 제거한 개수를 반환 

    ex. COUNT(DISTINCT 컬럼), COUNT(DISTINCT S.USER_ID)

---