[TIL] 240216

Geehyun(장지현)·2024년 2월 16일
목록 보기
33/70
Today

SQL 프로그래밍
MariaDB 서브쿼리 수업 및 실습
MariaDB GROUP BY 수업 및 실습
# 2024-02-16

-- DELETE문 안에서 서브쿼리
SELECT * FROM tbl_orderinfo;

DELETE
FROM tbl_orderinfo
WHERE to_char(regDate, 'YYYY-MM-DD') IN
(
		SELECT to_char(regDate, 'YYYY-MM-DD') FROM tbl_orderinfo WHERE regDate < '2023-01-27 00:00:00'
);

-- 실습1
/*
주문 금액이 5,000원과 10,000원 사이의 주문 내역만 삭제하는 쿼리를 서브쿼리를 이용해 작성하세요.
*/

DELETE
FROM tbl_orderinfo
WHERE orderAmount IN (
		SELECT orderAmount FROM tbl_orderinfo WHERE orderAmount BETWEEN 5000 AND 10000
);

-- INSERT문 안에서 서브쿼리 
/*
사용법 ① : 필요한 컬럼만 갖고오기
INSERT INTO 테이블명(컬러명,,,)
SELECT 컬럼명,,, FROM 테이블명

사용법 ② : 컬럼구조가 전부 동일할 경우 
INSERT INTO 테이블명
SELECT * FROM 테이블명

◆ 대상 테이블과 복사해올 테이블의 컬럼 구조가 똑같다면 ALL/* 으로 사용해도됨
◆ AUTO_INCREMENT 속성의 컬럼을 사용할 떄는 복사해오기전에는 잠깐 빼고 복사 다하고 와서 추가해줘야 에러가 안남
*/

# 실습용 테이블 생성
CREATE TABLE tbl_customer (
		custid VARCHAR(10) NOT NULL,
		custnm VARCHAR(20) NOT NULL,
		PRIMARY KEY (custid) USING BTREE
)
COLLATE = 'utf8mb4_general_ci'
ENGINE = INNODB
;

# INSERT 내에서 서브쿼리 써보기

INSERT INTO tbl_customer (custid, custnm)
SELECT memberID, NAME FROM tbl_member;

-- 실습2
CREATE TABLE tbl_customer_test (
		custid VARCHAR(10) NOT NULL,
		custnm VARCHAR(20) NOT NULL,
		PRIMARY KEY (custid) USING BTREE
)
COLLATE = 'utf8mb4_general_ci'
ENGINE = INNODB
;

INSERT INTO tbl_customer_test (custid, custnm)
SELECT memberId, NAME FROM tbl_member;

SELECT * FROM tbl_customer_test;

-- UNION절 : 각 각 다른 테이블의 컬럼에서 자료형이 같을 때 해당 컬럼들을 합쳐서 조회할 수 있습니다.
/*
사용법(기본)
SELECT 컬럼,,,, FROM 테이블1
UNION
SELECT 컬럼,,,, FROM 테이블2

◆ 테이블1의 컬럼 + 테이블2의 컬럼으로 나옵니다.
◆ UNION할 컬럼은 서로 자료형이 같아야합니다.
◆ UNION : UNION한 컬럼에 대해 중복된 값은 1번만 출력합니다.
   UNION ALL : UNION한 컬럼에 대해 중복된 값도 다 나옵니다.
*/

-- 실습 : UPDATE문 실습
# UPDATE문의 경우 다른 INSERT, DELETE문 등과 달리 한땀한땀 한개씩 수작업 하는 경우가 많음!
SELECT * FROM tbl_member;

UPDATE tbl_member
SET memberId = 'memberId', NAME = '이름'
WHERE memberId = 'lim';

UPDATE tbl_member
SET memberId = 'member1', NAME = '회원1'
WHERE memberId = 'park';

UPDATE tbl_customer
SET custid = 'park', custnm = '박문수'
WHERE custid = 'member1';

UPDATE tbl_customer
SET custid = 'lim', custnm = '임꺽정'
WHERE custid = 'memberId';

-- 실습 : UNION 실습
# 기본 UNION
SELECT custid, custnm FROM tbl_customer
UNION
SELECT memberId, NAME FROM tbl_member
ORDER BY custnm             # 기준테이블(위 테이블) 컬럼기준으로 정렬해줘야함.
;

# UNION DISTINCT = 기본 UNION절과 같음
SELECT custid, custnm FROM tbl_customer
UNION DISTINCT
SELECT memberId, NAME FROM tbl_member
ORDER BY 1                  # 컬럼의 순서를 숫자로 사용할 수 있음! 단, 실제로는 컬럼을 명시적으로 사용하는게 옳음.          
;

# UNION ALL
SELECT custid, custnm FROM tbl_customer
UNION ALL
SELECT memberId, NAME FROM tbl_member
ORDER BY custnm     
LIMIT 5       
;

-- 실습
/*
회원테이블과 고객테이블에서 아이디가 중복되지 않는
회원의 아이디와 이름을 가져오는 쿼리를 UNION을 이용하여 작성하세요.
*/

# 지현답 (정답!)
SELECT custid, custnm FROM tbl_customer 
WHERE custid NOT IN (
		SELECT memberId FROM tbl_member
)
UNION
SELECT memberId, NAME FROM tbl_member 
WHERE memberId NOT IN (
		SELECT custid FROM tbl_customer
)
;

-- 실습2 : JOIN + 서브쿼리로 위 문제 풀시오 (난 못품 ㅎㅎ)
SELECT memberId, NAME FROM tbl_member AS M
WHERE memberId NOT IN (
		SELECT custid FROM tbl_customer AS C WHERE C.custid = M.memberId
)
UNION
SELECT custid, custnm FROM tbl_customer AS C
WHERE custid NOT IN (
		SELECT memberId FROM tbl_member AS M WHERE M.memberId = C.custid
);

-- EXCEPT절 : 앞 테이블에서 뒤 테이블 데이터를 제외한 데이터를 가져옵니다.
/*
사용법(기본)
SELECT 컬럼,,,, FROM 테이블1
EXCEPT
SELECT 컬럼,,,, FROM 테이블2

◆ 테이블1의 컬럼 - 테이블2의 컬럼으로 나옵니다.
   따라서 테이블1에는 없고 테이블2에만 있는 데이터는 애초에 나오지 않습니다.
◆ EXCEPT할 컬럼은 서로 자료형이 같아야합니다.
◆ EXCEPT절은 MySQL에서는 지원하지 않는다.
*/

SELECT custid, custnm FROM tbl_customer
EXCEPT
SELECT memberId, NAME FROM tbl_member
;

-- 실습
/*
tbl_member에 있는 주문정보 중에 tbl_customer에는 없는 
회원정보를 가져오는 쿼리를 작성하십시오
*/

SELECT * FROM tbl_member;

SELECT memberId, NAME, pwd, jumin, addr1, addr2, birthday, jobCode, mileage,   memberState, regDate
FROM tbl_member
WHERE memberID IN (
		SELECT memberId FROM tbl_member
		EXCEPT
		SELECT custid FROM tbl_customer
);

-- 실습2 (위 문제를 INNER JOIN / OUTER JOIN 두개로 변형해서 작성하시오)
# INNER JOIN
SELECT MB.memberId, MB.NAME, MB.pwd, MB.jumin, MB.addr1, MB.addr2, MB.birthday, MB.jobCode, MB.mileage, MB.memberState, MB.regDate
FROM tbl_member AS MB
INNER JOIN (
		SELECT memberId FROM tbl_member
		EXCEPT
		SELECT custid FROM tbl_customer
) AS A ON A.memberId = MB.memberId;

# OUTER JOIN
SELECT MB.memberId, MB.NAME, MB.pwd, MB.jumin, MB.addr1, MB.addr2, MB.birthday, MB.jobCode, MB.mileage, MB.memberState, MB.regDate
FROM tbl_member AS MB
RIGHT OUTER JOIN (
		SELECT memberId FROM tbl_member
		EXCEPT
		SELECT custid FROM tbl_customer
) AS A ON A.memberId = MB.memberId;

-- INTERSECT절 : 앞 테이블에서 뒤 테이블 서로 겹치는 값만 가져옵니다.
/*
사용법(기본)
SELECT 컬럼,,,, FROM 테이블1
INTERSECT
SELECT 컬럼,,,, FROM 테이블2

◆ 테이블1의 컬럼 ∩ 테이블2의 컬럼으로 나옵니다.
   서로 교집합인 데이터만 나옴!
◆ INTERSECT할 컬럼은 서로 자료형이 같아야합니다.
◆ INTERSECT절은 MySQL에서는 지원하지 않는다.
*/
SELECT memberId, NAME FROM tbl_member
INTERSECT
SELECT custid, custnm FROM tbl_customer
;

-- 실습1 : UNION + 서브쿼리 이용해서 위 결과와 똑같이 나오게 하시오
SELECT memberId, NAME FROM tbl_member 
WHERE memberId IN (
		SELECT custid FROM tbl_customer
)
UNION
SELECT custid, custnm FROM tbl_customer 
WHERE custid IN (
		SELECT memberId FROM tbl_member
)
;

-- 실습2 : 그룹바이 + UNION
SELECT memberId, NAME
FROM tbl_member
GROUP BY memberId
HAVING memberId IN (SELECT custid FROM tbl_customer)
UNION
SELECT custid, custnm
FROM tbl_customer
GROUP BY custid
HAVING custid IN (SELECT memberId FROM tbl_member)
;

SELECT *
FROM (
		SELECT memberId, name
		FROM tbl_member
		UNION ALL
		SELECT custid, custnm
		FROM tbl_customer
) AS A
GROUP BY A.memberId
HAVING COUNT(memberId) > 1;         # Group By는 집계함수를 사용하기 위해 쓰므로 그룹핑한것의 개수를 활용해야한다.

-- GROUP BY : 컬럼의 데이터를 기준으로 그룹핑을 해서 그룹핑한 데이터 기준으로 집계함수를 사용할 떄 이용합니다.
/*
[자주 쓰는 집계 함수]
- SUM(열) : (그룹 별)데이터 다 더한 값
- MAX(열) : (그룹 별)데이터 최대 값
- MIN(열) : (그룹 별)대이터 최소 값
- AVG(열) : (그룹 별)데이터 평균 값
- COUNT(열) : (그룹 별)데이터 개수(행개수)
◆ GROUP BY로 각 데이터 묶은 그룹에 대해 해당 집계함수를 적용한다고 보면 됨
◆ GROUP BY로 안 묶고 사용하면 그냥 냅다 해당 컬럼 총 집계 되므로 주의요망
   GROUP BY로 묶을 때는 SELECT 절에 있는 집계함수가 아닌 컬럼으로 사용해야 함.

[연산함수(계산할 컬럼) OVER(PARTITION BY 컬럼)]
◆ 컬럼기준으로 같은 데이터끼리 연산해주는 절
◆ 다만, 해당 데이터 행 갯수만큼 똑같이 나온다는 특징이 있음
◆ GROUP BY 처럼 쓸려면 DISTICT로 같은 데이터를 하나만 표시해주는 식으로 응용해야 함.
*/

# GROUP BY 이용 안한 버전
SELECT
memberID, orderAmount
FROM tbl_orderinfo
;
SELECT memberId,
SUM(orderAmount) OVER (PARTITION BY memberId) AS total
FROM tbl_orderinfo
ORDER BY memberId; 

# DISTINCT 사용 시 GROUP BY 사용할 때랑 결과는 같게 해줄 수 있음.
SELECT DISTINCT memberId,
SUM(orderAmount) OVER (PARTITION BY memberId) AS total
FROM tbl_orderinfo
ORDER BY memberId; 

# Group By 사용
SELECT
memberID, SUM(orderAmount)
FROM tbl_orderinfo
GROUP BY memberId
;

-- group by VS partition by 비교
SELECT 
		memberId
		, SUM(orderAmount)
		, AVG(orderAmount)
FROM tbl_orderinfo
GROUP BY memberID
;
SELECT
		memberId
		, SUM(orderAmount) OVER (PARTITION BY memberId) AS total
		, AVG(orderAmount) OVER (PARTITION BY memberId) AS total_AVG
FROM tbl_orderinfo
;

-- 실습
/*
각각의 테이블에서
tbl_orderinfo
tbl_orderdetail
 
1. 주문 번호별 주문 합계 금액 tbl_orderinfo / tbl_orderdetail
2. 상품 코드별 매출 합계 금액 tbl_orderdetail
3. 회원별 매출 합계 금액 tbl_orderinfo / tbl_orderdetail / tbl_member    orderAmoun, Amount 합계
4. 주문아이디가 주문정보 테이블에 없는 매출 합계금액 tbl_orderinfo / tbl_orderdetail
*/

# 1번
SELECT orderNo, sum(orderAmount)
FROM tbl_orderinfo
GROUP BY orderNo
;
SELECT orderNo, sum(amount)
FROM tbl_orderdetail
GROUP BY orderNo
;

# 2번
SELECT goodsCode, sum(amount)
FROM tbl_orderdetail
GROUP BY goodsCode
;

# 3번
SELECT
		MB.memberId, MB.name, 
		SUM(OI.orderAmount) AS OI_orderAmount, SUM(OD.amount) AS OD_amount
FROM tbl_member AS MB
INNER JOIN tbl_orderinfo AS OI ON MB.memberId = OI.memberId
INNER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
GROUP BY MB.memberId, OI.orderNo
;
/*
★ 문제 발생★
이렇게 하면 우리 데이터 정합성 문제로 orderInfo의 orderAmount가 Orderdetail의 OrderNo 행 개수만큼 반복됨.
따라서 member1 OrderInfo 기준 30,000원 + 1,000원 = 31,000원이어야함 근데, 1,000원의 OrderNo가 Orderdetail에서 2개가 반복되어
총 30,000 + 1,000 + 1,000(중복) = 32,000원이 나옴
*/

# 선생님 추가 답안
# 위 3번에서 orderInfo의 orderAmount 값의 중복문제로 해당 문제 해결을 위한 선생님 답안
SELECT memberId, SUM(orderAmount) AS orderAmount, SUM(amount) AS amount
FROM (
		SELECT memberId, orderNo, SUM(orderAmount) AS orderAmount
		FROM tbl_orderinfo
		GROUP BY memberId, orderNo
) AS A
INNER JOIN
(
		SELECT orderNo, SUM(amount) AS amount
		FROM tbl_orderdetail
		GROUP BY orderNo
) AS B
ON B.orderNo = A.orderNo
GROUP BY memberId;

# 4번
SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;

SELECT 
		OD.orderNo, SUM(OD.amount) AS OD_amount
FROM tbl_orderinfo AS OI
RIGHT OUTER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
WHERE OI.orderNo IS NULL
GROUP BY OD.orderNo
;

-- MIN() / MAX()
# 선생님 TIP : 이걸로 쓰는 것 보다 추후 나올 피벗테이블로 하는게 더 정확함
SELECT orderNo, MAX(orderAmount) AS maxOrderAmount, MIN(orderAmount) AS minOrderAmount
FROM tbl_orderinfo
GROUP BY orderNo
;

SELECT  orderNo, MAX(amount) AS maxAmount, MIN(amount) AS minAmount
FROM tbl_orderdetail
GROUP BY orderNo
;

-- AVG()
# memberId 기준으로 구하고 싶을 때
SELECT memberId, AVG(orderAmount), MAX(orderAmount), MIN(orderAmount)
FROM tbl_orderinfo
GROUP BY memberId;

# memberId, OrderNo 기준으로 구하고 싶을 때
SELECT memberId, OrderNo, AVG(orderAmount), MAX(orderAmount), MIN(orderAmount)
FROM tbl_orderinfo
GROUP BY memberId, OrderNo;

-- COUNT()
-- 실습
/*
1. 날짜별 매출 합계, 평균, 최대, 최소 정보를 출력하는 쿼리를 작성하세요.
   1-1. GROUP BY
   1-2. OVER(PARTITION BY)
2. cast(), CONVERT()를 사용하여 똑같이 작성하시오
*/

# 1-1. GROUP BY
SELECT 
		to_char(orderDate,'YYYY-MM-DD') AS Order_date
		, SUM(orderAmount) AS total_SUM
		, AVG(orderAmount) AS total_AVG
		, MAX(orderAmount) AS MAX_AMOUNT
		, MIN(orderAmount) AS MIN_AMOUNT
FROM tbl_orderinfo
GROUP BY to_char(orderDate,'YYYY-MM-DD')     # 시간은 포함하지 않고 날짜만으로 구분하기 위해 to_char()로 연-월-일로 변환 후 그룹핑 함.
;

# 1-2. OVER(PARTITION BY)
SELECT DISTINCT                              # DISTINCT 안해도되는데 그냥 깔끔하게 보기 위해 함.
		to_char(orderDate,'YYYY-MM-DD') AS Order_date
		, SUM(orderAmount) OVER(PARTITION BY to_char(orderDate,'YYYY-MM-DD')) AS total_SUM
		, AVG(orderAmount) OVER(PARTITION BY to_char(orderDate,'YYYY-MM-DD')) AS total_AVG
		, MAX(orderAmount) OVER(PARTITION BY to_char(orderDate,'YYYY-MM-DD')) AS MAX_AMOUNT
		, MIN(orderAmount) OVER(PARTITION BY to_char(orderDate,'YYYY-MM-DD')) AS MIN_AMOUNT
FROM tbl_orderinfo
;

# 2-1 CAST()
SELECT 
		CAST(orderDate as VARCHAR(10)) AS Order_date
		, SUM(orderAmount) AS total_SUM
		, AVG(orderAmount) AS total_AVG
		, MAX(orderAmount) AS MAX_AMOUNT
		, MIN(orderAmount) AS MIN_AMOUNT
FROM tbl_orderinfo
GROUP BY CAST(orderDate as VARCHAR(10))     # 시간은 포함하지 않고 날짜만으로 구분하기 위해 to_char()로 연-월-일로 변환 후 그룹핑 함.
;
SELECT DISTINCT
		CAST(orderDate as VARCHAR(10)) AS Order_date
		, SUM(orderAmount) OVER(PARTITION BY CAST(orderDate as VARCHAR(10))) AS total_SUM
		, AVG(orderAmount) OVER(PARTITION BY CAST(orderDate as VARCHAR(10))) AS total_AVG
		, MAX(orderAmount) OVER(PARTITION BY CAST(orderDate as VARCHAR(10))) AS MAX_AMOUNT
		, MIN(orderAmount) OVER(PARTITION BY CAST(orderDate as VARCHAR(10))) AS MIN_AMOUNT
FROM tbl_orderinfo
;

# 2-2 CONVERT()
SELECT 
		CONVERT(orderDate, VARCHAR(10)) AS Order_date
		, SUM(orderAmount) AS total_SUM
		, AVG(orderAmount) AS total_AVG
		, MAX(orderAmount) AS MAX_AMOUNT
		, MIN(orderAmount) AS MIN_AMOUNT
FROM tbl_orderinfo
GROUP BY CONVERT(orderDate, VARCHAR(10))     # 시간은 포함하지 않고 날짜만으로 구분하기 위해 to_char()로 연-월-일로 변환 후 그룹핑 함.
;
SELECT DISTINCT
		CONVERT(orderDate, VARCHAR(10)) AS Order_date
		, SUM(orderAmount) OVER(PARTITION BY CONVERT(orderDate, VARCHAR(10))) AS total_SUM
		, AVG(orderAmount) OVER(PARTITION BY CONVERT(orderDate, VARCHAR(10))) AS total_AVG
		, MAX(orderAmount) OVER(PARTITION BY CONVERT(orderDate, VARCHAR(10))) AS MAX_AMOUNT
		, MIN(orderAmount) OVER(PARTITION BY CONVERT(orderDate, VARCHAR(10))) AS MIN_AMOUNT
FROM tbl_orderinfo
;
Review

평균 80점까지 달성 완료!
TO DO

정처기 기출문제 풀기!!! 하루전!!
=> 2/17 필기 합격!!! 이제 실기 준비해야합니다!
Geehyun(장지현)
블로그 이전 했습니다. 아래 블로그 아이콘(🏠) 눌러서 놀러오세요
[TIL] 240216

TIL

Today

Review

TO DO

[TIL] 240215

[TIL] 240219

0개의 댓글
