[MySQL] 내장함수 종류 정리

집계 함수

---

COUNT(필드명) --NULL 값이 아닌 레코드 수를 구한다.

SUM(필드명) - 필드명의 합계를 구한다.

AVG(필드명) - 각각의 그룹 안에서 필드명의 평균값을 구한다.

MAX(필드명) - 최대값을 구한다.

MIN(필드명) - 최소값을 구한다.

select userId, sum(amount) as 'SUM' # as안쓰면 sum(amount)이 컬럼 이름이 되니까 깔끔하게 별칭 사용
from buyTbl
group by userid; #그룹핑을 안해주면 sum이 전체를 더해버린다. 그룹을 해줘야 그룹된 id에 맞게 sum을 해준다

select name, height 
from userTbl 
where height = (select max(height) from usertbl) or height = (select min(height) from usertbl); 
--> 회원들 중에 가장 큰 키인 사람과 가장 작은 키 사람

#도시는 총 몇개인가?
select count(*) from city;

※ 참고

집계함수는 select에 써서 테이블로 나타낼수도 있고, having에서 조건절 재료로 쓰일 수도 있다.
전체를 집게하는 것이 필요할때는 따로 select 집계함수 from 테이블명을 쓴다

 

---

집계함수 조건 having

#having : where과 비슷한 개념으로, 집계함수에 대해서 조건 제한을 걸때!! group by 다음에 쓸것!
select countrycode, max(population) as 'max'
from city
group by countrycode
having max > 8000000; # 위에서 max를 별칭으로 지정함

 

ex) 동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.

SELECT name, count(name) -- 동물 이름, 해당 이름이 쓰인 횟수
from animal_ins
where name is not null -- 집계 하기 전에, 우선 이름이 없는 동물은 제외
group by name -- 동물 이름으로 그룹핑 함
having count(name) > 1 -- 그룹핑 중에, 두 번 이상 쓰인 이름만 고름
order by name -- 결과는 이름 순으로 조회

 

프로그래머스

 

 

 

Math 함수

---

 

ABS(숫자) - 절대값 출력.

MOD (분자, 분모) - 분자를 분모로 나눈 나머지를 구한다. (연산자 %와 같음)

 

소수

CEILING(숫자) - 소수점 올림

FLOOR(숫자) - 소수점 내림

TRUNCATE(숫자,자릿수) - 숫자를 소수점 이하 자릿수에서 버림.

ROUND(숫자,자릿수) - 숫자를 소수점 이하 자릿수에서 반올림. (자릿수는 양수,0,음수를 갖을 수 있다.)

round(값, 반올림 할 자리)
round(10.95, -1);

 

MAX / MIN 수

GREATEST(숫자1,숫자2,숫자3...) - 주어진 수 중 제일 큰 수 리턴.

LEAST(숫자1,숫자2,숫자3...) - 주어진 수 중 제일 작은 수 리턴.

 

진수

bin() : 2진수

hex() : 16진수

oct() : 8진수

select bin(31), hex(31),oct(31);

 

고급 함수

SQRT(4) : 루트

POW(2, 3) : 제곱

EXP(3) : e의 거듭제곱

LOG(3) :자연로그

 

삼각 함수

SIN(PI() / 2) 

COS(PI())

TAN(PI() / 4)

 

랜덤 값

ROUND(RAND()*100, 0) : 0 ~ 100 사이 랜덤값

 

각도 값

degrees : 라디안을 각도값으로

radians : 각도값을 라디안으로 변환

select degrees(pi()),radians(180);

 

 

---

String 함수

 

문자열 길이

bit_length : 할당된 비트의 크기

char_length : 문자의 개수

length : 할당된 byte 반환

-- 한글과 영어의 byte는 다르다.
select bit_length('abc'), char_length('abc'), length('abc');
select bit_length('가나다'), char_length('가나다'),length('가나다');

 

문자열합치기

concat('My', 'sql op', 'en source');

​

문자열 반복

repeat('이것이',3);

​

문자열 뒤집기

reverse('mysql');

​

문자열 위치

-- mysql은 배열인덱스 시작이 1임.

LOCATE('abc', 'ababcDEFabc'); // 3

LOCATE('abc', 'ababcDEFabc', 4); // 9

​

문자열 지정한 갯수만큼 오른쪽 왼쪽 뽑음

left('MySQL is an open source relational database management system', 5); -- MYSQL
right('MySQL is an open source relational database management system', 6); -- system

 

문자열 분리

-- mysql은 배열인덱스 시작이 1임.

substring('대한민국만세',1,2);

​

문자열 소문자 대문자

select lower('MySQL is an open source relational database management system');

select upper('MySQL is an open source relational database management system');

​

문자열 교체

replace('MSSQL', 'MS', 'My'); #파라미터1의 파라미터2부분을 파라미터3으로 변경

​

문자열 공백 없애기

SELECT

TRIM('     Mysql     ') -- 문자열 공백 다 없애줘

TRIM(leading '!' FROM '!!!!!!!!!!!!!!!!MySQL PHP###') -- 문자열 앞에 '!' 다 없애줘 -> MySQL PHP###

TRIM(trailing '@' from '%%%MYSQL@@@@@@@@'); -- 문자열 뒤쪽 @ 업애줘 -> %%%MYSQL

​

ltrim('     이것이') : 왼쪽 공백 제거

rtrim('이것이     ') : 오른쪽 공백 제거

​

문자열 포맷

select format(123123123123.123123123, 3);

 -> 123,123,123,123,123

---

 

Date 함수

---

현재 날짜와 시간 출력

NOW()

SYSDATE()

CURRENT_TIMESTAMP() 

​

현재 날짜 출력

CURDATE()

CURRENT_DATE() 

​

현재 시간 출력

CURTIME()

CURRENT_TIME() 

​

날짜에서 기준값 만큼 덧셈

DATE_ADD(날짜, INTERVAL 기준값) 

※ 기준값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

​

날짜에서 기준값 만큼 뺄셈

DATE_SUB(날짜, INTERVAL 기준값) 

※ 기준값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

​

두개의 날짜사이의 차이값을 구하기

SELECT DATEDIFF('구분자','Start_Date','End_Date')

 

DATEDIFF()는 총 3개의 인수가 있는데 Start_Date와 End_Date는 차이를 구할 두개의 날짜값을 넣는곳이고 '구분자'는 어떤차이를 구할지 정해주는 부분이다.

예를 들어 두 날짜사이의 날짜 차이를 구하고 싶으면 'day' 혹은 'dd'등을 넣어주면 된다.

 

예를 들어 2018년의 일수를 구하고 싶다면

SELECT DATEDIFF(dd,'2018-01-01','2018-12-31') + 1

※자주하는 실수인데 한달 일수를 구하는데 31일 - 1일을 하면 30일이 된다.

 

날짜 포맷

YEAR(날짜) -날짜의 연도 출력.

MONTH(날짜) -날짜의 월 출력.

MONTHNAME(날짜) -날짜의 월을 영어로 출력.

DAYNAME(날짜) -날짜의 요일일 영어로 출력.

DAYOFMONTH(날짜) -날짜의 월별 일자 출력.

DAYOFWEEK(날짜) -날짜의 주별 일자 출력(월요일(0),화요일(1)...일요일(6))

WEEKDAY(날짜) -날짜의 주별 일자 출력(월요일(0),화요일(1)...일요일(6))

DAYOFYEAR(날짜) -일년을 기준으로 한 날짜까지의 날 수.

WEEK(날짜) -일년 중 몇 번쨰 주.

​

FROM_DAYS(날 수) : 00년 00월 00일부터 날 수 만큼 경과한 날의 날짜 출력.

TO_DAYS(날짜) : 00 년 00 월 00일 부터 날짜까지의 일자 수 출력.

DATE_FORMAT(날짜,'형식') : 날짜를 형식에 맞게 출력

 

 

 

---

참고

• https://faq.hostway.co.kr/Linux_DB/7421