[신세계I&C KDT][MySQL] #2 함수 function (0329)

1. 함수 (Function)

1) 역할

: 데이터 가공 처리

2) 종류

(1) 단일행 함수 (single row function)

• 테이블의 행 (레코드) 단위로 적용되는 함수
• 데이터별 종류
  a. 문자 데이터 : 길이, 부분열, 대문자, 소문자, 공백 제거,...
  b. 수치 데이터 : 반올림, 절삭, 부호 식별, 절대값,...
  c. 날짜 : 현재 날짜, 시간, 연산, 특정날짜 추출(년도, 월, 시간) - Oracle과 완전 다름
  d. 변환 함수 : 숫자 <-> 문자, 날짜 <-> 문자
  e. etc : ifnull, case, if

(2) 그룹 함수 (group function)

: 테이블의 그룹 단위로 적용되는 함수 (예> 부서별, 성별)

• 총합, 평균, 최대, 최소, 갯수
• sum(), avg(), max(), min(), count()

2. 단일행 함수 (single row function)

1) 문자 데이터

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/string-functions.html

• upper()
• lower()
• length()
• char_length()
• substr(), substring()
• instr()
• left(str, len), right(str, len)
  SELECT LEFT('ABCDEFG', 4); // ABCD
  SELECT RIGHT('ABCEDFG', 4); // EDFG
  왼쪽/오른쪽부터 4개를 반환한다
• lpad(), rpad()
• ltrim(), rtrim(), trim()
• concat(n,n2,..)
• concat_ws(구분자,n,n2,.)
• format()
• insert()
  : SELECT INSERT('ABCDEFG', 1, 4, '$'); // $EFG
  첫 번째 글자부터 글자 4개 자리에 $를 넣는다
• replace()
• space(n)
• repeat(str, count)
  : SELECT REPEAT('ABCD', 2); // ABCDABCD
• reverse()

2) 수치 데이터

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/numeric-functions.html

• round(숫자, [n])
  : SELECT ROUND(45.678), ROUND(45.678, 2) , ROUND(45.678, -1); // 46 45.68 50
  숫자를 소수점 n자리까지 반올림해서 출력한다. 생략하면 기본값은 0
  n이 양수이면 소수 자리를, 음수이면 정수 자리를 반올림한다
• truncate(숫자, n)
  : SELECT TRUNCATE(45.678, 0), TRUNCATE(45.678, 2), TRUNCATE(45.678, -1) FROM DUAL; // 45 45.67 40
  숫자를 소수 n 자리까지 절삭해서 출력한다
  (Oracle에서는 TRUNC(숫자, [n]) n 생략 가능)
• mod(m, n)
  SELECT MOD(10, 3), 10%3 , 10 MOD 3 FROM DUAL; // 1 1 1
  m을 n으로 나눈 나머지 리턴
  Oracle 에서는 mod(m, n)만 지원됨
• ceil(숫자)
  : SELECT CEILING(45.178), CEIL(-45.178); // 46 -45
  올림값
  주어진 숫자보다 크거나 같은 최소 정수 리턴
• floor(숫자)
  : SELECT FLOOR(45.178), FLOOR(-45.178); // 45 -46
  내림값
  주어진 숫자보다 작거나 같은 최대 정수 리턴
• sign(숫자)
  SELECT SIGN(-1.200), SIGN(34.3), SIGN(0) FROM DUAL; // -1 1 0
  숫자의 부호를 식별하는 함수
• abs()

3) 날짜 데이터

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/date-and-time-functions.html

• curdate() : 'yyyy-MM-DD'
• curtime() : 'hh:mm:ss'
• now(), sysdate()
• adddate()
• date_date()
  date_sub()
• datediff()
• TIMESTAMPDIFF()
• last_day()
• extract()
• date_format() : 날짜를 문자열로 변환
• str_to_date(): 문자를 날짜로 변환

4) 조건 처리

• case ~ end ( ANSI )
• if()

5) 형변환 함수

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/cast-functions.html

• cast()
• convert()

3. 그룹 함수 (group function)

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/aggregate-functions.html

• 단일 행 함수와는 달리 그룹 함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되
  어 하나의 결과를 가져오는 함수를 말한다
• 그룹 당 하나의 결과가 주어지도록 행의 집합에 대해 연산할 경우 GROUP BY절을 이
  용하여 그룹화 할 수 있고 HAVING 절을 이용하여 그룹에 대한 조건을 제한 할 수 있다.
• 기본적으로 그룹 함수는 null 제외하고 처리됨
• GROUP BY로 묶지 않은 컬럼은 SELECT 절에 그룹 함수와 같이 사용 불가 ★
  예>

SELECT DEPTNO, SUM(SAL)
FROM EMP; // 에러. 여기서 deptno는 14개 sum(sal)은 1개 출력되기 때문

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO; // 해결

SELECT DEPTNO, HIREDATE, SUM(SAL)
FROM EMP
GROUP BY DEPTNO, HIREDATE; // 가능 (다중 그룹)

• where 절에는 그룹 함수 사용 불가 ★
• having, order by 절에는 사용 가능

1) 종류

sum([distinct] 컬럼)
avg([distinct] 컬럼)
max([distinct] 컬럼)
min([distinct] 컬럼)
count([distinct] 컬럼|) : 레코드 갯수
: count() 는 null을 포함하여 전체 레코드 갯수를 반환함

2) 명시적으로 그룹으로 묶는 방법 - GROUP BY

문법:
group by 표현식, 표현식 // 다중 그룹

mysql : 표현식에 컬럼명, 별칭, 순서 모두 가능
oracle : 컬럼명만 사용 가능

SELECT SUM(SAL) (그룹 함수) // 4번
FROM // 1번
WHERE // 2번
GROUP BY DEPTNO // 3번 부서별
ORDER BY ; // 5번

3) 그룹에 대한 필터링 - HAVING

SELECT SUM(SAL), 그룹 함수 // 5번
FROM // 1번
WHERE // 2번 (where절은 from에 대한 조건)
GROUP BY DEPTNO // 3번 부서별
HAVING 조건식 // 4번 (having은 group by에 대한 조건)
ORDER BY ; // 6번