2023.03.09 DB

DB MySQL ver

26. 날짜 함수

26.1 현재 시간을 얻은 함수

1.현재시간의 연월일
curdate()
current_date()

2.현재 시간 시분초
curtime()
current_time()

3.현재 시간의 연월일 시간
NOW();
sysdate();
current_timestamp()

오라클은 다음과 같은데 ()가 필요가 없지만 mysql에서는 파라미터가 없더라도 함수를 넣어야한다.
SELECT SYSDATE, SYSTIMESTAMP , CURRENT_DATE, CURRENT_TIMESTAMP

26.2 포맷변경

date_format(date,format)
format 의 정의에 따라 날짜 혹은 시간을 출력한다.
매우 빈번히 쓰이는 함수 이다.
format 에 사용되는 문자는 다음과 같다.

26.2.1 연도

%Y : 4자리 년도
%y : 2자리 년도

26.2.2 월

%M : 월이름 (january ~ december)
%m : 월(01 ~ 12)
%c : 월(1 ~ 12)
%b
짧은 월이름 (jan..dec)

26.2.3 일

%d : 일(01 31)
%e : 일( 131 )
%W : 요일명 (monday ~ sunday)
%a : 짧은 요일명(Mon~ Sun)
%D : 영어식 접미사를 붙인 일(1st, 2nd, 3rd)

26.2.4 시

%H : 12시 형식의 시간 (01..12)
%l : 12시간 형식 (1 ~ 12)
%h : 24시 형식의 시간 (00 ~ 23)
%k : 24시 형식의 시간 (0 ~23)

26.2.4 분

%i : 분 (00~59)

26.2.5 초

%s : 초 (00..59)

26.2.6 시분초 통으로

%r : 시분초 (hh:mm:ss [ap]m)
%t : 시분초 24시 형식 (hh:mm:ss)

26.2.7 기타

%p : am 또는 pm 문자
%w : 일주일의 몇 번째 요일인가(0=sunday..6=saturday)
%U : 한해의 몇 번째 주인가(0..52). 일요일이 시작일
%u :한해의 몇 번째 주인가(0..52). 월요일이 시작일

26.3 날짜 추출 함수

YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND, MICROSECOND
함수(기준 날짜)하면 각각 연 월 일 등을 뽑아낼 수 있다.

가입한 회원 중에 비수기(2,3,11,12)월 달에 가입한 회원을 조회
SELECT * FROM MEMBER WHERE MONTH(REGDATE) IN (2,3,11,12);

26.4 날짜 누적함수

adddate(기준연월일, interval 값 타입)
subdate()
date_add()
date_sub()
기준시간에 값만큼 타입에서 계산

가입 회원 중에 가입한지 6개월이 안되는 회원
SELECT * FROM MEMBER WHERE ADDDATE(SYSDATE(), interval -6 month) < REGDATE;

26.5 날짜의 차이를 알아내는 함수

DATEDIFF(날짜1, 날짜2); 날짜1 - 날짜2 날짜 일수의 차이
TIMEDIFF(시간1, 시간2); 시간1 - 시간2 시간의 차이

TIMESTAMPDIFF(타입, 날짜1, 날짜2);
날짜1 부터 날짜2까지 타입의 차이
타입은 다음과 같다.
YEAR : 연 MONTH : 월
QUARTER : 분기 WEEK : 주
DAY : 일 HOUR : 시
SECOND : 초 MINUTE : 분

가입 회원 중에 가입한지 6개월이 안되는 회원
SELECT * FROM MEMBER WHERE TIMESTAMPDIFF(MONTH, REGDATE, SYSDATE()) < 6;

26.6 마지막 날짜

SELECT LAST_DAY('2020-02-04');
해당월의 마지막 날짜

날짜는 너무나도 많으니 필요할때 하나씩 알아보는 수밖에 없다.

27. 형식 변환 함수

FORMAT(컬럼 및 데이터, 소수점 이하 표시될 자리수)
숫자 타입의 데이터를 세 자리마다 쉼표(,)를 사용하는 '#,###,###.##' 형식으로 변환해준다.
반환되는 데이터의 형식이 숫자 타입이 아닌 문자열 타입이다.

숫자 -> 날짜 문자열 등 함수
CAST(숫자 AS 타입)
CONVERT(숫자, 타입)
SELECT CAST(20211014 AS DATE); 2021-10-14
SELECT CAST(20211014 AS CHAR); '20211014'

필요할때마다 찾아보자. 오라클과 너무다르다!!!

28. DECODE 함수 vs if함수

DECODE는 오라클의 함수이다.
조건에 따른 값 선택하기 DECODE(기준값,비교값, 출력값, 비교값, 출력값)
삼항연산자의 느낌이다.

mysql은 비슷하게 if함수를 가지고 있다.
IF(조건,'조건이 일치할 경우의 VALUE','조건이 일치하지 않을경우의 VALUE')
오라클은 기준값을 = 밖에 못넣지만 mysql은 비교연산자를 다 넣을 수있다.
즉 IF(기준값 <= 10, A,B)이런식도된다.

29. SELECT 구절과 정렬(ORDER BY)

집계함수는 select구절과 함께 활용되어야한다.
SELECT문의 구절
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
이 순서를 잘외우고 있어야한다. 순서를 지켜야 한다. 순서가 바뀌어서는 안된다.

FROM 데이터를 연산하는 구조 테이블.
테이블들이 가지고 있는 구조를 알 수 잇다.
테이블이 오거나 연산하는 공간 합쳐진게 올 수도 있다.
격자형 데이터를 모으는 것

WHERE 필터링

GROUP BY(COUNT, AVG) 집계 함수

HAVING 집계한것으로 필터링
집계된 결과를 필터링
HAVING COUNT(ID)>=2

29.1 ORDER BY 순서정렬

ORDER BY 컬럼 [ASC/DESC]
ASC 오름차순 DESC내림차순

아이디 기준으로 역순 정렬해보기
SELECT * FROM MEMBER ORDER BY ID ASC;
SELECT * FROM MEMBER ORDER BY ID DESC;

회원중에서 박씨 성을 가진 회원을 조회하고 나이기준 오름차순 정렬
SELECT * FROM MEMBER WHERE NAME = '박%' ORDER BY AGE ASC;

2차정렬하려면 , 로 구분해서 해주면된다.
SELECT * FROM NOTICE ORDER BY HIT DESC, REGDATE DESC;

30. 집계함수와 group by

집계함수 COUNT, SUM, AVG, MAX, MIN

COUNT : 개수를 집계한다.
COUNT NULL값은 집계가 안된다.
그럼 레코드 수를 알고 싶다면?
1.절대 NULL이들어가지않는 값으로 계산
2.COUNT(*)
전자가 빨라서 바람직함.
SUM:총합 AVG:평균 MAX:최대값 MIN:최소값

그런데 집계를 할때 보통 그냥 하지 않고 그룹으로 나눠서한다.
소규모 그룹으로 나눠서 집계 작성자별로 글 수?
SELECT memberId, COUNT(ID) COUNT FROM NOTICE GROUP BY memberId;
->격자로 ID와 집계한 갯수로 제공해준다.

그런데 중간에 TITLE같은거 넣을 수 있나?
집계는 집계된 단위가 한단위로 표시된것이니 집계에 포함안된것은 포함하면안된다.
오류발생함!

집계된 값을 기준으로 내림차순 해보자
SELECT memberId, COUNT(ID) COUNT FROM NOTICE GROUP BY memberId ORDER BY COUNT DESC;

작성순서는 SELECT FROM WHERE GROUP BY HAVING ORDER BY이다.
그렇지만 실행순서는 다음과 같다.
SELECT가 가장먼저 작성되지만 FROM이 가장먼저 실행된다.
FROM절 -> CONNECT BY -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
그래서 FROM절에서 결정지은 별칭은 모두 사용가능하고
SELECT절에서 결정지은 별칭은 ORDER BY만 가능하다.

31. HAVING 절

회원별 게시글 수를 조회하시오 단 게시글이 2이하인 목록만 출력

SELECT memberId, COUNT(ID) FROM NOTICE WHERE COUNT(ID) < 2 GROUP BY memberId;
-> 이렇게 하면안된다. GROUP BY로 집계햇기때문에 WHERE절에서 집계함수를 사용할 수 없다.

SELECT memberId, COUNT(ID) FROM NOTICE GROUP BY memberId HAVING COUNT <=2 ;

33 부조회 서브쿼리

(오라클기준 예시)
순서를 바꾸어야하는 경우가 있다.
이럴때 사용하는 것이 서브쿼리 이다.

SELECT * FROM MEMBER WHERE ROWNUM BETWEEN 1 AND 10
->최신등록순으로 정렬한 결과에서 상위 열명을 원하는 경우라면?
SELECT * FROM MEMBER /ORDER BY REGDATE DESC /WHERE ROWNUM BETWEEN 1 AND 10 -> 순서때문에 오류발생

서브쿼리를 사용하는 방법으로는 여러가지가 있다.
FROM절에는 반드시 테이블이 와야하는게 아니다.
격자형데이터가 있다면 다 올 수 있다.
그래서 먼저 정렬을 한 격자형데이터를 만들고 그것으로 WHERE절을 넣자.

혼자 확인 REGDATE로 정렬후 -> 히트수가 0개인 것 서브쿼리
SELECT * FROM (SELECT * FROM NOTICE order by REGDATE DESC) A WHERE HIT = 0;

여기서 서브쿼리에 별칭(alias)을 안달아주면 에러(error : 1248) 가 난다.
MySql FROM 절의 모든 테이블에는 이름이 있어야 한다고 한다. from절에서 서브쿼리를 사용한다면 꼭 이름을 달아주자.

2023.03.09

오라클과 MySQL은 같으면서도 다른점이 너무 많다.
서브쿼리와 같은 경우 많이 사용하기 때문에 사용방법을 잘 이해할 필요가 있다.
MySQL은 오라클과는 다르게 from절의 모든 테이블에 별칭을 지어줘야한다. 이런 부분이 나중에 사용할때 많이 헷갈릴 거같으니 잘 기억해두자.
함수와 같은 경우는 정말 많은데 sql문을 작성하면서 필요한 내용들을 하나씩 찾아가며 하는 것이 맞을 것 같다.