[스타터스 TIL] 9일차.SQL을 통한 데이터활용과 분석 (2) - 기본 문법

---

1. 새롭게 알게된 내용

1.1 단일조건 if vs 다중조건 case when

✅ 단일조건 if

- IF (조건, 참일 때 값, 거짓일 때 값)

SELECT IF (100 > 300, '크다', '작다');

 

✅ 다중조건 case when

- CASE WHEN 조건1 THEN 반환값1

             WHEN 조건2 THEN 반환값2

             ELSE 충족되는 조건이 없을 때 반환값

   END

SELECT custid
     , SUM(saleprice) AS '총구매액'
     , (CASE WHEN (SUM(saleprice) >= 60000) THEN '최우수고객'
             WHEN (SUM(saleprice)  >= 45000) THEN '우수고객'
             WHEN (SUM(saleprice)  >= 30000 ) THEN '일반고객'
             ELSE '유령고객'
         END) AS '고객등급'
FROM Orders
GROUP BY custid;

 

1.2 내장함수

- 집계함수 이외의 내장함수

 

✅ POWER(숫자, 제곱) / POW(숫자, 제곱) - 제곱

SELECT power(2,1)
     , power(2,2)
     , power(2,3);

 

✅ ROUND(숫자, 반올림할 소수점) - 반올림

SELECT round(1234.123, 1)
     , round(1234.123, -1);

 

✅ MOD(N, M) - 나머지

N을 M으로 나눈 나머지

SELECT MOD(9, 2)
     , MOD(17, 5);

✅ TRIM() : 문자열 좌우 공백 제거

- TRIM(BOTH FROM 문자열) : 문자열 좌우 공백 제거
- TRIM(LEADING FROM 문자열) : 문자열 좌측 공백 제거
- TRIM(TRAILING FROM 문자열) : 문자열 우측 공백 제거

- TRIM(BOTH 문자 FROM 문자열) : 문자열 좌우 문자 제거
- TRIM(LEADING 문자 FROM 문자열) : 문자열 좌측 문자 제거
- TRIM(TRAILING 문자 FROM 문자열) : 문자열 우측 문자 제거

- LTRIM() : 문자열 좌측 공백/문자 제거
- RTRIM() : 문자열 우측 공백/문자 제거

SELECT TRIM(' 안녕하세요 ');

-- 문자열 좌우 공백, 문자 제거 (BOTH)
SELECT TRIM(BOTH FROM ' 안녕하세요 ');
SELECT TRIM(BOTH '안' FROM '안안녕하세요안');  -- [OUT] 녕하세요

-- 문자열 좌측 공백, 문자 제거 (LEADING)
SELECT TRIM(LEADING FROM ' 안녕하세요 ');
SELECT TRIM(LEADING '안' FROM '안안녕하세요');  -- [OUT] 녕하세요

-- 문자열 우측 공백, 문자 제거 (TRAILING)
SELECT TRIM(TRAILING FROM ' 안녕하세요 ');
SELECT TRIM(TRAILING '요' FROM '안녕하세요요');  -- [OUT] 안녕하세

-- 문자열 좌측 공백 제거
SELECT LTRIM(' 안녕하세요 ');

-- 문자열 우측 공백 제거
SELECT RTRIM(' 안녕하세요 ');

 

✅ 문자 갯수 함수

- LENGTH() : 바이트 출력 (1개의 영문자는 1byte)

- CHAR_LENGTH() / CHARACTER_LENGTH() : 문자열 갯수 출력

-- BYTE 출력
SELECT LENGTH('Hello');  -- [OUT] 5
SELECT LENGTH('안녕'); -- [OUT] 6

-- 문자 갯수 출력
SELECT CHAR_LENGTH('Hello');  -- [OUT] 5
SELECT CHARACTER_LENGTH('Hello');  -- [OUT] 5

SELECT CHAR_LENGTH('안녕');  -- [OUT] 2
SELECT CHARACTER_LENGTH('안녕');  -- [OUT] 2

 

✅ 결합 함수

- CONCAT() : 문자열 결합
- CONCAT_WS('구분자', 문자열/컬럼, 문자열/컬럼) : 문자열/컬럼을 결합할 때 구분자 사용
- GROUP_CONCAT() : 특정 컬럼의 각 결과값을 하나의 가로열(row)로 표시

SELECT  concat('홍길동', '모험');    -- [OUT] 홍길동모험
SELECT  concat_ws(',', '홍길동', '모험');   -- [OUT] 홍길동,모험
SELECT GROUP_CONCAT(username, ":", phone) AS "전화" FROM customer;
-- [OUT] '박지성:010-1234-1010,김연아:010-1223-3456,장미란:010-4878-1901,추신수:010-8000-8765,박보영:010-8214-9010,홍길동:11111'

 

✅ 문자열 추출 함수

- substring(문자열, 인덱스) : 인덱스에 해당하는 문자 출력
- substring_index(문자열, 구분자, 인덱스) : 문자열에서 구분자를 기준으로 인덱스만큼의 문자 추출
- LEFT(문자열, 숫자), RIGHT(문자열, 숫자) : 문자열에서 왼쪽/오른쪽에서 숫자 갯수만큼 문자 출력

SELECT SUBSTRING('안녕하세요', 2, 3);  -- [OUT] 녕하세
SELECT SUBSTRING_INDEX('ars420.tistory.com', '.', 1); -- [OUT] ars420

# 첫번째 구분자 전까지의 문자열
SELECT SUBSTRING_INDEX('안.녕.하.세.요', '.', 1);  -- [OUT] 안
# 두번째 구분자 전까지의 문자열
SELECT SUBSTRING_INDEX('안.녕.하.세.요', '.', 2);  -- [OUT] 안.녕
# 뒤에서 3번째 구분자 이후의 문자열
SELECT SUBSTRING_INDEX('안.녕.하.세.요', '.', -3); -- [OUT] 하.세.요

SELECT LEFT('안녕하세요', 3);   -- [OUT] 안녕하
SELECT RIGHT('안녕하세요', 3);   -- [OUT] 하세요

 

 

1.3 날짜 데이터 처리

✅ 날짜 데이터에서 일, 월, 연도, 입력한 월의 마지막 날짜 구하기

SELECT DAY('2023-02-16');  -- [OUT] 16
SELECT MONTH('2023-02-16');  -- [OUT] 2
SELECT YEAR('2023-02-16');  -- [OUT] 2023
SELECT LAST_DAY('2023-02-16');  -- [OUT] 2023-02-28

 

✅ 현재 날짜 구하기

# 날짜
SELECT CURDATE();       -- [OUT] 2023-02-16
SELECT CURRENT_DATE();  -- [OUT] 2023-02-16

# 시간
SELECT CURTIME();       -- [OUT] 17:11:40
SELECT CURRENT_TIME;    -- [OUT] 17:11:40

# 날짜 + 시간
SELECT NOW();             -- [OUT] 2023-02-16 17:11:40
SELECT SYSDATE();         -- [OUT] 2023-02-16 17:11:40
SELECT CURRENT_TIMESTAMP  -- [OUT] 2023-02-16 17:11:40

 

✅ 날짜/시간 증감 함수

-- DATE 기준으로 더하기
SELECT DATE_ADD('2023-02-16', INTERVAL 1 DAY);  -- [OUT] 2023-02-17
SELECT ADDDATE('2023-02-16', INTERVAL 1 DAY);  -- [OUT] 2023-02-17

-- DATE 기준으로 빼기
SELECT DATE_SUB('2023-02-16', INTERVAL 1 DAY);  -- [OUT] 2023-02-15
SELECT SUBDATE('2023-02-16', INTERVAL 1 DAY);  -- [OUT] 2023-02-15

-- 기준 TIME에서 시간 더하고 빼기
SELECT ADDTIME('2023-02-16 17:11:40', '01:00:00');   -- [OUT] 2023-02-16 18:11:40
SELECT SUBTIME('2023-02-16 17:11:40', '01:00:00');   -- [OUT] 2023-02-16 16:11:40

 

✅ 날짜/시간 사이의 차이

SELECT DATEDIFF('2023-02-16', '2023-02-14');  -- [OUT] 2
SELECT DATEDIFF('2023-02-16', '2023-02-18');  -- [OUT] -2

SELECT TIMEDIFF('2023-02-16 12:00:00', '2023-02-16 00:00:00');  -- [OUT] 12:00:00

 

✅ 날짜, 시간에서 특정 정보 출력

-- 요일을 숫자로 출력
SELECT DAYOFWEEK('2023-02-16');  -- [OUT] 5 (일 : 1 ~ 토 : 7)

-- 요일을 영문으로 출력
SELECT MONTHNAME('2023-02-16');  -- [OUT] February

-- 1년 중 몇 번째 날인지 출력
SELECT DAYOFYEAR('2023-02-16');  -- [OUT] 47

-- 시간을 초 단위로 변환
SELECT TIME_TO_SEC('12:00:00');  -- [OUT] 43200

 

✅ 그 외 날짜 관련 함수

-- 연도의 첫날부터 정수만큼 더한 날짜 출력
SELECT MAKEDATE('2023', 47);  -- [OUT] 2023-02-16

-- 시, 분, 초를 이용하여 '시:분:초' 형식으로 출력
SELECT MAKETIME(12, 0, 0);  -- [OUT] 12:00:00

-- 날짜를 FORMAT 형식으로 변환
SELECT DATE_FORMAT('2023-02-16 00:00:00', '%Y-%m-%d');  -- [OUT] 2023-02-16

-- 날짜가 4분기 중 몇 분기에 속하는 지 출력
SELECT QUARTER('2023-02-16');  -- [OUT] 1

 

1.4 자료형 변환 함수

- CAST(변환할 데이터 AS 자료형)

- CONVERT(변환할 데이터, 자료형)

-- 1. 평균 구매 가격 구하기
SELECT AVG(SALEPRICE) AS '평균 구매 가격'
FROM ORDERS;  # 23031.2500

-- 2. 평균 구매 가격을 정수로 변경하기
-- CAST 함수
SELECT CAST(AVG(SALEPRICE) AS SIGNED INTEGER) AS '평균 구매 가격'
FROM ORDERS;  # 23031

-- CONVERT 함수
SELECT CONVERT(AVG(SALEPRICE), SIGNED INTEGER) AS '평균 구매 가격'
FROM ORDERS;  # 23031

-- CAST(데이터 AS 타입)
SELECT CAST(20230216 AS DATE);  -- [OUT] '2023-02-16'

-- CONVERT(데이터, 타입)
SELECT CONVERT(20230216, DATE);  -- [OUT] '2023-02-16'

 

✅ 다양한 구분자를 날짜 형식으로 변경

-- [OUT] 2021-09-20
SELECT CAST('2021$09$20' AS DATE) AS '날짜';
SELECT CAST('2021/09/20' AS DATE) AS '날짜';
SELECT CAST('2021%09%20' AS DATE) AS '날짜';
SELECT CAST('2021@09@20' AS DATE) AS '날짜';

 

✅ 날짜 데이터, 시간 데이터 형식으로 변경

SELECT CAST('2020-10-19 12:35:29.123' AS DATE) AS 'DATE';  -- [OUT] 2020-10-19
SELECT CAST('2020-10-19 12:35:29.123' AS TIME) AS 'TIME';  -- [OUT] 12:35:29
SELECT CAST('2020-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME';  -- [OUT] 2020-10-19 12:35:29
SELECT CAST('2020-10-19' AS DATETIME) AS 'DATETIME';   -- [OUT] 2020-10-19 00:00:00

 

1.5 암묵적 형 변환

연산의 결과에 따른 규칙으로 형을 변환한다.

-- 문자열을 정수로 변환하여 처리
SELECT '100' + '200';  -- [OUT] 300
-- 문자열 그대로 처리
SELECT CONCAT('100', '200');  -- [OUT] 100200
-- 정수를 문자로 변환하여 처리
SELECT CONCAT(100, '200');  -- [OUT] 100200

-- 정수 3으로 변환한 후 비교
SELECT 1 > '3mega';  -- [OUT] 0
SELECT 4 > '3mega';  -- [OUT] 1

-- 문자열이 0으로 변환한 후 비교
SELECT 0 = 'mega3';  -- [OUT] 1

 

1.6 서브쿼리

PRODUCT 테이블

 

❓ 제품 판매 가격이 평균보다 큰 제품은?

→ WHERE 절에 집계함수를 사용할 수 없기 때문에 서브쿼리를 사용해야 한다. (단, HAVING 절에는 집계함수를 사용할 수 있다.)

SELECT *
FROM PRODUCT
WHERE PRICE >= (SELECT AVG(PRICE) FROM PRODUCT);

 

✅ Inline View (인라인 뷰)

서브쿼리가 FROM절 안에 사용될 경우, 해당 서브쿼리를 인라인 뷰라고 한다.

서브쿼리의 결과가 하나의 테이블에 대한 뷰처럼 사용되기 때문에 인라인 뷰라고 한다.

인라인 뷰를 사용할 때에는, 반드시 서브쿼리의 결과에 대해 Alias를 지정해줘야 한다.

SELECT CODE
	 , NAME
	 , MIN(PRICE)
FROM (SELECT * FROM PRODUCT WHERE PRICE >= 2000) AS C_PRICE;

 

✅ 상관서브쿼리 (Correlated Subquery)

바깥쪽 쿼리의 컬럼 중에 하나가 안쪽 서브쿼리의 조건에 이용되며, 그 결과는 다시 바깥쪽 쿼리에 영향을 주는 쿼리이다.

 

BOOK 테이블

 

❓ 출판사 별로 각 출판사의 평균 도서 가격보다 비싼 도서를 구하시오.

→ 바깥쪽 쿼리 데이터의 출판사 별로 서브쿼리에서 평균 가격을 계산하고 가격끼리 비교하여 출력한다.

SELECT publisher
     , b1.bookname
FROM Book b1
WHERE b1.price > (SELECT AVG(b2.price)
                  FROM Book b2
                  WHERE b2.publisher = b1.publisher);

 

❓ 주문한 내용에서 도서의 가격(PRICE)과 판매가격(SALEPRICE)의 차이가 가장 많이 나는 주문은?

SELECT *
FROM BOOK AS B
    INNER JOIN ORDERS AS O
        ON B.BOOKID = O.BOOKID
WHERE SALEPRICE - PRICE = (SELECT MAX(SALEPRICE - PRICE)
                           FROM BOOK AS B
                               INNER JOIN ORDERS AS O
                                   ON B.BOOKID = O.BOOKID);

 

❓ 하나의 주문에 2권 이상 구매한 도서는?

SELECT DISTINCT B.BOOKNAME
     , B.PRICE
FROM BOOK AS B
    INNER JOIN ORDERS AS O
        ON B.BOOKID = O.BOOKID
WHERE O.SALEPRICE != B.PRICE;

 

1.7 Group By

ORDERS 테이블

CUSTOMER 테이블

 

❓ 1. 고객 별로 주문한 도서의 총 수량과 총 판매액을 구하시오.

SELECT CUSTID
     , COUNT(*) AS '도서 총 수량'
     , SUM(SALEPRICE) AS '총 판매액'
FROM ORDERS
GROUP BY CUSTID;

 

❓ 2. 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가와 고객아이디를 출력하시오.

SELECT CUSTID
     , SUM(SALEPRICE) AS '총 판매액'
     , ROUND(AVG(SALEPRICE), 0) AS '평균'
     , MIN(SALEPRICE) AS '최저가'
     , MAX(SALEPRICE) AS '최고가'
FROM ORDERS
GROUP BY CUSTID;

 

❓ 3. 8000원 이상의 도서를 2권 이상 주문한 고객, 주문 수량, 총 판매액을 구해라.

SELECT C.USERNAME AS '이름'
	 , COUNT(*) AS '수량'
     , SUM(O.SALEPRICE) AS '총 판매액'
FROM ORDERS AS O
	INNER JOIN CUSTOMER AS C
		ON O.CUSTID = C.CUSTID
WHERE O.SALEPRICE >= 8000
GROUP BY C.CUSTID
HAVING COUNT(*) >= 2;

 

1.8 JOIN

✅ INNER JOIN

조인하고자 하는 테이블들의 공통된 데이터만 출력

-- 고객 이름, 주문한 도서의 총 구매액을 주문일자로 정렬하여 출력
-- CUSTOMER, ORDERS 테이블 조인
SELECT USERNAME
     , SUM(SALEPRICE)
     , O.ORDERDATE
FROM CUSTOMER C
    INNER JOIN ORDERS O
        ON C.CUSTID = O.CUSTID
GROUP BY USERNAME
ORDER BY ORDERDATE;

 

-- 고객 이름, 주문 도서 이름 출력

-- CUSTOMER, ORDERS, BOOK 테이블 조인 
-- (CUSTOMER, BOOK 테이블은 서로 연관된 컬럼이 존재하지 않기 때문에 ORDERS 테이블을 함께 조인)
SELECT C.USERNAME AS '이름'
     , B.BOOKNAME AS '도서명'
FROM CUSTOMER C
    INNER JOIN ORDERS O
        ON C.CUSTID = O.CUSTID
    INNER JOIN BOOK B
        ON B.BOOKID = O.BOOKID;

 

✅ LEFT JOIN

-- 고객 별 도서 구매액 출력 (도서를 구매하지 않은 고객도 포함)
SELECT USERNAME
     , O.SALEPRICE
FROM CUSTOMER C
    LEFT JOIN ORDERS O
        ON C.CUSTID = O.CUSTID;

 

✅ CROSS JOIN

상호 존재하는 행을 모두 반환하며, 행이 더 많은 테이블의 행 수만큼 반환한다.

-- 도서를 구매한 이력이 있는 고객의 이름, 판매 도서의 가격을 출력
SELECT C.USERNAME
	 , O.SALEPRICE
FROM CUSTOMER C
	CROSS JOIN ORDERS O
		ON C.CUSTID = O.CUSTID;

 

1.9 집합 연산자

✅ UNION vs UNION ALL

- UNION : 쿼리의 결과를 합치는데, 중복된 ROW는 제거한다.

- UNION ALL : 쿼리의 결과를 합치는데, 중복 제거를 하지 않는다.

SELECT	username, phone
FROM	Customer
WHERE 	address LIKE '%서울%'

UNION

SELECT	username, phone
FROM	Customer
WHERE	custid IN (SELECT custid FROM Orders);

 

✅ IN 연산자

-- 대한민국에 거주를 하며 도서를 주문한 고객만 출력 (IN)
SELECT *
FROM CUSTOMER
WHERE ADDRESS LIKE '대한민국%'
  AND USERNAME IN (SELECT USERNAME 
                   FROM CUSTOMER 
                   WHERE CUSTID IN (SELECT CUSTID
                                    FROM ORDERS));

 

-- 대한민국에 거주를 하며 도서를 주문하지 않은 고객만 출력 (NOT IN)
SELECT *
FROM CUSTOMER
WHERE ADDRESS LIKE '대한민국%'
  AND USERNAME NOT IN (SELECT USERNAME 
                       FROM CUSTOMER 
                       WHERE CUSTID IN (SELECT CUSTID
                                        FROM ORDERS));

 

✅ EXISTS 연산자

-- 주문이 있는 고객의 이름, 주소 정보를 출력하자
SELECT *
FROM CUSTOMER C
WHERE EXISTS (SELECT *
			  FROM ORDERS O
              WHERE C.CUSTID = O.CUSTID);

 

💡 EXISTS vs IN

- 2개의 연산자 모두 WHERE 절에서 사용되며, 조건에 따라 데이터를 걸러내어 결과를 조회할 때 사용한다.

- IN 연산자는 서브쿼리를 처리할 때, 메인 쿼리의 정보가 입력되지 않지만

  EXISTS 연산자는 메인쿼리부터 처리하기 때문에 서브쿼리를 처리할 때 모든 정보를 가지고 있다.

  그래서 EXISTS 연산자를 사용한 쿼리를 보면, 메인쿼리에서는 속성만 결정해주고 서브쿼리에서 모든 조건을 수행한다.

 

- EXISTS : 조건을 만족하는 데이터가 있으면 True. T/F 여부만 확인하여 T인 데이터만 추출

- IN : 조건을 만족하는 데이터의 컬럼을 비교하여 체크

 

[MySQL] in 연산자와 exists 연산자 의 차이

[MySQL] EXISTS와 IN 사용법 비교하기 (예제)

 

✅ ANY, ALL

- ANY : 조건식에 서브쿼리 결과값 중 하나라도 만족하면 TRUE

- ALL : 조건식에 서브쿼리 결과값을 모두 만족해야 TRUE

[DB] 서브쿼리 (단일행, 다중행, 다중열, WITH절)

 

 

1.7 VIEW

✅ 뷰 생성 (CREATE VIEW)

CREATE VIEW V_ORDERS
AS SELECT O.ORDERID, O.CUSTID, C.USERNAME, O.SALEPRICE, O.ORDERDATE
   FROM ORDERS AS O
	   INNER JOIN CUSTOMER AS C
		   ON C.CUSTID = O.CUSTID
	   INNER JOIN BOOK AS B
		   ON O.BOOKID = B.BOOKID;

 

✅ 뷰 접근 (SELECT)

# 일반 테이블과 동일하게 SELECT 활용
SELECT * FROM V_ORDERS;

 

✅ 뷰 대체 (CREATE OR REPLACE VIEW)

- 기존에 생성한 뷰를 다시 새로운 뷰로 대체한다.
- 뷰는 한 번 생성하면 변경이 불가하기 때문에 새로운 뷰로 대체해야 한다.

CREATE OR REPLACE VIEW V_CUST_PURCHASE
AS SELECT C.USERNAME AS 고객, SUM(O.SALEPRICE) 구매액
   FROM CUSTOMER C, ORDERS O
   WHERE C.CUSTID = O.CUSTID
   GROUP BY 고객
   ORDER BY 구매액 DESC;

 

✅ 뷰 삭제 (DROP VIEW)

DROP VIEW V_CUST_PURCHASE;

 

💡 VIEW에 대한 상세한 내용은 예전에 혼공SQL에서 정리한 것 다시 참고하기

5-3. 가상의 테이블: 뷰

 

 

2. 멘토링

어제 공부하면서 되지 않은 '와일드카드 문자열'에 대해서 물어봤다...!

 

[나의 질문]
SQL 와일드카드 문자열을 사용하고 있는데, LIKE '[0-9]%' 이 적용되지 않습니다!
혹시나 해서 다른 쿼리를 가져와서 동일하게 해보았는데도 결과가 안 나옵니다ㅠㅠ 와일드카드 문자열이 실행되지 않는 이유가 궁금합니다...

-- 0~9로 시작하는 문자열 데이터 출력

SELECT *
FROM customer
WHERE phone LIKE '[0-9]%';

 

with test(test_text) as
(
    select 'Korea'
    union
    select '12345'
    union
    select '.Net 2010'
    union
    select '10 years ago'
)
select *
  from test
 where test_text like '[0-9]%'

 

▶ [답변]

와일드카드 문자열 사용이 불가함. 그래서 정규표현식을 활용해야함. (왜... 되지 않는 것을 알려주신 것인가..!)

SELECT *
FROM customer
WHERE phone regexp('^[0-9]+');

 

 

[MY SQL] 정규식 (REGEXP)

 

 

[+] 데이터 구조를 파악하기 위해서는 ERD를 먼저 만든다.

Database > Reverse Engineer

ERD Diagram

 

 

3. 회고

SQL의 문법 세세한 부분들을 많이 배웠다. 처음 본 문법들도 많아서 정리하는 데에도 제법 오랜 시간이 걸렸다.

확실히 오류가 발생하는 것을 뜯어보고 다시 작성해보면서 많이 배웠다.

또 멘토링 시간에 강사님께서 다시 한번 더 복습 차원에서 정리를 해주셔서 더 쉽게 이해가 되었다. 

벌써 내일 SQL 강의 마지막인데, 잘 마무리해보자!