집계 함수
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 -- 결과는 이름 순으로 조회
프로그래머스
코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요.
programmers.co.kr
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(날짜,'형식') : 날짜를 형식에 맞게 출력
참고