[TIL 22.01.27] MySQL 내장함수(날짜처리, 변환) , 그룹함수

#날짜 처리 함수(Date Functions)

날짜 → YYYY, YY - 년도, MM - 월, DD - 일, DY, DAY - 요일(월, 월요일)

시간 → HH(12시간기준) HH24(24시 기준) - MI(분) - SS(초) PM(AM, PM)

 

※ 현재 시스템의 날짜와 시간 구하는 함수

CURDATE() : 현재의 년-월-일을 반환

CURTIME() : 현재 시:분:초 를 반환

NOW() : 년-월-일 시:분:초 를 반환

SYSDATE() : 년-월-일 시:분:초 를 발환

DATE() : 날짜와 시간에서 년-월-일 을 반환

TIME() : 날짜와 시간에서 시:분:초 를 반환

- 사용방법 

SQL>SELECT CURDATE(), CURTIME(), NOW(), SYSDATE();

SQL>SELECT DATE(NOW()), TIME(NOW());

 

-  날짜데이터에서 특정 년, 월, 일, 시, 분, 초, 밀리초를 구한다.

SQL> SELECT YEAR(NOW()), MONTH(NOW()), DAYOFMONTH(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()), MICROSECOND(NOW());

 

-ADDDATE : 날짜를 기준으로 차이를 더한 날짜를 반환한다.

-SUBDATE : 날짜를 기준으로 차이를 뺀 날짜를 반환한다.

SQL>SELECT ADDDATE('2022-01-01', INTERVAL 31 DAY) -- ADDDATE(날짜, 차이) '2022-02-01'

SQL>SELECT SUBDATE('2022-01-01', INTERVAL 31 DAY) --SUBDATE(날짜, 차이) '2021-12-01'

 

-DATEDIFF(날짜1, 날짜2) : 날짜2에서 날짜1까지 몇 일 남았는지를 반환

-TIMEDIFF(날짜1 or 시간1, 날짜2 or 시간2) : 시간이 얼마나 남았는지를 반환

SQL> SELECT DATEDIFF('2022-01-05', '2022-01-15')  -- 10

SQL> SELECT TIMEDIFF('14:30:00', '06:30:00');  -- 08:00:00

 

-DAYOFWEEK(날짜) : 요일을 반환한다.(ex) 월:2 화:3)

-MONTHNAME(날짜) : 해당 월의 영어이름 반환

-DAYOFYEAR(날짜) : 1년 중 몇일이 지났는지를 반환

SQL> SELECT DAYOFWEEK(NOW()), MONTHNAME(NOW()), DAYOFYEAR(NOW()); -- 5, JANUARY, 27

 

-LAST_DAY(날짜) : 주어진 월의 마지막 날을 반환

SQL>SELECT LAST_DAY('2022-01-15');  -- 2022-01-31

 

-TIME_TO_SEC(시간) : 시간을 초 단위 반환

SQL> SELECT TIME_TO_SEC('5:53:10'); --21190

 

 

#변환 함수(Conversion Function)

데이터 형식 : BINARY, CHAR, DATE, DATETIME, SIGNED, TIME UNSIGNED

 

-CONVERT(표현할 값, 데이터형식[(길)])

형변환 함수는 위 두가지이다. CAST와 CONVERT는 같은 의미로, 표현할 값을 해당 데이터 형식으로 바꾸는 것이다. 데엍 형식 [(길이)]에서 [(길이)]는 생략 가능함.

SIGNED INTEGER는 부호가 있는 정수(-21억~21억)

UNSIGNED INTEGER는 부호가 없는 정수(0~42억)

SQL> SELECT AVG(SALARY), CONVERT(AVG(SALARY), signed INTEGER) FROM SALARIES;

 

-CAST(표현할 값 AS 데이터형식[(길이)])

SQL> SELECT CAST('2022~01~01' AS DATE), CAST('2022/01/01' AS DATE), CAST('2022.01.01'), CAST('2022,01,01' AS DATE); 

SQL> SELECT CAST(NOW() AS DATE);

 

-DATE_FORMAT(날짜시간, 포맷형식)

SQL> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');  -- 2022-01-27

SQL> SELECT DATE_FORMAT('20220405', '%Y/%m/%d);  -- 2022/04/05

SQL> SELECT DATE_FORMAT('2022-04-05', '%W %M %Y')  -- Tuesday April 2022

SQL SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i%S %p %W %a');

  -- 2022-01-27 11:54:26 AM Thursday Thur

 

#그룹 함수(Group Function)

-그룹함수란 여러 행 또는 테이블 전체의 행에 대해 함수가 적용되어 하나의 결과값을 가져오는 함수를 말한다.

-GROUP BY절을 이용하여 그룹 당 하나의 결과가 주어지도록 그룹화 할 수 있다.

-HAVING절을 사용하여 그룹 함수를 가지고 조건비교를 할 수 있다.

-COUNT(*)를 제외한 모든 그룹함수는 NULL값을 고려하지 않는다.

-MIN, MAX 그룹함수는 모든 자료형에 대해서 사용할 수 있다.

 

-COUNT : 검색된 행의 수를 반환

SQL> SELECT COUNT(depno) FROM DEPT;  : 검색된 행의 총 수를 반환

-MAX : 컬럼 중의 최대값을 반환

SQL> SELECT MAX(sal) FROM emp; : emp테이블에서 sal값이 가장 큰 결과를 반환

-MIN : 컬럼 중의 최소값을 반환

SQL> SELECT MIN(sal) FROM emp;  : emp테이블에서 sal값이 가장 적은 결과를 반환

-AVG : 평균값을 반환

SQL> SELECT ROUND(AVG(sal), 1) salary FROM emp WHEREdeptno = 30; 

: 309부서 사원의 평균 급여를 소수점 1째자리이하에서 반올림

-SUM : 컬럼의 합을 반환

SQL> SELECT SUM(sal) salary FROM emp WHERE deptno = 30;  : 30부서 사원의 급여 합계를 반환

 

※GROUP BY절

-특정한 컬럼의 데이터들을 다른 데이터들과 비교해 유일한 값에 따라 무리를 짓는다

-GROUP BY절을 사용하여 한 테이블의 행들을 원하는 그룹으로 나눈다.

-Column명을 GROUP함수와 SELECT절에 사용하고자 하는 경우 GROUP BY뒤에 Column명을 추가한다.

SQL> SELECT b.deptno, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY b,deptno;

: 부서별로 그룹지은 검색결과의 값이며 부서별로 사원수를 보여준다.

 

//예제1) 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 구하여 출력

SQL> SELECT DEPTNO, COUNT(ENAME), AVG(SAL, SUM(SAL) FROM EMP GROUP BY DEPTNO

//예제2) 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계 출력

SQL> SELECT job, count(empno) 사원수, avg(sal) 평균급여,
min(sal) 최저급여, sum(sal) 급여합계, max(sal)최고급여
FROM emp GROUP BY job;

왼) 예제 1 출력결과  /  오) 예제 2 출력결과

※GROUP BY HAVING절

-WHERE절에 GROUP Function을 사용할 수 없다.

-HAVING절은 GROUP함수를 가지고 조건비교를 할 때 사용한다.

-WHERE → GROUP BY → HAVING → ORDER BY순으로 쿼리문이 와야 된다.

 

//예제1) 사원수가 5명이 넘는 부서의 부서명과 사원수를 출력해라

SQL> SELECT b.dname COUNT(a.empno) FROM emp a, dept b 
WHERE a.deptno = b.deptno
GROUP BY dname HAVING COUNT(a.empno) >5;

//예제2) 전체 월급이 5000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하여라.
단 판매원은 제외하고 월 급여 합계로 내림차순 정렬 하여라

SQL> SELECT job, SUM(sal) "급여합계" FROM emp
WHERE job NOT IN ('SALES')
GROUP BY job HAVING SUM(sal)>5000 ORDER BY SUM(sal) DESC

왼) 예제1 출력결과  /  오) 예제2 출력결과