SELECT
L.login_id
, M.m_email
, L.login_date
FROM
tb_login AS L
INNER JOIN
tb_member AS M
ON
L.login_id = M.m_id
GROUP BY L.login_id
HAVING
MONTH(L.login_date) = 2;
/* GORUP BY로 그룹으로 묶으면 딱 하나의 튜플만 조회 된다.
=> 2월 기록이 있어도 3월 기록이 있으면 3월 기록만 남음
그 후에 2월 기록을 찾는 것이기 때문에 2월에 로그인한 회원을 찾을 수 없음 */
=======================================
SELECT
L.login_id
, M.m_email
, L.login_date
FROM
tb_login AS L
INNER JOIN
tb_member AS M
ON
L.login_id = M.m_id
WHERE
MONTH(L.login_date) = 2
GROUP BY L.login_id;
/* WHERE로 먼저 2월 로그인 기록을 찾고 그룹으로 묶으면 조회가 된다.
WHERE와 GROUP BY를 함께 쓸 수 없다는 것은 GROUP BY로 묶고 그 안에서 조건문을 쓸 때 불가능하다는 듯
WHERE와 GROUP BY를 별개로 사용하면 가능하다.*/
- LAST_DAY('날짜') : 해당 날짜가 속한 달의 가장 마지막 날짜
SELECT
L.login_id
, M.m_email
, L.login_date
FROM
tb_login AS L
INNER JOIN
tb_member AS M
ON
L.login_id = M.m_id
WHERE
L.login_date BETWEEN '2023-02-01' AND LAST_DAY('2023-02-01')
GROUP BY L.login_id;
[ 데이터 형 변환 함수 ] 명시적 형변환 : 내가 직접 함수를 사용해서 변환 - CAST(갑 AS 데이터_형식[(길이)]) / CONVERT(값, 데이터_형식[(길이)])
[ NULL 관련 함수 ]
[ 기타 : 시스템 함수 ] 시스템 정보 함수 시스템의 정보를 출력하는 함수를 제공한다 - USER() : 사용자 아이디 / root@localhost 출력 - DATABASE() : 데이터베이스 이름 / ksmart47db_basic 출력 - VERSION() : MySQL 버전 출력 - SLEEP(초) : 의도적으로 쿼리 실행을 멈춘다.
[[ WINDOW FUNCTION ]]
MySQL 8버전 이상에서 지원 -> 낮은 버전의 개발 환경에서는 사용할 수 없음 GROUP BY와 비슷한 역할 / 집계 함수 대부분 사용 가능 + 윈도우 함수에서만 지원하는 기능 사용 가능 행과 행을 비교 / 행과 행에 대한 연산 - OVER(PARTITION BY ORDER BY) - OVER(PARTITION BY) - OVER(ORDER BY) * PARTITION BY가 ORDER BY 보다 앞에 나와야함. * PARTITION BY와 ORDER BY가 둘 다 있어야하는 것은 아니다.
함수(컬럼) OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명) - 함수 : 집계 함수 , 순위 함수 .... - (컬럼) : 집계 대상 열 이름 작성 - PARTITION BY 컬럼명 : 어떤 컬럼을 그룹으로 만들지 기준이 되는 컬럼을 작성한다. (GROUP BY와 비슷) - ORDER BY 컬럼명 : 데이터 정렬
[ 집계 함수 ] SELECT 함수() OVER(PARTITION BY 컬럼명 ORDER BY 컬럼명 내림차순 또는 오름차순) FROM 테이블_이름;
SELECT
c.Name
, c.CountryCode
, c.Population
, MAX(c.Population) AS 최대인구
FROM
city AS c
GROUP BY c.CountryCode;
-- GROUP BY : 내용을 축약해서 보여준다.
SELECT
c.Name
, c.CountryCode
, c.Population
, MAX(c.Population) OVER(PARTITION BY c.CountryCode) AS 최대인구
FROM
city AS c;
-- PARTITION BY : 축약하지 않고 전부 보여준다.
/*
GROUP BY를 사용하면 CountryCode가 같은 것을 묶어서
그 중 최댓값을 갖고 있는 하나의 튜플만 보여준다.
OVER(PARTITION BY)를 사용하면 CountryCode가 같은 것을 묶어서
그 중 최댓값을 같은 그룹 내 모든 튜플의 '최대인구' 컬럼에 넣어서 보여준다.
*/
GROUP BY 사용OVER(PARTITION BY) 사용
SUM에 ORDER BY를 사용하면, 누적합을 구할 수 있다.
SELECT
C.Name
, C.CountryCode
, C.Population
, SUM(C.Population) OVER(PARTITION BY C.CountryCode) AS 인구합계
FROM
city AS C;
/*
ORDER BY가 없을때
같은 CountryCode를 그룹으로 묶어서
같은 그룹 내 모든 튜플의 인구합계 컬럼에 합계를 넣어준다.
*/
SELECT
C.Name
, C.CountryCode
, C.Population
, SUM(C.Population) OVER(PARTITION BY C.CountryCode ORDER BY C.Name) AS 인구합계
FROM
city AS C;
/*
ORDER BY가 있을때
같은 CountryCode를 그룹으로 묶어서
같은 그룹 내 모든 튜플의 인구합계 컬럼에 차례대로 누적 합계를 넣어준다.
*/
ORDER BY 사용 X
ORDER BY 사용
조건 : g_seller_id를 그룹으로 묶어서 판매 금액 총 합이 3,000,000 초과 / g_seller_id와 g_price 출력
SELECT
g.g_seller_id
, g.g_price
FROM
tb_goods AS g
GROUP BY
g.g_seller_id
HAVING
SUM(g.g_price) > 3000000;
/*
GROUP BY / HAVING 단독으로 사용할 경우
g_seller_id의 판매 금액들 중 맨 위에 있는 튜플 딱 하나만 출력
*/
SELECT
g.g_seller_id
, g.g_price
FROM
tb_goods AS g
WHERE
g.g_seller_id
IN (
SELECT g_seller_id
FROM tb_goods
GROUP BY g_seller_id
HAVING SUM(g_price) > 3000000
)
ORDER BY g.g_seller_id;
/*
GROUP BY / HAVING 을 WHERE ~ IN ~ 절에 넣어 사용할 경우
g_seller_id의 판매 금액들 모두 출력
*/
GROUP BY / HAVING만 사용WHERE IN 절에 GROUP BY 사용
조건을 넣고 싶을 때 OVER(PARTITION BY) 와 GROUP BY 둘 다 WHERE에 직접적으로 사용 불가능 GROUP BY - HAVING 사용하면 된다. (딱 하나의 결과만 출력) 딱 하나의 결과만 출력하지 않고 여러 결과 출력하고 싶을때는 OVER 사용? - 안 됨 OVER랑 WHERE / HAVING 둘 다 못 쓰기 때문에 WHERE ~ IN (GROUP BY ~ HAVING) 사용한다.
[ 순위 ] 순위_함수() OVER(ORDER BY 컬럼_명) * 인자에 아무 값도 작성하지 않는다.
- ROW_NUMBER : 작은 순서부터 출력. 값이 커지는 순서에 따라 순위도 높아짐. 어떻게든 순서를 정해서 중복 순서 없음 - RANK : 값이 동일하면 같은 순위를 부여한다. - DENSE_RANK : 1이 3개 있을 때RANK는 2와 3 없이 4로 넘어가지만, DENSE_RANK는 넘기는 것 없이 2와 3 부여
SELECT
C.Name
, ROW_NUMBER() OVER(ORDER BY C.Population) AS row_num
, RANK() OVER(ORDER BY C.Population) AS 'rank' -- rank는 예약어이기 때문에 '' 필수
, DENSE_RANK() OVER(ORDER BY C.Population) AS dense
FROM
city AS C
WHERE
C.CountryCode ='KOR';
[ 사용자 정의 함수 ]
사용자가 직접 만드는 함수
- 다른 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있는데, 형태가 약간 다름
SET @변수_이름 := 개입값;
SET @num1 := 10;
SET @num2 := 20;
SELECT
@num1 AS '변수1'
, @num2 AS '변수2';
-- 변수1 = 10 / 변수2 = 20
LIMIT의 경우 변수를 사용할 수 없으나 PREPARE문과 EXCUTE문을 사용하여 변수를 사용할 수 있다.
/*
city 테이블 = c 에서 CountryCode가 KOR인 경우만
cityName 컬럼에 도시의 이름: 을 붙여서
Name, CountryCode 컬럼 출력
*/
SET @cityName := '도시의 이름:';
SET @num := 10;
SELECT
@cityName
, c.Name
, c.CountryCode
FROM
city AS c
WHERE
c.CountryCode = 'KOR'
LIMIT @num;
-- LIMIT @num 에서 오류 발생
PREPARE 쿼리_이름 FROM '쿼리문'; PREPARE - > 쿼리문을 준비하고, 실행은 하지 않는다.
EXECUTE 쿼리_이름 USING 변수명;
EXECUTE 쿼리_이름 -> EXECUTE 쿼리를 만나는 순간 실행, PREPARE 쿼리문의 ? 자리에 USING 변수명을 대입한다.
/*
city 테이블 = c 에서 CountryCode가 KOR인 경우만
cityName 컬럼에 도시의 이름: 을 붙여서
Name, CountryCode 컬럼 출력
*/
SET @cityName := '도시의 이름:';
SET @num := 10;
PREPARE queryTest
FROM
'SELECT
@cityName
, c.Name
, c.CountryCode
FROM
city AS c
LIMIT ?';
EXECUTE queryTest USING @num;
