Post

내일배움캠프 사전캠프 온라인강의 SQL 3주차, 4주차

Posted
By Hoon
3 min read

🚀 공부한 내용

문자 데이터 가공 (REPLACE, SUBSTRING, CONCAT)

  • REPLACE
    1
2
3
4

    SELECT restaurant_name AS "원래 상점명",
       REPLACE(restaurant_name, 'Blue', 'Pink') AS "바뀐 상점명"
FROM food_orders
WHERE restaurant_name LIKE '%Blue Ribbon%';
  • SUBSTRING
    1
2
3
4

    SELECT addr AS "원래 주소",
       SUBSTRING(addr, 1, 2) AS "시도"
FROM food_orders
WHERE addr LIKE '%서울특별시%';

    ‘SUBSTR’ 로 줄여쓸 수 있다.

  • CONCAT
    1
2
3
4
5
6

    SELECT restaurant_name AS "원래 이름",
       addr AS "원래 주소",
       CONCAT(restaurant_name, '-', cuisine_type) AS "음식타입별 음식점",
       CONCAT('[', SUBSTRING(addr, 1, 2), '] ', restaurant_name) AS "바뀐 이름"
FROM food_orders
WHERE addr LIKE '%서울%'

문자 데이터 가공 후 GROUP BY

1
2
3
4
5
6

SELECT SUBSTR(addr, 1, 2) AS "지역",
       cuisine_type,
       AVG(price) AS "평균 금액"
FROM food_orders
WHERE addr LIKE '%서울%'
GROUP BY 1, 2

1
2
3
4
5

SELECT SUBSTR(email, 10) AS "도메인",
       COUNT(*) AS "고객 수",
       AVG(age) AS "평균 연령"
FROM customers
GROUP BY 1;

IF, CASE

  • IF
    1
2
3
4

    SELECT addr AS "원래 주소",
       IF (addr LIKE '%평택군%', REPLACE(addr, '문곡리', '문가리'), addr) AS "바뀐 주소"
FROM food_orders
WHERE addr LIKE '%문곡리%';

    1
2
3
4
5

    SELECT SUBSTRING(IF(email LIKE '%gmail%', REPLACE(email, 'gmail', '@gmail'), email), 10) AS "이메일 도메인",
       COUNT(customer_id) AS "고객 수",
       AVG(age) AS "평균 연령"
FROM customers
GROUP BY 1;
  • CASE
    1
2
3
4
5
6
7
8
9
10

    SELECT CASE WHEN delivery_time > 30 THEN price * 0.1 * IF(addr LIKE '%서울%', 1.1, 1)
            WHEN delivery_time > 25 THEN price * 0.5 * IF(addr LIKE '%서울%', 1.1, 1)
            else 0
       END "수수료",
       restaurant_name,
       order_id,
       price,
       delivery_time,
       addr
FROM food_orders;

    CASE문 안에 IF문도 사용이 가능하다.

데이터타입 관련 에러가 났을 때 (CAST)

  • 숫자로 변경
    1

    CAST(IF(rating = 'Not given', '1', rating) AS DECIMAL)
  • 문자로 변경
    1

    CONCAT(restaurant_name, '-', CAST(order_id AS CHAR))

SUBQUERY

1
2
3
4
5
6

SELECT order_id, restaurant_name, IF(over_time >= 0, over_time, 0) over_time
FROM 
  (
    SELECT order_id, restaurant_name, food_preparation_time - 25 over_time
    FROM food_orders
  ) a

JOIN (LEFT JOIN, INNER JOIN)

  • LEFT JOIN
    1
2

    SELECT *
FROM food_orders LEFT JOIN payments ON food_orders.order_id = payments.order_id

    1
2

    SELECT *
FROM food_orders f LEFT JOIN payments p ON f.order_id = p.order_id

    payments 테이블에 값이 없더라도 다 가져온다.

  • INNER JOIN
    1
2

    SELECT *
FROM food_orders INNER JOIN payments ON food_orders.order_id = payments.order_id

    두 테이블에 값이 있는 것만 가져온다.

🚀 회고

실무에서 정말 많이 썼던 문법들인데
까먹지 않도록 공부를 게을리하지 않아야겠다.

내일배움캠프
내일배움캠프 사전캠프 TIL