함수
concat ( A ): concat이라는 함수 안에다가 A안에 속해있는 값을 하나씩 넣어서 모두 처리를 해라는 뜻
함수 종류
- 단일 행 함수
; 행 단위로 값을 처리하는 함수
단일 행은 select, where 절에서 사용 가능
→ CHAR_LENGTH(CONCAT(’A’. ‘B’)) - 다중 행 함수
: 여러 행의 값들을 묶어서 한번에 처리하는 함수
= 집계함수, 그룹함수
다중행은 select, having 절에서 사용 가능
→ where 절에서는 사용 불가(sub query 이용)
조건 처리 함수
- if (조건 수식 , 참 , 거짓)
→ 조건 수식이 True 이면 참 을 False 이면 거짓 을 출력한다 - ifnull (기준 컬럼 값 ), 기본 값
→ 기준 컬럼 값 이 NULL 값이면 기본 값 을 출력하고 NULL 이 아니면 기준 컬럼 값 을 출력 - nullif (컬럼1, 컬럼2)
→ 컬럼1 과 컬럼2 가 같으면 NULL 을 반환 , 다르면 컬럼1 을 반환
📍 문자열 관련 함수
- char_length (’v’) : v의 글자 수 반환
- upper(v), lower(v) : v를 모두 대문자/소문자 로 변환
- replace(기준 문자열, 원래 문자열, 바꿀 문자열) :기준 문자열의 원래 문자열을 바꿀 문자열로 바꾼다.
- format(숫자, 소수부 자릿수) : 정수부에 단위 구분자 "," 를 표시하고 지정한 소수부 자리까지만 문자열로 만들어 반환
- concat(v1, v2[, ..]) : 값들을 합쳐 하나의 문자열로 반환
- insert(기준 문자열, 위치, 길이, 삽입 문자열) : 기준 문자열의 위치(1부터 시작)에서 부터 길이까지 지우고 삽입 문자열을 넣는다.
- substring(기준 문자열, 시작 위치, 길이) : 기준 문자열에서 시작 위치부터 길이 개수의 글자 만큼 잘라서 반환한다. 길이를 생략하면 마지막까지 잘라낸다
- substring_index(기준 문자열, 구분자, 개수) : 기준 문자열을 구분자를 기준으로 나눈 뒤 개수만큼 반환. 개수: 양수 – 앞에서 부터 개수, 음수 – 뒤에서 부터 개수만큼 반환
- left(기준 문자열, 길이), right(기준 문자열, 길이) : 기준 문자열에서 왼쪽(left), 오른쪽(right)의 길이 만큼의 문자열을 반환한다.
- trim(방향 제거할문자열 from 기준문자열): 기준문자열에서 방향에 있는 제거할문자열을 제거한다.
- ltrim(문자열), rtrim(문자열), trim(문자열) : 문자열에서 왼쪽(ltrim), 오른쪽(rtrim), 양쪽(trim)의 공백을 제거한다. 중간 공백은 유지
→ 방향: both (앞,뒤), leading (앞), trailing (뒤) - lpad(기준 문자열, 길이, 채울 문자열), rpad(기준 문자열, 길이, 채울 문자열) : 기준 문자열을 길이만큼 늘린 뒤 남는 길이만큼 채울 문자열로 왼쪽(lpad), 오른쪽(rpad)에 채운다.
기준 문자열 글자 수가 길이보다 많을 경우 나머지는 자른다.
ex)
select char_length('가나다라마'); -- 글자수 밭환
select * from emp;
select char_length(emp_name) >= 7; # emp_name을 통째로 넣는것이 아니라 그 안에 있는 하나의 값을 넣는 것
select char_length(emp_name) from emp; #result set 에는 이게 들어가 있음
select upper('abcDEFgh'), lower('abcDEFgh');
select format(123456789, 0); -- 자리구분자를 넣을 숫자, 반올림할 자릿수 # 현재 0의 위치를 이야기 함(자릿수 넣는것은 필수)
select format(123456789.987654, 2);
select format(123456789.987654, 0); -- 0은 소수점 이하에서 반올림
select concat('홍길동', '님');
select concat ('나이:', 20, '세'); -- 어떤 값을 넣든간에 문자열로 다 붙여버림
select insert('123456789', 2, 3, '안녕'); -- 두번째 글자부터 3글자를 '안녕'으로 변경
select replace('123456789', 234, '안녕'); -- '234'문자열을 '안녕'으로 변경
select substring('1234567890', 4, 2); -- 4번째 글자부터 두글자 나머지를 반환
select substring_index('aaa-bbb-ccc-ddd-eee', '-', 3);
-- 문자열을 '-' 구분자를 기준으로 나눈 뒤 앞에서 3개를 반환
select substring_index('aaa-bbb-ccc-ddd-eee', '-', -3);
-- 개수: 음수 => 뒤에서 부터 3개를 반환
select left('1234567890', 5); -- 왼쪽에서 5글자 반환
select right('1234567890', 5); -- 오른쪽에서 5글자 반환
select trim(' aaaa '), char_length(trim(' aaaa ')); -- 좌우 공백 제거=> trim 하고 난 글자수 츶정
select rtrim(' aaaa ') as "b";
select ltrim(' aaaa ') as "b";
select trim(both '-' from '-------aaaa--------') as "b"; -- 여기서의 '-' 이것이 꼭 한글자일 필요는 없다
-- 어디에 있는것 지울 문자열 from 대상
select trim(leading '-+' from '-+-+-+-+-+-+-aaaa-+-+-+-+-+-+-+-') as "b"; -- 앞에 있는 것만
select trim(trailing '-+' from '-+-+-+-+-+-+-aaaa-+-+-+-+-+-+-+-') as "b"; -- 뒤에 있는 것만
select lpad('test', 10,' ') as 'b'; -- 자릿수를 맞춰주고 싶을 때 사용
-- 10글자로 맞출 때 값이 모자란 경우 왼쪽에 '공백'을 붙인다
select rpad('test', 10,' ') as 'b'; -- -- 10글자로 맞출 때 값이 모자란 경우 오른쪽에 '공백'을 붙인다
select rpad('test', 10,' '), char_length(rpad('test', 10,' ')) as 'b'; -- 자릿수 10개 확인
select lpad(3,2,'0');
select rpad('aaaaaaaaaaaaaa', 3, ''); -- 모자라면 채우고, 넘치면 버린다- abs(값) : 절대값 반환
- round(값, 자릿수) : 자릿수이하에서 반올림 (양수 - 실수부, 음수 - 정수부, 기본값: 0-0이하에서 반올림이므로 정수로 반올림)
- truncate(값, 자릿수) : 자릿수이하에서 절삭-버림(자릿수: 양수 - 실수부, 음수 - 정수부, 기본값: 0)
- ceil(값) : 값보다 큰 정수중 가장 작은 정수. 소숫점 이하 올린다.
- floor(값) : 값보다 작은 정수중 가장 작은 정수. 소숫점 이하를 버린다. 내림
- sign(값) : 숫자 n의 부호를 정수로 반환(1-양수, 0, -1-음수)
- mod(n1, n2) : n1 % n2
ex)
select abs(10), abs(-10); -- 절댓값 반환
select round(1.23456); -- 소숫점 이하에서 반올림
select round(1.6789, 2); -- 소숫점 2자리 이하에서 반올림
select round(189.123, -1); -- 0이 소숙점 이하에서 반올림이므로 -는 이제 반대로 올라가는 것 # 자리: 음수 -> 정수 위치
select ceil(50.99999); -- 내림(소수점 아래 정수만 반환)
select floor(50.99999); -- 내림(소수점 위 정수만 반환)
select truncate(1234.567, 2); -- 절삭(내림- 잘라서 뒤는 버린다고 생각) -> 자짓수 지정
select truncate(1234.567, -1); -- 얘도 음수는 정수자리로 이동
select sign(-10), sign(0), sign(10); -- 부호를 return- date/time/datetime : 산술연산 -> 정수로 변환한 다음에 계산
ex) 2023-09-01 ==> 20230901 + 5 - now() : 현재 datetime
- curdate() : 현재 date
- curtime() : 현재 time
- year(날짜), month(날짜), day(날짜) : 날짜 또는 일시의 년, 월, 일 을 반환한다.
- hour(시간), minute(시간), second(시간), microsecond(시간) : 시간 또는 일시의 시, 분, 초, 밀리초를 반환한다.
- date(), time() : datetime 에서 날짜(date), 시간(time)만 추출한다.
날짜 연산
- adddate/subdate(DATETIME/DATE/TIME, INTERVAL 값 단위)
: 날짜에서 특정 일시만큼 더하고(add) 빼는(sub) 함수.
→ 단위: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER(분기-3개월), YEAR - datediff(날짜1, 날짜2) : 날짜1 – 날짜2한 일수를 반환
- timediff(시간1, 시간2) : 시간1-시간2 한 시간을 계산해서 반환 (뺀 결과를 시:분:초 로 반환)
- dayofweek(날짜) : 날짜의 요일을 정수로 반환 (1: 일요일 ~ 7: 토요일)
- date_format(일시, 형식 문자열) : 일시를 원하는 형식의 문자열로 반환
⇒SELECT date_format (now(), ‘%Y 년 %m 월 %d 일
→ 참고 링크 (mysql)
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
→ 토요일이 1이 아니라 6 ( *오타 )
ex)
-- 실행 시점의 일/시를 조회 함수
select now(); -- 일시 -> datetime
select curdate(); -- 낭짜 -> date
select curtime(); -- 시간 -> time
-- 날짜 타입에서 년 월 일 조회
select year(now()), month(now()), day(curdate());
-- 시간 타입에서 시 분 초 조회
select hour(now()), minute(curtime()), second(curtime()), microsecond(now());
select date(now()); -- datetime -> date
select time(now()); -- datetime -> time
-- 날짜 연산
select adddate(now(), interval 2 day);
select adddate(now(), interval 2 month);
select adddate(now(), interval 2 year);
select adddate(now(), interval 2 quarter); -- 2 분기 후 => 6개월 후
select subdate(now(), interval 2 year); -- 2년 전
select adddate(now(), interval -2 year); -- 2년 전
select adddate(curtime(), interval 10 hour);
select adddate(curtime(), interval 10 minute);
select datediff(curdate(), '2023-08-30'); -- curdate: 오늘 날짜에섭 투터 2023/8/30 일수 차이
select datediff('2023-08-30', curdate()); -- 앞에 날짜가 과거일 경우 해당하는 날짜 만큼의 음숙 나온다
select timediff(curtime(), '11:20:10'); -- curtime - '11:20: 10' 차이가 나는 시간 분 초를 반환한다
select dayofweek(now()); -- 해당하는 실행 시점의 요일을 반환(1: 일요일 ~ 7: 토요일)
select date_format(now(), '%Y년 %m월 %d일 %h시 %i분 %s초 %p %w');
select date_format(curdate(), '%m/%d/%y %w');- ifnull (기준컬럼(값), 기본값) : 기준컬럼(값)이 NULL값이면 기본값을 출력하고 NULL이 아니면 기준컬럼 값을 출력
- if (조건수식, 참, 거짓) : 조건수식이 True이면 참을 False이면 거짓을 출력한다.
- nullif(컬럼1, 컬럼2) : 컬럼1과 컬럼2가 같으면 NULL을 반환, 다르면 컬럼1을 반환
- coalesce(ex1, ex2, ex3, .....) : ex1 ~ exn 중 null이 아닌 첫번째 값 반환.
ex)
select ifnull(null, "없음"); -- select 시 그 행의 column이 없는 경우
select ifnull(comm_pct, 'no commisision') from emp;
-- 조건연산자 기능 함수
select salary, if(salary > 10000, '평균이상', '평균미만') from emp; -- 조건 연산자라고 생각 가능
select nullif(10, 10); -- 두 값이 같으면 null 반환
select nullif(100, 1); -- 두 값이 다르면 앞의 값을 반환
-- ex) nullif(2022년 판매개수, 2021년 판매개수)
select coalesce(null, null, 10, 20, 30); -- 값이 나열 되었을 때 null 이 아닌 첫번 째 값이 returnCASE 문
case문 동등비교
case 컬럼 when 비교값 then 출력값
[when 비교값 then 출력값]
[else 출력값]
endif dept_name==null:
return '부서없음'
elif dept_name=='IT':
return '전산실'
elif dept_name=='Finance':
return '회계부'
else:
return dept_namecase 절은 where 절과 select 절 모두에 사용 가능
ex)
select case dept_name when 'IT' then '전산실'
when 'Finance' then '회계부'
when 'Sales' then '영업부'
else dept_name -- 원래 값을 return 해라
end as "부서명"
from emp; -- case문은 끝났다는 의미로 end를 붙여줘야 함
-- 마지막에 order by 1;를 추가해서 정렬 가능
-- else dept_name 에서 else ifnull(dept_name, '부서 없음') 으로 변경 시
--> 원래 값을 return 하고 null이라면 부서 없을을 return📍 CASE 조건문
case문 조건문
case when 조건 then 출력값
[when 조건 then 출력값]
[else 출력값]
endex)
-- EMP테이블에서 급여와 급여의 등급을 조회 할 때,
-- 급여 등급은 10000이상이면 '1등급', 10000미만이면 '2등급' 으로 나오도록 조회 할 경우
select salary,
case when salary >= 10000 then '1등급'
when salary < 10000 then '2들급'
end "salary 등급",
case when salary >= 10000 then '1등급'
else '2등급'
end "salary 등급"
from emp;
- 암시적 변환 : 구문에 맞춰 MySQL 서버가 알아서 변환
– select ‘1000’ + ‘2000’; 문자열 ‘1000’, ‘ 을 정수로 변환 후 더한다 – select concat (322, 개 ’); 322 정수를 문자열로 변환한 뒤 붙인다
- 명시적 변환 : 변환 함수를 이용해 명시적으로 변환한다
- cast (값 as 변환할 타입)
- convert (값 변환할 타입) → 값을 변환할 타입으로 변환한다
→ 두 함수는 구문만 차이가 있다
ex)
select '1000' + '2000';
select curdatre() + 10;
select convert(curdate(), signed);
select convert (20130901, date);
select convert (102030, time);
-- 위와 값은 역할 하는 함수 cast
select cast(102030 as time);