공부 일지 #17 | MySQL: Update, Delete, 상관쿼리 → JOIN

학습 날짜: 2025.08.05

---

 

1. Update

• 테이블 내 기존 레코드의 값을 수정할 때 사용하는 명령어
• 하나의 컬럼 또는 여러 컬럼 동시에 수정 가능
• WHERE 절이 없을 경우, 모든 레코드의 값이 수정되므로 매우 주의해야 함

-- 실습 진행을 위해 grade 칼럼 추가 / alter: 변경 기능
alter table customers add column grade varchar(50);

-- 문제1. 총 주문 금액이 30만원 이상인 고객을 vip로 업데이트 하기
SET SQL_SAFE_UPDATES = 0; -- safe mode on for update

update customers
set grade = 'VIP'
where customer_id in 
	(select customer_id 
		from orders 
        group by customer_id 
        having sum(amount) >= 300000);

-- 문제 2. 가장 최근에 주문한 고객의 grade를 “Active”로 지정
update customers
set grade = 'Active'
WHERE customer_id in (
	select distinct o.customer_id 
	from orders o
	WHERE o.order_date = (select max(order_date) from orders)
);

-- 문제 3. 서울 고객 중 첫 주문이 8월 1일인 고객은 “EarlyBird”로 설정
UPDATE customers
SET grade = 'EarlyBird'
WHERE city = '서울' AND 
	  customer_id IN (SELECT customer_id 
					  FROM orders 
                      GROUP BY customer_id 
                      HAVING MIN(order_date) = '2025-08-01'
);

2. Delete

• DELETE FROM 테이블_이름 WHERE 조건
• 특정 조건을 만족하는 레코드를 삭제할 때 사용하는 명령어
• 역시 WHERE 절이 없으면 모든 행이 삭제되므로 반드시 조건을 명시할 것

-- 문제 4. 주문 기록이 없는 고객을 삭제하시오.
DELETE FROM customers 
WHERE customer_id NOT IN 
	(SELECT DISTINCT customer_id
	 FROM orders
);

-- 문제 5. 평균 주문 금액이 80,000원 이하인 고객의 주문만 삭제
DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM (
        SELECT customer_id
        FROM orders
        GROUP BY customer_id
        HAVING AVG(amount) <= 80000
    ) AS sub
); -- 삭제 대상 테이블을 서브쿼리 테이블로 못쓰기 때문에 from 에 서브쿼리를 추가하고 별칭 설정.
     
SET SQL_SAFE_UPDATES = 1; -- safe mode off

 

3. 상관쿼리 ( → JOIN )

• 상관쿼리(correlated query): nested for loop
  • 서브쿼리가 메인쿼리의 컬럼값을 참조해서 행마다 서브쿼리를 수행하는 구조
  • 단점: 너무 많은 반복이 생길 수 있음
• SELECT *
  FROM main_table A
  WHERE 비교... IN(
  SELECT ...
      FROM sub_table B
      WHERE B.column = A.column
  );
  • where절에 A.column은 메인 쿼리의 것. 메인의 값을 갖고 행마다 비교
• data가 많을수록 상관쿼리보다는 JOIN이 더 효과적!

-- 상관쿼리를 JOIN을 이용한 쿼리로 바꾸기
-- 문제 1. 각 고객의 최고 주문 금액을 출력하시오.
-- sub query
SELECT order_id, customer_id, amount
FROM orders o1
WHERE amount = (
	SELECT MAX(amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);
-- join; data가 많을 수록 이게 더 효과적
SELECT o.order_id, o.customer_id, o.amount
FROM orders o
JOIN (
	SELECT customer_id, MAX(amount) AS max_amount
    FROM orders
    GROUP BY customer_id
) sub
ON o.customer_id = sub.customer_id AND o.amount = sub.max_amount;


-- 문제 2. 각 고객의 첫 주문(가장 오래된 주문일)을 출력하시오.
SELECT order_id, customer_id, order_date
FROM orders o1
WHERE order_date = (
    SELECT MIN(order_date)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

-- join
SELECT o.order_id, o.customer_id, o.order_date
FROM orders o
JOIN (
	SELECT customer_id, MIN(order_date) as min_date
	FROM orders
    GROUP BY customer_id
    ) m
ON o.customer_id = m.customer_id AND o.order_date = m.min_date
ORDER BY o.customer_id;


-- 문제 3. 주문 금액이 고객별 평균보다 높은 주문만 출력하시오.
SELECT order_id, customer_id, amount
FROM orders o1
WHERE amount > (
    SELECT AVG(amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

SELECT o.order_id, o.customer_id, o.amount
FROM orders o
JOIN (
	SELECT customer_id, AVG(amount) as avg_amount
    FROM orders
    GROUP BY customer_id
) sub
ON o.customer_id = sub.customer_id and o.amount > sub.avg_amount;


-- 문제 4. 각 고객이 주문한 금액 중 가장 낮은 주문을 출력하시오.
SELECT order_id, customer_id, amount
FROM orders o1
WHERE amount = (
    SELECT MIN(amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

SELECT o.order_id, o.customer_id, o.amount
FROM orders o
JOIN (
	SELECT customer_id, MIN(amount) as min_amount
	FROM orders
    GROUP BY customer_id
) sub
ON o.customer_id = sub.customer_id and o.amount = sub.min_amount
ORDER BY customer_id;


-- 문제 5. 각 고객이 며칠에 첫 주문을 했는지 출력하시오 (고객 이름 포함)
SELECT c.name, (
    SELECT MIN(order_date)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) AS first_order_dates
FROM customers c;

SELECT c.name, sub.min_date
FROM customers c
JOIN(
	SELECT customer_id, Min(order_date) as min_date
    FROM orders
    GROUP BY customer_id
) sub
ON c.customer_id = sub.customer_id;