[DB] 팀 프로젝트 3주차 작업 기록

이현경·2026년 5월 26일
db

Database

목록 보기
12/13

‘주문’ 주요 기능 SQL 작성하기

동작 시나리오: 주문

  • Admin은 고객의 배송상태를 변경할 수 있다.
  • 회원은 장바구니에 담은 물건을 주문할 수 있다.
    • 각 주문에 대해 주문일자, 배송지, 주문한 상품 총 결제 금액을 확인할 수 있다.
  • 회원은 자신이 주문했던 전체 내역을 조회해볼 수 있다.
    • '최근 6개월' 등 특정 기간을 설정하여 최신순으로 조회할 수 있다.
  • 회원은 자신의 특정 주문에 대해서 상태(배송중, 주문취소 등) 등을 상세 조회할 수 있다.
  • 회원은 언제든지 본인이 주문한 상품을 주문 취소할 수 있다.

회원 배송상태 변경

UPDATE order_item
SET status_id = {변경할 상태 ID}
WHERE order_item_id = {클릭한 주문상품 ID};

회원 주문 생성

INSERT INTO orders (member_id, order_date, shipping_address, total_price)
VALUES ({회원 ID}, NOW(), {입력한 배송지 주소}, {계산된 총 결제 금액});

-- 장바구니에서 선택한 상품의 개수만큼 반복
INSERT INTO order_item (order_id, product_id, status_id, quantity, order_price)
VALUES ({생성된 주문 ID}, {상품 ID}, 1, {선택 수량}, {주문시점 가격});

-- 장바구니 비우기
DELETE FROM cart WHERE cart_id = {주문한 장바구니ID};

자신의 주문 내역 특정 기간 최신순 조회

SELECT *
FROM orders
WHERE member_id = {회원 ID}
  AND order_date >= DATE_SUB(NOW(), INTERVAL {설정기간(ex. 6)} MONTH)
ORDER BY order_date DESC;

자신의 특정 주문 내역 조회

SELECT 
    o.order_id, o.order_date, o.shipping_address, o.total_price,
    p.product_name, oi.quantity, oi.order_price, os.status_name
FROM orders o
JOIN order_item oi ON o.order_id = oi.order_id
JOIN product p ON oi.product_id = p.product_id
JOIN order_status os ON oi.status_id = os.status_id
WHERE o.order_id = {주문 ID} AND o.member_id = {회원 ID};

회원의 주문 취소

UPDATE order_item
SET status_id = 5
WHERE order_item_id = {취소할 주문상품 ID};



매출통계 SQL 작성

시간별 매출 통계 조회

-- 특정 날짜 하루동안 시간에 따른 매출 통계 조회
SELECT 
    DATE_FORMAT(order_date, '%Y-%m-%d %H') AS od,
    SUM(total_price) AS revenue,
    COUNT(order_id) AS order_count
FROM orders
WHERE order_date BETWEEN '2026-05-23 00:00:00' AND '2026-05-23 23:59:59'
GROUP BY DATE_FORMAT(order_date, '%Y-%m-%d %H:00')
ORDER BY od ASC;

-- 누적 데이터로 시간대별 매출 통계 조회
SELECT 
    DATE_FORMAT(order_date, '%H') AS od,
    SUM(total_price) AS revenue,
    COUNT(order_id) AS order_count
FROM orders
WHERE order_date BETWEEN '2026-01-01 00:00:00' AND '2026-05-23 23:59:59'
GROUP BY DATE_FORMAT(order_date, '%H')
ORDER BY od ASC;


일별 매출 통계 조회

SELECT 
    DATE_FORMAT(order_date, '%Y-%m-%d') AS od,
    SUM(total_price) AS revenue,
    COUNT(order_id) AS order_count
FROM orders
WHERE order_date BETWEEN '2026-05-01 00:00:00' AND '2026-05-23 23:59:59'
GROUP BY DATE_FORMAT(order_date, '%Y-%m-%d')
ORDER BY od ASC;


월별 매출 통계 조회

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS od,
    SUM(total_price) AS revenue,
    COUNT(order_id) AS order_count
FROM orders
WHERE order_date BETWEEN '2025-01-01 00:00:00' AND '2026-05-31 23:59:59'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY od ASC;


연별 매출 통계 조회

SELECT 
    DATE_FORMAT(order_date, '%Y') AS od,
    SUM(total_price) AS revenue,
    COUNT(order_id) AS order_count
FROM orders
WHERE order_date BETWEEN '2020-01-01 00:00:00' AND '2026-12-31 23:59:59'
GROUP BY DATE_FORMAT(order_date, '%Y')
ORDER BY od ASC;



더미 데이터 넣어 테스트

주문

schema.sql

-- 주문상태 테이블
CREATE TABLE order_status (
		status_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		status_name VARCHAR(50) NOT NULL UNIQUE
);

-- 주문 테이블
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    member_id BIGINT NOT NULL,
    order_date DATETIME(6) NOT NULL,
    shipping_address VARCHAR(255) NOT NULL,
    total_price BIGINT NOT NULL,

    FOREIGN KEY (member_id)
        REFERENCES member(member_id)
        ON DELETE RESTRICT
);

CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_orders_member_id_order_date ON orders(member_id, order_date);

-- 주문목록 테이블
DROP TABLE IF EXISTS order_item;

CREATE TABLE order_item (
		order_item_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		order_id BIGINT NOT NULL,
		product_id BIGINT NOT NULL,
		status_id BIGINT NOT NULL,
		quantity BIGINT NOT NULL DEFAULT 1,
		order_price BIGINT NOT NULL,
		
		FOREIGN KEY (order_id)
				REFERENCES orders(order_id)
				ON DELETE CASCADE,
		FOREIGN KEY (product_id)
				REFERENCES product(product_id)
				ON DELETE RESTRICT,
		FOREIGN KEY (status_id)
				REFERENCES order_status(status_id)
				ON DELETE RESTRICT
);

data.sql

-- [주문상태] 데이터 (고정)
INSERT INTO order_status (status_name)
VALUES ('주문접수'),
       ('결제완료'),
       ('배송중'),
       ('배송완료'),
       ('주문취소');

-- [주문] 더미 데이터
INSERT INTO orders (member_id, order_date, shipping_address, total_price)
VALUES (1, '2026-05-10 10:30:00', '서울 강남구 테헤란로 123 101호', 239000),  -- 1번 주문
       (2, '2026-05-12 14:15:22', '부산 해운대구 해운대로 789 303호', 109700), -- 2번 주문
       (1, '2026-05-15 09:00:11', '서울 송파구 올림픽로 456 202호', 59700),   -- 3번 주문
       (4, '2026-05-18 18:45:50', '서울 마포구 홍익로 321 404호', 248000),   -- 4번 주문
       (2, '2026-05-20 11:20:30', '부산 해운대구 해운대로 789 303호', 124900); -- 5번 주문

-- [주문목록] 더미 데이터
INSERT INTO order_item (order_id, product_id, status_id, quantity, order_price)
VALUES
-- 1번 주문: 트위드 자켓(150,000) 1개 + 플리츠 스커트(89,000) 1개 = 총 239,000원(배송완료)
(1, 1, 4, 1, 150000),
(1, 2, 4, 1, 89000),

-- 2번 주문: 오버사이즈 티셔츠(29,900) 2개 + 슬림핏 청바지(49,900) 1개 = 총 109,700원(배송중)
(2, 4, 3, 2, 29900),
(2, 5, 3, 1, 49900),

-- 3번 주문: 히트텍 롱슬리브(19,900) 3개 = 총 59,700원(주문취소)
(3, 6, 5, 3, 19900),

-- 4번 주문: 드라이핏 반팔(45,000) 2개 + 트랙 재킷(89,000) 1개 + 조거 팬츠(69,000) 1개 = 총 248,000원(결제완료)
(4, 8, 2, 2, 45000),
(4, 9, 2, 1, 89000),
(4, 10, 2, 1, 69000),

-- 5번 주문: 리넨 블라우스(65,000) 1개 + 후리스 집업(59,900) 1개 = 총 124,900원(주문접수)
(5, 3, 1, 1, 65000),
(5, 7, 1, 1, 59900);

1. 조회

1) 특정 회원 특정 주문 상세 조회

SELECT 
    o.order_id, o.order_date, o.shipping_address, o.total_price,
    p.product_name, oi.quantity, oi.order_price, os.status_name
FROM orders o
JOIN order_item oi ON o.order_id = oi.order_id
JOIN product p ON oi.product_id = p.product_id
JOIN order_status os ON oi.status_id = os.status_id
WHERE o.order_id = {주문 ID} AND o.member_id = {회원 ID};


2) 특정 회원의 주문 내역 전체 조회(특정 기간 이후)

SELECT *
FROM orders
WHERE member_id = {회원 ID}
  AND order_date >= DATE_SUB(NOW(), INTERVAL {설정기간} MONTH)
ORDER BY order_date DESC;


2. 삽입

1) 새로운 주문 생성

INSERT INTO orders (member_id, order_date, shipping_address, total_price)
VALUES ({회원 ID}, NOW(), {입력한 배송지 주소}, {계산된 총 결제 금액});

-- 장바구니에서 선택한 상품의 개수만큼 반복
INSERT INTO order_item (order_id, product_id, status_id, quantity, order_price)
VALUES ({생성된 주문 ID}, {상품 ID}, 1, {선택 수량}, {주문시점 가격});


3. 업데이트

1) 주문 상태 변경

UPDATE order_item
SET status_id = {변경할 상태 ID}
WHERE order_item_id = {클릭한 주문상품 ID};


2) 사용자가 특정 상품 주문 취소

UPDATE order_item
SET status_id = 5
WHERE order_item_id = {취소하려는 아이템 ID}
  AND order_id IN (SELECT order_id FROM orders WHERE member_id = {로그인한 회원 ID});
  • 마지막 order_id IN (SELECT order_id FROM orders WHERE member_id = {로그인한 회원 ID}); 이건 취소하려는 아이템이 진짜 이 회원이 주문한 아이템이 맞는지 검증하는 구문.


4. 삭제 → 우리 쇼핑몰은 주문 내역 삭제 x

1) 주문 이력이 있는 회원 삭제 시도


2) 이미 팔린 적이 있는 상품 삭제 시도


장바구니

schema.sql

-- 장바구니 테이블
DROP TABLE IF EXISTS cart;

CREATE TABLE cart (
		member_id BIGINT NOT NULL PRIMARY KEY,
		
		FOREIGN KEY (member_id)
				REFERENCES member(member_id)
				ON DELETE CASCADE
);

-- 장바구니 아이템 테이블
DROP TABLE IF EXISTS cart_item;

CREATE TABLE cart_item (
		cart_item_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		member_id BIGINT NOT NULL,
		product_detail_id BIGINT NOT NULL,
		quantity BIGINT NOT NULL DEFAULT 1,
		
		CONSTRAINT check_cart_quantity CHECK(quantity>=1),
		UNIQUE (member_id, product_detail_id),
		
		FOREIGN KEY (member_id)
				REFERENCES cart(member_id)
				ON DELETE CASCADE,
		FOREIGN KEY (product_detail_id)
				REFERENCES product_detail(product_detail_id)
				ON DELETE CASCADE
);

data.sql

-- [장바구니] 더미 데이터
INSERT INTO cart (cart_id, member_id)
VALUES (1, 1),
       (2, 2),
       (3, 3),
       (4, 4),
       (5, 5);

-- [장바구니 아이템] 더미 데이터
INSERT INTO cart_item (member_id, product_detail_id, quantity) VALUES
-- 1번 회원
(1, 1, 1),
(1, 3, 2),
(1, 14, 1),
(1, 21, 3),

-- 2번 회원
(2, 5, 1),
(2, 6, 1),
(2, 17, 2),
(2, 25, 1),
(2, 37, 1),

-- 3번 회원
(3, 9, 1),
(3, 10, 1),
(3, 23, 2),

-- 4번 회원
(4, 2, 1),
(4, 13, 2),
(4, 29, 1),
(4, 33, 1),
(4, 38, 1),

-- 5번 회원
(5, 11, 1),
(5, 18, 1),
(5, 31, 2);

1. 조회

1) 내 장바구니 목록 조회

  • 장바구니에서 볼 수 있는 항목
    • 구분용: cart_item_id
    • 상품 이름, 상품별 수량을 반영한 총가격, 상품 이미지, 상품 수량
    • 선택한 옵션: 색상, 크기
SELECT 
    ci.cart_item_id,
    p.product_name,
    pd.image_url,
    ci.quantity,
    (p.price + pd.surcharge) * ci.quantity AS total_price,
    MAX(CASE WHEN od.option_type_id = 1 THEN od.option_value END) AS size_option,
    MAX(CASE WHEN od.option_type_id = 2 THEN od.option_value END) AS color_option
FROM cart_item ci
JOIN product_detail pd ON ci.product_detail_id = pd.product_detail_id
JOIN product p ON pd.product_id = p.product_id
JOIN product_option po ON pd.product_detail_id = po.product_detail_id
JOIN option_detail od ON po.option_detail_id = od.option_detail_id
WHERE ci.member_id = {조회하려는 회원 ID}
GROUP BY ci.cart_item_id;
  • 그냥 테이블끼리 단순 조인하면 옵션이 2개이므로 하나의 상품인데 두 줄씩 출력되어버림.
    • CASE WHEN으로 각 줄에 하나의 옵션만 출력되고 다른 하나는 NULL이 출력되도록 바꿈.
    • cart_item_id를 기준으로 GROUP BY를 하고 MAX를 적용해주면 NULL을 무시하는 MAX()의 특성으로 이쁘게 하나의 상품에 대해 하나의 줄로 출력이 된다! (제미나이의 도움을 받음,, 똑똑한 자식)


2. 삽입

1) 장바구니에 새로운 상품 담기

INSERT INTO cart_item (member_id, product_detail_id, quantity)
VALUES ({회원 ID}, {담으려는 상품상세 ID}, {선택한 수량});


2) 이미 담긴 상품을 또 담으려고 할 때

  • 처음 설계: 장바구니에 상품이 이미 담겨져 있다면 중복 데이터로 취급해서 UNIQUE 제약조건을 걸어놨었음

    • 근데 시중의 앱을 다시 보니 그냥 똑같은 상품이 하나 더 추가됨 → 에러가 나는게 아님

    • 그래서 찾아보니 INSERT하다가 UNIQUE 제약조건에 걸리면 UPDATE하라고 할 수 있다고 함!

      INSERT INTO cart_item (member_id, product_detail_id, quantity)
VALUES ({회원 ID}, {상품상세 ID}, {추가할 수량}) AS new_data
ON DUPLICATE KEY UPDATE quantity = cart_item.quantity + new_data.quantity;


3. 업데이트

1) 장바구니 상품 수량 변경

  • 장바구니에 담겨 있는 상품의 수량만 변경할 때
UPDATE cart_item
SET quantity = {새로운 수량}
WHERE cart_item_id = {변경할 장바구니 아이템 ID};


2) 장바구니 상품 수량을 0개 이하로 수정 시도


4. 삭제

1) 장바구니에서 특정 상품 하나 삭제

DELETE FROM cart_item
WHERE cart_item_id = {삭제할 장바구니 아이템 ID};


2) 장바구니에 있는 모든 상품 주문 = 장바구니 내용 삭제

DELETE FROM cart_item
WHERE member_id = {회원 ID};


3) 장바구니에서 몇 개만 선택하여 주문

DELETE FROM cart_item
WHERE member_id = {회원 ID}
  AND cart_item_id IN ({선택한 장바구니 아이템 ID 1}, {선택한 장바구니 아이템 ID 2}, ...);



배송상태 업데이트 프로시저 구현

  • 배송상태: ‘주문접수 → 결제완료 → 배송중 → 배송완료’ or ‘주문취소’
    • 주문접수 후 1분이 지나면 자동으로 결제완료로 변경
    • 결제 완료 후 24시간이 지나면 자동으로 배송중으로 변경
    • 배송중에서 24시간이 지나면 자동으로 배송완료로 변경
  • 데이터베이스 알람시계 켜기
-- 이벤트 스케줄러 활성화
SET GLOBAL event_scheduler = ON;

-- 켜졌는지 확인하는 쿼리 -> ON으로 나와야 함
SHOW VARIABLES LIKE 'event_scheduler';
  • 자동 업데이트 프로시저 만들기
DELIMITER //

CREATE PROCEDURE AutoUpdateOrderStatus()
BEGIN
    -- 주문접수(1) -> 결제완료(2): 주문한 지 1분 경과
    UPDATE order_item oi
    JOIN orders o ON oi.order_id = o.order_id
    SET oi.status_id = 2
    WHERE oi.status_id = 1 AND o.order_date <= DATE_SUB(NOW(), INTERVAL 1 MINUTE);

    -- 결제완료(2) -> 배송중(3): 주문한 지 24시간 후
    UPDATE order_item oi
    JOIN orders o ON oi.order_id = o.order_id
    SET oi.status_id = 3
    WHERE oi.status_id = 2 AND o.order_date <= DATE_SUB(NOW(), INTERVAL 24 HOUR);

    -- 배송중(3) -> 배송완료(4): 주문한 지 48시간 후
    UPDATE order_item oi
    JOIN orders o ON oi.order_id = o.order_id
    SET oi.status_id = 4
    WHERE oi.status_id = 3 AND o.order_date <= DATE_SUB(NOW(), INTERVAL 48 HOUR);
END //

DELIMITER ;
  • 이벤트 생성하기
CREATE EVENT auto_update_order_status_event
ON SCHEDULE EVERY 1 MINUTE
DO CALL AutoUpdateOrderStatus();

⇒ 현재 설계에서는 상태 변경 시점을 추적하는 별도의 컬럼이 없어서 최초 결제일인 orders.order_date를 기준으로 INTERVAL을 각각 1분, 24시간, 48시간으로 누적 계산하여 상태를 업데이트하도록 했습니다.



재고 관리 및 주문 트랜잭션

주문 생성 과정

  1. 장바구니에서 선택한 상품 주문하기
  2. orders 테이블에 데이터 삽입
  3. order_item 테이블에 주문 목록 데이터 삽입
  4. cart_item 테이블에서 주문한 상품 데이터 삭제

재고 관리

  • 주문 생성까지 과정: 재고확인 → 재고 차감 → 주문 처리

이걸 이제 프로시저롤 만들면 프로시저 호출만 하면 돼서 편함! 아래 프로시저는 제미나이 만들어줬습니당.

  • 결제 총 금액 계산
  • 재고 확인 후 주문 생성하고 장바구니 비우기까지 모두 포함
  • 회원이 결제한 금액을 회원의 누적 구매 금액에도 반영
DELIMITER //

CREATE PROCEDURE CheckoutCartWithShipping(
    IN p_member_id BIGINT,
    IN p_shipping_address VARCHAR(255)
)
BEGIN
    -- 변수 선언
    DECLARE v_item_total BIGINT DEFAULT 0; -- 상품 총액
    DECLARE v_shipping_fee INT DEFAULT 0; -- 배송비
    DECLARE v_final_price BIGINT DEFAULT 0; -- 최종 결제액

    -- 에러 발생 시 롤백
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'FAIL: 데이터베이스 오류로 주문이 취소되었습니다.' AS result;
    END;

    START TRANSACTION;

    -- 결제하는 회원의 등급별 배송비 조회
    SELECT mg.shipping_fee INTO v_shipping_fee
    FROM member m
    JOIN member_grade mg ON m.grade_name = mg.grade_name
    WHERE m.member_id = p_member_id;

    -- 장바구니에 담긴 상품들의 순수 결제 금액 계산
    SELECT SUM((p.price + pd.surcharge) * ci.quantity) INTO v_item_total
    FROM cart_item ci
    JOIN product_detail pd ON ci.product_detail_id = pd.product_detail_id
    JOIN product p ON pd.product_id = p.product_id
    WHERE ci.member_id = p_member_id;

    -- 장바구니가 비어있다면 롤백
    IF v_item_total IS NULL THEN
        ROLLBACK;
        SELECT 'FAIL: 장바구니가 비어있습니다.' AS result;
    ELSE
        -- 최종 결제 금액 계산 (상품 총액 + 배송비)
        SET v_final_price = v_item_total + v_shipping_fee;
        
        -- 회원 누적 구매 금액 업데이트
        UPDATE member 
        SET total_purchase_amount = total_purchase_amount + v_item_total
        WHERE member_id = p_member_id;

        --  재고 차감
        UPDATE product_detail pd
        JOIN cart_item ci ON pd.product_detail_id = ci.product_detail_id
        SET pd.stock_quantity = pd.stock_quantity - ci.quantity
        WHERE ci.member_id = p_member_id 
          AND pd.stock_quantity >= ci.quantity; 

        -- 주문 생성
        INSERT INTO orders (member_id, order_date, shipping_address, total_price)
        VALUES (p_member_id, NOW(), p_shipping_address, v_final_price);

        -- 주문 상세 내역 다중 삽입
        INSERT INTO order_item (order_id, product_id, status_id, quantity, order_price)
        SELECT 
            LAST_INSERT_ID(), 
            pd.product_id, 
            1,                
            ci.quantity, 
            (p.price + pd.surcharge)
        FROM cart_item ci
        JOIN product_detail pd ON ci.product_detail_id = pd.product_detail_id
        JOIN product p ON pd.product_id = p.product_id
        WHERE ci.member_id = p_member_id;

        -- 장바구니 비우기
        DELETE FROM cart_item WHERE member_id = p_member_id;

        COMMIT;
        SELECT CONCAT('SUCCESS: 주문 완료. 상품(', v_item_total, '원) + 배송비(', v_shipping_fee, '원) = 총 ', v_final_price, '원 결제됨') AS result;
    END IF;

END //

DELIMITER ;



장바구니 API 명세서 작성

장바구니 파트 CRUD를 작성하였다.

API 명세서

profile
이현경
커피 한 잔의 여유를 아는 품격있는 여자
이전 포스트

[DB] 팀 프로젝트 1, 2주차 작업 기록

다음 포스트

[DB] 팀 프로젝트 4주차 작업 기록 및 소감

0개의 댓글