[MariaDB] chapter4

group by, having, 집계함수, rollup

group by : 집계 함수와 함께 데이터를 그룹화하는 데 사용

- 쿼리문 순서 -
select * 
[from table]
[where 조건문]
[group by 컬럼명]
[having 조건문]
[order by asc/desc]

SELECT grade, SUM(height), AVG(height), MAX(height),MIN(height) 
FROM student 
GROUP BY grade;

SELECT position,emp_type, SUM(pay), AVG(pay) 
FROM emp2 
GROUP BY POSITION,emp_type;

having : where절과 비슷한 개념으로 GROUP BY 절과 함께 데이터를 그룹화하고 그룹화된 결과에 조건을 적용하는 데 사용
HAVING 절은 특히 그룹화된 데이터에 대한 필터링 조건을 설정할 때 유용하며, 집계 함수와 함께 사용

SELECT customer_id, SUM(order_amount)
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) >= 1000;

집계함수 : 데이터베이스 테이블의 열(칼럼)에 대한 집계(aggregate) 또는 계산된 결과를 반환하는 함수

함수명	설명
AVG()	평균을 구한다.
MIN()	최솟값을 구한다.
MAX()	최댓값을 구한다.
COUNT()	행의 개수를 센다.
COUNT(DISTINCT)	행의 개수를 센다(중복은 1개만 인정)
STDEV()	표준편차를 구한다.
VAR_SAMP()	분산을 구한다.

rollup : 총합 또는 중간 합계가 필요하다면 group by절과 함께 with rollup문을 사용하면 됨.

분류별로 합계 및 그 총합을 구하고 싶으면 
SELECT emp_type,hobby,sum(pay)
FROM emp2 GROUP BY emp_type,hobby
 WITH rollup;

데이터 형식과 형변환

cast(expression as 데이터형식)
convert(expression, 데이터형식)

SELECT NAME, pay, CAST(pay*1.02 AS SIGNED integer) FROM professor;
SELECT NAME, pay, convert(pay*1.02 , SIGNED integer) FROM professor;

[데이터 형식]
BINARY : 이진(binary) 데이터
CHAR : 문자열
DATE : 날짜 정보
DATETIME : 날짜와 시간 정보
DECIMAL : 고정 소수점 숫자 (정확한 소수점 표현이 필요한 정밀한 계산 활용)
JSON : JSON(JavaScript Object Notation) 형식의 데이터
SIGNED INTEGER : 부호 있는 정수(양수,음수)
TIME : 시간 정보
UNSIGNED INTEGER : 부호 없는 정수(양수만 가능)

제어 흐름 함수

if : 수식이 참 또는 거짓인지 결과에 따라 2중 분기

select if (100>200, '참','거짓'); --거짓 출력

ifnull : 수식1이 null이 아니면 수식1이 반환, null이면 수식2가 반환

select ifnull(null,'널널'), ifnull(100,'널널'); -- 널널, 100 출력

nullif : 수식1과 수식2가 같으면 null, 다르면 수식1을 반환

select nullif(100,100), nullif(200,100); -- null, 200출력

case ~ when ~ else ~ end : 다중 분기에 사용

select case 10
when 1 then '일'
when 5 then '오'
when 10 then '십'
else '모름'
end;  -- 십 출력

문자열 함수

ascii(아스키코드), char(숫자)

select ascii('A'), char(65); -- 65와 'A' 출력

bit_length(문자열)

할당된 bit 크기 출력
select bit_length('abc'); -- 24 출력

[참조]
영어, 숫자, 특수문자는 1바이트/8비트, 한글은 3바이트/24비트로 계산

char_length(문자열)

문자의 개수 출력
select char_length('abc'); -- 3 출력

length(문자열)

할당된 바이트 수 출력
select length('abc') -- 3 출력

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

문자열들을 이어 준다.
select concat(pay, name) from professor;

limit 숫자

검색 결과에서 반환할 행의 수를 제한하는 데 사용
SELECT * FROM members LIMIT 10; -- 처음 10개의 행만 출력

concat_ws(구분자, 문자열1, 문자열2...)

구분자와 함께 문자열을 이어준다.
select concat_ws('/','2002','01','01') -- 2002/01/01 출력

elt(위치, 문자열1,문자열2,...)

위치번째에 해당하는 문자열 출력 
만약 위치의 값이 1.4이면 반올림하여 1로 치환 후 위치번째 문자열 출력
select elt(2,'하나','둘','셋'); -- '둘' 출력

field(찾을 문자열, 문자열1,문자열2,...)

찾을 문자열의 위치를 찾아서 출력 매치되는 문자열이 없으면 0 출력
select field('둘','하나','둘','셋'); -- 2 출력

find_in_set(찾을 문자열, 문자열리스트)

주어진 문자열이 쉼표로 구분된 문자열 목록 안에 존재하는지 확인 후 위치 출력
select find_in_set('둘','하나,둘,셋'); -- 2출력

instr(기준 문자열, 부분문자열)

기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 출력
select instr('하나둘셋','둘'); -- 3 출력

locate(부분 문자열, 기준문자열)

instr()과 동일하지만 파라미터의 순사거 반대로 되어 있음
select locate('둘','하나둘셋') -- 3 출력
**locate() 와 position()은 동일한 함수

format(숫자, 소수점 자릿수)

숫자를 소수점 아래 자릿수까지 표현, 1000단위마다 콤마(,)표시
select format(123456.123456,4); -- 123,456.1235 출력
**소수점 넷째자리까지 출력하는데 다섯째 자리에서 반올림

bin(숫자), hex(숫자), oct(숫자)

2진수, 16진수, 8진수의 값을 출력
select bin(31), hex(31), oct(31); -- 11111, 1F, 37을 출력

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

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

left(문자열,길이), right(문자열,길이)

왼쪽 또는 오른쪽에서 문자열의 길이만큼 출력
select left('abcdefghi',3), right('abcdefghi',3); -- 'abc', 'ghi'출력

upper(문자열), lower(문자열)

소문자를 대문자로, 대문자를 소문자로 변경
select lower('abc'), upper('abc'); -- 'abc','ABC' 출력

lpad(문자열, 길이, 채울 문자열), rpad(문자열, 길이, 채울 문자열)

문자열을 길이만큼 늘린 후에, 빈곳을 채울 문자열로 채운 뒤 출력
select lpad('이것',5,'##'),rpad('이것',5,'##'); 
-- '###이것', '이것###' 출력

ltrim(문자열), rtrim(문자열)

문자열의 왼쪽/오른쪽 공백을 제거. 단 중간의 공백은 제거되지 않음.
select ltrim('  이것'),rtrim('이것   '); -- 둘다 공백이 제거된 '이것' 출력

trim(문자열), trim(방향 자를_문자열 from 문자열)

trim은 앞뒤 공백을 모두 제거
방향(leading(앞),both(양쪽),trailing(뒤) 로 방향 지정 후 제거 할 수 있음)
select trim('   이것이   '), trim(both 'ㅋ' from 'ㅋㅋ재미ㅋㅋ') ;
-- '이것이' 와 '재미'출력 
select trim(leading 'ㅋ' from 'ㅋㅋ재미ㅋㅋ') ; --'재미ㅋㅋ' 출력

repeat(문자열, 횟수)

문자열을 횟수만큼 반복
select repeat('이',5) -- '이이이이이' 출력

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

문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿈
select replace('이것이 MariaDB다','이것이','This is');
-- 'This is MariaDB다' 출력

reverse(문자열)

문자열의 순서를 거꾸로 출력
select reverse('aabbcc'); -- 'ccbbaa' 출력

space(길이)

길이 만큼의 공백을 출력
select concat('이것이',space(10),'엘리스다');
-- 이것이          엘리스다' 출력

substring(문자열, 시작위치, 길이) 또는 substring(문자열 from 시작위치 for 길이)

시작 위치부터 길이만큼 문자를 출력. 길이가 생략되면 문자열의 끝까지 출력
select substring('대한민국만세',3,2); -- 민국 출력
select substring('대한민국만세' from 3 for 2); -- 민국 출력
** substring(), substr(), mid()는 동일함수

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

문자열에서 구분자가 왼쪽부터 횟수 번째 나오면 그 이후의 오른쪽은 버림.
횟수가 음수면 오른쪽부터 세고 왼쪽을 버림
select substring_index('www.naver.com','.',2); -- 'www.naver' 출력
select substring_index('www.naver.com','.',-2); -- 'naver.com' 출력

수학함수

abs(숫자)

숫자의 절대값을 계산

삼각함수

acos(숫자), asin(숫자), atan(숫자), atan2(숫자1,숫자2),
sin(숫자), cos(숫자), tan(숫자) 

ceiling(숫자), floor(숫자), round(숫자)

올림, 버림, 반올림을 계산

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

숫자를 원래 진수에서 변환할 진수로 계산
select conv('AA',16,2) -- 10101010 출력

degrees(라디안값), radians(각도값), pi()

라디안 값을 각도값으로, 각도값을 라디안값으로, pi값인 3.141592 출력

지수,로그

exp(x) 는 무리수 상수 'e'의 지수 함수
ln(숫자) 는 무리수 상수 'e'의 로그 함수
log(숫자), log(밑수, 숫자), log2(숫자), log10(숫자)

mod(숫자1, 숫자2) 또는 숫자1 % 숫자2 또는 숫자1 mod 숫자2

숫자1을 숫자2로 나눈 나머지값을 계산
select mod(157,10); -- 7을 출력

pow(숫자1,숫자2), sqrt(숫자)

거듭제곱값 및 제곱근을 계산
select pow(2,3), sqrt(9); -- 2의 3제곱과 루트9를 출력
**pow()와 power()은 동일함수

rand()

0 이상 1 미만의 실수 아무거나 출력
만일 m <= 임의의 정수 < n 를 구하고 싶으면 floor(m + (rand() *(n-m)); 사용
select rand() -- 0~1미만 실수 출력
select floor(1+(rand() * (7-1))); -- 주사위 숫자 출력

sign(숫자)

숫자가 양수, 0, 음수인지를 구함 결과는 -1,0,1 셋 중 하나를 출력
select sign(100), sign(0), sign(-122.22); -- 1, 0, -1 출력

truncate(숫자,정수)

숫자를 소수점을 기준으로 정수 위치까지 구하고 나머지는 버림
select truncate(12345.12345,2),truncate(12345.12345,-2) 
-- 12345.12, 12300을 출력

날짜 및 시간 함수

adddate(날짜, 차이), subdate(날짜, 차이)

날짜를 기준으로 차이를 더하거나 뺸 날짜를 구함
select adddate('2022-01-01',interval 31 day) -- 2022-02-01 출력
select subdate('2022-01-01',interval 31 day) -- 2022-12-01 출력
**day대신에 month, year도 가능

addtime(날짜/시간, 시간), subtime(날짜/시간, 시간)

날짜/시간을 기준으로 시간을 더하거나 뺸 결과를 구함
select addtime('2022-01-01 23:59:59','1:1:1'), 
addtime('15:00:00','2:10:10');
-- '2022-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()

curdate()는 현재 연-월-일, curtime()은 현재 시:분:초 출력
now(), sysdate()는 현재 연-월-일 시:분:초 출력

year(날짜), month(날짜), day(날짜), hour(시간), minute(시간), second(시간), microsecond(시간)

날짜 또는 시간에서 연,월,일,시,분,초 밀리초를 구함
**dayofmonth()와 day()는 동일함수

date(), time()

datetime형식에서 연-월-일 및 시:분:초만 추출
select date(now()), time(now()); 

datediff(날짜1, 날짜2), timediff(날짜1또는시간1, 날짜2또는시간2)

datediff는 날짜1 - 날짜2의 일수 결과 (몇일 남았는지 출력)
timediff는 시간1 - 시간2의 결과를 출력 날짜간 일수는 출력x

dayofweek(날짜), monthname(), dayofyear(날짜)

요일(1:일, 2:월~7:토) 및 1년 중 몇번쨰 날짜인지를 구함
select dayofweek(curdate()), monthname(curdate()), dayofyear(curdate());
-- 2(월요일), november, 310 출력

last_day(날짜)

주어진 날짜의 마지막 날짜를 구함. 주로 그 달이 몇일까지 있는지 확인
select last_day('2022-02-01'); -- '2022-02-28' 출력

makedate(연도,정수)

연도에서 정수만큼 지난 날짜를 구함
select makedate(2022,32); -- 2022년의 32일이 지는 '2022-02-01' 출력

maketime(시,분,초)

시, 분, 초를 이용해서 '시:분:초'의 TIME 형식을 만듬
select maketime(12,11,10); -- '12:11:10'의 time형식을 출력

period_add(연월, 개월수), period_diff(연월1,연월2)

period_add는 연월에서 개월만큼의 개월이 지난 연월을 구함
연월은 YYYY또는 YYYYMM형식을 사용
period_diff는 연월1-연월2의 개월수를 구함
select period_add(202201,11), period_diff(202201,201812);
-- 2022년 12월과 37개월을 출력

quarter(날짜)

날짜가 4분기 중에서 몇 분기인지를 구함
select quarter('2022-07-07'); -- 3출력

time_to_sec(시간)

시간을 초 단위로 구함
select time_to_sec('12:11:10'); -- 43870초 출력

시스템 정보 함수

uesr(), database()

현재사용자 및 현재 선택된 데이터베이스를 구함
**uesr(), session_user(), current_user()는 모두 동일함수
database(), schema()도 동일 함수

found_rows()

바로 앞의 select문에서 조회된 행의 개수를 구함
SELECT * FROM emp2;
SELECT FOUND_ROWS(); -- emp2테입르의 행 개수 출력

row_count()

바로 앞의 insert, update, delete문에서 입력, 수정, 삭제된 행의 개수를 구함
create, drop문은 0을 출력, select문은 -1을 출력
use sqlDB
update buyTBL set price=price*2;
select row_count(); -- 구매테이블의 12개의 행을 변경했으므로 12가 출력

version()

현재 MariaDB의 버전을 구함

sleep(초)

쿼리의 실행을 잠깐 멈춤
select sleep(5);
select '5초후에 보임'; -- 5초를 멈춘 후에 결과 출력

to_char/date_format

to_char와 date_format은 날짜와 시간을 문자열로 변환하는 데 사용하는 함수
to_char(expr, format)
date_format(expr, format)

SELECT to_char(now(), '%Y-%m-%d');  -- 대소문자 조심
SELECT to_char(now(), '%H:%i:%s');

%Y	년도, 4자리
%y	년도, 2자리
%m	월, 2자리
%d	일, 2자리
%H	시간, 2자리
%i	분, 2자리
%s	초, 2자리
%p	오전/오후