mariaDB) 내장 함수 정리

▤ 목차

✔ 1. 문자형 함수

⌨ substr (문자 자르기)

SELECT SUbstr('hello World',3) FROM DUAL;

 

SELECT SUbstr('hello World',3,6) FROM DUAL;

 

SELECT SUBstr('hello World',-3,2) FROM DUAL;

 

SELECT SUBstr(jikwon_name,2) FROM jikwon WHERE jikwon_no <=3;

 

⌨ 데이터 문자열 길이 length

SELECT LENGTH('hello') FROM DUAL;

 

⌨ 특정 문자 위치 찾기 instr

select instr('hello','e') FROM DUAL;

 

 

⌨특정 문자 채우기 LPAD/RPAD

SELECT Lpad('hello',10,'*') FROM DUAL;
SELECT RPAD('hello',10,'*') FROM DUAL;

 
 

⌨ 공백제거 TRIM

SELECT TRIM(' aaa bbb  ') FROM DUAL; -- 양쪽
SELECT LTRIM(' aaa bbb  ') FROM DUAL; -- 왼쪽
SELECT RTRIM(' aaa bbb  ') FROM DUAL; -- 오른쪽

⌨ 문자 특정 문자 변경 LEPLACE

SELECT REPLACE('011.111.1111','.','-') FROM DUAL;

---

✔  2. 숫자함수

⌨ 반올림

SELECT ROUND(123.4567, 2) FROM DUAL;
SELECT ROUND(123.4567) FROM DUAL;
SELECT ROUND(123.4567,0) FROM DUAL;
SELECT ROUND(123.4567,-1) FROM DUAL;
SELECT ROUND(123.4567,-2) FROM DUAL;

⌨ 올림

SELECT CEILING(4.3) FROM DUAL;
SELECT CEILING(4.6) FROM DUAL;

⌨ 버림

SELECT FLOOR(4.3),FLOOR(4.6) FROM DUAL;

⌨ TRUNCATE (버림)

SELECT TRUNCATE(45.678,0) FROM DUAL;
SELECT TRUNCATE(45.678,1) FROM DUAL;
SELECT TRUNCATE(45.678,-1) FROM DUAL;

⌨ 나머지 구하기 MOD

SELECT MOD(15,2) FROM DUAL;
SELECT 15%2 FROM DUAL;
SELECT 15 MOD 2 FROM DUAL;

✔ 내장함수

⌨ 가장 큰 값, 가장 작은 값

#가장 큰 값 찾기
SELECT GREATEST(3,7,12) FROM DUAL;
# 가장 작은 값 찾기
SELECT LEAST(3,7,12) FROM DUAL;

⌨ 제곱, 제곱근 

- 제곱
SELECT POW(3,2) FROM DUAL;

- 제곱근
SELECT SQRT(9) FROM DUAL;

 
 

⌨ 날짜 나타내기 CURDATE

SELECT CURDATE(), CURDATE()+0 FROM DUAL;

⌨ 날짜와 시간 모두 출력하기

SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP() FROM DUAL;

⌨ 날짜 더하기 , 빼기

SELECT ADDDATE('2020-08-01', 5); -- 5일 더하기 윤년체크해준다.
SELECT ADDDATE('2020-08-01', -30); -- 30일 빼기
SELECT SUBDATE('2020-08-01', 3); -- 3일 빼기

 

⌨ DATE_ADD / DATE_SUB (, INTERVAL )

SELECT DATE_ADD(NOW(),INTERVAL 5 MINUTE); -- 5분 더하기
SELECT DATE_ADD(NOW(),INTERVAL 5 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 5 MONTH);

SELECT DATE_SUB(NOW(),INTERVAL 5 MINUTE); -- 5분 빼기
SELECT DATE_SUB(NOW(),INTERVAL 5 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 5 Month);
SELECT DATE_SUB(NOW(),INTERVAL 5 YEAR);

⌨ 디데이 

SELECT DATEDIFF(NOW(),'2000-5-5');

결과가 날수로 나온다.

⌨ 날짜 사이 차이 구하기

SELECT TIMESTAMPADD(unit,interval,datetime_expr)

unit위치: HOUR, second, minute, day, week , month, Quarter, year

SELECT TIMESTAMPDIFF(quarter,'2022-1-1','2024-05-01');
SELECT TIMESTAMPDIFF(year,'2022-1-1','2024-05-01');

 

⌨ 해당 날짜 숫자데이터만 구하기

SELECT LAST_DAY(SYSDATE())); -- 날짜
SELECT DAYOFYEAR(SYSDATE())); -- 1월 1일부터 일수
SELECT DAYOFWEEK(SYSDATE())); -- 이번달 주차
SELECT DAYOFMONTH(SYSDATE()); -- 이번달 날자

 
 

✔ 형변환 함수 : 자동 형변환(implicit conversion) 

⌨ substr (문자 자르기)

SELECT jikwon_pay*0.5, jikwon_pay*'0.5' FROM jikwon WHERE jikwon_no=1;

 

✔ 형변환 함수 :강제 형변환환(explicit conversion)

날짜 관련

⌨ DATE_FORMAT : 날짜> 문자형

즉, 반환값이 문자열이다.

SELECT NOW(),
DATE_FORMAT(NOW(), '%y%m%d'),
DATE_FORMAT(NOW(), '%y-%m-%d'),
DATE_FORMAT(NOW(), '%y년%m월%d일'),
DATE_FORMAT(NOW(), '%H시%i분%S초');

 

SELECT NOW(),
DATE_FORMAT(NOW(), '%d'), -- 1달중
DATE_FORMAT(NOW(), '%j'), -- 1년중 
DATE_FORMAT(NOW(), '%a'), -- 요일
DATE_FORMAT(NOW(), '%w') -- 대문자 요일
;

 

⌨ 문자 > 날짜 STR_TO_DATE

SELECT STR_TO_DATE('2024-4-23','%Y-%m-%d %H:%i:%S');
SELECT STR_TO_DATE('2024-4-23','%Y-%m-%d');
SELECT ADDDATE(STR_TO_DATE('2024-4-23','%Y-%m-%d'),10);

 

⌨ 년도 - 월 - 일 형식 만들기 CAST

SELECT CAST('2024/4/23' AS DATE),CAST('2024$4$23' AS DATE);

 

숫자 관련

⌨ 해당 형식으로 숫자 만들기

SELECT CAST(1234.567 AS INTEGER)AS a,
CAST(1234.567 AS CHAR(3))AS b;

SELECT CONVERT(1234.567 , INTEGER);

 

⌨  반올림

SELECT FORMAT(1234.567,2),FORMAT(1234.567,0);

 

✔ 기타 함수

⌨ rank() : 순위를 결정 _ 동점자 처리 O

SELECT jikwon_no, jikwon_name,jikwon_pay FROM jikwon ORDER BY jikwon_pay desc;

SELECT jikwon_no, jikwon_name,jikwon_pay,
RANK() OVER (ORDER BY jikwon_pay ) AS RANK1
FROM jikwon;

 

 

⌨ DENSE_RANK() : 순위를 결정 _ 동점자 처리 X

SELECT jikwon_no, jikwon_name,jikwon_pay,
DENSE_RANK() OVER (ORDER BY jikwon_pay ) AS RANK
FROM jikwon;

 

SELECT jikwon_no, jikwon_name,jikwon_pay,
RANK() OVER (ORDER BY jikwon_pay ) AS RANK1,
DENSE_RANK() OVER (ORDER BY jikwon_pay ) AS RANK2
FROM jikwon;

 

 
 

⌨ nvl(value1, value2) : value1이 null이면 value2를 취함

NULL값에 다른 값 넣

SELECT jikwon_no, jikwon_name, jikwon_jik, nvl(jikwon_jik,'임시직')AS jik FROM jikwon;

⌨ nvl2(value1, value2, value3) : value1이 null인지 평가  후  null이면 value3, null이 아니면 value2

SELECT jikwon_no, jikwon_name, jikwon_jik,
nvl2(jikwon_jik,'정규직','임시직')AS jik
FROM jikwon;

✔ 시스템 정보 함수

⌨ CURRENT_USER(), DATABASE()

SELECT CURRENT_USER(), DATABASE();

 

CURRENT_USER() : 현재 클라이언트를 사용하는 계정의 사용자 이름과 후스트 이름을 반환

DATABASE() : 현재 데이터베이스의 이름을 반환한다.

데이터베이스가 없으면 NULL 또는 ""를 반환한다.

⌨  FOUND_ROWS()

SELECT * FROM 테이블명;
SELECT FOUND_ROWS();

직전의 실행한 행의 수를 리턴한다. 

 

⌨ ROW_COUNT()

UPDATE 테이블명 SET 열 = 변경할값 ;
SELECT ROW_COUNT();

직전에 실행한 INSERT, UPDATE, DELETE 쿼리를 통해 수행된 row 수를 알려줌

SELECT 후 사용하면 '-1'을 리턴한다.

✔ 제어 흐름 함수

⌨ IF(조건, 참, 거짓)

SELECT IF (100>200,'참이다','거짓이다');

 

⌨ IFNULL(value1, value2)

SELECT IFNULL(NULL,'널')

value1이 NULL이 아니면 value1 리턴, NULL이면 수식2 리턴

 

⌨ NULLIF(value1, value2) : 두 개의 값이 일치하면 null을, 아니면 value1을 취함

SELECT NULLIF(length('asdf'), LENGTH('kbs'));
SELECT NULLIF(length('abc'), LENGTH('kbs'));

직급이 대리인 경우 null 출력하기

SELECT jikwon_name, jikwon_jik,NULLIF(jikwon_jik, '대리')FROM jikwon;

 

⌨ 조건 표현식 (conditional expression) : case - when - than

1번 방법

SELECT 
	case 10/5
	when 5 then '안녕'
	when 2 then '반가워'
	ELSE '기타'
	END AS 결과
FROM DUAL;

2번 방법

SELECT jikwon_name,
case
when jikwon_gen='남' then 'M'
when jikwon_gen='여' then 'F'
END AS jender
FROM jikwon;

 

---

😊정리

 
문제에 따라 잘 사용하는게 중요하다.
DB벤더사마다 내장 쿼리가 조금씩 차이가 있다.
ORACLE 내장 객체와 문법이 다를 수 있는 점을 유의하자!

 

MYSQL에는 많은 내장 기능이 있다. 아래 다양한 함수들이 설명되어 있다.(한국어 지정 가능)

https://ko.w3hmong.com/mysql/mysql_ref_functions.htm

MySQL 함수