[MariaDB] 마리아DB SQL 쿼리 고급 - 내장 함수 (MySQL)

[MariaDB] 마리아DB SQL 쿼리 고급 - 내장 함수 (MySQL)

 

제어 흐름 함수

IF(수식, 참, 거짓)

/* 수식이 참이면 2번째 값 출력, 수식이 거짓이면 3번째 값 출력 */
SELECT IF (100>200, '참이다', '거짓이다'); -- 거짓이 출력

IFNULL(수식1, 수식2)

/* 수식1값이 NULL이면 수식2가 반환, NULL이 아니면 수식1이 반환 */
SELECT IFNULL(NULL, '널이군요'), IFNULL(100, '널이군요'); -- 앞은 NULL이라 '널이군요'반환, 뒤는 NULL이 아니라 100이 반환

NULLIF(수식1, 수식2)

/* 수식1과 수식2가 같으면 NULL을 반환, 다르면 수식1을 반환 */
SELECT NULLIF(100,100), NULLIF(200,100);	-- 앞은 같으니 NULL반환, 뒤는 다르니 200 반환

CASE~WHEN~ELSE~END

SELECT 	CASE 10 -- CASE값이 10일때
		WHEN 1  THEN  '일'
		WHEN 5  THEN  '오'
		WHEN 10 THEN  '십'	-- WHEN 값이 10인것을 찾아 THEN 뒤에 값을 반환
		ELSE '모름' -- WHEN 값에 존재하지 않으면 ELSE 뒤에 값을 반환
	END;

 

문자열 함수

ASCII(아스키코드), CHAR(숫자)

SELECT ASCII('A'), CHAR(65);	-- 앞은 65 출력, 뒤는 'A' 출력

BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)

/*	BIT_ 할당된 Bit 크기, 또는 문자 크기를 반환
	CHAR_ 문자의 개수를 반환
    LENGTH 할당된 바이트 수를 반환 */
    
SELECT BIT_LENGTH('abc'),  CHAR_LENGTH('abc'), LENGTH('abc');	-- 24, 3, 3 (영문은 3바이트)
SELECT BIT_LENGTH('가나다'),  CHAR_LENGTH('가나다'), LENGTH('가나다');	-- 72, 3, 9  (한글은 9바이트)

CONCAT(문자열1, 문자열2,...), CONCAT_WS(문자열1, 문자열2,...)

/* 문자열들을 이어준다. CONCAT_WS는 구분자와 함께 문자열을 이어준다 */
SELECT CONCAT_WS('/', '2022', '01', '01');	-- 2020/01/01 출력

ELT(위치, 문자열1, 문자열2,...), FIELD(찾을문자열, 문자열1, 문자열2,...), FIND_IN_SET(찾을문자열, 문자열리스트), INSTR(기준문자열, 부분문자열), LOCATE(부분문자열, 기준문자열)

SELECT ELT(2, '하나', '둘', '셋');	-- '둘' 출력
SELECT FIELD('둘', '하나', '둘', '셋');	-- 2 출력
SELECT FIND_IN_SET('둘', '하나,둘,셋');	-- 2 출력
SELECT INSTR('하나둘셋', '둘');	-- 3출력
SELECT LOCATE('둘', '하나둘셋');	-- 3출력 (LOCATE와 POSITION은 동일한 함수임)

FORMAT(숫자, 소수점자리수)

SELECT FORMAT(123456.123456, 4);	-- 123,456.1235 출력

BIN(숫자), HEX(숫자), OCT(숫자)

SELECT BIN(31), HEX(31), OCT(31);	-- 2진수 11111, 16진수 1F, 8진수 37 출력

INSERT(기준문자열, 위치, 길이, 삽입할문자열)

/* 기준 문자열의 위치부터 길이만큼을 지우고, 삽입할 문자열을 끼워 넣음 */
SELECT INSERT('abcdefghi', 3, 4, '@@@@'), INSERT('abcdefghi', 3, 2, '@@@@');	-- ab@@@@ghi, ab@@@@efghi 출력

LEFT(문자열, 길이), RIGHT(문자열, 길이)

SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3);	-- abc, ghi를 출력

UPPER(문자열), LOWER(문자열)

SELECT LOWER('abcdEFGH'), UPPER('abcdEFGH');	-- abcdefgh, ABCDEFGH를 출력
/* LOWER()와 LCASE()는 동일함수. UPPER()와 UCASE()는 동일함수 */

LPAD(문자열, 길이, 채울문자열), RPAD(문자열, 길이, 채울문자열)

/* 문자열을 길이만큼 늘린 후에 빈곳을 채울 문자열로 채움 */
SELECT LPAD('김이박', 5, '##'), RPAD('김이박', 5, '##');	-- '##김이박', '김이박##' 출력

LTRIM(문자열), RTRIM(문자열)

/* 문자열의 왼쪽, 오른쪽 공백을 제거. 가운데 공백은 제거하지 않음. */
SELECT LTRIM('   김이박'), RTRIM('김이박   ');	-- '김이박', '김이박' 출력

TRIM(문자열), TRIM(방향 자를문자열 FROM 문자열)

/*	TRIM() 앞뒤 공백을 모두 제거함
	방향: LEADING 앞쪽, BOTH 양쪽, TRAILING 뒤쪽 */
SELECT TRIM('   김이박   ');	-- '김이박' 출력
SELECT TRIM(BOTH 'ㅎ' FROM 'ㅎㅎㅎ힘들다.ㅎㅎㅎ');	-- '힘들다.' 출력

REPEAT(문자열, 횟수)

SELECT REPEAT('김이박', 3);	-- '김이박김이박김이박' 출력

REPLACE(문자열, 원래문자열, 바꿀문자열)

SELECT REPLACE ('이제는 잘 수 있겠다.', '있겠다' , '없겠다');	-- '이제는 잘 수 없겠다'로 변경 출력

REVERSE(문자열)

SELECT REVERSE ('김이박');	-- '박이김' 출력

SPACE(길이)

SELECT CONCAT('이제는', SPACE(10), '졸립다');	-- '이제는          졸립다' 출력

SUBSTRING(문자열, 시작위치, 길이) or (문자열 FROM 시작위치 FOR 길이)

/* 시작위치부터 길이만큼 문자를 반환. 길이가 생략되면 준마열 끝까지 반환 */
SELECT SUBSTRING('이제그만자자', 3, 2);	-- '그만' 출력
/* SUBSTRING(), SUBSTR(), MID()는 모두 같은 함수 */

SUBSTRING_INDEX(문자열, 구분자, 횟수)

/* 문자열에서 구분자의 횟수번째 나오는 부분 뒤를 버림. 횟수가 음수면 우측부터 횟수번째 나오는 앞을 버림. */
SELECT SUBSTRING_INDEX('cafe.daum.net', '.', 2);	-- cafe.daum 출력
SELECT SUBSTRING_INDEX('cafe.daum.net', '.', -2);	-- daum.net 출력

 

수학 함수

ABS(숫자)

SELECT ABS(-100);	-- 절대값 100이 출력됨

CEILING(숫자), FLOOR(숫자), ROUND(숫자)

SELECT CEILING(4.7), FLOOR(4.7), ROUND(4.7);	-- 올림 5, 내림 4, 반올림 5 출력

CONV(숫자, 원래진수, 변환할진수)

SELECT CONV('AA', 16, 2);	-- 16진수 AA를 2진수로 변환하여 10101010 출력
SELECT CONV(100, 10, 8);	-- 10진수 100을 8진수로 변환하여 144 출력

DEGREES(숫자), RADIANS(숫자), PI()

/* 라디안 값을 각도값으로 변환, 각도값을 라디안 값으로 변환, PI값인 3.141592를 반환 */
SELECT DEGREES(PI()), RADIANS(180);	 -- 180, 3.141592 출력

MOD(숫자1, 숫자2) or 숫자1 % 숫자2 or 숫자1 MOD 숫자2

/* 숫자1을 숫자2로 나눈 나머지 값을 반환 */
SELECT MOD(157, 10), 157 % 10, 157 MOD 10;	-- 모두 157을 10으로 나눈 나머지값인 7을 반환

POW(숫자1, 숫자2), SQRT(숫자)

/* 숫자1의 숫자2 만큼 제곱값 반환, 숫자의 제곱근을 반환 */
SELECT POW(2,3), SQRT(9);	-- 8, 3 출력

RAND()

/* 0이상 1미만의 실수를 반환. 실행할때마다 값이 변경 됨*/
SELECT RAND(), FLOOR(1 + (RAND() * (7-1)) );	-- 0~1 실수 반환. 1~6 정수 반환

SIGN(숫자)

/* 숫자가 양수, 0, 음수 인지를 구함. 각 1, 0, -1 을 반환. */
SELECT SIGN(100), SIGN(0), SIGN(-100.123);	-- 1, 0, -1 출력

TRUNCATE(숫자, 정수)

/* 숫자를 소수점을 기준으로 정수 위치(양수는 우측, 음수는 좌측)까지 구하고 나머지는 버린다. */
SELECT TRUNCATE(12345.12345, 2), TRUNCATE(12345.12345, -2);	-- 12345.123, 12300 반환

 

날짜 및 시간 함수

ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)

/* 날짜를 기준으로 차이를 더한 날짜, 뺀 날짜를 구한다. */
SELECT ADDDATE('2022-01-01', INTERVAL 31 DAY), ADDDATE('2022-01-01', INTERVAL 1 MONTH);	-- 2022-02-01 출력
SELECT SUBDATE('2022-01-01', INTERVAL 31 DAY), SUBDATE('2022-01-01', INTERVAL 1 MONTH);	-- 2021-12-01 출력

ADDTIME(날짜/시간, 시간), SUBTIME(날짜/시간, 시간)

/* 날짜/시간을 기준으로 시간을 더한결과, 뺀결과를 반환 */
SELECT ADDTIME('2022-01-01 23:59:59', '1:1:1'), ADDTIME('15:00:00', '2:10:10');	-- 2020-01-02 01:01:00, 17:10:10 출력
SELECT SUBTIME('2022-01-01 23:59:59', '1:1:1'), SUBTIME('15:00:00', '2:10:10');	-- 2022-01-01 22:58-58, 12:49:50 출력

CURDATE() 현재 연-월-일, CURTIME() 현재 시:분:초, NOW()와 SYSDATE() 현재 연-월-일 시:분:초 출력

 

YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)

(현재 날짜 또는 시간에서 연, 월, 일, 시, 분, 초 , 밀리초를 출력)

 

DATE(), TIME()

SELECT DATE(NOW()), TIME(NOW());	-- 2020-08-29, 05:33:00 현재 날짜, 시간 출력

DATEDIFF(날짜, 날짜2), TIMEDIFF(날짜1or시간1, 날짜2or시간2)

/* 날짜1 - 날짜2 일수 결과 반환, 시간1-시간2 시간 결과 반환. */
SELECT DATEDIFF('2022-01-01', NOW()), TIMEDIFF('23:23:59', '12:11:10');	-- 490, 11:12:49 출력

DAYOFWEEK(날짜), MONTHNAME(), DAYOFYEAR(날짜)

/* 현재요일(순서), 월이름, 일년 중 몇일이 지났는지를 반환 */
SELECT DAYOFWEEK(CURDATE()), MONTHNAME(CURDATE()), DAYOFYEAR(CURDATE());	-- 7, August, 242 출력

LAST_DAY(날짜)

/* 날짜 달의 마지막 날짜를 반환. 보통 날짜 달의 마지막 날짜를 확인할 때 사용. */
SELECT LAST_DAY('2022-02-01');	-- 2022-02-28 출력

MAKEDATA(연도, 정수)

/* 연도에서 정수만큼 지난 날짜를 구한다 */
SELECT MAKEDATE(2022, 32);	-- 2022-02-01 출력

MAKETIME(시, 분, 초)

/* 시:분:초 형식으로 만든다 */
SELECT MAKETIME(12, 11, 10);	-- 12:11:10 출력

PERIOD_ADD(연월, 개월수), PERIOD_DIFF(연월1, 연월2)

/* 연월(YYYYMM)에서 개월수만큼 지난 연월을 구한다. 연월1 - 연월2의 개월수를 구한다. */
SELECT PERIOD_ADD(202201, 11), PERIOD_DIFF(202201, 201812);	-- 202212, 37 출력

QUARTER(날짜)

/* 날짜가 몇분기인지 출력 */
SELECT QUARTER('2022-07-07');	-- 3 출력

TIME_TO_SEC(시간)

/* 시간을 초단위로 구한다 */
SELECT TIME_TO_SEC('12:11:10');	-- 43870 출력

 

시스템 정보 함수

USER(), DATABASE()

SELECT CURRENT_USER(), DATABASE();	-- 현재 사용자, 현재 선택된 DATABASE 출력

FOUND_ROWS()

USE DB명;	-- DB선택
SELECT * FROM 테이블명;	-- 테이블에서 *(모든) 열 조회
SELECT FOUND_ROWS();	-- 바로 앞의 SELECT에서 조회된 행의 개수를 구함

ROW_COUNT()

USE DB명;	-- DB선택
UPDATE 테이블명 SET 값이숫자인열이름=값이숫자인열이름*2;	-- 테이블명의 값이숫자인열이름 행들을 *2 하여 업데이트
SELECT ROW_COUNT();	-- 바로 앞의 INSERT, UPDATE, DELETE문에서 변경된 행들의 개수를 반환. CREATE, DROP문은 0을 반환. SELECT는 -1을 반환

VERSION() - 현재 DBMS의 버전을 확인

 

SLEEP(초)

/* 쿼리의 실행을 초만큼 멈춤 */
SELECT SLEEP(5);	-- 5초간 멈춤
SELECT '5초후에 보여요';	-- 5초후 '5초후에 보여요' 출력

 

 

[MariaDB] 전체보기

'MariaDB' 카테고리의 글 목록