2021년 12월 14일 🔆맑음
프롤로그
오늘은 함수와 조인에 대해 배웠습니다.
함수
<숫자함수>
-
ABS(n) : n의 절대값 반환
EX) ABS(3) → 3, ABS(-3) → 3 -
CEIL(n) : n과 같거나 가장 큰 정수 반환
EX) CEIL(10.123) → 11, CEIL(10.541) → 11 -
FLOOR(n) : n보다 작거나 가장 큰 정수 반환
EX) FLOOR(10.123) → 10, FLOOR(10.541) → 10 -
ROUND(n, i) : n을 소수점 기준 ( i+1 ) 번째에서 반올림한 결과 반환
EX) ROUND(10.154) → 10, ROUND(10.154, 2) → 10.15
<문자함수>
- LOWER(char) : 소문자 변환 후 반환
EX) LOWER('NEVER SAY GOODBYE') → never say goodbye - UPPER(char) : 대문자 변환 후 반환
EX) UPPER('never say goodbye') → NEVER SAY GOODBYE - CONCAT(char1, char2) : 두 문자를 붙여 반환
EX) CONCAT('I Have', ' A Dream') → I Have A Dream - SUBSTR(char, pos, len) : char의 pos번째 문자부터 len 길이만큼 잘라낸 결과를 반환
EX) SUBSTR('ABCDEFG', 1, 4) → ABCD - REPLACE(char, search_str, replace_str) : char에서 search_str을 찾아 이를 replace_str로 대체한 결과를 반환
EX) REPLACE('나는 너를 모르는데', '나', '너') → 너는 너를 모르는데 - LENGTH(char) : 문자열의 길이 반환
EX) LENGTH('대한민국') → 4, LENGTHB('대한민국') → 12
<날짜함수>
- SYSDATE : 현재일자와 시간 반환
EX) SYSDATE → 2021-03-16 22:10:56
EX) SYSTIMESTAMP → 2021-03-16 22:10:56.998000000 +09:00
<변환함수>
- TO_CHAR(숫자 혹은 날짜, format)
EX) TO_CHAR(SYSDATE, 'YYYY-MM-DD') → 2021-12-14 - TO_NUMBER(expr, format)
EX) TO_NUMBER('123456') → 123456 - TO_DATE(char, format)
EX) TO_DATE('20140101', 'YYYY-MM-DD') → 2014/01/01 00:00:00
<NULL 관련 함수>
- NVL(expr1, expr2) : expr1 값이 null일 때 expr2값으로 반환
EX) 컬럼1값이 NULL일 때
NVL(컬럼1, 'null입니다.') → null입니다. - NVL2(expr1, expr2, expr3) : expr1값이 null이 아니면 expr2, null이면 expr3값 반환
EX) 컬럼1값이 NULL일 때
NVL2(컬럼1, 'null아니', 'null임') → null임
<집계함수>
- count(*) : 쿼리결과 건 수, 로우 수 반환
- sum(expr) : expr의 전체 합계
- avg(expr) : expr의 평균
- min(expr) : expr의 최소값
- max(expr) : expr의 최대값
- variance(expr) : expr의 분산
- stddev(expr) : expr의 표준편차
GROUP BY
- GROUP BY는 특정 컬럼을 기준으로 집계를 하는데 사용
- HAVING절은 GROUP BY로 집계된 값의 조건문으로 사용
- GROUP BY 다음에 HAVING 절이 옴
조인
- 조인 종류
- 오라클 : 등가조인(EQUI), 셀프조인, 카타시안조인, 외부조인(OUTER)
- ANSI : 내부조인(INNER), 셀프조인(SELF), 크로스조인(CROSS), 외부조인(OUTER)
--ANSI 조인 용어로 외울 것을 추천
--실무에서는 조인 종류를 따져가면서 말하지 않고 통틀어서 조인이라고 함
<내부조인:등가조인>
- 오라클 등가조인(EQUI JOIN)
SELECT *
FROM book b, orders o
WHERE b.bookid = o.bookid; - ANSI 내부조인(INNER JOIN)
SELECT *
FROM book b INNER JOIN orders o
ON b.bookid = o.bookid
<셀프조인(SELF JOIN)>
- 오라클 셀프조인
SELECT *
FROM emp A, emp B
WHERE A.mgr = B.empno; - ANSI 셀프조인
SELECT *
FROM emp A JOIN emp B
ON A.mgr = B.empno;
<크로스조인>
- 오라클 카타시안조인
SELECT *
FROM book, orders; - ANSI 크로스조인
SELECT *
FROM book CROSS JOIN orders;
<외부조인(OUTER JOIN)>
1. LEFT OUTER JOIN
- 오라클
SELECT *
FROM book b, orders o
WHERE book.bookid = orders.bookid(+); - ANSI
SELECT *
FROM book b LEFT OUTER JOIN orders o
ON b.bookid = o.bookid
- RIGHT OUTER JOIN
- (+)가 왼쪽
- FULL OUTER JOIN
- ANSI
SELECT *
FROM book b FULL OUTER JOIN orders o
ON b.bookid = o.bookid
<예제>
1) 할인을 받은 고객의 이름, 할인 받은 누적금액을 조회
SELECT c.name, SUM(b.price - o.saleprice)
FROM customer c, orders o, book b
WHERE c.custid = o.custid
AND b.bookid = o.bookid
AND b.price - o.saleprice > 0
GROUP BY c.name;
2) 주문 일자 기준/ 그날 총 판매금액(saleprice), 주문횟수 조회
SELECT orderdate, SUM(saleprice), COUNT()
FROM orders
GROUP BY orderdate;
3) 고객별 주문한 고객이름, 주문한 횟수, 총 판매금액(saleprice) 조회
SELECT c.name, COUNT(), SUM(saleprice)
FROM customer c, orders o
WHERE c.custid = o.custid
GROUP BY c.name;
4) 키트리서점 책 중 주문이 한번도 없었던 책 이름, 출판사, 가격을 조회
SELECT b.bookname, b.publisher, b.price
FROM book b, orders o
WHERE b.bookid = o.bookid(+)
AND o.orderid is null;
