FUNCTION
데이터베이스에서 FUNCTION,함수는 일반적인 프로그래밍에서의 함수와 동일.
매개변수를 받아 특정 계산(작업)을 수행하고 결과를 반환하는 구조로 특정 동작을 수행하는 코드 부분을 의미한다.
AGGERATE FUNCTION (집계 함수)
- 여러 행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수
- GROUP BY에서 데이터를 압축할 때 사용
- COUNT(), MAX(), MIN(), AVG(), ROUND(), SUM()...
COUNT()
- 검색된 행의 수를 반환하며 NULL이 없는 컬럼을 대상으로 하는 것이 좋다.
- count(*)을 지양을 권장.
- select count(deptno) from dept;
MAX()
- 특정 행에 대한 최대값을 반환
- 숫자 뿐만 아니라 문자열(ㄱ-ㅎ.A-Z), 날짜 등도 가능
- select max(salary) as Max_Salary from employees e ;
MIN()
- 특정 행에 대한 최솟값을 반환
- select min(salary) as Min_Salary from employees e ;
AVG()
- 특정행의 평균을 반환
- select avg(salary) from employees;
SUM()
- 특정행의 합을 반환
select sum(salary) from employees;
NUMERIC FUNCTION (숫자 함수)
- 수 값을 인수로 받아 NUMBER 타입의 값을 반환하는 함수
ABS(n)
- 절대값을 계산하는 함수이다.
- SELECT ABS (-1.234) absolute;
-- 결과 : 1.234
CEIL(n)
- 올림값을 반환하는 함수이다.
- SELECT CEIL(10.1234) "CEIL";
- 결과 : 11 - SELECT CEIL(-10.1234) "CEIL";
- 결과 : -10
FLOOR(n)
- 버림값을 반환하는 함수이다.
- SELECT FLOOR(10.1234) "FLOOR";
- 결과 : 10 - SELECT FLOOR(-10.1234) "FLOOR";
- 결과 : -11
ROUND(n, [m])
- 반올림, n 값을 반올림, m은 반올림 자릿수
- SELECT ROUND(192.153, 1) "ROUND";
- 결과 : 192.2 소숫점 2자리를 반올림 하여 1자리 까지 표현 - SELECT ROUND(192.153, -1) "ROUND";
- 결과 : 190 숫자 2자리를 반올림 하여 1자리 까지 표현
TRUNCATE(n, m)
- n값을 절삭하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.
- SELECT TRUNCATE(7.5597, 2) "TRUNCATE";
- 결과 : 7.55
MOD(m, n)
- m을 n으로 나눈 나머지를 반환 한다. n이 0일 경우 m을 반환 한다.
- SELECT MOD(9, 4) "MOD" ;
- 결과 : 1
CHARACTER FUNCTION (문자형 함수)
CHAR, VARCHAR 타입을 인수로 받아 VARCHAR 타입의 값을 반환하는 함수이다.
CONCAT(char1, char2, ...)
- SELECT CONCAT('www.', 'mariadb','.org');
LOWER(char), UPPER(char)
- LOWER : 주어진 문자열을 소문자로 변환시켜 준다.
- UPPER : 주어진 문자열을 대문자로 변환시켜 준다.
- SELECT UPPER('mariadb') name UNION ALL
- SELECT LOWER('mariadb') name;
LPAD(char1, n [,char2]), RPAD(char1, n [,char2])
- LPAD : 왼쪽에 문자열을 끼어 넣는 역할을 한다.
- RPAD : 오른쪽에 문자열을 끼어 넣는 역할을 한다.
- n은 반환되는 문자열의 전체 길이를 나타내며, char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 잘려서 반환 한다.
- SELECT LPAD('mydatabase', 12, '*') name; -- **mydatabase
- SELECT RPAD('mydatabase', 12, '*') name; -- mydatabase**
- SELECT RPAD('mydatabase',9,'*') AS pass; -- mydatabas
SUBSTR(char, m ,[n]), SUBSTRB(char, m ,[n])
- SUBSTR 함수는 m 번째 자리부터 길이가 n개인 문자열을 반환 한다.
- m이 음수일 경우에는 뒤에서 m 번째 문자부터 n개의 문자를 반환한다. - SUBSTRB 함수에서 B는 Byte단위로 처리하겠다는 의미이다.
- 세번째 이후 문자열 반환. - SELECT SUBSTR('mydatabase', 3) name;
세번째 이후 네개의 문자열 반환. - SELECT SUBSTR('mydatabase', 3, 4) name;
뒤에서 세번째아후 두개의 문자열 반환.
SELECT SUBSTR('mydatabase', -3, 2) name;
LENGTH(char)
- 문자열의 길이를 반환 한다.(한글은 한 글자에 3byte <- UTF-8)
- SELECT LENGTH('마리아디비') length;
REPLACE(char1, str1, str2)
- REPLACE는 문자열의 특정 문자를 다른 문자로 변환 한다.
- 대소문자를 구분
- SELECT REPLACE('mydatabase','my','maria ') name;
INSTR (char1, str1)
- 문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환 한다.
- 지정한 문자열이 발견되지 않으면 0이 반환 된다.
- char1 : 지정문자, str1 : 검색문자
- SELECT INSTR('CORPORATE FLOOR','OK') idx; -- 0 반환
- SELECT INSTR('CORPORATE FLOOR','OR') idx; -- 2 반환
TRIM(char1)
- 양쪽 공백을 지운다.
- SELECT ' mydatabase ' AS title union ALL
- SELECT TRIM(' mydatabase ') AS title;
- TRIM(char1 FROM char2) 특정한 문자열을 명시하면 앞 뒤에서 지운다.
- SELECT TRIM('토' FROM '토마토') AS title;
LTRIM(char1)
- 왼쪽 공백 지운다.
- SELECT LTRIM(' mydatabase ') AS title;
RTRIM(char1)
- 오른쪽 공백 지움
- SELECT RTRIM(' mydatabase ') AS title;
DATE TIME FUNCTION (날짜 함수)
날짜 표시
DAYOFWEEK(DATE)
- 날짜를 한 주의 몇 번째 요일인지를 나타내는 숫자로 리턴
- 1-7 : 일요일 - 토요일 로 치환된다.
- select dayofweek('2021-06-30') as '요일';
- 4로 출력되며 수요일을 의미한다.
WEEKDAY(DATE)
- 날짜를 한 주의 몇 번째 요일인지를 나타내는 숫자로 리턴
- 0:월요일 ~ 6:일요일로 치환된다.
- select weekday('2021-06-30') as '요일';
- 2로 출력되며 수요일을 의미한다.
DAUOFMONTH(DATE)
- 그 달의 몇 번째 날인지를 알려준다. 리턴 값은 1에서 31 사이이다.
- select dayofmonth('2021-06-29') as '일';
DAYOFYEAR(DATE)
- 한 해의 몇 번째 날인지를 알려준다. 리턴 값은 1에서 366 사이이다.
- select dayofyear('2021-01-12');
MONTH(DATE)
- 해당 날짜가 몇 월인지 알려준다. 리턴 값은 1에서 12 사이이다.
- select month('2021-02-03');
DAYNAME(DATE)
- 해당 날짜의 영어식 요일이름을 리턴한다.
- select dayname('2021-12-25');
- Saturday로 출력된다.
MONTHNAME(DATE)
- 해당 날짜의 영어식 월 이름을 리턴한다.
- select monthname('2021-02-05');
- February로 출력된다.
QUARTER(DATE)
- 분기를 리턴한다 (1~ 4)
select quarter('2021-04-01') as '분기';
날짜 계산
PERIOD_ADD(P,N)
- yymm 또는 yyyymm 형식으로 주어진 달에 n개월을 더한다. 리턴 값은 yyyymm의 형식이다.
- select period_add(2109,36); -- 202409
PERIOD_DIFF(P1,P2)
- yymm 또는 yyyymm 형식으로 주어진 두 기간사이의 개월을 구한다
- select period_diff(2104,202109); -- -5
DATE_ADD(DATE,INTERVAL EXPR TYPE) or ADDDATE(DATE,INTERVAL, EXPR TYPE)
- 특정한 단위를 더한다.
- seconds, minutes, hours, days, months, years
- select date_add("2021-12-31 23:59:59",interval 1 second); -- 1초 더하기
- select date_add("2021-12-31 23:59:59",interval 30 day); -- 30일 더하기
- select date_add("2021-12-31 23:59:59",interval 1 month); -- 1개월 더하기
- select date_add("2021-12-31 23:59:59",interval "10:1" minute_second); -- 10분:1초 더하기
- select date_add("2021-12-31 23:59:59",interval "1:30" hour_minute); -- 1시간:30분 더하기
DATE_SUB(DATE,INTERVAL EXPR TYPE) or SUBDATE(DATE,INTERVAL, EXPR TYPE)
- 특정한 단위를 뺀다.
- seconds, minutes, hours, days, months, years
- select date_sub("2022-01-01 00:00:00",interval "1 1" day_hour); -- 1일 1시간
TO_DAYS(DATE)
- 주어진 날짜를 0000년-00월-00일 부터의 일수로 바꾼다.
- select to_days(210629); -- 0000년-00월-00일 부터 2021년-06월29일 까지의 일 수
- select to_days('2021-06-29');
FROM_DAYS(N)
- 주어진 일수로부터 날짜를 구한다(to_days 반대 개념)
- select from_days(738335);
날짜 형식
DATE_FORMAT(DATE,FORMAT)
- format 의 정의에 따라 날자 혹은 시간을 출력한다.
- %y : 2자리 년도 - %Y 는 4자리 년도
- %m : 월(01~12) - %M 은 월 이름
- %d 일(00~31)
- %W : 요일명, %w : 일주일의 몇 번째 요일인가(0=sunday~6=saturday)
- %c : 월(1~12), %e : 일(0~31), %H : 24시 형식의 시간 (00~23), %i : 분, %s : 초(00~59)
- %h : 12시 형식의 시간 (01~12), %p : am 또는 pm 문자
- %r : 시분초12시 형식 (hh:mm:ss [ap]m)
- %T : 시분초 24시 형식 (hh:mm:ss)
- %U : 한해의 몇 번째 주인가(0~52) 일요일이 시작일
- %u : 한해의 몇 번째 주인가(0~52) 월요일이 시작일
select date_format('2021-07-01 13:54:00', '%y년 %m월 %d일');
select date_format('2021-07-01 13:54:00', '%M %Y');
select date_format('2021-07-01 13:54:00', '%b %D (%a)');
select date_format('2021-07-01 13:54:00', '%b %D (%W)');
select date_format('2021-07-01 13:54:00', '%c-%e %H:%i:%s');
select date_format('2021-07-01 13:54:00', '%h:%i:%s %p');
select date_format(NOW(), '%r');
select date_format(NOW(), '%T');
select date_format(NOW(), '%U');
TIME_FORMAT(TIME,FORMAT)
- date_format()와 비슷한 역할을 하지만 단지 시,분,초 만을 나타낼 수 있다.
- select date_format('2021-07-01 13:54:00', '%h:%i:%s %p')
union all
select time_format('2021-07-01 13:54:00', '%h:%i:%s %p');
CURDATE()
- 오늘 날짜를 오늘 날짜를 'yyyy-mm-dd' 형식으로 리턴
- select curdate(); -- yyyy-mm-dd
- select curdate() + 0; -- yyyymmdd
CURTIME()
- 'hh:mm:ss' 형식으로 현재시간을 나타낸다.
- select curtime(); -- hh:mm:ss
- select curtime() + 0; -- hhmmss
NOW() | SYSDATE() | CURRENT_TIMESTAMP()
- 오늘 날자와 현재 시간을 'yyyy-mm-dd hh:mm:ss' 형식으로 반환
- select now();
- select now() + 0;
UNIX_TIMESTAMP()
- 인수가 없이 사용될 경우 현재 시간의 유닉스 타임스탬프,
- 날짜형식의 date 가 인수로 주어진 경우에는 주어진 날짜의 유닉스 타임스탬프를 반환
- 유닉스 타임스탬프 : 그리니치 표준시로 1970 년 1월 1일 00:00:00 이 후의 시간경과를 초단위로 나타낸 것
- select unix_timestamp();
- select unix_timestamp(now());
FROM_UNIXTIME(UNIX_TIMESTAMP)
- 주어진 유닉스 타임스탬프 값으로부터 'yyyy-mm-dd hh:mm:ss' 형식의 날짜로 반환
- select from_unixtime(1625031063);
- select from_unixtime(1625031063) + 0;
FROM_UNIXTIME(UNIX_TIMESTAMP,FORMAT)
- 주어진 유닉스 타임스탬프 값을 주어진 날짜 형식에 맞게 바꿔서 보여준다.
- select from_unixtime(unix_timestamp(),'%Y-%m-%d %h:%i:%s');
SEC_TO_TIME(SECONDS)
- 주어진 초를 'hh:mm:ss' 형식으로 반환
- select sec_to_time(2378);
- select sec_to_time(2378) + 0;
TIME_TO_SEC(TIME)
- 주어진 시간을 초 단위로 바꿔준다.
- select time_to_sec('22:23:00');
특정 년-월 의 마지막 날짜 구하기
select LAST_DAY('2022-02-01') as date;
날짜와 날짜 사이 모든 날짜 구하기
select from (
select adddate('2000-01-01',t4.i10000 + t3.i1000 + t2.i100 + t1.i*10 + t0.i) date_of_month from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) v where date_of_month between '2022-10-01' and LAST_DAY('2022-10-01');
날짜 기준으로 right outer join
select b.idx,b.bHit, m.reg_date from bbs b RIGHT OUTER JOIN (
select from (
select adddate('2000-01-01',t4.i10000 + t3.i1000 + t2.i100 + t1.i*10 + t0.i) reg_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) v
where reg_date between '2022-10-01' and LAST_DAY('2022-10-01')) m
ON b.reg_date = m.reg_date order by m.reg_date;
