[DBMS] Single-Row Function

DBMS

[DBMS] Single-Row Function

shb 2022. 2. 23. 15:12

단일행 함수 ? vs 그룹 함수?

단일행 함수(Single-Row Function)는 테이블 데이터에 여러개(레코드)에 ‘각각 적용’되어 산출
그룹함수(Aggregate Function) 는 여러 개(레코드) 를 동시에 입력받아 ‘한개의 결과값’ 산출

단일행 함수 종류

Lower() , upper()

-- lower(), upper()

SELECT 'Hello', lower('Hello'), upper('Hello');

-- t_student 테이블 : 1전공(deptno1) 이 201번인 학생들의 
-- ID를 이름과 함께 소문자, 대문자로 출력 / upper() 사용

SELECT name, id, lower(id) 소문자, upper(id) 대문자
FROM t_student WHERE deptno1 = 201;

concat(문자열, 문자열, ...)

-- concat(문자열, 문자열, ...)

SELECT concat(name, ' ', POSITION) 교수님명단
FROM t_professor WHERE deptno = 101

SUBSTR 함수

#-- SUBSTR 함수 
#-- 구문: SUBSTR( ‘문자열’ 또는 컬럼명,   시작위치,  추출할 글자수 )
#-- 문자열에서 특정 길이의 문자를 추출할 때 사용하는 함수
#-- 시작위치, 음수 가능.
#-- ★ 시작 인덱스가 1부터 시작한다 (인덱스는 1부터 시작) 

SELECT substr('ABCDE', 2, 3);  # 2번째부터 3개의 문자 추출
SELECT substr('ABCDE', 20, 3);  -- EMPTY 문자열 (에러 아니다. NULL도 아니다)
SELECT substr('ABCDE', -2, 2); -- 음수 인덱싱 가능

-- #4108
SELECT name, substr(jumin, 1, 6) 생년월일
FROM t_student
WHERE substr(jumin, 3, 2) = '08'
;

-- 실습: #4109 쿼리
-- t_student 테이블 : 
-- 4학년 학생들중 ‘여학생’ 들의 
-- 이름과 주민번호 출력 / substr() 사용

SELECT name, jumin FROM t_student
WHERE substr(jumin, 7, 1) IN ('2', '4') AND grade = 4;

* null 처리 함수
null 을 허용하는 필드에 대해서 연산이 이루어질 경우 매우! 주의해야 한다.
null 은 연산에서 빠지기 때문이다! (단일행 함수및 일반 연산)

* IFNULL / ISNULL / if 함수
IFNULL : NULL값을 만나면 다른 값으로 치환해서 출력하는 함수
ISNULL : NULL 인지 체크하는 함수 (null 이면 1, 아니면 0 리턴)
IF : 첫번째 인자가 true 이면 두번째 값을, false 이면 세번째 값!

SELECT * FROM t_professor;

-- null 값과의 연산 결과는 언제나 null 이다
SELECT name, pay, bonus, pay + bonus
FROM t_professor;

# IFNULL : NULL값을 만나면 다른 값으로 치환해서 출력하는 함수
# ISNULL : NULL 인지 체크하는 함수  (null 이면 1, 아니면 0 리턴)
# IF : 첫번째 인자가 true 이면  두번째 값을, false 이면 세번째 값!


SELECT bonus, ifnull(bonus, 0), isnull(bonus), if(bonus, bonus, 0) -- null값은 거짓판정
FROM t_professor; 


SELECT name, pay, bonus, pay + ifnull(bonus, 0) "총지급액"
FROM t_professor;

묵시적(자동)변환

SELECT 1 + '1';

숫자로 된 ‘문자’ 는 산술 연산시 자동적으로 숫자로 변환해준다.
실제로는 아래와 같은 형변환 함수가 호출되는 거다

SELECT 1 + CAST('1' AS SIGNED);

형변환 함수 : CAST, CONVERT

-- java  1 + "1" => "11"

# MySQL 은 필요시 string -> number, 혹은 number -> string 으로 묵시적 형변환 수행
SELECT 1 + '1';  -- 2 (숫자)
SELECT concat(1, '1');  -- '11' (문자)

-- 실제로는 아래와 같이 동작
SELECT 1 + CAST('1' AS SIGNED);

-- 묵시적 형변환은 편한것 같지만, 
-- 튜닝에서 뜻하지 않은 성능저하를 가져올수 있다.

-- 명시적 형변환 
-- CAST(expr AS type) 
-- CONVERT(expr, type)

SELECT 38.8, CAST(38.8 AS CHAR);

# BINARY[(N)]
# CHAR[(N)] [charset_info]
# DATE
# DATETIME
# DECIMAL[(M[,D])]
# JSON
# NCHAR[(N)]
# SIGNED [INTEGER]
# TIME
# UNSIGNED [INTEGER]

# 시간 -> SIGNED 타입
SELECT CAST(now() AS SIGNED);
SELECT CONVERT(now(), SIGNED);

# 숫자 -> 날짜타입
SELECT CAST(20210801 AS DATE);

# 숫자 -> 문자열 타입
SELECT CAST(20200221030330 AS CHAR);

DATE_FORMAT() 함수 (날짜 → 문자)

# 참고 https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

# DATE_FORMAT() 함수

SELECT date_format(now(), '%Y%m%d');
SELECT date_format(now(), '%Y-%m-%d %H:%i:%s');
SELECT date_format(now(), '%Y년 %m월 %d일 %H시 %i분 %s초');

SELECT YEAR(now()), MONTH(now()), DAY(now()), weekday(now())
	, HOUR(now()), MINUTE(now()), SECOND(now());

-- weekday() : 월요일 0, 화요일 1 ....

# 4302 (실습)
# t_student 테이블의 생일(birthday) 이 3월인 학생의 이름(name) 과 생일(birthday) 를 
# 다음과 같은 형식으로 출력하세요 (DATE_FORMAT 사용)

SELECT name, date_format(birthday, '%Y-%m-%d') 생일
FROM t_student
WHERE month(birthday) = 3
;

숫자 콤마 찍기

# 숫자 세자리마다 콤마 찍기
SELECT 1234567, format(1234567, 0)
;

# 4304
-- t_professor 테이블에서 2000년 이전에 입사한 교수명과 입사일,
-- 현재 연봉과 10% 인상 후 연봉을 출력하세요.
-- 연봉은 보너스(bonus)를 제외한 pay * 12 로 계산하고
-- 연봉과 인상후 연봉은 천단위 구분 기호를 추가하여 출력하세요
SELECT
	name, 
	date_format(hiredate, '%Y-%m-%d') 입사일,
	format(pay * 12, 0) 연봉,
	format((pay * 12) * 1.1, 0) 인상후
FROM
	t_professor
WHERE
	YEAR(hiredate) < 2000;

숫자 단일행 함수
숫자함수

ROUND() 반올림

-- ROUND()
SELECT 
	'ROUND'
	, ROUND(12.34) "(12.34)"  -- 12  소숫점 이하 반올림
	, ROUND(12.536) "(12.536)"
	, ROUND(12.536, 2) "(12.536, 2)"  -- 소숫점 3자리에서 반올림
	, ROUND(16.345, -1) "(16.345, -1)"
	;

TRUNCATE()

-- TRUNCATE()
SELECT
	'TRUNCATE'
	, TRUNCATE(12.345, 0) "(12.345, 0)"
	, TRUNCATE(12.345, 2) "(12.345, 2)"  -- 소숫점 3자리부터 자름.
	, TRUNCATE(12.345, -1) "(12.345, -1)" -- 1의 자리부터 자름.
	;

MOD() 나머지

-- MySQL 은 % 연산자 없슴
-- MOD() : 나머지 연산 함수

SELECT
	MOD(12, 10), MOD(12.6, 4.1)
	;

power()

-- power() : 제곱
SELECT 
	power(3, 2)
	, power(-3, 3)
	, power(10, -2)
	, power(2, 1/2)
	;

날짜 단일행 함수

날짜함수

SELECT now();

SELECT
	now() "오늘"
	, now() + 1  -- date 타입이 정수 변환한뒤 +1 산술연산 수행
	, now() + 60
	;

-- 날짜 더하고 빼기
-- DATE_ADD(date, INTERVAL 계산수 계산형식)
-- DATE_SUB(date, INTERVAL 계산수 계산형식)
-- 계산형식이란 월,일,시간 중 어떤걸 더할꺼냐를 선택하는 것이다.  

SELECT
	now()
	, date_add(now(), INTERVAL 1 MONTH) "한달뒤"
	, date_add(now(), INTERVAL 1 DAY) "내일"
	, date_add(now(), INTERVAL 2 DAY) "모레"
	, date_add(now(), INTERVAL 1 HOUR) "한시간뒤"
	;

-- 날짜 차이 계산
-- DATEDIFF(날짜1, 날짜2); : 일 차이
-- TIMESTAMPDIFF(단위, 날짜1, 날짜2); : 연, 분기, 월, 주, 일, 시, 분, 초  .. 차이

-- 단위
-- SECOND : 초
-- MINUTE : 분
-- HOUR : 시
-- DAY : 일
-- WEEK : 주
-- MONTH : 월
-- QUARTER : 분기
-- YEAR : 연

SELECT now() "오늘", datediff(now(), '2022-01-11') "수업시작한지"
;

SELECT TIMESTAMPDIFF (SECOND, '2022-01-11', now());
SELECT TIMESTAMPDIFF (MINUTE, '2022-01-11', now());
SELECT TIMESTAMPDIFF (HOUR, '2022-01-11', now());
SELECT TIMESTAMPDIFF (DAY, '2022-01-11', now());
SELECT TIMESTAMPDIFF (WEEK, '2022-01-11', now());
SELECT TIMESTAMPDIFF (MONTH, '2022-01-11', now());
SELECT TIMESTAMPDIFF (quarter , '2022-01-11', now());
SELECT TIMESTAMPDIFF (year , '2020-01-11', now());

-- #4501
SELECT
	name "이름", 
	date_format(now(), '%Y-%m-%d') "오늘", 
	date_format(hiredate, '%Y-%m-%d') "입사일",
	TIMESTAMPDIFF(YEAR, hiredate, now()) "근속연수",
	TIMESTAMPDIFF(MONTH , hiredate, now()) "근속개월",
	TIMESTAMPDIFF(day, hiredate, now()) "근속일"
FROM
	t_professor;