멋쟁이 사자처럼 17회차 ( 03 / 21 )

 

[학습 목표]

1. 집계 함수의 종류를 활용하고 그룹핑을 구현할 수 있다. 2. SELECT 전체 구문을 통해 실행 순서를 학습할 수 있다. 3. 다양한 함수를 통해서 각 데이터를 변환할 수 있다. 4. 테이블의 연관 관계를 이해하고 관계를 식별 할 수 있다.

 

---

 

다이어그램 

database -> reverse Engineer 를 통해 다이어그램을 만들 수 있다.

 

위 그림을 보고 관계를 살펴보자.

emp에 새발과 같은 모양으로 연결되어있는 것을 볼 수 있다. 하나의 부서에 여러 사원이 속해있음을 알 수 있다.

이는 crow's foot 으로 1:n 관계도를 나타낸다. 즉 이를 통해 알 수 있는 것은 사원은 부서 배치를 받을 수도 있고 아닐 수도 있다.

 

---

 

내장 함수 및 명령어

UNSIGNED : 부호가 없는 숫자를 저장할 때 사용한다 -> INT 일 경우 32 비트 중 1비트를 부호 비트로 사용하지만 UNSIGNED를 사용할 경우 32비트를 전부 양의 정수를 나타내는데 사용한다 (2 ^32 최댓값이 됨) -> 더 많은 데이터를 표현하기에 적합하다

 

DEFAULT : INSERT 할 때 값이 주어지지 않으면 디폴트 값으로 지정한 값을 넣겠다는 의미이다.

 

UUID_TO_BIN : 고유 ID값을 바이너리 타입으로 반환하겠다는 의미이다. (VER 8.0부터) -> 인덱스 성능 향상을 위한 목적으로 사용된다.

UUID() : MYSQL에서 고유 ID 값을 생성해줌 , 보통 32개 자리를 사용한다. -> BINARY(16)자리로 변경해서 사용한다.

 

ex) 

DROP TABLE T1;
-- 테이블 생성해서 기본값을 대입해 보자.
-- DEFAULT 는 INSERT 하지 않는 속성이 있다면 기본값으로 입력된다.
CREATE TABLE t1 (
  -- literal defaults  -> 크기를 지정 , 수치를 크기 NUMBER( 전체 크기 , 소수 자리)
  i INT         DEFAULT 0, #디폴드 값이 0임
  c VARCHAR(10) DEFAULT 'ABCD', # INSERT할 때 값이 없으면 디폴트 값으로 'ABCD'를 넣겠다는 의미 
  -- expression defaults 밑에는 객체 타입으로 저장함
  f FLOAT       DEFAULT (RAND() * RAND()),
  b BINARY(16)  DEFAULT (UUID_TO_BIN(UUID())), # UUID_TO_BIN : 고유 ID값을 리턴하는 함수이다.
  d DATE        DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
  p POINT       DEFAULT (Point(0,0)),
  j JSON        DEFAULT (JSON_ARRAY())
);

 

 

JSON_ARRAY() : 배열 생성

JSON_OBJECT() : 객체 생성 ( KEY - VALUE)

JSON_ARRAYAGG() : GROUP BY 한 후 집계 결과를 JSON 배열로 리턴 (집계함수 , GROUP BY 뒤에 명시됨)

 

ex)

SELECT JSON_ARRAY(“사과”, “딸기”, “바나나”);

 

 

FORMAT(X,D[,LOCALE]) : 천단위 표시 (3자리마다 콤마를 설정하에 사용할 수 있다.)

ROUND : 반올림 , 2개의 인자값을 받음 ( 숫자 , 반올림할 자릿수)

 

ex) 

SELECT EMPNO , ENAME , FORMAT(SAL,2) , SAL(SAL,2) FROM EMP WHERE SAL >= 3000;

 

 

DATE_FORMAT 

 

여기서 몇 가지만 살펴보자!

%a 요일의 약어

%j  일년중 몇 번째 날

%H 24시간 2자리 표시

%k 24시간 1자리 표시

%I 12시간제

%r 12시간 표시 시, 분 , 초

%T 24시간 시 , 분, 초

%S 초

%w  요일 숫자 0 = 일요일

 

-> 현재 날짜 포맷해보기

SELECT DATE_FORMAT(NOW() , '오늘은 %Y년도 %m월 %d일 %w 입니다');

 

 

IFNULL

ex)

-- Q8 사원의 이름과 매니저를 출력하자, 단 매니저가 없으면 '없음'으로 출력 해줘 
SELECT ENAME ,IFNULL( MGR,'없음') 
FROM EMP;

 

 

CASE WHEN THEN

ex)

-- Q9 사원의 이름과 매니저를 출력하자, 단 매니저가 없으면 '없음'으로 출력 해줘 [번외편 : CASE WHEN THEN ]
SELECT ENAME ,
		CASE WHEN MGR IS NULL THEN  '없음'
			ELSE CAST(MGR AS CHAR) -- 숫자를 문자로 변환
		END AS 매니저 
FROM EMP;

 

 

집계함수 ( COUNT , MAX , MIN , SUM , AVG )

https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html

MySQL :: MySQL 8.4 Reference Manual :: 14.19.1 Aggregate Function Descriptions

ex)

SELECT DEPTNO , MAX(SAL)
FROM EMP
GROUP BY DEPTNO;

 

---

 

GROUP BY

SELECT  컬럼리스트   -----------------------------------------------5
FROM    테이블 리스트          ------------------------------------ 1
WHERE   조건문 [숫자비교, 문자비교, 대소문자비교, NULL, 날짜비교,,]-------  2
GROUP BY   그룹화( 집계연산 SUM, MAX, MIN, AVG, MEAN, STD,,,,)------------  3
HAVING    GROUP BY 조건문    -----------------------------------  4
ORDER BY  정렬  ----------------------------------------------- 6 
LIMIT[옵션]  결과 제한  ---------------------------------------------7

 

- GROUP BY 절에서는 테이블의 칼럼이나 변수만 사용할 수 있으며, { 그룹 함수는 사용할 수 없다}

- SELECT 리스트에는 GROUP BY에 명시된 표현식과 그룹 함수만 사용할 수 있으며, *는 사용할 수 없다 (MYSQL 는 예외적용)

- ORDER BY 절에서는 SELECT 리스트에 명시된 칼럼, 표현식, 별칭, 또는 칼럼 번호를 사용할 수 있다.

- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

 

ex) 이렇게 하면 안된다 !!!!!

SELECT DEPTNO AS "번 호" , AVG(SAL)
FROM EMP
GROUP BY "번 호"; -- 10 , 20 , 30   X -> 문자열 그룹핑

 

 

ex) 

SELECT DEPTNO AS "번 호" , AVG(SAL) 
FROM EMP
GROUP BY 1; -- SELECT 의 속성명 위치

 

---

 

HAVING

그룹함수로 집계된 데이터의 조건을 줄 때 사용 하는 구문

1) GROUP BY 절로 그룹화된 데이터에 조건을 줄때 사용

2) WHERE 절은 각 행에 대한 조건을 적용하는 반면, HAVING 절은 그룹화된 데이터에 대한 조건을 적용한다.

3) HAVING절에는 그룹핑된 컬럼이나 그룹함수에 대한 조건만 적용된다.

4) WHERE 와 HAVING은 함께 사용할 수 있으며, WHERE절로 행단위 조건은 먼저 수행하고, 그룹화된

결과에 추가 조건을 HAVING을 통해 적용할 수 있다.

5) HAVING 다음에 SELECT-LIST에서 그룹핑에 사용한 컬럼과 , 그룹함수에 사용한 컬럼에 대해서만 조건을

줄 수 있다.

 

ex)

-- Q2. 직업별 월급의 합을 구하고 , 월급의 합이 5000이상인 사원만 출력해보자. 
SELECT JOB , SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) >= 5000;

 

---

 

WITH ROLLUP

그룹의 총계, 부분 소계를 나타낸다. ROLLUP 연산자는 GROUP BY 문과 함께 사용되며 GROUP BY 문에서 명시된 컬럼 순서대로 추가적인 요약 정보를 단계적으로 만들어 준다.

 

ex)

-- Q3. 부서별 월급의 총합, 전체 총합 및 세부 내역을 출력해보자 . WITH ROLLUP
SELECT DEPTNO , ENAME , SUM(SAL)
FROM EMP 
GROUP BY DEPTNO , ENAME WITH ROLLUP;

 

 

>>  MYSQL에는 포함이 안되지만 오라클 , POSTGRESQL, MS-SQL 지원

GROUPING SET : 선택적인 그룹화 구현하는 키워드

- 여러개의 조합을 그룹으로 사용할 때 사용하는 키워드

- GROUP BY +

ex) 부서별 그룹화, 직업별 그룹화를 동시에 결합

SELECT DEPTNO, JOB , AVG(SAL)
FROM EMP 
GROUP BY GROUPING SETS((DEPTNO ),(JOB),(DEPTNO,JOB));