[TIL] 9일차 TIL(20230216) - 집계함수, 문자열/날짜 자료형, 서브쿼리, JOIN

 

 

[TIL] 9일차 TIL(20230216) - 집계함수, 문자열/날짜 자료형, 서브쿼리, JOIN

 

 

1. SQL 내장함수

- POWER(숫자, 제곱): 숫자를^제곱하여 반환
- ROUNT(값, 소수점): 소수점 몇자리까지 표현할지 지정
- MOD(값1, 값2): 값1을 값2로 나눈 나머지
- CEIL : 자신보다 큰 정수 중에서 가장 작은 수를 반환
- FLOOR : 자신보다 작은 정수 중에서 가장 큰 정수를 반환
- GREATEST(숫자1,숫자2,...) 주어진 수 중 제일 큰 수 반환
- LEAST(숫자1,숫자2,...) 주어진 수 중 제일 작은 수 반환

1.1 집계함수
- 내장함수들 중 결과를 통계적으로 계산하고 집계하는 함수들이 존재
- AVG(col): 칼럼의 평균값 반환
  NULL값이 있는 경우 오류가 나기 때문에 NULL값을 수정해주는 작업이 필요

  SELECT COUNT(*), SUM(salary), AVG(IFNULL(salary,0))
  FROM Orders;

- STDEV(col): 칼럼의 표준편차 반환
- VAR_SAMP(col): 칼럼의 분산 반환
- COUNT(*), COUNT(col):  * 를 사용하면 null을 포함한 총 Row의 개수를 구하며, 칼럼을 명시할 경우, null 값을 제외
*주의: 집계함수를 사용할 때, GROUP BY와 함께 사용하지 않으면 경우에 따라 오류가 발생할 수 있음

 

2. 문자열 다루기

2.1 문자열 결합
- CONCAT(문자열1, 문자열2, ...)
- CONCAT_WS('구분자', 문자열1, 문자열2, ...): 문자열 사이에 구분자를 넣어서 연결
- GROUP_CONCAT(칼럼별로 결합할 문자열-CONCAT과 동일한 형식): -컬럼에서 NULL이 아닌 모든 값을 콤마(,)로 합쳐 하나의 문자열로 가져오는 함수

2.2 문자열 길이 함수
- LENGTH(문자열): 문자열의 byte길이
- CHAR_LENGTH(문자열): 문자의 개수

2.3 공백 제거
- TRIM: 문자열 좌우 공백 제거
- LTRIM/RTRIM: 문자열 좌/우 공백 제거
- TRIM(LEADING '문자열1' FROM '문자열2'): 문자열2의 맨 왼쪽에 문자열1이 있으면 제거
- TRIM(TRAILING '문자열1' FROM '문자열2'): 문자열2의 맨 오른쪽에 문자열1이 있으면 제거

2.4 대문자/소문자 변환
- UPPER(문자열): 대문자로 변환
- LOWER(문자열): 소문자로 변환

2.5 문자열 추출
- SUBSTRING(문자열, 시작지점, 개수): 문자열에서 시작지점부터 개수만큼 추출
  MID(문자열, 시작지점, 개수)도 동일하게 사용
- LEFT(문자열,count): 문자열에서 왼쪽을 기준으로 일정 갯수를 가져오는 함수
  RIGHT(문자열,count): 문자열에서 오른쪽을 기준으로 일정 갯수를 가져오는 함수
- substring_INDEX(문자열, 구분자, index): 문자열을 구분자로 구분하여 index 위치까지 추출(구분자를 제외하고 개수를 셈)

3. 날짜 자료형 다루기

- DAY/MONTH/YEAR/LAST_DAY(날짜자료형): 각 함수 내에 날짜 자료형 값을 넣으면 각각 일/월/년/입력한 월의 마지막 날짜를 반환

3.1 현재 날짜/시간
- CURDATE(), CURRENT_DATE: 시스템의 오늘 날짜 연-월-일 반환
- NOW(), SYSDATE(), CURRENT_TIMESTAMP: 시스템의 오늘 날짜 연-월-일 시:분:초 반환
- CURTIME(), CURRENT_TIME: 시스템의 오늘 날짜 시:분:초 반환

3.2 날짜/시간 증감 함수
- DATE_ADD(date, INTERVAL _ 시간단위): date를 기준으로 입력한 시간단위만큼 더한다.
  ADDDATE(date, INTERVAL _ 시간단위)
- DATE_SUB(date, INTERVAL _ 시간단위): date를 기준으로 입력한 시간단위만큼 뺀다.
  SUBDATE(date, INTERVAL _ 시간단위)
- ADDTIME(time, 더할시간)
  SUBTIME(TIME, 뺄시간)

3.3 날짜/시간 사이의 차이와 월/요일/주 값

함수명	기능
DATEDIFF(날짜1, 날짜2)	날짜1-날짜2의 차이를 반환
TIMEDIFF(시간1, 시간2)	시간1-시간2의 차이를 반환
DAYOFWEEK(날짜)	요일(1: 일~7: 토) 반환
MONTHNAME()	월의 영문(January ~December) 반환
DAYOFYEAR(날짜)	1년 중 몇 번째 날(1~366)인지를 반환
TIME_TO_SEC(시간)	시간을 초 단위로 반환

3.4 기타 날짜/시간 함수
- MAKEDATE(연도, 정수): 연도의 첫날부터 정수만큼 지난 날짜를 반환
- MAKETIME(시, 분, 초): 시, 분, 초를 이용하여 ‘시:분:초’의 TIME 형식을 만듦
- DATE_FORMAT(날짜, format) 날짜를 format 형식으로 반환한다.
- QUARTER(날짜): 날짜가 4분기 중에서 몇 분기인지를 반환

 

4. 자료형 변환 함수

- CAST(칼럼/값 AS 데이터형식)
- CONVERT(칼럼/값 AS 데이터형식)
- 데이터 형식: CHAR, DATE, TIME, DATETIME, SIGNED (INTEGER), UNSIGNED (INTEGER)
- CAST함수에 날짜형식의 데이터를 다양한 구분자로 구분지어 입력해도('2021@09@20') 인식하고 날짜형식으로 바꿔줌
- 문자열에 연산함수를 적용할 때, 문자열의 첫글자가 숫자라면 해당 숫자로 변환/없으면 0으로 취급하여 계산

5. 서브쿼리

- 상관서브쿼리: 서브쿼리와 본 쿼리간 칼럼끼리 독립적이지 않고 서로 관련을 맺고 있는 쿼리

- 예시. 

-- 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오.
SELECT B1.BOOKNAME, B1.PRICE FROM book B1
WHERE PRICE > (SELECT AVG(PRICE) FROM BOOK B2
WHERE B2.PUBLISHER = B1.PUBLISHER);

 

5.1 인라인뷰
- FROM절에 (SELECT ~ FROM)절의 서브쿼리를 사용하여 특정 조건을 만족하는 테이블을 뽑아온 후, 그 안에서 결과를 조회하는 것
- 예시.

SELECT min(price) FROM (select * from product where price >= 2000) AS A;

 

6. GROUP BY + HAVING

- GROUP BY 에 선택한 컬럼의 조건을 HAVING 절에 지시
- GROUP BY절 사용시 GROUP BY에 사용한 칼럼이 PK가 아니라면 집계함수와 PK이외에 다른 값을 그대로 SELECT하면 오류 발생
- PK로 GROUP BY했을 때는 이외의 칼럼도 각각 SELECT할 수 있음

/*예제. 
가격이 8000원 이상인 도서의 주문 수량을 구하는데,
2권 이상 주문한 고객이름, 수량, 판 금액을 조회하자.*/

SELECT C.CUSTID, C.USERNAME, A.주문수량, A.총판매금액
FROM CUSTOMER C,
(SELECT CUSTID, COUNT(ORDERID) 주문수량, SUM(SALEPRICE) 총판매금액 FROM ORDERS
WHERE SALEPRICE >= 8000
GROUP BY CUSTID
HAVING COUNT(ORDERID) >= 2) AS A
WHERE A.CUSTID = C.CUSTID;

 

7. JOIN

- 방법1: 테이블을 모두 가져와서 WHERE절에서 공통된 값들만 남기는 방법(INNER JOIN)

SELECT *
FROM Customer, Orders
WHERE Customer.custid = Orders.custid

- 방법2: SELECT ~ FROM ~ JOIN ~ ON을 통해 조인 방법과 기준 칼럼을 정해주는 방법

SELECT Customer.username, saleprice
FROM Customer LEFT OUTER JOIN Orders 
ON Customer.custid =Orders.custid;

- CROSS JOIN: 두 테이블 간의 모든 조합을 받아옴

8. 집합 연산자

- UNION(합집합), MINUS(차집합). INTERSECT(교집합)
- MySQL에서는 MINUS, INTERSECT 연산자를 지원하지 않음

- UNION 연산자 예시:

SELECT username
FROM Customer
WHERE address LIKE '대한민국%'

UNION

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

 

9. 다중행 연산자

- IN: 서브쿼리 반환 값에서 연산 조건에 해당하는 값이 있으면 참
- IN 연산자 예시:

/*대한민국 거주 고객 중 도서를 주문한 고객의 이름*/

SELECT username FROM Customer
WHERE address LIKE '대한민국%' AND 
name IN (SELECT username
FROM Customer
WHERE custid IN (SELECT custid FROM Orders));

- NOT IN: 서브쿼리 반환 값에서 연산 조건에 해당하는 값이 없으면 참
- NOT IN 연산자 예시:

/*대한민국 거주 고객의 이름에서 도서를 주문 고객의 이름 제외하고 출력*/

SELECT username FROM Customer
WHERE address LIKE '대한민국%' AND 
name NOT IN (SELECT username
FROM Customer
WHERE custid IN (SELECT custid FROM Orders));

- ALL: 서브쿼리에 의해 반환되는 모든 값과 메인쿼리와 조건값을 비교하여 모든 값을 만족해야만 참이다.
- ALL 연산자 예시:

/*주문 테이블에 주문이 있는 모든 고객의 이름과 주소를 조회*/

SELECT bookname, publisher, price FROM BOOK 
WHERE price > ALL (
SELECT saleprice
FROM orders
WHERE orderdate < '2021-02-03');

- EXISTS: 서브쿼리의 어떤 행이 조건에 만족하면 참이 된다.
- EXISTS 연산자 예시:

/*주문이 있는 고객의 이름, 주소 정보를 출력*/

SELECT username, address
FROM Customer cs
WHERE EXISTS (SELECT *
FROM Orders od
WHERE cs.custid =od.custid);

- ANY: 서브 쿼리에 의해 구해진 값 중 아무 값과 비교하고 싶은 경우에 사용
- ANY 연산자 예시:

/*주문 테이블에 주문 가격이 5000에서 20000 사이 도서의 이름, 가격을 조회*/

SELECT bookname, price FROM book
WHERE price = ANY (SELECT saleprice
FROM orders 
WHERE saleprice between 5000 and 20000);

---

 

소감 및 정리

예전에 간단한 쿼리문을 작성하는 방법정도는 배운적이 있었는데 평소에 잘 활용하지 않다보니 대부분 잊어버렸다는 사실을 깨달았다 ㅇㅁㅇ... 예제를 보고 스스로 작성하려고 노력해보았는데, 간단한 코드임에도 생각보다 바로 떠오르지 않아서 시간이 걸렸다. 실무에서 SQL은 꼭 필요한 스킬이라고 하니 이번 기회에 실습 코드들을 반복해서 스스로 작성해보면서 한번 확실히 익히고 넘어가야겠다고 다짐했다.