코테 전에 보려고 만든, SQL 총정리!!!

[MYSQL]

1. NULL

IFNULL(변수명, 널값일경우) = 다른 값으로 대체

IFNULL(NAME, 'No name')

IS NULL / IS NOT NULL : 널값인지 아닌지 조건걸어서 출력해주기

WHERE NAME IS NULL

 

 

2.날짜 함수

DATEDIFF(날짜2,날짜1) = 날짜2-날짜1

DATEDIFF(END_DATE,START_DATE)

두 날짜 사이의 기간을 구하려면 DATEDIFF(END_DATE,START_DATE) +1

TYPE이 DATE인 경우, CREATED_DATE = '2022-10-05'로 바로 비교 가능

YEAR(), MONTH(), DAY() = 날짜변수의 연,월,일

DATE_FORMAT(DATE, '%Y-%m-%d')

%Y-%m-%d = 2022-05-01 형태

DATE_FORMAT(DATE,'%m') = 10 = 10월인 경우

 

DATE_FORMAT(START_DATE,'%Y-%m') BETWEEN '2022-08' AND '2022-10'

WHERE '2022-10-16' BETWEEN DATE_FORMAT(START_DATE,'%Y-%m-%d') AND DATE_FORMAT(END_DATE,'%Y-%m-%d')

WHERE HOUR(DATETIME) BETWEEN '9' AND '19'

 

 

3. 숫자 함수

AVG() 평균

MOD(N,M) = N을 M으로 나눈 나머지 = N % M = N MOD D

MAX(), MIN() 최대최소

WHERE VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)

-- 가장 비싼 식품의 정보 모두 출력
SELECT *
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT)

 

ROUND(숫자,반올림할 자릿수) 반올림

ROUND(3.333,2) = 3.33 <- 소숫점 세번째자리에서 반올림

CEIL() FLOOR() 올림/내림

TRUNCATE(숫자,버릴 자릿수) 뒤에서 4개 버리고 0으로 채움

TRUNCATE(22000,-4) = FLOOR(22000/10000)*10000 = 20000

 

 

4. 문자 함수

LIKE '_문자열%'

% 부분일치

_ 글자개수 일치

APNT_YMD = '2022-04-013 12:30:00' 와 같은 경우,  APNT_YMD LIKE '2022-04-13%'

WHERE OPTIONS LIKE "%네비게이션%"

CONCAT(문자1,문자2,문자3) = 문자 합쳐서 문자로 만들기

LEFT(문자,3), RIGHT(문자,3)

SUBSTRING(문자,4,4)

SUBSTRING('ABC',1) = 'ABC'

SUBSTRING('ABC',1,2) = 'AB'

NAME IN ("Lucy", "Ella", "Pickle", "Rogan", "Sabrina", "Mitty")

 

COUNT(*) = NULL 포함 건수 집계

COUNT(변수명) = NULL이 아닌 건수 집계

COUNT(DISTINCT 변수명) = 중복 제거 후, NULL이 아닌 건수 집계

COUNT(*) OVER() = 조회된 전체 건수를 각 행에 표시

COUNT(*) OVER(PARTITION BY 칼럼명) = 칼럼명별 전체 건수를 각 행에 표시

 

INSTR(문자열, 찾고자하는 문자열) = 위치 반환

INSTR('FOOBAR','BAR') = 4, INSTR('FOOBAR','TEST') = 0

LENGTH() 길이

REPLACE('ABC','A','C') = 'CBC' 문자열 치환

LOWER(), UPPER() 소/대문자 반환

TRIM(문자,특정문자) 특정문자/공백 삭제

TRIM('     ABC ') = 'ABC' 공백 삭제

 

 

5. 조건문

CASE

WHEN 조건1 THEN 값1

WHEN 조건2 THEN 값2

ELSE 값3

END AS 별칭

 

IF(조건문,TRUE 값, FALE값)

 

 

6. 정렬

DESC/ASC 내림차순/오름차순

ORDER BY 1 별칭사용 가능

LIMIT N 정렬 기준 상위 N개 출력

 

ORDER BY DATETIME
LIMIT 1

-> 가장 빠른 날짜 = 상위1개

 

GROUP BY USER_ID
HAVING COUNT(*) >= 3
ORDER BY USER_ID DESC;

 

 

7. JOIN과 UNION

FROM 테이블1,테이블2

 

FROM 테이블1

JOIN 테이블2

ON 테이블1.칼럼명1 = 테이블2.칼럼명2

 

JOIN = INNER JOIN = 교집합

 

https://hongong.hanbit.co.kr/sql-%EA%B8%B0%EB%B3%B8-%EB%AC%B8%EB%B2%95-joininner-outer-cross-self-join/

SQL 기본 문법: JOIN(INNER, OUTER, CROSS, SELF JOIN)

 

 

SELECT 절

UNION

SELECT 절

 

UNION 할때 칼럼 모두 같아야함. 만약 없는 칼럼있다면, NULL AS USER_ID 이런 식으로 지정해줘야함

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

프로그래머스

 

+ 별칭

AS 문자열 <- 문자열에 ' ' 안해도 된다.

별칭 사용 가능 = HAVING절

별칭 사용 불가능 = WHERE절

 

 

 

<어려운 문제 LIST>

서브쿼리

1. 두개 칼럼을 WHERE IN 서브쿼리로 뽑기

-- FOOD_TYPE 별 가장 많은 FAVORITES
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
                                FROM REST_INFO
                                GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC

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

프로그래머스

 

2. GROUP BY 두번해야해서 IN 서브쿼리

-- 월별 자동차 ID별 총대여횟수가 5회 이상인 총대여횟수 구하기
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
        SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
        WHERE DATE_FORMAT(START_DATE,'%Y-%m') BETWEEN '2022-08' AND '2022-10'
        GROUP BY CAR_ID
        HAVING COUNT(CAR_ID) >= 5
    )
    AND DATE_FORMAT(START_DATE,'%Y-%m') BETWEEN '2022-08' AND '2022-10'
GROUP BY MONTH, CAR_ID 
ORDER BY MONTH ASC, CAR_ID DESC;

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

프로그래머스

 

3.각 카테고리별 최대값 구하기

서브쿼리 안에서 GROUP BY 필요한 문제.

서브쿼리를 안하면, 최대값 1개만 나옴.

-- 카테고리별 가장 비싼 가격 조회
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN (
    SELECT MAX(PRICE)
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY
)
AND CATEGORY IN ('과자','국','김치','식용유')
ORDER BY MAX_PRICE DESC;

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

프로그래머스

 

4. CASE WHEN 절에 서브쿼리로 조건 걸어주기

-- 2022-10-16일에 대여중이 아니고 + 자동차별로 GROUP BY 필요
SELECT CAR_ID,
CASE WHEN CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-10-16' BETWEEN DATE_FORMAT(START_DATE,'%Y-%m-%d') AND DATE_FORMAT(END_DATE,'%Y-%m-%d')
    )
THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY

FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

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

프로그래머스

 

5. WHERE NOT IN 서브쿼리

22년 11월 1일 부터 30일까지 대여 가능 + 30일 이상 조건을 반대로 NOT IN으로 바꿔서 생각

SELECT 절에서 명시한 FEE 값을 조건을 걸어줘야해서 무조건 GROUP BY가 있어야 함

하지만 날짜는 WHERE 절에서 조건을 걸어줘야 하므로, 서브쿼리를 사용한다.

SELECT A.CAR_ID, A.CAR_TYPE, ROUND(A.DAILY_FEE*30*(100-P.DISCOUNT_RATE)/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS A
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON A.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON A.CAR_TYPE = P.CAR_TYPE

WHERE A.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE END_DATE > "2022-11-01" AND START_DATE < "2022-12-01"
) AND P.DURATION_TYPE = '30일 이상'

GROUP BY A.CAR_ID
HAVING A.CAR_TYPE IN ('세단', 'SUV') AND (FEE >= 500000 AND FEE < 2000000)
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

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

프로그래머스

 

6. JOIN 할때 서브쿼리

7월 전체 총주문량을 GROUP BY로 묶고 또 거기에 상반기 총주문량을 더해야 했기 때문에 서브쿼리 필요

SELECT A.FLAVOR
FROM FIRST_HALF AS A
JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
      FROM JULY
      GROUP BY FLAVOR
      ) AS B
ON A.FLAVOR = B.FLAVOR
ORDER BY (A.TOTAL_ORDER + B.TOTAL_ORDER) DESC
LIMIT 3;

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

프로그래머스

 

7. 가장 많이 리뷰를 쓴 사람 구하기

MAX(리뷰) -> 그런 사람 = 서브쿼리 두개

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, "%Y-%m-%d") REVIEW_DATE
FROM MEMBER_PROFILE AS M 
JOIN REST_REVIEW AS R
ON M.MEMBER_ID = R.MEMBER_ID
-- 가장 많이 쓴 리뷰 개수를 쓴 사람 구하기
WHERE R.MEMBER_ID IN
    (SELECT MEMBER_ID FROM REST_REVIEW GROUP BY MEMBER_ID HAVING COUNT(*) =
    	-- 가장 많이 쓴 리뷰 개수 구하기
        (SELECT MAX(CNT) FROM
        	-- 리뷰 개수 구하기
            (SELECT COUNT(*) AS CNT FROM REST_REVIEW GROUP BY MEMBER_ID) AS A
        )
     )
ORDER BY R.REVIEW_DATE ASC;

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

프로그래머스

 

8. 2021년에 가입한 전체 회원수, 2021년에 가입한 회원 중 상품을 구매한 회원수

두 값을 동시에 구하지 못함! 서브쿼리문 필요하다.

SELECT YEAR, MONTH, COUNT(*) AS PUCHASED_USERS,
ROUND((COUNT(*)/(SELECT COUNT(*)
                 FROM USER_INFO
                 WHERE YEAR(JOINED) = 2021)),1) AS PUCHASED_RATIO

FROM (SELECT DISTINCT YEAR(S.SALES_DATE) AS YEAR, MONTH(S.SALES_DATE) AS MONTH, U.USER_ID
      FROM ONLINE_SALE AS S
      JOIN USER_INFO AS U
      ON S.USER_ID = U.USER_ID AND YEAR(JOINED) = 2021
     ) AS A
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

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

프로그래머스

 

 

SET @변수명 = 값;

1. @HOUR := @HOUR + 1

@HOUR += 1 1씩 더해주라는 뜻

SET @HOUR = -1;

SELECT (@HOUR := @HOUR + 1) AS HOUR,
(SELECT COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME)=@HOUR) AS COUNT

FROM ANIMAL_OUTS
WHERE @HOUR < 23;

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

프로그래머스